- discuss - lists.mariadb.org

[Maria-discuss] Problem updating to MariaDB java connector 2.6
by Ling, Andy 24 Jun '20

24 Jun '20

I'm not sure if this is the right place to ask, so if not, please point me to the right place. I have just tried updating the mariadb java connector from 2.5.4 to 2.6.1 and I get a Null pointer exception trying to open a connection. I am using the DriverManager.getConnection call which works with 2.5.4, but just swapping the jar file to 2.6.1 causes an exception (2.6.0 also has the problem). The exception traceback is shown below. I am using MariaDB 10.4.12 on Windows Am I doing something wrong or is this a problem with 2.6. Thanks for any help Andy Ling Exception in thread "main" java.lang.ExceptionInInitializerError at org.mariadb.jdbc.internal.com.send.SendHandshakeResponsePacket.writeConnectAttributes(SendHandshakeResponsePacket.java:202) at org.mariadb.jdbc.internal.com.send.SendHandshakeResponsePacket.send(SendHandshakeResponsePacket.java:187) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.authenticationHandler(AbstractConnectProtocol.java:684) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.createConnection(AbstractConnectProtocol.java:546) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1367) at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:635) at org.mariadb.jdbc.MariaDbConnection.newConnection(MariaDbConnection.java:150) at org.mariadb.jdbc.Driver.connect(Driver.java:89) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:270) at com.quantel.QuentinManager.ds.mysql.MySQLBootStrap.getInitialConnection(MySQLBootStrap.java:48) at com.quantel.QuentinManager.DBCluster.getInitialConnection(DBCluster.java:128) at com.quantel.QuentinManager.QuentinManager.start(QuentinManager.java:329) at com.quantel.QuentinManager.QuentinManager.main(QuentinManager.java:630) Caused by: java.lang.NullPointerException at org.mariadb.jdbc.internal.util.constant.Version.<clinit>(Version.java:84) ... 14 more

2 1

[Maria-discuss] MariaDB Multi-Master Cluster Replication
by Zishan Javaid 22 Jun '20

22 Jun '20

1 0

