innodb & MySQL 8: DYNAMIC & COMPRESSED ROW_FORMAT missing

[Samer-Al-iraqi]

To Reproduce

Steps to reproduce the behavior:

1. upgrade to MySQL 8, Go to any table,
2. press Operations
3. try to change ROW_FORMAT of that table
4. Dropdown shows only Compact and redundant formats.

Expected behavior

Dynamic and Compressed format should be available

Fix:

code of this method in Operations class

    private function getPossibleRowFormat(){
        $possible_row_formats = array(
            'ARCHIVE' => array(
                'COMPRESSED' => 'COMPRESSED',
            ),
            'ARIA'  => array(
                'FIXED'     => 'FIXED',
                'DYNAMIC'   => 'DYNAMIC',
                'PAGE'      => 'PAGE'
            ),
            'MARIA'  => array(
                'FIXED'     => 'FIXED',
                'DYNAMIC'   => 'DYNAMIC',
                'PAGE'      => 'PAGE'
            ),
            'MYISAM' => array(
                 'FIXED'    => 'FIXED',
                 'DYNAMIC'  => 'DYNAMIC'
            ),
            'PBXT'   => array(
                 'FIXED'    => 'FIXED',
                 'DYNAMIC'  => 'DYNAMIC'
            ),
            'INNODB' => array(
                 'COMPACT'  => 'COMPACT',
                 'REDUNDANT' => 'REDUNDANT',
		  'DYNAMIC' => 'DYNAMIC',
		  'COMPRESSED' => 'COMPRESSED'
            )
        );
        return $possible_row_formats;
    }

[lem9]

Thanks but this fix is incomplete; a good fix would take into account the MySQL server version. Have a look at https://dev.mysql.com/doc/refman/5.5/en/innodb-row-format-dynamic.html which mentions the Barracuda file format, and https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format-dynamic.html which does not mention Barracuda. Then have a look at the code at the end of getPossibleRowFormat() which conditionally adds DYNAMIC and COMPRESSED to the array. I have no idea what's the Innodb file format in MySQL 8.

[Samer-Al-iraqi]

sorry. I forgot about older version. However, for now, this is just a useful hack for those just upgraded to MySQL 8.

I guess the fix is at follow: first we get the MySQL version, if it is 8 and above then no need to check the file format, just list "COMPACT, REDUNDANT,DYNAMIC, COMPRESSED", otherwise the same code apply.

$version= a_function_to_get_mysql_version(); // e.g. SELECT version() => 8.0.2
$version=explode('.', $version)[0];
if($version>=8){
	$possible_row_formats['INNODB']+=array('DYNAMIC' => 'DYNAMIC','COMPRESSED' => 'COMPRESSED');
}
else {
 // same original code to that find out the support of those two types
}

[lem9]

To get the database server version, use $GLOBALS['dbi']->getVersion() which returns the integer version like 80002 for 8.0.2. However the server is not necessarily MySQL. You might want to verify the booleans $GLOBALS['dbi']->isMariaDB() and $GLOBALS['dbi']->isPercona() but I did not check what these servers support in terms of row formats.

[pigochu]

Mariadb 10.3 also has the problem.

I think no need to check what version support . Just thown an error.

If I excute the query on a innodb table.

ALTER TABLE `test1-abc` ROW_FORMAT=FIXED

I got error

#1478 - Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE'

So I can unserstand what happend .

[OlafvdSpek]

IMO pMA shouldn't do row_format = '...' when the user didn't change the row_format.
Same for auto_increment, what happens if a row is inserted in the mean time, a race condition?

I agree with pigochu, a complicated availability check isn't necessary.

[vassil-velichkov]

Any plans to implement fix for this incompatibility with MySQL 8.x? We've just completed migration of 50+ servers and 1000+ tables from MySQL 5.7 to 8.0.14 and the actual ROW_FORMAT values are no longer correct / visible in Table->Operations.
Quick check with the following SQL statement shows that all tables are imported with the correct row formats (some with COMPACT, some with DYNAMIC, some with COMPRESSED), but all of them appear in pMA as "COMPACT":
SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES

[vassil-velichkov]

The actual compatibility issue is within the method called on line 1293 in Operations.php class:
https://github.com/phpmyadmin/phpmyadmin/blob/c36086dd693001a4d77e47d3411b1cfb740e9cd8/libraries/classes/Operations.php#L1293

/Engines/Innodb->getInnodbFileFormat() method checks for 'innodb_file_format' global variable with the following SQL statement:

SHOW GLOBAL VARIABLES LIKE 'innodb_file_format'

All file-format variables are deprecated in MySQL 5.7.7. and completely removed from all system tables & views in MySQL 8.0.0 (development milestone).
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-0.html

A check for the Innodb version is required and if the major version is >= 8, the method should return "Barracuda" for backward compatibility: "Barracuda" is still the internal maximum file format for tables with ROW_FORMAT = DYNAMIC / COMPRESSED, while the COMPACT / REDUNDANT still use the older "Antelope".

The check could be done with:
SELECT @@innodb_version;

P.S. It seems I will have to submit pull-request, because even an innocent operation like renaming a table through pMA->Table->Operations overwrites the actual ROW_FORMAT to the default COMPACT...

[latifolia]

I think since All file-format variables are deprecated, erased in newer MySQL and MariaDB, the solution proposed may be to focus on these lines in Operations.php :

   if ('Barracuda' == $innodb_file_format
        && $innodbEnginePlugin->supportsFilePerTable()
    ) {
        $possible_row_formats['INNODB']['DYNAMIC'] = 'DYNAMIC';
        $possible_row_formats['INNODB']['COMPRESSED'] = 'COMPRESSED';
    }

   /* 
  Newer MySQL/MariaDB always return empty a.k.a '' on $innodb_file_format otherwise old versions of MySQL/MariaDB must be returning something, so we  may change it into  : 
   */

   if ( (('Barracuda' == $innodb_file_format) || ('' == $innodb_file_format))
        && $innodbEnginePlugin->supportsFilePerTable()
    ) {
        $possible_row_formats['INNODB']['DYNAMIC'] = 'DYNAMIC';
        $possible_row_formats['INNODB']['COMPRESSED'] = 'COMPRESSED';
    }

I have submitted PULL Request for proposed patch above : #14979