I have two MySQL instances (Percona server 5.6.31) in Master-Slave replication setup. I have set the below configuration: 1. ROW based replication is set. 2. Transaction Isolation is set to read-committed. Today, there was a insert going on in my Master. It was in the format INSERT INTO table1 SELECT * FROM table2 Table 2 has 200 million rows. Though the number of insert records was only 5000 but the operation lasted for 30 mins. I observed replication lag during the insert operation. I have load infile disabled due to security concerns. Hence I can't insert using that as well. I went this article from Percona which says that this can be resolved if txn isolation is used as ROW and versions above 5.1 that this is resolved. 1. In what way I can make my slave to be in sync with Master in such conditions? 2. Why does the slave lag here?

I have a MySQL 5.7 (5.7.19-17-57-log Percona XtraDB Cluster) cluster using Galera, but I am in the middle of upgrading to 8.0 -- 8.0.23-14 Percona Server (GPL), Release '14'. I wasn't sure if it was possible to join the 8.0 box to the clustered 5.7.

We are running Percona mysql server **5.7.36-39.1** and corresponding version of mysqlbinlog. I'm running mysqlbinlog against a binlog file and position (taken from the output of show master status;) with --verbose and/or --base64-output=DECODE-ROWS options but im not seeing the expected pseudo-SQL output based on https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog-row-events.html (note i have censored the actual BINLOG output)

# mysqlbinlog --base64-output=DECODE-ROWS --verbose --start-position 56871313 --stop-position 56871313 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

# mysqlbinlog --verbose --start-position 56871313 --stop-position 56871313 /pdisk1/mysql/replication/master-bin.019473
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220211 16:23:41 server id 176422933  end_log_pos 123 CRC32 0x8ffe454f 	Start: binlog v 4, server v 5.7.36-39-log created 220211 16:23:41
BINLOG '
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXX
'/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

However i do see pseudo-SQL for other BINLOG statements in the same binlog file. I'd be grateful if someone could point me in the right direction to decoding this BINLOG statement! Thanks!

I have a few Percona 5.6.30 servers with a TokuDB table that has ~120 million rows. On some of these servers, row count in SHOW TABLE STATUS mysteriously decreases until is hits 1. This is accompanied by rather unpleasant performance reduction and happens on both master and slave servers. I've tried to fix this by running ANALYZE TABLE, running OPTIMIZE TABLE, restarting the affected server, running ALTER TABLE ... FORCE. None of these has had any effect. The only two things that help are ALTER TABLE ... ENGINE=TokuDB and recreating the table from dumped data. These bring row count back to sane values, after which they begin to decrease again. Any hints about what might have caused this issue and how to fix it would be helpful.

I have a commercial web application that requires Percona Server for MySQL 8.x only. They don't support MySQL/MariaDB and even have code to check for these databases and abort the application. But I don't believe that they use any functions/features specific to Percona Server that make them incompatible with the stock MySQL/MariaDB. I checked the source code and there are no Percona-specific encryption functions , UUID_VX functions , data masking functions and no PERCONA_SEQUENCE_TABLE . I have disabled the check, and the application now runs in a testing environment with the stock Debian mariadb-server 10.11.11-0+deb12u1 without issues (yet). Is there anything else that can cause compatibility issues? EDIT: I'll repeat the title of the question: I'm looking for a list of functions and keywords specific to Percona MySQL 8.x so I can grep the source code. MariaDB compatibility is out of scope. I have already identified a possible scenario. Their previous versions supported MariaDB (our installation has been running on stock MariaDB/MySQL included in Debian for more than a decade). In the recent version, they added JSON fields but this feature wasn't supported by older MariaDB versions included in Debian before Bookworm so they went with a simple solution and wrote down into the requirements that they support the only database they have locally - Percona. But this is just my assumption and I need exact proof that their requirement to run the app with Percona has no basis, so I can push through the dismissive support to the developers. UPDATE: I managed to get to the developers and they confirmed what I suspected: "We officially support only Percona 8 = Because it is the DB we use in the cloud and test on it. Unofficially I can say that we work with MySql 8.0 without any problems.". I also managed to get them to describe some specific issues they encountered with MariaDB and they all are caused by the use of JSON type fields. They refuse to fix these issues on the ground that "JSON is not inherently included in the basic SQL standard, but it is part of the SQL:2016 standard (ISO/IEC 9075:2016), which MariaDB does not support.". After being told that JSON fields are not part of the standard, they stopped communicating.

We have a development database server with Percona-server 5.7.15-9 on it. It replicates two schemas from two different production servers using multi-source GTID replication. Lets call these schemas alice and bob. On the dev server we clone this replicas of production databases to get databases for development. They are called 1_alice, 1_bob, 2_alice, 2_bob, etc. All of them use the same instance of MySQL. For fast cloning we use Percona XtraBackup as described here https://www.percona.com/doc/percona-xtrabackup/2.4/innobackupex/restoring_individual_tables_ibk.html In the past there were only one replica (alice) and we used binary log position for replication instead of GTID. These times everything worked fine and fast. One day (I'm not sure when exactly) it became broken. Now when I perform ALTER TABLE 2_alice.access_group IMPORT TABLESPACE query, it hangs in the 'System lock' state. And could be hanging in this state from 1 min 'till 1 hour and more (then it works). There are no more active connections instead of two replicas, but they doesn't use 2_alice schema. Why is IMPORT TABLESPACE query hanging and how could I debug this case?

We have a 2 node (plus an arbiter) Percona XtraDB cluster. It is used for data indexing/archiving. It has it's own database called archive but it is also the replication slave for a database called primary. This comes from a standard master->slave Percona Server pair. One of the clustered servers segfaulted (reason still being investigated...). This node was, specifically, the one that was doing replication. On restarting it, it deleted the contents of the MySQL data directory (/var/lib/mysql/) and did an SST from the other node. This went fine, but it now has a partially incomplete dataset on the primary database and no master binlog position information. How should replication be restored? - It is not feasible to export the primary database as a mysql dump since it is circa 1TB in size when looking at the raw innodb files - If we export the primary DB with xtrabackup and prepare it as a whole on the clustered node, it is assumed it will get deleted as soon as the node starts SST - It is difficult but not infeasible to allow the node to do SST and then restore an xtrabackup of primary DB table by table... but it will take a while given 500+ tables So, the question is... What is the correct way to set up replication into a multi-master cluster?

