Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

436 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
0
votes
1answer
41 views

Help on improving the query performance of MySQL table

My team handles a tool that automatically detects and categorizes images.We have a Mysql DB (InnoDB engine) used by our tool in production where we store information about each image processed. The ...
0
votes
0answers
16 views

MySQL count limit on COUNT + GROUP BY results for performance benefit

I have a very large MySQL8 database, currently using the InnoDB engine, that I have to perform some COUNT() queries on. Specifically, something like this: SELECT parameter, COUNT(*) FROM table WHERE .....
0
votes
0answers
23 views

MySQL - Counting multiple parameters from the same subquery

I have a very large MySQL (~10M rows) database, currently using the InnoDB engine, which stores log events. I am working on a front-end that allows searching through this log database, and one of the ...
0
votes
0answers
18 views

Why does insert on duplicate-key error create a share lock?

In the mysql docs is the following example for locking on insert: INSERT sets an exclusive lock on the inserted row. [...] If a duplicate-key error occurs, a shared lock on the duplicate index ...
0
votes
0answers
11 views

How to avoid duplicate in mysql INNODB transaction for non index key?

I have a INNODB table with txnno column without unique index but I have primary key on another column. Now I am inserting transaction from multiple places. And there are some concurrent insertion ...
0
votes
0answers
42 views

MySQL: message “Incorrect key file for table” “try to repair it”

I'm using MySQL 5.7.10 I have a table like this, with 100M rows and a size of 16GB . CREATE TABLE `my_table` ( `id` DOUBLE NOT NULL AUTO_INCREMENT, `entity_id` DOUBLE NOT NULL, `concept_id`...
0
votes
1answer
610 views

MySql InnoDB crash recovery - File ./ibdata1: 'open' returned OS error 71. Cannot continue operation

Mysql 8.0.23 on CentOS 7.9 with WHM I ended up with a corrupted InnoDB table. I gave up on a long query and killed it. Then the query was still blocking so I did a forceful server reboot. I know now ...
0
votes
2answers
29 views

in Mariadb - Can you cache the indexes and data for Aria Storage engine tables?

I have a table that is a read-only table (real-time view), I used Aria based on the recommendation for read-heavy workload. Based on this What are the main differences between InnoDB and MyISAM? When ...
0
votes
1answer
10 views

delete var/lib/mysql folders after dropping database via mysql

I am aware that you don't want to remove the innodb tables from /var/lib/mysql for a linux distro of mysql (I have Ver 14.14). So I used the command drop database mydb1; drop database mydb2; -- etc ...
0
votes
0answers
87 views

InnoDB: page_cleaner: settings might not be optimal (how to tune)

I am having similar issues to this question but I'm trying to understand how to tune the database correctly (rather than just lowering settings and hoping ;) In the error log I have these entries (...
0
votes
0answers
94 views

How to restore a MySQL InnoDB with ONLY .ibd files?

I had a Bitnami Joomla! stack running on a computer that suddenly kicked the bucket. I had neglected to make a back-up before this happened so I do not have any dumps or backed up files, but was able ...
0
votes
0answers
22 views

Innodb buffer pool internal

I'm learning the Innodb buffer pool of mysql 5.7. And I have some question about that. What is the relation of buffer pool,buffer block,buffer page and buffer chunk? How to locate a row in the ...
0
votes
0answers
20 views

InnoDB engine lock tables higher than tables used

