Here is my SQL (with explain): EXPLAIN SELECT * FROM GBI gbi INNER JOIN VF_KRED kred ON gbi.vendor = kred.vendor; Giving below the outcome: Now, the select SQL takes ~10 seconds to produce the outcome. Below are the indexes on tables: - VF_KRED table: - GBI table: Any clues?

I am trying to set a stage server which replicate some tables like articles, user etc from master. If I start the slave server as read_only I am not able to my stage site up because there are some tables like cache or session which needs to be rewritten. So I am trying to use replicate_do_table and then locking those tables manually using below snippets and let other tables like cache and session rewritable. In short I want read_only lock for all the tables in a database excluding some specific tables to read-write access. I found below snippet on mysql website. But how can I select only specific tables without writing all the table names manually inside LOCK TABLES LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... lock_type: { READ [LOCAL] | [LOW_PRIORITY] WRITE } Note: I am using innodb storage engine and mariadb 10.3

Behind the scene: Basically I operate an app that heavily relies on time calculation. We store unixtime in the database according to the user's timezone. Now to perform specific operations we need to convert that time from the native timezone to UTC. We recently transferred the application from another server It was working perfect but when we moved the application to a new server. The calculation that was written in Server Side Langauge was accurate but those calculations in which we wrote the logic in stored procedure output wrong date while converting it from unix_time to timestamp. Now you might question how do you know that output is wrong? Because I ran the same query over 2 different servers and the one that I am talking about returned wrong output. The result from server having bug.

select convert_tz(from_unixtime(1585804204),'-04:00','+00:00')
----Output---
convert_tz(from_unixtime(1585804204),'-04:00','+00:00')
2020-04-02 11:10:04

The accurate result:

select convert_tz(from_unixtime(1585804204),'-04:00','+00:00')
----Output---
convert_tz(from_unixtime(1585804204),'-04:00','+00:00')
2020-04-02 05:10:04

