Page MenuHomePhabricator

table_type is removed in MySQL 5.5.3
Closed, ResolvedPublic

Description

https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_table_type

This variable was removed in MySQL 5.5.3. Use storage_engine instead.

https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_storage_engine

This variable is deprecated as of MySQL 5.5.3. Use default_storage_engine instead.

This variable was added in MySQL 5.5.3 to be used in preference to storage_engine, which is now deprecated.

From DatabaseMysqlBase.php:1147:

$result = $this->query( 'SHOW FULL TABLES WHERE TABLE_TYPE = "VIEW"' );


Version: unspecified
Severity: normal

Event Timeline

bzimport raised the priority of this task from to High.Nov 22 2014, 3:57 AM
bzimport set Reference to bz71566.
bzimport added a subscriber: Unknown Object (MLST).
Reedy created this task.Oct 2 2014, 7:02 PM
Reedy added a comment.Oct 2 2014, 7:15 PM

I fixed WikimediaMaintenance addWiki in https://gerrit.wikimedia.org/r/#/c/164405/

Reedy added a comment.Oct 2 2014, 7:16 PM

We need to vary on MySQL version to whether we use TABLE_TYPE or default_storage_engine

Change 164405 had a related patch set uploaded by Legoktm:
table_type -> default_storage_engine

https://gerrit.wikimedia.org/r/164405

Reedy added a comment.Oct 3 2014, 11:16 AM

(In reply to Sam Reed (reedy) from comment #2)

We need to vary on MySQL version to whether we use TABLE_TYPE or
default_storage_engine

Unless we want to bump minimum version of MySQL. Doesn't seem a good idea to me for what is only used in core with mysql views. For WikimediaMaintenance, the clue is in the name. Switching the hardcoding is fine as we are using newer mysql

Change 164405 merged by jenkins-bot:
table_type -> default_storage_engine

https://gerrit.wikimedia.org/r/164405

Reedy added a comment.Oct 8 2014, 10:58 AM

Core still needs fixing

Created attachment 16827
a patch based on getServerVersion()

I wonder if something like the attached would work well enough.

I'm new here, and I'm sorry to say I'm not sure how to test this.

attachment bug71566.patch ignored as obsolete

Created attachment 16828
a far better patch based on getServerVersion()

something like this should actually work for now.

maybe better to query "show variables' and check for default_storage_engine or table_type.

attachment bug71566-1.patch ignored as obsolete

Reedy added a comment.Oct 21 2014, 4:04 PM

You're creating duplicate branches that are probably un-necessary....

Version parsing can be a PITA... Ideally it just needs something like

if version >= 5.5.3
default_storage_engine
else
table_type

Reedy added a comment.Oct 21 2014, 4:14 PM

You probably want to be using version_compare()

http://php.net/manual/en/function.version-compare.php

Created attachment 16832
a still better patch based on getServerVersion() and version_compare()

thanks for the feedback, and the tip on version_compare()!

attachment bug71566-2.patch ignored as obsolete

Reedy added a comment.Oct 21 2014, 8:30 PM

You can simplify the regex using something along the lines of

\d+\.\d+\.\d+

There might be some letter suffixes, but I suspect these don't make much of a difference

but the logic seems about right

var_dump( version_compare( '10.0.11', '5.5.3', '>=' ) );

bool(true)

var_dump( version_compare( '5.5.3', '5.5.3', '>=' ) );

bool(true)

var_dump( version_compare( '5.5.2', '5.5.3', '>=' ) );

bool(false)

Created attachment 16836
an even better patch based on getServerVersion() and version_compare() with simplified regex

here you go...

thanks for the \d reminder!

attachment bug71566-3.patch ignored as obsolete

Created attachment 16837
a still better patch, now with additional correct line endings!

Attached:

Thanks for your patch!
You are welcome to use Developer access

https://www.mediawiki.org/wiki/Developer_access

to submit this as a Git branch directly into Gerrit:

https://www.mediawiki.org/wiki/Git/Tutorial

Putting your branch in Git makes it easier to review it quickly. If you don't want to set up Git/Gerrit, you can also use https://tools.wmflabs.org/gerrit-patch-uploader/

(In reply to B. Miller from comment #14)

Created attachment 16837 [details]
a still better patch, now with additional correct line endings!

mysql> use testwiki;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT VERSION()\G

  • 1. row *******

VERSION(): 5.5.40-0ubuntu0.14.04.1
1 row in set (0.00 sec)

mysql> SHOW FULL TABLES WHERE TABLE_TYPE = "VIEW"\G
Empty set (0.00 sec)

mysql> SHOW FULL TABLES WHERE DEFAULT_STORAGE_ENGINE = "VIEW"\G
ERROR 1054 (42S22): Unknown column 'DEFAULT_STORAGE_ENGINE' in 'where clause'

Attached:

I've been wondering whether the TABLE_TYPE in SHOW FULL TABLES was the old MySQL system variable.

Reedy added a comment.Oct 22 2014, 7:55 PM

Same named things that do different things?

What's the current status on this? Still needs fixing in core?

Ybura added a subscriber: Ybura.Jun 1 2015, 6:58 PM
Restricted Application added a subscriber: TerraCodes. · View Herald TranscriptOct 16 2016, 11:48 PM
Reedy added a comment.May 9 2017, 7:23 PM
extensions/WikimediaMaintenance  (1 usage found)
    renameWiki.php  (1 usage found)
        91$extdb->query( "SET table_type=InnoDB" );

If we fix that one, we can close this bug off...

Change 352885 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/extensions/WikimediaMaintenance@master] table_type -> default_storage_engine

https://gerrit.wikimedia.org/r/352885

Change 352885 merged by jenkins-bot:
[mediawiki/extensions/WikimediaMaintenance@master] table_type -> default_storage_engine

https://gerrit.wikimedia.org/r/352885

Reedy closed this task as Resolved.May 9 2017, 7:30 PM
Reedy claimed this task.
Reedy removed a project: Patch-For-Review.