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

learn more… | top users | synonyms

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 ...
16
votes
2answers
26k 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. ...
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
5k views

MySQL service stops after trying to grant privileges to a user

I am using MySQL 5.6, I was trying to create a super user for specific database but I got ERROR 1396 (HY000): Operation CREATE USER failed for 'superuser'@'%' Then I thought maybe there is already ...
12
votes
5answers
25k 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 ...
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 ...
11
votes
3answers
888 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, ...
1
vote
2answers
199 views

mysql master slave replication scenario

We are creating a new mysql slave, but for dump we are taking from a backup server which is a slave of current master. Now to enable replication we want to connect it with the master so should I take ...
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 ...
0
votes
1answer
4k views

In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table

On Windows, I upgraded from 5.1 to 5.5 no problem. Copied my 5.1 data folder into my 5.5 instance Started mysqld skipping grants Ran mysql_upgrade All good, but going from 5.5 to 5.6: Copied ...
1
vote
1answer
132 views

MySQL 5.6 - Creating an event inside a stored procedure

I am trying to create a procedure which create an event in MySQL. For example, something similar to this: delimiter // CREATE PROCEDURE schedule_my_db_job() BEGIN CREATE EVENT MY_DB_JOB ...
1
vote
0answers
24 views

Connection timed out & MySQL server has gone away with innodb engine

I have created chat application which ping server every sec for messages. Currently I have done load test with httperf, with 500 request/sec. "Connection timed out & MySQL server has gone away ...
1
vote
1answer
5k views

MySQL 5.6 OS X 10.8: how tell if my.cnf read?

With mysqld running, I expect to see that /etc/my.cnf has been used by at least one of the commands ps aux | grep mysql | grep -v grep mysqladmin variables | grep defaults-file showing the value ...
5
votes
2answers
161 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
2answers
7k 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 ...
2
votes
1answer
111 views

Synchronizing a RDS instance with another RDS instance

I have a database on an RDS Instance (master) that needs to be synchronized to another RDS instance (slave). I created a read-replica for the first one, but that is created as read_only so I cannot do ...
2
votes
3answers
440 views

Does MySQL 5.6 support fan-in replication?

I've known for quite awhile that Continuent's Tungsten Replicator supports fan-in replication (example). By fan-in replication I mean combining multiple datasources into a single instance (like a ...
1
vote
2answers
3k views

Mysql Server 5.6 Mysqld high memory consumption

I have MySQL Server 5.6 installed in a development machine with these specs: RAM: 4 GB Windows 7 Professional 64 bits Intel Pentium CPU G630 @ 2.70 GHZ Since I installed the MySQL Server, the ...
1
vote
1answer
277 views

optimize the query with big and low cardinality

I have big enough dataset and few joins to get them all with help next query: SELECT DISTINCT c.c_id FROM c_active z1 INNER JOIN cs c ON (z1.cv_id=c.cv_id) INNER JOIN indi i ON (c.m_id=i.m_id) INNER ...
0
votes
1answer
108 views

How can I move a MySQL database from 5.1 to 5.6?

I have pretty big database, like with 5 000 000 rows in total. It is located in a remote server. I want to copy this database to my personal computer, for testing purposes. However I don't know the ...
3
votes
1answer
824 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) ...
2
votes
1answer
47 views

MySQL users not visible after import

I have a Percona MySQL database 5.6 running in a linux machine. I have created new users called test1 and test2. What I did was I imported mysql database from my test setup to this new setup. After ...
2
votes
3answers
592 views

Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB

I have a question in regards of what potential issues that might happen with a MySQL 5.1 DB that replicates from a MySQL 5.6 DB. I know the risks involved based on resources such as ...
2
votes
1answer
63 views

Change column without interrupting service

We need to change the data type of a column in a large table (~1.7 GiB) from VARCHAR(1024) to VARCHAR(512) in order to be able to create a secondary index over two columns. We're using MySQL 5.6 with ...
2
votes
1answer
648 views

InnoDB: “Only one log file found” after binary import

My first one on DBA... After successful upgrading a master MySQL server 5.5 to 5.6.17 (CentOS 6 VPS serer), I done and moved my binary backups to a slave server (same version 5.6.17 but different ...
2
votes
1answer
484 views

Issues converting MyISAM table to InnoDB (auto column issue)

I'm having issues trying to convert a table from MyISAM to InnoDB in MySQL 5.6. The following is the table dump: -- -- Table structure for table `companies` -- DROP TABLE IF EXISTS `companies`; ...
1
vote
1answer
31 views

mysql logical ranking based on score

I have a table like this that keeps a total score of a history exam for students. +----+---------------+-------+---------+- | id | name | history | rank | ...
1
vote
0answers
273 views

Records not getting replicate from Mysql 5.5.32 to MySQL 5.6

I have a MySQL 5.5.32 server (community edition) on one Linux server which is master. I have a MySQL 5.6 server (community edition) on another Linux server which is slave. I have installed various ...
1
vote
1answer
633 views

How to select max value faster from large size table

I have query SELECT max(ParameterID) FROM modelparam_ems Table Name: modelparam_ems No Of Rows: 9000 It takes time to retrieve max value of parameterId, ParameterId in Unique value. Output Time ...
1
vote
1answer
95 views

Unknown sticky Problem after MySQL upgrade

few weeks ago we upgraded mysql server from 5.5 to 5.6 on 1st server few days ago (5May) I reboot mysql server , after this customer reported that some database data table not load to their site . ...
1
vote
0answers
333 views

mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?

I was reading this document: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html It says when upgrading from mysql 5.1 to do a mysqldump and then a reload. Is there anyway ...
1
vote
0answers
3k views

mysql 5.6 GTID replication 'Got fatal error 1236 from master when reading data from binary log'

I Checked SHOW SLAVE STATUS error is "'Got fatal error 1236 from master when reading data from binary log: ''The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master ...
1
vote
1answer
1k views

Data lost in MySQL InnoDB after crash

I found a 6-days rollback in a MySQL 5.6 community edition server installation. It seems that some logs were lost, and I cannot understand why. 2013-12-22 20:38:54 380 [Note] Plugin 'FEDERATED' is ...
0
votes
0answers
40 views

manual data migration/rescue MySQL version: 5.6.23

MySQL died and could not be started. So I transferred my database files via rsync to another server and restore from back up. Now I am trying to access my data with my updated records. mysql> use ...
0
votes
1answer
226 views

Why is setting open_files_limit making MySQL slow?

We noticed that adding open_files_limit in /etc/my.cnf is making server slow. Reference : What variable(s) to look to maintain MySQL speed of scanning through all database tables while it starts?
0
votes
2answers
267 views

Converting MySQL 5.6.10 - 32 bit to 64 bit version

I am currently using MySQL DB Version 5.6.10 for 32 bit running in 64 bit Window OS.I want to change it to 64 bit without taking any dump..Can anyone help in this?
0
votes
1answer
711 views

MySQL Read Speed and Partitioning on Separate Drives

Introduction Simple project but for an heavy read load -90% reads- over a table(~20Milion Rows ever-growing where I have to boost performance and ensure response low time for read queries) that was ...