mariadb – ALTER innoDB table from row to page compression

My tables have been created with InnoDB row compression (ENGINE=InnoDB ROW_FORMAT=COMPRESSED). Not I am changing them to page compression. According to the official documentation of MariaDB, enabling page compression affect the newly created tables only.

Thus, I create a replica table and use INSERT INTO SELECT.

I wonder if it is possible to ALTER a table to change the compression type (from row to page)?

mariadb – How to setup replication of 100Gb mysql database

I have 150Gb database on running project.
How to create replication for it without minimum downtime?

All manuals say: lock database, export data, import data at new server, configure and start replication.

In my case i assume exportimport will take forever. I could stop servers for several hours for weekend, but i think process could take more time than i have.

Database is on VDS, so I thought I could make a full image copy and clone it to another VDS, but how can configure it after that?

Current db server is MariaDB 10.1.38, so another related question is up: how can I (or should at all) to upgrade MariaDb itself?

mariadb – How to restore a Galera Cluster node from backup

I have a 5 node cluster, with 3 running as VMs on different hosts, and 2 nodes as physical machines. We had a power failure which took down the cluster, and I was not able to get any node to restart (using galera_new_cluster command). All nodes are current, as the power failure occurred during a weekend when everything was idle. I made a database backup, and my plan is to restore the database after getting all nodes running with no data. For the VMs, this is easy, as I can rewind the snapshot to just prior to starting with no data. For the physical servers, I’m not sure what is the best approach. Uninstall mariadb, delete the data, re-install?

update – MariaDB why do I get error “Unknown table x in field list” when it does (yes, another one!)

Using mariadb 10.4.13-MariaDB but I get the same error in a copy of the database running under 10.3.

The important part of the schema shows

/*Database: mamlocal*/
----------------------

/*Table Information*/
---------------------

Name                                  Engine  Version  Row_format    Rows  Avg_row_length  Data_length  Max_data_length  Index_length  Data_free  Auto_increment  Create_time          Update_time          Check_time  Collation          Checksum  Create_options  Comment                                                        Max_index_length  Temporary  
------------------------------------  ------  -------  ----------  ------  --------------  -----------  ---------------  ------------  ---------  --------------  -------------------  -------------------  ----------  -----------------  --------  --------------  -----------------------------------------------------------  ------------------  -----------
...
member                                InnoDB       10  Dynamic        706             162       114688                0         49152          0            4528  2020-08-17 14:48:55  2021-04-06 10:27:53  (NULL)      utf8_general_ci      (NULL)                                                                                                0  N          
...

 

These statements each produce the expected results.

SELECT * FROM information_schema.tables WHERE table_schema = 'mamlocal' AND table_name = 'member';
SELECT * FROM information_schema.tables WHERE  table_name = 'member';
SELECT member_id FROM member
SELECT member_id FROM mamlocal.member

but these produce Error Code: 1109 Unknown table 'member' in field list

UPDATE member SET  member_id = member_id ;
UPDATE mamlocal.member SET  member_id = member_id 

(The real update does something useful but I simplified it to try to find out where the error was)

Can anyone help me to identify why?

I have looked at these other questions but still cannot see what is wrong
Qn1
Qn2
Qn3

enter code here

mariadb – Database connection lost whenever SQL query string is too long

I recently switched from running my Rails app on a single VM to running the database — MariaDB 10.3 — on a separate (Debian Buster) VM. Now that the database is on a separate server, Rails immediately throws Mysql2::Error::ConnectionError: MySQL server has gone away whenever it tries to make a query where the SQL itself is very long. (They query itself isn’t necessarily one that would put significant load on the system.)

An example query that causes the problem looks like this:

SELECT `articles`.`id` FROM `articles` WHERE `articles`.`namespace` = 0 AND `articles`.`wiki_id` = 1 AND `articles`.`title` IN ('Abortion', 'American_Civil_Liberties_Union', 'Auschwitz_concentration_camp', 'Agent_Orange', 'Ahimsa')

… except the array of titles is about 5000 items long, and the full query string is ~158kB.

On the database side, this corresponds to warnings like this:

2021-03-25 15:47:13 10 (Warning) Aborted connection 10 to db: 'dashboard' user: 'outreachdashboard' host: 'programs-and-events-dashboard.globaleducation.eqiad1.wikimed' (Got an error reading communication packets)

