January 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

[Maria-discuss] Mariadb 10.4 to 10.6 performance regression
by Reinis Rozitis 30 Jan '23

30 Jan '23

Hello, for testing the upgrade path I did an in-place slave server (with optimistic parallel replication) upgrade from 10.4.26 to 10.6.11, but the performance difference is so significant/abysmal I don't even know where to look at (see the attached QPS chart before and after upgrade - it's like 2-3x times lower!). All the previous settings/configuration are kept. I have various server parameters monitored but none seem to indicate why would everything become way slower (see for example the disk write operations also are 2-3 times lower). Maybe someone can give an idea where to look or what to check what could make such an impact? p.s. I know about https://jira.mariadb.org/browse/MDEV-29988 but I don't think that I'm hitting it since the cpu load hasn't changed (is even lower). wbr rr

1 1

[Maria-discuss] Branch 10.4 conflict
by Otto Kekäläinen 30 Jan '23

30 Jan '23

Hello! My local branch of 10.4 was at commit https://github.com/MariaDB/server/commit/9335e2a2b1d555d2ad83ae6f44acb8467c… and pulling from upstream causes now conflicts. Did somebody (Jan?) overwrite git history recently for mariadb-server or what might be going on? - Otto

3 2

[Maria-discuss] base64 text
by Ruben Safir 29 Jan '23

29 Jan '23

is there a way to encode text blogs to base64 so I can handle emails that are stored in the database so they don't look like binary trash like this? http://www.nylxs.com/messages.html?id=553339&archive=2023-01-01 ------ 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://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

[Maria-discuss] subscribe
by Ruben Safir 29 Jan '23

29 Jan '23

subscribe -- 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://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

1 0

[Maria-discuss] subscribe
by Ruben Safir 29 Jan '23

29 Jan '23

subscribe -- 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://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

1 0

[Maria-discuss] Query got stuck while we fetch for specific timestamp
by ragul rangarajan 27 Jan '23

27 Jan '23

Hi all, I have a TableA with data around 1.4Billion which was partitioned by timestamp. <https://stackoverflow.com/posts/75231474/timeline> TableA ( Column1, Column 2....Column N, timestamp) Partition by times I was able to see a weird behavior recently on this table where querying specific timestamps ended in an issue (Query getting stuck more than expected time - 10min and block IO utilization which inturn lead to service degradation). 127.0.0.1:3307>select * from TableA where cloumn1=v1 and cloumn2=v2 and cloumn3=v3 and cloumn4=v4 and cloumn5=v5 and (timestamp between 1673319600 and 1673319600);^CCtrl-C -- query killed. Continuing normally. ERROR 1317 (70009): Query execution was interrupted 127.0.0.1:3307> analyze select * from TableA where cloumn1=v1 and cloumn2=v2 and cloumn3=v3 and cloumn4=v4 and cloumn5=v5 and (timestamp between 1673319600 and 1673319600);^CCtrl-C -- query killed. Continuing normally. ERROR 1317 (70100): Query execution was interrupted 127.0.0.1:3307> explain select * from TableA where cloumn1=v1 and cloumn2=v2 and cloumn3=v3 and cloumn4=v4 and cloumn5=v5 and (timestamp between 1673319600 and 1673319600); +------+-------------+--------------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+--------------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | TableA | ALL | PRIMARY | NULL | NULL | NULL | 14 | Using where |+------+-------------+--------------+------+---------------+------+---------+------+------+-------------+1 row in set (0.042 sec) while the same was working under different timestamp 127.0.0.1:3307> explain select * from TableA where cloumn1=v1 and cloumn2=v2 and cloumn3=v3 and cloumn4=v4 and cloumn5=v5 and (timestamp between 1673319600+3600 and 1673319600+7200+7200+7200+7200+7200+7200);+------+-------------+--------------+------+---------------+---------+---------+-------------------------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+--------------+------+---------------+---------+---------+-------------------------------+------+-------------+| 1 | SIMPLE | TableA | ref | PRIMARY | PRIMARY | 7 | const,const,const,const,const | 12 | Using where |+------+-------------+--------------+------+---------------+---------+---------+-------------------------------+------+-------------+1 row in set (0.002 sec) 127.0.0.1:3307> analyze select * from TableA where cloumn1=v1 and cloumn2=v2 and cloumn3=v3 and cloumn4=v4 and cloumn5=v5 and (timestamp between 1673319600+3600 and 1673319600+7200+7200+7200+7200+7200+7200);+------+-------------+--------------+------+---------------+---------+---------+-------------------------------+------+--------+----------+------------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |+------+-------------+--------------+------+---------------+---------+---------+-------------------------------+------+--------+----------+------------+-------------+| 1 | SIMPLE | TableA | ref | PRIMARY | PRIMARY | 7 | const,const,const,const,const | 12 | 600.00 | 100.00 | 91.67 | Using where |+------+-------------+--------------+------+---------------+---------+---------+-------------------------------+------+--------+----------+------------+-------------+1 row in set (0.003 sec) Suspecting something might get corrupted in the DB entry for a specific time but not sure how to verify it. 1. Is there any way to check the Data corruption issue in the specific table? a. Tried to check via mysqlcheck but it consumes more time and IO utilization. b. Do we have any other option to check the same in a better way? 1. If we have a problem with DB table entries do we have options to solve this a. Either restarting the active DB or other ways to repair the data to solve the issue like mysqlcheck repair. 1. Not sure why we see NULL in key, Key_len, and possible_key for the first explain statement for the same table. Regards, Ragul R

2 1

[Maria-discuss] [newbie] Include latest ID into INSERT?
by Gilles 23 Jan '23

23 Jan '23

Hello, I need to write a script in SQL to import topics + posts into a phpBB forum. The issue I have is: How can I reference the last topic ID when adding a post to it, since a post must reference a topic? ============ #Create new topic (ie. discussion thread) INSERT INTO `phpbb_topics` (forum_id,topic_title,topic_poster,topic_time) VALUES (); #Add first post to topic #INSERT INTO `phpbb_posts` (topic_id,forum_id,poster_id,post_time,post_username,post_text) VALUES (); INSERT INTO `phpbb_posts` (SELECT LAST_INSERT_ID(),forum_id,poster_id,post_time,post_username,post_text) VALUES (); ============ Can it be done in SQL, or should I write it in eg. Python? Thank you.

1 0

[Maria-discuss] Inspect SQL sentencies in aria_log files
by woodyst 22 Jan '23

22 Jan '23

Hello! Please, forgive me if I am asking in the wrong list so I do not know if it is the correct place for asking this question. I was using MyISAM engine in the past, and I used binlog files for inspecting sql queries executed in the system for debuging purposes. Now I use Aria and I would need to list queries as I did with mysqlbinlog, but for the aria_log files. I have been reading a lot of documentation but I only find that the aria_read_log utility with the display-only option does not print the sql queries as mysqldump did. Please, can anybody help me with this? Thanks in advance. -- Edu.

2 1

[Maria-discuss] Re-starting mariadb again
by Jogchum Reitsma 19 Jan '23

19 Jan '23

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. 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

1 1