Time to upgrade hardware? ... I'm not sure if there is much more I can do, spent a few days tweaking/googling -- before taking the upgrade plunge I thought I'd check here... to see if the well experienced DBAs might offer some further hints. My server has 64GB of RAM and 4c/8t processors with SSD drives. It is dedicated to MySQL (Percona 5.7). The CPUs are nearly always maxed out, yet only about 15% of memory is ever utilized. Disk IO seems always near max too... I've tweaked all the settings I could find to reduce CPU/and disk IO. Our application is very active -- the backend scans approx 1.5Millon locations stored in the database for any changes to information about what might be at this location, and if there is change it updates the relative table in the db -- on the front end is a live map that queries this data and maintains a connection to update the map as the data updates. Most of my slow queries in the slow query log are related to queries getting this updated information to the map for the end user. My tables are indexed. Losing data in a crash would not be a big deal. MySQL status: -------------- mysql Ver 14.14 Distrib 5.7.20-19, for debian-linux-gnu (x86_64) using 6.3 Server version: 5.7.20-19-log Percona Server (GPL), Release '19', Revision '3c5d3e5d53c' Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 17 hours 53 min 27 sec Threads: 748 Questions: 328898348 Slow queries: 980150 Opens: 7302 Flush tables: 1 Open tables: 1445 Queries per second avg: 5106.562 my.cnf: [client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] # GENERAL # user = mysql default-storage-engine = InnoDB socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci port = 3306 performance-schema = false # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 skip-name-resolve # DATA STORAGE # datadir = /var/lib/mysql/ # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 0 query-cache-size = 0 max-connections = 1000 thread-cache-size = 100 thread_pool_size = 32 open-files-limit = 65535 table-definition-cache = 1024 table-open-cache = 2048 sync_binlog = 0 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 2G innodb_log_buffer_size = 8M innodb-flush-log-at-trx-commit = 2 innodb-file-per-table = 1 innodb-buffer-pool-size = 54G innodb_buffer_pool_instances = 10 innodb_write_io_threads = 16 innodb_read_io_threads = 16 # LOGGING # log-error = /var/lib/mysql/mysql-error.log log-queries-not-using-indexes = 1 slow-query-log = 1 slow-query-log-file = /var/lib/mysql/mysql-slow.log htop: iostat: avg-cpu: %user %nice %system %iowait %steal %idle 98.83 0.00 1.18 0.00 0.00 0.00 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util nvme0n1 0.00 13.40 0.00 58.20 0.00 1306.50 44.90 0.00 0.08 0.00 0.08 0.04 0.24 md2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 md3 0.00 0.00 0.00 78.20 0.00 1290.40 33.00 0.00 0.00 0.00 0.00 0.00 0.00 nvme1n1 0.00 13.40 0.00 58.20 0.00 1306.50 44.90 0.00 0.03 0.00 0.03 0.01 0.08 avg-cpu: %user %nice %system %iowait %steal %idle 98.85 0.00 1.15 0.00 0.00 0.00 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util nvme0n1 0.00 11.40 0.00 58.40 0.00 1178.20 40.35 0.00 0.07 0.00 0.07 0.03 0.16 md2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 md3 0.00 0.00 0.00 76.80 0.00 1162.40 30.27 0.00 0.00 0.00 0.00 0.00 0.00 nvme1n1 0.00 11.40 0.00 58.40 0.00 1178.20 40.35 0.00 0.08 0.00 0.08 0.03 0.16 avg-cpu: %user %nice %system %iowait %steal %idle 98.90 0.00 1.10 0.00 0.00 0.00 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util nvme0n1 0.00 10.40 0.00 52.80 0.00 1093.90 41.44 0.00 0.06 0.00 0.06 0.02 0.08 md2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 md3 0.00 0.00 0.00 70.20 0.00 1078.40 30.72 0.00 0.00 0.00 0.00 0.00 0.00 nvme1n1 0.00 10.40 0.00 52.80 0.00 1093.90 41.44 0.00 0.06 0.00 0.06 0.03 0.16 avg-cpu: %user %nice %system %iowait %steal %idle 98.80 0.00 1.20 0.00 0.00 0.00 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util nvme0n1 0.00 8.80 0.00 50.80 0.00 1082.30 42.61 0.00 0.02 0.00 0.02 0.00 0.00 md2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 md3 0.00 0.00 0.00 67.80 0.00 1066.40 31.46 0.00 0.00 0.00 0.00 0.00 0.00 nvme1n1 0.00 8.80 0.00 50.80 0.00 1082.30 42.61 0.00 0.02 0.00 0.02 0.00 0.00 mysqltuner.pl: >> MySQLTuner 1.7.5 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.7.20-19-log [OK] Operating on 64-bit architecture -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 19h 3m 15s (350M q [5K qps], 1M conn, TX: 156G, RX: 97G) [--] Reads / Writes: 62% / 38% [--] Binary logging is disabled [--] Physical Memory : 62.6G [--] Max MySQL memory : 56.1G [--] Other process memory: 115.1M [--] Total buffers: 55.0G global + 1.1M per thread (1000 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [!!] Maximum reached memory usage: 55.9G (89.22% of installed RAM) [!!] Maximum possible memory usage: 56.1G (89.65% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (1M/350M) [OK] Highest usage of available connections: 75% (753/1000) [OK] Aborted connections: 0.00% (36/1607260) [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 18M sorts) [!!] Joins performed without indexes: 6435 [OK] Temporary tables created on disk: 0% (65 on disk / 187K total) [OK] Thread cache hit rate: 99% (1K created / 1M connections) [!!] Table cache hit rate: 18% (1K open / 7K opened) [OK] Open file limit used: 0% (45/1M) [OK] Table locks acquired immediately: 100% (2K immediate / 2K locks) -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. [--] Memory used by P_S: 0B [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 32 thread(s). [OK] thread_pool_size between 16 and 36 when using InnoDB storage engine. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.3% (1M used / 8M cache) [OK] Key buffer size / total MyISAM indexes: 8.0M/44.0K [!!] Read Key buffer hit rate: 93.8% (112 cached / 7 reads) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 55.0G/1.8G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (7.27272727272727 %): 2.0G * 2/55.0G should be equal 25% [!!] InnoDB buffer pool instances: 10 [--] Number of InnoDB Buffer Pool Chunk : 440 for 10 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 100.00% (77730783334 hits/ 77730970038 total) [!!] InnoDB Write Log efficiency: 48.21% (32580982 hits/ 67583255 total) [OK] InnoDB log waits: 0.00% (0 waits / 35002273 writes) -------- Recommendations --------------------------------------------------------------------------- General recommendations: Control warning line(s) into /var/lib/mysql/mysql-error.log file Control error line(s) into /var/lib/mysql/mysql-error.log file Restrict Host for user@% to user@SpecificDNSorIp MySQL started within last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Adjust your join queries to always utilize indexes Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: Beware that open_files_limit (1048576) variable should be greater than table_open_cache (2048) Performance should be activated for better diagnostics Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: Variables to adjust: join_buffer_size (> 256.0K, or always use indexes with joins) table_open_cache (> 2048) performance_schema = ON enable PFS innodb_log_file_size should be (=6G) if possible, so InnoDB total log files size equals to 25% of buffer pool size. innodb_buffer_pool_instances(=55)

