- discuss - lists.mariadb.org

mysqlcheck/mariadbcheck: consistency check before mysqldump
by Felix Natter 21 Apr '24

21 Apr '24

Dear mariadb experts, I would like to do this to verify my dbs (to detect hardware errors so that I can replay a backup timely): mysqlcheck -u root -pX --check --all-databases --extended Is it definitely read-only? Can I rely on the return code (0=success)? Or do I not need this because "mysqldump" does consistency checks? Many Thanks and Best Regards! Felix -- Felix Natter

2 2

Transaction Log Flushing got worse again in 10.5.23 -> 10.5.24
by Gordan Bobic 16 Apr '24

16 Apr '24

I've mentioned this here a few times before, but it looks like the latest 10.5.x update to 10.5.24 actually made things worse than in 10.5.23. I have attached two screenshots of transaction log checkpoint age over a 5 minute oltp_write_only sysbench test with 10.5.23 and 10.5.24. 10.5.23 starts off with the flushing all or nothing behaviour but it dampens out after the first minute or so. 10.5.24 exhibits it permanently. I ran multiple separate tests and the behaviour is very consistent for each version. 10.5.23 almost (not quite but almost) seems to have had things fixed to a state where things were in 10.5.8. It's pretty bad that whatever causes this undesirable flushing behaviour seems to have been merged as of 10.5.9 (10.5.8 seems to be the newest version that doesn't exhibit it). It's much more concerning that nobody seems to care that the last good major version (10.4) is going EOL soon with apparently no newer long-term tested version that is not broken. I also tested the latest 10.6.17 and that exhibits the same broken behaviour. Is a fix for this behaviour expected any time soon? On high throughput production systems that can have prolonged bursts of large writes that can hit at any time - if they hit at the top of the checkpoint age that the database made no effort to flush, there is no space left in the transaction log to absorb that impact and it leads to stalls. If combined with disk I/O exhaustion, these stalls can be so bad that for a period of tens of seconds or even minutes even the database telemetry gathering starts to fails. I also attached a screenshot with a 10 minute period where 10.5.8 and 10.5.9 were sysbenched back-to-back for 5 minutes each, with an update/restart in the middle. How extensively is the commit that introduced this behaviour built upon in subsequent changes? How much of a rabbit hole am I heading down if I try to cherry-unpick it? The config I tested with is as follows: ``` [mysqld] innodb_buffer_pool_dump_at_shutdown = 0 innodb_buffer_pool_load_at_startup = 0 innodb_buffer_pool_size = 1G innodb_fast_shutdown = 0 # in case a upgrade introduces redo log format change innodb_io_capacity = 2000 innodb_io_capacity_max = 10000 innodb_log_file_size = 256M innodb_log_write_ahead_size=16K innodb_monitor_enable = all innodb_use_native_aio = 0 # native aio is slow on ZFS innodb_use_atomic_writes = 0 # native aio is slow on ZFS ```

3 5

Windows gssapi plug-in with multiple SIDs/Groups
by darrel.fenstad＠gmail.com 15 Apr '24

15 Apr '24

I am using MariaDB 10.117 and gssapi plug-in to allow SID:BA as follows: "ALTER USER `myuser`@`localhost` IDENTIFIED VIA gssapi USING 'SID:BA';" This works fine. But i want to add an additional GROUP besides SID:BA. I have tried: "ALTER USER `myuser`@`localhost` IDENTIFIED VIA gssapi USING 'SID:BA' OR gssapi USING 'GROUP:My Group';" However, when I look at mysql - View - user it shows the authentication_string as only GROUP:My Group. I also tried: "ALTER USER `myuser`@`localhost` IDENTIFIED VIA gssapi USING 'SID:BA', 'GROUP:My Group'; But that showed authentication_string in mysql - View - user as only SID:BA. Is it possible to specify multiple SIDs and GROUPs and, if so, what is the syntax to do so? Regards, Darrel

2 1

Backup MariaDB Databases for Suprema BioStar 2 Door Access System
by Turritopsis Dohrnii Teo En Ming 15 Apr '24

15 Apr '24

Subject: Backup MariaDB Databases for Suprema BioStar 2 Door Access System Good day from Singapore, On 7 March 2024 Thursday, when I was installing new self-signed SSL certificate for the door access system for a law firm in Singapore, I notice that Suprema BioStar 2 also uses the open source MariaDB database server. If I did not remember wrongly, there are 3 databases. However, the entire platform is a Windows environment, not Linux. How can I backup all the databases in the MariaDB database server? Thank you. Regards, Mr. Turritopsis Dohrnii Teo En Ming Targeted Individual in Singapore Blogs: https://tdtemcerts.blogspot.com https://tdtemcerts.wordpress.com GIMP also stands for Government-Induced Medical Problems.

2 2

Why MariaDB 11.4.1 master-slave replication not sync old data
by i18n.site＠gmail.com 07 Apr '24

07 Apr '24

I have a question about an issue I'm facing with MariaDB 11.4.1 (MariaDB 11 should have GTID enabled by default). I am trying to set up a highly available MariaDB (1 master with 2 slaves) using orchestrator. After shutting down the master database, orchestrator automatically switches one of the slave databases (u3) to become the new master (all slaves have log_slave_updates enabled). At this point, I manually create a brand new, empty slave database u2 (with no data) to synchronize with this new master. The Slave_IO_Running and Slave_SQL_Running status on u2 are both Yes. I attempt to create a new database on the master, and it appears on u2 as well. However, the problem is that the old databases present on u3 are not showing up on u2. ( see this image https://p.3ti.site/1712458958.webp ) I have searched online for solutions and it seems like I would need to manually back up the old databases, import them, and then modify the binlog files and synchronization positions. This feels cumbersome and is not convenient for automated recovery. I am wondering if there might be a misconfiguration on my part that is causing this issue.

2 1

Why is configuring init-file causing the error COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'binary'
by i18n.site 07 Apr '24

07 Apr '24

Why is configuring init-file causing the error COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'binary' I use default character-set-server=binary ❯ rg binary conf.d/mariadb.cnf 7:character-set-server=binary 8:collation-server=binary ❯ /usr/local/mysql/bin/mariadbd --init-file=/etc/mysql/init.sql --defaults-file=/etc/mysql/mariadb.cnf 2024-04-07 12:07:40 0 [ERROR] COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'binary' 2024-04-07 12:07:40 0 [ERROR] Aborting ❯ cat /etc/mysql/init.sql STOP SLAVE;CHANGE MASTER TO master_use_gtid=slave_pos, MASTER_USER='sync',MASTER_HOST='xx',MASTER_PORT=2002,MASTER_PASSWORD='xx',MASTER_CONNECT_RETRY=1; START SLAVE; /etc/mysql

1 0

LOCK TABLES FOR WRITE with mysqldump imports
by stephan.vos＠metacom.net 05 Apr '24

05 Apr '24

I noticed that my data archiving process has been adding LOCK TABLES (table) FOR WRITE to the mysqldump file. I am now worried that if I ever need to import this data into the 3 node Galera cluster it will cause deadlocking as described in the MariaDB known issues article. Does MariaDB with Galera still lock up when this statement is executed or would it just ignore the LOCK TABLES command?

1 0

Help - Creating a loop to go through every row in a table, no errors, not working
by ram3n 02 Apr '24

02 Apr '24

Hi all, I've ran into a little roadblock while creating a WHILE loop within a procedure that's intended to go through every row within a table, *world.creature_template*. Query runs fine in my database, no warnings or errors, but it's not actually changing anything at all. Affected rows are always at 0. Link to the code is below, thanks in advance. https://srcb.in/YVuGJQU4o8 Some sample records: entrydifficulty_entry_1difficulty_entry_2difficulty_entry_3KillCredit1 KillCredit2modelid1modelid2modelid3modelid4namesubnameIconNamegossip_menu_id minlevelmaxlevelexpfactionnpcflagspeed_walkspeed_runscalerankdmgschool BaseAttackTimeRangeAttackTimeBaseVarianceRangeVarianceunit_classunit_flags unit_flags2dynamicflagsfamilytypetype_flagslootidpickpocketlootskinloot PetSpellDataIdVehicleIdmingoldmaxgoldAINameMovementTypeHoverHeight HealthModifierManaModifierArmorModifierDamageModifierExperienceModifier RacialLeadermovementIdRegenHealthmechanic_immune_mask spell_school_immune_maskflags_extraScriptNameVerifiedBuild 29 0 0 0 25499 0 25275 0 0 0 Kanrethad Master of Death 0 90 90 0 35 0 1 1.14286 1 0 0 2000 2000 1 1 1 0 2048 0 0 7 0 0 0 0 0 0 0 0 0 1 1250 1 1 1 1 0 0 1 0 0 0 12340 30 0 0 0 0 0 382 0 0 0 Forest Spider 0 5 6 0 22 0 1 0.85714 1 0 0 2000 2000 1 1 1 0 2048 0 3 1 1 30 0 0 5880 0 0 0 SmartAI 1 1 1 1 1 1 1 0 100 1 0 0 0 12340 31 0 0 0 0 0 145 0 0 0 Furbolg 0 1 2 0 35 0 1.2 1.14286 1 0 0 2000 2000 1 1 1 0 2048 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 0 0 1 0 0 0 12340 36 0 0 0 0 0 367 0 0 0 Harvest Golem 0 11 12 0 14 0 1 1.14286 1 0 0 2000 2000 1 1 1 0 2048 0 0 9 32768 36 0 0 0 0 13 21 1 1 1 1 1 1 1 0 0 1 16384 0 0 12340 38 0 0 0 0 0 5035 5036 0 0 Defias Thug 0 3 4 0 7 0 1 0.85714 1 0 0 2000 2000 1 1 1 0 2048 0 0 7 0 38 38 0 0 0 2 7 SmartAI 1 1 1 1 1 1 1 0 100 1 0 0 0 12340 40 0 0 0 0 0 373 0 0 0 Kobold Miner 0 6 7 0 26 0 1 0.85714 1 0 0 2000 2000 1 1 1 0 2048 0 0 7 0 40 40 0 0 0 5 12 SmartAI 1 1 1 1 1 1 1 0 100 1 0 0 0 12340 *generated 2024-03-31 22:08:23 by HeidiSQL 12.1.0.6537 <https://www.heidisql.com/>*

1 0

Spatial indexes, innodb, myisam and update lock cannot be acquired during a READ UNCOMMITTED transaction.
by Pascal Boulesteix 27 Mar '24

27 Mar '24

Hello everyone I am currently testing the migration of my web server to PHP 8.2.13 and mariadb 11.2.2. During my tests, I got stuck on a SELECT problem on tables using spatial indexes. During a complex page chaining several CREATE TABLEs on temporary tables with spatial indexes, a SELECT planted with the message "update lock cannot be acquired during a READ UNCOMMITTED transaction". After consulting Google, I first unblocked the situation by removing the spatial indexes on temporary tables but then the performance of intermediate queries collapsed. In the end, after doing a test package, I definitely found a solution: the default database engine is innodb. By forcing the myisam engine on the temporary tables the page executes in a few seconds! Questions: - what to think of this use case of spatial indexes with innodb vs myisa? - Is this a bug? Below are the DMLs for queries with myisam explicitly indicated. With innodb, the last query in the list crashes, either timeout if I execute via php, or "update lock cannot be acquired during a READ UNCOMMITTED transaction" if I execute the query via phpmyadmin 1 CREATE TABLE TEMPTABLE_localites_du_site(Localite_unik_commune int, Localite_dept varchar(15), Localite_unik int, GPS_unik int, Type_Localite varchar(1), Localite_p_coordonnees_spacial point not null) ENGINE = myisam ; 0 0 2 ALTER TABLE TEMPTABLE_localites_du_site ADD SPATIAL INDEX IDX_temptable_localites_du_site_Localite_p_coordonnees_spacial(Localite_p_coordonnees_spacial); 0 0 1 CREATE TABLE WKWNat_2A2B50F4_415A_443F_A959_38AB2F0EC1C6 (Localite_unik_commune int, Localite_dept varchar(15), Localite_unik int, GPS_unik int, Type_Localite varchar(1), Localite_p_coordonnees_spacial point, Maille_unik int, Maille_unik_site int, Maille_maille varchar(80), Maille_taille int, Maille_g_coordonnees_spacial geometry NOT NULL, Maille_couleur_bordure int, Maille_couleur_aire int, Maille_transparence_aire int, Maille_Exclusion bit NOT NULL, Longitude_Centre double(10,6), Latitude_Centre double(10,6), in_use_by_leudit bit NOT NULL, myTaille varchar(3)) ENGINE = myisam; 0 0 5 insert INTO TEMPTABLE_localites_du_site (SELECT DISTINCT wnat_lieuxdits.unik_commune, wnat_communes.Dept, wnat_lieuxdits.unik AS unik_lieudit, NULL AS unik_gps, 'L' AS Type_Localite, wnat_lieuxdits.p_coordonnees_spacial FROM wnat_lieuxdits INNER JOIN wnat_inventaires ON wnat_inventaires.unik_lieudit = wnat_lieuxdits.unik INNER JOIN wnat_communes ON wnat_communes.unik = wnat_lieuxdits.unik_commune WHERE wnat_inventaires.unik_organisme=10 AND ST_CONTAINS(ST_PolygonFromText('POLYGON((41.36475929466526 8.54315203804477,43.0275854970405 8.54315203804477,43.0275854970405 9.555702267296203,41.36475929466526 9.555702267296203,41.36475929466526 8.54315203804477))'),wnat_lieuxdits.p_coordonnees_spacial) ) 0 788 6 insert INTO TEMPTABLE_localites_du_site (SELECT DISTINCT wnat_lieuxdits.unik_commune, wnat_communes.Dept, wnat_lieuxdits.unik AS unik_lieudit, wnat_gps.unik AS unik_gps, 'G' AS Type_Localite, wnat_gps.p_coordonnees_spacial FROM wnat_lieuxdits INNER JOIN wnat_gps ON wnat_lieuxdits.unik = wnat_gps.unik_lieudit INNER JOIN wnat_inventaires ON wnat_gps.unik = wnat_inventaires.unik_gps INNER JOIN wnat_communes ON wnat_communes.unik = wnat_lieuxdits.unik_commune WHERE wnat_inventaires.unik_organisme=10 AND ST_CONTAINS(ST_PolygonFromText('POLYGON((41.36475929466526 8.54315203804477,43.0275854970405 8.54315203804477,43.0275854970405 9.555702267296203,41.36475929466526 9.555702267296203,41.36475929466526 8.54315203804477))'),wnat_gps.p_coordonnees_spacial) ) 0 29566 7 DELETE TEMPTABLE_localites_du_site FROM TEMPTABLE_localites_du_site INNER JOIN wnat_lieuxdits ON ( TEMPTABLE_localites_du_site.Localite_unik = wnat_lieuxdits.unik) INNER JOIN wnat_communes ON (wnat_lieuxdits.unik_commune = wnat_communes.unik) where wnat_communes.unik_organisme<>10 0 0 765 CREATE TABLE TEMPTABLE_W1 ENGINE = myisam AS SELECT MAX(wnat_geometries_mailles.taille) AS max_taille FROM TEMPTABLE_localites_du_site INNER JOIN wnat_geometries_mailles ON ST_CONTAINS(wnat_geometries_mailles.g_coordonnees_spacial, TEMPTABLE_localites_du_site.Localite_p_coordonnees_spacial) WHERE wnat_geometries_mailles.unik_geometrie_objet IN (121332,121333,121334,121335,121419,121385,121376,121383,121449,121450,121345,121373,121370,121371,121317,121379,121355,121362,121363,121367,121372,121346,121347,121348,121349,121365,121366,121378,121442,121443,121444,121448,121411,121404,121325,121406,121315,121392,121393,121394,121395,121396,121391,121343,121344,121407,121388,121389,121412,121413,121414,121415,121420,121421,121422,121423,121424,121322,121318,121319,121320,121321,121369,121360,121361,121426,121427,121428,121429,121430,121431,121432,121327,121356,121357,121405,121316,121408,121409,121425,121375,121351,121350,121323,121384,121390,121364,121324,121416,121417,121418,121368,121386,121387,121352,121447,121440,121441,121433,121434,121435,121328,121329,121330,121331,121446,121374,121451,121397,121377,121445,121382,121336,121337,121338,121339,121340,121341,121342,121436,121437,121438,121353,121354,121380,121381,121439,121326,121398,121399,121400,121401,121402,121403,121410,121358,121359,-1) AND wnat_geometries_mailles.Exclusion = FALSE; 0 1 query_Recordset1 insert INTO WKWNat_2A2B50F4_415A_443F_A959_38AB2F0EC1C6 (SELECT TEMPTABLE_localites_du_site.Localite_unik_commune, TEMPTABLE_localites_du_site.Localite_dept, TEMPTABLE_localites_du_site.Localite_unik, TEMPTABLE_localites_du_site.GPS_unik, TEMPTABLE_localites_du_site.Type_Localite, TEMPTABLE_localites_du_site.Localite_p_coordonnees_spacial, wnat_geometries_mailles.unik, wnat_geometries_mailles.unik_geometrie_objet, wnat_geometries_mailles.maille, wnat_geometries_mailles.taille, wnat_geometries_mailles.g_coordonnees_spacial, wnat_geometries_mailles.couleur_bordure, wnat_geometries_mailles.couleur_aire, wnat_geometries_mailles.transparence_aire, wnat_geometries_mailles.Exclusion, wnat_geometries_mailles.longitude_centre, wnat_geometries_mailles.latitude_centre, 1, MAX_TAILLE.max_taille AS myTaille FROM TEMPTABLE_localites_du_site INNER JOIN wnat_geometries_mailles ON ST_CONTAINS(wnat_geometries_mailles.g_coordonnees_spacial, TEMPTABLE_localites_du_site.Localite_p_coordonnees_spacial) INNER JOIN TEMPTABLE_W1 as MAX_TAILLE ON wnat_geometries_mailles.taille = MAX_TAILLE.max_taille WHERE wnat_geometries_mailles.unik_geometrie_objet IN (121332,121333,121334,121335,121419,121385,121376,121383,121449,121450,121345,121373,121370,121371,121317,121379,121355,121362,121363,121367,121372,121346,121347,121348,121349,121365,121366,121378,121442,121443,121444,121448,121411,121404,121325,121406,121315,121392,121393,121394,121395,121396,121391,121343,121344,121407,121388,121389,121412,121413,121414,121415,121420,121421,121422,121423,121424,121322,121318,121319,121320,121321,121369,121360,121361,121426,121427,121428,121429,121430,121431,121432,121327,121356,121357,121405,121316,121408,121409,121425,121375,121351,121350,121323,121384,121390,121364,121324,121416,121417,121418,121368,121386,121387,121352,121447,121440,121441,121433,121434,121435,121328,121329,121330,121331,121446,121374,121451,121397,121377,121445,121382,121336,121337,121338,121339,121340,121341,121342,121436,121437,121438,121353,121354,121380,121381,121439,121326,121398,121399,121400,121401,121402,121403,121410,121358,121359,-1) AND wnat_geometries_mailles.Exclusion = FALSE and in_use_by_lieudit<>0); 8 insert INTO WKWNat_2A2B50F4_415A_443F_A959_38AB2F0EC1C6 (SELECT TEMPTABLE_localites_du_site.Localite_unik_commune, TEMPTABLE_localites_du_site.Localite_dept, TEMPTABLE_localites_du_site.Localite_unik, TEMPTABLE_localites_du_site.GPS_unik, TEMPTABLE_localites_du_site.Type_Localite, TEMPTABLE_localites_du_site.Localite_p_coordonnees_spacial, wnat_geometries_mailles.unik, wnat_geometries_mailles.unik_geometrie_objet, wnat_geometries_mailles.maille, wnat_geometries_mailles.taille, wnat_geometries_mailles.g_coordonnees_spacial, wnat_geometries_mailles.couleur_bordure, wnat_geometries_mailles.couleur_aire, wnat_geometries_mailles.transparence_aire, wnat_geometries_mailles.Exclusion, wnat_geometries_mailles.longitude_centre, wnat_geometries_mailles.latitude_centre, 1, MAX_TAILLE.max_taille AS myTaille FROM TEMPTABLE_localites_du_site INNER JOIN wnat_geometries_mailles ON ST_CONTAINS(wnat_geometries_mailles.g_coordonnees_spacial, TEMPTABLE_localites_du_site.Localite_p_coordonnees_spacial) INNER JOIN TEMPTABLE_W1 as MAX_TAILLE ON wnat_geometries_mailles.taille = MAX_TAILLE.max_taille WHERE wnat_geometries_mailles.unik_geometrie_objet IN (121332,121333,121334,121335,121419,121385,121376,121383,121449,121450,121345,121373,121370,121371,121317,121379,121355,121362,121363,121367,121372,121346,121347,121348,121349,121365,121366,121378,121442,121443,121444,121448,121411,121404,121325,121406,121315,121392,121393,121394,121395,121396,121391,121343,121344,121407,121388,121389,121412,121413,121414,121415,121420,121421,121422,121423,121424,121322,121318,121319,121320,121321,121369,121360,121361,121426,121427,121428,121429,121430,121431,121432,121327,121356,121357,121405,121316,121408,121409,121425,121375,121351,121350,121323,121384,121390,121364,121324,121416,121417,121418,121368,121386,121387,121352,121447,121440,121441,121433,121434,121435,121328,121329,121330,121331,121446,121374,121451,121397,121377,121445,121382,121336,121337,121338,121339,121340,121341,121342,121436,121437,121438,121353,121354,121380,121381,121439,121326,121398,121399,121400,121401,121402,121403,121410,121358,121359,-1) AND wnat_geometries_mailles.Exclusion = FALSE and in_use_by_lieudit<>0); 0 17672

1 0

Advice concerning inconsistent query results
by Jef Van Loon 24 Mar '24

24 Mar '24

Hello, I am looking for advice concering inconsistent query results. Problem: We are observing queries that return inconsistent query results. The query in question selects records within a period of time (e.g., month) based on a datetime column. The result set is missing random records within the queried time period. It appears that the inconsistent result is produced when the query uses an index containing the datetime column. When the index is not used, the result seems to contain all records. Dropping and recreating the index caused the result to be complete again. However, this has NOT solved the problem permanently as the wrong behaviour has reappeared afterwards. On second appearance, we have used OPTIMIZE table on the table in question which has again resolved the problem, we will have to wait to see whether the problem occurs again. CHECK TABLE returned no erros for the affected table. The problem does not seem to be limited to one installation, we are seing it on different MariaDB installations. Questions: - Has anybody seen such behaviour in the past? - How can we diagnose the problem? Environment: - MariaDB Version: MariaDB 10.5.13 - MariaDB Storage engine: InnoDB - MariaDB Collation: utf8mb4_unicode_ci - OS: Windows Server Kind regards, Jef

4 7