- discuss - lists.mariadb.org

[Maria-discuss] When do Query Select to Big Table is too slow !
by Ing Angel Cantu 13 Dec '19

13 Dec '19

Hi. I have problems to make query to a table from the db, is delaying so much (too slow), I don't know whats I need to do, I do some test to my code but is not the problem, is the table. When I make a select to the table marked with red, the response delay 6 or 8 seconds, but, if I do a query to another table the response delay 1 second (fast). Only the table marked with red is delaying. I do this specific query: /select * from FACTURACION where ID_USER='ID_HASH' order by SERVER_DATE limit 1,12;// / I only query the first 12 results order by date, and in the code I make the pagination using "?page=NUMBER" Regards! -- AngelHanielCantuJauregui angel.cantu(a)moneybox.business <mailto:angel.cantu@moneybox.business;> Gerencia | Servicios de Innovacion para Empresas SA de CV Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones (818) 421 9924 <tel:(818) 421 9924> | (899) 871 1722 <https://wa.me/528998711722> angel.cantu(a)moneybox.business <mailto:angel.cantu@moneybox.business> https://www.moneybox.business 20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico facebook <https://business.facebook.com/moneyboxco/> twitter <https://twitter.com/moneyboxlatam/> linkedin <https://www.linkedin.com/company/27011448/> instagram <https://www.instagram.com/moneyboxlatam/> <https://www.moneybox.business>

2 6

[Maria-discuss] Performance lags with MariaDB built from source
by Nisarg Shah 05 Dec '19

05 Dec '19

System config: OS: Ubuntu 18.04 CPU: 2x Intel Xeon E5-2660 (40 logical cores) RAM: 160GB I built a debug build of MariaDB server 10.5 from the Github source and tried to load and execute the TPC-H benchmarks on it with a database size of 1GB. While loading the data and executing queries, I observed that MariaDB takes an unusually large amount of time (more than 10x compared to other databases like QuickStep or MonetDB) to load the data and execute queries (more than 10 minutes per query, compared to less than a minute for other databases). I'm running the server with following options - ./bin/mysqld --user=nisargs --datadir=/fastdisk/mariadb-server-install-2/data/ --innodb-read-io-threads=40 --innodb-write-io-threads=40 --innodb-buffer-pool-size=16G --innodb-buffer-pool-instances=40 I just wanted to get some eyes on this and check if there is something wrong with the command line options (or whether I can tweak some options to improve performance). I'm just trying to conduct some benchmarking experiments and not very familiar with databases. Thanks Nisarg

3 3

[Maria-discuss] Migrating a Galera cluster to new hardware and major Maria version
by Tom Worster 06 Nov '19

06 Nov '19

I've three servers with Debian 9, Maria 10.1 and running a Galera cluster. We're upgrading the hardware by getting three new servers. They will have Debian 10 and Maria 10.3. What are my options for accomplishing this with minimal service downtime? One option I can think of Using a mysqldump snapshot, set up Maria on one of the new servers to replicate as an asyncronous slave from one node of the existing cluster.Set up Maria on the two remaining new servers in a cluster with that async slave.When all looks good, migrate workload to the new cluster. Does that seem reasonable? Tom

2 3

[Maria-discuss] What's the agenda of https://mariadb.org/2019-developers-unconference-shanghai/
by workplaylifecycle workplaylifecycle 03 Nov '19

03 Nov '19

2 1

[Maria-discuss] SSL issue with Windows MariaDB client
by Kenneth Penza 29 Oct '19

29 Oct '19

