Bug #13365

SQL error upgrading AtoM demo data to AtoM 2.6

Added by David Juhasz 4 months ago. Updated 3 months ago.

Status:VerifiedStart date:06/23/2020
Priority:MediumDue date:
Assignee:-% Done:

0%

Category:Migration task
Target version:Release 2.6.0
Google Code Legacy ID: Tested version:2.6
Sponsored:No Requires documentation:

Description

I get the following error when attempting to upgrade the AtoM demo data (v102) with the symfony tools:upgrade-sql script:

$ sudo -u www-data ./symfony tools:upgrade-sql -v

  WARNING: Your database has not been backed up!
  Please back-up your database manually before you proceed.
  If this task fails you may lose your data.

  Have you done a manual backup and wish to proceed? (y/N)

y
>> upgrade-sql Upgrading from Release 1.1
up(102)
>> upgrade-sql Upgrading from Release 1.2
up(102)

  SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'function_object_FK_1'; check that column/key exists

History

#1 Updated by David Juhasz 4 months ago

  • Assignee set to José Raddaoui Marín

#2 Updated by David Juhasz 4 months ago

After the error the database version is 175:

$ sudo -u www-data ./symfony tools:get-version
2.6.0 v175

Trying to run symfony tools:upgrade-sql again throws the error:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'function_object' already exists

#3 Updated by David Juhasz 4 months ago

Here's my current `function_object` schema after running tools:upgrade-sql (and dying on upgrade 176):

 CREATE TABLE `function_object` (
  `id` int NOT NULL,
  `type_id` int DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  `description_status_id` int DEFAULT NULL,
  `description_detail_id` int DEFAULT NULL,
  `description_identifier` varchar(1024) DEFAULT NULL,
  `source_standard` varchar(1024) DEFAULT NULL,
  `lft` int DEFAULT NULL,
  `rgt` int DEFAULT NULL,
  `source_culture` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `function_object_FI_2` (`type_id`),
  KEY `function_object_FI_3` (`parent_id`),
  KEY `function_object_FI_4` (`description_status_id`),
  KEY `function_object_FI_5` (`description_detail_id`),
  CONSTRAINT `function_FK_1` FOREIGN KEY (`id`) REFERENCES `object` (`id`) ON DELETE CASCADE,
  CONSTRAINT `function_FK_2` FOREIGN KEY (`type_id`) REFERENCES `term` (`id`),
  CONSTRAINT `function_FK_3` FOREIGN KEY (`parent_id`) REFERENCES `function_object` (`id`),
  CONSTRAINT `function_FK_4` FOREIGN KEY (`description_status_id`) REFERENCES `term` (`id`),
  CONSTRAINT `function_FK_5` FOREIGN KEY (`description_detail_id`) REFERENCES `term` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

#4 Updated by David Juhasz 4 months ago

And:

mysql> SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  WHERE TABLE_NAME="function_object" AND COLUMN_NAME="id" AND REFERENCED_TABLE_NAME="object";
+----------------------+
| CONSTRAINT_NAME      |
+----------------------+
| function_object_FK_1 |
| function_FK_1        |
+----------------------+
2 rows in set (0.03 sec)

#5 Updated by David Juhasz 4 months ago

Oh, it looks the query in migration0176.class.php is finding the `function_object_FK_1` in a different database. I'm upgrading the `atom_dev` database, but it's getting the index name from the `atom` db on the same MySQL server.

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
WHERE TABLE_NAME="function_object" 
AND COLUMN_NAME="id" AND REFERENCED_TABLE_NAME="object"\G
*************************** 1. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: atom
              CONSTRAINT_NAME: function_object_FK_1
                TABLE_CATALOG: def
                 TABLE_SCHEMA: atom
                   TABLE_NAME: function_object
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
      REFERENCED_TABLE_SCHEMA: atom
        REFERENCED_TABLE_NAME: object
       REFERENCED_COLUMN_NAME: id
*************************** 2. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: atom_dev
              CONSTRAINT_NAME: function_FK_1
                TABLE_CATALOG: def
                 TABLE_SCHEMA: atom_dev
                   TABLE_NAME: function_object
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
      REFERENCED_TABLE_SCHEMA: atom_dev
        REFERENCED_TABLE_NAME: object
       REFERENCED_COLUMN_NAME: id
2 rows in set (0.03 sec)

#6 Updated by David Juhasz 4 months ago

Similar error thrown by arMigration0177.class.php from the QubitMigrate::updateForeignKeys() call:

  SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'information_object_FK_8'; check that column/key exists

#7 Updated by David Juhasz 4 months ago

  • Status changed from New to Code Review

#8 Updated by David Juhasz 4 months ago

  • Assignee deleted (José Raddaoui Marín)

#9 Updated by José Raddaoui Marín 4 months ago

  • Status changed from Code Review to Feedback
  • Assignee set to David Juhasz
  • Target version set to Release 2.6.0

#10 Updated by David Juhasz 4 months ago

  • Status changed from Feedback to QA/Review
  • Assignee deleted (David Juhasz)

#11 Updated by Dan Gillean 3 months ago

  • Status changed from QA/Review to Verified

Also available in: Atom PDF