Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

Filter by
Sorted by
Tagged with
0 votes
0 answers
19 views

Mariadb is not truncating even Innodb Undo log Truncate is On

we have configured the below settings in our configuration file. --innodb_file_per_table=1 --innodb_undo_tablespaces=3 --innodb_undo_directory=/var/lib/mysql/ --innodb_undo_log_truncate=ON --...
chwajahat's user avatar
0 votes
0 answers
7 views

Temporary table information left over on InnoDB data dictionary after a db crash while a alter table was runnig

After a database crash while the system was running an alter table command I'm getting the following message during mysqld initialization: 2024-02-25 19:58:48 7fa2f9e84880 InnoDB: Operating system ...
Vaiks's user avatar
  • 1
0 votes
0 answers
17 views

Error mysqlimport loading to symlink table

Getting ERROR 1030 (HY000): Got error 168 - 'Unknown (generic) error from engine' when trying to import a .sql file produced via mysqldump. Running MySQL 8 on a Ubuntu 22.04 system. chris@chris-X1C6:/...
Chris's user avatar
  • 191
0 votes
1 answer
31 views

InnoDB High history list length grows indefinitely

I have MariaDB 10.11.5 slave on quite busy system (40k QPS, 50% UPDATES, slave replication ROW based). For some reason History list length cannot catch up with running transactions: SHOW ENGINE INNODB ...
forke's user avatar
  • 15
0 votes
3 answers
37 views

Mariadb threads blocking randomly

We have a mariadb instance hosted on Ubuntu 22.04. We are regularly seeing some threads getting stalled on relatively high activity tables(although the tables are not huge ones). Once stuck, it never ...
vamyip's user avatar
  • 121
0 votes
0 answers
30 views

ibdata1 file size still increases, after adding innodb_file_per_table=1 and deleting ibdata1 file

MariaDB ibdata1 file is increasing in size significantly. Even after deleting the ibdata1 file and adding the innodb_file_per_table=1 in my.cnf file. ibdata1 is again consuming the space and ...
chwajahat's user avatar
0 votes
0 answers
36 views

InnoDB I/O error on ALTER TABLE MODIFY

I'm trying to update some of our database's columns from utf8mb3 to utf8mb4, but while trying to execute the query I lose connection, because the server crashes. Are my buffers too small or is there ...
Ron Nabuurs's user avatar
0 votes
1 answer
18 views

MariaDB in the cloud....need some guidance

my plan is to move a mariadb into the cloud in germany. the clients will connect to it from 2 different office locations with 1gig/50mb up/dowload cable internet. Approx 10 clients will use the ...
kongootto's user avatar
1 vote
0 answers
69 views

MySQL using more memory than allocated in InnoDB

I have a Ubuntu 20.04 machine with 64 GB of RAM that is used both as a web server and for MySQL version 8.0.35. I am using InnoDB, but even if the innodb_buffer_pool_size variable is set to 128MB, the ...
marcos's user avatar
  • 11
1 vote
1 answer
79 views

Fatal error 1408 when installing mariadb-server / mariadb-upgrade

I recently upgraded my Debian 10 server running MySQL 5.7 to Debian 11 and MariaDB 10.3. That upgrade went smoothly. I then decided to upgrade to Debian 12 and MariaDB 10.11. While performing that ...
Curtis Spencer's user avatar
0 votes
0 answers
24 views

mysql clusterset vs replication

We run a medium sized app which may need the potential to grow but for a few years a single Mysql server would be sufficient. However we host on Azure which twice in the last 6 months has gone down ...
Antony's user avatar
  • 101
0 votes
1 answer
30 views

How do I prevent ibdata1 from continually growing by over 1GB per hour?

Like most DBAs I have two copies of my entire system, one running on a commercial website provider and one on my development system. Both are identical: the same options, the same software level, ...
James Cobban's user avatar
3 votes
0 answers
70 views

Why can't I combine CHECK constraint with ON UPDATE CASCADE?

