Questions tagged [mysql-5.6]
MySQL version 5.6 - please also tag with mysql for search purposes.
71
questions
27
votes
3answers
37k 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 ...
42
votes
2answers
95k 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.
http://forums.mysql.com/read.php?22,...
3
votes
1answer
9k 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 ...
19
votes
5answers
72k 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 ...
6
votes
1answer
13k 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 ...
10
votes
3answers
54k views
Restoring MySQL Tables from .ibd, .frm and mysqllogbin files
For some reason, when I try to open up my tables that are stored in .frm and .ibd files (whether on MySQL or phpmyadmin) it gives me a syntax error, or it says it does not exist.
I've read the other ...
1
vote
2answers
1k 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 ...
22
votes
3answers
41k 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 ...
12
votes
3answers
3k 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, ...
3
votes
2answers
2k views
MySQL 5.6 showing wrong “second_behind_master”
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.7.98
...
4
votes
2answers
3k 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 ...
2
votes
3answers
1k 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 http://dev....
8
votes
3answers
7k views
MySql Gap Lock Deadlock on Inserts
I'm getting Deadlocks from gap locks on a table when inserting into it frequently from multiple sources. Here is an overview of my processes.
START TRANSACTION
UPDATE vehicle_image
SET active = 0
...
7
votes
3answers
54k 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 running ...
9
votes
2answers
537 views
MySQL - maximum of sum over different months with ties over multiple years
This question was inspired by this one [closed] and is virtually identical to this one but using different RDBMS's (PostgreSQL vs. MySQL).
Suppose I have a list of tumours (this data is simulated ...
4
votes
1answer
4k views
How to get row count from all tables of a schema ( without using information schema )
The query should collect data from table name , schema name from information.schema and row count should be taken from actual table.
2
votes
1answer
2k 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 ...
0
votes
1answer
5k 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 5.5 ...
0
votes
1answer
2k views
Mysql Crash SIGSEGV signal 11, recovered after killing apport
My mysql instance (ver 5.6.33) on AWS ( r3.4xlarge CPU Cores : 16 , RAM : 120 GB
, filesystem : XFS ) is crashing with signal 11 .
The instance is EBS optimised with EBS for storage.
At around 9:04 ...
12
votes
3answers
14k 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 this: ...
5
votes
1answer
10k views
MySQL settings useful to speed up a mysqldump import
Recently I had to import a 7 Gb MySQL dump file to a MySQL 5.6 server. The import took around 7 hours on a mono-core CPU with 1 Gb of RAM.
Someone else tested the import on a MySQL server which has, ...
2
votes
1answer
790 views
Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore?
I inherited a DB using myisam on mysql 5.1 because of the need for fulltext search. I now read that fulltext search is available on mysql 5.6 innodb. Doing this would solve a lot of problems, as long ...
2
votes
1answer
893 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
2answers
1k views
Restoring XtraBackup from MySQL 5.6 to MySQL 5.7
We are running MySQL 5.6 and taking backups using XtraBackup 2.4. We want to migrate to MySQL 5.7 using that same backup as we have datasets well over 600GB.
Is it possible to do so and what are ...
0
votes
1answer
703 views
Mysql Percona 5.6 slave lags gradually
I have got into slave lag gradually say every minute it lags around 5secs on slave.
So it started morning 10AM the Seconds_behind_master was 13. Now at 4:30 PM it has reached to 560.
No variable ...
-1
votes
1answer
149 views
Reorder / Fill the missing numbers in a sequence using MySQL
I have a table "number" having a column "n" which is of type int. I inserted some values like,
n
--
1
3
5
8
10
Now I want to the column n as 1,2,3,4,5. That is 3 should be changed as 2, 5 as 3, ...
40
votes
6answers
98k 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 ...
20
votes
2answers
24k views
MySQL - Difference between using count(*) and information_schema.tables for counting rows
I want a fast way to count the number of rows in my table that has several million rows. I found the post "MySQL: Fastest way to count number of rows" on Stack Overflow, which looked like it ...
6
votes
2answers
9k views
Alter large table without downtime
I have a very large table, lets call it example, and I try to execute a little alter command on that table:
ALTER TABLE `example` ADD `example_column` TINYINT(1) NOT NULL DEFAULT 0;
Checking the ...
8
votes
1answer
23k 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)...
7
votes
2answers
21k views
Set sql_mode “blank” after upgrading to MySQL 5.6
After upgrading MySQL from 5.5 to 5.6, some our app face to problem and need set sql_mode to blank to solve this issue. I added sql_mode = '' to my.cnf but there was no effect on the mysql setting.
...
10
votes
3answers
44k views
mysqldump --single-transaction, yet update queries are waiting for the backup
If I use mysqldump --single-transaction, according to the docs it should do flush tables with read lock to get a consistent state and then start a transaction and no writers should be waiting.
...
10
votes
2answers
21k views
Changing Data Directory on MySQL DB Server
I have installed mysql server 5.6.24 on Windows Server 2012 R2.
I imported dump files and the data files are stored in C:\ProgramData\MySQL\MySQL Server 5.6\data
I want to change directory like D:\...
1
vote
2answers
6k 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 ...
0
votes
1answer
1k 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 ...
6
votes
2answers
1k 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 ...
3
votes
1answer
4k views
How to make a FULLTEXT search with ORDER BY fast?
I'm trying to get a simple FULLTEXT match to be faster when using order by on another column on a table with over 100 million rows. The basis is one table with a fulltext on two columns and I want to ...
2
votes
3answers
537 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 ...
2
votes
1answer
2k 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 ...
2
votes
2answers
4k views
Importing CSV's into MySQL(5.6) tables leaves date fields as 0000-00-00
During the import from a CSV file created from an MS Access table into a MySQL table it converts all date fields in the csv into 0000-00-00. I use the following Load Data command for the import.
...
1
vote
1answer
2k 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
...
0
votes
1answer
137 views
MySQL Internals: SQL Execution Flow [closed]
This is a descriptive question regarding MySQL SQL flow architecture. I need a flow chart on the complete steps regarding MySQL SQL flow using the following components.
eg the sequence an UPDATE query ...
0
votes
1answer
445 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 ...
6
votes
2answers
241 views
Issues after schemas migration from mysql 4 to mysql 5.6
Recently I've migrated some schemas from mysql 4 instance into an instance of mysql 5.6 by mysqldump and import operations. After some syntax adjustments, I could import the dump file successfully.
...
4
votes
1answer
982 views
Concurrent UPDATE deadlock (updating 2 fields, selecting by PK)
I have a strange deadlock case, where simple concurrent updates are causing deadlocks on MySQL (5.6.27).
The setup is with multiple workers (several different servers) processing queues and sometimes ...
4
votes
3answers
1k views
In replication A->B->C, can A be binlog_format=STATEMENT and B be binlog_format=ROW?
I have replication setup between an on-prem MySQL 5.6 server (A) to another on-prem MySQL 5.6 server (B). I was setting up an AWS DMS instance (C) that I wanted to migrate and then replicate. It ...
3
votes
1answer
3k 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 ...
2
votes
1answer
105 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
0answers
4k 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 ...
2
votes
1answer
3k views
Mysql Server 5.6 high memory consumption
I have a problem with my mySQL server. It consumes 9.7 GB ram. I have only a very limited amount of data on it (13 mb)
I have been searching the internet and seval places a solution like this one
...
