Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

Filter by
Sorted by
Tagged with
2
votes
1answer
25 views

InnoDB errors and crashes with MyISAM database?

I have one server with one mysql database containing only MyISAM tables. There is no other databases on the server (except system ones of course). Since it moved to mysql 5.7.27 there are InnoDB ...
0
votes
0answers
9 views

show extended index equivalent for MySQL 5.7

If you look at this documentation for show index in version 8.0, it says SHOW [EXTENDED] {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr] The ...
0
votes
0answers
12 views

can delete manually a .ibd file and .frm file?

I have the following trouble in a mysql server database: 190919 17:35:30 [ERROR] Cannot find or open table abril2017/mexico from the internal data dictionary of InnoDB though the .frm file for ...
0
votes
1answer
13 views

In InnoDB, how does fuzzy checkpointing's recovery consistency work?

I've looked the documentation quite thoroughly but still cannot figure out so asking this question. In InnoDB, I understand that any updates against buffer pool are tracked by redo log, which gets ...
1
vote
1answer
21 views

Cannot remove orphaned InnoDB Temp Table - missing .frm file

For the second time, I have encountered an issue, where our MySQL server (MariaDB v10.1) suddenly threw an error - Could not remove temporary table: './database_name/#sql-4593_791', error: 120. ...
1
vote
0answers
21 views

Unable to recover MySQL 5.6 (InnoDB) database

I've tried multiple different suggestions from other questions on here, with no luck... This is a MySQL database running version 5.6.41, using the InnoDB engine, and the instance is running out of a ...
1
vote
1answer
26 views

MySQL 5.7 Can slave be ahead of master during replication

From my understanding, once commit() is received from client, transactions are written to binlog first before any commit is done to InnoDB. In this case, is it possible for slaves to be ahead of ...
0
votes
2answers
48 views

MySQL DBs taking up way too much disk space

I'm on Ubuntu Server 18.04 running MySQL server 5.7.27 with InnoDB. I confirmed that InnoDB file-per-table is On. I have a mysqldump containing all of my DBs that takes up about 1.3 GB disk space. ...
0
votes
1answer
8 views

Mysql - tablename.ibd file is huge in size

I'm using MySQL 5.6 and innodb file per table has been enabled. I noticed that one file named as tablename.ibd is 211GB, then query the information schema there it's showing 21GB. I have done a huge ...
0
votes
1answer
29 views

Size of record pointer in InnoDB?

I am trying to put on a scale redundant attributes and performance gain. Although storing redundant information causes many anomalies, I am solely focusing on the size of a redundant attribute vs. ...
0
votes
0answers
6 views

Got an error reading communication packets

I have a server application that's using MySQL INNODB databases to store its data. I know that this application has worked as is in the past, but I don't know anything about the database server ...
0
votes
0answers
22 views

index corrupt error while trying to index a large table

I am trying to index a large table, approximately 12 million rows and 33GB in size. There is currently no indexing on the table. When I am trying to index one of the columns in a table, I get the ...
0
votes
1answer
31 views

Why do some write sometimes go faster than expected for MEDIUMTEXT

We are running a MySQL Innodb database, our server has 4gb memory. We are trying to improve the speed at which a 800KB XML File is stored in a MEDIUMTEXT column. Currently, we have this query ...
0
votes
0answers
26 views

MySQL Service won't start with error [ERROR] InnoDB: Trying to access page number 4294963199 in space 0

My MySQL installation on Ubuntu 18.04 crashed and now won't start. The error log says Trying to access page number 4294963199 in space 0, space name innodb_system, which is outside the ...
0
votes
0answers
21 views

MySQL 5.7 isn't using new innodb buffer pool settings

I have a very tiny VM which keeps running out of memory. I believe this is due to the buffer pool size. Default appears to be 128M for innodb_buffer_pool_size and innodb_buffer_pool_chunk_size. So I'...
0
votes
0answers
22 views

Optimising InnoDB configuration in MySQL80 Server

I have a WAMP production environment, currently running Apache24, MySQL57, PHP73 on two Windows Server 2016 servers - an application server and a dedicated database server - and I'm toying with the ...
0
votes
0answers
15 views

mysql db data folder restore in 5 days old backup [duplicate]

recently I had the need to install a driver on a server and because of the destination I finished uninstalling XAMPP in which I had a database that I need to recover. The fact is that after ...
0
votes
0answers
7 views

innodb corrupt database - recover events and functions

