#StackBounty: #mariadb #collation Why can't set variables related collation as utf8mb4_unicode_ci?

Bounty: 50

Show mysql version here:

mysql --version
mysql  Ver 15.1 Distrib 10.5.11-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

And my mariadb’s configuration file.

cat   /etc/mysql/mariadb.cnf
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
init_connect = 'SET collation_connection = utf8mb4_unicode_ci'


[client-server]
socket = /run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Enter into mysql,issue command show variables like 'collation%';:

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

How can get the below result?

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

Log in with a normal user showkey.

debian@debian:~$ mysql -u showkey -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 31
Server version: 10.5.11-MariaDB-1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)


Get this bounty!!!

#StackBounty: #mariadb #collation Why can't set variables related collation as utf8mb4_unicode_ci?

Bounty: 50

Show mysql version here:

mysql --version
mysql  Ver 15.1 Distrib 10.5.11-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

And my mariadb’s configuration file.

cat   /etc/mysql/mariadb.cnf
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
init_connect = 'SET collation_connection = utf8mb4_unicode_ci'


[client-server]
socket = /run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Enter into mysql,issue command show variables like 'collation%';:

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

How can get the below result?

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

Log in with a normal user showkey.

debian@debian:~$ mysql -u showkey -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 31
Server version: 10.5.11-MariaDB-1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)


Get this bounty!!!

#StackBounty: #mariadb #collation Why can't set variables related collation as utf8mb4_unicode_ci?

Bounty: 50

Show mysql version here:

mysql --version
mysql  Ver 15.1 Distrib 10.5.11-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

And my mariadb’s configuration file.

cat   /etc/mysql/mariadb.cnf
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
init_connect = 'SET collation_connection = utf8mb4_unicode_ci'


[client-server]
socket = /run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Enter into mysql,issue command show variables like 'collation%';:

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

How can get the below result?

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

Log in with a normal user showkey.

debian@debian:~$ mysql -u showkey -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 31
Server version: 10.5.11-MariaDB-1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)


Get this bounty!!!

#StackBounty: #mariadb #collation Why can't set variables related collation as utf8mb4_unicode_ci?

Bounty: 50

Show mysql version here:

mysql --version
mysql  Ver 15.1 Distrib 10.5.11-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

And my mariadb’s configuration file.

cat   /etc/mysql/mariadb.cnf
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
init_connect = 'SET collation_connection = utf8mb4_unicode_ci'


[client-server]
socket = /run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Enter into mysql,issue command show variables like 'collation%';:

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

How can get the below result?

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

Log in with a normal user showkey.

debian@debian:~$ mysql -u showkey -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 31
Server version: 10.5.11-MariaDB-1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)


Get this bounty!!!

#StackBounty: #mariadb #collation Why can't set variables related collation as utf8mb4_unicode_ci?

Bounty: 50

Show mysql version here:

mysql --version
mysql  Ver 15.1 Distrib 10.5.11-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

And my mariadb’s configuration file.

cat   /etc/mysql/mariadb.cnf
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
init_connect = 'SET collation_connection = utf8mb4_unicode_ci'


[client-server]
socket = /run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Enter into mysql,issue command show variables like 'collation%';:

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

How can get the below result?

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

Log in with a normal user showkey.

debian@debian:~$ mysql -u showkey -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 31
Server version: 10.5.11-MariaDB-1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)


Get this bounty!!!

#StackBounty: #mariadb #collation Why can't set variables related collation as utf8mb4_unicode_ci?

Bounty: 50

Show mysql version here:

mysql --version
mysql  Ver 15.1 Distrib 10.5.11-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

And my mariadb’s configuration file.

cat   /etc/mysql/mariadb.cnf
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
init_connect = 'SET collation_connection = utf8mb4_unicode_ci'


[client-server]
socket = /run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Enter into mysql,issue command show variables like 'collation%';:

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

How can get the below result?

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

Log in with a normal user showkey.

debian@debian:~$ mysql -u showkey -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 31
Server version: 10.5.11-MariaDB-1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)


Get this bounty!!!

#StackBounty: #mariadb #collation Why can't set variables related collation as utf8mb4_unicode_ci?

Bounty: 50

Show mysql version here:

mysql --version
mysql  Ver 15.1 Distrib 10.5.11-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

And my mariadb’s configuration file.

cat   /etc/mysql/mariadb.cnf
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
init_connect = 'SET collation_connection = utf8mb4_unicode_ci'


[client-server]
socket = /run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Enter into mysql,issue command show variables like 'collation%';:

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

How can get the below result?

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

Log in with a normal user showkey.

debian@debian:~$ mysql -u showkey -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 31
Server version: 10.5.11-MariaDB-1 Debian 11

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)


Get this bounty!!!

#StackBounty: #mariadb #mycollab What can I do about a hanging MyCollab installation?

Bounty: 50

MyCollab displays an initial setup screen a la WordPress, but the installer hangs, regardless of how permissive I’ve set its directory to be.

When I test a database connection, it said that it can’t make a connection. I have specified the database name, a user with access to that database, the corresponding password, and the address. If I click "Check connection" it says "Cannot connect to database. Recheck your input."

