Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

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

What are the possible downsides or consequences of using ALGORITHM=INPLACE, LOCK=NONE when running alters on a table?

I've been trying to find the answer to this online for a while with no luck. What are possible downsides or consequences to running an alter (such as add index, add column, drop index, drop column) ...
user avatar
-2 votes
0 answers
10 views

InnoDB: Assertion failure in thread 34399657984 in file ut0ut.cc line 921

I've tried to setup and configure WordPress on FreeBSD 13. This is the tutorial that I've followed,but I haven't been able to complete it because a mysql error that I'm not able to fix : https://...
user avatar
1 vote
1 answer
50 views

Database becoming unresponsive after time (4 to 24 hours)

Since a couple of days my mariadb database randomly becomes unresponsive after random intervals (which have been between 4 and 20 hours). The database comes back to life when rebooting the ct. And ...
user avatar
0 votes
1 answer
43 views

mysqld Shutdown every 1 hours

Please help me solve a problem which initiates a mysql shutdown every hour. Naturally the site on the server can't connect to the database and I get a bunch of errors in the site's logs. config: ...
user avatar
0 votes
1 answer
49 views

Mysql service down with [ERROR] InnoDB: Page log sequence number is in the future

I have a Centos 7.9 server with MariaDB version 10.2.43. All of a sudden on May 2 2022, I have started receiving mysql down alerts. On checking I could see below log information in logs. Error 2:- ...
user avatar
0 votes
1 answer
24 views

Mysql 8.0 "ERROR 1030 (HY000): Got error 100" when adding index to very large table

I'm attempting to add an index to a very large table (2B rows) and the command fails after a few hours with: ERROR 1030 (HY000): Got error 100 - 'InnoDB error' from storage engine I'm finding the ...
user avatar
  • 3
0 votes
0 answers
39 views

How to understand and decrease Innodb_rows_read.avg (RDS INNODB)?

I am looking to understand what's behind Innodb_rows_read value and how to decrease it. For some unknown reasons my RDS server has been over 90% of CPU for more than 5 days and after investigation, ...
user avatar
  • 109
0 votes
0 answers
42 views

Why mysql 8.0.17 is clean after crash

I was working with my website and after I started a very huge process in the server (CentOS 8.5.2111), the server can not allocate more tasks because memory full, and MySQL (8.0.17) started with ...
user avatar
0 votes
3 answers
63 views

The UPDATE queries is 50x slower than its SELECT query

The UPDATE queries is 50x slower than its SELECT query. I have a table named sync_read and the have the following columns id club_id created_at user_id queue_id The table have the following Indexes: ...
user avatar
0 votes
2 answers
22 views

InnoDB Clustered Index is stored under memory?

firstly i'm newbie about internals databases, sorry if my question is dumb. I have read in Stackoverflow(post link at final) that InnoDB clustered index is stored in disk and in memory. And i have ...
user avatar
1 vote
2 answers
23 views

Change location of innodb_temporary

I want to change the path where the ibtmp1 is stored in mysql 8.0.25 on debian 11. How to do this? I see: mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G ***...
user avatar
  • 111
2 votes
2 answers
297 views

MySQL Innodb, why does the size of primary index depend upon the order of insertion of records