We recently migrated our mysql to new hardware and it was running in slave mode for 15 days. We made it the master 11th June. On 13th June it restarted for the first time with signal 11. Stacktrace from 1st segfault - > 04:32:32 UTC - mysqld got signal 11 ; This could be because you hit a > bug. It is also possible that this binary or one of the libraries it > was linked against is corrupt, improperly built, or misconfigured. > This error can also be caused by malfunctioning hardware. We will try > our best to scrape up some info that will hopefully help diagnose the > problem, but since we have already crashed, something is definitely > wrong and this may fail. Please help us make Percona Server better by > reporting any bugs at http://bugs.percona.com/ > > key_buffer_size=33554432 read_buffer_size=131072 > max_used_connections=547 max_threads=5002 thread_count=430 > connection_count=430 It is possible that mysqld could use up to > key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = > 2023198 K bytes of memory Hope that's ok; if not, decrease some > variables in the equation. > > Thread pointer: 0x2a83900 Attempting backtrace. You can use the > following information to find out where mysqld died. If you see no > messages after this, something went terribly wrong... stack_bottom = > 7f86b8c24e88 thread_stack 0x30000 > /usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x8c66bc] > /usr/sbin/mysqld(handle_fatal_signal+0x469)[0x64d079] > /lib/x86_64-linux-gnu/libpthread.so.0(+0xf890)[0x7f8e9871c890] > /usr/sbin/mysqld(_Z25gtid_pre_statement_checksPK3THD+0x0)[0x848820] > /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x316)[0x6cb8a6] > /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x5d8)[0x6d15e8] > /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x117f)[0x6d2eaf] > /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1a2)[0x69f962] > /usr/sbin/mysqld(handle_one_connection+0x40)[0x69fa00] > /usr/sbin/mysqld(pfs_spawn_thread+0x146)[0x8fbfe6] > /lib/x86_64-linux-gnu/libpthread.so.0(+0x8064)[0x7f8e98715064] > /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f8e9675462d] > > Trying to get some variables. Some pointers may be invalid and cause > the dump to abort. Query (7f85e012ee80): is an invalid pointer > Connection ID (thread ID): 247827 Status: NOT_KILLED Again after 2 days mysql restarted 3 times with the following traces. Dump 2 - 02:15:36 UTC - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Please help us make Percona Server better by reporting any bugs at http://bugs.percona.com/ key_buffer_size=33554432 read_buffer_size=131072 max_used_connections=723 max_threads=5002 thread_count=358 connection_count=358 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2023198 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x2b73a90 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f0e12d45e88 thread_stack 0x30000 /usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x8c66bc] /usr/sbin/mysqld(handle_fatal_signal+0x469)[0x64d079] /lib/x86_64-linux-gnu/libpthread.so.0(+0xf890)[0x7f15f20b9890] /usr/sbin/mysqld[0x64b000] /usr/sbin/mysqld(vio_io_wait+0x76)[0xb77b56] /usr/sbin/mysqld(vio_socket_io_wait+0x18)[0xb77bf8] /usr/sbin/mysqld(vio_read+0xca)[0xb77cda] /usr/sbin/mysqld[0x642203] /usr/sbin/mysqld[0x6424f4] /usr/sbin/mysqld(my_net_read+0x304)[0x6432e4] /usr/sbin/mysqld(_Z10do_commandP3THD+0xca)[0x6d413a] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1a2)[0x69f962] /usr/sbin/mysqld(handle_one_connection+0x40)[0x69fa00] /usr/sbin/mysqld(pfs_spawn_thread+0x146)[0x8fbfe6] /lib/x86_64-linux-gnu/libpthread.so.0(+0x8064)[0x7f15f20b2064] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f15f00f162d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0): is an invalid pointer Connection ID (thread ID): 40900 Status: NOT_KILLED Dump 3 - 02:36:32 UTC - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Please help us make Percona Server better by reporting any bugs at http://bugs.percona.com/ key_buffer_size=33554432 read_buffer_size=131072 max_used_connections=401 max_threads=5002 thread_count=369 connection_count=369 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2023198 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x32448f0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f2fb82c3e88 thread_stack 0x30000 /usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x8c66bc] /usr/sbin/mysqld(handle_fatal_signal+0x469)[0x64d079] /lib/x86_64-linux-gnu/libpthread.so.0(+0xf890)[0x7f3792426890] /usr/sbin/mysqld(_ZN9PROFILING15start_new_queryEPKc+0x0)[0x6e60a0] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x47)[0x6d1d77] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1a2)[0x69f962] /usr/sbin/mysqld(handle_one_connection+0x40)[0x69fa00] /usr/sbin/mysqld(pfs_spawn_thread+0x146)[0x8fbfe6] /lib/x86_64-linux-gnu/libpthread.so.0(+0x8064)[0x7f379241f064] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f379045e62d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0): is an invalid pointer Connection ID (thread ID): 482 Status: NOT_KILLED After these restarts we did a master slave switch and took this box out of active cluster. We were running simple sysbench oltp_read_write test to see if it happens again. 2 days after starting the benchmark it is happened again on the same machine with trace - 10:51:18 UTC - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Please help us make Percona Server better by reporting any bugs at http://bugs.percona.com/ key_buffer_size=33554432 read_buffer_size=131072 max_used_connections=4 max_threads=5002 thread_count=3 connection_count=3 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2023198 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x22c8270 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f65ec060e88 thread_stack 0x30000 /usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x8c66bc] /usr/sbin/mysqld(handle_fatal_signal+0x469)[0x64d079] /lib/x86_64-linux-gnu/libpthread.so.0(+0xf890)[0x7f6644371890] /lib/x86_64-linux-gnu/libc.so.6(__poll+0x0)[0x7f66423a0ac0] /usr/sbin/mysqld(vio_io_wait+0x86)[0xb77b66] /usr/sbin/mysqld(vio_socket_io_wait+0x18)[0xb77bf8] /usr/sbin/mysqld(vio_read+0xca)[0xb77cda] /usr/sbin/mysqld[0x642203] /usr/sbin/mysqld[0x6424f4] /usr/sbin/mysqld(my_net_read+0x304)[0x6432e4] /usr/sbin/mysqld(_Z10do_commandP3THD+0xca)[0x6d413a] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x1a2)[0x69f962] /usr/sbin/mysqld(handle_one_connection+0x40)[0x69fa00] /usr/sbin/mysqld(pfs_spawn_thread+0x146)[0x8fbfe6] /lib/x86_64-linux-gnu/libpthread.so.0(+0x8064)[0x7f664436a064] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f66423a962d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0): is an invalid pointer Connection ID (thread ID): 32 Status: NOT_KILLED Logs from sysbench - FATAL: mysql_drv_query() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT SUM(k) FROM sbtest20 WHERE id BETWEEN 5008643 AND 5008742' FATAL: mysql_drv_query() returned error 2013 (Lost connection to MySQL server during query) for query 'DELETE FROM sbtest4 WHERE id=5025943' FATAL: mysql_drv_query() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT SUM(k) FROM sbtest15 WHERE id BETWEEN 5049412 AND 5049511' FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:432: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:487: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:432: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query Error in my_thread_global_end(): 3 threads didn't exit Our new master also restarted today with similar trace. Can someone help us debug this? We cannot enable general query log since the load is very high and it fills up the disk. We cannot deterministically reproduce this as well. Mysql version is - 5.6.40-84.0-log Debian version is - Linux version 3.16.0-6-amd64 (debian-kernel@lists.debian.org) (gcc version 4.9.2 (Debian 4.9.2-10+deb8u1) ) #1 SMP Debian 3.16.56-1+deb8u1 (2018-05-08) Machine memory is 40GB Innodb buffer pool is 30GB