I am not sure what it’s not connecting to. I can use a mysql -ufoo -p, giving the same credentials as I gave MyCollab, and telnetting to localhost on 3306 gets binary data that includes an intelligible MariaDB prompt. I can use the database from the command line.

What else should I be doing?


Get this bounty!!!

#StackBounty: #mariadb #columnstore "Error opening column file" in MariaDB Columnstore data cpimport

Bounty: 150

I’m trying to import a large file, around 2GB, into MariaDB Columnstore. Other files that are smaller worked fine, so I suspect it’s a size issue. I create the table with engine Columnstore with no problem. Then I run the command:

sudo cpimport -s '|' -E '"' industry  fdic_callr_rcr_regulat_capital fdic_callr_rcr_regulat_capital.csv

And I get:

Using table OID 20208 as the default JOB ID
Input file(s) will be read from : /home/sofuser/data/callr
Job description file : /var/lib/columnstore/data/bulk/tmpjob/20208_D20210617_T180941_S792633_Job_20208.xml
Log file for this job: /var/lib/columnstore/data/bulk/log/Job_20208.log
2021-06-17 18:09:42 (2870) INFO : successfully loaded job file /var/lib/columnstore/data/bulk/tmpjob/20208_D20210617_T180941_S792633_Job_20208.xml
2021-06-17 18:09:42 (2870) INFO : Job file loaded, run time for this step : 0.40599 seconds
2021-06-17 18:09:42 (2870) INFO : PreProcessing check starts
2021-06-17 18:09:42 (2870) INFO : input data file /home/sofuser/data/callr/fdic_callr_rcr_regulat_capital.csv
2021-06-17 18:09:50 (2870) ERR  : Error opening column file for OID-21217; DBRoot-1; partition-0; segment-0; filename-/var/lib/columnstore/data1/000.dir/000.dir/082.dir/225.dir/000.dir/FILE000.cdf;  opening a column file. The file was not found or was inaccessible. [1052]
2021-06-17 18:09:50 (2870) CRIT : Error in pre-processing the job file for table industry.fdic_callr_rcr_regulat_capital [1052]
2021-06-17 18:09:50 (2870) ERR  : Error deleting meta file; Error deleting bulk rollback data subdirectory /var/lib/columnstore/data1/bulkRollback/20208_data; [1053]

NOTE: the file that’s not found is not the input file, it’s a file that MariaDB has in its database structure.

This is the log


Get this bounty!!!

#StackBounty: #mysql #mariadb #connectivity #tcpip What happens on the server when a MySQL client connection is lost?

Bounty: 100

Imagine the following situation:

You have a MySQL server S and two clients, A and B.
Both clients connect to S (e.g. using the CLI).

A starts a transaction and then locks a table with WRITE permissions and inserts a row into the table.

Now B tries to select all rows from the table and – of course – has to wait until A has committed AND unlocked the table.

Imagine that A‘s connection is interrupted before it is able to commit or rollback. In my special use-case, the connection might be interrupted at any point in time.

I tested this scenario using three virtual machines which are connected to a common virtual bridge. Using brctl I can simply remove individual VMs in order to simulate connection interruptions.

The result

Client B is stuck, because it waits for the table to get unlocked.
Typing SHOW PROCESSLIST in the CLI of the server shows me this:

+----+------+-----------------+----------+---------+------+---------------------------------+----------------------------+----------+
| Id | User | Host            | db       | Command | Time | State                           | Info                       | Progress |
+----+------+-----------------+----------+---------+------+---------------------------------+----------------------------+----------+
|  8 | root | B:38129         | foo      | Query   | 1676 | Waiting for table metadata lock | select blockname from root |    0.000 |
|  9 | root | A:54806         | foo      | Sleep   | 1679 |                                 | NULL                       |    0.000 |
| 12 | root | localhost       | NULL     | Query   |    0 | init                            | show processlist           |    0.000 |
+----+------+-----------------+----------+---------+------+---------------------------------+----------------------------+----------+

So the server thinks that the connection from A is still alive, while it already was lost minutes ago. Having a look at netstat also tells me that the connection is still considered to be ESTABLISHED and still alive.

I also observed the bridge: Not a single packet from S to A.

Client B is stuck forever. I have to restart the whole server to unlock the table.

What I expected

I want my server to detect (after some predefined time) that the connection to A is lost even if A did not send any more queries. Then I want my server to rollback any uncommitted transactions and finally to unlock the table.

How can I realise this?

Why aren’t there any TCP keepalives from S to A?

Is it possible to define any kind of timeouts?


I played around with the variables wait_timeout, innodb_lock_wait_timeout, and interactive_timeout.

  • interactive_timeout is not interesting because my productive system will not use the CLI.
  • innodb_lock_wait_timeout seems not to be respected in any way.
  • wait_timeout indeed improves my situation: idling sessions are killed after the timeout. But I only want them to be killed in case of connection interruption. I want to keep the connection alive to improve performance (save tcp handshakes, SQL logins, db selection…)


Get this bounty!!!