Questions tagged [innodb]
InnoDB : MySQL's ACID-compliant Storage Engine
2,087
questions
0
votes
1answer
14 views
Sync tables with selections
I have a problem that I don't know how I could solve it.
I have two tables in two different databases on two servers.
Server 1 (Master) has the data and server 2 is the slave.
But Server 2 only needs ...
2
votes
1answer
46 views
Improve MySql query performance in large table
I have a MySql database that is used to store events from an app, since we created it, we only inserted and selected data, we've never deleted any rows. I'm not a DB admin and there's no DB admin in ...
0
votes
1answer
27 views
How do I correct InnoDB Multiple files found for the same tablespace ID?
Some time ago after an update of Ubuntu 20.10, I discovered that MySQL was not running. When I tried to restart it, the error message suggested that it had started, failed and stopped. A check of the ...
2
votes
1answer
94 views
Can not drop virtual column | ERROR 1054 (42S22): Unknown column in 'GENERATED ALWAYS'
I'm just running a command:
optimize table some_table_name_here;
But it results with:
Table | Op | Msg_type | Msg_text
db.some_table_name_here | optimize | note | Table does not support optimize, ...
0
votes
1answer
17 views
Recovering InnoDB database from backed up “data” folder in XAMP in Windows 10
I copied the data directory from mysql folder and uninstalled the XAMPP as the MariaDB was not starting. I installed XAMPP same version and backed up the data folder into mysql data directory. At ...
1
vote
1answer
44 views
Table is full even with innodb_file_per_table
I am trying to create an index to my table using alter query.
My my.cnf file
innodb_data_home_dir = /usr/local/mysql5/data
innodb_data_file_path = ibdata1:60021538816;ibdata2:300M;ibdata3:30000M;...
0
votes
1answer
73 views
Queries freezes in MariaDB
I have a problem where queries freezes sporadically during the day. I made a script monitoring the processlist and have detected a pattern where a slow query is running (different slow queries) and ...
0
votes
1answer
23 views
Pessimistic locking and isolation levels
How is pessimistic locking with read and write locks related to isolation levels?
Readers block writers and writers block readers. This behaviour is always constant irrespective of isolation level ...
0
votes
0answers
10 views
Checksum difference between table in MariaDb 10.4 and Mysql 8
We have a script that transfers databases between clusters. To make sure the data is still valid, we run a checksum on some of the tables. If those are ok, we mark transfer succesful.
The problem is ...
2
votes
0answers
62 views
MySQL/InnoDB cluster unable to reboot
I have a three-node MySQL/InnoDB cluster (version 8.0.25), that has been deployed by Juju/OpenStack a couple of month ago. The cluster has recently crashed and I unfortunately can't manage to bring it ...
2
votes
0answers
31 views
Is it worth using a JSON column type, even if it's only used for simple serialized strings or integers?
I have a table with an unidexed value column of VARCHAR(1024), and I've used my ORM to automatically convert it to and from JSON. The entire reason for this is I want it to be able to store any data ...
1
vote
0answers
63 views
Same query taking different amounts of time to execute
Table Details
SHOW CREATE TABLE foldertable
CREATE TABLE `foldertable` (
`serverToken` bigint(1) NOT NULL,
`folderName` varchar(255) NOT NULL,
`folderid` varchar(255) NOT NULL,
`RootFolderPath` ...
0
votes
1answer
37 views
Why transaction blocking simple insert in MySQL? [closed]
transaction Query
mysqli_begin_transaction($conn);
$sql = "UPDATE foldertable SET trashed = 1 WHERE serverToken = (SELECT serverToken from servertoken where userToken = ? limit 1) and
...
1
vote
1answer
41 views
slow disk with InnoDB page compression
I have a write-intensive MariaDB with both NVMe SSD and HDD disks. I recently enabled page compression (innodb_compression_default=ON). I encountered two problems:
The database gets slow after a ...
3
votes
1answer
207 views
InnoDB tables in an 8GB RAM server: Are these settings optimized?
I've been reading about how to optimize my database (MySQL 5.6.51) settings, and I've found an article suggesting to increase the key_buffer as much as possible.
Here's my my.cnf file:
slow_query_log =...
0
votes
0answers
16 views
Recover MySql data without Frm files
I was using Xampp out of the box and was doing some development work. Someone deleted my Xampp directory but full mysql data folder with ibdata1, iblog, db folders is present. Db folders only have ibd ...
0
votes
2answers
38 views
Does a NULL DATE use as much storage as a value?
I need to store a DATETIME value that may or may not have a date.
My options are:
| starts DATETIME |
|---------------------|
| 0000-00-00 12:38:06 |
| starts_date DATE | starts_time TIME |
|----...
0
votes
1answer
31 views
MySQL 8.0.23: Removeing huge '#sql-ib****.idb files?
I had a massive mysql database crash this morning and realized it was due to the server running out of disk space. I recently upgraded mysql to version 8.0.23 last week from 5.7.
As I was looking for ...
0
votes
1answer
29 views
Unexpected behaviour with InnoDB Transaction retry
This is going to be difficult to reproduce but hopefully someone can shed some light on my problem based on the logic involved. After having some intermittent issues with deadlocks during a ...
0
votes
0answers
32 views
MySQL server startup error
My Issue
After shutting down and starting my system up, my MySQL daemon gives me this in the logs (the pertinent snippet is as follows):
2021-05-03T12:48:32.458594Z 1 [ERROR] [MY-013183] [InnoDB] ...
0
votes
2answers
24 views
Why is MySQL InnoDB table copied despite using ALGORITHM=INPLACE for ALTER TABLE
I run an ALTER STATEMENT on an InnoDB table on a MySQL server version 5.7 that:
drop some foreign key constraints
drop some indexes (non unique)
add some columns (without index or foreign key ...
-1
votes
2answers
29 views
Is it possible to have a table and its index on different drives?
I'm running an Ubuntu MySQL server that has HDD and SSD internal drives. The HDD has larger capacity, but of course the SSD has much faster read/write speeds.
Is it possible for me to hold a (2.5 ...
0
votes
1answer
24 views
MySQL queries still slow after deleting bunch of records
I need some help with resolving some mysql issues. My website had been running slow since last week and after contacting my host I found out that some queries were taking too long mainly because of ...
0
votes
1answer
36 views
How to count the reads on index in MySQL
I have a table with index.
CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL,
P_NAME VARCHAR(55) NOT NULL,
P_MFGR CHAR(25) NOT NULL,
...
-1
votes
1answer
23 views
Httpd is blocked at 512 connections on dedicated Server
I'm having serious performance problems with Httpd on dedicated server. In traffic peaks httpd stucks on 512 connections and without any reason all connections are slow and files which are downloading ...
0
votes
0answers
3 views
Innodb cluster instance is changed during creation. The instance reports it to another one which I did not provided. What is the problem here?
MySQL db1:3306 ssl JS > cluster = dba.createCluster('prod_cluster');
A new InnoDB cluster will be created on instance 'db1:3306'.
Validating instance configuration at db1:3306...
This instance ...
1
vote
1answer
24 views
What schema does this mean 'mysql - schema's default character set: utf8'?
While upgrading mysql from 5.6 -> 5.7 -> 8.0.23 in step 5.7 -> 8.0.23 I got a warning:
The following objects use the utf8mb3 character set. It is recommended to convert them to use utf8mb4 ...
0
votes
1answer
66 views
MySQL Dead Lock on Delete Issue
I have recently updated my production code with the new query that is supposed to clean up orphaned records. It runs within the transaction as the last step.
And, as a result, I am seeing occasional ...
1
vote
1answer
31 views
ALTER innoDB table from row to page compression
My tables have been created with InnoDB row compression (ENGINE=InnoDB ROW_FORMAT=COMPRESSED). Not I am changing them to page compression. According to the official documentation of MariaDB, enabling ...
0
votes
1answer
42 views
Mysqld draining memory, using way more then assigned and not giving any back
This is a production server.
Whenever mysqld is restarted the memory consumption slowly builds up to about 75 to 90% of my total memory (while only 8 out of 32GB is allocated to mysqld).
Sometimes ...
0
votes
0answers
13 views
How to resolve the corrupted innodb table
I had a corrupted InnoDB MySql database after the customer downloading the wamp server on the computer again for wrong! so I worked on a lot and I got back most of the data, but one of the tables not ...
0
votes
1answer
58 views
MariaDB innodb bufferpool load taking long time
When I am restarting my MariaDB database engine it takes a long time to show 'innodb bufferpool load completed' depending on data volume. Is there a way to make it faster?
+----------------------------...
2
votes
0answers
63 views
Got fatal error 1236 from master when reading data from binary log
I am trying to do a MySQL failover, in some situations I endup with broken replication. Any idea what could be the cause of it ?
Background
Needed to perform a host updates on master host. Hence ...
1
vote
0answers
47 views
MariaDB service fails to start with InnoDB Space id error
I've been running MariaDB on a Raspberry Pi 4 (Raspbian 10 / buster) as the database server for my Webtrees and Owncloud sites. Recently, I noticed that the server wasn't looking right, and after a ...
1
vote
0answers
29 views
How can I tune my MySQL server to bulk import XML file (42 GB)?
I have a 40GB xml file which needs to be loaded into the db. Currently we are not sure about PK/FK relationships or schema details. So I am just loading them up without any indexes or keys. Every time ...
0
votes
0answers
13 views
Truncating partition locking whole table in mysql
Novice here.
I have a huge data that is generated every day and are stored in day wise partition. However, I have a cron that automatically deletes the old day (30 days ago). The query for deletion is ...
0
votes
0answers
22 views
MySQL 5.7 32-bit table is full on unix
As per the subject, I'm receiving an error on an InnoDB table: "table is full" when it reached 4GB the filesystem is far from being full so the only thing that I'm thinking right now is that ...
0
votes
1answer
16 views
Mysql InnoDB Full-Text Search IN BOOLEAN MODE Sort Rows In Order of Decreasing Relevance
In the official documentation of Boolean Full-Text Searches:
Boolean full-text searches have these characteristics:
They do not automatically sort rows in order of decreasing relevance.
However, ...
0
votes
1answer
38 views
Can large data deletion affect Innodb buffer pool performance? [duplicate]
At the beginning of this year we lunched a lot of massive data deletions (millions of rows per week) in our production database. Also, there is an automatic integration of new data (insert/updates) of ...
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
17 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
24 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 ...
1
vote
1answer
41 views
SQL: How to get all tables' columns & keys + the primary key?
Database Info
MySQL 8.0.22
InnoDB
The Goal
I'm trying to get all tables in a database, with their column definitions and foreign keys, and primary keys on their own row for each table group.
Given the ...
0
votes
0answers
20 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 ...
1
vote
1answer
235 views
mysqldump with --no-tablespaces on InnoDB with index?
Does mysqldump --no-tablespaces dumps index files of InnoDB?
I know that MyIsam uses separate index files *.MYI for index. So if one uses mysqldump with --no-tablespaces, does that mean index is not ...
0
votes
1answer
30 views
Optimize Table query causing Fatal error during execution with InnoDB table
I have a problem with OPTIMIZE TABLE query with one of my InnoDB database tables.
I have a database that contains several different tables and data is constantly accumulating in these tables. However ...
0
votes
1answer
24 views
Should I use MyISAM over InnoDB to save disk space?
I have a table that consists of an int primary key and text column and serving primarily in a "read" role. There are currently only about 40,000 rows, but in time, I expect there to be an ...
0
votes
1answer
74 views
Innodb primary key advice
We have a database table growing at a rate of ~20 million records per month, we're expecting this to double shortly.
The existing schema has a uuid4 as a primary key, and then two additional indexes ...
1
vote
1answer
30 views
Latest value of each GROUP with INNER JOIN
I'm using sensors to get temperature and humidity every X minutes.
I've one table for sensors and one table for their results.
tb_sensors:
tb_results:
I want to get the latest measurement for every ...
