- discuss - lists.mariadb.org

[Maria-discuss] Creating a trigger on information_schema.processlist
by Gordan Bobic 28 Feb '23

28 Feb '23

A long standing annoyance has been that stored procedures are opaque when it comes to profiling them with more granularity than just the entire procedure. SHOW FULL PROCESSLIST shows the individual queries while they are running, but polling SHOW FULL PROCESSLIST or SELECT * FROM information_schema.PROCESSLIST is not a practical solution because you can only poll it so many times per second without crippling the server. So I had the idea to hook triggers to information_schema.PROCESSLIST to capture the changes to the INFO column and shunt them into mysql.slow_log. But MariaDB informs them that root isn't privileged enough to crate a trigger in information_schema. Is there another way to augment slow logging granularity of queries that execute as part of the stored procedure?

2 1

[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