Doctrine Backuptables-Collection of common programming errors

2 years ago
admin
4 minutes

is there any way to make Backup Tables in Doctrine 2.

I have an XML and want to import it every day with an cron job. First I create a new Schema with the end _new.

if ($newTables) {
    $tablePastfix = new \DoctrineExtensions\TablePastfix('_new');
    $evm->addEventListener(\Doctrine\ORM\Events::loadClassMetadata, $tablePastfix);
    }

    $entityManager = \Doctrine\ORM\EntityManager::create($conn, $config, $evm);

    if ($newTables) {
    $tool = new \Doctrine\ORM\Tools\SchemaTool($entityManager);
    $classes = array (
        $entityManager->getClassMetadata('MyClasses'),
        $entityManager->getClassMetadata('MyClasses'),
        $entityManager->getClassMetadata('MyClasses'),
    );
        $tool->createSchema($classes);
}

After it i import the data from xml. And is all okay, i rename the Tables

$dm = Doctrine\DBAL\DriverManager::getConnection($conn);
$sql = "DROP TABLE `mytable_old`;";
$stmt = $dm->query($sql);
$stmt->execute();

$dm = Doctrine\DBAL\DriverManager::getConnection($conn);
$sql = "ALTER TABLE `mytable` RENAME `mytable_old`;";
$stmt = $dm->query($sql);
$stmt->execute();

$dm = Doctrine\DBAL\DriverManager::getConnection($conn);
$sql = "ALTER TABLE `mytable_new` RENAME `mytable`;";
$stmt = $dm->query($sql);
$stmt->execute();

But when i want to import again, i get the following error . . . . . 🙁

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1005 Can't create table 'databasename.#sql-220_87' (errno: 121)' in /Applications/MAMP/bin/php/php5.3.14/lib/php/Doctrine/ORM/Tools/ToolsException.php on line 33

PDOException: SQLSTATE[HY000]: General error: 1005 Can't create table 'databasename.#sql-220_87' (errno: 121) in /Applications/MAMP/bin/php/php5.3.14/lib/php/Doctrine/DBAL/Connection.php on line 646

Call Stack:
    0.0011     761296   1. {main}() /path/to/jobs/import.php:0
    0.0014     767024   2. require_once('/path/to/jobs/config.php') /path/to/jobs/import.php:7
    0.0017     770536   3. require_once('/path/to/jobs/bootstrap.php') /path/to/jobs/config.php:3
    0.0020     796264   4. require_once('/path/to/jobs/bootstrap_doctrine.php') /path/to/jobs/bootstrap.php:5
    0.1468    7673040   5. Doctrine\ORM\Tools\SchemaTool->createSchema() /path/to/jobs/bootstrap_doctrine.php:53
    1.1216    9445080   6. Doctrine\DBAL\Connection->executeQuery() /Applications/MAMP/bin/php/php5.3.14/lib/php/Doctrine/ORM/Tools/SchemaTool.php:90
    1.1216    9445656   7. PDO->query() /Applications/MAMP/bin/php/php5.3.14/lib/php/Doctrine/DBAL/Connection.php:646

Doctrine\DBAL\DBALException: An exception occurred while executing 'ALTER TABLE doc_consultant_practice_new ADD CONSTRAINT FK_60C69DE344F779A2 FOREIGN KEY (consultant_id) REFERENCES doc_xml_consultant_new (id) ON DELETE CASCADE':

SQLSTATE[HY000]: General error: 1005 Can't create table 'databasename.#sql-220_87' (errno: 121) in /Applications/MAMP/bin/php/php5.3.14/lib/php/Doctrine/DBAL/DBALException.php on line 47

Call Stack:
    0.0011     761296   1. {main}() /path/to/jobs/import.php:0
    0.0014     767024   2. require_once('/path/to/jobs/config.php') /path/to/jobs/import.php:7
    0.0017     770536   3. require_once('/path/to/jobs/bootstrap.php') /path/to/jobs/config.php:3
    0.0020     796264   4. require_once('/path/to/jobs/bootstrap_doctrine.php') /path/to/jobs/bootstrap.php:5
    0.1468    7673040   5. Doctrine\ORM\Tools\SchemaTool->createSchema() /path/to/jobs/bootstrap_doctrine.php:53
    1.1216    9445080   6. Doctrine\DBAL\Connection->executeQuery() /Applications/MAMP/bin/php/php5.3.14/lib/php/Doctrine/ORM/Tools/SchemaTool.php:90

Doctrine\ORM\Tools\ToolsException: Schema-Tool failed with Error 'An exception occurred while executing 'ALTER TABLE doc_consultant_practice_new ADD CONSTRAINT FK_60C69DE344F779A2 FOREIGN KEY (consultant_id) REFERENCES doc_xml_consultant_new (id) ON DELETE CASCADE':

SQLSTATE[HY000]: General error: 1005 Can't create table 'databasename.#sql-220_87' (errno: 121)' while executing DDL: ALTER TABLE doc_consultant_practice_new ADD CONSTRAINT FK_60C69DE344F779A2 FOREIGN KEY (consultant_id) REFERENCES doc_xml_consultant_new (id) ON DELETE CASCADE in /Applications/MAMP/bin/php/php5.3.14/lib/php/Doctrine/ORM/Tools/ToolsException.php on line 33

Call Stack:
    0.0011     761296   1. {main}() /path/to/jobs/import.php:0
    0.0014     767024   2. require_once('/path/to/jobs/config.php') /path/to/jobs/import.php:7
    0.0017     770536   3. require_once('/path/to/jobs/bootstrap.php') /path/to/jobs/config.php:3
    0.0020     796264   4. require_once('/path/to/jobs/bootstrap_doctrine.php') /path/to/jobs/bootstrap.php:5
    0.1468    7673040   5. Doctrine\ORM\Tools\SchemaTool->createSchema() /path/to/jobs/bootstrap_doctrine.php:53

Do anybody know my mistake or have an better idea to realize backup tables?

  1. it looks like you have an issue because of index name duplication, prefixing tables does not prefix indexes for them. You should use a different database for that kind of functionality, you can rename it afterwards or sync data with two connections opened.

Originally posted 2013-11-27 12:08:56.