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
408 views

Have foreign key constraint and index key for the same column(s)

I am creating a composite index key for 2 columns and then adding foreign keys to its appropriate table. Do I need to create a multiple index key reference and then create separate foreign keys? My ...
2
votes
1answer
57 views

Optimizing a 3 table join query

SELECT job.id_job, job.fk_company, job.fk_user, job.fk_job_category, job.fk_job_type, job.fk_place, job.job_identifier, job.external_job_id, job.title, job.short_description, job.status, ...
2
votes
2answers
101 views

Can't select 2 tables at the same time

I have a pesId (peopleId) column at pessoas (people) where is stored data from medics and patient. Also I have a table that stores medical consult information like Date, patient id, medic id and ...
2
votes
3answers
7k views

SQL Error (2027): Malformed packet

I have a mysql database (Version 5.6.23-log running on windows) that is set up for use with Teamcity. When running a command against it using HeidiSQL I am getting an error: SQL Error (2027): ...
2
votes
1answer
4k views

Backup/Restore Users/Passwords/Privileges

I am moving from one server to another and I want to backup all databases + users/privileges/passwords from my MySQL Server. I found to backup a database using mysqldump, but I cannot figure out, how ...
2
votes
2answers
1k views

SELECT and UPDATE in a single query while updating selective fields of duplicate records only

I have a MySQL table named Contacts_table with, say, the following contents: ID NUMBER NAME CITY 001 2222222222 John Doe Los Angeles 002 ...
2
votes
3answers
385 views

Query 'optimizing' state is taking too long

I have a MySQL 5.6 on Amazon RDS that I'm using for testing some data archiving scripts. I'm removing oldest data based on a "updated_date" column and index. Curiously, after removing a few million ...
2
votes
2answers
2k views

Best way to implement concurrent table based queue

I have a table in MySQL that represents a queue of links to be processed. The links are processed by an external app, one by one, and deleted in the end. This is a high volume queue and I have ...
2
votes
1answer
87 views

MySQL stored procedure user privilege

I have a query regarding user permission on a stored procedure. If a user that does not have permissions to create tables in a database, but it does have permissions to create and execute stored ...
2
votes
2answers
1k views

How do I give root@localhost permission to grant privileges in MySQL?

Whenever I try to Grant Privileges as root@localhost, it says permission denied (using password=Yes). My root user is the one that I am logged in as. I verify this by select user(); It responds ...
2
votes
2answers
131 views

Squirel SQL uses wrong command for MySQL when renaming a column

I installed Squirel SQL on Ubuntu and a downloaded and put a MySQL driver into the lib folder of the Squirel folder. Then I chose this driver in the "extra class" tab when configuring Squirel MySQL ...
2
votes
2answers
405 views

MySQL, RAM disk for tmpdir - how to figure out what size it should be?

We regularly run a few large SQL queries to generate reports. It seems that the bottleneck is the amount of time that is spent writing to the tmpdir, which is currently on disk. We are upgrading to a ...
2
votes
1answer
259 views

Are CREATE INDEX statements replicated in row-based Mysql Replication?

I've not been able to find a simple explanation of what kinds of statements are or are not replicated from a master db to a slave in MySQL, row-based replication. Specifically I want to add a ...
2
votes
2answers
238 views

SELECT WHERE CASE

I have the following table (MySQL): -------------------------------------------------------- | id |product_id| store_id | stock_id | priority | ...
2
votes
1answer
385 views

What happens if two mySQL queries both try to lock the same row at the same time?

So this isn't exactly my strong suit, but here's my basic understanding of the situation: If two threads of the same application both try to execute the same query at the same time, mySQL will ...
2
votes
1answer
152 views

How to select all Mondays (or any day combo like MTW, WTTh) between two given dates in MySQL?

What I see over the internet is just counting how many days occur whenever you input the day of the week you need. Like between May 1 to May 31, how many mondays are there in it. What I need is to ...
2
votes
3answers
1k views

find all numbers that appear at least three times consecutively

This is a question from leetcode online judge I tried this SELECT Num FROM Logs GROUP BY Num HAVING COUNT (Num) >1 but got runtime error!! Anyhelp would be appreciated
2
votes
1answer
62 views

Slow query on mid-size tables with good index coverage