If you put the same value of unixtime in the epoch converter (https://www.epochconverter.com/) you will get the value above mentioned select timezone America/New York. I have also checked that clock of the host machine is fine. Please tell me how to resolve this error. I am using MariaDB version 10.3

I have been playing around with this new column type. The use case being that I want to hide this column from a client issuing a "Select *" query. But the column seems to behave like any normal column and is not hidden. I have enabled the Invisible column type by using the following command: ALTER TABLE MyTable ADD COLUMN TestA INT INVISIBLE; Reading up in the MariaDB documentation , the examples provided are only with Create statements. Which makes me wonder if one can only enable this feature when creating a table and NOT with an Alter statement. Can anybody shed some light?

Anyone can help me to check this MySQL configuration? I have a VPS 2GB RAM - 2 vcpu, installed cyberpanel from litespeed. Any help would be appreciated! my.cnf settings are: [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] tmpdir = /dev/shm binlog_cache_size = 64K thread_stack = 256K max_heap_table_size = 64M join_buffer_size = 256K default_storage_engine = InnoDB performance_schema_max_table_instances = 400 performance_schema = ON innodb_io_capacity = 6000 innodb_io_capacity_max = 10000 table_definition_cache = 400 skip-external-locking key_buffer_size = 128M max_allowed_packet = 100G table_open_cache = 32000 tmp_table_size = 100M max_heap_table_size = 100M sort_buffer_size = 768K net_buffer_length = 4K read_buffer_size = 768K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M thread_cache_size = 64 query_cache_size = 0 query_cache_type = OFF sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #skip-name-resolve max_connections = 100 max_connect_errors = 100 open_files_limit = 65535 log-bin=mysql-bin binlog_format=mixed server-id = 1 expire_logs_days = 10 slow_query_log = 1 long_query_time = 3 #log_queries_not_using_indexes=on innodb_data_file_path = ibdata1:10M:autoextend innodb_buffer_pool_size = 1024M innodb_buffer_pool_instances = 1 innodb_log_file_size = 128M innodb_file_format = Barracuda innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 innodb_max_dirty_pages_pct = 90 innodb_read_io_threads = 2 innodb_write_io_threads = 2 [mysqldump] quick max_allowed_packet = 500M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 32M sort_buffer_size = 768K read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout **Additional information** >> MySQLTuner 1.7.13 - 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] Logged in using credentials passed on the command line [OK] Currently running supported MySQL version 10.3.25-MariaDB-0ubuntu0.20.04.1-log [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [--] Log file: /var/log/mysql/error.log(8K) [OK] Log file /var/log/mysql/error.log exists [OK] Log file /var/log/mysql/error.log is readable. [OK] Log file /var/log/mysql/error.log is not empty [OK] Log file /var/log/mysql/error.log is smaller than 32 Mb [!!] /var/log/mysql/error.log contains 15 warning(s). [!!] /var/log/mysql/error.log contains 3 error(s). [--] 3 start(s) detected in /var/log/mysql/error.log [--] 1) 2021-03-05 2:08:56 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 2) 2021-03-05 0:21:10 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 3) 2021-03-05 0:15:52 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 2 shutdown(s) detected in /var/log/mysql/error.log [--] 1) 2021-03-05 2:08:42 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 2) 2021-03-05 0:21:09 0 [Note] /usr/sbin/mysqld: Shutdown complete -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in InnoDB tables: 8.7M (Tables: 291) [OK] Total fragmented tables: 0 -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA. -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [--] There are 618 basic passwords in the list. -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 3h 31m 52s (99K q [7.844 qps], 3K conn, TX: 190M, RX: 22M) [--] Reads / Writes: 97% / 3% [--] Binary logging is enabled (GTID MODE: OFF) [--] Physical Memory : 1.9G [--] Max MySQL memory : 1.8G [--] Other process memory: 380.8M [--] Total buffers: 1.5G global + 2.5M per thread (100 max threads) [--] P_S Max memory usage: 89M [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 1.6G (83.55% of installed RAM) [!!] Maximum possible memory usage: 1.8G (94.64% of installed RAM) [!!] Overall possible memory usage with other process exceeded memory [OK] Slow queries: 0% (0/99K) [OK] Highest usage of available connections: 14% (14/100) [OK] Aborted connections: 0.12% (4/3349) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 33K sorts) [!!] Joins performed without indexes: 6409 [!!] Temporary tables created on disk: 54% (10K on disk / 18K total) [OK] Thread cache hit rate: 99% (14 created / 3K connections) [OK] Table cache hit rate: 98% (434 open / 440 opened) [OK] Open file limit used: 0% (61/130K) [OK] Table locks acquired immediately: 100% (1K immediate / 1K locks) [OK] Binlog cache memory access: 100.00% (2272 Memory / 2272 Total) -------- Performance schema ------------------------------------------------------------------------ [--] Memory used by P_S: 89.5M [--] Sys schema isn't installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 2 thread(s). [--] Using default value is good enough for your version (10.3.25-MariaDB-0ubuntu0.20.04.1-log) -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.2% (24M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/124.0K [!!] Read Key buffer hit rate: 90.0% (20 cached / 2 reads) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 1.0G/8.7M [OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 128.0M * 2/1.0G should be equal 25% [OK] InnoDB buffer pool instances: 1 [--] Number of InnoDB Buffer Pool Chunk : 8 for 1 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: 99.94% (2898858 hits/ 2900641 total) [!!] InnoDB Write Log efficiency: 56.12% (747 hits/ 1331 total) [OK] InnoDB log waits: 0.00% (0 waits / 2078 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/1B [!!] Aria pagecache hit rate: 85.3% (67K cached / 9K reads) -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] Binlog format: MIXED [--] XA support enabled: ON [--] Semi synchronous replication Master: OFF [--] Semi synchronous replication Slave: OFF [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: Control warning line(s) into /var/log/mysql/error.log file Control error line(s) into /var/log/mysql/error.log file MySQL was started within the last 24 hours - recommendations may be inaccurate Reduce your overall MySQL memory footprint for system stability Dedicate this server to your database for highest performance. Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Consider installing Sys schema from https://github.com/mysql/mysql-sys Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** join_buffer_size (> 256.0K, or always use indexes with JOINs) tmp_table_size (> 200M) max_heap_table_size (> 200M)

Please I need help on how I can transform the data that appears in the attached photo. Maybe Unpivot SQL?

I already discussed how much slower performance I have experienced on MariaDB 10.3 compared to MySQL 5.5 for some queries in my previously posted thread , but I am now struggling with a specific kind of queries that give me an extremely slow result I can't figure out, even if indexes are added to all involved columns. The query I am experimenting with right now is the following: select distinct a.asset_id, a.* from HLAsset a, HLAssetCategory c where a.asset_id=c.asset_id and a.filetype='SCORCH' and a.date_deleted='' and a.excluded=0; It requests distinct IDs from two pretty big tables. The first table HLAsset includes around 303,000 rows with 31 columns each. The second table HLAssetCategory includes around 627,000 rows with just 2 columns each. On the old server with MySQL 5.5.50 the query takes around 8 seconds to complete. On the new MariaDB 10.3 it takes well over 22 seconds (!!!), and I can't figure out why. If anyone of you could help me to understand why such a huge difference in performance, please, let me know. Keep in mind that the new MariaDB is on a much newer and faster server, which makes the whole issue even weirder. Here is some info for you: Explain of the query on the old server with MySQL 5.5.50:

mysql> explain select distinct a.asset_id, a.* from HLAsset a, HLAssetCategory c where a.asset_id=c.asset_id and a.filetype='SCORCH' and a.date_deleted='' and a.excluded=0;
+----+-------------+-------+------+----------------------------------------+---------+---------+------------------------------+--------+------------------------------+
| id | select_type | table | type | possible_keys                          | key     | key_len | ref                          | rows   | Extra                        |
+----+-------------+-------+------+----------------------------------------+---------+---------+------------------------------+--------+------------------------------+
|  1 | SIMPLE      | a     | ALL  | PRIMARY,date_deleted,excluded,filetype | NULL    | NULL    | NULL                         | 303211 | Using where; Using temporary |
|  1 | SIMPLE      | c     | ref  | PRIMARY                                | PRIMARY | 34      | mydb.a.asset_id |      2 | Using index; Distinct        |
+----+-------------+-------+------+----------------------------------------+---------+---------+------------------------------+--------+------------------------------+
2 rows in set (0.00 sec)

Explain of the query on the new server with MariaDB 10.3:

MariaDB [virtualsheetmusic]> explain select distinct a.asset_id, a.* from HLAsset a, HLAssetCategory c where a.asset_id=c.asset_id and a.filetype='SCORCH' and a.date_deleted='' and a.excluded=0;
+------+-------------+-------+------+----------------------------------------+---------+---------+------------------------------+--------+------------------------------+
| id   | select_type | table | type | possible_keys                          | key     | key_len | ref                          | rows   | Extra                        |
+------+-------------+-------+------+----------------------------------------+---------+---------+------------------------------+--------+------------------------------+
|    1 | SIMPLE      | a     | ALL  | PRIMARY,date_deleted,excluded,filetype | NULL    | NULL    | NULL                         | 303366 | Using where; Using temporary |
|    1 | SIMPLE      | c     | ref  | PRIMARY                                | PRIMARY | 34      | mydb.a.asset_id |      2 | Using index; Distinct        |
+------+-------------+-------+------+----------------------------------------+---------+---------+------------------------------+--------+------------------------------+
2 rows in set (0.003 sec)

And here is a SHOW CREATE TABLE of the first table HLAsset on the old server with MySQL 5.5.50:

mysql> show create table HLAsset;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HLAsset | CREATE TABLE HLAsset (
  asset_id varchar(32) NOT NULL,
  asset_type varchar(64) DEFAULT NULL,
  format varchar(64) DEFAULT NULL,
  asset_title varchar(255) DEFAULT NULL,
  description text,
  date_added varchar(32) DEFAULT NULL,
  date_deleted varchar(32) DEFAULT NULL,
  page_count int(11) DEFAULT NULL,
  retail_price decimal(9,2) DEFAULT NULL,
  filename varchar(255) DEFAULT NULL,
  filetype varchar(64) DEFAULT NULL,
  filesource varchar(32) DEFAULT NULL,
  mime_type varchar(64) DEFAULT NULL,
  fileurl varchar(256) DEFAULT NULL,
  world_rights enum('true','false') DEFAULT NULL,
  song_number int(11) DEFAULT NULL,
  public_domain enum('true','false') DEFAULT NULL,
  package_type varchar(32) DEFAULT NULL,
  asset_action varchar(32) DEFAULT NULL,
  asset_voicing varchar(256) DEFAULT NULL,
  performance_time varchar(256) DEFAULT NULL,
  external_ref varchar(30) DEFAULT NULL,
  difficulty_level_low decimal(9,2) DEFAULT NULL,
  difficulty_level_high decimal(9,2) DEFAULT NULL,
  tempo int(11) NOT NULL DEFAULT '0',
  minimum_quantity int(11) NOT NULL DEFAULT '0',
  image_url varchar(256) DEFAULT NULL,
  renderingPDF tinyint(4) NOT NULL,
  renderingXML tinyint(4) NOT NULL,
  extra_score tinyint(4) NOT NULL,
  excluded tinyint(4) NOT NULL,
  PRIMARY KEY (asset_id),
  KEY asset_type (asset_type),
  KEY date_deleted (date_deleted),
  KEY world_rights (world_rights),
  KEY format (format),
  KEY page_count (page_count),
  KEY renderingXML (renderingXML),
  KEY date_added (date_added),
  KEY tempo (tempo),
  KEY asset_title (asset_title),
  KEY excluded (excluded),
  KEY extra_score (extra_score),
  KEY filetype (filetype)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

And here is a SHOW CREATE TABLE of the second table HLAssetCategory on the old server with MySQL 5.5.50:

mysql> show create table HLAssetCategory;
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                                           |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HLAssetCategory | CREATE TABLE HLAssetCategory (
  asset_id varchar(32) NOT NULL,
  category_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (asset_id,category_id),
  KEY category_id (category_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

And here is a SHOW CREATE TABLE of the first table HLAsset on the new server with MariaDB 10.3:

MariaDB [virtualsheetmusic]> show create table HLAsset;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HLAsset | CREATE TABLE HLAsset (
  asset_id varchar(32) NOT NULL,
  asset_type varchar(64) DEFAULT NULL,
  format varchar(64) DEFAULT NULL,
  asset_title varchar(255) DEFAULT NULL,
  description text DEFAULT NULL,
  date_added varchar(32) DEFAULT NULL,
  date_deleted varchar(32) DEFAULT NULL,
  page_count int(11) DEFAULT NULL,
  retail_price decimal(9,2) DEFAULT NULL,
  filename varchar(255) DEFAULT NULL,
  filetype varchar(64) DEFAULT NULL,
  filesource varchar(32) DEFAULT NULL,
  mime_type varchar(64) DEFAULT NULL,
  fileurl varchar(256) DEFAULT NULL,
  world_rights enum('true','false') DEFAULT NULL,
  song_number int(11) DEFAULT NULL,
  public_domain enum('true','false') DEFAULT NULL,
  package_type varchar(32) DEFAULT NULL,
  asset_action varchar(32) DEFAULT NULL,
  asset_voicing varchar(256) DEFAULT NULL,
  performance_time varchar(256) DEFAULT NULL,
  external_ref varchar(30) DEFAULT NULL,
  difficulty_level_low decimal(9,2) DEFAULT NULL,
  difficulty_level_high decimal(9,2) DEFAULT NULL,
  tempo int(11) NOT NULL DEFAULT 0,
  minimum_quantity int(11) NOT NULL DEFAULT 0,
  image_url varchar(256) DEFAULT NULL,
  renderingPDF tinyint(4) NOT NULL,
  renderingXML tinyint(4) NOT NULL,
  extra_score tinyint(4) NOT NULL,
  excluded tinyint(4) NOT NULL,
  PRIMARY KEY (asset_id),
  KEY asset_type (asset_type),
  KEY date_deleted (date_deleted),
  KEY world_rights (world_rights),
  KEY format (format),
  KEY page_count (page_count),
  KEY renderingXML (renderingXML),
  KEY date_added (date_added),
  KEY tempo (tempo),
  KEY asset_title (asset_title),
  KEY excluded (excluded),
  KEY extra_score (extra_score),
  KEY filetype (filetype)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

And here is a SHOW CREATE TABLE of the second table HLAssetCategory on the new server with MariaDB 10.3:

MariaDB [virtualsheetmusic]> show create table HLAssetCategory;
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                                         |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HLAssetCategory | CREATE TABLE HLAssetCategory (
  asset_id varchar(32) NOT NULL,
  category_id int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (asset_id,category_id),
  KEY category_id (category_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

And finally, here is the my.cnf on the old server with MySQL 5.5.50 (I have removed the comments):

[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]

user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking

bind-address		= 0.0.0.0

key_buffer		= 16M

thread_stack		= 192K
thread_cache_size       = 8


myisam-recover         = BACKUP

query_cache_limit	= 1M
query_cache_size        = 16M


expire_logs_days	= 10
max_binlog_size         = 100M

symbolic-links=0
max_allowed_packet=1000M
net_buffer_length=100M
read_buffer_size=16M
max_connections=300
query-cache-type=1
query-cache-size=20M


myisam_recover_options=OFF
thread_stack=262144




innodb_buffer_pool_size=1GB
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit = 2




[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]


[isamchk]
key_buffer		= 16M

And here is the same file on the new server with MariaDB 10.3:

[server]

character-set-server  = latin1
collation-server      = latin1_swedish_c

[mysqld]
open_files_limit = 65535

user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
socket                  = /run/mysqld/mysqld.sock
port                   = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
skip-external-locking


sql_mode = 'NO_ENGINE_SUBSTITUTION'

bind-address = 0.0.0.0

key_buffer_size        = 20M

thread_stack           = 192K
thread_cache_size      = 200


myisam_recover_options = BACKUP

query_cache_limit      = 1M


tmp_table_size = 64M
max_heap_table_size = 64M


log_error = /var/log/mysql/error.log


slow_query_log
slow_query_log_file    = /var/log/mysql/mariadb-slow.log
long_query_time        = 10
log_slow_rate_limit    = 1000

log_queries_not_using_indexes=ON

expire_logs_days        = 10
max_binlog_size        = 100M

character-set-server  = latin1
collation-server      = latin1_swedish_ci

symbolic-links=0

max_allowed_packet=160M
net_buffer_length=100M
read_buffer_size=16M
max_connections=300
query_cache_type=1
query_cache_size=20M


myisam_recover_options=OFF
thread_stack=262144

innodb_buffer_pool_size=10GB

innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M

innodb_thread_sleep_delay = 0
innodb_file_per_table = 1
skip-name-resolve


innodb_strict_mode = 0


innodb_lru_scan_depth=100
innodb_change_buffer_max_size = 50
read_buffer_size = 256K
innodb_io_capacity = 3900

innodb_flush_neighbors = 0

[embedded]

[mariadb]


[mariadb-10.3]

Eager to know your thoughts about all this. Thank you in advance to everyone again! All the best, Fab.

How would I know in MySQL if the user has exceeded his max_queries per hour? I know that I can see this error on the client side, but I just want to know that before client is reporting to me, somewhere on the server side, would be nice to have it in error_log at least.

I have an application where each page load inserts information about the visit into a MariaDB table. There is an analytics hub where statistics from this data can be viewed. In the beginning, this used to work well, but now the table is large enough (currently 650,000 rows), where performing SELECT queries on it with lots of filters tends to take a while, sometimes as long as 20 seconds. This in and of itself isn't an issue. The problem is that since all pages insert records into this table, whenever analytics are being retrieved, the whole site is basically down until the query finishes. At that point, everything else continues again. This is admittedly not a great architecture for this purpose, but I would like to modify some things so that the analytics queries are not locking the table. By this, I mean simple modifications, not setting up a read replica and point the analytics queries at that. Is it possible to change the queries so that the SELECT doesn't lock the table in any way that would prevent the INSERTs from succeeding. The INSERTs are all auto-increment, and I don't care if a little accuracy is sacrificed on the SELECT. I tried moving this particular table into its own table, but that hasn't really helped in, since it is, in fact, this specific table which is the issue, not access to other tables in either database. I have tried some of the approaches such as in this answer , but they don't work because they are focused on SELECT. I don't care if the SELECT's are slow, but the INSERTS all queue up while a SELECT is being run and this is causing serious issues whenever this occurs. Speeding up the query might help a little bit, but fundamentally I want to prevent the INSERTs from waiting on the SELECTs to complete. Is this possible to do, without changing the database setup (e.g. using separate tables for SELECT/INSERT)? I am using MariaDB 10.3 with the InnoDB engine. This is the structure of the table: views | CREATE TABLE views ( id int(10) unsigned NOT NULL AUTO_INCREMENT, datetime datetime NOT NULL DEFAULT current_timestamp(), userid int(11) unsigned NOT NULL, sessionid varchar(64) NOT NULL, ip varchar(64) NOT NULL, agent varchar(256) NOT NULL, domain varchar(32) NOT NULL, pagebase varchar(256) NOT NULL, pagefull varchar(256) NOT NULL, hash varchar(128) NOT NULL, ref varchar(256) NOT NULL, PRIMARY KEY (id), KEY userid (userid), KEY pagebase (pagebase), KEY pagebase_2 (pagebase,domain), KEY hash (hash) ) ENGINE=InnoDB AUTO_INCREMENT=930146 DEFAULT CHARSET=utf8mb4 Here is one of the faster queries (really), which takes 14 seconds to run: SELECT domain, COUNT(DISTINCT(hash)) as d, COUNT(*) AS c FROM views GROUP BY domain ORDER BY d DESC; Some of the much queries use lots of wildcard searches, which, even on indexed columns, perform quite badly. Again, I have no issue with these queries taking a while, but whenever they are running, they block everything else, which is a serious issue.

We have migrated our database from a local hosting company to AWS. Now when we run a particular query on AWS it finishes in about a minute (processlist says "sending data") where on the old server the query ran for only 0.5 seconds. Server specs are about the same. EXPLAIN on both servers says that the queries use different indexes. Old server where the query runs fast, MariaDB 5.5.56: EXPLAIN SELECT COUNT(*) AS layer_impressions, DATE_FORMAT(i.created, "%H") AS the_date FROM layer_impression i INNER JOIN layer l ON l.id = i.layer_id WHERE i.created BETWEEN '2018-11-15' AND '2018-11-15 23:59:59' GROUP BY the_date; +------+-------------+-------+--------+------------------+---------+---------+--------------------+--------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+------------------+---------+---------+--------------------+--------+--------------------------------------------------------+ | 1 | SIMPLE | i | range | layer_id,created | created | 8 | NULL | 249116 | Using index condition; Using temporary; Using filesort | | 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 4 | getback.i.layer_id | 1 | Using index | +------+-------------+-------+--------+------------------+---------+---------+--------------------+--------+--------------------------------------------------------+ New server (AWS where the query runs slow), MariaDB 10.3.8: EXPLAIN SELECT COUNT(*) AS layer_impressions, DATE_FORMAT(i.created, "%H") AS the_date FROM layer_impression i INNER JOIN layer l ON l.id = i.layer_id WHERE i.created BETWEEN '2018-11-15' AND '2018-11-15 23:59:59' GROUP BY the_date; +------+-------------+-------+-------+------------------+------------+---------+--------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+------------------+------------+---------+--------------+------+----------------------------------------------+ | 1 | SIMPLE | l | index | PRIMARY | voucher_id | 4 | NULL | 652 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | i | ref | layer_id,created | layer_id | 4 | getback.l.id | 545 | Using where | +------+-------------+-------+-------+------------------+------------+---------+--------------+------+----------------------------------------------+ What surprises me is that "possible_keys" lists PRIMARY but uses "voucher_id", a column which has nothing to do with our query. If we add USE INDEX or make a STRAIGHT_JOIN the query runs fast and the correct indexes are being used: EXPLAIN SELECT COUNT(*) AS layer_impressions, DATE_FORMAT(i.created, "%H") AS the_date FROM layer_impression i STRAIGHT_JOIN layer l ON l.id = i.layer_id WHERE i.created BETWEEN '2018-11-15' AND '2018-11-15 23:59:59' GROUP BY the_date; +------+-------------+-------+--------+------------------+---------+---------+--------------------+--------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+------------------+---------+---------+--------------------+--------+--------------------------------------------------------+ | 1 | SIMPLE | i | range | layer_id,created | created | 5 | NULL | 272230 | Using index condition; Using temporary; Using filesort | | 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 4 | getback.i.layer_id | 1 | Using index | +------+-------------+-------+--------+------------------+---------+---------+--------------------+--------+--------------------------------------------------------+ What behaviour is that and how can we prevent this without modifying all our queries throughout our application? It's more a general question about how this can happen and less about this particular query. We tried so far: Switching it off and on again, OPTIMIZE and ANALYZE, FLUSH, setting extended_keys=off (in the optimizer_switch variable). Schema (omitted some additional columns): CREATE TABLE IF NOT EXISTS layer ( id int(10) unsigned NOT NULL AUTO_INCREMENT, shop_id int(10) unsigned NOT NULL, voucher_id int(10) unsigned NOT NULL, templ_id int(10) unsigned NOT NULL COMMENT 'Mailtemplate ID', doi_templ_id int(10) unsigned NOT NULL, key char(5) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL, status enum('active','inactive','deleted') NOT NULL DEFAULT 'active', created datetime NOT NULL, createdby int(10) unsigned NOT NULL, modified timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (id), KEY shop_id (shop_id,status) USING BTREE, KEY key (key), KEY voucher_id (voucher_id), KEY templ_id (templ_id), KEY doi_templ_id (doi_templ_id) ) ENGINE=InnoDB AUTO_INCREMENT=656 DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS layer_impression ( id int(10) unsigned NOT NULL AUTO_INCREMENT, layer_id int(10) unsigned NOT NULL, session_id bigint(20) unsigned NOT NULL, device enum('desktop','mobile','tablet') NOT NULL DEFAULT 'desktop', created datetime NOT NULL, PRIMARY KEY (id), KEY layer_id (layer_id), KEY session_id (session_id), KEY created (created,device) USING BTREE, CONSTRAINT layer_impression_ibfk_1 FOREIGN KEY (layer_id) REFERENCES layer (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=34184266 DEFAULT CHARSET=utf8; layer has 652 rows, layer_impression has ~34M rows The cardinality on the layer_id index is also quite different: show index from layer_impression; +------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | layer_impression | 0 | PRIMARY | 1 | id | A | 34062000 | NULL | NULL | | BTREE | | | | layer_impression | 1 | layer_id | 1 | layer_id | A | 62499 | NULL | NULL | | BTREE | | | | layer_impression | 1 | session_id | 1 | session_id | A | 34062000 | NULL | NULL | | BTREE | | | | layer_impression | 1 | created | 1 | created | A | 34062000 | NULL | NULL | | BTREE | | | | layer_impression | 1 | created | 2 | device | A | 34062000 | NULL | NULL | | BTREE | | | +------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ On AWS (above) it's 62499 while on our old server it's 18: show index from layer_impression; +------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | layer_impression | 0 | PRIMARY | 1 | id | A | 34295989 | NULL | NULL | | BTREE | | | | layer_impression | 1 | layer_id | 1 | layer_id | A | 18 | NULL | NULL | | BTREE | | | | layer_impression | 1 | session_id | 1 | session_id | A | 34295989 | NULL | NULL | | BTREE | | | | layer_impression | 1 | created | 1 | created | A | 34295989 | NULL | NULL | | BTREE | | | | layer_impression | 1 | created | 2 | device | A | 34295989 | NULL | NULL | | BTREE | | | +------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ Thanks for any advice! EDIT: Most of the time we have additional WHERE clauses, eg. WHERE l.shop_id = 1 # or WHERE l.shop_id IN (1,2,3) I left this part out for simplicity because I was more interested in why one server would handle it completely different than the other. Here's a typical query which uses the layer table. On AWS it runs for ~18sec, on the old server for ~2sec. # AWS EXPLAIN SELECT COUNT(*) AS layer_impressions, DATE_FORMAT(i.created, "%H") AS the_date FROM layer_impression i LEFT JOIN layer l ON l.id = i.layer_id WHERE i.created BETWEEN '2018-11-10' AND '2018-11-15 23:59:59' AND l.shop_id IN (42,36,103,63) GROUP BY the_date; +------+-------------+-------+-------+------------------+----------+---------+--------------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+------------------+----------+---------+--------------+------+-----------------------------------------------------------+ | 1 | SIMPLE | l | range | PRIMARY,shop_id | shop_id | 4 | NULL | 11 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | i | ref | layer_id,created | layer_id | 4 | getback.l.id | 545 | Using where | +------+-------------+-------+-------+------------------+----------+---------+--------------+------+-----------------------------------------------------------+ # Old server EXPLAIN SELECT COUNT(*) AS layer_impressions, DATE_FORMAT(i.created, "%H") AS the_date FROM layer_impression i LEFT JOIN layer l ON l.id = i.layer_id WHERE i.created BETWEEN '2018-11-10' AND '2018-11-15 23:59:59' AND l.shop_id IN (42,36,103,63) GROUP BY the_date; +------+-------------+-------+--------+------------------+---------+---------+--------------------+---------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+------------------+---------+---------+--------------------+---------+--------------------------------------------------------+ | 1 | SIMPLE | i | range | layer_id,created | created | 8 | NULL | 1615766 | Using index condition; Using temporary; Using filesort | | 1 | SIMPLE | l | eq_ref | PRIMARY,shop_id | PRIMARY | 4 | getback.i.layer_id | 1 | Using where | +------+-------------+-------+--------+------------------+---------+---------+--------------------+---------+--------------------------------------------------------+

I am facing more CPU usage for my app server. Server configuration is as below. 1> CPU: Intel(R) Xeon(R) CPU E5-2650 v4 @ 2.20GHz 8 core 2> RAM: 32 GB 3> 600 GB SSD NVRAM 4> Mariadb version 10.3.17. 5> OS: Debian 9 I have run mysql tuning script to optimize server performance and add some parameter in my.cnf. But yet it's not creating big impact. I can see that RAM used very less compare to CPU. How can I balance in both and improve server performance? Here is my tuner script output tunner . Here is current my.cnf output on my.cnf

New installation of Debian 10, with MariaDB 10.3.29. Trying to set up encryption-at-rest, but MariaDB won't start due to an error...

[ERROR] /usr/sbin/mysqld: unknown variable 'file-key-management-filename=/etc/openssl/keys/keyfile.key'

My server.cnf has...

[mariadb-10.3]
plugin-load-add = file_key_management
file_key_management_filename = /etc/openssl/keys/keyfile.key
file_key_management_encryption_algorithm = AES_CTR

The above config I have working in a Centos7 system, so I'm baffled as to why it isn't working on Debian10. This message is what I encountered when trying to install encryption in a MariaDB pre-10.1 version that didn't have encryption as an option, but I know encryption is an option after Mariadb 10.1, so it should be available in this package (unexciting installation via apt), right? I have tried moving the cnf code around, to the [mysqld] and [mariadb] sections to no avail. There are no previous versions of MariaDB hanging around - this is a clean install. Has anyone encountered this issue and solved it already?

I am testing a node.js client which connects to a mysql DB (mariadb). It works fine on its own, but if I launch this client simultaneously on another host, the first instance gets disconnected with the following error. I have read some posts suggesting to change the server-id in my.cnf, but that didn't help. Besides, why would I need to change the id of the server since both clients are pointing to the same server? { Error: UNKNOWN_CODE_PLEASE_REPORT: A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'mysql-b in.000024' at 334, the last event read from 'mysql-bin.000024' at 256, the last byte read from 'mysql-bin.000024' at 334. at Binlog.Sequence._packetToError (/home/myapp/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14) at Binlog.Sequence.ErrorPacket (/home/myapp/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:17) at Protocol._parsePacket (/home/myapp/node_modules/mysql/lib/protocol/Protocol.js:278:23) at Parser.write (/home/myapp/node_modules/mysql/lib/protocol/Parser.js:76:12) at Protocol.write (/home/myapp/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/home/myappp/node_modules/mysql/lib/Connection.js:91:28) at Socket. (/home/myapp/node_modules/mysql/lib/Connection.js:502:10) at Socket.emit (events.js:182:13) at addChunk (_stream_readable.js:283:12) at readableAddChunk (_stream_readable.js:264:11) -------------------- at Protocol._enqueue (/home/myapp/node_modules/mysql/lib/protocol/Protocol.js:144:48) at Immediate._start (/home/myapp/node_modules/@rodrigogs/zongji/index.js:234:31) at processImmediate (timers.js:632:19) code: 'UNKNOWN_CODE_PLEASE_REPORT', errno: 4052, sqlMessage: "A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'mysql-bin.000024' at 334, the last event read from 'mysql-bin.000024' at 256, the last byte read from 'mysql-bin.000024' at 334.", sqlState: 'HY000' } { Error: Connection lost: The server closed the connection. at Protocol.end (/home/myapp/node_modules/mysql/lib/protocol/Protocol.js:112:13) at Socket. (/home/myapp/node_modules/mysql/lib/Connection.js:97:28) at Socket. (/home/myapp/node_modules/mysql/lib/Connection.js:502:10) at Socket.emit (events.js:187:15) at endReadableNT (_stream_readable.js:1094:12) at process.internalTickCallback (internal/process/next_tick.js:72:19) fatal: true, code: 'PROTOCOL_CONNECTION_LOST' }

I saw a new feature Invisible Columns in MariaDB 10.3.x. What are practical use cases for DBA and web developer? When to use this feature? > Columns can be given an INVISIBLE attribute in a CREATE TABLE or `ALTER > TABLE` statement. These columns will then not be listed in the results > of a SELECT * statement, nor do they need to be assigned a value in an > INSERT statement, unless INSERT explicitly mentions them by name. > > Since SELECT * does not return the invisible columns, new tables or > views created in this manner will have no trace of the invisible > columns. If specifically referenced in the SELECT statement, the > columns will be brought into the view/new table, but the INVISIBLE > attribute will not. > > Invisible columns can be declared as NOT NULL, but then require a > DEFAULT value

I've upgraded the mariadb to 10.3 packages on my Debian 10, however something went wrong and I ended in the same issue which is mentioned several times - [Cannot open table mysql/innodb_index_stats [duplicate]][1] - InnoDB: Error: Table “mysql”.“innodb_table_stats” not found after upgrade to mysql 5.6 - InnoDB: Error: Table “mysql”.“innodb_table_stats” not found - MySQL > Table doesn't exist. But it does (or it should) - mysql error: Table “mysql”.“innodb_table_stats” not found So in my case you can see mysql_upgrade complains of missing tables although they seem to exist

$ sudo mysql_upgrade -u root --force -pxxxxx
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry
Error    : Table 'mysql.transaction_registry' doesn't exist in engine
status   : Operation failed
mysql.user                                         OK

Repairing tables
mysql.transaction_registry
Error    : Table 'mysql.transaction_registry' doesn't exist in engine
status   : Operation failed
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1813 (HY000) at line 77: Tablespace for table 'mysql.innodb_table_stats' exists. Please DISCARD the tablespace before IMPORT
ERROR 1813 (HY000) at line 81: Tablespace for table 'mysql.innodb_index_stats' exists. Please DISCARD the tablespace before IMPORT
ERROR 1813 (HY000) at line 153: Tablespace for table 'mysql.gtid_slave_pos' exists. Please DISCARD the tablespace before IMPORT
ERROR 1146 (42S02) at line 639: Table 'mysql.innodb_index_stats' doesn't exist
ERROR 1243 (HY000) at line 640: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1146 (42S02) at line 642: Table 'mysql.innodb_table_stats' doesn't exist
ERROR 1243 (HY000) at line 643: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1146 (42S02) at line 647: Table 'mysql.innodb_index_stats' doesn't exist
ERROR 1146 (42S02) at line 651: Table 'mysql.innodb_table_stats' doesn't exist
ERROR 1146 (42S02) at line 654: Table 'mysql.innodb_table_stats' doesn't exist
FATAL ERROR: Upgrade failed

However the tables are there

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| index_stats               |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+
31 rows in set (0.005 sec)

As non-db expert and althought some of the posts mentioned above are old, I followed them trying to solve the problem 1. Stop mariadb with sudo systemctl stop mariadb 2. Delete files

cd /var/lib
    sudo rm mysql/ibdata1
    sudo rm mysql/ib_logfile0
    sudo rm mysql/ib_logfile1
    sudo rm mysql/mysql/innodb_index_stats.ibd
    sudo rm mysql/mysql/innodb_table_stats.ibd
    sudo rm mysql/mysql/gtid_slave_pos.frm
    sudo rm mysql/mysql/gtid_slave_pos.ibd
    sudo rm mysql/mysql/slave_master_info.frm
    sudo rm mysql/mysql/slave_master_info.ibd 
    sudo rm mysql/mysql/slave_worker_info.frm
    sudo rm mysql/mysql/slave_worker_info.ibd
    sudo rm mysql/mysql/slave_relay_log_info.frm
    sudo rm mysql/mysql/slave_relay_log_info.ibd

3. Start mariadb with sudo systemctl start mariadb 4. Open mysql using mysql -u root -p 5. Recreate tables for mariadb 10

CREATE TABLE gtid_slave_pos (
    domain_id int(10) unsigned NOT NULL,
    sub_id bigint(20) unsigned NOT NULL,
    server_id int(10) unsigned NOT NULL,
    seq_no bigint(20) unsigned NOT NULL,
    PRIMARY KEY (domain_id,sub_id)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';

    CREATE TABLE innodb_index_stats (
    database_name varchar(64) COLLATE utf8_bin NOT NULL,
    table_name varchar(64) COLLATE utf8_bin NOT NULL,
    index_name varchar(64) COLLATE utf8_bin NOT NULL,
    last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    stat_name varchar(64) COLLATE utf8_bin NOT NULL,
    stat_value bigint(20) unsigned NOT NULL,
    sample_size bigint(20) unsigned DEFAULT NULL,
    stat_description varchar(1024) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (database_name,table_name,index_name,stat_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

    CREATE TABLE innodb_table_stats (
    database_name varchar(64) COLLATE utf8_bin NOT NULL,
    table_name varchar(64) COLLATE utf8_bin NOT NULL,
    last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    n_rows bigint(20) unsigned NOT NULL,
    clustered_index_size bigint(20) unsigned NOT NULL,
    sum_of_other_index_sizes bigint(20) unsigned NOT NULL,
    PRIMARY KEY (database_name,table_name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

    CREATE TABLE slave_master_info (
    Number_of_lines int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
    Master_log_name text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
    Master_log_pos bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
    Host char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
    User_name text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
    User_password text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
    Port int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
    Connect_retry int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
    Enabled_ssl tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
    Ssl_ca text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
    Ssl_capath text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
    Ssl_cert text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
    Ssl_cipher text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
    Ssl_key text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
    Ssl_verify_server_cert tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
    Heartbeat float NOT NULL,
    Bind text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
    Ignored_server_ids text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
    Uuid text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
    Retry_count bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
    Ssl_crl text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
    Ssl_crlpath text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
    Enabled_auto_position tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
    PRIMARY KEY (Host,Port)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';

    CREATE TABLE slave_relay_log_info (
    Number_of_lines int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
    Relay_log_name text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
    Relay_log_pos bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
    Master_log_name text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
    Master_log_pos bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
    Sql_delay int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
    Number_of_workers int(10) unsigned NOT NULL,
    Id int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
    PRIMARY KEY (Id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';

    CREATE TABLE slave_worker_info (
    Id int(10) unsigned NOT NULL,
    Relay_log_name text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    Relay_log_pos bigint(20) unsigned NOT NULL,
    Master_log_name text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    Master_log_pos bigint(20) unsigned NOT NULL,
    Checkpoint_relay_log_name text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    Checkpoint_relay_log_pos bigint(20) unsigned NOT NULL,
    Checkpoint_master_log_name text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    Checkpoint_master_log_pos bigint(20) unsigned NOT NULL,
    Checkpoint_seqno int(10) unsigned NOT NULL,
    Checkpoint_group_size int(10) unsigned NOT NULL,
    Checkpoint_group_bitmap blob NOT NULL,
    PRIMARY KEY (Id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

However situation got worse:

$ sudo mysql_upgrade -u root --force -pxxxx
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry
Error    : Table 'mysql.transaction_registry' doesn't exist in engine
status   : Operation failed
mysql.user                                         OK

Repairing tables
mysql.transaction_registry
Error    : Table 'mysql.transaction_registry' doesn't exist in engine
status   : Operation failed
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
information_schema
nc
nc.oc_accounts
Error    : Table 'nc.oc_accounts' doesn't exist in engine
status   : Operation failed
nc.oc_activity
Error    : Table 'nc.oc_activity' doesn't exist in engine
status   : Operation failed
nc.oc_activity_mq
...
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

What can I do? Not only are there errors with mysql-tables but also all others.

I am running MariaDB 10.3.27 and after an erroneous long query MariaDB stopped working. I restarted it and checked systemctl status. MariaDB was working but also have the below errors:

-none
[ERROR] mariadbd: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
mariadbd: 2021-09-30 19:44:58 1 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
mariadbd: 2021-09-30 19:44:58 0 [Note] Reading of all Master_info entries succeeded
mariadbd: 2021-09-30 19:44:58 0 [Note] Added new Master_info '' to hash table
mariadbd: 2021-09-30 19:44:58 0 [Note] /usr/sbin/mariadbd: ready for connections.
mariadbd: Version: '10.5.10-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
systemd: Started MariaDB 10.5.10 database server.
mariadbd: 2021-09-30 19:44:58 3 [ERROR] InnoDB: Table mysql.innodb_table_stats not found.
mariadbd: 2021-09-30 19:47:20 0 [Note] InnoDB: Buffer pool(s) load completed at 210930 19:47:20
mariadbd: 2021-10-01  6:29:22 14690 [ERROR] Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 50565, now running 100510. Please use mariadb-upgrade to fix this error

Well, I think this is very bad. I can see that I am suggested to run mariadb-upgrade, but I am afraid that this would break things. I also don't understand why I have suddenly to upgrade. It worked fine before the long running query.

**I need to periodically copy the outcome of a view on our local database to another remote database.** I've tried using mysqldump which works fine when copying data from tables to tables, but i need to copy the data from either a view or a stored procedure. As an example I've created a sourcedb and a destinationdb. I need to copy data from the view in sourcedb and append the data in a table at the destinationdb. Is that possible? **Mysqldump:**
mysqldump --skip-add-drop-table -uroot -p*** sourcedb sourceview | sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' | mysql -P3306 -uroot -p*** destinationdb **Error:**
ERROR 1146 (42S02) at line 41: Table 'destinationdb.table1' doesn't exist

I am getting below error while importing the dump, > ERROR 3105 (HY000) at line 82: The value specified for generated > column 'column_name' in table 'table_name' is not allowed. When I describe the table with error, there are some generated columns in it. > option_type | varchar(45) | YES | MUL | NULL | VIRTUAL > GENERATED What is the correct command to use when taking mysql dump for the database contains generated column? MySQL server source and destination version: > mysqld Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL)) MariaDB mysqldump CLI version: > mysqldump Ver 10.17 Distrib 10.3.12-MariaDB, for Linux (x86_64)

I have a MariaDB 10.3.13 server installed on Red Hat linux. I can only login to mysql, as root@localhost, through root linux user. Login to mysql server through non root linux user. It fails [dave@dbserver ~]$ mysql -uroot -p Enter password: ERROR 1698 (28000): Access denied for user 'root'@'localhost' Login to mysql server through root linux user. It works. [dave@dbserver ~]$ sudo su [sudo] password for dave: [root@dbserver dave]# mysql -uroot -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. ..... root database user information: MariaDB [(none)]> SELECT USER,HOST,PLUGIN FROM mysql.user WHERE USER = 'root'; +------+-----------+-----------------------+ | USER | HOST | PLUGIN | +------+-----------+-----------------------+ | root | localhost | mysql_native_password | +------+-----------+-----------------------+ Added 1: SHOW CREATE USER root@localhost; -- It returns: -- CREATE USER 'root'@'localhost' IDENTIFIED VIA unix_socket USING 'RGFF345' Why can't I login to mysql, as root@localhost, with dave linux user?

I'm currently using MariaDB 10.3 and am troubleshooting this case where threads will start to build up until the MySQL service eventually has to be restarted. I was able to capture the process list before restarting MySQL, but I have a few questions that I hope will help me head in the right direction. In the screenshot below, I've included a portion of the process list, which I sorted by time. The same thing has happened two different days, and on both days, the output is very similar. From looking at the processes, there are two things I've observed: 1. There is a longer running process in the statistics state. 2. All of the processes are waiting for the table flush after our database backup process, which seems to be waiting for a table flush itself. I need to confirm with the team what tool is being used for backups, but I believe it might be something from Percona. The main issue I'm trying to solve here is figuring out what's causing all of these processes to wait for a table flush, but I'd also like to understand the output here. Does this output produce something that looks obvious in terms of what's causing it or is there a particular way you would go about figuring this out? Thanks in advance!