#3292 (ok 4.5) Incoherent backup generated

[Anonymous]

In SQL Export mode, phpmyadmin generates incoherent backups. Besides, it does not backup relations.

How to reproduce :

Run attached PHP script on a "testdb" database

[Anonymous]

PHP test script

[Anonymous]

Ah, I hit the wrong button, sorry.

Anyway, run the attached script. It creates test tables and inserts data into them.

While this is running, make a backup, either with
-phpmyadmin (using the "use tranaction" option)
-mysqldump (default options or --single-transaction --lock-tables=false)

Then Ctrl-C the script, and restore the database.

With the mysqldump dumps :
- relations are backed up
- myisam tables are locked correctly and backup is coherent (unless --single-transaction --lock-tables=false)
- innodb tables have a coherent backup with both settings

With phpmyadmin,
- relations are not backed up
- both innodb and myisam backup is not coherent since tables are not locked.

SQL queries used to verify coherency :

SELECT count(*) AS a_test_myisam_rowcount FROM a_test_myisam;
SELECT count(*) AS c_test_myisam_rowcount FROM c_test_myisam;
SELECT count(*) AS a_test_innodb_rowcount FROM a_test_innodb;
SELECT count(*) AS c_test_innodb_rowcount FROM c_test_innodb;
SELECT count(*) AS b_test_rowcount FROM b_test;

SELECT 'myisam', abs(count(a_id)-count(c_id)) AS errors, count(*) FROM a_test_myisam a
LEFT JOIN c_test_myisam c
ON (a_id = c_id);

SELECT 'myisam', abs(count(a_id)-count(c_id)) AS errors, count(*) FROM c_test_myisam c
LEFT JOIN a_test_myisam a
ON (a_id = c_id);

SELECT 'innodb', abs(count(a.a_id)-count(c.a_id)) AS errors, count(*) FROM a_test_innodb a
LEFT JOIN c_test_innodb c ON (a.a_id=c.a_id);

SELECT 'innodb', abs(count(a.a_id)-count(c.a_id)) AS errors, count(*) FROM c_test_innodb c
LEFT JOIN a_test_innodb a ON (a.a_id=c.a_id);

If phpmyadmin did backup relations, then restoring the dump would fail.

[Anonymous]

Haha, there was a bug in my bug report. Too much postgres lately.

Turns out this silently ignores the FK declaration :

CREATE TABLE c_test_innodb (
c_id INTEGER PRIMARY KEY AUTO_INCREMENT,
a_id INTEGER NOT NULL REFERENCES a_test_innodb( a_id ),
KEY( a_id )
) ENGINE=InnoDB;

This works though :

CREATE TABLE c_test_innodb (
c_id INTEGER PRIMARY KEY AUTO_INCREMENT,
a_id INTEGER NOT NULL,
FOREIGN KEY (a_id) REFERENCES a_test_innodb( a_id ),
KEY( a_id )
) ENGINE=InnoDB;

Now phpmyadmin exports the foreign key declaration. Good.

And when importing the dump, it FAILS with a foreign key check, because the backup isn't coherent, since phpmyadmin didn't take any locks or use an InnoDB single point in time snapshot backup.

[Anonymous]

updated php script

http://pastebin.com/rD72AvH5

[Anonymous]

Bug reproduced on a popular french shared hosting (ovh) -- Backup was generated by ISP's provided phpmyadmin while the php test script attached in the previous message was running. Generated backup is not coherent and attempt to restore it gives foreign key violation errors.

[Madhura Jayaratne]

Table locking has been implemented by https://github.com/phpmyadmin/phpmyadmin/commit/df38a76b9f4bf745f5bd769fc43125b3036c3a68 and will be part of version 4.5.
With that version I ran the test suite a couple of times and coherency of the backup generated could be verified.