- discuss - lists.mariadb.org

[Maria-discuss] mysql.innodb-table-stats-and mysql.innodb-index-stats are not updating properly
by ragul rangarajan 21 Apr '23

21 Apr '23

Hello all, I am facing a weird issue with my DB table where its Index and table stats not getting updated with values automatically in MariaDB 10.6.7 The table has more than a billion records. On querying the stats of the table, I could not see any stats for the table that begin automatically calculated until we do a mysql restart or ANALYZE TABLE. Once triggered we can able to see the index values are calculated and updated in this table 127.0.0.1:3307> SELECT * FROM mysql.innodb_table_stats ;+---------------+-------------------------------------+---------------------+---------+----------------------+--------------------------+| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |+---------------+-------------------------------------+---------------------+---------+----------------------+--------------------------+| DB | TableStats#P#p2023_04_11_13_00_00 | 2023-04-11 12:00:11 | 0 | 1 | 0 || DB | TableStats#P#p2023_04_11_14_00_00 | 2023-04-11 13:00:10 | 0 | 1 | 0 || DB | TableStats#P#p2023_04_11_15_00_00 | 2023-04-11 14:00:04 | 0 | 1 | 0 || DB | TableStats#P#p2023_04_11_16_00_00 | 2023-04-11 15:00:04 | 0 | 1 | 0 |+---------------+-------------------------------------+---------------------+---------+----------------------+--------------------------+ 127.0.0.1:3307> SELECT * FROM mysql.innodb_index_stats;+---------------+-------------------------------------+------------+---------------------+--------------+------------+-------------+------------------------------------------------------------+| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |+---------------+-------------------------------------+------------+---------------------+--------------+------------+-------------+------------------------------------------------------------+| DB | TableStats#P#p2023_04_11_16_00_00 | PRIMARY | 2023-04-11 15:00:04 | n_diff_pfx01 | 0 | 1 | AgentId || DB | TableStats#P#p2023_04_11_16_00_00 | PRIMARY | 2023-04-11 15:00:04 | n_diff_pfx02 | 0 | 1 | AgentId,rackNr || DB | TableStats#P#p2023_04_11_16_00_00 | PRIMARY | 2023-04-11 15:00:04 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index || DB | TableStats#P#p2023_04_11_16_00_00 | PRIMARY | 2023-04-11 15:00:04 | size | 1 | NULL | Number of pages in the index |+---------------+-------------------------------------+------------+---------------------+--------------+------------+-------------+------------------------------------------------------------+ As per the documentation https://mariadb.com/kb/en/innodb-persistent-statistics/, enabling *innodb_stats_auto_recalc* parameter should update the index value automatically which was not happening for me even though the changes are 10% of the row got affected within few days. The same was working for other DB table which is of lower load. Is there any way to debug why the calculation was not updated properly with values or any way to reduce the sample size? *Thanks and Regards,* Ragul R

2 2

[Maria-discuss] DELETE query hanging on Statistics (10.6.12)
by mariadb＠Biblestuph.com 14 Apr '23

14 Apr '23