I had to install my mysql slave server due to storage issue. I have synced my master disk to the slave disk. Now i'm not able to start my slave server (4GB RAM) due to the error: Unknown table engine 'InnoDB' I can see that: mysql> show variables like 'have_innodb'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_innodb | NO | +---------------+-------+ 1 row in set (0.00 sec) I went over some similar questions, and tried to do as recommended. Nothing helped so far. Here is what i've tried: - Delete/rename/move ib_logfile - Check if skip-innodb is enabled (was disabled). - Edit innodb configuration settings Here is my current settings. cat my.cnf | grep inno #ignore-builtin-innodb #plugin-load=innodb=ha_innodb_plugin.so #skip-innodb #innodb_io_capacity=400 #innodb_support_xa=0 innodb_additional_mem_pool_size = 50M innodb_buffer_pool_size = 1G innodb_data_file_path = ibdata1:200G;ibdata2:20G;ibdata3:20G;ibdata4:20G;ibdata5:20G;ibdata6:20G;ibdata7:10G;ibdata8:10G;ibdata9:20G;ibdata10:10G;ibdata11:10G;ibdata12:10G;ibdata13:5G;ibdata14:5G;ibdata15:20G;ibdata16:10G;ibdata17:20G;ibdata18:20G;ibdata19:20G:autoextend innodb_autoextend_increment = 1000 innodb_data_home_dir = /data/mysql/ innodb_file_io_threads = 4 #innodb_force_recovery=1 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 0 #innodb_fast_shutdown innodb_log_buffer_size = 8M innodb_log_file_size = 500M innodb_log_files_in_group = 2 #innodb_log_group_home_dir innodb_max_dirty_pages_pct = 90 #innodb_flush_method=O_DSYNC innodb_lock_wait_timeout = 120 Here is my data folder: ll /data -rw-r--r-- 1 mysql mysql 0 Dec 27 2013 i_am_new_data -rw-rw---- 1 mysql mysql 500M Dec 28 2013 ib_logfile0.bak -rw-rw---- 1 mysql mysql 500M Dec 28 2013 ib_logfile1.bak -rw-rw---- 1 mysql mysql 500M Dec 28 2013 ib_logfile2.bak -rw-r--r-- 1 mysql mysql 22K Mar 22 03:53 my.cnf drwxr-xr-x 13 mysql mysql 4.0K Mar 22 03:56 mysql drwxr-xr-x 2 mysql mysql 4.0K Dec 27 2013 recovering drwxr-xr-x 2 mysql mysql 6 Mar 22 03:56 tmp Here is my /data/mysql folder: drwx------ 2 mysql mysql 19 Dec 28 2013 default -rw-rw---- 1 mysql mysql 12G Mar 22 01:04 ibdata1 -rw-rw---- 1 mysql mysql 2.0G Mar 22 01:04 ib_logfile0 -rw-rw---- 1 mysql mysql 2.0G Mar 22 01:04 ib_logfile1 -rw-rw---- 1 mysql mysql 76 Mar 22 04:16 master.info drwx------ 2 mysql mysql 4.0K Dec 28 2013 mysql -rw-rw---- 1 mysql mysql 1.1G Mar 12 07:19 mysql-bin.000314 -rw-rw---- 1 mysql mysql 1.1G Mar 14 17:56 mysql-bin.000315 -rw-rw---- 1 mysql mysql 1.1G Mar 17 09:53 mysql-bin.000316 -rw-rw---- 1 mysql mysql 602M Mar 19 12:34 mysql-bin.000317 -rw-rw---- 1 mysql mysql 18M Mar 19 13:18 mysql-bin.000318 -rw-rw---- 1 mysql mysql 125 Mar 19 13:18 mysql-bin.000319 -rw-rw---- 1 mysql mysql 313M Mar 20 08:45 mysql-bin.000320 -rw-rw---- 1 mysql mysql 3.8M Mar 20 08:58 mysql-bin.000321 -rw-rw---- 1 mysql mysql 125 Mar 20 08:58 mysql-bin.000322 -rw-rw---- 1 mysql mysql 765M Mar 22 01:04 mysql-bin.000323 -rw-rw---- 1 mysql mysql 125 Mar 22 02:20 mysql-bin.000324 -rw-rw---- 1 mysql mysql 125 Mar 22 02:21 mysql-bin.000325 -rw-rw---- 1 mysql mysql 125 Mar 22 02:33 mysql-bin.000326 -rw-rw---- 1 mysql mysql 125 Mar 22 02:36 mysql-bin.000327 -rw-rw---- 1 mysql mysql 125 Mar 22 02:36 mysql-bin.000328 -rw-rw---- 1 mysql mysql 125 Mar 22 03:10 mysql-bin.000329 -rw-rw---- 1 mysql mysql 125 Mar 22 03:13 mysql-bin.000330 -rw-rw---- 1 mysql mysql 125 Mar 22 03:15 mysql-bin.000331 -rw-rw---- 1 mysql mysql 125 Mar 22 03:47 mysql-bin.000332 -rw-rw---- 1 mysql mysql 125 Mar 22 03:53 mysql-bin.000333 -rw-rw---- 1 mysql mysql 125 Mar 22 03:56 mysql-bin.000334 -rw-rw---- 1 mysql mysql 106 Mar 22 03:56 mysql-bin.000335 -rw-rw---- 1 mysql mysql 399 Mar 22 03:56 mysql-bin.index -rw-rw---- 1 mysql mysql 155 Mar 22 02:18 mysql-relay-bin.000001 -rw-rw---- 1 mysql mysql 173K Mar 22 02:18 mysql-relay-bin.000002 -rw-rw---- 1 mysql mysql 5.8M Mar 22 02:20 mysql-relay-bin.000003 -rw-rw---- 1 mysql mysql 155 Mar 22 02:20 mysql-relay-bin.000004 -rw-rw---- 1 mysql mysql 49K Mar 22 02:21 mysql-relay-bin.000005 -rw-rw---- 1 mysql mysql 155 Mar 22 02:22 mysql-relay-bin.000006 -rw-rw---- 1 mysql mysql 564K Mar 22 02:33 mysql-relay-bin.000007 -rw-rw---- 1 mysql mysql 155 Mar 22 02:35 mysql-relay-bin.000008 -rw-rw---- 1 mysql mysql 116K Mar 22 02:36 mysql-relay-bin.000009 -rw-rw---- 1 mysql mysql 125 Mar 22 02:36 mysql-relay-bin.000010 -rw-rw---- 1 mysql mysql 125 Mar 22 03:10 mysql-relay-bin.000011 -rw-rw---- 1 mysql mysql 155 Mar 22 03:10 mysql-relay-bin.000012 -rw-rw---- 1 mysql mysql 1.7M Mar 22 03:13 mysql-relay-bin.000013 -rw-rw---- 1 mysql mysql 155 Mar 22 03:14 mysql-relay-bin.000014 -rw-rw---- 1 mysql mysql 85K Mar 22 03:15 mysql-relay-bin.000015 -rw-rw---- 1 mysql mysql 155 Mar 22 03:46 mysql-relay-bin.000016 -rw-rw---- 1 mysql mysql 2.5M Mar 22 03:47 mysql-relay-bin.000017 -rw-rw---- 1 mysql mysql 155 Mar 22 03:47 mysql-relay-bin.000018 -rw-rw---- 1 mysql mysql 762K Mar 22 03:53 mysql-relay-bin.000019 -rw-rw---- 1 mysql mysql 155 Mar 22 03:54 mysql-relay-bin.000020 -rw-rw---- 1 mysql mysql 203K Mar 22 03:56 mysql-relay-bin.000021 -rw-rw---- 1 mysql mysql 155 Mar 22 03:56 mysql-relay-bin.000022 -rw-rw---- 1 mysql mysql 1.7M Mar 22 04:16 mysql-relay-bin.000023 -rw-rw---- 1 mysql mysql 575 Mar 22 03:56 mysql-relay-bin.index srwxrwxrwx 1 mysql mysql 0 Mar 22 03:56 mysql.sock -rw-r--r-- 1 mysql mysql 6 Dec 28 2013 mysql_upgrade_info drwx------ 2 mysql mysql 59 Jan 21 2016 percona -rw-rw---- 1 mysql mysql 56 Mar 22 03:56 relay-log.info drwx------ 2 mysql mysql 48 Dec 28 2013 test drwx------ 2 mysql mysql 57 Dec 28 2013 test2 drwx------ 2 mysql mysql 4.0K Mar 1 13:08 mydb0 drwx------ 2 mysql mysql 4.0K Mar 1 13:08 mydb1 drwx------ 2 mysql mysql 4.0K Mar 1 13:08 mydb2 drwx------ 2 mysql mysql 4.0K Mar 1 13:08 mydb3 drwx------ 2 mysql mysql 4.0K Mar 1 13:08 mydb4 drwx------ 2 mysql mysql 4.0K Mar 1 13:08 mydb5 -rw-rw---- 1 mysql mysql 3.9K Dec 27 2013 mydb5_percona.test.co.il.err -rw-rw---- 1 mysql mysql 117K Mar 22 01:04 mydb5_repair.test.co.il.err -rw-r----- 1 mysql root 28K Mar 22 03:56 mydb5_slave.test.co.il.test.co.il.err -rw-rw---- 1 mysql mysql 5 Mar 22 03:56 mydb5_slave.test.co.il.test.co.il.pid log file: 170322 03:46:22 mysqld_safe Starting mysqld daemon with databases from /data/mysql 170322 3:46:22 [Note] Plugin 'FEDERATED' is disabled. InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Compressed tables use zlib 1.2.3 170322 3:46:22 InnoDB: Initializing buffer pool, size = 3.0G 170322 3:46:22 InnoDB: Completed initialization of buffer pool InnoDB: Error: data file /data/mysql/ibdata1 is of a different size InnoDB: 770688 pages (rounded down to MB) InnoDB: than specified in the .cnf file 13107200 pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 170322 3:46:22 [ERROR] Plugin 'InnoDB' init function returned error. 170322 3:46:22 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 170322 3:46:23 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.1.73-rel14.11-log' socket: '/data/mysql/mysql.sock' port: 3306 Percona Server (GPL), 14.11, Revision 603 170322 3:46:36 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000323' at position 801756782, relay log './mysql-relay-bin.000002' position: 251 170322 3:46:36 [ERROR] Slave SQL: Error 'Unknown table engine 'InnoDB'' on query. Default database: ''. Query: 'INSERT INTO mydb4.obj_mail. Error_code: 1286 170322 3:46:36 [Warning] Slave: Unknown table engine 'InnoDB' Error_code: 1286 170322 3:46:36 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000323' position 801756782 170322 3:46:36 [Note] Slave I/O thread: connected to master 'repl@192.118.71.35:3306',replication started in log 'mysql-bin.000324' at position 8573354 170322 3:47:33 [Note] /usr/sbin/mysqld: Normal shutdown 170322 3:47:33 [Note] Slave I/O thread killed while reading event 170322 3:47:33 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000324', position 11116963 170322 3:47:33 [Note] /usr/sbin/mysqld: Shutdown complete 170322 03:47:33 mysqld_safe mysqld from pid file /data/mysql/mydb5_slave.test.co.il.test.co.il.pid ended 170322 03:47:39 mysqld_safe Starting mysqld daemon with databases from /data/mysql 170322 3:47:39 [Note] Plugin 'FEDERATED' is disabled. InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Compressed tables use zlib 1.2.3 170322 3:47:39 InnoDB: Initializing buffer pool, size = 3.0G 170322 3:47:40 InnoDB: Completed initialization of buffer pool InnoDB: Error: data file /data/mysql/ibdata1 is of a different size InnoDB: 770688 pages (rounded down to MB) InnoDB: than specified in the .cnf file 13107200 pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 170322 3:47:40 [ERROR] Plugin 'InnoDB' init function returned error. 170322 3:47:40 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 170322 3:47:40 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.1.73-rel14.11-log' socket: '/data/mysql/mysql.sock' port: 3306 Percona Server (GPL), 14.11, Revision 603 170322 3:47:51 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000323' at position 801756782, relay log './mysql-relay-bin.000002' position: 251 170322 3:47:51 [Note] Slave I/O thread: connected to master 'repl@192.118.71.35:3306',replication started in log 'mysql-bin.000324' at position 11116963 170322 3:47:51 [ERROR] Slave SQL: Error 'Unknown table engine 'InnoDB'' on query. Default database: ''. Query: 'INSERT INTO mydb4.obj_mail, Error_code: 1286 170322 3:47:51 [Warning] Slave: Unknown table engine 'InnoDB' Error_code: 1286 170322 3:47:51 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000323' position 801756782 Please save me. I feel lost.

