MySQL version 5.6 - please also tag with mysql for search purposes.

learn more… | top users | synonyms

17
votes
2answers
27k views

InnoDB: Error: Table “mysql”.“innodb_table_stats” not found after upgrade to mysql 5.6

I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup I found a possible solution here, but it does not seem official. ...
14
votes
3answers
11k views

Why query_cache_type is disabled by default start from MySQL 5.6?

We've upgraded to MySQL 5.6 and start seeing the loading of db server increased significantly, and finally found out the query_cache_type is defaulted to off start from 5.6. We enabled it again and ...
12
votes
5answers
26k views

Cannot open table mysql/innodb_index_stats [duplicate]

After a fresh install of XAMPP and import from my linux live db that is working to the windows Dev-stage, I started encountering problems with INSERT. Everything else seems to work well. The errors I ...
11
votes
3answers
950 views

MySQL instance stalling “doing SYNC index”

Problem An instance of MySQL 5.6.20 running (mostly just) a database with InnoDB tables is exhibiting occasional stalls for all update operations for the duration of 1-4 minutes with all INSERT, ...
11
votes
1answer
566 views

MySQL Commited data not seen to select query

Context: The framework used is Spring and all queries are run with JdbcTemplate. Mysql Server version is 5.6.19. The table is an InnoDB table and defaults like auto commit and isolation level ...
8
votes
1answer
720 views

Issues with MySQL installation

First of all, excuse the length, this post is very long, and needs some background, second; it has been hard for me discerning the signal from the noise, please bear if some of this is not relevant: ...
7
votes
5answers
888 views

How to update 10 million+ rows in MySQL single table as Fast as possible?

Using MySQL 5.6 with InnoDB storage engine for most of the tables. InnoDB buffer pool size is 15 GB and Innodb DB + indexes are around 10 GB. Server has 32GB RAM and is running Cent OS 7 x64. I have ...
6
votes
1answer
690 views

MySQL loading NULLs in numeric columns

MySQL 5.6.23, InnoDB I am loading tables from character delimited text files using the LOAD DATA INFILE commnd and I would like every field with a \N, which is the NULL character in this setting, to ...
6
votes
1answer
116 views

Non-grouping field error with ONLY_FULL_GROUP_BY mode and tables that have the same column name

I'm finding that MySQL seems to have an issue when ONLY_FULL_GROUP_BY mode is enabled and the query includes a HAVING that refers to grouped columns from different tables that have the same name. ...
6
votes
1answer
132 views

MySQL database design to sort and filter air traffic data

I am struggling to design the correct database / structure for what I'm trying to do. Let me try and explain the situation. Background We are a group of enthusiasts who monitor air traffic via ...
6
votes
2answers
180 views

How can I copy the MySQL EXPLAIN result into a table?

I'd like to archive the results of a series of query EXPLAIN plans into a table for later analysis. I created a table that contains all the fields from an EXPLAIN result, but I can't figure out how ...
5
votes
3answers
2k views

How can I force MySQL to IGNORE ALL indexes?

I have read articles about FORCE index, but how can I force MySQL to IGNORE ALL indexes? I tried SELECT * FROM tbl IGNORE INDEX(*), but I was not successful. As for why I (and others) need to do ...
5
votes
2answers
8k views

ERROR 1054 (42S22): Unknown column 'plugin' in 'mysql.user'

I am trying to grant privileges for a user to a new database mysql> grant all on db_test.* to 'user_test'@'localhost' identified by 'usersexistingpassword'; I get the following error ERROR 1054 ...
5
votes
1answer
621 views

Why does InnoDB table by default uses the UNIQUE constraint instead of the PRIMARY KEY?

In my MySQL database I have a table with 4 columns, the PRIMRAY KEY, two columns with a FOREIGN KEY CONSTRAINT and one UNIQUE index on all fields except for the PRIMARY KEY. The SHOW CREATE TABLE: ...
5
votes
3answers
169 views

MySQL Index Creation Internals

Right now there are two ways to build an index of a table in MySQL: Create the table structure first, then import the data and add indexes later. Create the table structure with indexes and then ...
5
votes
1answer
6k views

Best way to create a materialized view in MySQL

I am using MySQL 5.6. I am not being able to a create materialized view like I can in Oracle. I have seen one or two solutions like Flexview. Can anybody tell me best way to create a materialized ...
5
votes
1answer
2k views

