June 2020 - discuss - lists.mariadb.org

[Maria-discuss] mdb 10.4.13 manual delete/recreate of 'root' user fails: ERROR 1471 (HY000): The target table user of the INSERT is not insertable-into ?
by PGNet Dev 22 Jun '20

22 Jun '20

i've installed distro-packaged MDB mariadb -V mariadb Ver 15.1 Distrib 10.4.13-MariaDB, for Linux (x86_64) using readline 5.1 on lsb_release -rd Description: Arch Linux Release: rolling uname -rm 5.7.3-arch1-1 x86_64 i'd like to modify & delete/restore my 'root' user; an exercise in recovery ... starting with a clean install, mysql -e " SELECT User, Host, HEX(authentication_string) FROM mysql.user;" " +-------------+-----------+----------------------------+ | User | Host | HEX(authentication_string) | +-------------+-----------+----------------------------+ | mariadb.sys | localhost | | | root | localhost | 696E76616C6964 | | mysql | localhost | 696E76616C6964 | +-------------+-----------+----------------------------+ mysql -e " SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user WHERE user = 'root'; " +----------------------------------------------------------+ | CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') | +----------------------------------------------------------+ | SHOW GRANTS FOR 'root'@'localhost'; | +----------------------------------------------------------+ adding grants & mod'ing, mysql > REVOKE ALL ON *.* FROM 'root'@'localhost'; REVOKE GRANT OPTION ON *.* FROM 'root'@'localhost'; REVOKE ALL ON *.* FROM 'root'@'%'; REVOKE GRANT OPTION ON *.* FROM 'root'@'%'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket; ALTER USER 'root'@'%' IDENTIFIED VIA unix_socket; FLUSH PRIVILEGES; exit; works as expected mysql -e " SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user WHERE user = 'root'; " +----------------------------------------------------------+ | CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') | +----------------------------------------------------------+ | SHOW GRANTS FOR 'root'@'%'; | | SHOW GRANTS FOR 'root'@'localhost'; | +----------------------------------------------------------+ mysql -e " SELECT CONCAT('plugin: ', plugin, ' \'', user, '\'@\'', host, '\';') FROM mysql.user WHERE user = 'root'; " +-----------------------------------------------------------------+ | CONCAT('plugin: ', plugin, ' \'', user, '\'@\'', host, '\';') | +-----------------------------------------------------------------+ | plugin: unix_socket 'root'@'localhost'; | | plugin: unix_socket 'root'@'%'; | +-----------------------------------------------------------------+ mysql -e " SELECT User, Host, HEX(authentication_string) FROM mysql.user; " +-------------+-----------+----------------------------+ | User | Host | HEX(authentication_string) | +-------------+-----------+----------------------------+ | mariadb.sys | localhost | | | root | localhost | | | mysql | localhost | 696E76616C6964 | | root | % | | +-------------+-----------+----------------------------+ mysql -e status -------------- mysql Ver 15.1 Distrib 10.4.13-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 24 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.4.13-MariaDB-log Arch Linux Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /run/mysqld/mysqld.sock Uptime: 15 min 9 sec Threads: 8 Questions: 93 Slow queries: 0 Opens: 19 Flush tables: 1 Open tables: 13 Queries per second avg: 0.102 -------------- if i stop, then safe-restart mdb systemctl stop mariadb.service killall mysqld killall mysqld sleep 10 mysqld_safe \ --defaults-file=/usr/local/etc/mariadb/my.cnf \ --skip-grant-tables \ --skip-networking & mysql -u root mysql MariaDB [(mysql)]> server still looks good, status; -------------- mysql Ver 15.1 Distrib 10.4.13-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 9 Current database: mysql Current user: root@ SSL: Not in use Current pager: /usr/bin/less Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.4.13-MariaDB-log Arch Linux Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /run/mysqld/mysqld.sock Uptime: 14 sec Threads: 8 Questions: 37 Slow queries: 0 Opens: 34 Flush tables: 1 Open tables: 30 Queries per second avg: 2.642 -------------- if I *destroy* the 'root' user user mysql; DELETE FROM mysql.user WHERE user = 'root' AND host = 'localhost' OR host = '%'; flush privileges; SELECT User, Host, HEX(authentication_string) FROM mysql.user; +-------------+-----------+----------------------------+ | User | Host | HEX(authentication_string) | +-------------+-----------+----------------------------+ | mariadb.sys | localhost | | | mysql | localhost | 696E76616C6964 | +-------------+-----------+----------------------------+ then, get a pass hash, select password('testpass'); +-------------------------------------------+ | password('testpass') | +-------------------------------------------+ | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 | +-------------------------------------------+ & attempt to re-init & insert the 'root' user insert into `user` VALUES('localhost','root','*00E247AC5F9AF26AE0194B41E1E769DEE1429A29','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'',''); it fails ERROR 1471 (HY000): The target table user of the INSERT is not insertable-into what different/additional steps are required to recreate a deleted root user?

2 4

[Maria-discuss] setting db root passwd failed now reinstall is failing
by ubuntu＠enabled.com 20 Jun '20

20 Jun '20