The problem seems to be with the network layer, but I can’t get to the bottom of it. I’ve tried adjusting many MariaDB config variables (max_allowed_packet, innodb_log_buffer_size, innodb_log_file_size, innodb_buffer_pool_size) but none of those made a difference. The problem seems to be that the connection is aborted while it is attempting to transmit the long SQL query string from the app server to the database server. (There’s no corresponding problem with receiving large query results from the database.)

I’ve tried adjusting several timeout-related settings as well, although that seems unlikely to be the problem because I can replicate the connection error without any significant wait, just by issuing one of the long-SQL-string queries from a Rails console.

I’ve tried using tcpdump to see what’s coming in, but didn’t pick up any additional clues from that.

mariadb – What is the correct way to declare and use MarisDB user defined variables?

I’m using MariaDB version 10.3.28-MariaDB-cll-lve

This site https://mariadb.com/kb/en/user-defined-variables/ says

User-defined variables names must be preceded by a single at character (@)

That’s what I usually do but recently I was rushing, forgot the ‘@ and wrote the following function which seems to work fine without the variables having ‘@’ at the front.

So is having a ‘@’ in front of a user defined variable a firm requirement or not?

If it is, why does my function work correctly without it?

DELIMITER $$
CREATE  FUNCTION get_next_redacted_member_id() RETURNS INT(11)
BEGIN
       DECLARE result INT SIGNED;
       DECLARE lastid INT;
       INSERT INTO redacted_member_id ( date_used) VALUES ( NOW() ); 
       SET lastid = (SELECT LAST_INSERT_ID() );
       SET result =  -1 * lastid ;
       RETURN result;
END$$
DELIMITER ;

sql server – how to change “localhost” in MariaDB

I wonder if there is any way to change ‘localhost’ in MariaDB. for example, when you create a user in database, it shows ‘user’@’localhost’
I would like to know how to change ‘localhost’ to any other name. for example, I will be able to create a user in database like the following: ‘user’@’mydomain.com’
by the way, I’m very new in MariaDB and SQL server stuff.
thank you in advance!

mysql – MariaDB – Changing explicit_defaults_to_timestamp

open you my.conf/ini

find the section [mysqld]

and add explicit_defaults_for_timestamp = 1

sometimes you need instead

explicit_defaults_for_timestamp = ON

You can check after restarting mysql with
SHOW GLOBAL VARIABLES LIKE '%timestamp%'; if it is enabled

But this is a not standard option and could be removed at some point

mariadb – mysql.gtid_executed table is empty

I want to keep track of GTIDs and the corresponding mysqlbin log somewhere. I see this mysql.gtid_executed table, however on my mariadb DB, there are no rows in this table. Any idea how this gets populated ? These are the GTID related parameters. Any help is appreciated.

MariaDB ((none))> show variables like '%gtid%';
+-------------------------+--------------+
| Variable_name           | Value        |
+-------------------------+--------------+
| gtid_binlog_pos         | 0-10001-4866 |
| gtid_binlog_state       | 0-10001-4866 |
| gtid_cleanup_batch_size | 64           |
| gtid_current_pos        | 0-10001-4866 |
| gtid_domain_id          | 0            |
| gtid_ignore_duplicates  | OFF          |
| gtid_pos_auto_engines   |              |
| gtid_seq_no             | 0            |
| gtid_slave_pos          | 0-10001-4866 |
| gtid_strict_mode        | ON           |
| last_gtid               |              |
| wsrep_gtid_domain_id    | 0            |
| wsrep_gtid_mode         | OFF          |
| wsrep_gtid_seq_no       | 0            |
+-------------------------+--------------+

physical backup in mariadb 10.5 InnoDB: Unsupported redo log format

I have mariadb 10.5 version 3 node galera cluster, on node 1 physical backup is running/working but on node 2 and 3 physical backup script is not working and getting error…..
mariabackup –backup –target-dir=/var/mariadb/backup –user=root –password=xxxx@123

error

2021-03-22 8:29:28 0 [ERROR] InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.5.8.
[00] 2021-03-22 08:29:28 Error: cannot read redo log header

Can you please somebody suggest here what is going to wrong.