Tag: MariaDB

How to calculate innodb_buffer_pool_size value

2160  admin  

Run to the following command to start calculation:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Example:

mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 60295069
Server version: 10.3.34-MariaDB MariaDB Server

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

… Read the rest

How to check if JSON_ARRAYAGG supported

2119  admin  
(last edited )

Run the following simple query

with t1 as ( select 0 as a ) select version(), JSON_ARRAYAGG(a) as j from t1 where a > 0;

Example:

MariaDB [(none)]> with t1 as ( select 0 as a ) select version(), JSON_ARRAYAGG(a) as j from t1 where a > 0;
+-----------------+------+
| version() | j |
+-----------------+------+
| 10.5.13-MariaDB | NULL |
+-----------------+------+
1 row in set (0.002 sec)

If it doesn’t support you should get:

MariaDB [(none)]> with t1 as ( select 0 as a ) select version(), JSON_ARRAYAGG(a) as j from t1 where a > 0;
ERROR 1305 (42000): FUNCTION JSON_ARRAYAGG does not exist

 

Source: https://mariadb.com/kb/en/json_arrayagg/… Read the rest

How to fix innodb_table_stats and innodb_index_stats has length mismatch

1246  admin  

To fix annoying warnings like:

[Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

Just go ahead and run mysql_upgrade.… Read the rest