Once it was necessary to make a record in the table very quickly and I quickly wrote a SQL query, but after executing it I got an error:Continue reading “Solution for error 21S01 Column count doesn’t match value count at row 1”
Category Archives: MySQL
Solution: Specified key was too long; max key length is 767 bytes
Once on Ubuntu Server 18.04 I upgraded Zabbix 4.4 to 5.0 and found an error in the logs during the upgrade:Continue reading “Solution: Specified key was too long; max key length is 767 bytes”
How to change innodb_flush_method in MySQL
innodb_flush_method defines the method for flushing data from RAM to disk.Continue reading “How to change innodb_flush_method in MySQL”
Migrating MySQL Directory
Once I created a raid, mounted it as a disk system partition and transferred the MySQL directory directly to it, but in this case there were drawbacks, the lost+found directory could appear, it could not be used for other data, etc., after that I began to transfer the MySQL directory not directly to the partition, but created a directory on it and transferred it to it.Continue reading “Migrating MySQL Directory”
How to change innodb_io_capacity in MySQL
innodb_io_capacity – determines the number of write IOPS for background InnoDB tasks, such as flushing pages from a buffer pool, etc. If the cleanup operation is lagging, then InnoDB can perform cleanup more aggressively at a higher IOPS, which is defined in the innodb_io_capacity_max variable.Continue reading “How to change innodb_io_capacity in MySQL”
Partitioning tables in MySQL
Partitioning tables in MySQL is splitting a large table into parts according to the specified criteria, which will speed up reading and writing data to it if this table contains a very large amount of data.Continue reading “Partitioning tables in MySQL”
How to change innodb_stats_on_metadata in MySQL
innodb_stats_on_metadata – Lets you collect statistics on metadata operations such as SHOW TABLE STATUS when accessing INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS tables.
In newer versions of MySQL, the innodb_stats_on_metadata parameter is disabled by default, which makes working with these tables faster.
How to change default-storage-engine in MySQL
When creating a table, the ENGINE option is used, which specifies the storage engine, if this option is not specified in the SQL query, then the default storage engine is used, which is specified in the MySQL server configuration file or when it starts mysqld –default-storage-engine=InnoDB.Continue reading “How to change default-storage-engine in MySQL”
Changing thread_cache_size in MySQL
thread_cache_size – responsible for caching client threads after they are disconnected so that they can be reused, for example, when hundreds of connections are made to the server per second, this parameter can greatly improve performance.Continue reading “Changing thread_cache_size in MySQL”
Changing innodb_flush_log_at_trx_commit in MySQL
Default value is 1, possible values are 0-2.
0 – Logs are written and flushed to disk once per second. Transactions that have not been flushed out can be lost as a result of a crash.
1 – Logs are written and flushed to disk every time a transaction is committed.
2 – Logs are written after each commit of a transaction and flushed to disk once per second. Transactions that have not been flushed out can be lost in the event of a failure.