Given table A: CREATE TABLE `A` ( `id` int NOT NULL, PRIMARY KEY (`id`) ); when I try to create this second table B: CREATE TABLE `B` ( `id` int NOT NULL, `val1` int, PRIMARY KEY (`id`), ...
Jonatan G's user avatar
-2 votes
1 answer
69 views

MariaDB InnoDb & Columnstore

I have a DB mariadb with innodb tables with one, two million records. I checked online and saw that Columnstore is faster. I created a Columnstore table in my DB and put all the contents of the Innodb ...
kali's user avatar
  • 7
1 vote
2 answers
59 views

Innodb_buffer_pool_pages_free - Can it be 1 or 0?

I have been researching this topic on whether the Innodb_buffer_pool_pages_free can be 0 or not and still has no impact on the site. Some DB admins are of the opinion that ...
Andy's user avatar
  • 13
0 votes
0 answers
104 views

MySql not starting on Xampp : InnoDB plugin not initializing due to missing MLOG checkpoints

Unexpectedly MySql server not starting. I checked error logs and found two errors: Aria recovery failed and InnoDB plugin not initializing. Aria issue is solved but InnoDB not initializing. The error ...
user avatar
0 votes
0 answers
19 views

MaySQL InnoDB cluster log fill on one server

in my mysqld.cnf file, I enabled logging via the following general_log = 1 general_log_file = /var/log/mysql/mysql.log It is a three-node cluster. I can see all the queries in the log ...
orddie's user avatar
  • 1
0 votes
1 answer
83 views

innodb_buffer_pool_size complications

I had some complications regarding innodb_buffer_pool_size. I read the official documentation, and I found out the formula innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. so if my ...
datascinalyst's user avatar
1 vote
3 answers
56 views

MySQL query optimisation

This is the MySQL table DDL: CREATE TABLE `users` ( `username` varchar(250) NOT NULL, `jid` varchar(250) NOT NULL, `nick` text NOT NULL, `subscription` char(1) NOT NULL, `ask` char(1) NOT ...
datascinalyst's user avatar
-1 votes
1 answer
270 views

How do i fix "InnoDB Plugin initialization aborted with error Generic Error"

i am running a Nextcloud Server on a Ubuntu 20.04 which was running fine. I wanted to upgrade the Nextcloud Version and make a backup before. This is what i did: plugged in a external harddrive (/dev/...
user avatar
0 votes
2 answers
57 views

INSERTing millions of files into a table

I archive millions of XML files (1-100MB each) in a table with the structure of CREATE TABLE Data ( ID int(11) unsigned NOT NULL, XML longtext COMPRESSED, PRIMARY KEY(ID) ) ENGINE=Aria ...
Googlebot's user avatar
  • 4,521
0 votes
1 answer
26 views

MySQL Data not flushing to disk

Lets start with what I did... I copied a table via PHPmyadmin operations/ copy table/(temp1) structure and data. nothing should make any changes to this copy.phpmyadmin shows 1185199 entries I copy ...
tek-dragon's user avatar
0 votes
0 answers
32 views

MySql keep loosing tablespace id for random tables

In one of our databases we are seeing more and more frequent errors where the table space id of an innodb file is lost. We are running mysql 8.0.31. The error message in the log file is: [ERROR] [MY-...
Ingar Pedersen's user avatar
0 votes
1 answer
124 views

Mysql deadlock on non unique index

I'm getting the deadlock on MySQL v8.0.32 which is very strange for me because of the following: I'm trying to insert the data: parent-child record (users, user_roles) - each transactions inserts new ...
Andrey Khataev's user avatar
0 votes
1 answer
37 views

If I UPDATE one column in one row in the MySQL InnoDB engine, do I have to wait for the lock to be released when I SELECT another column?

For example, suppose you run the queries below. UPDATE `user` SET point=30 WHERE id=1; SELECT nickname FROM `user` WHERE id=1; In this case, should the second SELECT query wait for the lock from the ...
Woojin Lee's user avatar
0 votes
2 answers
38 views

What is the correct index on a large dataset with group by?

I've been managing this very large data set for mlb data in mysql for awhile and am trying to rework the indexes to increase efficiency. I have several instances I need to manage where I need to sort ...
Keith's user avatar
  • 185
0 votes
1 answer
74 views

Getting deadlocks very frequently in Mysql insert query

=2023-09-15 15:16:35 0x1530b7bc17002023-09-15 15:16:35 0x1530b7bc1700 *** (1) TRANSACTION: TRANSACTION 55060584750, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 8 lock struct(s), ...
Paras Sahu's user avatar
1 vote
0 answers
41 views

Wrong query optimization plan with InnoDB

After switching DB engine from MyIsam to InnoDB on MariaDB 10.3.39 we noticed endless queries and huge disk space usage. The problem are some queries that are wrong optimized with the new engine. ...
LateAtNight's user avatar
1 vote
1 answer
423 views

Restoring old raw backup in MariaDB 10.8.1+ without ib_logfile0

I tried to restore an old raw backup of databases (whole datadir) in MariaDB 10.11.5 but it didn't booted up. After that I found following lines in log: [ERROR] InnoDB: File ./ib_logfile0 was not ...
VirtualVoid's user avatar
0 votes
0 answers
133 views

How to alter table with ALGORITHM=INSTANT after dropping fulltext index

I have a table lets say my_table with the following fields: id (int) title (varchar) content (text) And a FULLTEXT index on title and on title + content Let's call those indexes index_text_1 and ...
Sander Visser's user avatar
0 votes
1 answer
133 views

which way to increase InnoDB Buffer after more resources given

Sorry for re-treading the constant discussions of Buffer size and efficiently, we have a standalone server for a vendor system that has a database of 10GB on MySQL 8.0.28 the server itself is a bit ...
LeccyFez's user avatar
0 votes
0 answers
370 views

MySQL 8.0.3 database not starting after power outage

I have a MySQL 8.0.3 database, which I can't start, after a power outage. Unfortunately I have been very stupid and made any backups (I know you are more than welcome to laugh at me). The database is ...
Kim Josh's user avatar
0 votes
1 answer
44 views

Advice on WooCommerce DB and really slow WC core admin queries

Thanks for reading! So I'm kind of banging my head against a wall but I firstly want to say that I think this is a DB setup problem rather than the code as even when stripped back to bare bones WC the ...
Devon Developer's user avatar
0 votes
1 answer
609 views

Reclaiming Disk Space for MariaDB Table

I'm facing a disk space issue with a MariaDB database and would appreciate your insights on resolving it. Here's the scenario: MariaDB version: 10.3 OS: Ubuntu 20.04 Engine: Innodb Table name: '...
Tamer Mukayev's user avatar
0 votes
1 answer
109 views

idb file randomly being deleted/going missing - how to troubleshoot?

Full disclosure, I'm a website owner, not a professional DBA. I hope this question is OK. I'm trying to discover why an idb file is, seemingly randomly at a rate of around once every 4-7 days, ...
Jamie's user avatar
  • 1
0 votes
0 answers
57 views

It's possible switchover a innodb cluster instance as primary?

If I am in topologyMode Single-Primary, it's possible to switchover the primary instance between machines in the cluster? I did not find anything in the documentation. Ie: in my lab I have: topology&...
Astora's user avatar
  • 703
0 votes
2 answers
155 views

Why does Mariadb fail to run, claiming the redo log is ancient if I have been running a newer version for a long time?

After a power outage, my Mariadb 10.5.10 server fails to start, saying this: Jun 23 17:35:38 heater mysqld[1260999]: 2023-06-23 17:35:38 0 [Note] /usr/sbin/mysqld (mysqld 10.5.10-MariaDB) starting as ...
Johannes Ernst's user avatar
0 votes
1 answer
124 views

MariaDB Power Outage Issue

We have a desktop application on Windows OS that installs with MariaDB server 10.4.10. Sometimes the client loses power and after restarting of PC MariaDB database becomes corrupted and Windows Server ...
WhoKnows's user avatar
  • 103
0 votes
0 answers
33 views

InnoDB deadlocks with IX locks

Table schema `id` varchar(255) NOT NULL, `created_time` bigint DEFAULT NULL, `updated_time` bigint DEFAULT NULL, `tdi` varchar(255) NOT NULL, `gid` varchar(255) NOT NULL, `auth_id` varchar(...
uzumas's user avatar
  • 101
0 votes
1 answer
208 views

Joining Columns with different character set

I am joining two tables on varchar columns having CHARACTER SET utf8 COLLATE utf8_bin and one with CHARACTER SET latin1. Indexing is not working when i am joining these two tables on this column, I am ...
Manish Kumar's user avatar
3 votes
3 answers
962 views

MySQL 5.7.42, Gradual memory growth over time, memory leak?

I'm currently running a MySQL server (version 5.7.42) in production, and I've noticed a gradual increase in memory usage throughout the day. I suspect there might be a memory leak issue causing this ...
George's user avatar
  • 31
0 votes
0 answers
215 views

"Table doesn't exist in engine" for innoDB tables after recovery attempt

I'm trying to restore a database from an old server, the person in charge lost their data backups, and only mysql folder remains. The database uses both MyISAM and InnoDB engines. For the InnoDB ...
stock omatic's user avatar
0 votes
2 answers
313 views

Deadlock while concurrently updating multiple rows on the same table

We have a database where concurrent kafka streams are updating multiple rows at the same time, We are seeing deadlock occur in very few cases and I am not entirely sure what's causing it --------------...
owl's user avatar
  • 3
-1 votes
1 answer
740 views

XAMPP Control Panel mysql shutdown unexpectedly

When I try to start on mysql from xampp control panel it says: 20:52:08 [mysql] Status change detected: stopped 20:52:08 [mysql] Error: MySQL shutdown unexpectedly. 20:52:08 [mysql] This may ...
Akshat Kr. Pandey's user avatar
0 votes
0 answers
224 views

[MariaDB 10.3 -> MariaDB 10.6][InnoDB] Duplicate entry '...' for key 'unique_index'

Recently we upgraded mariadb from 10.3 to 10.6 and we are facing a weird problem. Although there is a unique key on 3 columns (table_hash,table_id,meta_key) there are multiple records with same data. ...
Gioggiolo's user avatar
0 votes
1 answer
50 views

MySQL gets slow before optimizing table

We have a custom made webshop built on the Laravel PHP framework which processes, on a busy day, 7000 orders a day. Since all orders are sent to a third-party point of sale system via an API, the ...
Rogier's user avatar
  • 1
0 votes
0 answers
160 views

LOAD DATA INFILE query never completes (yet data is loaded), mysqlimport rejects

Using different mysql clients, my LOAD DATA INFILE query never completes, but I can see the data has been successfully loaded. Where can I look to see why the driver gets stuck? My complete command: ...
Hein du Plessis's user avatar
1 vote
2 answers
292 views

Tuning MYSQL 8.0.32 for my server

EDIT: User @mustaccio let me acknowledge that I must present the problem that motivates the need for the tuning: There are querys running faster in an older server, less powerful, MySQL Version 5.7....
lio89's user avatar
  • 21
0 votes
0 answers
93 views

What kind of lock does INSERT ... ON DUPLICATE KEY UPDATE obtain when the UPDATE is a no-op?

I am working on an apparent performance problem possibly involving row lock contention, and I'm seeking to understand whether INSERT ... ON DUPLICATE KEY UPDATE Foo=Foo (with a no-op in the UPDATE ...
cbmanica's user avatar
  • 101
3 votes
2 answers
226 views

Why innodb locks index till the end?

Why a query BETWEEN two IDs locks index till the end? UPDATE elem SET c='' WHERE id BETWEEN 2 AND 5; -- locks id>5 for some reason The database is MySQL 8.0.33 GA. Here's the full example, taken ...
John Smith's user avatar

1
2 3 4 5
45