Questions tagged [mysql-5.6]

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

Filter by
Sorted by
Tagged with
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 ...
43
votes
2answers
96k 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
55k 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 ...
23
votes
3answers
42k 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....
6
votes
1answer
11k 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, ...
8
votes
3answers
8k 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 ...
10
votes
2answers
550 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: ...
2
votes
1answer
915 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 ...
2
votes
1answer
794 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 ...
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
707 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
160 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
101k 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 ...
28
votes
2answers
150k 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 ...
22
votes
2answers
25k 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 ...
7
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 ...
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:\...
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
45k 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. ...
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
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
3answers
538 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
2answers
5k 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
450 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 ...
0
votes
1answer
148 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 ...
6
votes
2answers
246 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. ...
5
votes
2answers
8k views

MySQL create index with LOCK=NONE still locks a table

I have the following MySQL RoR Migrations: class ReindexRpushNotification < ActiveRecord::Migration def up execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;") ...
4
votes
1answer
1k 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 ...
3
votes
2answers
4k 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 ...
2
votes
2answers
2k views

Why are some views listed as Materialized in MySQL explain statements if Materialized views don't exist in MySQL?

Hopefully the question is self explanatory. I've read that Materialized views do not exist in MySQL, yet sometimes I see something like this in EXPLAIN Can somebody clear this up for me? What's ...