- discuss - lists.mariadb.org

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

Possible Security Bug
by Gordan Bobic 24 Mar '24

24 Mar '24

Hi, What is an appropriate forum for discussing what seems to be a security bug that I ran into?

3 3

Re: Setting up a jira self hosted instance
by Daniel Black 20 Mar '24

20 Mar '24

Query obviously from connector. Works in 11.3.3 MariaDB [test]> SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout\G *************************** 1. row *************************** auto_increment_increment: 1 character_set_client: utf8mb3 character_set_connection: utf8mb3 character_set_results: utf8mb3 character_set_server: latin1 collation_server: latin1_swedish_ci collation_connection: utf8mb3_general_ci init_connect: interactive_timeout: 28800 license: GPL lower_case_table_names: 0 max_allowed_packet: 16777216 net_write_timeout: 60 performance_schema: 0 sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION system_time_zone: AEDT time_zone: SYSTEM transaction_isolation: REPEATABLE-READ wait_timeout: 28800 1 row in set (0.001 sec) MariaDB [test]> select version(); +----------------+ | version() | +----------------+ | 11.3.3-MariaDB | +----------------+ 1 row in set (0.000 sec) Obviously fails in earlier versions: MariaDB [test]> SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, -> @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout\G ERROR 1193 (HY000): Unknown system variable 'transaction_isolation' MariaDB [test]> select version(); +-----------------+ | version() | +-----------------+ | 10.11.8-MariaDB | +-----------------+ 1 row in set (0.000 sec) Are you sure you have the right version of MariaDB that you described? Otherwise upgrade/use MariaDB Connector/J. On Thu, 21 Mar 2024 at 04:45, <juanhernandez98(a)gmail.com> wrote: > > Just as a FYI, I enabled the MariaDB general logs: > > And I'm getting this when the jira instance tries and connect to my mriadb server: > > 240319 14:28:23 239 Connect jira@localhost on jira using TCP/IP > 239 Query SET SESSION default_storage_engine=InnoDB > 239 Query /* mysql-connector-j-8.3.0 (Revision: 805f872a57875f311cb82487efcfb070411a3fa0) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout > > > -----Original Message----- > From: Daniel Black via discuss <discuss(a)lists.mariadb.org> > Sent: Tuesday, March 19, 2024 9:57 PM > To: discuss(a)lists.mariadb.org > Subject: [MariaDB discuss] Re: Setting up a jira self hosted instance > > On Wed, 20 Mar 2024 at 07:20, Juan Hernandez via discuss <discuss(a)lists.mariadb.org> wrote: > > > > Hi, > > > > So I’ve been running MariaDB 11.3 for a while. I have various applications working great with this server. > > > > I want to setup a Jira 9.14 self hosted instance on one of my servers. I see mariadb.com uses Jira. > > > > So I put the mysql java connector v8.3.0, and the mariadb java connector v3.3.3 installed in my /opt/Atlassian/jira/lib directory. > > > > I tried connecting jira via the setup web interface to my maria db, using the mysql 8.0 driver, and I get the error that I’m not running the right version. > > > > Is that from the driver or from JIRA? > > > I then added under [mysqld] in my.cnf, version = “8.0.25-0” and now get the below error. > > > Error connecting to database > > > > Unknown system variable 'transaction_isolation' > > https://jira.mariadb.org/browse/MDEV-21921 - added this to 11.1, so you should be right in 11.3. Its not 10.11.3 right? > > Can JIRA use the MariaDB Java Connector v3.3.3? > > A last resort is to specify the version=5.7.44 (any 5.7) and/or use a > 5.7 compatible mysql java connector. > > > > I am now getting this error when testing the connection in jira: > > > > I don’t want to setup a postgresql instance or a mysql 8 instance, can I get this to work? Any help would be greatly appreciated. > > Us too. We're still battling against JIRA supported databases a little after all these years. > _______________________________________________ > discuss mailing list -- discuss(a)lists.mariadb.org To unsubscribe send an email to discuss-leave(a)lists.mariadb.org >

1 0

Setting up a jira self hosted instance
by juanhernandez98＠gmail.com 20 Mar '24

20 Mar '24

Hi, So I've been running MariaDB 11.3 for a while. I have various applications working great with this server. I want to setup a Jira 9.14 self hosted instance on one of my servers. I see mariadb.com uses Jira. So I put the mysql java connector v8.3.0, and the mariadb java connector v3.3.3 installed in my /opt/Atlassian/jira/lib directory. I tried connecting jira via the setup web interface to my maria db, using the mysql 8.0 driver, and I get the error that I'm not running the right version. I then added under [mysqld] in my.cnf, version = "8.0.25-0" and now get the below error. Error connecting to database Unknown system variable 'transaction_isolation' I am now getting this error when testing the connection in jira: I don't want to setup a postgresql instance or a mysql 8 instance, can I get this to work? Any help would be greatly appreciated. Best Juan

2 1

Trigger Fails
by steffan＠hldns.com 15 Mar '24

15 Mar '24

I just converted to MariaDB from MySQL where this trigger worked fine. DELIMITER ;; CREATE TRIGGER `email_uuid_before_insert` BEFORE INSERT ON `email` FOR EACH ROW BEGIN IF new.id_email IS NULL THEN SET new.id_email = uuid(); END IF; END;; DELIMITER ;; In MariaDB, it creates with no errors but when adding a row, I get an error 1062 : Duplicate entry '' for key 'PRIMARY' It’s caused because the last row that inserted wasn’t populated with that UUID – meaning the trigger failed. What would cause this to fail? Thank you, Steffan Cline steffan(a)hldns.com<mailto:steffan@hldns.com> 602-793-0014

2 2

Support for MariaDB Community Repo after EL7 EOL
by james.manley＠jadu.co.uk 14 Mar '24

14 Mar '24

Hi, We currently use MariaDB 10.5 from the MariaDB Community Repo on EL7. EL7 is end of life at the end of June 2024. Will MariaDB 10.5 continue to receive support and security updates until its end of life, or will support and updates stop on EL7 once the OS reaches end of life? Thanks!

2 1

MariaDB's Percona Tookit Fork Source Code Repository?
by Gordan Bobic 13 Mar '24

13 Mar '24

Hi, I noticed references to these tools: https://mariadb.com/kb/en/mariadb-tools/ but the github links are all 404. Where can all of the sources for these be downloaded from?

3 4

next and previous words
by ChipsRunner 24 Feb '24

24 Feb '24

Hi I've noticed that between debian 10 and 11 (mariadb-client-core-10.3 and mariadb-client-core-10.5) the client console tool (CLI) treats words in a different way: alt+f and alt+b will skip the dot on the new version. Example if I have something like 'test.domain.com' using alt+f on the old version would treat 'test', 'domain' and 'com' as three separate words while the new version would skip at the end of '.com' Is there a way to restore the old behaviour ? Thank you, PM

2 2

Back again for more help
by Ken Wright 21 Feb '24

21 Feb '24

Now that you kind people have pointed me in the right direction as far as log-bin was concerned, I've moved on to trying to replicate the databases from the primary to the secondary. I've created the connection profile and started the secondary connection, but when I ran show slave 'master01' status\G I got the following error: Last_Errno: 1062 Last_Error: Could not execute Write_rows_v1 event on table databasename.oc_filecache; Duplicate entry '110' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log master-bin.00000#, end_log_pos ###### How do I resolve this duplicate entry error? And do I need to do the same resolution on the primary as well? Ubuntu Server 23.10, mariadb version 15.1 Distrib 10.11.6-MariaDB Ken -- If you ever think international affairs make sense, remember this: because a Serb shot an Austrian in Bosnia, Germany invaded Belgium.

3 2

Setting up primary-secondary replication
by Ken Wright 17 Feb '24

17 Feb '24

Greetings! I'm trying to set up a backup email server using Ubuntu 23.10 Server. I want to replicate my MariaDB databases from the primary email server to the secondary, but whenever I add the line log-bin = /var/log/mysql/master-bin to /etc/mysql/mariadb.conf.d/50-server.cnf MariaDB fails to restart. I've looked for help online, but haven't had any luck. Can anyone help? Further details available on request. Ken -- If you ever think international affairs make sense, remember this: because a Serb shot an Austrian in Bosnia, Germany invaded Belgium.

3 4

Windows downloads
by madhon＠madhon.co.uk 15 Feb '24

15 Feb '24

Have the windows downloads for versions 10.6.17 and 10.11.7 been pulled as the only option i see are source and linux ?

3 2

Slow log and processlist.memory_used
by Gordan Bobic 13 Feb '24

13 Feb '24

In processlist, there is a column called memory_used. Is there a way to make the peak value for a query get logged into slow log? Failing that, does the performance_schema provide any such info for previously run statements to go with statement digests? Or is the only place where this can be found in the information_schema.processlist table while the query is running?

1 0