Has anyone had any luck using the audit_log_filter plugin / component from Percona? I have installed Percona Server for MySQL 8.0 (8.0.41-32) and Percona Server for MySQL 8.4 (8.4.3-3) and followed the instructions to install it. I'm using Debian 12 (bookworm) if that makes any difference. e.g. 1) run the script audit_log_filter_linux_install.sql to create the two tables and install the plugin / component. 2) set up logging using: SELECT audit_log_filter_set_filter('log_all', '{"filter": {"log": true}}'); SELECT audit_log_filter_set_user('%', 'log_all'); This should enable logging of all events for all users. I then run a series of queries, that include create schema / create table / insert / update / delete / drop etc. I've tried it with different users I've tried it on workbench and from the cmdline And yet the only thing that ever appears in the log is: Audit 0_2025-03-13T11:47:51 2025-03-13T11:47:51 1 which is generated when the server restarts. Am I missing something, or does it simply not work?

So what I'm trying to do is create a temporary table with a unioned set of results that have distinct conditions. The information is largely the same, just in different places that need to be matched. My query is below;

DROP TABLE IF EXISTS temp_data;
CREATE TEMPORARY TABLE IF NOT EXISTS temp_data AS (SELECT * FROM 
  ((SELECT
      src.value AS 'sr_code',
      DATE(rd.value) AS 'release_date',
      sw.value AS 'songwriter',
      SEC_TO_TIME(rt.value) AS 'runtime',
      COALESCE(track.original, 0) AS 'original',
      COALESCE(track.exclude_mlc, 0) AS 'exclude_mlc'
  FROM catalog_product_entity prod
  LEFT JOIN catalog_product_entity_datetime rd ON rd.row_id = prod.row_id AND rd.store_id = 0 AND rd.attribute_id = 249
  LEFT JOIN catalog_product_entity_varchar src ON src.row_id = prod.row_id AND src.store_id = 0 AND src.attribute_id = 218
  LEFT JOIN catalog_product_entity_varchar sw ON sw.row_id = prod.row_id AND sw.store_id = 0 AND sw.attribute_id = 308
  LEFT JOIN catalog_product_entity_int rt ON rt.row_id = prod.row_id AND rt.store_id = 0 AND rt.attribute_id = 226
  LEFT JOIN downloadable_link link ON prod.row_id = link.product_id
  LEFT JOIN downloadable_link_track track ON track.link_id = link.link_id 
  WHERE prod.attribute_set_id = 13 AND src.value IS NOT NULL AND track.original = 0 AND track.exclude_mlc = 0)
  UNION ALL
  (SELECT
      track.sr_code AS 'sr_code',
      DATE(rd.value) AS 'release_date',
      track.songwriter AS 'songwriter',
      SEC_TO_TIME(track.runtime) AS 'runtime',
      COALESCE(track.original, 0) AS 'original',
      COALESCE(track.exclude_mlc, 0) AS 'exclude_mlc'
  FROM catalog_product_entity prod
  LEFT JOIN catalog_product_entity_datetime rd ON rd.row_id = prod.row_id AND rd.store_id = 0 AND rd.attribute_id = 249
  LEFT JOIN downloadable_link link ON link.product_id = prod.row_id
  LEFT JOIN downloadable_link_track track ON track.link_id = link.link_id
  LEFT JOIN downloadable_link_title title ON title.link_id = link.link_id
  WHERE prod.attribute_set_id = 12 AND track.sr_code IS NOT NULL AND track.original = 0 AND track.exclude_mlc = 0))
AS 'temp_data_table');