There is plenty of articles how to restore the data but can't find anything about the scheduled events and functions recovery. The db crashed due to running out of disk space (plesk). I managed to ...
1
vote
1answer
429 views

MySQL Replication stuck at 'preparing'

My MariaDB replication stuck at 'preparing'. This happend after an update and restart of the server yesterday. Since then, replication is not working. Setup: 2 MariaDB servers with master-master ...
0
votes
0answers
34 views

How do I recover an innodb database when I have ibdata1 intact and a subset of the original .ibd/.frm pairs of files?

Originaly it runned on mysql Server version: 5.6.28-0ubuntu0.15.04.1-log (Ubuntu) now it runs on Server version: 5.7.27-0ubuntu0.18.04.1-log (Ubuntu) I can get the structure by generating a .sql file ...
0
votes
1answer
25 views

Selecting consecutive rows with same values and one that differs

So there is this question that explains how to do it but what I want is to find the duration from idle to active for a given userID and status for example for Brown it should show something like this: ...
0
votes
3answers
28 views

How to maintain unique sequence across table columns?

I hava two tables registed_products and unregisted_products. Both have autoincrement primary key which can be overlapping which if 100 id exist reg table, it can exist in unreg table as well. Insert ...
0
votes
1answer
20 views

InnoDB Mariadb 10.0.27 high IO while idle

centos7 mariadb 10.0.27 Yesterday i did a lot of delete operations from innodb tables (table size - 100gb, deleted part - 40gb. Many indexes on table). And now i have strange situation on this ...
0
votes
0answers
13 views

Best setup variables for mysql with big large table row size?

Here are the variables for our server, and I want to know what are the best settings for mysql server with 2.5 million records transaction... MariaDB [(none)]> show variables like 'innodb%'; +-----...
0
votes
0answers
25 views

mariadb import - all rows missing except those in final INSERT of InnoDB tables

This is a very strange bug. I'm using this on a mac: mysql Ver 15.1 Distrib 10.4.6-MariaDB, for osx10.13 (x86_64) If I import an SQL dump of a WordPress DB (so not very complicated structure, but ...
1
vote
2answers
93 views

IN clause not working on mysql 8.0.17

I was on mysql:8.0.15 and I updated to mysql:8.0.17 On 8.0.15 ... and (col1, col2) in (('a', 'b')) the query return results, this is what I expect On 8.0.17 the same query with same data return 0 ...
0
votes
1answer
19 views

ibdata1 back to it's original size after deleting it

I have followed the following steps in order to save space but I am surprised after restarting MySQL ibdata1 gets back to it's original size. As you want to reclaim the space from ibdata1 you ...
1
vote
1answer
104 views

Unable to add new instance on MySQL InnoDB cluster

I'm setting up new MySQL InnoDB cluster 8.0.16 consisting of three instances: srv-mysql-01 srv-mysql-02 srv-mysql-03 I run JS-script in mysql shell for build cluster: mysqlsh --file=/tmp/...
0
votes
0answers
11 views

How Do Multiple Innodb Buffer Pool Work in Mysql 5.7

Assume that I am using the default settings in Mysql 5.7 which has 8 buffer pools and each is allocated 1GB. Then assume that there are 3 tables, each table are 2GB each. Also, assume this is a test ...
0
votes
0answers
39 views

How do I use the .ibd/.frm files to recover an InnoDB table that went missing when MySQL crashed during OPTIMIZE TABLE?

I have seen How to Recover an InnoDB table whose files were moved around but I am not sure if it's relevant, since MySQL renamed the files and there was no tablename.frm afterwards. I ran OPTIMIZE ...
0
votes
1answer
42 views

Table Partitioning and Performance

I have to migrate one large table and need to do partitioning of the same. I would like to know whether i do Partitioning first and migrate the data second or I do migrate the data first and ...
0
votes
1answer
25 views

How to find if my innodb row uses overflow/off pages?

I use INNODB engine. My row format is Dynamic. For variable-length columns like TEXT, BLOB and varchar etc. , data is stored in off page and not in the same page itself. Do I have a way to ...
0
votes
1answer
11 views

MySQL 5.6 service not going to stop

Something happennig in my environment MySQL 5.6.34 RHEL 6 22G buffer pool size When i stop MySQL it shows: service mysql stop ..................................... Until and unless I kill using ...
0
votes
1answer
25 views

Percona/XtraDB Database with many of writes to a small table uses less CPU than the same database with less writes?