With this table: CREATE TABLE t1 ( label varchar(100) NOT NULL, dt datetime NOT NULL, success tinyint(1) NOT NULL, PRIMARY KEY (label,dt), KEY dt (dt), KEY success (success,dt) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC I have a DELETE query that has been stuck in Statistics for nearly an hour now: DELETE MyDB.t1 FROM MyDB.t1 WHERE dt <= '2023-02-21 08:06:46' AND dt >= '2023-02-21 08:03:48' This statement runs several times per hour to trim the table (and has for years). It has run fine against my 10.6.12 DB (this is a downstream replica DB) for a couple weeks now(when I upgraded the replica); but suddenly today produced the hang. Any ideas why? The table has nearly 15 million rows; but the rows are more or less evenly spaced across datetimes. Also I find that I cannot kill the query using KILL 12345; the KILL statement just comes back immediately without reporting an error and the statement continues. Is there no way to kill this statement without crashing the DB? Again, this is a replica statement that is executing. I had planned to stop the query, execute an ANALYZE TABLE t1 and then try it again, but I don't seem to be able to kill the query. Thank you! Dan

3 5

[Maria-discuss] Privilege Question
by Scott Canaan 06 Apr '23

06 Apr '23

We are on MariaDB 10.5.18. There is a requirement to send all syslog data to a central syslog server. In the past, we did it using a login called ITS_READ. It has limited privs on purpose, but used to be able to execute the SET GLOBAL statements that we needed. Those statements are: SET GLOBAL server_audit_output_type=SYSLOG; SET GLOBAL server_audit_logging=1; SET GLOBAL server_audit_syslog_facility=LOG_LOCAL2; SET GLOBAL server_audit_events="connect,table,query_ddl,query_dcl"; Now, it doesn't have the privs needed to do that. In looking online, I found that there is a "new" privilege (in MySQL 8), called system_variables_admin that allows the user to execute SET GLOBAL statements. However, when I try to grant that privilege, I get an error: MariaDB [(none)]> grant system_variables_admin to 'ITS_READ'@'localhost'; ERROR 1959 (OP000): Invalid role specification `system_variables_admin` It seems that the only other option is to grant the SUPER privilege, which we don't want to do. How do I get around this, or do I just tell the sysadmins they can't have the syslog data? Scott Canaan '88 Sr Database Administrator Information & Technology Services Finance & Administration Rochester Institute of Technology o: (585) 475-7886 | f: (585) 475-7520 srcdco(a)rit.edu<mailto:srcdco@rit.edu> | c: (585) 339-8659 CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

3 3

[Maria-discuss] Disable validation for `DATETIME` columns
by William Edwards 03 Apr '23

03 Apr '23

Hi, Is it possible to disable validation for `DATETIME` columns? Context: A customer uses the Europe/Amsterdam session time zone. Therefore, they cannot insert dates during the hour in which DST starts and ends. Using the UTC session time zone would solve the issue. However, they also use auto-converting `TIMESTAMP` columns. Switching to a different session time zone would cause issues with those. Relaxing constraints with `sql_mode=NO_ZERO_DATE,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES` does not suffice. With kind regards, William Edwards

2 3

[Maria-discuss] Column named 'row_number' gives error in 10.11
by Keith Edmunds 02 Apr '23

02 Apr '23

I have a table that includes an int column named row_number. This worked fine until I upgraded Debian from 11 to 12 which upgraded MariaDB from 10.5 to 10.11. I now get errors when referencing that column for updates. If I put backticks around the column name the update runs without error. Is this an expected behaviour change from 10.5 → 10.11? Example: CREATE DATABASE test; USE test; CREATE TABLE `kae` ( `id` int(11) NOT NULL, `row_number` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO `kae` (`id`, `row_number`) VALUES (1, 0); UPDATE kae SET row_number=12 WHERE id=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'row_number=12 WHERE id=1' at line 1

4 8

[Maria-discuss] Mounting tmpdir via NFS
by Simon Avery 29 Mar '23

29 Mar '23

Hello, I have a question that I've been struggling to get a clear answer to, or even any indication someone has done this before, and would appreciate any experience or thoughts. Are there any known issues with defining tmpdir on multiple servers to point to a single NFS-mounted network share? Would each instance co-habit peacefully and not try to change any files from another instance? Why do I want to do this? I have around 80 Centos/Rocky vms which run Mariadb 10.6.x. Due to the nature of the work they do, some of them require a significant amount of tmpdir (up to 75Gb) relatively briefly (once or twice a day for a few minutes) This growth is not always predictable, so generally the first I know about it is when Mariadb reports lowspace and the query stops. We then grow the /tmp partition that is defined as tmpdir and if we're quick enough, the query completes, but often the job dies. Then that extra space stays locked away forever, even if that server never tries to use that much tmpdir again. This does not feel efficient and I am pondering better ways of doing this. One such thought would be to have a single HA NFS share on our network, mounted onto each VM as something like /mariadb-tempdir, and set $tmpdir to it. In my mind, this would make a better use of one large allocation of space used by many things often, rather than lots of individual small to large sized bits of space used by one thing, rarely. The chance of two or more servers filling that space feels small, but if it happened, one would wait until space was cleared - which might actually happen in this shared scenario when it never does in a single-server situation. In my particular world, we have fast storage and a fast network. Speed tests on large and tiny files over NFS give surprisingly similar performance to writing to local vm disks, so I'm not worried about the overhead of NFS and even if it was, slow might still be preferred to full. Thank you, Simon

3 5

[Maria-discuss] MariaDB Online-DDL
by Rhys.Campbell＠swisscom.com 17 Mar '23

17 Mar '23

Hello All, I'm back working with MariaDB again after a break of some years. While InnoDB Online-DDL had appeared when I was working with it, it was still sometimes a challenge to manage schema changes for very large tables. https://mariadb.com/kb/en/innodb-online-ddl-overview/ Can anyone summarize how InnoDB stacks up for Online-DDL now? Is it comparable to MSSQL (which I found to be pretty good for tables in the many tens of GB)? Are workarounds like pt-online-schema-change and blue/green deployments still required? Are there any specific areas to watch out for? Any feedback appreciated. Cheers, Rhys

2 1

[Maria-discuss] can not kill process
by Radisson97＠gmx.de 17 Mar '23

17 Mar '23

hi i have a strange effect a simple insert got stuck (why ?) i tried to kill but no success, it is even marked as "killed" (see below) 1398867 kpc localhost kpc 1716 Killed INSERT INTO i can kill other processes- my system: 10.5.15-MariaDB engine aria 5.10.0-14-amd64 any ideas what may the problem ? (no usefull entries in any log, as you expected ) re,

3 4

[Maria-discuss] Optimized way to build MariaDB
by Otto Kekäläinen 12 Mar '23

12 Mar '23

Hi! I have spent a lot of time teaching various people how to build (and re-build) MariaDB. If you are interested to learn how I use entr + cmake --fresh + ninja + clang + ccache check out https://optimizedbyotto.com/post/quick-builds-and-rebuilds-of-mariadb-with-… - Otto

1 0

[Maria-discuss] MariaDB Connector C 3.1 ABI contract broken in latest release
by Otto Kekäläinen 04 Mar '23

04 Mar '23

Hello! After uploading MariaDB 10.3.38 in Debian I started seeing reports about libmariadb3 being broken for some consumers. Turns out that the function mysql_get_client_version() had a major behavior change in the latest MariaDB Connector C 3.1.20 which is embedded in the MariaDB 10.3 build. Details in PR https://github.com/mariadb-corporation/mariadb-connector-c/pull/219. Affected library consumers are listed in https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=1031863#86 Unfortunately it might be too late to fix this, the milk is already spilled. Could you please in future keep as the engineering policy that *libraries are not allowed to have backwards-incompatible changes* unless the soname/version is also bumped. You might add a new symbol in an existing library version without causing breakage in library consumers, but you cannot remove any symbols/functions, or change their behavior significantly. In this case you completely changed what version the library returns. Please acknowledge these base rules and be more diligent in the future to avoid risks. Also, if you want to introduce libmariadb4, please do it on MariaDB 11.0 or newer only, and don't change any ABI in existing stable releases of libmariadb3. Thanks!

1 0