[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

[Maria-discuss] Error writing to binary log file
by Gustavo Vieira 29 May '20

29 May '20

Hello, I have several MariaDB 10.1.45 instances running in Linux containers with memory limits. This week I have enabled binary logging on all of them with the following parameters: log-bin = mysqld-bin binlog-format = mixed expire_logs_days = 1 After that I have been getting several errors like this in a few of them: [ERROR] mysqld: Error writing file 'mysqld-bin' (errno: 11 "Resource temporarily unavailable") A few also show these other two errors mixed with the previous one: [ERROR] mysqld: Error writing file 'mysqld-bin' (errno: 12 "Cannot allocate memory") [ERROR] mysqld: Error writing file 'mysqld-bin' (errno: 0 "Internal error/check (Not system error)") It gets stuck on this state, reporting errors to every attempt of executing a query. A restart on the service seems to solve the problem, at least temporarily. Does anybody have a clue on why it gets stuck like that? If the container ran out of memory, should not mariadb crash? I could not find any information about this error and binary logs being related. Thanks in advance, Gustavo Vieira

2 2

[Maria-discuss] backup from any cluster member can't be restarted and restored
by Jeff Dyke 27 May '20

27 May '20

This has biting me for a bit, and i have tried a litany of things. Here is the issue: VPC 1: (prod) Mariadb 10.4.12 - galera in a 3 node cluster, cluster functioning perfectly VPC 2: (staging) Mariadb 10.4.12 galera in a 3 node cluster also running smoothly. -- backup script (clipped for only the essentials) -- set -e DIR="/home/jeff/restore" rm -rf $DIR mkdir $DIR AUTH='--defaults-extra-file=/root/db.cnf -u root' sudo mysql ${AUTH} -e "SET GLOBAL wsrep_desync = ON;" sudo mysql ${AUTH} -e "SET GLOBAL wsrep_on = OFF"; sudo mariabackup ${AUTH} --backup --binlog-info=ON --target-dir="${DIR}" 2>&1 | grep -vE "Copying|done|log scanned" sudo mysql ${AUTH} -e "SET GLOBAL wsrep_on = ON;" sudo mysql ${AUTH} -e "SET GLOBAL wsrep_desync = OFF"; -- end script -- -- restore process -- sudo service mariadb stop (or kill -9 PID only when the server won't start) sudo rm -rf /var/lib/mysl/* sudo mariabackup --prepare --target-dir=restore sudo mariabackup --copy-back --target-dir=restore sudo chown -R mysql:mysql /var/lib/mysql/* sudo service mariadb start -- end restore -- I can run this on any of the script and backup process on any of the *staging* nodes and restore them to a freshly built server within that VPC. I can also take the backup from *staging* and load in into a newly build machine on the *prod* VPC. What i can not do is run the script on any of the prod members and restore it to a newly built server, regardless of location. (really the locations don't matter at all) The following is the log from a failing restore from a proddb backup and restore: 2020-04-15 22:11:10 0 [Note] InnoDB: Using Linux native AIO 2020-04-15 22:11:10 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2020-04-15 22:11:10 0 [Note] InnoDB: Uses event mutexes 2020-04-15 22:11:10 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2020-04-15 22:11:10 0 [Note] InnoDB: Number of pools: 1 2020-04-15 22:11:10 0 [Note] InnoDB: Using SSE2 crc32 instructions 2020-04-15 22:11:10 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts) 2020-04-15 22:11:10 0 [Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 8, chunk size = 128M 2020-04-15 22:11:10 0 [Note] InnoDB: Completed initialization of buffer pool 2020-04-15 22:11:10 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2020-04-15 22:11:10 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes 2020-04-15 22:11:10 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 50331648 bytes 2020-04-15 22:11:10 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2020-04-15 22:11:10 0 [Note] InnoDB: New log files created, LSN=42006460184 2020-04-15 22:11:10 0 [Note] InnoDB: 128 out of 128 rollback segments are active. 2020-04-15 22:11:10 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2020-04-15 22:11:10 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2020-04-15 22:11:10 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2020-04-15 22:11:10 0 [Note] InnoDB: Waiting for purge to start 2020-04-15 22:11:10 0 [Note] InnoDB: 10.4.12 started; log sequence number 42006460428; transaction id 23858673 2020-04-15 22:11:10 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2020-04-15 22:11:10 0 [Note] Plugin 'FEEDBACK' is disabled. 2020-04-15 22:11:11 0 [Note] InnoDB: Buffer pool(s) load completed at 200415 22:11:11 -- hangs here for about an hour before timing out -- Logfile from a successful start 2020-04-15 23:13:15 0 [Note] InnoDB: Using Linux native AIO 2020-04-15 23:13:15 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2020-04-15 23:13:15 0 [Note] InnoDB: Uses event mutexes 2020-04-15 23:13:15 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2020-04-15 23:13:15 0 [Note] InnoDB: Number of pools: 1 2020-04-15 23:13:15 0 [Note] InnoDB: Using SSE2 crc32 instructions 2020-04-15 23:13:15 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts) 2020-04-15 23:13:15 0 [Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 8, chunk size = 128M 2020-04-15 23:13:16 0 [Note] InnoDB: Completed initialization of buffer pool 2020-04-15 23:13:16 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2020-04-15 23:13:16 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes 2020-04-15 23:13:16 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 50331648 bytes 2020-04-15 23:13:16 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2020-04-15 23:13:16 0 [Note] InnoDB: New log files created, LSN=43323668550 2020-04-15 23:13:16 0 [Note] InnoDB: 128 out of 128 rollback segments are active. 2020-04-15 23:13:16 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2020-04-15 23:13:16 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2020-04-15 23:13:16 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2020-04-15 23:13:16 0 [Note] InnoDB: Waiting for purge to start 2020-04-15 23:13:16 0 [Note] InnoDB: 10.4.12 started; log sequence number 43323669004; transaction id 22422468 2020-04-15 23:13:16 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2020-04-15 23:13:16 0 [Note] Plugin 'FEEDBACK' is disabled. 2020-04-15 23:13:16 0 [Note] Server socket created on IP: '0.0.0.0'. 2020-04-15 23:13:16 0 [Note] Reading of all Master_info entries succeeded 2020-04-15 23:13:16 0 [Note] Added new Master_info '' to hash table 2020-04-15 23:13:16 0 [Note] /usr/sbin/mysqld: ready for connections. Version: '10.4.12-MariaDB-1:10.4.12+maria~bionic' socket: '/var/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution 2020-04-15 23:13:22 0 [Note] InnoDB: Buffer pool(s) load completed at 200415 23:13:22 ------------------------ The main difference between the two is that: - the server socket is logged - master_info is logged - and it binds to the port/socket Before they *both* end with 2020-04-15 23:13:22 0 [Note] InnoDB: Buffer pool(s) load completed at 200415 23:13:22 -- Other Notes -- - Config files are the same except for ips, both on newly built servers as the cluster as they are managed through salt. I ran a diff -r against prod vs staging and only saw There has to be some corruption in the mariabackup output, but still reported OK. Since i can put a staging backup on a prod server, but i can not put a prod backup on a staging server. If you've gotten this far, thank you. If you have any ideas on what i could try, i'd be grateful. aria_chk and all other table checks return correctly. I'm not sure what makes a server write out the master_info, so possibly that is a pointer. I'm happy to answer any questions, i've tried way to many things to post here as i didn't want to write until i had enough of dumb stuff figured out. I could rebuild the prod cluster but am really looking to understand this. I don't really want to build my own with debug info on b/c why would they be different between the two vpcs.

3 10

[Maria-discuss] Alter table engine causes duplicate key
by Ling, Andy 26 May '20

26 May '20

I'm using MariDB 10.4.12 on Windows server 2016 I have a table with one of the columns defined as `id` bigint(20) NOT NULL AUTO_INCREMENT and PRIMARY KEY (`id`) It is currently a MyISAM table and has been in use for ages. The current auto increment value is AUTO_INCREMENT=106274948879084. Rows have been added and removed over time such that there are gaps in the id value. The table passes all checks, so there are no duplicate entries. I recently tried to change the table to use the Aria engine using the command ALTER TABLE xxx ENGINE=ARIA; and got the error ERROR 1062 (23000) at line 4: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '106274948781020' for key 'PRIMARY' I can fix the problem by resetting the auto increment values using ALTER TABLE xxx AUTO_INCREMENT=1; but is this something I should expect? First, I don't really see why it is resequencing. No data should be changing. Then why is the resequencing generating a duplicate entry? Fortunately in this instance I don't care what the id is, but had this been used by another table, changing the id may have caused problems. Thanks for any help on this. 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.

1 0