Questions tagged [innodb]
InnoDB : MySQL's ACID-compliant Storage Engine
0
votes
0answers
19 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
29 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
32 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
28 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'....
0
votes
0answers
46 views
MySQL 5.1.73 Tuning for PHP5.6 Application [on hold]
I have a PHP5.6 application running Symfony 2.x, backed by MySQL 5.1.73. We are currently experiencing latency in our data tier during peak load. During normal operation we see about 2k queries per ...
1
vote
0answers
38 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
42 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
76 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
17 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
17 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
37 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
84 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
42 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
19 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
29 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
43 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
36 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
36 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
66 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
74 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
30 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 ...
0
votes
1answer
23 views
Table metadata lock MariaDB
I'm having serious problems in a DB, where a "Waiting for table level lock" box is loitering and locks the whole system:
Based on some research, when the problem occurred to get the command "SELECT * ...
3
votes
1answer
119 views
Are innodb_large_prefix and innodb_file_format settings backwards compatible?
I got the following message when I tried to install a piece of software trough the command line. I use a mySQL db.
* innodb_large_prefix = ON is required.
* innodb_file_format = Barracuda is ...
0
votes
1answer
26 views
How to PARTITION a table in mysql?
I read a lot of articles that PARTITIONing is not helpful in most cases. I believe it should speed up the queries in my case. I have a table with the structure of
CREATE TABLE pages
(
page_id int(11) ...
1
vote
1answer
40 views
Much slower replication on Innodb compared to myisam
Good morning. I wanted to test how InnoDB will work on my servers and for this I made a slave and connected it to my master with myisam.
The problem is that when the slave works on innodb, replication ...
0
votes
0answers
22 views
Innodb errors - considering changing to aria
On my local machine, using XAMPP, my MySQL would intermittently not start. I have followed lots of solutions for this, and the innodb_force_recovery=1 usually fixes it, at least for a while.
It is ...
0
votes
2answers
18 views
Considerable size difference between mysql versions
We usually employ different MySQL versions for different scenarios. And I realized that similar databases, whith the same amount of tables and data, just because of different mysql versions differs in ...
0
votes
2answers
38 views
How to optimize SQL query which involves GROUP_CONCAT function with ORDER BY
I wrote the following SQL query to group results and then sort results in each group by date:
SELECT
origin,
provider,
SUBSTRING_INDEX(GROUP_CONCAT(price
ORDER BY departure_date
...
3
votes
0answers
25 views
Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?
I have a big table (30 millions rows) defined like this:
CREATE TABLE `drawing` (
`id` int unsigned NOT NULL,
`artist` int unsigned NOT NULL,
`imageUrl` text NOT NULL,
`uploadDate` timestamp NOT ...
0
votes
1answer
56 views
Subtract values in same column from 2 different dates in same Mysql table
I have a MySql table like:
Item Date Likes
Book 2012-05-20 402
Book 2012-05-21 432
Book 2012-05-22 450
Pen 2012-05-20 20
Pen 2012-05-...
0
votes
1answer
40 views
How to find the MySQL query that got rolled back
I want to know if a certain transaction got rolled back. If we analyze
SHOW ENGINE INNODB STATUS\G;
we get the list rolled back transactions with transactionid , but how to get the actual query ...
0
votes
1answer
33 views
Planning and Optimizing the table structure InnoDB MySql
I am planning to have an InnoDB MySql table with tens of millions of rows.
Each row represents a record that can belong to AT LEAST one of 200+ categories. Most of the records will belong to 2 or ...
0
votes
2answers
109 views
Upgrade Antelope to Barracuda on Slave
I received a Last_Errno: 1118,
Last_Error: Error 'Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row ...
1
vote
0answers
29 views
Why is my InnoDB table size so much larger than the data?
I have a MySQL table (version 8.0.13). One table is unreasonably large and doesn't correspond to the current data. I rebuilt the table by running ALTER TABLE users FORCE and OPTIMIZE TABLE users, but ...
1
vote
1answer
128 views
MariaDB 10.3: InnoDB much slower than MyISAM
I'm using MariaDB 10.3.12 on Arch Linux and haven't modified any of the default settings under /etc/mysql. The following script takes 0.75 s to run on average using the default InnoDB engine:
drop ...
0
votes
2answers
38 views
How does MySQL store data, can I detach a mysql Database?
I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to ...
0
votes
0answers
47 views
MySQL InnoDb crash when importing tablespace from remote machine
InnoDB crashes when importing ibd file from remote machine. Source version is MySQL-5.6.40, Destination version is MySQL-5.6.40.
Database was backed up was backed Percona Xtrabackup with xbstream ...
0
votes
1answer
36 views
Safe online dumping of an InnoDB database in the presence of DDL changes
When dumping an InnoDB database using mysqldump, it is often recommended to use the --single-transaction and --skip-table-locks options. This allows for minimal blocking of other concurrent reads + ...
1
vote
2answers
69 views
Overly eager explicit row locking causes deadlock
I'm running into deadlock problems on MariaDB using the INNODB engine.
In my use case, I've got a number of threads working on a table. Each thread first reads a number of rows and then updates those....
0
votes
0answers
35 views
tokudb, rocksdb or aria engine with huge number of tables
My question is about what engine to use with a super big quantity of tables and lots of tables with some million rows.
My quantity of table is increasing and it will get to near 100 000 tables...
...
4
votes
2answers
49 views
Performance issues with groupwise MAX in JOIN clause
Problem
I have some assets in my application which are updated from time to time in an asynchronous fashion.
The example I'm gonna use here is Vehicles. There are two tables:
Vehicles: holds ...
2
votes
1answer
112 views
SELECT execution is slow on InnoDB when compared to MyISAM
We experiencing a slow select on InnoDB comparing to MyISAM - difference for execution on MyISAM is about 2 min., on InnoDB is 2 h.
The number of rows involved is 33 M. The row set in result is about ...
5
votes
4answers
463 views
Which columns should be indexed when all may be used in different search queries?
Background
I'm working on a website for a movie theather chain currently located in four different cities (might expand in the future). They use the same, single-database website for all cities, ...
0
votes
1answer
39 views
MySQL: Change compression on an existing InnoDB table
Is there any way to turn on compression for an existing table? I don't care if there's downtime for this.
The docs only mention setting compression when the table is created.
0
votes
2answers
133 views
Unable to fix ROW size too large even with innodb_file_per_table
I am having a row length error I've been unable to find a solution for. The error messages are all variations on "Row size too large"
Error executing 'CREATE TABLE IF NOT EXISTS `db`.`table`
Row size ...
0
votes
1answer
72 views
Unable to turn on compression using innodb
I am unable to create a new table using compression.
I'm using the following options which from what I understand should allow me to use compression.
default-storage-engine = InnoDB
...
2
votes
2answers
71 views
When does MySQL begin to start sending data to a client?
I am working on MySQL 5.7 with the default configuration of InnoDB storage engine. I have two questions each for a different scenario and need to know the internal working on how MySQL sends data to ...
0
votes
0answers
72 views
Mysql 8.0:Innodb index performance loss until server is dead. Performance perfect after index is re-created?
Important Update
Spent 15 hours on this problem, rebuilt and changed indexes and the table many times.
Finally I can say that it's NOT a "virtual column" problem, it's an Innodb Indexing issue.
The "...
1
vote
0answers
19 views
Getting DEADLOCKS frequently on Versioned TABLE
I am having trouble to understand the increase in DEADLOCKS for Update transactions on Versioned TABLES. The problem, was not present earlier and then we enabled the versioning and after a while we ...
