June 2023 - discuss - lists.mariadb.org

[Maria-discuss] Galera 23.2.7 mariadb 5.5.33a msvc 1600 binaries available
by Yann Nayn 05 Dec '24

05 Dec '24

Binaries have been uploaded for both x86 and amd64 platforms. due to github file size limitations , the directories have been uploaded unarchived. for Win33 binaries , please checkout https://github.com/YannNayn/galera-mariadb-win32.git and for Win64(amd64) binaries (being uploaded/pushed): https://github.com/YannNayn/galera-mariadb-win64.git they are not recommended for a production environment. in the my.cnf file: _________ wsrep_provider=galera_smm.dll wsrep_sst_method=msqldump # percona xtrabackup will be tested soon _________ libevent and openssl(the provided binaries use respectively 2.1.3-alpha and 1.1.0-dev versions ) will be added next on github some limitations are stated on openssl website copied here for convenience: ____________ from http://www.openssl.org/ ____________ This software package uses strong cryptography, so even if it is created, maintained and distributed from liberal countries in Europe (where it is legal to do this), it falls under certain export/import and/or use restrictions in some other parts of the world. PLEASE REMEMBER THAT EXPORT/IMPORT AND/OR USE OF STRONG CRYPTOGRAPHY SOFTWARE, PROVIDING CRYPTOGRAPHY HOOKS OR EVEN JUST COMMUNICATING TECHNICAL DETAILS ABOUT CRYPTOGRAPHY SOFTWARE IS ILLEGAL IN SOME PARTS OF THE WORLD. SO, WHEN YOU IMPORT THIS PACKAGE TO YOUR COUNTRY, RE-DISTRIBUTE IT FROM THERE OR EVEN JUST EMAIL TECHNICAL SUGGESTIONS OR EVEN SOURCE PATCHES TO THE AUTHOR OR OTHER PEOPLE YOU ARE STRONGLY ADVISED TO PAY CLOSE ATTENTION TO ANY EXPORT/IMPORT AND/OR USE LAWS WHICH APPLY TO YOU. THE AUTHORS OF OPENSSL ARE NOT LIABLE FOR ANY VIOLATIONS YOU MAKE HERE. SO BE CAREFUL, IT IS YOUR RESPONSIBILITY. ________________________________________________ msvc 1600 redistributables SP1 need to be installed/available on the target machine: vcredist_x86.exe : http://www.microsoft.com/en-us/download/details.aspx?id=8328 vcredist_x64.exe : http://www.microsoft.com/en-us/download/details.aspx?id=13523 MSys binaries are provided and msqld.bat should then be used instead of mysqld.exe (e.g. mysld.bat --console --wsrep_cluster_address=gcomm:// ) in order to have a working sh shell(cygwin has been proved to do it as well) Please note that oqgraph is disabled (boost trunk is used , that is 1.55) Comments and suggestions are welcome.

3 2

Can we place a temporary table inside a stored function
by manpritsinghece＠gmail.com 29 Jun '23

29 Jun '23

Just need to know if we can place a temporary table inside the stored function ?

3 6

can you purge data older than 4 months old
by robert k Wild 29 Jun '23

29 Jun '23

hi all, i was just wondering i know about the "mysqlcheck -a and -o" ie to optimize and to analyse ie to cut it down in size but NOT to purge any data but is there like a purge option to delete all data older than 4 months old in the database thanks, rob -- Regards, Robert K Wild.

2 2

2023 MariaDB (Un)Conference and MariaDB Server Fest
by Ian Gilfillan 28 Jun '23

28 Jun '23

The MariaDB (Un)Conference, for MariaDB Server developers, and the MariaDB Server Fest, for anyone interested in MariaDB Server, will be taking place from 3 - 5 October as hybrid events, both in Helsinki and online. See https://mariadb.org/fest2023/ for details. The CfP for both events is available at https://mariadb.org/fest2023cfp/

1 0

mysql restore error
by robert k Wild 26 Jun '23

26 Jun '23

hi all, i successfully backed up the database /opt/aspera/common/mysql/bin/mysqldump -u root -p'password' aspera_console > /mnt/pixit/engineering/aspera_console/mysql_backups/20230623.sql then i try to restore it, first i delete the database and create the database /opt/aspera/common/mysql/bin/mysql -u root -p'password' drop database aspera_console; create database aspera_console; then run the restore command and i get the error /opt/aspera/common/mysql/bin/mysql -u root -p'password' aspera_console < /mnt/pixit/engineering/aspera_console/mysql_backups/20230623.sql ERROR 1305 (42000) at line 80504: FUNCTION aspera_console.report_user_id does not exist luckily this is not the live vm, its a clone of the live, so im messing around thanks, rob -- Regards, Robert K Wild.

2 4

Very large ibdata1 growth (335Gb) with 10.6.13, solved in 10.6.14 ?
by Simon Avery 20 Jun '23

20 Jun '23

