tracking_version_auto_create = true CREATE TABLE doesn't track changes on a new table

[Sylvain303]

Describe the bug

I would like to enable Change tracking feature for all operations.

I added the following config in config.inc.php

// phpmyadmin tracking                                                                                                                
// https://docs.phpmyadmin.net/en/latest/config.html
$cfg['Servers'][$i]['tracking_version_auto_create'] = true;
$cfg['Servers'][$i]['tracking_default_statements'] = 'CREATE TABLE,ALTER TABLE,DROP TABLE,RENAME TABLE,CREATE INDEX,DROP INDEX,CREATE VIEW,ALTER VIEW,DROP VIEW,CREATE DATABASE,ALTER DATABASE,DROP DATABASE';
$cfg['Servers'][$i]['tracking_add_drop_view'] = true;
$cfg['Servers'][$i]['tracking_add_drop_table'] = true;
$cfg['Servers'][$i]['tracking_add_drop_database'] = true;

To Reproduce

1. activate tracking in config.inc.php
2. login to phpmyadmin
3. go to a database
4. Click on 'Create table '
5. Fill up the form and create the table
6. The table doesn't appear in pma_tracking nor it has tracking enabled.

Expected behavior

The new table has tracking enabled by default and the CREATE TABLE is recorded.

Server configuration

• Operating system: Debian 9 Stretch
• Web server: Apache/2.4.25 (Debian)
• Database version: Server version: 10.1.41-MariaDB-0+deb9u1 - Debian 9.9
• PHP version: Version PHP : 7.3.13-1+020191218.50+debian91.gbp23c2da
• phpMyAdmin version: phpMyAdmin-5.0.1-all-languages from download page.

If I put that at the end of config.inc.php I get:

print_r($cfg);
die();

I have

Array
(
    [blowfish_secret] => Someblowfishsecret
    [TempDir] => /var/lib/phpmyadmin/tmp
    [LoginCookieValidity] => 86400
    [Servers] => Array
        (
            [1] => Array
                (
                    [auth_type] => cookie
                    [host] => localhost
                    [compress] => 
                    [AllowNoPassword] => 
                    [controlhost] => localhost
                    [controlport] => 
                    [controluser] => phpmyadmin
                    [controlpass] => pma_db_password_here
                    [pmadb] => phpmyadmin
                    [bookmarktable] => pma__bookmark
                    [relation] => pma__relation
                    [table_info] => pma__table_info
                    [table_coords] => pma__table_coords
                    [pdf_pages] => pma__pdf_pages
                    [column_info] => pma__column_info
                    [history] => pma__history
                    [table_uiprefs] => pma__table_uiprefs
                    [tracking] => pma__tracking
                    [userconfig] => pma__userconfig
                    [recent] => pma__recent
                    [favorite] => pma__favorite
                    [users] => pma__users
                    [usergroups] => pma__usergroups
                    [navigationhiding] => pma__navigationhiding
                    [savedsearches] => pma__savedsearches
                    [central_columns] => pma__central_columns
                    [designer_settings] => pma__designer_settings
                    [export_templates] => pma__export_templates
                    [tracking_version_auto_create] => 1
                    [tracking_default_statements] => CREATE TABLE,ALTER TABLE,DROP TABLE,RENAME TABLE,CREATE INDEX,DROP INDEX,CREATE VIEW,ALTER VIEW,DROP VIEW,CREATE DATABASE,ALTER DATABASE,DROP DATABASE
                    [tracking_add_drop_view] => 1
                    [tracking_add_drop_table] => 1
                    [tracking_add_drop_database] => 1
                )

        )

    [UploadDir] => 
    [SaveDir] => 
)

Client configuration

• Browser: FireFox 74.0 (64-bit) ubuntu
• Operating system: xubuntu 18.04

Additional context

phpmyadmin is configured from ansible playbook.

[Sylvain303]

So I've read some of the code and traced some use case.

As far as I see, the test

phpmyadmin/libraries/classes/Tracker.php

Line 836 in 517222f