But when I run the query, I'm returned with a syntax error. At first, I was just running the union selects as the create statement, which I learned you cannot do. I wrapped it in another select to use that statement as a subquery (as indicated here - https://stackoverflow.com/a/31751787/18582667) . I'm able to get the SELECT + UNION + SELECT to run without the temp table syntax so my query itself seems to be fine. This is what I receive:

1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''temp_data_table')' at line 31

I'm not sure what I did wrong and why it's throwing that at me. I'm leaning towards parens, but I don't have any non-paired ones in there. I am working with a Magento 2 install, but I don't believe that's entirely relevant. Throwing it in there just to be thorough. Running MySQL 5.6.42-84.2 Percona Server (GPL), Release 84.2, Revision 6b2b987 on Red Hat Enterprise Linux Server 7.9 (Maipo).

We have a percona mysql database where one of the table which is touching almost 26.8Gb in size. The server is just 4Gb but its specifically only hosting mysql. Now when I am trying to run a simple query on this specific table is takes almost 5 minutes. Before increasing the ram is there anything else I could optimise. The current settings > innodb_buffer_pool_size=3072M . This table is actually partition too. Here the required information as suggested in the different paste bin 1. SHOW GLOBAL STATUS; - https://pastebin.com/5ijQ01Aj 2. SHOW GLOBAL VARIABLES; - https://pastebin.com/tWt7UjdV 3. SHOW FULL PROCESSLIST; - https://pastebin.com/xPEwVAHs 4. SHOW ENGINE INNODB STATUS; - https://pastebin.com/yj03VWMc But the weird part after I restart the mysql this particular table now is accesible. So what could be wrong here.

