#3292 (ok 4.5) Incoherent backup generated

3.5.5
resolved
Low
2015-03-31
2011-05-10
Anonymous
No

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

Discussion

  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    Anonymous - 2011-05-10

    PHP test script

     
  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    Anonymous - 2011-05-10

    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.

     
  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    Anonymous - 2011-05-10

    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.

     
  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    Anonymous - 2011-05-10

    updated php script

    http://pastebin.com/rD72AvH5

     
  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    Anonymous - 2011-05-18

    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.

     
  • Marc Delisle

    Marc Delisle - 2013-01-22
    • milestone: 3.3.10 --> 3.5.5
     
  • Marc Delisle

    Marc Delisle - 2015-02-15
    • Priority: 6 --> Normal
     
  • Madhura Jayaratne

    • assigned_to: Madhura Jayaratne
     
  • Madhura Jayaratne

    • summary: Incoherent backup generated --> (ok 4.5) Incoherent backup generated
    • status: open --> resolved
    • Priority: Normal --> Low