All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

2
votes
1answer
105 views

Compare two tables with same structure, but differing membership numbers

I have two tables with the same structure and most of the data is the same. I also have a third table that contains ids and names. The ids are connected to the membership numbers discussed below. The ...
2
votes
1answer
36 views

Wordpress using varchar(255) for index with InnoDB and utf8mb4_unicode_ci?

I'm trying to learn a bit more about the details of proper database construction and ran across something that confuses me. In a current Wordpress installation, I'm finding varchar fields used for ...
0
votes
0answers
45 views

Should I make a new table to store date?

I want to store date on which users sign up on my site. Since on a given date several users will be signing up, there will be repetition of data. Should I make a new table to store date to make it ...
0
votes
1answer
21 views

Is it possible to log mysql queries from other hosts in general log?

I'm having a task to figure out what are the hosts that are still running scripts that accessing our database directly so we can prevent that from happening(we only want our web app to access the ...
0
votes
0answers
50 views

Is there a clean way to extract data from this single-column value?

I'm attempting to extract data from a single MySQL column which contains data that looks like this: a:6:{s:15:"info_buyRequest"; a:3:{s:7:"product"; s:1:"1"; s:3:"qty"; ...
0
votes
0answers
45 views

mysql: Error reading file 'UNOPENED' (Errcode: 5 “Input/output error”)

I restore the backup of my main server daily in this other server. Today something went wrong and I received this message: mysql: Error reading file 'UNOPENED' (Errcode: 5 "Input/output error") I ...
0
votes
0answers
44 views

MySQL DB server had restarted automatically

We are using MySQL 5.6.27 version and Amazon AWS instance for a database server with the configuration given below: innodb_buffer_pool_size = 40G innodb_buffer_pool_instances = 40 ...
0
votes
1answer
49 views

Trying to fit indexes in memory: partitioning vs compression or neither?

I have a "transaction" table that grows at about 150k rows-per-day rate. 97% of user aggregate queries refer to the last 3 months of data and must run very efficiently. Trimming older transaction data ...
0
votes
1answer
34 views

MySQL Commands out of sync error

I am new to MySQL, normally use SQL Server. I have a MySQL server (version 5.1.73) and I have two databases, syslog and syslog_archive. There are three tables: syslog.logs syslog_archive.logs ...
0
votes
1answer
46 views

View created using Join Queries taking too much to load

I have written a query to create a view which i can use as a flat table in my application, which is having more than 30 inner join conditions across 25 tables. Now the problem is every time i am ...
0
votes
0answers
32 views

Unable to fetch data based on latest date and time

I have a table which stores application details. Applications are nothing but a set of data showing scores. Each application has 6 parts namely 1,2,3,4,5, and 6. So, in the database table parts can ...
1
vote
2answers
37 views

Inserting BLOBs through PHP are always 0 bytes

I'm making a site that allows users to upload small thumbnails for books they post. I want to store them as a BLOB. The first row there was inserted manually through phpMyAdmin. The second row was ...
1
vote
2answers
74 views

Fix MySQL server crash [on hold]

We recently suffered a pretty severe server crash on one of our linux machines (Fedora Core 5), which has resulted in a lot of corruption of data, unfortunately the mysql database is amongst the data ...
0
votes
1answer
46 views

MySQL MVCC implementation

The question is about the behavior of simultaneous SELECT and UPDATE in MySQL InnoDB table: We have a relatively large table which we periodically scan reading several fields including a field named, ...
1
vote
1answer
54 views

MySQL57 is using 25% CPU and 10% Memory on Desktop

I do a simple left join query on two fairly large table using MySQL57. It takes a long time. I check my desktop's CPU and memory usage for MySQL. It only uses 25% CPU and 10% Memory on my Desktop of ...
0
votes
1answer
50 views

PostgreSQL to MySQL migration to use database for WordPress site - how to's?

I have a complex issue to solve, related to database migration. I have a website full of news to migrate that is currently based on a PostgreSQL database and written in PHP, without a CMS. I have a ...
2
votes
1answer
143 views