Good morning, Whilst testing SSL of a MariaDB server version 10.4.8 running Linux from a Windows 10 machine I noted that connection using MySQL client (mysql-8.0.18-winx64) connects successfully, however connections with MariaDB client (mariadb-10.4.8-winx64) fails. In case of MariaDB I have downloaded the file ( https://downloads.mariadb.org/interstitial/mariadb-10.4.8/winx64-packages/m…) whilst for MySQL client I used ( https://dev.mysql.com/downloads/file/?id=490026) C:\temp\mariadb-10.4.8-winx64>mysql --user=penzk001 --password --host=<hostname> --port=3306 --tls-version=TLSv1.2 --ssl-ca=c:\temp\CACert.pem Enter password: ******** ERROR 2026 (HY000): Unknown SSL error (0x80090308) C:\temp\mariadb-10.4.8-winx64\bin> cd ..\mysql-8.0.18-winx64\bin C:\temp\mysql-8.0.18-winx64\bin> mysql --user=penzk001 --password --host=<hostname> --port=3306 --tls-version=TLSv1.2 --ssl-ca=c:\temp\CACert.pem Welcome to the MySQL monitor. Commands end with ; or \g. ... mysql>\s ... SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256 ... mysql> To ensure that the SSL certificate is valid I also tried "--ssl-mode=VERIFY_IDENTITY" with the mysql-8.0.18 client and it worked fine. Regards Kenneth

2 3

[Maria-discuss] Fwd: database corrupted when switching from MySQL to MariaDB on Ubuntu 19.04
by bapt x 21 Oct '19

21 Oct '19

@Andrei all the error messages I found were included in my original email, let me know how I can provide additional information if no one can reproduce the problem. I forgot to include maria-discuss(a)lists.launchpad.net, you can see my reply below. ---------- Forwarded message --------- From: bapt x <baptx.is(a)gmail.com> Date: Wed, 16 Oct 2019 at 10:35 Subject: Re: [Maria-discuss] database corrupted when switching from MySQL to MariaDB on Ubuntu 19.04 To: Gordan Bobic <gordan.bobic(a)gmail.com> Thanks for the information. It looks like I did everything properly since I was able to reproduce the problem several times with a clean install of Ubuntu 19.04 on VirtualBox. I think if someone else tries the steps I explained, he can reproduce the problem. Now it would be nice to know if there is a way to recover the data. If MariaDB was able to corrupt the data, there should be a way to reverse engineer the process and restore the data. Maybe a developer that knows well MariaDB upgrade system has a solution. On Wed, 16 Oct 2019 at 10:23, Gordan Bobic <gordan.bobic(a)gmail.com> wrote: > I don't know if it is recoverable but it sounds like you missed the step > of always needing a full, clean shutdown between upgrades with > innodb_fast_shutdown=0. Then you can delete ib_logfile*, and upgrade. > > On Wed, 16 Oct 2019, 09:19 bapt x, <baptx.is(a)gmail.com> wrote: > >> Hello, >> >> On Ubuntu 19.04, which uses packages mariadb-server 10.3.17 and >> mysql-server 5.7.27, I noticed that if I wanted to switch from MySQL to >> MariaDB, the database is corrupted and there is a complete data loss >> even if I switch back to MySQL. >> In the previous version of Ubuntu, switching from MySQL to MariaDB did >> not manage to import data automatically (unlike Debian) but at least it >> created a backup of the data in /var/lib/mysql-5.7/ folder which is not >> done anymore. >> >> Here is the error message I saw during install when trying to use the >> database corrupted by MariaDB and switching back to MySQL: >> "MySQL has been frozen to prevent damage to your system. Please see >> /etc/mysql/FROZEN for help." >> >> And in /var/log/mysql/error.log: >> "[ERROR] InnoDB: Unsupported redo log format. The redo log was created >> with MariaDB 10.3.17. Please follow the instructions athttp://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html" >> >> I was able to reproduce the issue with a clean installation of Ubuntu >> 19.04 in VirtualBox. >> >> Do you know where the problem comes from and if it is possible to fix >> the binary data from */var/lib/mysql/* to make it work with either MySQL >> or MariaDB? >> It looks like MariaDB tried to convert the data ("[ERROR] InnoDB: >> Unsupported redo log format") but now it fails with both MySQL and MariaDB. >> Is it possible to revert the changes done by MariaDB to make the data >> work again with MySQL? (and then do a proper backup with mysqldump) >> >> Thanks. >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-discuss >> Post to : maria-discuss(a)lists.launchpad.net >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp >> >

3 8

[Maria-discuss] lock_wait_timeout default and best practice values
by jkmdb＠w3.org 18 Oct '19

18 Oct '19

Hi, When migrating from mariadb 10.1 to 10.3 we noticed that the default value for lock_wait_timeout went from 1 year to 1 day. Although 1 day seems more reasonable than 1 year, it seems pretty high for a lock_wait. Does anyone know why this value is so high? Are there any rule of thumbs for a good timeout value that won't degrade the system and yet allow it to operate as intended? I know that part of the answer is "it depends on which operations your applications are doing". What I don't know is if beyond those applications, mariadb needs a specific timeout to function properly, for example, when doing a mariabackup or flushing tables. Is anyone else using a timeout value other than the default ones? My apologies for the naivete of this question. Thanks! --josé

2 1

[Maria-discuss] database corrupted when switching from MySQL to MariaDB on Ubuntu 19.04
by bapt x 16 Oct '19

16 Oct '19

Hello, On Ubuntu 19.04, which uses packages mariadb-server 10.3.17 and mysql-server 5.7.27, I noticed that if I wanted to switch from MySQL to MariaDB, the database is corrupted and there is a complete data loss even if I switch back to MySQL. In the previous version of Ubuntu, switching from MySQL to MariaDB did not manage to import data automatically (unlike Debian) but at least it created a backup of the data in /var/lib/mysql-5.7/ folder which is not done anymore. Here is the error message I saw during install when trying to use the database corrupted by MariaDB and switching back to MySQL: "MySQL has been frozen to prevent damage to your system. Please see /etc/mysql/FROZEN for help." And in /var/log/mysql/error.log: "[ERROR] InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.3.17. Please follow the instructions athttp://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html" I was able to reproduce the issue with a clean installation of Ubuntu 19.04 in VirtualBox. Do you know where the problem comes from and if it is possible to fix the binary data from */var/lib/mysql/* to make it work with either MySQL or MariaDB? It looks like MariaDB tried to convert the data ("[ERROR] InnoDB: Unsupported redo log format") but now it fails with both MySQL and MariaDB. Is it possible to revert the changes done by MariaDB to make the data work again with MySQL? (and then do a proper backup with mysqldump) Thanks.

4 8

[Maria-discuss] Performance of multiple queries on 10.1 vs 10.3 and 10.4
by Andre Nathan 11 Oct '19

11 Oct '19

Hi I have a trivial PHP script using mysqli::multi_query (error handling ommited): $queries = "..."; $db = new mysqli("localhost", $user, $pass, $database); $db->multi_query($queries); $db->close(); Here $queries refers to a large number (hundreds) of CREATE TABLE, ALTER TABLE and INSERT queries derived from a database migration system. When I run this script against MariaDB 10.1, it returns immediately, and a SHOW PROCESSLIST on the MariaDB console shows tables being created after the script has finished running. In other words, it's as if the queries run asynchronously. If I upgrade to 10.3 or 10.4, the behavior changes and becomes "synchronous": the script takes minutes to run and only finishes once every query has run. Is this a known behavior on later MariaDB versions? Is there a configuration setting that can restore the behavior from 10.1? I've tested this on an Ubuntu 18.04 VM with packages installed from MariaDB's APT repositories, using default configurations. Thanks in advance, Andre

1 0

[Maria-discuss] Limited Unicode Support?
by Björn Keil 11 Oct '19

11 Oct '19

Hello, I hope this is the proper mailing list to ask such questions, I apologise if it isn't. I am having some problems with unusual Unicode characters in my MariaDB database. $ mariadb --version mariadb Ver 15.1 Distrib 10.3.17-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 $ sudo ./mariadb.php [sudo] Passwort für bjoern: Query: INSERT INTO `test` SET `string` = '🙋 Huhu. wie geht es dir?' Inserted: '🙋 Huhu. wie geht es dir?' Returned: '???? Huhu. wie geht es dir?' SHOW VARIABLES LIKE 'character%': character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ As you can see here, MariaDB does not take the character '🙋' ( https://www.fileformat.info/info/unicode/char/1f64b/index.htm ) and instead replaces it with four question marks and I have no idea why. I've attached the PHP code for the example. I would be most grateful for any suggestion. Regards, Björn Keil

4 4