Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

Filter by
Sorted by
Tagged with
1
vote
1answer
71 views

Safe values for InnoDB variables when using FULLTEXT searches

I'm using Maria DB, version 10.2.22, where one database column uses FULLTEXT for a broad document searching. However, I've ran into a "Table handler out of memory" on some searches. The ...
0
votes
0answers
11 views

Is it theoretically possible to INSERT and DELETE a row using the InnoDB storage engine before any changes are written to disk?

I'm debating whether to use the InnoDB or MEMORY storage engine for a high-frequency INSERT/DELETE access token table, and I want to minimize the amount of disk writes. If it's possible for InnoDB to ...
0
votes
0answers
15 views

mysqldump causing database to get not saved records

I wish there was a safe way to backup mysql database. The database itself is getting not saved records if the records was about to be saved duding the dump. It is happening occasionally, maybe when ...
0
votes
1answer
2k views

MariaDB crashed: Unknown/unsupported storage engine: InnoDB

I've a Debian GNU/Linux 9 (4GB, 2 CPUs) on Digitalocean. Tonight (I've done nothing) my DB (mariaDB) crashed with this errors. I ran a wordpress with InnoDB and myISAM tables: 2020-10-17 0:51:18 ...
0
votes
0answers
72 views

Cannot drop empty database in mariadb

I am having trouble dropping a database on my server innodb-file-per-table is set to true We previously had a huge table (1TB) which we used a drop table command. This result i believe was ...
0
votes
2answers
33 views

innodb_force_recovery

I did an innodb force recovery. I went from 1-> and on 5 MySQL booted up. I was able to do mydqldump. Customer is now saying some of data is wrong. (References to another table) Is this possible?
2
votes
2answers
104 views

What is the best configuration for a MySQL instance with a lot of databases and lot of tables?

I have a MySQL database instance with more than 3000 databases in it. Each database contains more than 200 tables. Total data of all theses database comes around 100 GB. I am using Windows Server ...
1
vote
1answer
18 views

MySql IDB files not named after tables

In my Linux filesystem, there are many ibd files (InnoDB file per table is ON) which are not named after tables. What are these files and what data do they contain? The question has arisen due to ...
0
votes
2answers
739 views

Foreign key constraint is incorrectly formed (MySQL) [closed]

So, I have these two tables CREATE TABLE IF NOT EXISTS `inart_ps_categories` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `inart_ps_category_id` int(11) UNSIGNED NOT NULL, `parent_id` int(11)...
0
votes
0answers
40 views

Does commit after a normal select can call any fsync or flush operations?

I'm using Mariadb 10.2.16. 3 Node cluster. Coms counts taken at particular time for all 3 (insert,update,commit). Com_inserts are in 1.1K Com_updates are in 1.6k Com_commit are in 10K When ...
0
votes
0answers
234 views

MySQL replication error 1452

could you help me to figure out the solution for a MySQL replication error? mysql replication error: (Last_Errno: 1452) Last_SQL_Error: Error
'Cannot add or update a child row: a foreign key ...
0
votes
1answer
124 views

MySQL / MariaDB - Start multi-source replication with mariabackup (xtrabackup)

Many times I successfully performed procedure described in the https://mariadb.com/kb/en/setting-up-a-replication-slave-with-mariabackup/ - it's fastest and easiest because replica is restored by ...
-1
votes
1answer
36 views

Nullable integer as part of composite primary key

I want to store phone numbers as integers and I've created this SQL: DROP TABLE `user_id_phone`; CREATE TABLE `user_id_phone` ( `user_id` int unsigned NOT NULL, `country_code` ...
0
votes
0answers
28 views

What files of Mysql lib directory safe to be deleted and to be recreated with innodb_file_per_table settings?

I have brand new mysql 8 setup and installed on a centos 7. Unfortunately I forget to set innodb_file_per_table. I would like to dump the database and recreate with the innodb_file_per_table settings. ...
0
votes
1answer
34 views

is it possible to reset innodb_row_lock without restart MySQL 5.7

Now I could using this command to check the innodb_row_lock values: show status like 'innodb_row_lock%'; Innodb_row_lock_current_waits 4 Innodb_row_lock_time 22471927 Innodb_row_lock_time_avg ...
1
vote
1answer
160 views

Why does our MySQL server become non-responsive with the processlist showing tons of processes waiting in STATE=init, INFO=commit?

