Mysql - Stored procedure not running in

Question

I need to count the exact row count of the tables in my DB (not using TABLE_ROWS from INFORMATION_SCHEMA) I am trying to execute the the script from this post

SET @tableSchema = 'Db_Warehouse';
SET SESSION group_concat_max_len = 10000000;
SET @rowCounts = (
  SELECT GROUP_CONCAT(CONCAT('SELECT ''',TABLE_NAME,''', COUNT(*) FROM ', TABLE_NAME) SEPARATOR ' union all ')
  FROM information_schema.tables WHERE table_schema = @tableSchema
);
PREPARE statement FROM @rowCounts;
EXECUTE statement;

But I keep getting Error Code 1064:

<e>Query: PREPARE statement FROM @rowCounts

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table union all SELECT 'COX_ClientSatisfaction', COUNT(*) FROM COX_ClientSatisfa' at line 1

Hope you can advise.

Do you have a table literally named `table`? You should modify your CONCAT() to put back-ticks around identifiers. — Bill Karwin, Oct 12 '17 at 17:40
I also have to comment, it looks like you're just trying to get the number of rows in all your tables. Would `SHOW TABLE STATUS` not work? Or its equivalent `SELECT TABLE_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES...` I know those are only *estimates* of the table rows, but they're usually close, and it would run many times faster than a COUNT(*) on every table. — Bill Karwin, Oct 12 '17 at 17:41
Hi @BillKarwin thank you for the advice - I did run the SELECT TABLE_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES... but I need this to determine whether a data migration was successful and since this is an estimate the numbers are different between the two DB's — A.Lot, Oct 12 '17 at 17:49
If you're comparing two databases you might like to use [mysqldbcompare](https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqldbcompare.html) instead of investing time into writing your own? That tool is free, it compares table definitions, row counts, and even data. There are lots of options to customize what it does, too. — Bill Karwin, Oct 12 '17 at 17:56

score 0 · Answer 1 · answered Oct 12 '17 at 18:14

Summary from comments above: the task to check if table row counts are the same in a cloned database is better accomplished by using the existing tool mysqldbcompare.

The cause of the error in the procedure is a syntax error on the MySQL reserved word table.

When designing an automatic query generator, it's best if you use delimited identifiers as described in the manual under Schema Object Names.

For example, this is a safer way to write your query:

SELECT GROUP_CONCAT(
  CONCAT(
    'SELECT ', QUOTE(TABLE_NAME), ', COUNT(*) FROM `', TABLE_NAME, '`')
  SEPARATOR ' UNION ALL ')
FROM information_schema.tables WHERE table_schema = @tableSchema

Mysql - Stored procedure not running in

1 Answers1