Bug #47973 innodb_data_file_path truncated on show variables
Submitted: 10 Oct 2009 22:40 Modified: 13 Jul 2010 18:58
Reporter: Andy Tomasello Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.0.86 OS:Linux
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any
Tags: regression

[10 Oct 2009 22:40] Andy Tomasello 
Description:
The innodb_data_file_path is truncated from actual when running a "show variables" in the mysql client. This is a problem for some backup software which uses this to find which files to back up.

This seemed to have been patched in 5.0.30 per bug #20862, but seems to have popped back in at least 5.0.45 which I tested as well as 5.0.86 (latest).

How to repeat:
The innodb data file path from the my.cnf file shows this:

#grep innodb_data_file /etc/my.cnf

innodb_data_file_path=ibdata1:2G;ibdata2:2G;ibdata3:2G;ibdata4:2G;ibdata5:2G;ibdata6:2G;ibdata7:2G;ibdata8:2G;ibdata9:2G;ibdata10:2G;ibdata11:2G;ibdata12:2G;ibdata13:2G;ibdata14:2G;ibdata15:2G;ibdata16:2G;ibdata17:2G;ibdata18:2G;ibdata19:2G;ibdata20:2G;ibdata21:2G;ibdata22:2G;ibdata23:2G;ibdata24:2G;ibdata25:2G;ibdata26:2G;ibdata27:2G;ibdata28:2G;ibdata29:2G;ibdata30:2G;ibdata31:2G;ibdata32:2G;ibdata33:2G;ibdata34:2G;ibdata35:2G;ibdata36:2G;ibdata37:2G;ibdata38:2G;ibdata39:2G;ibdata40:2G;ibdata41:2G;ibdata42:2G;ibdata43:2G;ibdata44:2G;ibdata45:2G;ibdata46:2G;ibdata47:2G;ibdata48:2G;ibdata49:2G;ibdata50:2G;ibdata51:2G;ibdata52:2G;ibdata53:2G;ibdata54:2G;ibdata55:2G;ibdata56:2G;ibdata57:2G;ibdata58:2G;ibdata59:2G;ibdata60:2G;ibdata61:2G;ibdata62:2G

Running a show variables shows this:

mysql> show variables like "innodb_data%";
+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name         | Value                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| innodb_data_file_path | ibdata1:2G;ibdata2:2G;ibdata3:2G;ibdata4:2G;ibdata5:2G;ibdata6:2G;ibdata7:2G;ibdata8:2G;ibdata9:2G;ibdata10:2G;ibdata11:2G;ibdata12:2G;ibdata13:2G;ibdata14:2G;ibdata15:2G;ibdata16:2G;ibdata17:2G;ibdata18:2G;ibdata19:2G;ibdata20:2G;ibdata21:2G;ibdata22:2G;ibdata23:2G;ibdata24:2G;ibdata25:2G;ibdata26:2G;ibdata27:2G;ibdata28:2G;ibdata29:2G;ibdata30:2G;ibdata31:2G;ibdata32:2G;ibdata33:2G;ibdata34:2G;ibdata35:2G;ibdata36:2G;ibdata37:2G;ibdata38:2G;ibdata39:2G;ibdata40:2G;ibdata41:2G;ibdata42:2G;ibdata43:2G;ibdat | 
| innodb_data_home_dir  | /var/lib/mysql/                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | 
+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Suggested fix:
Reapply bugfix from earlier version.
[10 Oct 2009 22:45] Andy Tomasello 
The bug was tested by me as not occurring in 5.1.26.
[11 Oct 2009 7:11] Sveta Smirnova 
Thank you for the report.

Verified as described.
[26 Jan 2010 6:24] Sveta Smirnova 
Bug #50608 was marked as duplicate of this one.
[26 Jan 2010 9:21] Robin Bowes 
The following patch fixes this:

--- sql/sql_show.cc.orig        2010-01-26 00:03:32.000000000 +0000
+++ sql/sql_show.cc     2010-01-26 00:05:36.000000000 +0000
@@ -4443,7 +4443,7 @@
 ST_FIELD_INFO variables_fields_info[]=
 {
   {"Variable_name", 80, MYSQL_TYPE_STRING, 0, 0, "Variable_name"},
-  {"Value", FN_REFLEN, MYSQL_TYPE_STRING, 0, 0, "Value"},
+  {"Value", 65535, MYSQL_TYPE_STRING, 0, 0, "Value"},
   {0, 0, MYSQL_TYPE_STRING, 0, 0, 0}
 };
[26 Jan 2010 9:22] Robin Bowes 
The previous patch was against v5.0.87.
[15 Jun 2010 14:13] Tatiana Azundris Nuernberg 
Not fixing in 5.0.

Suggesting docs update to the effect of, "SHOW VARIABLES is subject to a version-dependent limit in display-width; to get the entirety of a given value, use SELECT ('SELECT @@global.innodb_data_file_path;') in 5.1 and better."

thanks, T
[13 Jul 2010 18:58] Paul DuBois 
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.
[13 Jul 2010 21:36] Robin Bowes 
Can I suggest you amend the wording of your documentation change to be more explicit about the problem here?

The query "show variables like 'innodb_data_file_path'" is often used to get a list of ibdata files for backup purposes. If this issue has not been fixed (eg. my patch) then the list of ibdata files will be incomplete (the var is silently truncated) and THE BACKUP WILL BE CORRUPT.

How about something like:

The output from SHOW VARIABLES may be truncated if it exceeds a version-dependent internal length limit. For example, if you have a lot of InnoDB data files then "SHOW VARIABLES LIKE 'innodb_data_file_path'" may *not* return the complete list of files.