As title says I upgraded my mariadb, all fine and no issues, ran mariadb-upgrade and all fine but now every time i start mariadb I see this errors Mar 27 03:38:42 db3 debian-start: SELECT count(*) FROM mysql.user WHERE user='root' and password='' and password_expired='N' and plugin in ('', 'mysql_native_password', 'mysql_old_password') Mar 27 03:38:42 db3 debian-start: -------------- Mar 27 03:38:42 db3 debian-start: ERROR 1267 (HY000) at line 1: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_unicode_520_ci,COERCIBLE) for operation '=' I have tried changing the mysql.global_priv already and even tried to run this ALTER TABLE mysql.user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci; But it gives an error /* SQL Error (1347): 'mysql.user' is not of type 'BASE TABLE' */ This works fine but doesn't fix the issue

TABLE mysql.global_priv CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

Confirmation of the defaults being loaded at start mysqld would have been started with the following arguments: --socket=/run/mysqld/mysqld.sock --pid-file=/run/mysqld/mysqld.pid --basedir=/usr --skip-name-resolve --bind-address=0.0.0.0 --port=REDACTED --collation-server=utf8mb4_unicode_520_ci --init-connect=init-connect=SET NAMES utf8mb4 COLLATE utf8mb4_unicode_520_ci --character-set-server=utf8mb4 --character-set-client-handshake=FALSE --skip-character-set-client-handshake=1 --key_buffer_size=2G --myisam_sort_buffer_size=16M --max_allowed_packet=256M --myisam-recover-options=BACKUP --max_length_for_sort_data=8192 --expire_logs_days=7 --max_binlog_size=100M --max_connections=39000 --max_user_connections=38990 --wait_timeout=10 --back_log=4096 --max_connect_errors=4096 --table_open_cache=40000 --table_definition_cache=40000 --tmp_table_size=1G --max_heap_table_size=1G --innodb_buffer_pool_size=32G --innodb_log_file_size=8G --innodb_read_io_threads=64 --innodb_write_io_threads=64 --innodb_thread_concurrency=0 --innodb_flush_log_at_trx_commit=0 --innodb_flush_method=O_DIRECT --performance_schema=OFF --innodb-file-per-table=1 --innodb_io_capacity=2000 --innodb_table_locks=0 --innodb_lock_wait_timeout=50 --innodb_deadlock_detect=ON --query_cache_limit=0 --query_cache_size=0 --query_cache_type=0 --sql-mode=NO_ENGINE_SUBSTITUTION --log_error=/var/lib/mysql/mysql_error.log --log_queries_not_using_indexes=0 --long_query_time=5 --slow_query_log=1 --slow_query_log_file=/var/lib/mysql/mysql_slow.log --event_scheduler=ON --performance_schema_max_file_handles=999999 My show create table CREATE TABLE global_priv ( Host CHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_520_ci', User CHAR(128) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_520_ci', Priv LONGTEXT NOT NULL DEFAULT '{}' COLLATE 'utf8mb4_unicode_520_ci', PRIMARY KEY (Host, User) USING BTREE ) COMMENT='Users and global privileges' COLLATE='utf8mb4_unicode_520_ci' ENGINE=Aria ; I don't know what else to do. This doesn't seem to cause issues when connecting but it's annoying and also seems related to this issue here that had no solution https://dba.stackexchange.com/questions/339213/mariadb-cant-enforce-charset-and-collation-to-clients Anyone knows how to fix this?

I am pretty inexperienced with MariaDB, especially replication. However, being 'on call duty' I have an issue with this today. it looks like my replica is falling behind while I am taking a mysqldump of the replica. Is this to be expected behavior or is there an issue with my backup command below? mysqldump --defaults-file=$MYCNF -A --events --routines --master-data=2 | gzip -9 -c > ${BKPFILE} It looks like we are catching up after the dump is finished.

I have a table with data we've collected from various sources. It's got the item name, the source and the source's value. The table looks like the following: | id | name | source | value | | -- | ---- | ------ | ----- | | 1 | abc | web | 1 | | 2 | abc | print | 2 | | 3 | xyz | print | 8 | | 4 | xyz | web | 9 | Using the JSON_OBJECTAGG() function, I can group the sources together based on the name:

SELECT name, JSON_OBJECTAGG(source, value) AS data
FROM testStuff
GROUP BY name