Upgrading MariaDB 10.6.15 to 10.6.17 on Ubuntu 18.04 (unsupported)
by stephan.vos＠metacom.net 11 Feb '24

11 Feb '24

We are currently unable to upgrade our server os from Ubuntu 18.04 to 22.04. So we are stuck on MariaDB 10.6.15 and cannot apply any subsequent minor/major releases. Is it possible to somehow use installation binary package for Focal/Jammy or worse case recompiling from source for 18.04?

4 10

Fwd: REGEXP_REPLACE behaviour
by Richard Ellerbrock 07 Feb '24

07 Feb '24

Hi All, I am trying to understand the behaviour of REGEXP_REPLACE when there is a backslash in the subject string. It appears that the subject needs to have \'s escaped which is not desirable (see example 1 below). I can understand having to escape \'s in the regex, but not the subject string. Any suggestions? Thanks. Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 397751 Server version: 10.11.5-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SELECT REGEXP_REPLACE('Et12\4', '^Et', 'Ethernet'); +---------------------------------------------+ | REGEXP_REPLACE('Et12\4', '^Et', 'Ethernet') | +---------------------------------------------+ | Ethernet124 | +---------------------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT REGEXP_REPLACE('Et12\\4', '^Et', 'Ethernet'); +----------------------------------------------+ | REGEXP_REPLACE('Et12\\4', '^Et', 'Ethernet') | +----------------------------------------------+ | Ethernet12\4 | +----------------------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT REGEXP_REPLACE('Et12/4', '^Et', 'Ethernet'); +---------------------------------------------+ | REGEXP_REPLACE('Et12/4', '^Et', 'Ethernet') | +---------------------------------------------+ | Ethernet12/4 | +---------------------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT REGEXP_REPLACE('Et12 4', '^Et', 'Ethernet'); +---------------------------------------------+ | REGEXP_REPLACE('Et12 4', '^Et', 'Ethernet') | +---------------------------------------------+ | Ethernet12 4 | +---------------------------------------------+ 1 row in set (0.000 sec) -- Richard Ellerbrock

2 3

Question regarding AMD EPYC servers and MariaDB
by highclass99 06 Feb '24

06 Feb '24

Hello, In the past when 2nd gen EPYC's were launching and beating Intel servers on most benchmarks, I visited a MariaDB conference and when I asked a senior MariaDB engineer, and asked whether he recommended AMD servers. At that time, he recommended Intel for MariaDB, because MariaDB had much more Intel testing equipment and was optimized for Intel. I am curious whether that is still the case, and whether MariaDB should be more optimized for Intel? For example, would AMD EPYC 7763 vs Intel Platinum 8380, would one be recommended over the other for MariaDB? (of course cores, and workload differ, but on a general perspective) Thank you.

3 3

Replication corrupts index on InnoDB
by Oliver Welter 05 Feb '24

05 Feb '24

Hi All, I have a very strange problem which I am not able to debug any further, so I kindly ask here for any advise to find the root cause of this issue. I run a master-slave replication setup, both servers run on Debian Bookworm with the packages from the distro (10.11.4-MariaDB-1~deb12u1-log). There are two tables in two databases with the same setup (same shop software) and both suffer from recurring index corruption on the slave side. While the master server works without any issues, the slave machine stops replicating from time to time with the error: Last_Errno: 1712 Last_Error: Error 'Index s_order_details is corrupted' on query. Default database: 'foobar_shop'. Query: 'DELETE FROM s_order_details WHERE orderID='40007'' After rebuilding the index by running "alter table foobar_shop.s_order_details engine=innodb" I can restart the slave and it works again. What I did so far: Rebuild the whole slave from fresh SQL Dumps (twice!) Changed replication to "Using_Gtid: Slave_Pos" Reduced slave parallel threads Settings as different from debian defaults binlog_format = mixed expire_logs_days = 1 max_binlog_size = 256M slave_parallel_max_queued=524288 slave_parallel_threads=5 sync_binlog=0 sync_relay_log=0 innodb_flush_log_at_trx_commit=0 best regards Oliver -- Protect your environment - close windows and adopt a penguin!

2 4

terminology: Oracle and non-Oracle?
by Federico Razzoli 04 Feb '24

04 Feb '24

Peter Gulutzan has a very good point that the terms "Oracle" and "non-Oracle" for procedural dialects are not ideal: http://ocelot.ca/blog/blog/2024/01/31/packages-in-mariadb-default-mode/ I'd suggest you choose a name for your native procedural language, and consistently use it in the docs. A lot of DBMSs did that. Postgres uses PL/pgSQL, so you could go for PL/mdbSQL? Cheers, Federico -- Federico Razzoli Vettabase Ltd director & founder, Database Consultant https://vettabase.com federico.razzoli(a)vettabase.com +44 7739 427279 Telegram: federico_razzoli Skype: fede.razzoli The information in this e-mail and any attachments may be confidential. If you received it by mistake, please inform the sender. Unless you are the intended recipient or his/her representative you are not authorised to, and must not, read, copy, distribute, use or retain this message or any part of it.

1 0

Repeatable read anomalies
by Kyle Kingsbury 16 Jan '24

16 Jan '24

Hi y'all! I've just completed an analysis of isolation levels in MySQL. The report focuses on MySQL, but the test suite runs against MariaDB as well. MariaDB exhibits all the same behaviors--"Repeatable Read" allows a whole bunch of anomalies that should be prohibited under Repeatable Read, including G2-item, G-single (even without predicates), non-repeatable reads, violations of Monotonic Atomic View, etc. Figured this might be of interest to the list. https://jepsen.io/analyses/mysql-8.0.34 I'm also curious whether this is widely-understood behavior in the MariaDB community, or if it's news to most people. The extant literature doesn't really seem to discuss these behaviors much, but maybe there's more tacit knowledge among experts? Sincerely, --Kyle

7 19

Mariadb with hibernate-6
by Rajesh Jose 14 Jan '24

14 Jan '24

Hello everyone, In our WildFly based application, we are using mariadb-10.5.8 with mysql-connector as the driver. From the compatibility matrix, it appears that mariadb-10.2 and later are compatible with mysql-5.7 ORM we use is hibernate-6.2 and the documentation says MySQL-5.7 or higher and MariaDB-10.3 or higher are supported with out-of-box dialects. . So the mariadb we use should be compliant to hibernate-6 if we go by mariadb version or corresponding mysql version. However, when we start our app, we get a WARNING message - "HHH000511: The 5.5.0 version for [org.hibernate.dialect.MySQLDialect] is no longer supported" When we look at the connection meta-data returned from DB, it returns "MySQL" as the product-name and "5.5.5-10.5.8-MariaDB" as the product version. That seems to be the reason, hibernate is complaining. But if we connect with the mariadb-driver, it return "MariaDB" as the product name and "10.5.8-MariaDB" as the version. - What does the 5.5.0 prefix signify in the version? Is it the compatible MySQL version? If so, why its not 5.7 as it appears from documentation that versions above mariadb-10.2 are compatible with mysql-5.7? - Is this WARNING a problem that warrant a DB migration or is there a community supported dialect as explained here - https://docs.jboss.org/hibernate/orm/6.2/migration-guide/migration-guide.ht… -Rajesh

2 2

Replication Speed / Lag 10.3.31 vs 10.3.39
by Michael Caplan 14 Jan '24

14 Jan '24

