[Maria-discuss] Problem Upgrade MariaDb 5.5.52 MariaDb 10.1.22
Hi, i have a system running perfectly on mariadb 5.5.52, i will change everything to a new server with mariadb 10.1.22, but running the installation script gives me problems, this is the error, is there any way to configure mariadb 10.1.22 To omit this error, since mariadb 10.1.22 works with strict-trans-table, there is some old_mode feature that allows this.
QUERY:
CREATE TABLE `media_gallery_music` ( `gallery_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `value_id` INT(11) UNSIGNED NOT NULL, `artwork_url` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL, `name` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL, `position` INT(11) NOT NULL, PRIMARY KEY (`gallery_id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `media_gallery_music_album` ( `album_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `gallery_id` INT(11) UNSIGNED NOT NULL, `name` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL, `artwork_url` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL, `podcast_url` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL, `artist_name` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL, `type` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`album_id`), KEY `KEY_GALLERY_ID` (`gallery_id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `media_gallery_music_elements` ( `position_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `gallery_id` INT(11) UNSIGNED DEFAULT NULL, `album_id` INT(11) UNSIGNED DEFAULT NULL, `track_id` INT(11) UNSIGNED DEFAULT NULL, `position` INT(11) NOT NULL, PRIMARY KEY (`position_id`), KEY `KEY_GALLERY_ID` (`gallery_id`), KEY `KEY_ALBUM_ID` (`album_id`), KEY `KEY_TRACK_ID` (`track_id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `media_gallery_music_track` ( `track_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `album_id` INT(11) UNSIGNED DEFAULT NULL, `gallery_id` INT(11) UNSIGNED DEFAULT NULL, `name` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL, `duration` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL, `artwork_url` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL, `artist_name` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL, `album_name` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL, `price` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL, `currency` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL, `purchase_url` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL, `stream_url` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL, `type` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL, `position` INT(11) NOT NULL, PRIMARY KEY (`track_id`), KEY `KEY_ALBUM_ID` (`album_id`), KEY `KEY_GALLERY_ID` (`gallery_id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ALTER TABLE `media_gallery_music_album` ADD FOREIGN KEY `FK_GALLERY_ID`(`gallery_id`) REFERENCES `media_gallery_music` (`gallery_id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `media_gallery_music_elements` ADD FOREIGN KEY `FK_GALLERY_ID` (`gallery_id`) REFERENCES `media_gallery_music` (`gallery_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD FOREIGN KEY `FK_ALBUM_ID` (`album_id`) REFERENCES `media_gallery_music_album` (`album_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD FOREIGN KEY `FK_TRACK_ID` (`track_id`) REFERENCES `media_gallery_music_track` (`track_id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `media_gallery_music_track` ADD FOREIGN KEY `FK_ALBUM_ID` (`album_id`) REFERENCES `media_gallery_music_album` (`album_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD FOREIGN KEY `FK_GALLERY_ID` (`gallery_id`) REFERENCES `media_gallery_music` (`gallery_id`) ON DELETE CASCADE ON UPDATE CASCADE;
Work perfect MariaDb 5.5.52.
Error MariaDb 10.1.22:
Query : ALTER TABLE `media_gallery_music_elements` ADD FOREIGN KEY `FK_GALLERY_ID` (`gallery_id`) REFERENCES `media_gallery_musi...
Error Code : 1005 Can't create table `test`.`#sql-c82_1086` (errno: 121 "Duplicate key on write or update")
Execution Time : 00:00:00:000 Transfer Time : 00:00:00:000 Total Time : 00:00:00:000
I can manually modify and fix the error, but it is a system with many scripts and updates, is there any way to omit this error in Mariadb 10.1.22.
Thks,
Am 21.03.2017 um 21:08 schrieb Wilmer Arambula:
Hi, ihave a system running perfectly on mariadb 5.5.52, i will change everything to a new server with mariadb 10.1.22, but running the installation script gives me problems, this is the error, is there any way to configure mariadb 10.1.22 To omit this error, since mariadb 10.1.22 works with strict-trans-table, there is some old_mode feature that allows this.
Query : ALTER TABLE `media_gallery_music_elements` ADD FOREIGN KEY `FK_GALLERY_ID` (`gallery_id`) REFERENCES `media_gallery_musi...
Error Code : 1005 Can't create table `test`.`#sql-c82_1086` (errno: 121 "Duplicate key on write or update")
Execution Time : 00:00:00:000 Transfer Time : 00:00:00:000 Total Time : 00:00:00:000
I can manually modify and fix the error, but it is a system with many scripts and updates, is there any way to omit this error in Mariadb 10.1.22
either you have unique keys or not
however why not first upgrade to 10.0.x and why working with dumps instead just as others do for many many years an dbrought their server from mysql3 to mariadb 10.1 - this is not postgresql
* upgrade 10.x * mysql_upgrade -u root -p * upgrade 10.1.x * mysql_upgrade -u root -p
no, not on the live-server, on a virtual machine where you first rsync your datadir, play the upgrades and finally when you have a working plan after the last retry rsync the datadir to the new liveserver
Hi, Wilmer!
On Mar 21, Wilmer Arambula wrote:
Hi, i have a system running perfectly on mariadb 5.5.52, i will change everything to a new server with mariadb 10.1.22, but running the installation script gives me problems, this is the error, is there any way to configure mariadb 10.1.22 To omit this error, since mariadb 10.1.22 works with strict-trans-table, there is some old_mode feature that allows this.
No, 10.1.22 shouldn't have STRICT_TRANS_TABLES by default, at least it's not what I see in the code.
Why do you think you have STRICT_TRANS_TABLES enabled?
Query : ALTER TABLE `media_gallery_music_elements` ADD FOREIGN KEY `FK_GALLERY_ID` (`gallery_id`) REFERENCES `media_gallery_musi... Error Code : 1005 Can't create table `test`.`#sql-c82_1086` (errno: 121 "Duplicate key on write or update")
I cannot quite think what 10.0 or 10.1 change could've caused this error. A check for duplicate keys isn't new, it worked for the last 20 years.
May be there was some change in collations and values were considered different in 5.5 but are compared as equal in 10.1? Run CHECK TABLE on your media_gallery_music_elements in 10.1.
Or mysql_upgrade, indeed, as Reindl suggested.
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
participants (3)
-
Reindl Harald -
Sergei Golubchik -
Wilmer Arambula