I'm trying to understand why Percona and xtradb InnoDB/MySQL behaves the way it does and if there's anything I can change in the configuration to fix it. I've a InnoDB database with only one table ...
0
votes
1answer
29 views

Delete duplicate rows that have same non-unique values in MySQL InnoDB

How do I drop rows that have the same values in the column title? I do have a unique hash for all the rows, but they seem to have failed me anyway. As an added question, If my row_id is all over the ...
0
votes
1answer
61 views

MariaDB: Duplicate entry for key on OPTIMIZE with no duplications

There is a table named contracts with following structure: MariaDB [db_name]> DESCRIBE `contracts`; +-----------------------------+--------------+------+-----+---------+----------------+ | Field ...
0
votes
0answers
11 views

mysql innodb locking

In one transaction I'm inserting a lot of rows into table T1. "After insert" trigger of table T1 inserts into table T2. Is it possible during that process select from T2 in another transaction? Or ...
0
votes
0answers
26 views

MySQL - Investigate deadlock on atomic update

On a high traffic queue managed by Redis there is the following code: update `ads_publishers` set `impressions` = `impressions` + 1 where `ad_id` = ? and `pub_id` = ? Here's is the table schema: ...
0
votes
0answers
40 views

Corrupted InnoDB still won't start even after removing corrupted tables

My MySQL server stopped working randomly and upon checking log files I found this: 190622 23:45:10 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer ...
0
votes
1answer
170 views

Mysql innodb recover from frm and ibd

There are numerous question (in this site and elsewhere on the internet) about how to recover innodb database from *.frm and *.ibd file (the point: when you have the undamaged ibdata1). Situation: I ...
8
votes
2answers
327 views

Covering index used despite missing column

I have the following query, using MariaDB 10 / InnoDB: SELECT id, sender_id, receiver_id, thread_id, date_created, content FROM user_message WHERE thread_id = 12345 AND placeholder = FALSE ORDER ...
0
votes
0answers
51 views

Mysql 5.7.25 crash on first query to corrupted innodb table

Setup: Ubuntu 18.04.1 LTS 4.15.0-34-generic #37-Ubuntu Docker: 18.06.1-ce build e68fc7a mysql:5@sha256:e47e309f72c831cf880cc0e1990b9c5ac427016acdc71346a36c83806ca79bb4 mysql --version = Ver 14.14 ...
0
votes
0answers
5 views

InnoDB clustered index performance when using random values as primary key

By default, my primary keys of InnoDB storage engines are auto increment integers. In effort, to hide the number of rows in the database, application code implements some random generator for the ...
0
votes
0answers
26 views

innodb file per table

We are running with shared tablespace on MySQL database server in our environment. We are looking to enable file per table on database. We want to know What would be ideal plan to enable file per ...
0
votes
0answers
15 views

Mysql 8.0.16 crash due to informatiin_schema.Keywords table

How to resolve issue of mysql 8.0.16 crash?mysql 8.0.16 crash many times and shown server logs due to invalid argument.and in error log query is select WORD from information_schema.KEYWORDS. how to ...
2
votes
1answer
182 views

How to clear the InnoDB redo log before starting mysqld for crash recovery

System: Windows Server 2012 R2, MariaDB 10.2.8 We are currently trying to recover from a quasi-crash (it was a normal shutdown, but mysqld won't start up again) and are having problems with the redo ...
0
votes
1answer
123 views

MySQL 5.6 too much write after enabling compression

Recently I have enabled table compression (i.e. ROW_FORMAT=COMPRESSED) on half of the tables in a MySQL 5.6 server. I am observing: much higher write IOPS (~700 -> ~3000) dangerously lower dirty ...
0
votes
0answers
29 views

Different Permissions Behavior For Information Schema on Views

I have a view which have following definition Create view view1 as select * from information_schema.events where event_schema="database1" I have granted select permissions on the view1 to ...
1
vote
2answers
76 views

MySQL: Is it safe to set `key_buffer_size` to 0 if the db doesn't have any MyISAM table?

In our system, we don't use MyISAM - only InnoDB (and occasionally, MEMORY). Is it safe to set key_buffer_size to 0? Or there are unexpected side effects? Based on the manual, it seems it's safe.
0
votes
2answers
88 views

innodb high i/o , some parameters make mysql crash

i have a windows 2012 server with 16 core cpu and 64 gig of ram and 3*146gig sas 15k that have been used as a database server and just have mysql installed as a part of xampp. the mysql version is 5....