Hello, I'm trying to troubleshoot a performance a replication issue between two version of MariaDB. I know that we are using two EOL versions, but I want to rule out all issues before prioritizing migrating to a new version. I have built a new dedicated replication server (B) to replace the current dedicated replica (A). A and B are both running identical OSes and versions and near identical hardware in a RAID 10 configuration. The main difference is that B has newer disks that slightly outperform A. I benchmarked both A and B to validate this. Both A and B are configured identically (other then server-id). Replica A (and primary) is running MariaDB 10.3.31. Replica B is running 10.3.39. A few other things to note: * B is not feeding off of the primary, but A -- that is until we sunset A. * A is handing moderate read only traffic * B is just running as a replica with no other responsibilities I have external monitoring to alert when either hits 500+ Seconds_Behind_Master. Since standing B up, I am seeing it fall behind a few times a day. The interesting thing is that A is not falling behind from the primary. Upon further examination, a scheduled job that executes hundreds of similar delete operations off the same table (that has approx. 3 million records) is where B is largely getting hung up. Something like this: DELETE FROM table WHERE x = 800000 AND y = 40000 The table has an index on just x. Running ANALYZE FORMAT=JSON SELECT * FROM table WHERE x = 800000 AND y = 40000 from B show a striking difference in total time spent: REPLICA B: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 490.13, "table": { "table_name": "table", "access_type": "ref", "possible_keys": ["table_index"], "key": "table_index", "key_length": "4", "used_key_parts": ["x"], "ref": ["const"], "r_loops": 1, "rows": 795555, "r_rows": 31, "r_total_time_ms": 490.08, "filtered": 100, "r_filtered": 100, "index_condition": "table.y = '40000'" } } } vs: REPLICA A: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 106.37, "table": { "table_name": "table", "access_type": "ref", "possible_keys": ["table_index"], "key": "table_index", "key_length": "4", "used_key_parts": ["x"], "ref": ["const"], "r_loops": 1, "rows": 1146482, "r_rows": 31, "r_total_time_ms": 106.34, "filtered": 100, "r_filtered": 100, "index_condition": "table.y = '40000'" } } } That is a significant difference performance wise. From a hardware point of view, B should have a slight edge over A. But the contrary is true by a large margin. As a short term "fix", I added a secondary index: create index table_index2 on table(x, y); This significantly speeds up this operation, and, for now, seems to deal with much of the experienced replication lab in B: SERVER B: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 0.253, "table": { "table_name": "table", "access_type": "ref", "possible_keys": [ "table_index", "table_index2" ], "key": "table_index2, "key_length": "8", "used_key_parts": ["x", "y"], "ref": ["const", "const"], "r_loops": 1, "rows": 31, "r_rows": 31, "r_total_time_ms": 0.2037, "filtered": 100, "r_filtered": 100 } } } This seems reasonable as a general optimization, but provides little comfort in understanding the root cause of my performance issue with B. I'm hesitant to retire A in favor of B until I can get to the bottom of this. The question I have for the community is: what would you recommend that I do to get further perspective on the issue to determine a course of action? Thanks everyone, Mike Confidentiality Notice: The information contained in this electronic message and any attachments to this message are intended only for the individual(s) addressed in the message and may contain proprietary and confidential information. If you are not the intended recipient, you should not disseminate, distribute, or copy this e-mail. Please notify the sender and destroy this message. WARNING: Computer viruses can be transmitted via email. The recipient should scan this email before opening it. The company accepts no liability for any damage caused by any virus transmitted by this email.

3 2

Data truncated using C API
by Jogchum Reitsma 08 Jan '24

08 Jan '24

Hi, Using server version: 11.2.2, I try to extract data from a database with this C-code: #include <stdlib.h> #include <stdio.h> #include <string.h> #include <mysql.h> define MAXFLEN 66 char *bo_src = "SELECT FILENAMME, FILENUM FROM BOEK WHERE TOKENS IS NULL ORDER BY UDC_KOADE, FILENUM"; char filename[MAXFLEN], stavering[3]; long filenum; int fetch_result; MYSQL *bo_src_sql; MYSQL_ST MT *bo_src_stmt; MYSQL_RES *bo_src_res; MYSQL_BIND bo_src_bind[2], bo_src_bind_result[2]; int main (argc, argv) int argc; char *argv[]; { bo_src_sql = mysql_init(bo_src_sql) ; if ((bo_src_stmt = mysql_stmt_init(bo_src_sql))==NULL) printf ("Can't initialize %s:\n %s/n", bo_src, mysql_error(bo_src_sql)) ; if (!mysql_real_connect(bo_src_sql,"localhost","<user>","<passwd>","tdb",0,NULL,0)) fprintf(stderr, "Failed to connect to book table for select: Error: %s\n", mysql_error(bo_src_sql)) ; if (mysql_stmt_prepare(bo_src_stmt, bo_src, strlen(bo_src))) printf ("Can't prepare %s:\n %s\n", bo_src, mysql_error(bo_src_sql)) ; bo_src_bind_result[0].buffer_type = MYSQL_TYPE_STRING ; bo_src_bind_result[0].buffer = filename ; bo_src_bind_result[0].buffer_length = 100 ; bo_src_bind_result[1].buffer_type = MYSQL_TYPE_LONG ; bo_src_bind_result[1].buffer = (long *)&filenum ; bo_src_bind_result[1].buffer_length = sizeof (filenum) ; memset (bo_src_bind, 0, sizeof (bo_src_bind)) ; memset (bo_src_bind_result, 0, sizeof (bo_src_bind_result)) ; if (mysql_stmt_bind_param (bo_src_stmt, bo_src_bind)) fprintf(stderr, "Failed to bind parameters in %s: \n Error: %s\n", bo_src, mysql_error(bo_src_sql)) ; if (mysql_stmt_bind_result(bo_src_stmt, bo_src_bind_result)) fprintf(stderr, "Failed to bind result in %s: \n Error: %s\n", bo_src, mysql_error(bo_src_sql)) ; if (!mysql_stmt_execute (bo_src_stmt)) { fetch_result = mysql_stmt_fetch(bo_src_stmt) ; if (fetch_result == 0) printf ("%ld %s %s\n", filenum, filename, stavering) ; else if (fetch_result == MYSQL_NO_DATA) printf ("No data found\n") ; else if (fetch_result == MYSQL_DATA_TRUNCATED) printf ("Data truncated.\n%d/n", fetch_result) ; else printf ("Something else! %d \n%s/n", fetch_result, mysql_error(bo_src_sql)); } ; mysql_stmt_close(bo_src_stmt) ; mysql_close(bo_src_sql); } The definition of the table is describe BOEK; +-----------+-------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------------------------+------+-----+---------+----------------+ | FILENUM | smallint(4) | NO | PRI | NULL | auto_increment | | TITEL | varchar(50) | YES | | NULL | | | UDC_KOADE | varchar(10) | YES | | NULL | | | ISBN | bigint(13) | YES | | NULL | | | FILENAMME | varchar(20) | YES | | NULL | | | SKR_NUM | smallint(5) | YES | | NULL | | | UTJ_NUM | smallint(5) | YES | | NULL | | | UTJ_DATUM | date | YES | | NULL | | | OERSET | tinyint(1) | YES | | NULL | | | PRINTINGE | smallint(3) | YES | | NULL | | | YNL_DATUM | date | YES | | NULL | | | STAVERING | enum('ald','mid','nij','int') | YES | | NULL | | | TOKENS | smallint(6) | YES | | NULL | | | TYPES | smallint(5) | YES | | NULL | | | MAXTOKENS | smallint(6) | YES | | NULL | | | MAXTYPES | smallint(5) | YES | | NULL | | | TIID | time | YES | | NULL | | +-----------+-------------------------------+------+-----+---------+----------------+ So the size of FILENAMME, varchar20, is much smaller than the buffer length specified in the code: ; bo_src_bind_result[0].buffer_type = MYSQL_TYPE_STRING ; bo_src_bind_result[0].buffer = filename ; bo_src_bind_result[0].buffer_length = 100 Yet I get a MYSQL_DATA_TRUNCATED fetching the result of the query. Debugging, only the integer filenum gets its value. The statement (SELECT FILENAMME, FILENUM FROM BOEK WHERE TOKENS IS NULL ORDER BY UDC_KOADE, FILENUM;) gives the expected result when entered in a terminal session. I obviously do something wrong, but I can't find out what. Googling gets me no answer. Anyone who can spot the error? Many thanks in advance, best regards, Jogchum

1 1

Re: "Access denied for user" on AWS RDS MariaDB Instance
by serg＠mariadb.org 04 Jan '24

04 Jan '24