if (! self::isTracked($dbname, $result['tablename'])) {

Is done before

phpmyadmin/libraries/classes/Tracker.php

Line 847 in 517222f

if ($GLOBALS['cfg']['Server']['tracking_version_auto_create'] == true

So no chance that a new table will ever be tracked.

May be the pma__tracking table can be populated first.

No chance either than a CREATE DATABASE could ever be handled neither, as the test is done through:

phpmyadmin/libraries/classes/Tracker.php

Lines 152 to 155 in 517222f

	$sql_query = ' SELECT tracking_active FROM ' . self::_getTrackingTable() .
	" WHERE db_name = '" . $GLOBALS['dbi']->escapeString($dbname) . "' " .
	" AND table_name = '" . $GLOBALS['dbi']->escapeString($tablename) . "' " .
	' ORDER BY version DESC LIMIT 1';

[williamdes]

Thank you for the debug details !
If you want you can open a pull-request from and to branch QA_5_0

[Sylvain303]

hi @williamdes

I'm not a php developer and the PR will take me some time and probably some wrong php code.
I'm gonna hack a little more, I may find a gentle php dev on IRC or such.

I'm not aware enough about this functionally tracking, I tough is was mostly working, but now, I suppose it's at some early stage yet, isn't it?

The current Table format pma__tracking is not well designed for versioning as the SQL code is stored as raw text in data_sql and need extra parsing:

phpmyadmin/sql/create_tables.sql

Lines 210 to 224 in 441f8da

	CREATE TABLE IF NOT EXISTS `pma__tracking` (
	`db_name` varchar(64) NOT NULL,
	`table_name` varchar(64) NOT NULL,
	`version` int(10) unsigned NOT NULL,
	`date_created` datetime NOT NULL,
	`date_updated` datetime NOT NULL,
	`schema_snapshot` text NOT NULL,
	`schema_sql` text,
	`data_sql` longtext,
	`tracking` set('UPDATE','REPLACE','INSERT','DELETE','TRUNCATE','CREATE DATABASE','ALTER DATABASE','DROP DATABASE','CREATE TABLE','ALTER TABLE','RENAME TABLE','DROP TABLE','CREATE INDEX','DROP INDEX','CREATE VIEW','ALTER VIEW','DROP VIEW') default NULL,
	`tracking_active` int(1) unsigned NOT NULL default '1',
	PRIMARY KEY (`db_name`,`table_name`,`version`)
	)
	COMMENT='Database changes tracking for phpMyAdmin'
	DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

Store version of table change is done as text:

                $new_data_processed .= '# log ' . $date . ' ' . $data['username']

This part:

phpmyadmin/libraries/classes/Tracker.php

Lines 451 to 456 in 441f8da

	$new_data_processed = '';
	if (is_array($new_data)) {
	foreach ($new_data as $data) {
	$new_data_processed .= '# log ' . $date . ' ' . $data['username']
	. $GLOBALS['dbi']->escapeString($data['statement']) . "\n";
	}

The record pma__track.data_sql looks like:

# log 2020-03-19 15:56:03 sylvain
DROP TABLE IF EXISTS `matable`;
# log 2020-03-19 15:56:03 sylvain

CREATE TABLE `matable` (
  `id` int(11) NOT NULL,
  `criterer` varchar(50) NOT NULL,
  `valeur` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='C''est une seuper table dit donc';

# log 2020-03-19 18:50:26 sylvain
ALTER TABLE `matable` DROP `valeur`;
# log 2020-03-19 20:55:57 root
ALTER TABLE `matable`  ADD `prout` SET('Pipo','molo') NOT NULL  AFTER `criterer`;

It is then spited programmatically, which seems unnecessary to me, because we obviously have access to a database, and all that code handling tracking should be replaced by a DB relational model.

phpmyadmin/libraries/classes/Tracker.php

Lines 575 to 605 in 441f8da

	$log_schema_entries = explode('# log ', (string) $mixed['schema_sql']);
	$log_data_entries = explode('# log ', (string) $mixed['data_sql']);
	$ddl_date_from = $date = Util::date('Y-m-d H:i:s');
	$ddlog = [];
	$first_iteration = true;
	// Iterate tracked data definition statements
	// For each log entry we want to get date, username and statement
	foreach ($log_schema_entries as $log_entry) {
	if (trim($log_entry) != '') {
	$date = mb_substr($log_entry, 0, 19);
	$username = mb_substr(
	$log_entry,
	20,
	mb_strpos($log_entry, "\n") - 20
	);
	if ($first_iteration) {
	$ddl_date_from = $date;
	$first_iteration = false;
	}
	$statement = rtrim(mb_strstr($log_entry, "\n"));
	$ddlog[] = [
	'date' => $date,
	'username' => $username,
	'statement' => $statement,
	];
	}
	}

Do you have more information about tracking feature of phpmyadmin?

[williamdes]

Hi @Sylvain303 🇫🇷
It will take me some time to reproduce and try to fix this issue
Thank you for all the context and debug you provided us

[Sylvain303]

OK, @williamdes, let me know if I can help.

[shucon]

Starting to work on this issue.

[MauricioFauth]

Fixed by #16389.