March 2017 - discuss - lists.mariadb.org

[Maria-discuss] Problem Upgrade MariaDb 5.5.52 MariaDb 10.1.22
by Wilmer Arambula 21 Mar '17

21 Mar '17

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, -- *Wilmer.*

3 2

[Maria-discuss] sst failure?
by l vic 21 Mar '17

21 Mar '17

What can case the following problem? [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (2cd97600-0e4e-11e7-997a-0a414b1bb17e): 1 (Operation not permitted) at galera/src/replicator_str.cpp:prepare_for_IST():482. IST will be unavailable. [Note] WSREP: Member 1.0 () requested state transfer from '*any*'.

1 0

Re: [Maria-discuss] Problem Upgrade MariaDb 5.5.52 MariaDb 10.1.22
by Wilmer Arambula 21 Mar '17

21 Mar '17

Thanks for your answer, I understand perfectly, but this is an old system, I understand the procedure of the update, but I thought there was some old_mode configuration only to run that database with the options of mariadb 5.5.52 in mariadb 10.1.22. Thks, Wilmer. 2017-03-21 16:19 GMT-04:00 Reindl Harald <h.reindl(a)thelounge.net>: > >> >> >> 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 >> >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-discuss >> Post to : maria-discuss(a)lists.launchpad.net >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp >> > > >

1 0

[Maria-discuss] Missing MariaDB Seal logos?
by Colin Charles 20 Mar '17

20 Mar '17

Hi! Visiting: https://mariadb.com/kb/en/mariadb/branding-guidelines/ You’ll note that the Powered by MariaDB badges still exist (https://mariadb.org/about/logos/) — they should really say Powered by MariaDB Server But what is missing are all the seal logos? Currently beyond the archives at Wikimedia Commons (https://commons.wikimedia.org/wiki/File:Mariadb-seal-browntext.svg) they seem to have disappeared Is this just an oversight and something that needs fixing? Thanks -- Colin Charles, http://bytebot.net/blog/ twitter: @bytebot | skype: colincharles "First they ignore you, then they laugh at you, then they fight you, then you win." -- Mohandas Gandhi

2 1

[Maria-discuss] Questions to temporary myisam table files
by Hempel, Sebastian 20 Mar '17

20 Mar '17

Hallo, there is a known issue with the repair table function (MDEV-5871, MDEV-8475), which was fixed in MariaDB 10. Which is fine, but we have still MariaDB 5.5 server running. So we created our own repairtable function to work around this problem. For that function I like to know all possible file extensions for temporary myisam table files. For me temporary myisam table files are these files which are generated when calling "check table", "repair table" or other database functions. Thanks Sebastian

2 3

[Maria-discuss] Exclusive lock not working
by Jesse Schalken 20 Mar '17

20 Mar '17

I have a transaction that checks if a row exists with a particular value and if not, generates the data for that row and inserts it: -- schema CREATE TABLE test ( col INT, data TEXT, KEY (col) ); -- transaction START TRANSACTION; SELECT * FROM test WHERE col = 4 FOR UPDATE; -- If no results, generate data and insert INSERT INTO test SET col = 4, data = 'generated data goes here'; COMMIT; The problem I have is that if two copies of this transaction run concurrently (with the queries interleaved), they both pass the SELECT and deadlock on the INSERT. I would expect that the SELECT would acquire an exclusive lock on "col = 4" so only one transaction will proceed to insert, but the "FOR UPDATE" seems to behave as a shared lock instead. What is the correct way to write this transaction so only one copy proceeds to generate the "data" string and insert the row? (I originally posted this on Stack Overflow here <https://stackoverflow.com/questions/42547629/insert-row-if-not-exists-witho…> .) Thanks

2 2

[Maria-discuss] MariaDB 10.1.22 now available
by Daniel Bartholomew 19 Mar '17

19 Mar '17

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.1.22. This is a stable (GA) release. See the Release Notes and Changelogs for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.1.22 - Release Notes: https://mariadb.com/kb/en/mdb-10122-rn/ - Changelog: https://mariadb.com/kb/en/mdb-10122-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.1.22 About MariaDB 10.1: - https://mariadb.com/kb/en/what-is-mariadb-101/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - There are several upcoming MariaDB-focused webinars and many previously held ones available to watch on an on-demand basis at: - https://mariadb.com/news-events/webinars - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadbfdn - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

3 11

[Maria-discuss] Fix different gtid-positions on domain 0 in multi-master
by Reinder Cuperus 17 Mar '17

17 Mar '17

Hello, After moving half of the databases on our primary master-master-cluster to a different cluster, we have a problem on our backup-server which is now a slave of both servers. Topology before: master1 <-> master2 -> backup-slave Topology during migrations: master1 <-> master2 -> backup-slave <-> master3 <-> master4 Final topology: master1 <-> master2 -> backup-slave <- master3 <-> master master4 The gtid_domain_ids before/during the migration were: master1/master2 : 0 master3/master4 : 20 Replication-settings: master2 -> backup: Replicate_Do_Domain_Ids: 0 master3 -> backup: Replicate_Do_Domain_Ids: 20 backup->master3: Replicate_Do_DB: DbToMigrate1,DbToMigrate2,etc Server version: 10.1.9 So after the migration, a typical gtid_position would be on: master1/2: 0-1-12345 master3/4: 0-1-12345,20-21-5678 As long as I keep the connection backup->master running (safe, because no new transactions on the migrated databases are occurring anymore on master1/2), the position on domain 0 gets recorded on master3. The problem is, as soon as I stop that connection, that master2 and master3 have different gtid-positions for domain0, and stop/start on replication master3->backup results in the error: "Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 0-1-3898746614, which is not in the master's binlog' I have tried moving master1/2 to domain_id:1, and removing the domain_id:0 from the gtid_slave_pos on backup, but starting the replication master2->backup results in the error: Got fatal error 1236 from master when reading data from binary log: 'Could not find GTID state requested by slave in any binlog files. Probably the slave state is too old and required binlog files have been purged.' I have tried to find a way to insert an empty transaction, with the last gtid on domain_id:0 on the master3, to bring master2/master3 in sync again on that domain, but I could not find a way to do that on MariaDB. I tried finding a way to purge domain:0 from master3/master4, but the only way sofar I have found is doing a "RESET MASTER" on master3, which would break replication between master3 and master4. Are there other ways to fix this issue, so I can have reliable replication master3->backup without having to keep the dummy replication backup->master3 indefinitely? Regards, Reinder Cuperus

2 2

[Maria-discuss] Partition a Huge table
by Suresh Rajagopal 15 Mar '17

15 Mar '17

Hi, What is the fastest approach to partition a huge table with 2 additional columns? How safe its to alter table on slave and promote slave as master? It takes 25 hours to alter the table for now on slave. MariaDB Version : 10.1.18OS : Centos 6 ThanksSuresh

4 6

[Maria-discuss] [Warning] WSREP: Send action {(nil), 785, TORDERED} returned -103 (Software caused connection abort)
by Robbi Nespu 10 Mar '17

10 Mar '17

Hi there, I need to ask a guide from here. I have 5 galera cluster using mariadb 10.1 on centos 7 hosted on VPS for a few month. It running well until a weeks ago, during my leave, our VPS hosting rebooted our server. I create a backups and reboot the clusters1 till cluster2 (I don't enable auto start services on my box). Then on galera cluster 1, I ran command "galera_new_cluster" and start services on others clusters. cluster 2 - cluster 5 are working great. But it seems, cluster1 has problem. I checked services status and I got lot of this message : Mar 10 15:04:05 galera1 mysqld[6628]: 2017-03-10 15:04:05 139929087744768 [Warning] WSREP: Send action {(nil), 520, TORDERED} returned -103 (Software caused connection abort) Mar 10 15:04:05 galera1 mysqld[6628]: 2017-03-10 15:04:05 139929087744768 [Warning] WSREP: Send action {(nil), 520, TORDERED} returned -103 (Software caused connection abort) I tried to connect mariadb galera on cluster 1, seem the data are not synced unlike the others 4 cluster. Anyone face the same problem? Any guideline for me to troubleshoot this problem is most appreciated.

1 0