I work for a company that's been around for a while and has a large MySQL (5.6.48) monolith running in RDS. Recently, the database has starting going unresponsive for 10-30 minutes at a time during ...
1
vote
2answers
81 views

MySQL performance degraded after database migration?

I migrated my MySQL database from GCP to Azure (both 5.7), but it seems to have affected performance. Server before migration: 2 VCPUS with 7.5GB memory Server after migration: 2 VCPUS with 8GB memory ...
0
votes
1answer
79 views

How to do safety mirroring innodb table on mysql?

I have one Mysql InnoDb table with rush trafik on row update. The table is also frequently used for heavy select query (especially group by for summary report). As I know this heavy select query will ...
0
votes
1answer
32 views

order by slowing down query with multiple joins and limit/offset on larger result sets

I am having trouble with the following query taking quite a long time to process when results are large. The limit and offset can change as this is used with pagination. The range on ...
0
votes
0answers
58 views

Taking InnoDB MySQL/MariaDB cold file backup and restoring on new Omeka DB

I have an unresponsive Omeka MySQL/MariaDB database on a Windows server. It is unresponsive in the sense that it will not start from the XAMPP panel: 10:44:15 AM [mysql] Attempting to start ...
0
votes
1answer
108 views

MySQL table queries extremely slow

I am using MySQL 8.0.17 and I have a table with just 7 million rows defined like CREATE TABLE `measurement` ( `id` int(11) NOT NULL AUTO_INCREMENT, `field1` int(11) DEFAULT NULL, `field2` ...
3
votes
1answer
396 views

I can´t change innodb_buffer_pool_instances

I recently updated my mariadb database to 10.5.5 and I can't set innodb_buffer_pool_instances innodb_log_file_size = 3G innodb_buffer_pool_size = 12G innodb_buffer_pool_instances = 12 But when ...
0
votes
0answers
614 views

Innodb error log sequence number is in future

2020-08-28 16:46:02 d64 InnoDB: Error: page 1 log sequence number 23113680 InnoDB: is in the future! Current system log sequence number 3181581. InnoDB: Your database may be corrupt or you may have ...
0
votes
1answer
75 views

MySQL stops in windows VM and shows innodb os error in logs(error no. 5)

2020-08-23 17:22:36 5688 [Note] Plugin 'FEDERATED' is disabled. 2020-08-23 17:22:36 5688 [Note] InnoDB: Using atomics to ref count buffer pool pages 2020-08-23 17:22:36 5688 [Note] InnoDB: The InnoDB ...
0
votes
1answer
99 views

Com_commit and Com_rollback count are almost same. What doest it mean?

SHOW GLOBAL STATUS has Com_* variables: The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. When I compare Com_commit to Com_rollback, both are ...
0
votes
1answer
802 views

Mysql InnoDB Cluster

I'm trying to setup mysql innodb cluster. I have couple of Digital Ocean (DO) instances running centos 7. I have installed Mysql Ver 8.0.21 for Linux on x86_64 (MySQL Community Server - GPL) on the DO ...
1
vote
2answers
149 views

How do I enable full text search on MySql 8?

I'm using MySql 8. I would like to speed up searches on my table of the form select * FROM dirctory_coop where name like '%mystr%'; and someone mentioned using Full text searches -- https://dev....
1
vote
3answers
188 views

Is it normal for MySQL to gradually increase memory usage over time?

Is it normal for MySQL to increase memory usage over time? See image below of my server's memory usage over the last two weeks. After "service mysql restart" it drops to 40%. The database is ...
-1
votes
1answer
150 views

MySQL Query Fetch Time increases when LEFT JOIN with big table [closed]

I am fetching the same amount of data with 2 different queries. However, one of them have a fetching time of ~x130 compared to the other. Being the only difference between the two queries a LEFT JOIN ...
0
votes
2answers
29 views

Transfer rows into a new table, forcing to generate unique primary keys till its insertable

Lets say we have the following scenario... +-----------------+-------------+---------------+ | Table | PK | Additional | +-----------------+-------------+---------------+ | ...
0
votes
1answer
194 views

MySQL write performance after truncate & optimize [closed]

Once a year we truncate a table of several GBs and millions of rows and then run optimize in order to shrink the size of the corresponding ibd file on the file system. However, as this is write ...
1
vote
1answer
97 views

Issues with MySQL 8.0.17

Recently we upgraded MySQL version on our UAT database server to 8.0.17 from 5.7.18. We are facing issues with our web application, Website is loading slowly if there are more users accessing it I ...
2
votes
3answers
240 views

15 seconds to insert a single row in MYSQL 5.7 (happens occasionally)

I'm seeing very sporadic slow queries in MYSQL, at least, that's where Datadog is suggesting the issues are. I have, for example, 5 queries out of 726K traces (both selects & updates) in the last ...
1
vote
0answers
154 views

Non-existent table that actually exists?

I've been tasked to solve a very difficult and interesting problem. Given is a MySQL database created on the AWS-Aurora service. The MySQL version reported when connecting is 5.6.10. We have an ...
0
votes
2answers
196 views

MySQL taking up 400% CPU

LEMP stack on DigitalOcean (48GB ram, 960GB SSD, 12 vCPU) with WordPress on it. Added a new theme and mysql went through the roof. 400% CPU usage taken up by MySQL itself! Looked at slow-log to figure ...
1
vote
0answers
549 views

Trying to restore InnoDB tables

I've reinstalled MySQL but when I copy the data dir from the old instance: maicol07@maicol07$ sudo mysqld --user=root 2020-07-14T13:07:34.031447Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (...
1
vote
2answers
36 views

InnoDB storage for multiple BIT(M) columns?

Let's say if I have a table with four BIT(1) columns. Will InnoDB use 4 bytes to store these columns, or will it be smart and use 1 byte? (Referring to MySQL 8, if it matters.)
0
votes
1answer
24 views

More tables or More records performance wise

i am into a scenario that upon looking at the our project database contains Identical tables with the schema described below: id url region_1 region_2 region_3 region_4 date_created My ...
0
votes
1answer
84 views

Need help with some recovery

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 (...
1
vote
2answers
119 views

After changing engine from myisam to innodb tables are larger in size, is this an expected behaviour?

I updated a WordPress database from myisam to innodb on a server using apache and mysql, and I noticed that some tables, such as wp-posts grew in size after running the ALTER TABLE wp_posts ENGINE=...
-1
votes
1answer
60 views

MySQL Code implementation of locking and transactions

I am trying to understand the internals of how MySql (using InnoDB) implements locking and transactions in code. I understand the different types and concepts of locks and transactions, but I really ...
0
votes
1answer
17 views

Is there a way to set different destination order with mysql-router?

I have innodb cluster setup with 3 mysql instances. Each instance is on different server and each server has mysql-router also. I would like each mysql-router on each server to try to connect to the ...
0
votes
0answers
23 views

Adding a disabled foreign key to InnoDB table

The concrete problem is having a vast database which is lacking foreign keys, which makes it difficult for developers to work on. The plan was to add disabled foreign keys to it initially, which would ...
1
vote
1answer
135 views

To apply changes done to “/etc/my.cnf”, do we need to restart mysqld service?

I need to change max_connections parameter in /etc/my.cnf. After doing the change, do we need to restart mysqld service to apply the changes? I'm on CentOS 7
1
vote
1answer
915 views

MySQL my.cnf performance tuning recommendations

Most of the day, the performance of the server is good, but once per day it stops responding totally even I can't access via SSH, and I have to restart it to make it up again. I have a dedicated ...
1
vote
2answers
24 views

MySQL index size doesn't include all values

I have a table with ~1.4M rows and an id field as primary key and not all primary keys are included in index: mysql> SELECT DISTINCT(COUNT(id)) FROM my_table; +-------------+ | (COUNT(id)) | +------...
0
votes
1answer
37 views

InnoDB performance implications of one 3 column index VS three 2 column indexes

I'm using MariaDB with InnoDB but this would apply with MySql as well. I have a list of records that belong to categories and would like to order them by 3 different criteria. I have come up with two ...
0
votes
0answers
31 views

Recover data from MySQL Incremental Backup files (.FRM and .IBD files)

I have MySQL Enterprise full and incremental backups (.FRM and .IBD files). I have copied the full backup files to a different server, created table structures and imported the data the data using ...
0
votes
3answers
2k views

MySQL UUID() behavior

4 years ago, I answered a question on SO, "how to set an UUID for each row in bulk". I answered, after trying it, UPDATE table SET uuidcol = UUID(); It worked for me, but about half (it ...
0
votes
2answers
15 views

Possibility on huge MySQL database replication

I have a mysql database with size 2.4TB which has lots of reads and writes happening continuously. I want to replicate this database as a backup. But it's almost impossible to get a dump from this ...

1 2
3
4 5
42