I have a table with this schema CREATE TABLE `UserIdPhoneNo` ( `userId` int NOT NULL, `phoneNo` bigint NOT NULL, PRIMARY KEY (`userId`,`phoneNo`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 I am ...
user avatar
  • 123
0 votes
1 answer
79 views

Mysql service stops with [ERROR] InnoDB: CORRUPT LOG RECORD FOUND

In my Plesk Obsidian version 18.0.42 running on CentOS Linux release 7.9.2009 with mysql version 10.2.43-MariaDB. All of a sudden database has stopped working. Mysql service never comes up on start ...
user avatar
0 votes
1 answer
70 views

Row size too large (> 8126) - 5.7.mysql_aurora.2.10.0

CreateTableConstraintStep ddlList = (CreateTableConstraintStep) dslContext .createTableIfNotExists(getDSLTableName(List)) .columns(fields) ...
user avatar
0 votes
1 answer
98 views

Can it be safe to disable binary logging in MySQL?

Is the MySQL binlog similar to Oracle's redo log or PostgreSQL's Write-Ahead Log? If yes, how come it be possible to disable bin logging? If the machine crashes while the data files are being updated, ...
user avatar
0 votes
1 answer
22 views

Why is there a big gap in the actual Table space? (calculated vs information_schema vs file size)

I have a InnoDB table with the following columns: BIGINT -> 8 bytes BIGINT -> 8 bytes ENUM(0,1) -> 1 byte MEDIUMINT -> 3 bytes INT -> 4 bytes = 24 bytes per row It contains 10454004 ...
user avatar
  • 599
2 votes
0 answers
30 views

Table going into Repair mode during upgrade

Recently I was doing an upgrade in production system from MySQL 5.6 to 5.7. there is a particularly large innodb table about 2.5 TB which uses hash partitioning. when I ran mysql_upgrade I noticed ...
user avatar
0 votes
1 answer
33 views

What is the performance overhead of IFNULL in the select statement of mysql 5.7+ query

I've recently made an upgrade to a mysql query performed by my web server when serving request. There are two main permutations of the query, one will typically be limited to returning 500 rows and ...
user avatar
0 votes
0 answers
22 views

Why MariaDB doesnt respect adaptive flushing setting?

Using MariaDB 10.4.24. I have adaptive flushing enabled (innodb_adaptive_flushing=ON). My understanding is, that InnoDB will flush dirty pages slowly, but steadily. However my checkpoint age keeps ...
user avatar
  • 115
0 votes
1 answer
36 views

Importing data into Mysql slowly

This file is 23GB in size, it was created with Dump from the workbench It starts well, but when it reaches 1/3 of the recorded data, the import starts to be extremely slow. What do you suggest to ...
user avatar
4 votes
1 answer
171 views

Why SQL inserts are slower in AWS Aurora Serverless than in AWS RDS

We had an AWS MySQL RDS t3.xlarge instance with 2000 IOPS. Also, I have a cron job written in java which inserts data in a table: it can insert up to 12M rows daily. Since AWS RDS cost is quite high ...
user avatar
  • 141
1 vote
1 answer
44 views

Why MySQL dump consume memory after finished

Hi I have a cron job to backup a lot of my database. I create a script to run the dump command for each database every night, I created it like this. mysqldump --defaults-extra-file=$DEFAULT_FILE -e -...
user avatar
0 votes
1 answer
113 views

No free buffers in MariaDB 10.6

I have recently updated MariaDB from 10.4 to 10.6. One major change in the underlying InnoDB engine is the removal of buffer pool instances, so there is always only one. I believe this affects some ...
user avatar
  • 115
2 votes
1 answer
98 views

MySQL 5.7.37 is consuming CPU and High memory ( 90-95% )

I have a problem with my MySQL database. RAM usage is too high, CPU too. Does it come from my configuration ? Here's my configuration : auto_generate_certs ON auto_increment_increment 1 ...
user avatar
  • 21
0 votes
1 answer
64 views

how to recover from failed transaction due to crash [mysql]?

I am trying to start MySQL server, but it keeps throwing errors. Upon inspecting I found out that the server was stopped unexpectedly and there was a transaction in the process that didn't finish. I ...
user avatar
0 votes
0 answers
46 views

Can't UPDATE a database table

I'm getting a 'Error synchronizing data with database' error on MySQL 5.6.10 (on AWS). Trying to update a field (using dBeaver) but getting error message/ Further: Data truncated for column ...
user avatar
  • 1
0 votes
1 answer
66 views

Does innodb_buffer_pool_size setting affect update queries?

I have a huge database(16GB) to which I wish to add a new column based on an existing column. The existing column looks like this:- MariaDB [demo]> SELECT my_mon FROM mytable; +----------+ | my_mon ...
user avatar
0 votes
1 answer
18 views

How can MySQL be optimized for unattended operation on unreliable hardware?

I run some personal services on a not-entirely-reliable single-board computer. Sometimes it locks up and needs to be power cycled. One of these services is currently configured to use MySQL with ...
user avatar
  • 101
0 votes
1 answer
145 views

Recover corrupted database

The MySQL database running in an old Ubuntu 14.04 seems corrupted as successive sequence numbers don't match. 220127 15:16:11 InnoDB: Error: page 14357 log sequence number 41558915039 InnoDB: is in ...
user avatar
0 votes
1 answer
44 views

drop and re add an index on innodb table causes slow performance

We just made a code and db update, that involves a table on production. This INNODB table was "altered" by adding a new index and dropped the old one. The new is made by 3 params and the old ...
user avatar
0 votes
1 answer
76 views

mariadb innodb file per table data location

I have the following settings: datadir = /var/db/mysql/data innodb_data_home_dir = /var/db/mysql/innodb innodb_file_per_table = ON My intention is to ...
user avatar
  • 101
0 votes
0 answers
55 views

InnoDB - Fulltext (match against) extremely slow, wrong indexes?

I've recently migrated my myisam-tables to InnoDB and some things are a bit weird for me. For starters, the indexes looks a bit different (and maybe is the reason why the new InnoDB-implementation is ...
user avatar
  • 121
0 votes
1 answer
40 views

InnoDB Buffer Pool and Buffer Log

I have been reading too many information about InnoDB parameters, but I still have a doubt. Should "innodb_log_buffer_size" be larger than "innodb_buffer_pool_size"?
user avatar
1 vote
2 answers
139 views

InnoDB's undo log start growing after simple SELECT with READ-UNCOMMITTED level

I have a percona mysql (5.7) server with 20K QPS (lots of inserts/updates/deletes). My question is: why issuing a simple, but long, select query (to any table) with trx isolation=READ-UNCOMMITTED ...
user avatar
0 votes
2 answers
36 views

MySQL - Planning Cost Higher Than Number of Blocks Occupied in Disk

There is a table dept and it has 100 rows, all nicely tucked in 1 block, as seen in the mysql.innodb_index_stats table. table_name index_name stat_name stat_value sample_size stat_description dept ...
user avatar
  • 165
2 votes
1 answer
24 views

MariaDB index chosen differs on ARM and x64 based procesors

When I have been using mariaDB on apple M1 chip, I discovered some diference between index chosen in select statement versus x64 server linux system with this query: explain SELECT z.id_hlavni AS ...
user avatar
0 votes
1 answer
87 views

OPTIMIZE TABLE seems to "lock" tables (innodb)

During executing an OPTIMIZE TABLE on big tables the process seems to "lock" the tables. Is there something you can do to speed up the process or prevent it from "locking" the ...
user avatar
  • 123
1 vote
3 answers
158 views

Why does MySQL require two or more redo log files for InnoDB?

A single redo log file can also be written in a circular fashion. In fact, MariaDB has changed its InnoDB redo log to a single file, and its developers claimed that "the log file was ...
user avatar
3 votes
1 answer
92 views

Finding number of Innodb pages to read

Is there any way to find number of pages that needs to read when querying for a set of pks? Eg., select * from Table where id in (1,2,3,4,5,6); Now is there a way to find the innodb pages that'll be ...
user avatar
  • 31
0 votes
1 answer
290 views

fix InnoDB: corrupted TRX_NO after upgrading to 10.3.31

After upgrading mariadb from 10.3.25 to 10.3.31 it fails to start like described in this bug report I temporary fixed it by downgrading mariadb to 10.3.25 again like I described here. To fix this ...
user avatar
  • 798
1 vote
2 answers
72 views

Make running MySQL with InnoDB flush everything to file to allow file-level live backup

A client has a file-level backup, where every relevant file of a critical webserver is synced to another webserver around 1am. This includes the /var/lib/mysql directory where the databases of that ...
user avatar
  • 13
1 vote
1 answer
60 views

MySql 8.0 Foreign Keys Missing in InnoDB

I have created 10 tables in MySql 8.0 using the InnoDB engine. I defined relationships using the references statement. When I view the tables using show create table, the foreign keys are missing. I ...
user avatar
  • 111
0 votes
0 answers
278 views

MySQL Error: Multiple files found for the same tablespace ID

Today my MySQL started giving me this error when I try to start the MySQL Service: [ERROR] [MY-012209] [InnoDB] Multiple files found for the same tablespace ID [ERROR] [MY-012202] [InnoDB] Tablespace ...
user avatar
-1 votes
1 answer
123 views

Composite Primary Key and Secondary Covering Index: Does the Secondary Index Ever Need to Include a Primary-Key Column?

MySQL. InnoDB. Say there's a composite primary key 'a, b'. There is another column 'c.' I have a query hitting 'a, c.' Consider two claims. (A) "A secondary index on 'c' gets me a covering index.&...
user avatar
1 vote
1 answer
58 views

Speeding up SELECT FROM a large table

I have a large table (500M rows with ibd size of 1.5TB) CREATE TABLE t1 ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, ChildID int(11) unsigned NOT NULL, Number smallint(5) unsigned, Title varchar(255)...
user avatar
  • 4,179
0 votes
0 answers
37 views

How to recover data from idb files [duplicate]

I've tried: Used mysqlfrm to recover the table structure. Created it. Discarded tablespace. Copied .idb file to directory. Imported tablespace. But file size in information_schema was 0; Then I ...
user avatar
0 votes
1 answer
63 views

Force kill long running index creation

I have a 192GB table that I started adding an index to over night. This was still going several hours later. At the time the alter table started the database was very quiet. There were other queries ...
user avatar
  • 263
1 vote
1 answer
43 views

Monitoring MySQL/innoDB post-process

I did INSERT INTO SELECT of a huge table (1TB). When the process is finished, MySQL/InnoDB still does post-processing (creating updating secondary indices, etc.) for hours. Since these are internal ...
user avatar
  • 4,179
1 vote
4 answers
95 views

Tuning innoDB for a write-intensive machine

I have MariaDB 10.5 on my desktop with multiple disks (SSD and HDD) for write-intensive projects. Writing to a single table is fast and the percentage of dirty pages remains close to zero with 1000-...
user avatar
  • 4,179
0 votes
0 answers
130 views

Recover data from .ibd file with corrupted page

I'm seeing "incorrect information in file" errors when opening my INNODB tables. I followed the general recommendation to extract the CREATE TABLE statements from the .frm files using ...
user avatar

1
2 3 4 5
44