So i've gone and did something terribly wrong out of haste and not thinking properly. I have a PXC (percona cluster. I guess mysql cluster with Galera?) running with just 1 one being bootstrapped (already bad) to run as a production server. Now we had a request come in from a coworker who urgently needed some data recovered from a database from about a week ago, because he wanted to recover some settings from it. We don't take any backups using xtrabackup or mysqldumps or whatever. But we do take LVM snapshots. So i tried restoring the files from a specific database on another (test) mysql server i had running. But because it isn't a PXC the restore constantly failed. i decided to create a new database (lets name it DB-B) on the PXC, run a mysqldump of the database i wanted a restore of (lets name that DB-A), import it into the new database. delete the files of that database in it's directory and then i copied in the files from the database directory off the LVM backup. Of course the PXC cluster immediately crashed (because i had forgotten to turn it off) and the system could not start again. Now from what little MySQL knowledge i have i guess this is because innodb still keeps track of the files and seqno in the ibdata file. Now in the logs i can see that this is the issue preventing the PXC from starting up again. Even with all of the innodb_force_recovery settings 1-5 i get the following error: 2020-07-01T11:36:11.840315Z 13339321 [ERROR] InnoDB: Space id and page no stored in the page, read in are [page id: space=535955, page number=4], should be [page id: space=620409, page number=4] 2020-07-01T11:36:11.840385Z 13339321 [ERROR] InnoDB: Trying to access page number 1630627123 in space 620409, space name DB-B/liveuser_authlog, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server. Now the PXC does start if i set innodb_force_recovery=6 which i know is bad. But i know that it's the DB-B database which is causing problems and i was hoping i could just delete it. Restart the server and i could go on to regret all my mistakes another day. But when i try to start it after i've dropped the DB-B database i keep getting this error: 2020-07-01T13:18:40.771446Z 0 [Warning] InnoDB: Tablespace 620453 was not found at ./DB-B/mail_queue.ibd, and innodb_force_recovery was set. All redo log for this tablespace will be ignored! 2020-07-01T13:18:40.949631Z 0 [ERROR] [FATAL] InnoDB: Missing MLOG_FILE_NAME or MLOG_FILE_DELETE for redo log record 39 (page 559566:3) at 3425204536239 2020-07-01 08:18:40 0x7f5a56830880 InnoDB: Assertion failure in thread 140025975212160 in file ut0ut.cc line 943 is there any way i can resolve this by removing the DB-B records somewhere and restart this PXC and then try to restore this database elsewhere. The whole DB-B database is not a production database anyways so i do not mind deleting it. My config: # # The Percona XtraDB Cluster 5.7 configuration file. # # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # Please make any edits and changes to the appropriate sectional files # included below. # !includedir /etc/my.cnf.d/ !includedir /etc/percona-xtradb-cluster.conf.d/ # Node/SQL Settings [mysqld] server-id=1 port=3306 datadir=/mysql-data/data socket=/mysql-data/mysql.sock pid-file=/var/run/mysqld/mysqld.pid innodb_force_recovery=6 max_allowed_packet=1G sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" log_output=file slow_query_log=ON long_query_time=0 log_slow_rate_limit=100 log_slow_rate_type=query log_slow_verbosity=full log_slow_admin_statements=ON log_slow_slave_statements=ON slow_query_log_always_write_time=1 slow_query_log_use_global_control=all innodb_monitor_enable=all userstat=1 innodb_stats_on_metadata=0 max_connections = 2048 # InnoDB Settings innodb_buffer_pool_size = 24G innodb_log_file_size = 512M innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT optimizer_switch="derived_merge=off" # PXC Cluster Settings wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_name=ams-clus wsrep_cluster_address=gcomm://ip1,ip2,ip3 wsrep_node_name=name-1 wsrep_node_address=ip1 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=usr:passwd pxc_strict_mode=DISABLED binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_lock_mode=2

I have a mysql (Percona) 5.7 instance with over 1Million tables. When I start the database, it can take more than an hour to start. Errorlog doesn't show anything, but when I trace mysqld_safe, I found out that MySQL is getting a stat on every file in the DB. Any idea why this may happen? Also, please no suggestion to fix my schema, this is a blackbox. Also keep in mind that I've heard it all regarding how bad the design is.... Thanks

MySQL version : Percona Server for MySQL 5.7.40-43-log We have Master-Master(Active-Passive) Replication setup in our cluster. Only one Master(Active) is receiving the requests for writes while other one is Passive. Each Master then have 4 Replicas to it. There is bi-directional replica going on between Active and Passive Master. Now, I need to perform an ALTER to a huge table that has ~600M rows and the table size is around : 250G. All the replicas including the Masters(Since they are replicas for each other anyways) have the following flag for replication : **Replicate_Wild_Do_Table as data%.%** so I am not concerned about the _new, _old table creation while actual execution of the pt-osc because **all the tables(%)** are included in replication. Before running the pt-osc on the production, I would like to test it on a staging cluster so I have created a cluster like A --> B --> C where A is like my Active Master, B is my Passive Master and C is my Replica pointing to B. Please note that I have not set up Master-Master replication between A B. My main concern is with the replicas that are present in the production cluster as in while doing it on the Master, there shouldn't be any issues with the replication and the replicas should also have the updated change to the table. I have used the following flags in the pt-osc statement but I am getting error with recurse & recursion-method pt-online-schema-change --alter "ADD COLUMN Cost decimal(18,9) NOT NULL DEFAULT '0.000000000'" \ --alter-foreign-keys-method=auto \ --check-slave-lag=50 \ --max-lag=50 \ --recurse \ --recursion-method=processlist \ --host=replica-015 \ --max-load=Threads_running=30 \ --critical-load=Threads_running=50 \ --chunk-time=0.5 \ --slave-user=repl \ --slave-password='xxxxxxx' \ --chunk-size=10000 \ --progress percentage,1 \ --dry-run \ --user=test \ --password='xxxxxxx' \ D=avon,t=excel I tried processlist, hosts but that didn't worked and I got the following error : I updated the pt-osc command and used --recurse=1 and --recursion-method=processlist and the message is gone but I don't completely get it as to what recurse or recursion-method did here since in the out I don't see any slaves detected vs in some other output I saw online that says no slave detected. I not completely sure about the DSN table which is mentioned in the percona documentation : https://docs.percona.com/percona-toolkit/pt-online-schema-change.html Can someone help me here in understanding the replication related flag in pt-osc command and how should I proceed with the --dry-run or --execute in the staging cluster environment that I have created. Thank you in advance.

I have a RHEL linux server where I have installed mysql percona and set the auditing. The Queries received in the audit record has timestamp in below format. {"audit_record":{"name":"Query","record":"179295580_2024-10-03T10:56:38","timestamp":"2024-10-03T10:57:54 UTC","command_class":"drop_table","connection_id":"2","status":0,"sqltext":"drop table DPT","user":"abc[abc] @ localhost []","host":"localhost","os_user":"","ip":"","db":""}} In the above record , timestamp has UTC. ""timestamp":"2024-10-03T10:57:54 UTC"," And I want it in this format: "timestamp":"2024-10-03T10:57:54Z". Basically I want to replace UTC with Z. I tried changing the timezone of the server , but its no help. Please help me with this. Thankyou

