Questions tagged [innodb]
InnoDB : MySQL's ACID-compliant Storage Engine
1,848
questions
0
votes
1answer
2 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 ...
0
votes
0answers
17 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 --interactive --file=/...
0
votes
0answers
9 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
35 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
33 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
19 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
9 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
24 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
26 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 ...
-1
votes
0answers
32 views
Mysql 8.0.16 Hang cannot restart on centos 7
Mysql 8.0.16 on Centos 7 hang cannot restart and stuck
i tried to restart mysql (still waiting process) , kill PID (stuck and do ps aux | grep mysql and show [mysqld] )
this is log error mysqld.log ...
0
votes
1answer
44 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
22 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
23 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
43 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 ...
7
votes
2answers
323 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
36 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
12 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
13 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
60 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
108 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
49 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
70 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....
1
vote
0answers
14 views
How can I reproduce InnoDB buffer pool 'warm-up' time? What am I doing wrong with sysbench?
I've found that a large production DB experiences a severe degradation in performance, after a restart. It makes sense that this is because after a restart, the innoDB buffer pool isn't yet warmed up ...
1
vote
2answers
72 views
Same column present on two or more foreign key constraints
A simple schema leading to my question is the following:
CREATE TABLE A (
AID INT AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE A_COPY (
AID INT,
CID INT,
PRIMARY KEY (AID, CID),
...
0
votes
2answers
62 views
MySQL 5.6.26 high concurrency dead lock
Table structure:
CREATE TABLE `extend_0` (
`id` bigint(20) NOT NULL,
`code` varchar(30) NOT NULL,
`data_key` varchar(50) NOT NULL,
`data_value` varchar(200) NOT NULL,
`create_time` timestamp NOT ...
0
votes
0answers
42 views
Restore a 140GB - 292 Million entrie MySQL-Database faster?
i need help. I have an old innodb Server and my storage limit reached.
I dont use files per table entries because in old times there were no ability at this time. My ibdata grown up fast and there is ...
0
votes
0answers
26 views
Mysql 8.0 table_rows problem
I'm doing an application and I have the following problem:
I'm doing a LOAD DATA INFILE into a table from a txt file, that has 2 million rows.
I want to see the table_rows of my table with:
SELECT ...
0
votes
1answer
85 views
MySQL/innodb trying to lock uncommitted row from parallel transaction, deadlock in result
MySQL 8.0.16, 2 simple RC transactions, each trying to insert and delete rows:
CREATE TABLE test1 (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100),
PRIMARY KEY (id)
) ...
0
votes
1answer
61 views
Will too many tables slow Mysql down?
I'm making a database which should be able to handle over 1,000,000,000 rows.
in order to optimize it's performance i've decided to split tables.
so instead of having 1,000,000,000 entries in one ...
0
votes
1answer
29 views
What will be the order of statement execution in this use case?
Say I execute select * from users at t1 which gets finised at t3. At t2(in between t1 and t3) update comes for some of the records in users table say
update users set address='xyz' where name = 'abc'....
1
vote
0answers
72 views
The GET_LOCK() and RELEASE_LOCK() functions are taking too long
We are trying to use the MySQL's locking functions GET_LOCK() and RELEASE_LOCK() in our Spring Java application. The code we are using is defined in a procedure for both seperately which simply invoke ...
0
votes
0answers
99 views
buffer overflow detected in mariadb 10.3.11
We are getting Assertion failure every time memory usage of MySQL spikes up to the overall system's memory.
Example:
InnoDB: Assertion failure in file
/home/buildbot/buildbot/build/mariadb-10.3....
2
votes
1answer
149 views
Mysql/innodb deadlock on simple delete query
Mysql/innodb 8.0.16, read committed transactions, several statements in transaction raise deadlock on deletion of non intercepting rows. Trying to understand what is happening:
-----------------------...
0
votes
1answer
21 views
How to change all columns from one type to another, changing all FK columns as well?
In an inherited DB we found that there are around 25 tables with a primary key defined as tinyint.
For a few of those, it's an imminent problem, since this optimistic optimization clashes with the ...
0
votes
1answer
28 views
Make Xtrabackup ignore myisam tables & backup only innoDB tables?
I'm using mysql 5.7 (or rather percona 5.7)
Is it possible to backup only the innodb tables (easily, not to specify each table one by one because I have a lot of tables) with xtrabackup? and not take ...
0
votes
2answers
44 views
Query not returning results if selecting by indexed columns (Mysql 5.6)
We had a very strange encounter that all of our queries that uses an indexed column in the select wasn't returning any results. On the other hand, if we do select * or on any other non-indexed column ...
3
votes
2answers
147 views
Gap Locking in Read Committed isolation level in Mysql
MYSQL VERSION : 5.7.X
STORAGE ENGINE : Innodb
I have a general idea that Read Committed Isolation will mostly use Shared and Exclusive Record Locks. But, as per mysql docs, there are some cases where ...
0
votes
0answers
69 views
How to improve the speed of a large data migration between SQL Server and MySQL server?
I have an application that is connected to a SQL Server Express database. The database grew and the database is almost at 10GB which is the max limit that SQL Server Express allows for.
I am trying ...
1
vote
0answers
24 views
Preserving some data on rollback in InnoDB?
I want to keep log of attempted, sucessful, and failed operations (both for troubleshooting, and also for detecting malicious users trying things). I want to keep that log in InnoDB MySQL table.
Now, ...
0
votes
1answer
53 views
Wrong InnoDB table status (Size/Rows) after updating from MySQL 5.7 to 8
I've upgraded from Mysql 5.7 to 8 on Ubuntu 18.04
Made it by guide, very accurate, on a new empty server, checked all error/warning logs and fixed it. Everything works just fine with it.
I have 2 ...
0
votes
0answers
70 views
INSERT INTO SELECT for a large table
I have a large table (200 million rows, 40GB). I tried to create a new table out of it by INSERT INTO SELECT. The process took 5 hours and consumed over 60GB of RAM. The size of the ibd file of the ...
0
votes
2answers
37 views
What is going on with my user and max date query?
OK query below. Hitting about a half million rows and I am trying to grab the last time a user (uid) touched a page on our system denoted by LogDate. I have used this query quite a few times but ...
1
vote
1answer
37 views
Optimize MySQL InnoDB query for SUM and Order By
I have an MySQL InnoDB table with 1.3M rows and 670MB size:
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------...
1
vote
3answers
76 views
Mysql innodb table dead lock while batch update in multiple thread
I've two tables of innoDB type and I'm updating one table using batch statement from 10 thread (one batch statement per thread) but some time I'm getting dead lock.
java.sql.BatchUpdateException: ...
4
votes
1answer
77 views
Is there a way to improve performance of this simple SELECT query?
I'm still relatively new to MySQL so please forgive any ignorance on my part. I've tried looking through other posts on here and StackExchange, but haven't had much luck in improving the performance ...
0
votes
0answers
29 views
Counter Field that Increments on Update?
I need a "counter" field that gets the latest value of a counter and then increments the global counter value so that the next update on a reocrd gets the new latest value.
For example, lets say this ...
0
votes
0answers
55 views
MySQL InnoDB: how to interpret this InnoDB monitor output of Next-Key locks?
Next-Key Locks from MySQL documentation
A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.
InnoDB performs row-level ...