I am running the SQL statement CREATE OR REPLACE TEMPORARY TABLE my_schema.tmpx_test AS SELECT col1 , SUBSTR(col2, 1, 5) AS col3 FROM my_schema.my_table WHERE col4 = 'my_string' AND POSITION('...
0
votes
0answers
32 views

MySQL - how to optimize select within a range bound by values in 2 different columns - innodb

How do I optimize select within a range bound by values in 2 different columns with MySQL innodb? I'm matching words to the pages on which they were printed. Each instance of a word points to only 1 ...
0
votes
1answer
207 views

Cannot rejoin or add instance to MySQL InnoDB cluster

I have a MySQL InnoDB cluster running where I had added three nodes, one is having the Primary role and the others are Secondary. Today morning, the two secondary nodes were removed from the cluster ...
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
1k 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
62 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
27 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?
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
147 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
79 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 ...
0
votes
0answers
13 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
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
43 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
85 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` ...
0
votes
0answers
469 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
56 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
68 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
404 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 ...
0
votes
0answers
120 views

Restart MySQL server while query is in Killed State

I need to reboot the server (not just MySQL instance) to perform some urgent server updates. Currently on the MySQL server when I run: select * from information_schema.processlist where info is not ...
0
votes
1answer
73 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 (...
0
votes
0answers
22 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 ...
0
votes
0answers
28 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
2answers
14 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 ...
0
votes
0answers
23 views

MySQL InnoDB Weird Query Performance

I designed two tables, both using InnoDB. The first one has columns "Offset, Size, and ColumnToBeIndexed" with BIGINT "Offset" being the primary key, and the second one has columns "OffsetAndSize, ...
0
votes
0answers
55 views

Alter table executes despite Lost connection to Mysql server

mysql Ver 8.0.19 for Linux on x86_64 (MySQL Community Server - GPL) InnoDB engine I executed 'ALTER table ... drop column' on a mysql table. Despite the client losing connection to the server, I ...
0
votes
1answer
60 views

AWS RDS MySQL some time get slow performance when update table1 set field='any word' where primarykey=1234

Normally when update table1 set field='any word' where primarykey=1234 take 1sec. or less but sometimes random take much more time possible 500sec. or more. I was set very well and never see high CPU,...
0
votes
1answer
26 views

Transaction isolation level/design for write-only ledger

This is a simplified example of a problem I've been working on. Say I have the following database schema: Table: Deposits +--------+------------+--------+---------+ | ID | Date | Amount | ...
0
votes
1answer
37 views

Index dive/sampling row count very inaccurate in MySQL

The table emp_address_phone is filled with many random values. CREATE TABLE `emp_address_phone` ( `address_no` int(11) NOT NULL, `emp_no` int(11) DEFAULT NULL, `street1` varchar(255), `...
0
votes
2answers
71 views

Row size limits in Mysql w.r.t to TEXT and BLOB columns

There is a well-known limitation in Mysql for row size which 65,535 bytes. While going through the docs, I have found that BLOB and TEXT columns only contribute 9 to 12 bytes toward the row ...
0
votes
1answer
45 views

Changing storage engine to InnoDb from MyIsam is dropping records from table

Currently our Database all tables are using MyIsam as a storage engine. We tried changing storage engine of our mysql tables from MyISAM to InnoDB from PHPMyAdmin operations tab. However during the ...
0
votes
1answer
109 views

MySQL How to Script to create indexes on all date columns in database

I have a lot of tables in the database that are using a datetime stamp - I need to create a script that will create indexes on all columns in each table that does not already have an index . So far I ...
0
votes
0answers
627 views

Why can mysqlfrm not read from my .frm file? See debug output

I'm working on a project at home and learning databases as I go. Because of my inexperience I've just learned the hard way that cut/pasting .ibd files is an extremely bad idea. The tables have ...
0
votes
1answer
19 views

SELECT inside trigger gives wrong result (it ignores the WHERE clause)

When I run this code inside an after UPDATE trigger: SELECT * FROM `stock_totals` WHERE `product_id` = 2 AND `stock_place_id` = 3 INTO OUTFILE 'query3.csv' FIELDS TERMINATED BY ',' ...
0
votes
0answers
46 views

LOAD DATA INFILE Performance Degrading

I have very little database experience, but I've inherited a database for a production system that has major problems that I am attempting to fix. The system receives a continuous stream of data from ...
0
votes
0answers
307 views

How to manage partitions in MariaDB with InnoDB tables?

I'd like to set up partitioning for the "time series" use case as in https://mariadb.com/kb/en/partition-maintenance/. However, I'd also like to control data directories. And I want the "max" or "...
0
votes
1answer
70 views

Recovering deleted records from ibdata1

Is it possible to recover deleted rows from ibdata1? I have only ibdata1 file and no other files or tables. I want to recover all table structures and datas with deleted records. Is it possible ?
0
votes
1answer
350 views

MySQL: InnoDB data dictionary has unknown flags

I tried to delete and recreate a MySQL service and when I tried to start the service again I can't start it and I'm getting this error in error logs: InnoDB: Warning: table 'sampledb/table1' InnoDB: ...

1
3 4
5
6 7
9