MySQL Bugs: #70921: INSERT SELECT from information

Bug #70921	INSERT SELECT from information_schema sometimes returns nothing
Submitted:	15 Nov 2013 15:09	Modified:	19 Nov 2015 16:43
Reporter:	martin fuxa	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.6.14	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
see How to repeat
with few existing databases sometimes insert return "0 rows affected", when I expect inserting all existing rows. see below.
Why INSERT result is not deterministic? With SELECT is not problem (fine).
I can't find any reason in DOC.
Databases exists still the same.
same with engine=myisam for inserted table.

INSERT ...
Query OK, 0 rows affected (0.28 sec)

SELECT ... 
68 rows in set (1.11 sec)

INSERT ...
Query OK, 0 rows affected (0.28 sec)

SELECT
68 rows in set (1.17 sec)

INSERT
Query OK, 68 rows affected (0.94 sec)
Records: 68  Duplicates: 0  Warnings: 0

someone else have same experience
http://stackoverflow.com/questions/17286674/unable-to-do-an-insert-into-using-a-select-whe...

How to repeat:
USE test;
CREATE TABLE db_du_tmp (
 `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
 size_mb int unsigned not null,
 PRIMARY KEY (Db) ) engine=innodb;

and run few times
INSERT db_du_tmp
    SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL; SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL;

Thank you for the report.

Please describe failing scenario in more details: if follow your instructions second attempt fails with "ERROR 1062 (23000): Duplicate entry 'information_schema' for key 'PRIMARY'", thus inserts 0 rows which is expected behavior. Please describe what is wrong.

Hi Sveta,
my result isn't ERROR 1062.
If it's your case, well PRIMARY KEY = Db
- you have more database with name information_schema on your test instance?
- some previous INSERT allready inserted dat? fine after first successful insert is test gone. but better will be do ... before each INSERT.
TRUNCATE TABLE db_du_tmp;
- you got "0 rows affected" after INSERT and second end up with duplicate PK?
	
problem is that after few times "INSERT ... SELECT " returns "0 rows affected" suddenly is successful with "68 rows affected".

hmm, I try it on some other instances and works well. I send my conf. Maybe it does matter.

on last tested CentOS 5.x and 6.x with 5.6.14 and 5.6.13 works well at the first attempt (with different conf and full mix of storage, RAID 1 or 10, SAS or SSD ...).

problem is only (in my case) on Win Server 2008 R2 ENT with 5.6.14 (storage SSD Intel DC S3700 on HW RAID 1, Adaptive Read Ahead, Write Back)
conf attached
grep -vE "^#" my.ini > ~/tmp/1005.log

my.ini

Attachment: 1005.log (text/x-log), 1.42 KiB.

Thank you for the feedback.

In "how-to-repeat" instruction you wrote:

----<q>----
USE test;
CREATE TABLE db_du_tmp (
 `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
 size_mb int unsigned not null,
 PRIMARY KEY (Db) ) engine=innodb;

and run few times
INSERT db_du_tmp
    SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL; SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL;

----</q>----

This is exactly what I do:

INSERT db_du_tmp
    SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL; SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL;
INSERT db_du_tmp
    SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL; SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL;
INSERT db_du_tmp
    SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL; SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL;
INSERT db_du_tmp
    SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL; SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL;
INSERT db_du_tmp
    SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL; SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL;
....

And it is certain what you run some other test. Please send us step-by-step instructions of what you are doing. Do you call TRUNCATE or DROP TABLE between INSERTs?

maybe I wasn't clear, I'll try it again.
Problem is with non repeatable INSERT SELECT,
while SELECT is repeatable all times.

1. execute
SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL;

you got few rows, eg
68 rows in set (1.17 sec)

from now we do not modify any database and data/tables.
Than I expect all same SELECT's return same row count. This is true for SELECT.
But is not true for SELECT INSERT, and this is imho bug.

2. execute few times
USE test;
TRUNCATE TABLE db_du_tmp; INSERT db_du_tmp
    SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb
    FROM information_schema.tables GROUP BY TABLE_SCHEMA HAVING size_mb IS NOT NULL;

expected result (for INSERT operation) is 
Query OK, 68 rows affected (0.94 sec)
Records: 68  Duplicates: 0  Warnings: 0

but I got sometimes incorect zero rows
Query OK, 0 rows affected (0.28 sec)

For example, I got now
ok = Query OK, 68 rows affected (0.94 sec)
KO = Query OK, 0 rows affected (0.68 sec)
ok
KO = Query OK, 0 rows affected (0.68 sec)
ok
ok
KO = Query OK, 0 rows affected (0.68 sec)
ok
ok
ok
KO = Query OK, 0 rows affected (0.68 sec)

As I wrote at 18 Nov 9:13, problem exists only on my one Win Server box, all other Linux boxs works fine.

It's clear now?

Thank you for the feedback.

Yes, it is clear now.

I've tried the sequence on my windows box. And I indeed can't repeat it with the default database.

Please check if the number of tables/data has any impact on the repeatability of the bug and reopen the bug if you have any further insights in how to reproduce it.

I have a similar issue on MySQL 5.6.22 on RHEL6.
Insert into table select from information_schema gives empty set and sometimes gives error 1028.