This correctly gives: | name | data | | ---- | --------------------- | | abc | {"web":1, "print": 2} | | xyz | {"print":8, "web": 9} | Now, I want to run that query, but with a filter on the price/source, so I ran the following query:

SELECT name, JSON_OBJECTAGG(source, value) AS data
FROM testStuff
GROUP BY name
HAVING JSON_CONTAINS(data, 2, '$.print')

This *seems* to work, but there's a slight issue: | name | data | | ---- | ---------------------- | | abc | {"web":1, "print": 2}} | Why is there suddenly an extra } at the end of the JSON data? The HAVING clause is correctly filtering the rows, but why does it modify the result data? Online demo: https://sqlfiddle.com/mariadb/online-compiler?id=ccf1041c-3ada-4fc9-bd77-2b0fe1428392 I'm not sure what version of MariaDB that site uses, but testing this on my local computer using MariaDB 10.11.9 gives the same results. **EDIT**: It seems I can fix this by using:

SELECT name, JSON_OBJECTAGG(source, value) AS data
FROM testStuff
GROUP BY name
HAVING JSON_CONTAINS(JSON_OBJECTAGG(source, value), 2, '$.print')

I just was trying to avoid writing the JSON_OBJECTAGG() multiple times. Also, while this fixes the issue, I still am curious as to why this was an issue in the first place. **EDIT 2**: Another "fix" is to replace JSON_OBJECTAGG() with GROUP_CONCAT(). The following returns the correct data:

SELECT name,
    REPLACE(GROUP_CONCAT(JSON_OBJECT(source, value)), '},{', ',') AS data
FROM testStuff
GROUP BY name
HAVING JSON_CONTAINS(data, 2, '$.print')

I have a table like that: | Id | Note | Client | | -- | ------------------------------------------------------ | ------- | | 1 | Long note that have
multiple lines
Client: Name | null | | 2 | Anoter note that have multiple lines
Customer: Name | null | | 3 | Third note that have multiple lines
Client : Name
line | null | | 4 | Line: A
Client : Name
line | null | | 5 | Line B
B : Line
Customer : Name
line
others | null | I want to extract client informations from the "Note" column, to put it on the "Client" column with only one SQL query. I gave some example that represent the real thousand of line I have. I firstly made this query:

SELECT
    CASE
        WHEN posclient != 0 THEN SUBSTRING(
            Note,
            posclient + LENGTH('Client: '),
            LOCATE('\n', Note, posclient + LENGTH('Client: ')) - (posclient + LENGTH('Client: '))
        )
        WHEN poscustomer != 0 THEN SUBSTRING(
            Note,
            poscustomer + LENGTH('Customer : '),
            LOCATE('\n', Note, poscustomer + LENGTH('Customer : ')) - (poscustomer + LENGTH('Customer : '))
        )
        WHEN poscontact != 0 THEN SUBSTRING(
            Note,
            poscontact + LENGTH('Contact : '),
            LOCATE('\n', Note, poscontact + LENGTH('Contact : ')) - (poscontact + LENGTH('Contact : '))
        )
        ELSE ''
    END AS client,
    Id
FROM (SELECT Id, Note,
        LOCATE('Customer : ', Note) as poscustomer,
        LOCATE('Client: ', Note) as posclient,
        LOCATE('Contact : ', Note) as poscontact
    FROM myTable) x 
WHERE (poscustomer != 0 OR posclient != 0 OR poscontact != 0)

As there is duplicated code (the WHEN part), it's hard to read and not very fine if I have another way to add. And I have: there is at least 6 ways to write the client. I want to optimize this part:

WHEN poscontact != 0 THEN SUBSTRING(
   Note,
   poscontact + LENGTH('Contact : '),
   LOCATE('\n', Note, poscontact + LENGTH('Contact : ')) - (poscontact + LENGTH('Contact : '))
)

To be "generic" but I don't know how as I'm not in programming language. In php, that would be easy, but I can't use programming language, I have to use only one query. One thing that can be very helpful: the is only ONE way to write the client for each line. It will always contains only one. How can I do?

I've been reading the MariaDB manual and tutorials and apparently this should work: EXPLAIN CREATE TABLE prefix_database2.table1 LIKE prefix_database1.table1;` The LIKE operator is supposed to duplicate the table columns and keys. I'm at a loss at why I'm getting the syntax error. **How do I duplicate a table in a second database from the first while maintaining the exact same columns, keys, etc?**