Hi, Rhys.Campbell, First, please reply to (or cc:) the list, if you don't mind. On Jan 04, Rhys.Campbell(a)swisscom.com wrote: > Hello Sergei, > > SHOW PLUGINS; - List three authentication plugins... > > +----------------------------------+--------+--------------------+---------------+-------------+ > | Name | Status | Type | Library | License | > +----------------------------------+--------+--------------------+---------------+-------------+ > | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | > | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | > | unix_socket | ACTIVE | AUTHENTICATION | NULL | GPL | > +----------------------------------+--------+--------------------+---------------+-------------+ Okay. It shows what your user cannot don't use. But what they do use? Can you check whether they all - or at least those that get errors - only use IDENTIFIED WITH mysql_old_password USING 'xxxxx' ? I'm not asking to send SHOW CREATE USER to the list, you can just take a look yourself and reply "yes" /Sergei > -----Ursprüngliche Nachricht----- > Von: Sergei Golubchik <serg(a)mariadb.org> > Gesendet: Donnerstag, 4. Januar 2024 14:03 > An: Campbell Rhys, B2C-TSP-SRE <Rhys.Campbell(a)swisscom.com> > Cc: discuss(a)lists.mariadb.org > Betreff: Re: [MariaDB discuss] "Access denied for user" on AWS RDS MariaDB Instance > > Hi, Rhys.Campbell, > > How is authentication configured server-side? > Just the conventional mysql_native_password plugin? > > Regards, > Sergei > Chief Architect, MariaDB Server > and security(a)mariadb.org > > On Jan 04, Rhys.Campbell via discuss wrote: > > Hello All, > > > > I have a funny issue that is bothering me on a few MariaDB > > instances. We're running 10.6.14 on AWS RDS with very little changed > > in the default configuration. We have eight instances of a vendor > > application running on EC2 Instances that use these MariaDB > > Instances. This application is deployed and configured via > > TF/Ansible and we don't really manually login to these instance to > > change or configure anything. Very occasionally I get an issue with > > logging into the database. The vendor application is occasionally > > logging the following errors... > > > > Connect failed to database (db): Access denied for user > > 'user'@'X.X.X.X' (using password: YES) - waiting for 125 seconds > > before retry Connect failed to database (db): Access denied for user > > 'user'@'X.X.X.X' (using password: YES) - waiting for 25 seconds before > > retry Connect failed to database (db): Access denied for user > > 'user'@'X.X.X.X' (using password: YES) - waiting for 5 seconds before > > retry Connect failed to database (db): Access denied for user > > 'user'@'X.X.X.X' (using password: YES) - waiting for 1 seconds before > > retry > > > > Of course I recognized these as the classic "wrong password" login > > error. There are naturally corresponding error log entries in the > > MariaDB Log... > > > > Access denied for user 'user'@'X.X.X.X' (using password: YES) > > > > The vendor is a bit dismissive of this, blaming the MariaDB > > Instance. I'm not certain that is the case. We see perhaps 1 > > instance of this per day although somedays it doesn't happen and a > > small number of days we see two instances. It does not appear to > > happen on a predictable schedule. The MariaDB Instances are working > > fine at all other times. CPU utilization is very low and we only > > serve a small number of connections. There is nothing else in the > > error log of interest. Credentials are set, via Ansible, in > > configuration files. Details are correct and 99.9% of the time the > > vendor application has no issues logging into the MariaDB Instance. > > I can't actually tie these errors to something not working... but > > we're running very low traffic at the moment, and I'm concerned > > about it becoming more of an issue later on. > > > > Two possibilities exist in my mind... > > > > > > 1. Bug in vendor application is occasionally munging the password > > and creating the error we see logged. > > 2. Something else as yet unknown, i.e. rogue cronjob configured to > > use false vendor app credentials that creates the above error. I > > don't believe this to be the case but I will not 100% exclude it as > > a possibility. > > > > Is anyone aware of any logging/auditing that I could activate on the > > MariaDB RDS Instances to get a little more information about this? > > I'm thinking about setting up an strace on the vendor process but > > want to see if there are any better options first. > > > > Cheers, > > Rhys

2 1

"Access denied for user" on AWS RDS MariaDB Instance
by Rhys.Campbell＠swisscom.com 04 Jan '24

04 Jan '24

Hello All, I have a funny issue that is bothering me on a few MariaDB instances. We're running 10.6.14 on AWS RDS with very little changed in the default configuration. We have eight instances of a vendor application running on EC2 Instances that use these MariaDB Instances. This application is deployed and configured via TF/Ansible and we don't really manually login to these instance to change or configure anything. Very occasionally I get an issue with logging into the database. The vendor application is occasionally logging the following errors... Connect failed to database (db): Access denied for user 'user'@'X.X.X.X' (using password: YES) - waiting for 125 seconds before retry Connect failed to database (db): Access denied for user 'user'@'X.X.X.X' (using password: YES) - waiting for 25 seconds before retry Connect failed to database (db): Access denied for user 'user'@'X.X.X.X' (using password: YES) - waiting for 5 seconds before retry Connect failed to database (db): Access denied for user 'user'@'X.X.X.X' (using password: YES) - waiting for 1 seconds before retry Of course I recognized these as the classic "wrong password" login error. There are naturally corresponding error log entries in the MariaDB Log... Access denied for user 'user'@'X.X.X.X' (using password: YES) The vendor is a bit dismissive of this, blaming the MariaDB Instance. I'm not certain that is the case. We see perhaps 1 instance of this per day although somedays it doesn't happen and a small number of days we see two instances. It does not appear to happen on a predictable schedule. The MariaDB Instances are working fine at all other times. CPU utilization is very low and we only serve a small number of connections. There is nothing else in the error log of interest. Credentials are set, via Ansible, in configuration files. Details are correct and 99.9% of the time the vendor application has no issues logging into the MariaDB Instance. I can't actually tie these errors to something not working... but we're running very low traffic at the moment, and I'm concerned about it becoming more of an issue later on. Two possibilities exist in my mind... 1. Bug in vendor application is occasionally munging the password and creating the error we see logged. 2. Something else as yet unknown, i.e. rogue cronjob configured to use false vendor app credentials that creates the above error. I don't believe this to be the case but I will not 100% exclude it as a possibility. Is anyone aware of any logging/auditing that I could activate on the MariaDB RDS Instances to get a little more information about this? I'm thinking about setting up an strace on the vendor process but want to see if there are any better options first. Cheers, Rhys

2 1

Creating Replica With Rsync (But It Fails)
by Michael Caplan 30 Dec '23

30 Dec '23

Hi there, I am attempting to create a replica from a MariaDB 10.3.31 master. The replicate is running MariaDB 10.3.39 on the same OS (Ubuntu 20.04) and like hardware with identical MariaDB configuration. The strategy I'm using to create the replica is one that I have done before using earlier version of MariaDB and MySQL. But this time it is failing. It roughly looks like this: 1. rsync /var/lib/mysql from master to replica 2. On master: FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; 3. On master: Shutdown 4. rerun rsync from master to replica; then restart master 5. Start mariadb on replica 6. CHANGE MASTER on replica 7. Celebrate and have a nap When starting the replica which now has all the master data rsynced over, I am getting the following error repeated over and over again: 2023-12-26 22:14:54 0 [ERROR] InnoDB: Page [page id: space=43077, page number=3] log sequence number 19989139444854 is in the future! Current system log sequence number 19246539202516. 2023-12-26 22:14:54 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery. Then I get the following errors for many (but not all) tables: 2023-12-26 22:16:32 0 [ERROR] InnoDB: Expected tablespace id 3986547 but found 3986540 in the file ./ddx_practice_91804/alerts.ibd 2023-12-26 22:16:32 0 [Note] InnoDB: trying to read page [page id: space=3986547, page number=0] in the background in a non-existing or being-dropped tablespace The replica is totally useless, even though it is an exact copy, running on like hardware, same OS and similar major version of MariaDB. I've in the past used innodb_fast_shutdown = 0 on the master before final rsync, but I was told by MariaDB engineers that should not matter. I'm unclear why I'm seeing this corruption. Any ideas? Thanks, Mike Confidentiality Notice: The information contained in this electronic message and any attachments to this message are intended only for the individual(s) addressed in the message and may contain proprietary and confidential information. If you are not the intended recipient, you should not disseminate, distribute, or copy this e-mail. Please notify the sender and destroy this message. WARNING: Computer viruses can be transmitted via email. The recipient should scan this email before opening it. The company accepts no liability for any damage caused by any virus transmitted by this email.

2 2

Query Cache: Does a SELECT query tagged with SQL_NO_CACHE really touch the QC mutex?
by hellomaria-letmetrythisfirst＠mailinator.com 23 Dec '23

23 Dec '23

Hello, I found the query cache in MariaDB to be super useful and especially powerful when used in DEMAND mode. So I'd like to learn more about the performance characteristics. The documentation says that for the query cache, "the mutex is hit even for SQL_NO_CACHE": https://mariadb.com/kb/en/mariadb-memory-allocation/#query-cache Is there some more information about this somewhere? Why would a SELECT query that uses this directive dabble with the mutex at all?

2 3

Scheduled jobs recommendations
by ngtech1ltd＠gmail.com 19 Dec '23

19 Dec '23

Hey Everyone, I am currently using MariaDB 10.5.x from almalinux 9.3 and am testing latest 10.11 on Debian 12. I recently encountered an issue on 10.5 which probably didn’t run the stored procedure it should have. I have verified that the DB is configured properly ie: SET GLOBAL event_scheduler = ON; And also in the cnf files is default to ON and after a restart of the service the scheduler is on. I want to create a way to debug the stored procedure and the event scheduler. When I program a software I can log messages into somewhere but I don’t know how to do that in MariaDB and I would like to get a recommendation on how to do that this kind of logging in the when the even is being triggered and also after the call to the stored procedure ends. With this I can start to see if the event is being triggered and when. I am using it to cleanup a table and only leave the last 2 hours of data but it won’t run for at-least couple month now so I am calling the stored procedure from a cronjob. Thanks for any advice, Eliezer

2 1

Some questions restarting mariadb and importing the dumped databases
by Jogchum Reitsma 13 Dec '23

13 Dec '23