Hi All, We run around 60 Mariadb 10.6 servers on EL in vms and varying workloads. Some time after they updated from 10.6.12 to 10.6.13, four of the busiest started showing higher disk usage than expected. Upon investigation, /var/lib/mysql/ibdata1 was exceptionally large, up to 335Gb, and was still growing at several Gb/day. Note: All servers have only run with "innodb_file_per_table" set and only have innodb engines for user DBs. I cobbled together some data collection just before I went on holiday and checked the graphs today. (Pic attached - if not stripped by the list software) [A picture containing screenshot, line, parallel Description automatically generated] Four machines growing, rest static. Two affected machines stopped growing a week ago - and on checking, Mariadb was upgraded from 10.6.13 to 10.6.14 on them and they were rebooted then. The two still growing are still running 10.6.13 My questions! 1. Am I right that this was a known bug in Mariadb and has been resolved with the .14 release? 2. The .14 release notes mention "InnoDB does not free UNDO after the fix of MDEV-30671 (MDEV-31234)" - is the failure to free UNDO logs a likely cause for ibdata1 to grow, given that it stores the undo log? (The two worst affected machines process many million queries a day) 3. To recover this space, I will need to do a full mysqldump, wipe and reimport of the data, correct? I guess I'm mostly asking for reassurance that this issue is now resolved and that once I schedule in the downtime to recover the space, that it's not going to resurface... Thanks, Simon

2 2

Fwd: DBA in Tokyo
by James Tobin 19 Jun '23

19 Jun '23

Hello, I'm working with an employer that is looking to hire a DBA in Tokyo. Consequently, I had hoped some members of this list may like to discuss. I can be reached using "JamesBTobin (at) Gmail (dot) Com". Kind regards, James

1 0

Connector/C fails to read MariaDB 10.6+ binary logs
by sruli67＠gmail.com 15 Jun '23

15 Jun '23

Hi, I'm using MariaDB Connector/C to 3.3.5 to read binary logs. It works perfectly for MySQL 8.0 and MariaDB 10.5 databases. For MariaDB 10.6 and above, mariadb_rpl_fetch() crashes (!) if the binlog contains XA transactions. Specifically, the function fails when it tries to read event type GTID with this contents: 'XA START X\'54584e31\',X\'\',1 GTID 0-1-21' Please advise Sruli Ganor

4 9

[Maria-discuss] Backup on the replication server getting affected
by ragul rangarajan 09 Jun '23

09 Jun '23

3 3

Re: [Maria-discuss] Restore from mysqldump fails with "Error in foreign key constraint"
by mark f edwards 05 Jun '23

05 Jun '23

How is providers in the `xu6gc_tj_notification_providers' table defined? Is it also varchar(100) character sat uft8mbr collate utf8mb4-general-ci ?? The parent and child columns has to match exactly. On June 3, 2023, at 7:55 AM, Alex <mysqlstudent(a)gmail.com> wrote: Hi, I'm trying to use mysqlbackup to backup a mariadb-10.5.19 database on fedora37 server using the following: # mysqldump --verbose -u root -ppass --result-file=/var/backup/mysql-backup/backup-lsalex-2023-06-03-13008.sql lsalex I usually use a more involved mysqlbackup command, but it also failed, so I figured it would be better to start out more simply as above. It fails with the following when restoring to a new, test database: # mysql -u root -p lstest < /var/backup/mysql-backup/backup-lsalex-2023-06-03-13008.sql Enter password: ERROR 1005 (HY000) at line 12884: Can't create table `lsalex`.`xu6gc_tj_notification_user_exclusions` (errno: 150 "Foreign key constraint is incorrectly formed") This is a backup/restore of a Joomla database. Am I using the wrong command to perform the backup or restore? This isn't a database that I created and I really have no idea how it works. I've also searched extensively for this error, and it appears to be a programming error, not a database error, but obviously I have no control over Joomla programming. Here's what I found from "SHOW ENGINE INNODB STATUS": LATEST FOREIGN KEY ERROR ------------------------ 2023-06-03 10:09:01 0x7f3a8d8516c0 Error in foreign key constraint of table `lstest`.`xu6gc_tj_notification_user_exclusions`: Create table `lstest`.`xu6gc_tj_notification_user_exclusions` with foreign key `xu6gc_tj_notification_user_exclusions_ibfk_1` constraint failed. Field type or character set for column 'provider' does not match referenced column 'provider'.------------ Here's the lines from the backup mentioned in the error message: 12884 CREATE TABLE `xu6gc_tj_notification_user_exclusions` ( 12885 `user_id` int(11) NOT NULL, 12886 `client` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, 12887 `key` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, 12888 `provider` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, 12889 KEY `client1` (`client`,`provider`(50),`key`), 12890 KEY `key` (`key`), 12891 KEY `provider` (`provider`), 12892 CONSTRAINT `xu6gc_tj_notification_user_exclusions_ibfk_1` FOREIGN KEY (`provider`) REFERENCES `xu6gc_tj_notification_providers` (`provider`) 12893 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; Any ideas are greatly appreciated.

5 4