I'm running a query that touches 2 moderately sized tables (1-5 million rows). EXPLAIN shows it touches only indexes and very few rows, however it takes very long to complete (~8 seconds). The only ...
2
votes
1answer
1k views

Dropping multiple KEYS of a table

Dropping multiple KEYS in a table is a preferred option or one by one is preferred? “DROP KEY A,DROP KEY B,DROP KEY C" For more 900 million records in the table. How does it work internally?
2
votes
3answers
705 views

mysql replication master 5.5 slave 5.1 error on create database

I have a replication system where my master has a mysql 5.5 version while the slave is mysql 5.1. I know that this is not supported because the slave version should be greater than the master, but we ...
2
votes
2answers
585 views

Foreign key set to cascading but fails to update - why?

As mentioned elsewhere, I am rather new to SQL and databases. Getting my hands dirty at the moment and trying all the different possibilities. I have a server running MySQL and set up the following ...
2
votes
3answers
278 views

getting MySQL error 1064 but very (absurdly) simple query in MySQLWorkbench

I'm getting a MySQL 1064 error for the simplest query when run with \G at the end. This works: select * from locations; and this doesn't: select * from locations\G hmmm... is there any way to ...
2
votes
3answers
3k views

Could not start mysql or MariaDB. Permissions were ok, but mysql-bin.index permission error

Using MariaDB 5.5.36. Here's what I got while trying to start MariaDB: # syslog Dec 14 09:40:52 vps1497 mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql Dec 14 09:40:52 vps1497 ...
2
votes
1answer
127 views

at least one member of a list within another list when querying over a join