I have Percona Mysql 8.0.19-10 on debian 10. A year ago I had some issues restoring a backup that required me to mess with tablespaces. I forget what all I had done but eventually I got the database up and working and forgot it. Fast forward to now, I just did a backup up my whole data-dir using percona's xtrabackup and transferred it to a new server. Copy the backup into my data-dir and attempt to start mysql. Fails with Tablespace 1, name 'sys/sys_config', file './sys/sys_config.ibd' is missing! mysqld: Can't create/write to file './performance_schema/cond_instances_2201.sdi' (OS errno 2 - No such file or directory) mysqld: Can't create file './performance_schema/cond_instances_2201.sdi' (errno: 2 - No such file or directory) I look at the the data-dir (/var/lib/mysql) and while I notice my main database folder, I don't see a sys or performance_schema folder. Curiously I do see a mysql folder but its full of files called slow_log and general_log. My first thought is the backup failed so I checked the old server and it has the exact same thing! However, I do have a mysql.ibd on the old server. So I run show databases and it shows all 3 databases (sys, performance_schema, mysql). I try to go to sys/sysconfig and it complains about the table space but I am able to go into mysql database tables and perofrmance_schema tables. Something is definitely up and I read people suggest doing an upgrade and mysql can self correct missing main tables (mysql_upgrade is deprecated so I must use mysqld and force it). I am scared to run update on my current server so I go back to the new server (the one I attempted to restore from backup) I rename /var/lib/mysql/mysql to /var/lib/mysql/backup and execute sudo -u mysql mysqld --upgrade=FORCE --skip-grant-tables --skip-networking and get in the error log /usr/sbin/mysqld (mysqld 8.0.21-12) starting as process 4000 2020-12-13T23:41:31.040574Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2020-12-13T23:41:31.279802Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2020-12-13T23:41:31.281181Z 1 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement 'CREATE SCHEMA mysql DEFAULT COLLATE utf8mb4_0900_ai_ci' failed with error code = 1049, error message = 'System schema directory does not exist.'. 2020-12-13T23:41:31.281565Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed. 2020-12-13T23:41:31.281986Z 0 [ERROR] [MY-010119] [Server] Aborting 2020-12-13T23:41:32.579767Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21-12) Percona Server (GPL), Release '12', Revision '7ddfdfe'. So I am not sure what to do, I would prefer to get my new server up and running instead of messing with my old server because it's currently live and being used. I have innodb table per file Should I expect to see a mysql folder inside my data-dir that looks to be slow_log and general_log backups? Will the sys,performance_schma,mysql databases have their own folder in the data-dir? I wish there was a way to tell mysql to disregard the sys,mysql,performance_schema tables and re-create them, I thought that's what the upgrade option would do but it fails **Edit 12/23/2020** I think its problem that only shows up with the newest mysql. The backup was done on Percona Mysql 8.0.19-10 and the failed restore was attempted on Percona Mysql 8.0.21-12. Normally I wouldn't think it was the difference in versions... However, I just happen to upgrade the percona mysql to 8.0.22-13 on the the one that was previously 8.0.19-10 and the exact same issue occured. I believe the newer mysql version is more picky and fails to load if sys_config.ibd is missing where as 8.0.19 had no issues **Edit 12/24/2020** I uninstalled percona 8.0.21-12 on the target server and installed 8.0.19-10. Restored the backup with no issues (despite missing sys folder, etc). While the problem of missing a sys folder is still an issue it seems to only prevent startup with 8.0.2x

I am trying to read the [performance_schema.variables_by_thread](https://dev.mysql.com/doc/refman/8.4/en/performance-schema-system-variable-tables.html) table with the following query, but it always returns an empty set:

mysql> select * from performance_schema.variables_by_thread limit 1;
Empty set (0.00 sec)

This is an active production server with hundreds of connections, so I think it is unlikely to actually be empty. **Is there some configuration flag that must be set to enable this functionality?** MySQL version information:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.35-27 |
+-----------+
1 row in set (0.00 sec)

Also, server greeting string:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 107143716
Server version: 8.0.35-27 Percona Server (GPL), Release 27, Revision 2f8eeab2

I've tried following several guides to add a new Percona cluster as a slave to another cluster. The new cluster will run MySQL 8.0 while the old cluster needs to continue running 5.7 for a few more weeks at least. I have added a MySQL 8.0 server as a replica to a 5.7 Percona/Galera MySQL cluster. (The new server is the first node of a new 8.0 cluster, started in bootstrap mod. The other nodes in the PCX cluster has been shut down and the /var/lib/mysql data directory wiped on all of them) The data was seeded using using a mysqldump, using the command: mysqldump -h 172.16.75.3 -u repl --all-databases --master-data --ssl-mode=disabled | mysql It took three days to import the db. (Previous attempts to get the slave seeded using xtrabackup initially looked promising but the DB refuse to start.) On the selected master node I can see the slave connected using SHOW SLAVE HOSTS.

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|        11 |      | 3306 |         1 | ede7d279-0867-11ef-a45d-566fd2de0048 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

On the new replica the slave status looks like this:

[root@galera-dbnode-03 etc]# mysql -e 'show slave status \G'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.16.75.3
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: core-cloud-mysql-server-02-bin.000341
          Read_Master_Log_Pos: 453928562
               Relay_Log_File: galera-dbnode-03-relay-bin.000002
                Relay_Log_Pos: 162610326
        Relay_Master_Log_File: core-cloud-mysql-server-02-bin.000325
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1047889015
              Relay_Log_Space: 16772376805
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 62789
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 6c7d7361-e68b-11ee-bf51-566fd2de005e
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Waiting for dependent transaction to commit
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace:

I've tried stopping and starting the slave. Could this be a TLS related issue? What else did I miss? Edit: After restarting MySQL server on the replica (again in bootstrap mode, since it is the only valid node right now) it showed an error. I was able to skip the transaction, and then it got back to the same error as shown above. Restarting the service again does not help.

[root@galera-dbnode-03 ~]# mysql -e "show slave status \G"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.16.75.3
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: core-cloud-mysql-server-02-bin.000341
          Read_Master_Log_Pos: 722663436
               Relay_Log_File: galera-dbnode-03-relay-bin.000004
                Relay_Log_Pos: 712735716
        Relay_Master_Log_File: core-cloud-mysql-server-02-bin.000326
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 712735458
              Relay_Log_Space: 16852646253
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 65252
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 6c7d7361-e68b-11ee-bf51-566fd2de005e
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Waiting for dependent transaction to commit
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace:

Edit: The /etc/my.cnf file and the output of pt-mysql-summary here: https://pastebin.com/kB597zEW