Hi all, On 3th of januari I posted the following topic, on the previous list address, maria-discuss(a)lists.launchpad.net, and gave it a reminder on the 19th of januari. I did not get an answer yet, maybe also because the list address changed in the mean time. So I take the liberty to pose the question again here, hoping this time someone is able to answer the the questions asked. Would be much appreciated! (I didn't mention it in the OP, but I've read https://mariadb.com/kb/en/mariadb-dump/, which does not seem to answer the questions) regards, Jogchum Hi, 12th of October I started the topic" Maria-db refuses to start". With the help offered in the conversation following I could access my data by manually starting an older version of Mariadb. This was on the previous list address, maria-discuss(a)lists.launchpad.net, so I then dumped my tables with mysqldump, one by one, successfully. I also dumped the mysql table. Final advice was to purge all reminiscences of the older versions, and make a fresh start by installing the version of my OS (OpenSuse Tumbleweed). In that new environment, import the dumped data. A few questions remain: - should I, after the install process of the OS version (at the moment 10.10.2-1.1) take further actions to get a fully functioning system? - should I import the dumped "mysql" table? - if yes, should I do that before or after importing the user data? Or doesn't that matter? Again, thanks for your answers! kind regards, Jogchum

2 2

10.7/10.8 removed from Debian repository: unable to upgrade Galera clusters
by William Edwards 30 Nov '23

30 Nov '23

Hi, For Debian, versions 10.7 and 10.8 were removed from the repositories. This is understandable, as they're not LTS versions, but... this makes it impossible to upgrade Galera clusters on 10.6 (LTS, supported). According to the documentation, when upgrading Galera clusters, skipping major versions is not supported. Could the project either bring these versions back, or decide that skipping major versions is supported for Galera and update the documentation? Met vriendelijke groeten, William Edwards

4 3

Text Columns encoding
by Ruben Safir 23 Nov '23

23 Nov '23

Years ago I dumped the text of emails directly into the database into a text column message | text | YES | | NULL I pulled it out and displayed it on the website with a simple select like select message from postings where id = 553671 using DBI and DBD Much of the email now ends up like binary junk like this one: http://www.mrbrklyn.com/docs/file.txt I've tried decoding it with pearl with no success. Obvously it is textwidth set someout and has \ inserted in the output. Is there a fix to this? -- So many immigrant groups have swept through our town that Brooklyn, like Atlantis, reaches mythological proportions in the mind of the world - RI Safir 1998 http://www.mrbrklyn.com DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002 http://www.nylxs.com - Leadership Development in Free Software http://www2.mrbrklyn.com/resources - Unpublished Archive http://www.coinhangout.com - coins! http://www.brooklyn-living.com Being so tracked is for FARM ANIMALS and extermination camps, but incompatible with living as a free human being. -RI Safir 2013

2 1

Redo log and tablespace flushing
by Gordan Bobic 20 Nov '23

20 Nov '23

I have a question that may seem somewhat obscure, but what I really want to know is how the disk flushing and crash safety work. Do tablespace commits get explicitly flushed during normal runtime operation? If we have a write that successfully commits to the redo log and to the binlog, but the tablespace loses, say, 5 seconds worth of commits in an unclean shutdown, would crash recovery deal with it? Is replaying the redo log followed by binlog based recovery sufficient to put the tablespace(s) into a consistent state even if the redo+binary logs are in terms of on-disk state a few seconds ahead of the tablespaces? On other words, provided that write ordering is preserved (ordering as guided by flush calls), can I do the equivalent of LD_PRELOAD=libeatmydata on the tablespace operations safely as long as the redo and binary logs are fsync()-ed reliably?

3 10

centos7 mariadb5.5 -> MariaDB10.3
by Marc 17 Nov '23

17 Nov '23

Ever since I switched from the default included centos mariadb to your repo. The web applications are getting ~1 once a week connection refused or so. I have already scheduled a cron job to restart your server. But still that is not enough. In the previous 10 years or so I never had such issue nor do I have such issues with postgresql. Nothing is in the log file, only the restart. Should such things not be logged by default? How to configure that issues resulting in refusing access or being unable to give access are logged?

2 7

When using mariadb-10.3.38, after upgrading Openssl from 1.1.1 to 3.1 strange compilation errors occur
by Ma Zhenhua 16 Nov '23

16 Nov '23

Hi Team, When using mariadb-10.3.38, after upgrading Openssl from 1.1.1 to 3.1 strange compilation errors occur. /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_error' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_fetch_row' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_num_rows' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_select_db' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_num_fields' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_real_connect' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_close' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_init' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_free_result' /xxxx/bin/ld: ../build/lib/libyyyy.so: undefined reference to `server_mysql_store_result' collect2: error: ld returned 1 exit status make[1]: *** [../rules.Make:461: cli] Error 1 libyyyy.so uses shared library libmysqlclient.so and both of them use mysql_xxxx. I don't know why prefix "server_" is added. I found a simliar issue where valgrind is used. But in my scenario valgrind is not used. https://jira.mariadb.org/browse/MDEV-28958?page=com.atlassian.jira.plugin.s… Any constructive suggestions are welcomed. Many thanks in advance. Regards, Allen

2 1

How do I get detailed TLS debugging information?
by Nick Lockheart 01 Nov '23

01 Nov '23

Replication master: Server version: 10.5.21-MariaDB-0+deb11u1-log Debian 11 Replication slave: Server version: 10.5.21-MariaDB-0+deb11u1 Debian 11 When `require_secure_transport = on` is enabled on the master, I get: "error reconnecting to master 'user@domain.com:3306' - retry-time: 10 maximum-retries: 86400 message: Access denied for user 'user'@'domain.com' (using password: YES)." If I turn `require_secure_transport` off, replication works. So the error in the log *looks* like an authentication failure message, but it's clearly a TLS issue. journalctl -fu mariadb.service just shows the same: "Access denied for user 'user'@'domain' (using password: YES)" How can I get verbose messages about what is happening with TLS (why is it being rejected)?

3 5

Speeding up a query: how to send 3 million rows faster?
by Ivan Krylov 01 Nov '23

01 Nov '23

Dear MariaDB users, (I've asked the same question at <https://dba.stackexchange.com/q/332557>, so feel free to ignore this one if you've already seen the other.) Our scientific application needs to store and query fundamental parameters for a number of molecules. There are 2 to 28 million rows per molecule, but the number of molecules is expected to stay small (currently 4). Here's our schema: CREATE TABLE `mol_trans` ( `species_id` int(11) DEFAULT NULL, `wl_vac` double DEFAULT NULL, `upper_id` int(11) DEFAULT NULL, `lower_id` int(11) DEFAULT NULL, `prob` double DEFAULT NULL, `flag` tinyint(4) DEFAULT NULL, KEY `spid_flag_wl` (`species_id`,`flag`,`wl_vac`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci PARTITION BY LIST (`species_id`) (PARTITION `CaO` VALUES IN (6115) ENGINE = InnoDB, PARTITION `CN3` VALUES IN (6121) ENGINE = InnoDB, PARTITION `CN2` VALUES IN (6119) ENGINE = InnoDB, PARTITION `AlO` VALUES IN (6109) ENGINE = InnoDB) (The partitions are here to make it easier to drop a whole molecule if needed, which would otherwise be a painful large DELETE.) I'm using the admittedly old MariaDB 10.3.39 from Debian Buster for tests (connecting via the UNIX domain socket using the command line client), but we've been seeing the same problem on MySQL 5.6 and MariaDB 10.11 on Windows 10. The following query currently takes 1m 7s on my computer, as measured by running `time echo "$QUERY" | mysql "$DATABASE" >/dev/null`: select mtr.prob, mtr.lower_id, mtr.upper_id from mol_trans mtr where ( mtr.species_id=6115 and mtr.wl_vac > 766.0 and mtr.wl_vac < 883.0 and mtr.flag = 1 ) order by mtr.wl_vac; It needs to return 3024559 rows. The query seems to be using the right index, judging by the EXPLAIN output: id: 1 select_type: SIMPLE table: mtr type: ref possible_keys: spid_flag_wl key: spid_flag_wl key_len: 7 ref: const,const rows: 14158123 Extra: Using where With SET PROFILING=ON and the query cache disabled, the single operation with a duration of >1 second in the 23-row profile is "Sending data": Status: Sending data Duration: 66.712277 CPU_user: 66.436978 CPU_system: 0.279932 Context_voluntary: 2156 Context_involuntary: 6964 Block_ops_out: 0 Messages_sent: 0 Messages_received: 0 Page_faults_major: 0 Page_faults_minor: 0 Swaps: 0 Source_function: <unknown> Source_file: sql_parse.cc Source_line: 6086 Is there a way to spend less time sending data? I know the query can in theory run faster because it takes less than 10 seconds on SQLite and PostgreSQL, but if we switched our application from database engine to database engine every time we started having performance problems, we would be very far from done by now. I also know that MariaDB can be very fast at transferring data because it takes me only a few minutes more to download the whole database dump. -- Best regards, Ivan

5 13

INSERT ON DUPLICATE UPDATE and primary auto_increment key behaviour
by Richard Ellerbrock 31 Oct '23

31 Oct '23

I have the following table: CREATE TABLE `events_active_test` ( `timestamp` TIMESTAMP NOT NULL, `timestamp_earliest` TIMESTAMP NOT NULL, `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `source` CHAR(50) NOT NULL COLLATE 'utf8mb3_general_ci', `repeats` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '1', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `source` (`source`) USING BTREE, INDEX `timestamp` (`timestamp`) USING BTREE, INDEX `timestamp_earliest` (`timestamp_earliest`) USING BTREE ) COLLATE='utf8mb3_general_ci' ENGINE=InnoDB ; Then I insert some test rows (do this 5 times): INSERT INTO events_active_test (timestamp,timestamp_earliest,source) VALUES (NOW(),NOW(),'test') ON DUPLICATE KEY UPDATE repeats=repeats+1,timestamp=NOW(); When I then look at the table id = 1 and repeats = 5 as expected Now I insert a non duplicate key (test2): INSERT INTO events_active_test (timestamp,timestamp_earliest,source) VALUES (NOW(),NOW(),'test2') ON DUPLICATE KEY UPDATE repeats=repeats+1,timestamp=NOW(); The new row now has id = 6 and repeats = 1. This really baffled me as it appears that when using INSERT ON DUPLICATE UPDATE with a primary auto_increment column the auto_increment is increased even if the INSERT fails and the UPDATE occurs. In my case the majority of my INSERT's are duplicates causing massive jumps and gaps in the auto_increment numbers. Is this correct behaviour? Also is there an overhead of having to update the tables auto_increment metadata counter unnecessarily for every failed insert? -- Richard

3 2

Setting a debugging environment
by Gingko 10 Oct '23

10 Oct '23

Hello. This is my first message here, and I don't know if there is any usage about self introducing. I am trying to setup a comfortable developer environment for MariaDB, and especially a debugging environment, on a Linux Ubuntu box (using gdb or ddd, or gdbgui…). This is not obvious as all of them seems to have issues. Anyway, I tried to follow the instructions from that page: https://mariadb.com/kb/en/get-build-and-test-latest-mariadb-the-lazy-way/ But there is something that I am wondering: If I want to see the contents of some variables using these debuggers, is it normal that I very often get the following message? |<optimized out>| I understand what that means, but I am actually trying to debug code compiled with the following options: |-DCMAKE_BUILD_TYPE=Debug| Shouldn't all optimizations have been disabled when compiling in debugging mode? Regards, Gingko

3 2

InnoDB durability
by Federico Razzoli 29 Sep '23

29 Sep '23

Recently Laurynas commented this bug and he brought it up on social media: https://bugs.mysql.com/bug.php?id=75519 I was surprised to read thar, from his comments, this 5.6 bug is still in MySQL 8.0. Did anyone test whether it is in MariaDB too? If so, which versions? Thanks, Federico -- Federico Razzoli Vettabase Ltd director & founder, Database Consultant https://vettabase.com federico.razzoli(a)vettabase.com +44 7739 427279 Telegram: federico_razzoli Skype: fede.razzoli The information in this e-mail and any attachments may be confidential. If you received it by mistake, please inform the sender. Unless you are the intended recipient or his/her representative you are not authorised to, and must not, read, copy, distribute, use or retain this message or any part of it.

3 5

RAM/Swap Usage
by roughnecks 14 Sep '23

14 Sep '23

Hello, first time here :) I've got a single db for roundcube and yet after every couple days my swap for mariadb reaches 200MB and over. To be noted that I'm the sole roundcube's user and that even myself prefer using thunderbird, so I only access roundcube from time to time. Is this normal? I'm running debian stable, upgraded from oldstable when it came out and I remember that in bullseye ram usage was just 50MB or so. Thanks, have a nice day. -- roughnecks

3 10

Increasing RAM usage until oom
by Marco Dickert - evolver group 07 Sep '23

07 Sep '23

Hi folks, we experience a RAM issue with MariaDB (version 10.5.19-MariaDB-0+deb11u2-log) on a standard Debian bullseye system. The problem is that over time MariaDB uses more and more RAM, until the kernel's oom-killer terminates it. The server is a VMware guest with 8G RAM, 2G swap and 4 cores. The most important parameter, innodb_buffer_pool_size, is set to 2G. I know this is quite low for this system, but I want to have proof that MariaDB consumes defininitely more RAM than allowed. According to the calculation in this comment [1], the RAM usage shouldn't exceed 4.2G. Here is the result, including the raw configuration values: ``` MariaDB [(none)]> SELECT ROUND( -> ( @@GLOBAL.key_buffer_size -> + @@GLOBAL.query_cache_size -> + @@GLOBAL.tmp_table_size -> + @@GLOBAL.innodb_buffer_pool_size -> + @@GLOBAL.innodb_log_buffer_size -> + @@GLOBAL.max_connections * ( -> @@GLOBAL.sort_buffer_size -> + @@GLOBAL.read_buffer_size -> + @@GLOBAL.read_rnd_buffer_size -> + @@GLOBAL.join_buffer_size -> + @@GLOBAL.thread_stack -> + @@GLOBAL.binlog_cache_size) -> ) / 1024 / 1024, 1) `total MB`; +----------+ | total MB | +----------+ | 4220.0 | +----------+ 1 row in set (0,001 sec) MariaDB [(none)]> SELECT @@GLOBAL.key_buffer_size, @@GLOBAL.query_cache_size , @@GLOBAL.tmp_table_size, @@GLOBAL.innodb_buffer_pool_size , @@GLOBAL.innodb_log_buffer_size, @@GLOBAL.max_connections , @@GLOBAL.sort_buffer_size, @@GLOBAL.read_buffer_size , @@GLOBAL.read_rnd_buffer_size, @@GLOBAL.join_buffer_size , @@GLOBAL.thread_stack, @@GLOBAL.binlog_cache_size \G 1 row in set (0,000 sec) *************************** 1. row *************************** @@GLOBAL.key_buffer_size: 10485760 @@GLOBAL.query_cache_size: 536870912 @@GLOBAL.tmp_table_size: 16777216 @@GLOBAL.innodb_buffer_pool_size: 2147483648 @@GLOBAL.innodb_log_buffer_size: 134217728 @@GLOBAL.max_connections: 512 @@GLOBAL.sort_buffer_size: 2097152 @@GLOBAL.read_buffer_size: 131072 @@GLOBAL.read_rnd_buffer_size: 262144 @@GLOBAL.join_buffer_size: 262144 @@GLOBAL.thread_stack: 299008 @@GLOBAL.binlog_cache_size: 32768 ``` However, MariaDB uses up to 5.4G so far. And it keeps increasing. Last time the oom-killer was invoked, MariaDB used 10G total, and 7.4G rss (given in 4k pages): ``` [Mi Jul 26 16:01:06 2023] Tasks state (memory values in pages): [Mi Jul 26 16:01:06 2023] [ pid ] uid tgid total_vm rss pgtables_bytes swapents oom_score_adj name ... [Mi Jul 26 16:01:06 2023] [3462912] 10000 3462912 2753196 1895256 20762624 472509 0 mariadbd ... [Mi Jul 26 16:01:06 2023] oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/system.slice/mariadb.service,task=mariadbd,pid=3462912,uid=10000 [Mi Jul 26 16:01:06 2023] Out of memory: Killed process 3462912(mariadbd) total-vm:11012784kB, anon-rss:7581024kB, file-rss:0kB,shmem-rss:0kB, UID:10000 pgtables:20276kB oom_score_adj:0 ``` The MariaDB knowledge base [2] says that the problem probably correlates with one of the following options: ``` +----------------------------+----------------------+----------------+ | variable_name | default_value | variable_value | +----------------------------+----------------------+----------------+ | INNODB_IO_CAPACITY_MAX | 18446744073709551615 | 2000 | | QUERY_CACHE_LIMIT | 1048576 | 67108864 | | MAX_CONNECTIONS | 151 | 512 | | TABLE_OPEN_CACHE_INSTANCES | 8 | 7 | | LONG_QUERY_TIME | 10.000000 | 5.000000 | | KEY_BUFFER_SIZE | 134217728 | 10485760 | | QUERY_CACHE_SIZE | 1048576 | 536870912 | | INNODB_LOCK_WAIT_TIMEOUT | 50 | 80 | | INNODB_LOG_BUFFER_SIZE | 16777216 | 134217728 | | HOST_CACHE_SIZE | 128 | 654 | | INNODB_FT_MIN_TOKEN_SIZE | 3 | 2 | | WAIT_TIMEOUT | 28800 | 600 | | INNODB_FAST_SHUTDOWN | 1 | 0 | | INNODB_LOG_FILE_SIZE | 100663296 | 1073741824 | | THREAD_POOL_SIZE | 8 | 4 | | INNODB_BUFFER_POOL_SIZE | 134217728 | 2147483648 | | MAX_ALLOWED_PACKET | 16777216 | 268435456 | | BACK_LOG | 150 | 254 | +----------------------------+----------------------+----------------+ ``` I can't see what setting could probably cause the issue. Some values are Debian defaults. Most of them we set intentionally. Can anyone give a hint on where to look for? Do you need further information? Thanks for any help. [1] https://serverfault.com/a/1020847 [2] https://mariadb.com/kb/en/mariadb-memory-allocation/#how-to-troubleshoot-ou… Regards, Marco

3 4

Odp: Re: Re: Rocksdb as a replacement for toku?
by pslawek83 25 Aug '23

25 Aug '23

Hi Marko, thanks for the response ... so for the DOCS part > h mariadb.com ttps://mariadb.com/kb/en/innodb-page-compression/ > When InnoDB page compression is used, InnoDB may still write the compressed page to the tablespace file with the original size of the uncompressed page, which would be equivalent to the value of the mariadb.com innodb_page_size system variable. This is done by design, because when InnoDB's I/O code needs to read the page from disk, it can only read the full page size. However, this is obviously not optimal. > This punch hole technique allows InnoDB to read the compressed page from disk as the full page size, even though the compressed page really takes up less space on the file system. So as far as i understand if compression is enabled the server will write eg. 40% utilized page which will later be decompressed when read, but this compression/decompression won't have any advangage if there's no punch hole support. The only difference will be that the block will be filled with zeros for the empty space at the end. Or is it possible for PAGE_COMPRESSION to still be used without punch hole (just not that effective for example)? Also tested this row compression, and yes page compression seems much better. Is it possible to get rid of punch hole by just re-mapping the pages inside these innodb files? I was thinking about this... if it's possible to change the data structure to divide the file into 512b blocks while the beginning of each block will mark page number (0xffffffff for blocks which are part of previous page). To increase page size we'd need to add another page with same number at the end of the file, later continuous recycled blocks can be added to a list and used to store smaller pages. To make this atomic, first whole block would be allocated and written with 0xfffffffe as a dummy id and then this placeholder id could be replaced with real block number). The problem with this could be that the file would get 100% fragmented over time (but probably will work ok with NVME). The mapping could be read back by reading whole datafile during server start and it'd need 125MB of memory for 1TB of compressed data to store the mapping in memory for fast access. What you thinik? Would it be easy to add it on top of current code (if there's any API maybe, so for example i can do this) and would it even make sense? Best, Slawomir. Dnia 21 sierpnia 2023 07:08 Marko Mäkelä < marko.makela(a)mariadb.com > napisał(a): On Sun, Aug 20, 2023 at 6:12 PM pslawek83 via discuss <discuss(a)lists.mariadb.org> wrote: Thanks everyone for the answers. Tested this PAGE_COMPRESSION a bit and make some reading. Not sure if I fully understand how that works... is that correct? 1. Impossible without punch hole as if we don't have it the server will just write partially used pages which will take full page size anyway. But in docs it seems it described its use with punch hole disabled. What's the reason to still have page compression without punch hole? Can you please point to the misleading part of the documentation so that it can be corrected? 2. After enabling compression each page needs to be trimmed using punch hole, so if we have 1 TB database and 64k blocks, the database files will have over 15 million fragments (1000000000000/64000) That is correct. So if we get like 50% compression and then we'll be able to put another 1TB of data into these holes we'll be having close to 31 million file fragments. I'm not sure if under such conditions the FS will be able to even allocate space efficiently anymore, copy these files, etc. Or im not getting something? For what it is worth, around the same time when the page_compressed was being developed for MariaDB Server 10.1, something similar was being worked on at Oracle, I think for MySQL 5.7. To my understanding, the same hardware partner worked with both companies. The obvious answer to smalldatum.blogspot.com https://smalldatum.blogspot.com/2015/10/wanted-file-system-on-which-innodb.… would have been en.wikipedia.org https://en.wikipedia.org/wiki/Fusion-io_NVMFS if it had worked without kernel panics or existed in a mainline kernel. I don’t even know if it ever was open sourced. 3. I was testing compression of ~1.5GB table with nice results on default 16kb page size. One thing i noticed is that l was always getting exactly the same compressed size using `du`. However the table read/copy times were different from 40s for uncompressed to 80-400 seconds depending on algo. Is that possible that is because page size is too low? As far as I remember, most compression algorithms that are derivatives of the LZ77 family have an input buffer size of 32 KiB. From that point of view, it would seem to make sense to use innodb_page_size=32k or innodb_page_size=64k instead of the default. Furthermore, I think that it was a mistake to implement bzip2 support at all. That algorithm would work with much larger buffer sizes. 4. Myrocks seems to be very slow compared to compressed inno. Was also reading docs related to it. For its usecase is it more suitable for storing large rows rather than many small rows? To my understanding, the LSM trees that MyRocks is built on work best in an insert-only workload. On top of LSM trees, MyRocks implements some caches and Bloom filters. If you have few updates or deletes and most reads are accessing recently inserted data, it could work. 5. I remember reading that some innodb compression is going to be obsolete. Is that row compression? Or there are no plans to obsolete any compression model? I wanted to deprecate and remove the ROW_FORMAT=COMPRESSED that I designed and implemented in the InnoDB Plugin for MySQL 5.1 based on some high level ideas of Heikki Tuuri between 2005 and 2007. jira.mariadb.org https://jira.mariadb.org/browse/MDEV-22367 was the first step towards that. Based on community feedback, this won’t be happening. Even after some data structure cleanup that took place in MariaDB Server 10.6, supporting the format incurs an overhead of some 32 to 40 bytes per buffer page descriptor, which is not insignificant. Removing both ROW_FORMAT=COMPRESSED and the adaptive hash index would shrink the block descriptor to 80 bytes from its current 10.6 size of 160 bytes. The write performance with ROW_FORMAT=COMPRESSED should have been degraded when I implemented the easier-to-parse log record format in MDEV-12353 (MariaDB Server 10.5). There are open bugs about ROW_FORMAT=COMPRESSED, such as jira.mariadb.org https://jira.mariadb.org/browse/MDEV-31574. I would say that the InnoDB ROW_FORMAT=COMPRESSED seemed like a good idea back when it was implemented, when HDD storage was the only practical option. Just like the InnoDB change buffer, which I removed in MariaDB Server 11.0 after having to deal with too many cases of corruption: fosdem.org https://fosdem.org/2023/schedule/event/innodb_change_buffer/ With best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc

3 3

Re: Rocksdb as a replacement for toku?
by pslawek83 22 Aug '23

22 Aug '23

Thanks everyone for the answers. Tested this PAGE_COMPRESSION a bit and make some reading. Not sure if I fully understand how that works... is that correct? 1. Impossible without punch hole as if we don't have it the server will just write partially used pages which will take full page size anyway. But in docs it seems it described its use with punch hole disabled. What's the reason to still have page compression without punch hole? 2. After enabling compression each page needs to be trimmed using punch hole, so if we have 1 TB database and 64k blocks, the database files will have over 15 million fragments ( 1000000000000/64000) So if we get like 50% compression and then we'll be able to put another 1TB of data into these holes we'll be having close to 31 million file fragments. I'm not sure if under such conditions the FS will be able to even allocate space efficiently anymore, copy these files, etc. Or im not getting something? 3. I was testing compression of ~1.5GB table with nice results on default 16kb page size. One thing i noticed is that l was always getting exactly the same compressed size using `du`. However the table read/copy times were different from 40s for uncompressed to 80-400 seconds depending on algo. Is that possible that is because page size is too low? 4. Myrocks seems to be very slow compared to compressed inno. Was also reading docs related to it. For its usecase is it more suitable for storing large rows rather than many small rows? 5. I remember reading that some innodb compression is going to be obsolete. Is that row compression? Or there are no plans to obsolete any compression model? Thanks. Dnia 16 sierpnia 2023 16:08 Gordan Bobic via discuss < discuss(a)lists.mariadb.org > napisał(a): Larger pages will yield higher compression ratios, as will zstd (slightly slower than lz4). Mileage varies, but on some of my databases, between the 64KB pages (zfs recordsize) and zstd, my compression ratios approximately doubled, from about 1.8:1 to a little under 4:1. This will all heavily depend on the type of data you are storing, though. zstd has adjustable compression, so if you have tons of CPU to spare, you could turn it up from the default of 3 (probably optimal cost/size compromise) to high teens for a compression boost. On Wed, 16 Aug 2023, 14:34 Reinis Rozitis, < r(a)roze.lv > wrote: > From: Gordan Bobic via discuss < discuss(a)lists.mariadb.org > > > One of the best compromises is to configure InnoDB with 64KB pages, put it on ZFS with recordsize=64K, and enable zstd compression on ZFS. Sorry for hijacking the thread a bit. After the discontinuation of tokudb I chose the innodb+zfs route (bad previous/early experience with rocks, also million knobs to adjust for your workload etc) but at that time didn't think of increasing the Innodb page size to 64KB - do you have any (even rough) numbers of 16 vs 64KB (on zfs)? Also, what would be the benefit of zstd vs lz4? Higher compression ratio? rr _______________________________________________ discuss mailing list -- discuss(a)lists.mariadb.org To unsubscribe send an email to discuss-leave(a)lists.mariadb.org

3 2

Running events on same time
by kpesio＠gmail.com 17 Aug '23

17 Aug '23

Hi. Could someone confirm in some way about running multiple events at same time. Documentation at https://mariadb.com/kb/en/automating-mariadb-tasks-with-events/ states: "The event scheduler runs as a single thread. If any event is supposed to start while another event is running, its execution will be skipped." I have simulated events triggering at same time, running with delays to get all kinds of overlapping schedules, writing to same table from the event and not missed a single one. The information_schema.events tables column last_executed has - depending on scheduled time - valid values and I can see in the processlist events running at same time. Version I'm running is 10.6.10 on Linux. Tried to cross check against the MySQL documentation but couldn't find anything about this there. So what situation does the documentation refer to or it this something fixed long time ago?

3 5

Rocksdb as a replacement for toku?
by pslawek83 16 Aug '23

16 Aug '23

Hi Guys, i'm looking for tokudb replacement and i'm again thinking about rocksdb. Is it mature enough to be used as an engine for statistical tables (several billions rows, around 2TB in size?). - Is it (will it be?) possible to have something like single-file-per-table (or single dir per table?) - Is it shared per all tables, so all tables are still using single set of files (can single crashed table/row lead to all tables being unusable?) - Will partitioning the table have performance benefits like with other engines? Can I do instant TRUNCATE PARTITION for easy data retention? Thanks

5 6

Query on Building Maria DB for different Compression Algorithm
by Raj, Rahul 10 Aug '23

10 Aug '23

[AMD Official Use Only - General] Hi, I am building MariaDB from source code and have queries on linking of compression algorithm. Before building the source code of MariaDB, I am exporting the path for SNAPPY or LZMA or the algorithm which is currently it supports. Then I build the code with - cmake -B . -DCMAKE_INSTALL_PREFIX=$PWD/maria_bin -DPLUGIN_MROONGA=NO -DPLUGIN_ROCKSDB=NO cmake --build . -j cmake -install . It compiled the code and installed in the specified location. When I checked the lib/plugin folder it has generated, provider_lzma.so or provider_snappy.so files and when I did ldd on that .so file, it has given the path from where it is fetching the liblzma.so.1 or libsnappy.so.1 file. Query: 1. The path which ldd shows for provider_lzma.so or provider_snappy.so is taking form default path, and it is not from the path which I am exporting the library and bin path - could you please explain me the correct steps. 2. Is there any way or any Flag we can set to switch between different compression method during build time itself, and in which log we can check it has linked properly. 3. How to benchmark the linked compression method and how we can see it is using the linked compression method from the custom path of .so files. Kindly help on above query. Thanks Rahul Raj

4 9

Reduce logging output from mariabackup
by Tom Worster 03 Aug '23

03 Aug '23

Is there an option to reduce the volume of information mariabackup writes to the console as it does its magic? Some of the log lines are very welcome, for example I like the first three Aug 03 11:35:03 bhs6 v2_backup_maria.sh[1291066]: [00] 2023-08-03 11:35:03 Connecting to MariaDB server host: localhost, user: mariabackup, password: set, port: not set, socket: /run/mysqld/mysqld.sock Aug 03 11:35:03 bhs6 v2_backup_maria.sh[1291066]: [00] 2023-08-03 11:35:03 Using server version 10.6.14-MariaDB-1:10.6.14+maria~deb11-log Aug 03 11:35:03 bhs6 v2_backup_maria.sh[1291066]: mariabackup based on MariaDB server 10.6.14-MariaDB debian-linux-gnu (x86_64) and the last two are really great, like a gift from god Aug 03 11:36:34 bhs6 v2_backup_maria.sh[1291196]: [00] 2023-08-03 11:36:34 Last binlog file ./bhs8-bin.001617, position 952620696 Aug 03 11:36:34 bhs6 v2_backup_maria.sh[1291196]: [00] 2023-08-03 11:36:34 completed OK! But there's about 500 between these every hour in the journal and most of them are quite boring like this Aug 03 11:36:31 bhs6 v2_backup_maria.sh[1291066]: [01] 2023-08-03 11:36:31 Copying ./sys/sys_config.frm to /pool2/mariabackup/sys/sys_config.frm Aug 03 11:36:31 bhs6 v2_backup_maria.sh[1291066]: [01] 2023-08-03 11:36:31 ...done Or stuff I do not understand and suspect I probably don't need to undersand like Aug 03 11:35:03 bhs6 v2_backup_maria.sh[1291066]: [00] 2023-08-03 11:35:03 uses posix_fadvise().

3 5

Expected release date MariaDB 10.6.15
by William Edwards 01 Aug '23

01 Aug '23

Hi, Is MariaDB 10.6.15 expected to be released this week? According to Jira[1], it is 4 days overdue. This version will include a fix that I'm waiting for. Met vriendelijke groeten, William Edwards [1]: https://jira.mariadb.org/projects/MDEV/versions/29013

3 4

Help us redesign the mariadb.org website
by Ian Gilfillan 17 Jul '23

17 Jul '23

We are currently re-designing the mariadb.org website and you are invited to participate in this process. The interactive study shouldn't take longer than 10 to 15 minutes to complete and will close at midnight on Thursday 20 July 2023 (UTC-8). Access the study here: (no registration or login is required) https://study.uxtweak.com/cardsort/gkeYA0HPSXawp6YNsjllU The activity involves a method called Card Sorting, which will influence how we design the information architecture of the website: its structure, navigation and the findability of content and functionality. Your inclusion in this effort is important and will help to inform and assist us in designing an intuitive, usable and effective future website. Many thanks from the website redesign team MariaDB Foundation

1 0

Advice on Replication
by Andrew Gable 13 Jul '23

13 Jul '23

5 4

Re: Sandboxing mariabackup
by Tom Worster 07 Jul '23

07 Jul '23

Hi Daniel, That part is looking good for now but I gotta do fire drills validate the whole shebang. There's something almost blog worthy about this. mysqldump started as a handy import export utility and ended up being an exquisite point-in-time logical SQL snapshot tool. Now xtrabackup and mariabackup are so astonishingly fast at restoration/recovery they can't be ignored. But they are terrifying! They operate directly on the datadir. mysqldump is a nice friendly SQL client like any other. Does that make sense? An SQL client can ruin the DB as well as anything else if it has the right permissions (pun!). Hence subtracting permission to write datadir from the mariabackup process feels like a step to parity. Tom On 7/6/2023 6:52:17 PM, "Daniel Black" <daniel(a)mariadb.org> wrote: >Glad you worked it out. > >I did mean to write this onlist a while ago. Got saved a draft message >and I forgot about it. > >On Thu, Jul 6, 2023 at 10:47 PM Tom Worster <fsb(a)thefsb.org> wrote: >> >> On 7/6/2023 7:14:50 AM, "Daniel Black" <daniel(a)mariadb.org> wrote: >> >> >> I prefer to sandbox systemd services with ProtectSystem=strict. >> >> Especially so when User=root. >> > >> >Should be able to use User=mysql (or whatever the mariadb.service is >> >running as), provided the backup directory is also mysql: owned. >> > >> Thanks Daniel. >> >> Yes. I discovered that in subsequent testing. >> spin >> And I found that only target-dir and tmpdir need to be read-write. I >> forget what made me think that wasn't enough. >> >> To run as mariabackup as mysql user but with read-only access to datadir >> I separated the mariabackup part of the overall hourly backup script >> into its own service unit: >> [Unit] >> Description=mariabackup DBs >> OnFailure=unit-status-mail(a)%n.service unit-status-pushover(a)%n.service >> [Service] >> User=mysql >> Type=oneshot >> ProtectSystem=strict >> ReadWritePaths=/pool2/mariabackup /pool2/mariabackup_tmpdir >> ExecStart=/path/to/backup_maria.sh >> >> This is /path/to/backup_maria.sh. It's on a replica and the test of prev >> and new size are a check (among others) that the replica isn't stuck. >> #!/bin/bash >> prev_backup_size=`du -s /pool2/mariabackup` >> rm -rf /pool2/mariabackup/* >> mariabackup --backup --target-dir=/pool2/mariabackup/ >> --tmpdir=/pool2/mariabackup_tmpdir --user=mariabackup --password=__ >> mariabackup --prepare --target-dir=/pool2/mariabackup/ >> --tmpdir=/pool2/mariabackup_tmpdir >> new_backup_size=`du -s /pool2/mariabackup` >> if [ "$prev_backup_size" = "$new_backup_size" ] >> then >> echo WARNING: Consecutive mariabackup sizes unchanged >> # FIXME send alerts >> fi >>

2 2