Slower queries after upgrade from mysql-5.5 to mysql-5.6

There is lots written about how MySql 5.6 is faster than MySql 5.5 (e.g. http://dev.mysql.com/tech-resources/articles/mysql-5.6.html) but we upgraded our Amazon RDS instance (using the new upgrade ...
5
votes
0answers
102 views

insufficient history for index

In MySql log, there is one error `InnoDB: insufficient history for index 0' I don't know why its occur. I have done google and found following ...
4
votes
1answer
898 views

MySQL 1175 Error While Using Primary Keys

What would cause the error 1175 that starts out with: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.... The problem I have is that I did include ...
4
votes
1answer
16k views

mysql Lock wait timeout exceeded; try restarting transaction

we are running java application, running for ages, back end is db is MySQL, recently updated to mysql 5.6. Everything was running fine, past week started getting this error Lock wait timeout ...
4
votes
2answers
110 views

find the beginning and ending values of sequential runs

This article does the inverse of what I need: Find which numbers in [1, 161] are not in the result set? Given the sample data they used: mysql> CREATE TABLE wp_blogs -> ( -> ...
4
votes
1answer
8k views

How to do While Loops?

How can we use while loops in MySQL? My test script: BEGIN SELECT 0 INTO @n; WHILE @n < 10 DO SELECT @n; SET @n := @n +1; END WHILE; END; But it has syntax errors. I'm ...
4
votes
1answer
1k views

How to drop all scheduler events in MySQL 5.6?

In MySQL 5.6 is there a way to drop all scheduler events with an administrative command or do I have to go through them one by one with DROP EVENT [IF EXISTS] event_name?
4
votes
3answers
2k views

How can I make MySQL client read password from mylogin.cnf?

I'm trying to make the mysql client connect to a mysql server without requiring the password to be given interactively. Steps taken: 1) First create a mylogin.cnf file $ mysql_config_editor set ...
4
votes
1answer
2k views

What is default MySQL binlog retention in AWS RDS?

I tried to retrieve my MySQL RDS binlogs in order to run manual "rollback" for some SQLs. Unfortunately, I noticed that only 2 binlogs for the last 30 minutes are available. "call ...
4
votes
1answer
68 views

Running Triggers on Slave with Row-based Events in MySQL >=5.6

We would like to have a setup where a slave has triggers that are not present on the master for the purpose of updating some summary tables or run a specific udfs.sys If one uses statement-based ...
3
votes
2answers
186 views

Different behavior between MySQL 5.5 and 5.6

I have this schema with data: CREATE TABLE `ticket` ( `id` INT NOT NULL, `name` VARCHAR(45) NULL, `type` INT(11) NULL, PRIMARY KEY (`id`)); CREATE TABLE `task` ( `id` int(11) NOT NULL, ...
3
votes
2answers
75 views

MySQL for Oracle DBA

I am a newbie to MySQL, but understand Oracle databases well. I need a quick help to understand the architectural differences. Currently I am working on a Windows platform. 1) In Oracle we have to ...
3
votes
2answers
85 views

Better understand table indexing best practices

I'm not completely sure when would I start indexing my tables, when there are some criterias that I should consider: The tables are not that big, I think (1000 - 10,000 rows). The searches are made ...
3
votes
1answer
464 views

“Set Password” with a limited user

I'm using MySQL 5.6. I want to have a limited service-user which is able to create/modify the users of my databases. Now my problem is that I can quite figure out what privileges my service-user ...
3
votes
2answers
1k views

Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?

I've been growing concerned about the large size of ibdata1 that can never shrink even when using file-per-table on innodb Moving the undo log files outside seemed logical but this procedure seems ...
3
votes
1answer
51 views

mysqldump inconsistencies

I'm planning to take a dump of a table with the --single-transaction option enabled. I know that if we execute any DDL statements during the dump process we might get an inconsistent dump. Will the ...
3
votes
1answer
59 views

How do we implement SQL Server IF EXISTS (SELECT) in MySQL 5.6?

We would like to convert the following SQL Server statement: IF EXISTS(SELECT OptionValue FROM Options WHERE OptionCatagory = 'General' AND OptionName = ...
3
votes
1answer
724 views

Why didn't MySQL keep the innodb_file_format after loading a mysqldump

I was loading a mysql dump today when I ran into ERROR 1118 (42000) at line 279: Row size too large (> 8126) Which I thought was weird because I know I had turned on row compression and was ...
3
votes
1answer
112 views

Improve performance of query with multiple group by column and count function in select

Brief Background of issue We have a aggregate table to store activities of competitors and this table have currently 3 Million activities and we need to retrieve data from this table group by ...
3
votes
2answers
55 views

How to handle temporal datatype columns when doing upgrade from 5.5 to 5.6

I want to upgrade my MySQL 5.5.40 server to 5.6.28. But as per the documentation we need to alter the tables having columns which are using date or datetime or timestamp data types as storage ...
3
votes
1answer
41 views

Data truncated for column - return fatal error

I get this error in my error log. Warning: 1265 Data truncated for column ... How to configure mysql to return a fatal error on this kind of warnings? Mysql 5.6.27 cnf sql_mode ...
3
votes
1answer
929 views

Decimal or Point Data Type for storing Geo location data in MySQL

I want to store Geo location for each Address in my MySQL(Innodb) "tblAddress" table and I think I have 2 Options: 1) 2 columns for Each Address: Latitude DECIMAL(10, 8), Longitude DECIMAL(11, 8) ...
3
votes
2answers
131 views

SQL Question: Selecting different language versions based on availability

I have the following situation: I have a table that contains entries with language versions (German and English) as part of the key (the other part being a "name" field). However, not always both ...
3
votes
1answer
472 views

How to upgrade MySQL (5.5) production DB to 5.6 with minimal downtime

Our existing Database size around 200GB. We want to use consistent, parallel backups methods. For testing, I migrated my Development DB from 5.5 to 5.6 by running mysqldump with option ...
3
votes
2answers
302 views

Any way to speed up reorganize partition of a very large table in MySQL?

So we have this massive 1.5TB+ table with billions of records that has been neglected for the past few months and needs to have new partitions added. The partitions are based off a timestamp which are ...
3
votes
1answer
1k views

Is it mandatory to enable --log_slave_updates with MySQL Replication using GTID? [duplicate]

I'm setting up 5.6 Percona Mysql, in order to enable GTID should I have to enable log_slave_update on both Master and Slave ? If I should enable log_slave_update, then I'm counting this to CONS for ...
3
votes
3answers
2k views

Amazon RDS - and binary log rotation

New to RDS (used to having my own server in my own data center). We are on MySQL 5.6 on RDS. when a slave gets behind in replication lag, the master ceases to rotate out its binary logs. It is, ...
3
votes
2answers
2k views

MySQL version confusion

How is this possible: I installed mysql-5.6.11-osx10.7-x86.dmg which is supposed to be version Mac OS X ver. 10.7 (x86, 32-bit), DMG Archive (32 bit!) But upon SHOW VARIABLES LIKE "%version%"; I get ...
3
votes
1answer
32 views

Performance schema: 'stage/sql/Sending data'

Background I am using MariaDB 10.0 but MySQL 5.6 answers are also of interest. Two machines cloned from the same image, same MariaDB configuration as far as I could tell, same dataset. One machine ...
3
votes
1answer
60 views

InnoDB - Intermittent CPU spikes on a large database

I have a machine with a large MySQL 5.6 database (multiple tables with 10-100 million rows). It has a considerable amount of load, especially during the evening and is running on a 16 core machine. ...
3
votes
0answers
56 views

Read READ COMMITED transaction advantage in MySQL, high performance object model

I am writing high performance object model with READ COMMITED locking. My question is if there is any reason to starting READ ONLY transaction for read only operations vs. standard SELECT not ...
3
votes
0answers
19 views

Cluster of Clusters (Not using MySQL-Cluster)

I'll start with an apology, I'm an Ops guy at heart, not a DBA, so I may use the wrong terminology, not in an effort to annoy, merely because I don't know any better. OK, that over with, on to the ...
3
votes
0answers
137 views

Create an SSL connection with ODBC driver to MySQL(5.6) using openSSL

I want to create an asymmetric SSL connection from MySQL to an ODBC driver but don't fully know how to generate the keys and certificates (I'm using openSSL as recommended here: ...
3
votes
1answer
127 views

query optimization of a left join

I have the following query written into the slow log (mysql-slow.log). I don't have much idea how to optimize this query. I already have indexes for: mode, f.user_id... etc but doesn't seem to ...