After enabling TokuDB got error failed to execute: SELECT CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME),DATA_FREE

[iprok]

Hello!

I've converted some of my tables to TokuDB from InnoDB and mysqltunner gives me the following error now:
[!!] failed to execute: SELECT CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME),DATA_FREE FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema', 'mysql') AND DATA_LENGTH/1024/1024>100 AND DATA_FREE*100/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE) > 10 AND NOT ENGINE='MEMORY'
[!!] FAIL Execute SQL / return code: 256

If I run the query manually I get:
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(information_schema.TABLES.DATA_FREE * 100)'

[jmrenouard]

This is a fun bug :)

can you give me more details ?
Mysql/mariadb version ?
select * from information_schema.TABLES;

[iprok]

Mariadb version 10.1.22 running on Debian Jessie. The same was with 10.0.* (last) version. It has begun after converting one database to TokuDB.

select * from information_schema.TABLES;
gives me
589 rows in set
with some ammount of private data. Can you refine your request?

[iprok]

I tried to debug the issue myself. If I run
SELECT CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME),DATA_FREE FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema', 'mysql') AND DATA_LENGTH/1024/1024>100 AND NOT ENGINE='MEMORY';
I get

+-----------------------------------------------+----------------------+
| CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME) | DATA_FREE |
+-----------------------------------------------+----------------------+
| help.ost_session | 7340032 |
| piwik.piwik_log_link_visit_action | 18446744068448434636 |
| piwik.piwik_log_visit | 18446744073169356366 |
+-----------------------------------------------+----------------------+
3 rows in set (0.02 sec)

The piwik.* tables are the ones I've converted from InnoDB to TokuDB. We have highloaded piwik (visitor analytics platform) instance.

[iprok]

May be the link helps: https://dba.stackexchange.com/questions/99280/can-anyone-explain-tokudbs-seemingly-large-amount-of-data-free-for-an-append-onl#103392

[jmrenouard]

Hi I have explore tokutek information and tokuDb is not very active since around 3 years.

Have a look at : https://github.com/percona/tokudb-engine

I think rely on tokudb engine is not the most clear and evident solution.
This engine is a promising and cool technologies but is not maintain, so.

I don't know what Mt can do for this issue if no more bug fix are performed on this engine.

I propose to close this issue because I honestly do know what to do to fix that.

[jmrenouard]

Hi @iprok

Can we close this issue as long as we can rely on Tokudb status information ?

[iprok]

Hello, @jmrenouard !

Yes, sure. TokuDB is dead.

[jmrenouard]

Hi @iprok

In my opinion, we can close this ticket.

Do you share this idea ?

[CompPhy]

I just ran into this same issue on the latest 1.7.20 version of mysqltuner, with MySQL 5.7 release. It seems there was never any change made to at exclude TokuDB and get past this error. I realize that it's been abandoned, but for large databases it can take a long time to get migrated over to another engine.

Can we at least get a query that obeys order of operations? And, maybe an optional argument to "exclude" TokuDB from checks?

Both of these queries fail, the first one being the one mysqltuner is using in 1.7.20.

SELECT CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME),DATA_FREE FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema', 'mysql') AND DATA_LENGTH/1024/1024>100 AND DATA_FREE*100/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE) > 10 AND NOT ENGINE='MEMORY'

SELECT CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME),DATA_FREE FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema', 'mysql') AND DATA_LENGTH/1024/1024>100 AND DATA_FREE*100/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE) > 10 AND NOT ENGINE='MEMORY' AND NOT ENGINE=‘TokuDB’

This query works, because it does proper order of operations and excludes the tables before doing math calculations.

SELECT CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME),DATA_FREE FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema', 'mysql') AND NOT ENGINE='MEMORY' AND NOT ENGINE='TokuDB' AND DATA_LENGTH/1024/1024>100 AND DATA_FREE*100/(DATA_LENGTH+INDEX_LENGTH+DATA_FREE) > 10;