Hi there, I am doing a poor job installing mariadb. I need to install mariadb on a new ubunutu server. I attempted to set the root password and was having difficulties getting mariadb in a usable state after that. So I attempted a purge and reinstall of mariadb (same version). Now I cant start the daemon and I dont have a /var/lib/mysql directory anymore. What can I do? $ sudo mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) Enter current password for root (enter for none): ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) Enter current password for root (enter for none): ^^^ I dont know what this password is? Typing 'enter' doesnt work ~$ ls -l /var/lib/mysql* ls: cannot open directory '/var/lib/mysql-files': Permission denied ls: cannot open directory '/var/lib/mysql-keyring': Permission denied $ cat /etc/lsb-release DISTRIB_ID=Ubuntu DISTRIB_RELEASE=20.04 DISTRIB_CODENAME=focal DISTRIB_DESCRIPTION="Ubuntu 20.04 LTS" ~$ sudo apt list --installed | grep mariadb WARNING: apt does not have a stable CLI interface. Use with caution in scripts. mariadb-client-10.3/focal,now 1:10.3.22-1ubuntu1 amd64 [installed,automatic] mariadb-client-core-10.3/focal,now 1:10.3.22-1ubuntu1 amd64 [installed,automatic] mariadb-common/focal,now 1:10.3.22-1ubuntu1 all [installed,automatic] mariadb-server-10.3/focal,now 1:10.3.22-1ubuntu1 amd64 [installed,automatic] mariadb-server-core-10.3/focal,now 1:10.3.22-1ubuntu1 amd64 [installed,automatic] mariadb-server/focal,now 1:10.3.22-1ubuntu1 all [installed] noah@nuc2:~$ sudo systemctl restart mariadb Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details. noah@nuc2:~$ systemctl status mariadb.service ● mariadb.service - MariaDB 10.3.22 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) Active: failed (Result: exit-code) since Fri 2020-06-19 19:21:52 UTC; 4s ago Docs: man:mysqld(8) https://mariadb.com/kb/en/library/systemd/ Process: 548722 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS) Process: 548733 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Process: 548736 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=> Process: 548785 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE) Main PID: 548785 (code=exited, status=1/FAILURE) Jun 19 19:21:51 nuc2 systemd[1]: Starting MariaDB 10.3.22 database server... Jun 19 19:21:52 nuc2 mysqld[548785]: 2020-06-19 19:21:52 0 [Note] /usr/sbin/mysqld (mysqld 10.3.22-MariaDB-1ubuntu1) starting as process 548785 ... Jun 19 19:21:52 nuc2 mysqld[548785]: 2020-06-19 19:21:52 0 [Warning] Can't create test file /var/lib/mysql/nuc2.lower-test Jun 19 19:21:52 nuc2 mysqld[548785]: [97B blob data] Jun 19 19:21:52 nuc2 mysqld[548785]: 2020-06-19 19:21:52 0 [ERROR] Aborting Jun 19 19:21:52 nuc2 systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE Jun 19 19:21:52 nuc2 systemd[1]: mariadb.service: Failed with result 'exit-code'. Jun 19 19:21:52 nuc2 systemd[1]: Failed to start MariaDB 10.3.22 database server. Cheers, Noah

2 2

[Maria-discuss] Doubts about temporal table
by Roberto Spadim 17 Jun '20

17 Jun '20

Hi folks Could be possible query temporal tables but instead of use a timestamp, use a “commit” id? For example query first commit, second, third, ... and the last commit = current table? Thanks -- Roberto Spadim SPAEmpresarial - Software ERP/Scada Eng. Automação e Controle, Eng. Financeira

2 3

[Maria-discuss] CDC Adapter replacement
by Διονύσης Βυθούλκας 11 Jun '20

11 Jun '20

Hello, I recently started testing MariaDB Columnstore 1.2.5 and stumbled upon an error while using the MaxScale CDC Data Adapter (mxs_adapter). When I checked at jira.mariadb.org I realized the most recent tickets on mxs_adapter where closed with a 'won't fix' resolution, such as this: https://jira.mariadb.org/browse/MCOL-2037 Is there a replacement to it or another way to stream data from regular MariaDB instances to Columnstore instances? Thanks for any recommendations. dcv

1 0

[Maria-discuss] Poor performance compared to MySQL
by Ling, Andy 03 Jun '20

03 Jun '20

I am looking at switching from MySQL to MariaDB and have been comparing the performance of the two. I am using the same databases on the same Windows machine and running queries using MySQL and MariaDB and I am finding that MariaDB is 6 times slower. A query that takes 5 seconds on MySQL is taking 28 seconds on MariaDB. I am hoping I have some configuration wrong, so I looking for some help to work out what needs changing. One of the problem queries is a join between two tables. Analyzing the query gives.. ANALYZE FORMAT=JSON SELECT r.rushid FROM rushes r LEFT JOIN browse b ON r.rushID = b.rushID WHERE b.rushID IS NULL AND r.updated < NOW() - INTERVAL 1 DAY; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------+ | ANALYZE | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 107974, "table": { "table_name": "r", "access_type": "ALL", "r_loops": 1, "rows": 784286, "r_rows": 784286, "r_total_time_ms": 245.5, "filtered": 100, "r_filtered": 100, "attached_condition": "r.updated < '2020-05-31 16:35:59'" }, "table": { "table_name": "b", "access_type": "ref", "possible_keys": ["rushid_start", "rushid"], "key": "rushid", "key_length": "96", "used_key_parts": ["rushID"], "ref": ["quentin_v3afp.r.rushID"], "r_loops": 784286, "rows": 10, "r_rows": 1, "r_total_time_ms": 106252, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(b.rushID is null)", "using_index": true, "not_exists": true } } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------+ 1 row in set (1 min 48.244 sec) The table has been converted to the Aria engine from MyISAM. The my.ini file has had the following added/changed.. #Not using MyISAM so save memory key_buffer_size=64k #Setting to improve Aria performance aria_pagecache_buffer_size=4007M tmp_table_size=35M max_heap_table_size=35M Thanks for any help. Andy Ling ********************************************************************** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You.

6 18