Assume the following layout in a MySQL database Activity (id,etc) [id is primary key] AcitivityMapper (activityID, targetYearID) [composite key] Years (targetYearID, targetYearName) [id primary key, ...
2
votes
1answer
561 views

What is the Best way to set Disaster recovery setup for percona Xtra DB Cluster?

We do have a Percona XtraDB Cluster(Master-Master-Master) setup in one country. Now we want to setup a disaster recovery setup in some other country. Please provide us the best possible ways to do ...
2
votes
2answers
559 views

Mysqldump throwing error 'Couldn't execute 'ROLLBACK TO SAVEPOINT sp':

Can any one please explain what is happening with our DB dumps? We are executing dump as below mysqldump -v --single-transaction --quick --lock-tables=false --skip-triggers -uXXX -pXXX ...
2
votes
1answer
220 views

Does the order of a mysql multi-column MySQL index matter - if you're always using all columns in the query?

I'm aware that when you define a MySQL index that the order of the fields matters if the query is only using part of the index, as described here. But if your query is always going to use all columns ...
2
votes
1answer
646 views

Breaking down by minutes in between two dates

In MySQL, I've got a data model with a "visit" table that has start and end datetimes. How can I get a breakdown of the number of visits that are active per minute? e.g. | visit_id | start | end ...
2
votes
1answer
7k views

Any known issues upgrading from MySQL 5.1.73 to 5.6.21?

I am planning my MySQL DB ver5.1.73 running on a CentOS 6.5 machine to MySQL 5.6.21. I understand the basic procedure for this based on what I have read on StackOverflow, but I was curious if anyone ...
2
votes
1answer
396 views

Impact of using UUIDs with an UNIQUE index vs. PK?

It's well known how the performance of random UUIDs as PKs in an InnoDB table degrades terribly as it increases in size. Would an UNIQUE index for a non-PK UUID column have the same impact? UUIDs are ...
2
votes
1answer
243 views

MySQL Security - Storing lots of sensitive phone numbers securely?

Imagine a table in a database in MySQL that holds millions of telephone and cellphone numbers that relate to itemized billing for VoIP and Cellular carriers. All of these numbers are in VARCHAR ...
2
votes
2answers
4k views

mysql service takes too much memory

i have a mysql 5.6.19 on linux server, and it takes about 8 Gb of memory. That is much more than i expect. There are about 150 db, each with 700 tables. All of them are innodb. We do not care about ...
2
votes
1answer
202 views

Why does MySQL increment the auto_increment value when there's a constraint violation?

I have a table in a MySQL database with one column id, which has auto_increment and the Primary Key. There's also a Unique constraint in this table, on different columns. I noticed that when I try to ...
2
votes
1answer
979 views

MATCH AGAINST one character words returns empty rows with ft_min_word_len = 1

I've set ft_min_word_len = 1 to my.cnf file, also SHOW VARIABLES LIKE 'ft_min_word%' displays ft_min_word_len as 1. Then I've reloaded all database tables and it still doesn't search for one character ...
2
votes
1answer
9k views

Mysql enable remote connection in centos,my.cnf does not have bind-address to comment out

I am trying to make my mysql server running on centos to allow remote database connections.I am reading this article http://xmodulo.com/2012/06/how-to-allow-remote-access-to-mysql.html that says i ...
2
votes
2answers
350 views

Should we enable innodb_file_per_table for single tenant SaaS app (thousands of databases)

We have a mySQL backed SaaS application where each customer has their own mySQL database. So think of it like a wordpress/magento etc. database for each client. Each database has ~200 table, and we ...
2
votes
1answer
66 views

Which database technologies to consider for a data search application?

I'm designing an application that only does read queries to some database tables (it's a kind of search application); the queried data is updated at night by the system (so speed is less critical, ...
2
votes
2answers
328 views

“All joins can be rewritten to sub queries”, is this statement true?

I wonder if there any case that a join can not be converted to a sub-query. BTW: I am not discussing advantages and disadvantage of using sub-query or using join.
2
votes
4answers
6k views

How to subtract fields and insert the value in other field automatically?

id val1 val2 total 1 10 4 10-4(automatically get the value) . . . . I am using php and mysql. I have a total column I need to make calculation automatically in a field, how it ...
2
votes
1answer
407 views

mysql 5.6 gtid replication: is log_slave_updates required?

I've read the docs saying log_slave_updates is required. It makes sense for a general promotion situation. But is that really still required for something you're intending to be a read only slave ...
2
votes
1answer
72 views

Understanding JOINs and why the syntax works the way it does

Heyo, I'm confused as to how this works: UPDATE original SET price=(SELECT price FROM new WHERE new.id=original.id) WHERE id IN (SELECT id FROM new); What it does is updating an original table ...
2
votes
1answer
815 views

Summarize from unlimited level depth parent-child in mysql

I have 2 table master coa and coa transaction in MySQL like this, master : id | coa | id_parent | ----+------+-----------+ 1 | 1 | NULL | 2 | 11 | 1 | 3 | 111 | 2 ...
2
votes
1answer
5k views

pt-heartbeat showing “Host is blocked because of many connection errors”…but connections are well under limit

I'm running MySQL in a master-slave configuration. pt-heartbeat is showing "Host is blocked because of many connection errors"...but connections are well under limit (limit is 600 connections, MySQL ...
2
votes
1answer
571 views

Inconsistent MySQL query execution times

We run tests on two servers: one in production [Old Server] and the other one in tests [New Server]. Both tests used the same database. [New Server] Dedicated MySQL 5.6.17 server on Ubuntu 14.04 ...
2
votes
1answer
1k views

Is my MySQL instance irretrievably dead?

I'm trying to install a JIRA instance on a MySQL 5.1.73 instance running on CentOS 6.5 for our developers. The install gets to the point where I need to create the database, and then FLUSH ...
2
votes
2answers
3k views

How to monitor InnoDB Disk I/O Required for MySQL?

I am doing an estimate on how much i/o[disk read write] on Mysql 5.6 [innodb storage engine], for getting idea how to calculate total number of disk read write i have searched a lot on SO on DA and ...
2
votes
2answers
336 views

Why is this simple join taking so long?

I'm trying to run some reporting queries on a MySQL database, and I'm running into serious performance issues for some reason. One table, page_views, is extremely large (~70 million records), so some ...
2
votes
3answers
1k views

Avoiding MySQL Fabric to become a single point of failure

I managed to install and configure MySQL Fabric that manages multiple MySQL Server nodes. It works very well since I tested it by connecting to the Master node directly and played with the data ...
2
votes
1answer
165 views

How can I solve this error InnoDB: is in the future?

InnoDB: is in the future! Current system log sequence number 412018685584. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See ...
2
votes
1answer
184 views

How do I write a mysql query to combine data from related tables into a single summary?

I'm having trouble defining the data in a query for a chart. I want to summarize data from the first table and combine it with a data summary from the second table. Products - (serial_number, date) ...