join to other table multiplied by loop foreign key in mysql

i have 2 table one node and relation and Second store buy each node. my code work fine but when sum multiple buy for each node result multiplied by number of purchases . table nodes +-------------+--...
0
votes
1answer
42 views

How to migrate an Informix database from IBM AIX to MySQL on Linux [closed]

I have a migration activity where I have to migrate an Informix database which is running on an IBM AIX machine to MySQL running on a RedHat Linux machine. How can I do that?
0
votes
0answers
65 views

Why does CREATE TRIGGER lock a table in MySql?

What I've done? I tried to execute a CREATE TRIGGER statement on a table with about 4.x million rows. The trigger I tried to create was a INSERT trigger. And it looked like that the whole table was ...
4
votes
3answers
74 views

Behavior of transactions on multiple connections

I have a MariaDB database with InnoDB tables. I have two connections to the same database. One connection is using a transaction around a large chunk of work. The other connection is tasked with ...
0
votes
1answer
57 views

pt-online-schema-change — can't get it to stop quitting w/ “Threads_running=108 exceeds its critical threshold 100”

I've got an InnoDB table over 50GB with indexes. The query I'm running looks like this: pt-online-schema-change h=localhost,D=db,t=table --critical-load="Threads_running=100" --check-slave-lag "h=XX....
2
votes
3answers
122 views

Can't add Foreign Key Constraint

