Describe the bug
I tried an upgrade from the old part-db-version, but it doesn't work. At some time i get an error message, see below. I also tried to drop the database and restore the backup, but the migration also didn't work.
Server Side
- Part-DB Version: Part-DB 1.0
- PHP Version: 7.3
- Database Server 10.3.22-MariaDB-0+deb10u1 Raspbian 10
Additional context
pi@raspberrypi:/var/www/part-db $ php bin/console doctrine:migrations:migrate
WARNING! You are about to execute a database migration that could result in schema changes and data loss. Are you sure you wish to continue? (y/n)y
Migrating up to 20200502161750 from 0
++ migrating 1 (Creates an inital empty database)
SS skipped (Reason: Old Part-DB Database detected! Continue with upgrade...)
++ migrating 20190902140506 (Upgrade database from old Part-DB 0.5 Version (dbVersion 26))
-> SET sql_mode = ''
-> RENAME TABLE `attachement_types` TO `attachment_types`;
-> RENAME TABLE `attachements` TO `attachments`;
-> CREATE TABLE currencies (id INT AUTO_INCREMENT NOT NULL, parent_id INT DEFAULT NULL, iso_code VARCHAR(255) NOT NULL, exchange_rate NUMERIC(11, 5) DEFAULT NULL, comment LONGTEXT NOT NULL, not_selectable TINYINT(1) NOT NULL, name VARCHAR(255) NOT NULL, last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, datetime_added DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, INDEX IDX_37C44693727ACA70 (parent_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
-> CREATE TABLE `measurement_units` (id INT AUTO_INCREMENT NOT NULL, parent_id INT DEFAULT NULL, unit VARCHAR(255) DEFAULT NULL, is_integer TINYINT(1) NOT NULL, use_si_prefix TINYINT(1) NOT NULL, comment LONGTEXT NOT NULL, not_selectable TINYINT(1) NOT NULL, name VARCHAR(255) NOT NULL, last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, datetime_added DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, INDEX IDX_F5AF83CF727ACA70 (parent_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
-> CREATE TABLE part_lots (id INT AUTO_INCREMENT NOT NULL, id_store_location INT DEFAULT NULL, id_part INT NOT NULL, description LONGTEXT NOT NULL, comment LONGTEXT NOT NULL, expiration_date DATETIME DEFAULT NULL, instock_unknown TINYINT(1) NOT NULL, amount DOUBLE PRECISION NOT NULL, needs_refill TINYINT(1) NOT NULL, last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, datetime_added DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, INDEX IDX_EBC8F9435D8F4B37 (id_store_location), INDEX IDX_EBC8F943C22F6CC4 (id_part), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
-> INSERT IGNORE INTO part_lots (id_part, id_store_location, amount, instock_unknown, last_modified, datetime_added) SELECT parts.id, parts.id_storelocation, parts.instock, 0, NOW(), NOW() FROM parts WHERE parts.instock >= 0
-> INSERT IGNORE INTO part_lots (id_part, id_store_location, amount, instock_unknown, last_modified, datetime_added) SELECT parts.id, parts.id_storelocation, 0, 1, NOW(), NOW() FROM parts WHERE parts.instock = -2
-> ALTER TABLE currencies ADD CONSTRAINT FK_37C44693727ACA70 FOREIGN KEY (parent_id) REFERENCES currencies (id)
-> ALTER TABLE `measurement_units` ADD CONSTRAINT FK_F5AF83CF727ACA70 FOREIGN KEY (parent_id) REFERENCES `measurement_units` (id)
-> ALTER TABLE part_lots ADD CONSTRAINT FK_EBC8F9435D8F4B37 FOREIGN KEY (id_store_location) REFERENCES `storelocations` (id)
-> ALTER TABLE part_lots ADD CONSTRAINT FK_EBC8F943C22F6CC4 FOREIGN KEY (id_part) REFERENCES `parts` (id) ON DELETE CASCADE
-> ALTER TABLE parts DROP INDEX parts_order_orderdetails_id_k, ADD UNIQUE INDEX UNIQ_6940A7FE81081E9B (order_orderdetails_id)
-> ALTER TABLE parts DROP FOREIGN KEY parts_id_storelocation_fk
-> DROP INDEX favorite ON parts
-> DROP INDEX parts_id_storelocation_k ON parts
-> ALTER TABLE parts DROP FOREIGN KEY parts_id_footprint_fk
-> ALTER TABLE parts DROP FOREIGN KEY parts_id_manufacturer_fk
-> ALTER TABLE parts CHANGE mininstock minamount DOUBLE PRECISION NOT NULL, ADD id_part_unit INT DEFAULT NULL, ADD manufacturer_product_number VARCHAR(255) NOT NULL, ADD manufacturing_status VARCHAR(255) DEFAULT NULL, ADD needs_review TINYINT(1) NOT NULL, ADD tags LONGTEXT NOT NULL, ADD mass DOUBLE PRECISION DEFAULT NULL, DROP instock, CHANGE id_category id_category INT NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE order_quantity order_quantity INT NOT NULL, CHANGE manual_order manual_order TINYINT(1) NOT NULL, CHANGE manufacturer_product_url manufacturer_product_url VARCHAR(255) NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, CHANGE favorite favorite TINYINT(1) NOT NULL, DROP id_storelocation
-> ALTER TABLE parts ADD CONSTRAINT FK_6940A7FE5697F554 FOREIGN KEY (id_category) REFERENCES `categories` (id)
-> ALTER TABLE parts ADD CONSTRAINT FK_6940A7FEEBBCC786 FOREIGN KEY (id_master_picture_attachement) REFERENCES `attachments` (id)
-> ALTER TABLE parts ADD CONSTRAINT FK_6940A7FE2626CEF9 FOREIGN KEY (id_part_unit) REFERENCES `measurement_units` (id)
-> CREATE INDEX IDX_6940A7FEEBBCC786 ON parts (id_master_picture_attachement)
-> CREATE INDEX IDX_6940A7FE2626CEF9 ON parts (id_part_unit)
-> CREATE INDEX IDX_6940A7FE5697F554 ON parts (id_category)
-> DROP INDEX parts_id_category_k ON parts
-> DROP INDEX parts_id_footprint_k ON parts
-> CREATE INDEX IDX_6940A7FE7E371A10 ON parts (id_footprint)
-> DROP INDEX parts_id_manufacturer_k ON parts
-> CREATE INDEX IDX_6940A7FE1ECB93AE ON parts (id_manufacturer)
-> ALTER TABLE parts ADD CONSTRAINT parts_id_footprint_fk FOREIGN KEY (id_footprint) REFERENCES footprints (id)
-> ALTER TABLE parts ADD CONSTRAINT parts_id_manufacturer_fk FOREIGN KEY (id_manufacturer) REFERENCES manufacturers (id)
-> ALTER TABLE attachment_types DROP FOREIGN KEY attachement_types_parent_id_fk
-> ALTER TABLE attachment_types ADD filetype_filter LONGTEXT NOT NULL, ADD not_selectable TINYINT(1) NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE comment comment LONGTEXT NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
-> DROP INDEX attachement_types_parent_id_k ON attachment_types
-> CREATE INDEX IDX_EFAED719727ACA70 ON attachment_types (parent_id)
-> ALTER TABLE attachment_types ADD CONSTRAINT attachement_types_parent_id_fk FOREIGN KEY (parent_id) REFERENCES attachment_types (id)
-> ALTER TABLE categories DROP FOREIGN KEY categories_parent_id_fk
-> ALTER TABLE categories ADD not_selectable TINYINT(1) NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE disable_footprints disable_footprints TINYINT(1) NOT NULL, CHANGE disable_manufacturers disable_manufacturers TINYINT(1) NOT NULL, CHANGE disable_autodatasheets disable_autodatasheets TINYINT(1) NOT NULL, CHANGE disable_properties disable_properties TINYINT(1) NOT NULL, CHANGE comment comment LONGTEXT NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
-> DROP INDEX categories_parent_id_k ON categories
-> CREATE INDEX IDX_3AF34668727ACA70 ON categories (parent_id)
-> ALTER TABLE categories ADD CONSTRAINT categories_parent_id_fk FOREIGN KEY (parent_id) REFERENCES categories (id)
-> ALTER TABLE devices DROP FOREIGN KEY devices_parent_id_fk
-> ALTER TABLE devices ADD not_selectable TINYINT(1) NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE order_quantity order_quantity INT NOT NULL, CHANGE order_only_missing_parts order_only_missing_parts TINYINT(1) NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, CHANGE comment comment LONGTEXT NOT NULL
-> DROP INDEX devices_parent_id_k ON devices
-> CREATE INDEX IDX_11074E9A727ACA70 ON devices (parent_id)
-> ALTER TABLE devices ADD CONSTRAINT devices_parent_id_fk FOREIGN KEY (parent_id) REFERENCES devices (id)
-> ALTER TABLE footprints DROP FOREIGN KEY footprints_parent_id_fk
-> ALTER TABLE footprints ADD not_selectable TINYINT(1) NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE comment comment LONGTEXT NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
-> DROP INDEX footprints_parent_id_k ON footprints
-> CREATE INDEX IDX_A34D68A2727ACA70 ON footprints (parent_id)
-> ALTER TABLE footprints ADD CONSTRAINT footprints_parent_id_fk FOREIGN KEY (parent_id) REFERENCES footprints (id)
-> ALTER TABLE manufacturers DROP FOREIGN KEY manufacturers_parent_id_fk
-> ALTER TABLE manufacturers ADD not_selectable TINYINT(1) NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE address address VARCHAR(255) NOT NULL, CHANGE phone_number phone_number VARCHAR(255) NOT NULL, CHANGE fax_number fax_number VARCHAR(255) NOT NULL, CHANGE email_address email_address VARCHAR(255) NOT NULL, CHANGE website website VARCHAR(255) NOT NULL, CHANGE auto_product_url auto_product_url VARCHAR(255) NOT NULL, CHANGE comment comment LONGTEXT NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
-> DROP INDEX manufacturers_parent_id_k ON manufacturers
-> CREATE INDEX IDX_94565B12727ACA70 ON manufacturers (parent_id)
-> ALTER TABLE manufacturers ADD CONSTRAINT manufacturers_parent_id_fk FOREIGN KEY (parent_id) REFERENCES manufacturers (id)
-> ALTER TABLE storelocations DROP FOREIGN KEY storelocations_parent_id_fk
-> ALTER TABLE storelocations ADD storage_type_id INT DEFAULT NULL, ADD only_single_part TINYINT(1) NOT NULL, ADD limit_to_existing_parts TINYINT(1) NOT NULL, ADD not_selectable TINYINT(1) NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE is_full is_full TINYINT(1) NOT NULL, CHANGE comment comment LONGTEXT NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
-> ALTER TABLE storelocations ADD CONSTRAINT FK_7517020B270BFF1 FOREIGN KEY (storage_type_id) REFERENCES `measurement_units` (id)
-> CREATE INDEX IDX_7517020B270BFF1 ON storelocations (storage_type_id)
-> DROP INDEX storelocations_parent_id_k ON storelocations
-> CREATE INDEX IDX_7517020727ACA70 ON storelocations (parent_id)
-> ALTER TABLE storelocations ADD CONSTRAINT storelocations_parent_id_fk FOREIGN KEY (parent_id) REFERENCES storelocations (id)
-> ALTER TABLE suppliers DROP FOREIGN KEY suppliers_parent_id_fk
-> ALTER TABLE suppliers ADD default_currency_id INT DEFAULT NULL, ADD shipping_costs NUMERIC(11, 5) DEFAULT NULL, ADD not_selectable TINYINT(1) NOT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE address address VARCHAR(255) NOT NULL, CHANGE phone_number phone_number VARCHAR(255) NOT NULL, CHANGE fax_number fax_number VARCHAR(255) NOT NULL, CHANGE email_address email_address VARCHAR(255) NOT NULL, CHANGE website website VARCHAR(255) NOT NULL, CHANGE auto_product_url auto_product_url VARCHAR(255) NOT NULL, CHANGE comment comment LONGTEXT NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
-> ALTER TABLE suppliers ADD CONSTRAINT FK_AC28B95CECD792C0 FOREIGN KEY (default_currency_id) REFERENCES currencies (id)
-> CREATE INDEX IDX_AC28B95CECD792C0 ON suppliers (default_currency_id)
-> DROP INDEX suppliers_parent_id_k ON suppliers
-> CREATE INDEX IDX_AC28B95C727ACA70 ON suppliers (parent_id)
-> ALTER TABLE suppliers ADD CONSTRAINT suppliers_parent_id_fk FOREIGN KEY (parent_id) REFERENCES suppliers (id)
-> DROP INDEX attachements_class_name_k ON attachments
-> ALTER TABLE attachments DROP FOREIGN KEY attachements_type_id_fk
-> ALTER TABLE attachments ADD datetime_added DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, CHANGE type_id type_id INT DEFAULT NULL, CHANGE name name VARCHAR(255) NOT NULL, CHANGE filename filename VARCHAR(255) NOT NULL, CHANGE show_in_table show_in_table TINYINT(1) NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
-> ALTER TABLE attachments ADD CONSTRAINT FK_47C4FAD61F1F2A24 FOREIGN KEY (element_id) REFERENCES `parts` (id) ON DELETE CASCADE
-> DROP INDEX attachements_type_id_fk ON attachments
-> CREATE INDEX IDX_47C4FAD6C54C8C93 ON attachments (type_id)
-> CREATE INDEX IDX_47C4FAD61F1F2A24 ON attachments (element_id)
-> DROP INDEX attachements_element_id_k ON attachments
-> ALTER TABLE attachments ADD CONSTRAINT attachements_type_id_fk FOREIGN KEY (type_id) REFERENCES attachment_types (id)
-> ALTER TABLE users CHANGE name name VARCHAR(180) NOT NULL, CHANGE first_name first_name VARCHAR(255) DEFAULT NULL, CHANGE last_name last_name VARCHAR(255) DEFAULT NULL, CHANGE department department VARCHAR(255) DEFAULT NULL, CHANGE email email VARCHAR(255) DEFAULT NULL, CHANGE need_pw_change need_pw_change TINYINT(1) NOT NULL, CHANGE config_language config_language VARCHAR(255) DEFAULT NULL, CHANGE config_timezone config_timezone VARCHAR(255) DEFAULT NULL, CHANGE config_theme config_theme VARCHAR(255) DEFAULT NULL, CHANGE config_currency config_currency VARCHAR(255) NOT NULL, CHANGE perms_labels perms_labels INT NOT NULL, CHANGE last_modified last_modified DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
-> ALTER TABLE users ADD CONSTRAINT FK_1483A5E9FE54D947 FOREIGN KEY (group_id) REFERENCES `groups` (id)
Migration 20190902140506 failed during Execution. Error An exception occurred while executing 'ALTER TABLE users ADD CONSTRAINT FK_1483A5E9FE54D947 FOREIGN KEY (group_id) REFERENCES groups
(id)':
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (partdb
.#sql-2fec_40
, CONSTRAINT FK_1483A5E9FE54D947
FOREIGN KEY (group_id
) REFERENCES groups
(id
))
13:03:30 ERROR [console] Error thrown while running command "doctrine:migrations:migrate". Message: "An exception occurred while executing 'ALTER TABLE users ADD CONSTRAINT FK_1483A5E9FE54D947 FOREIGN KEY (group_id) REFERENCES groups
(id)':\n\nSQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (partdb
.#sql-2fec_40
, CONSTRAINT FK_1483A5E9FE54D947
FOREIGN KEY (group_id
) REFERENCES groups
(id
))" ["exception" => Doctrine\DBAL\Exception\ForeignKeyConstraintViolationException^ { …},"command" => "doctrine:migrations:migrate","message" => """ An exception occurred while executing 'ALTER TABLE users ADD CONSTRAINT FK_1483A5E9FE54D947 FOREIGN KEY (group_id) REFERENCES groups
(id)':\n \n SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (partdb
.#sql-2fec_40
, CONSTRAINT FK_1483A5E9FE54D947
FOREIGN KEY (group_id
) REFERENCES groups
(id
)) """]
In AbstractMySQLDriver.php line 49:
An exception occurred while executing 'ALTER TABLE users ADD CONSTRAINT FK_1483A5E9FE54D947 FOREIGN KEY (group_id) REFERENCES groups
(id)':
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (partdb
.#sql-2fec_40
, CONSTRA
INT FK_1483A5E9FE54D947
FOREIGN KEY (group_id
) REFERENCES groups
(id
))
In PDOConnection.php line 83:
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (partdb
.#sql-2fec_40
, CONSTRA
INT FK_1483A5E9FE54D947
FOREIGN KEY (group_id
) REFERENCES groups
(id
))
In PDOConnection.php line 78:
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (partdb
.#sql-2fec_40
, CONSTRA
INT FK_1483A5E9FE54D947
FOREIGN KEY (group_id
) REFERENCES groups
(id
))
doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] []
pi@raspberrypi:/var/www/part-db $ php bin/console doctrine:migrations:migrate
WARNING! You are about to execute a database migration that could result in schema changes and data loss. Are you sure you wish to continue? (y/n)y
Migrating up to 20200502161750 from 1
++ migrating 20190902140506 (Upgrade database from old Part-DB 0.5 Version (dbVersion 26))
-> SET sql_mode = ''
-> RENAME TABLE `attachement_types` TO `attachment_types`;
Migration 20190902140506 failed during Execution. Error An exception occurred while executing 'RENAME TABLE attachement_types
TO attachment_types
;':
SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'attachment_types' already exists
13:04:44 ERROR [console] Error thrown while running command "doctrine:migrations:migrate". Message: "An exception occurred while executing 'RENAME TABLE attachement_types
TO attachment_types
;':\n\nSQLSTATE[42S01]: Base table or view already exists: 1050 Table 'attachment_types' already exists" ["exception" => Doctrine\DBAL\Exception\TableExistsException^ { …},"command" => "doctrine:migrations:migrate","message" => """ An exception occurred while executing 'RENAME TABLE attachement_types
TO attachment_types
;':\n \n SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'attachment_types' already exists """]
In AbstractMySQLDriver.php line 38:
An exception occurred while executing 'RENAME TABLE attachement_types
TO attachment_types
;':
SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'attachment_types' already exists
In PDOConnection.php line 83:
SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'attachment_types' already exists
In PDOConnection.php line 78:
SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'attachment_types' already exists
doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] []