INSERT INTO... SELECT * FROM not copying data correctly

Question

I'm trying to:

• create an empty copy of a table
• insert all the data from the original to the copy

query I'm using is:

CREATE TABLE my_schema.account_copy_like LIKE my_schema.account; -- To inherit all table definitions
INSERT INTO my_schema.account_copy_like SELECT * FROM my_schema.account; -- Copying data from another table

The issue here is that the INSERT INTO... SLEECT * FROM is not copying as expected, there are some discrepancies on the table information as you can compare on the table size and rows, I was wondering why. It seems that in the copy table it has more rows than the original but it's size is lower?

Original table

copy table

• Akina: Why you do not use solid CREATE TABLE my_schema.account_copy_like SELECT * FROM my_schema.account;?
• Akina: there are some discrepancies on the table information as you can compare on the table size and rows The fact that a copy contains more rows than the source cannot be explained. Looks like some concurrent process which inserts into this table exists. Are you sure that the table did not exist during CREATE TABLE, and its creation succeed?
• Retrosec6: @Akina CREATE TABLE my_schema.account_copy_like SELECT * FROM my_schema.account; just inherits basic columns definitions, but will not inherit indexes and auto_increments, if you would like clone all definitions you should use the LIKE approach

Answer 1

• The copy has not finished yet, so the numbers are not yet updated.
• The source of some things, in particular, "Table rows", is an estimate, so it may never match exactly. (Do not let this concern you.)
• Do SELECT COUNT(*) FROM account_copy_like; to give yourself more confidence that all the data is there. Or SELECT SUM(nnn) ... where nnn is some numeric column.

• Retrosec6: Thanks, last bullet seemed to gave me the result I was seeking. Apparently the COUNT were the same for both original and copy tables, I was confused by the Mysql Workbench table inspector UI values, which now I suppose that is not very thrust worthy.
• Rick James: Any UI is going to simply run some queries against the database. SHOW TABLE STATUS is notoriously inaccurate; your experiment hit multiple problems with that statement.
• Hybris95: Basically a SHOW TABLE STATUS will give you the result of the statistics. It is highly not recommended to use those values for any integrity check as those values will be inaccurate and depend on how the statistics are refreshed.