As far as I can tell it should work, I went through other similar questions but it seemed to be a different thing causing the same error. CREATE TABLE Job ( JobNumber INT NOT NULL, JobName ...
1
vote
1answer
16 views

MYSql Full Text Boolean search and relevancy score improvements

I am using a mysql BOOLEAN full text search as shown in the query below and am getting what I think to be bad results that could be more relevant. Query: SELECT phppos_items.name,MATCH (phppos_items....
1
vote
1answer
19 views

Where do i change the my.cnf on mysql cluster

I'm trying to change the max_allowed_packet setting on mysql cluster (version 7.4.8) - i have 5 nodes 2x SQL Node 2x Data Node 1x Managament Node I have updated the my.cnf on all of the servers -> ...
-1
votes
2answers
62 views

Is there an SQL statement that I can run in MySQL that returns the location of my database(s) on my server? [closed]

I did a bit of research online and it looks like I need to have a column (or something) in my database called "sys.master_files" but I don't know what that consists of or how to create it.
1
vote
0answers
27 views

Mysql replication, master_log_pos sync but tables not update

First, my replication config on Master: replicate-do-db = mydb binlog-do-db = mydb expire_logs_days = 2 max_binlog_size = 100M binlog_format = MIXED log-...
1
vote
1answer
21 views

Is it possible to reorder tables in phpMyAdmin?

By default, database tables are ordered in alphabetical order. Can this be reordered? For example: default alphabetical: company, profile, user common User Flow: user, profile, company Maybe the ...
1
vote
2answers
43 views

Slave_IO_Running: Connecting in Master-Slave Replication

I was trying to implement Master-Slave Database Replication on two systems. I followed the steps from the following links https://www.youtube.com/watch?v=DmQWcU2INqQ and Master Slave Replication ...
1
vote
0answers
44 views

MySQL table filed with text datatype inserting some script value automatically and ignored user input

In my MY-SQL database table having with text datatype. In that field having some unwanted script tag added automatically. If I given that any value also ignored. and also I tried to edit that value ...
1
vote
2answers
77 views

High CPU usage from sorting result queries

I have two machine have two master-master mysql replicate, only problem i have that i got massive queries like "sorting result" queries and i don't know how to fix these queries . mysql process cpu ...
2
votes
1answer
22 views

Which is better in a master-slave setup to use local queue or write changes to remote instantly?

This is more of a a design question I have a local db (mysql if relevant) for reads and remote master for writes but wondering if it's better to.. 1) Write changes to a local queue and bulk insert ...
1
vote
2answers
69 views

How to decide that a new table is needed?

This might seem a vague question but I am going through a bit of a dilemma here. How do you people decide when a new table will be need? For example, when creating a table for storing user ...
2
votes
1answer
53 views

How to identify consonant/vowel pattern matches in MySQL?

Evening all, I'm having a bit of trouble with a MySQL table full of vocabulary words today. I need to be able to quickly retrieve words with the same arrangement of consonants and vowels, so my ...
1
vote
1answer
57 views

what's practical example / use case SELECT SLEEP() for DBA

I don't know how to use SELECT SLEEP(). It looks like a dirty hack. I found an interesting question How and when to use SLEEP() correctly in MySQL?. The answer is developer oriented: If you don't ...
1
vote
1answer
41 views

Use trigger to populate another column

I have two counters in a mysql table. I would like to have another column that contains the difference between counter column 1 and counter column 2. Could I use a trigger after counter column 2 is ...
1
vote
2answers
74 views

Help combining two MySQL queries to improve performance

I have two MySQL queries. The first one returns a list of codes, which comprise either one or two letters (e.g. "AB", "WO", "G"). This is the query: SELECT d.field_id_105 AS code, title FROM ...
1
vote
1answer
33 views

trigger to delete records with same value from another table after inserting record in another table

i have two tables normal and abnormal,i want to create a trigger that will be able to delete records from normal table once i insert the same details in the abnormal table for example if a person is ...
0
votes
0answers
16 views

php mysql insert time

I am getting time values from another application in this sample format "2016-12-25 13:43:15" but when they get to my database they change to "0000-00-00 00:00:00" for the date am using DATETIME mysql ...
2
votes
2answers
50 views

Importing from CSV file, is crashing mysql server

I am facing a problem with importing large data into single mysql table. Basically I assume it is happening because limited machine resources but, I am having troubles to proof it. this is my CSV ...
0
votes
1answer
29 views

MySQL: JOIN and Conditional Statement

Can comeone help me to decipher the right logic of code needed for my query? I just can't find solutions about this or maybe my search queries are just vague. Lol. I made a query and thought before ...
2
votes
0answers
36 views

Mirror data on local SSD storage to conserve EBS I/O?

I have an AWS instance with local (ephemeral) SSD storage backed by a permanent EBS storage. Is it possible to configure the local storage to act as a mirror/cache for row data in MySQL? I do not wish ...
0
votes
0answers
14 views

On Duplicate Update Multiple Keys (not or) With And

I have friends table user_id_1 | user_id_2 | points 1 2 99 ADD UNIQUE (user_id_1, user_id_2) works perfectly, but i run this query; INSERT INTO friends (user_id_1,user_id_2) ...
1
vote
1answer
36 views

Order by varchar mysql

Is there anyother alternative for order by varchar clause? At worst case consider I want to order by varchar column that is not indexed. When I tested a query with loads of data it took many times to ...
2
votes
3answers
194 views

Why does a query with a LEFT JOIN run for a long time?

I have this query, it works fine and takes over 2.5021 secs: SELECT c.id AS 'id', count(c.id) as orders, o.created_at as last_order FROM customer c, order_2016 o WHERE o.customer_relacional_id = c....
3
votes
1answer
54 views

Create index for multiple WHERE EXISTS subqueries

I have to find an efficient way to write a very specific query for an application that I'm developing. The idea is the following: Find all the genetic markers for which there exists at least one ...
1
vote
1answer
33 views

I'm seeing inconsisntecies in table data after exporting from one server and importing to another, why is this?

I'm quite new to the process of importing/exporting databases. I'm using Maria DB, and HeidiSQL to run queries, etc. I exported an entire database from our dev server, and loaded it into our live ...
2
votes
2answers
50 views

which time period covers date string over datetime column

My question is a restlessness I been having for a time. So, here it goes: If I have a datetime column and perform a date search without the time 'yyyy-mm-dd' in a between statement. Which period of ...
3
votes
2answers
33 views

Select only rows with at least 2 non-missing values

I'm pretty new to MySQL (used to programming in R/Stata), and getting stuck on this - I'm probably going about it the wrong way. Any pointers would be appreciated! I have a large database (a couple ...