All Questions
363
questions
0
votes
0
answers
94
views
While creating the MySQL Operator for Kubernetes I faced CrashLoopBackOff on pods
I am trying to create a MySQL Operator for Kubernetes, using the guide of MySQL official documentation(https://dev.mysql.com/doc/mysql-operator/en/).
While doing the steps mentioned in the Document, ...
0
votes
1
answer
791
views
Can’t start mysql - error.log constantly written
Recently my sites hosted on an Ubuntu server started to display "Error establishing a database connection".
Connecting to the server, it looks like mysql is being restarted constantly (3s ...
- 153
1
vote
0
answers
735
views
MariaDB Not Starting
I left it here, reverting to an outdated backup, as I needed to get the system up and running. If there are new answers and things to try, I will try so that the issue can be resolved. I shut down the ...
0
votes
0
answers
182
views
SQL Error (1114): The table 'global_priv' is full
I can no longer add users to my database. I only get the error:
SQL Error (1114): The table 'global_priv' is full
I researched this issue, and the my.ini config for innodb_data_file_path did not have ...
- 111
1
vote
1
answer
658
views
MySQL Corrupted After System Crash
I'm using MacOS Monterey 12.6.1 with Homebrew 3.6.6 and MariaDB 10.8.3
The OS crashed out of nowhere and rebooted itself. After the reboot I was unable to connect to the database: Can't connect to ...
- 11
0
votes
1
answer
635
views
MySQL + php due to high CPU usage app crashing
Just recently the application that I maintain started crashing, mysql's CPU usage is 100%... or more :)
The application itself is quite old and has a lot of slow queries but before blaming it I want ...
- 133
0
votes
1
answer
1k
views
ERROR 1034 (HY000): Incorrect key file for table 'mytable'; try to repair it
After upgrading mysql from 5.5 to 5.7, a compressed innodb database seems corrupted. I followed the method in this post to create a new database/table, drop the tablespace, copy the old .ibd file, ...
- 123
0
votes
1
answer
458
views
mysql crashes when accessing database after upgrade from 5.5 to 5.7
After upgrading mysql from 5.5 to 5.7, one of innodb databases seems corrupted.
mysqldump -u root -p mydatabase > mydatabase.sql Enter password:
mysqldump: Error: 'Lost connection to MySQL server ...
- 123
2
votes
4
answers
1k
views
MySQL keyring component (not plugin) not loading on server startup
Problem:
There is no indication mysqld is actually reading the server manifest and/or the keyring component configuration file. Any help would be greatly appreciated.
System Info:
Ubuntu 20.04
...
- 21
1
vote
1
answer
1k
views
How to reduce MySQL/InnoDB disk writes
I'm stress testing an application that uses MySQL. The bottleneck is the MySQL disks (separate for main data and redo log), which are written to quite much. atop indicates their usage is near 100%. If ...
- 2,626
0
votes
1
answer
1k
views
How to remove orphaned foreign key constraint in MySQL?
Using MySQL 5.5. I have a table for which I can't add a foreign key:
ALTER TABLE `SOURCE_TABLE`
ADD CONSTRAINT `ConstraintFK`
FOREIGN KEY (`otherTableID`)
REFERENCES `OTHER_TABLE` ...
- 212
0
votes
0
answers
294
views
mysql service crash (ubuntu server)
mysql service craches and the server becomes down everyday or every two days and I do not know the reason (I am using apache and innodb).
mysql error log:
2022-03-12 11:11:22 140142364764096 [Note] ...
- 101
0
votes
1
answer
3k
views
Failed to Import MySql database from phpMyAdmin as well as Command line
Well, I have tried to import small size database, it works. However, when I am about import a large database it shows the following error from phpMyadmin.
Static analysis:
1 errors were found during ...
0
votes
1
answer
1k
views
OPTIMIZE TABLE innodb file per table while system is running
I had an 800 GB Moodle mdl_logstore_standard_log.ibd database file.
I deleted around 900 million rows in 10-15 days.
The .idb file was around 800GB and still the same size after deletion.
I want to ...
0
votes
1
answer
502
views
Monitoring Byte Usage of Large MySQL InnoDB Buffer Pools?
I am trying to monitor the total number of used bytes in a MySQL 5.7 InnoDB Buffer Pool, that can go up to 100GB, using Innodb_buffer_pool_bytes_data but it seems that this status variable is a 32-bit ...
- 1
1
vote
0
answers
952
views
Alternate method for caching mysql query (query_cache) on version above 8.0
On mysql version 8+ the query_cache support was removed.
I know why it is not a friendly thing with InnoDB when considering the CPU.
But I've some databases which really need this support where user ...
- 33
3
votes
2
answers
1k
views
MySQL - Select queries 10x slower on Azure VM vs on-prem VM
We have been working on a project to migrate a MySQL database from an on-premise Linux server to a Windows VM on Azure (IaaS). (There's a specific reason because of which we have gone with the IaaS ...
0
votes
0
answers
562
views
Maria.db Service Doest Not Start in Ubuntu. InnoDB: The innodb_system data file 'ibdata1' must be writable
When I try to restart mariadb.service I get this error messages:
● mariadb.service - MariaDB 10.4.12 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: ...
- 99
1
vote
1
answer
4k
views
Optimizing MariaDB Performance
I need a different set of eyes checking my MariaDB settings. During peak load times, the CPU can hit upwards of 600% or more, and slows down Wordpress application. I have used MySQLTuner to tweak a ...
- 11
1
vote
0
answers
215
views
MySQL hangs on SELECT from table with blob fields
I managed to create a mysql InnoDB cluster with three nodes and a router everything works great but when i try to run a select query from the table below from a another server it freezes, but it works ...
- 77
-1
votes
1
answer
664
views
What is the best mysql configuration for mysql instance with a lot of databases and lot of tables inside?
I have a mysql database instance with more than 3000 database inside. Each database contains more than 200 tables. I have more than 100 gb of data in all these database at present. I am using windows ...
- 99
3
votes
1
answer
1k
views
MariaDB table has a metadata lock, but no blocking thread
I have a MariaDB 15.1 installed on a Debian system:
$ mariadb --version
mariadb Ver 15.1 Distrib 10.4.14-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
A script regularly loads data into ...
- 195
1
vote
2
answers
1k
views
Unclear why crash consistent backups are unsafe for MySQL
My understanding of the InnoDB engine is that the database is always recoverable after a crash, due to the internal architecture of the engine itself. That is, InnoDB can correctly recover from a ...
- 11
1
vote
0
answers
2k
views
mysql 8 only creating single log file instead of 2 (ib_logfile0)
I am having an issue with one of my mysql servers that has to do with the innodb logfiles. Even though it is configured to have 2 logfiles as normal, when I start up the server it only creates one of ...
1
vote
2
answers
2k
views
Will InnoDB or NDB cluster boost performance
We have fairly large MySQL database (about 35GB total) and topping about 900qps. Performance is not a big issue for now, but the project is growing constantly and I'd rather start thinking about ...
- 191
0
votes
2
answers
844
views
need help in understanding the deadlock
I got the deadlock below when running insert into statements. What's the possible cause of this and how you suggest to solve it?
LATEST DETECTED DEADLOCK
------------------------
2020-01-01 06:56:20 ...
- 143
0
votes
0
answers
263
views
why innodb use gen_clust_index instead of table lock when table have no any index?
My mysql version is 5.7. I try to test some case about innodb lock.I know:
a. query using table lock when table have no any index.
b. table have no any index, innodb will auto create gen_clust_index.
...
0
votes
0
answers
965
views
ERROR: InnoDB: Attempted to open a previously opened tablespace - how to avoid that?
I had no problems before, it was all fine, even when watchdog worked and restarted the device, everything worked fine. Until after one of such reboots something happened with partitioning, fixed that ...
- 281
1
vote
1
answer
2k
views
What could be causing the Google Cloud SQL MySQL replica instances to be stuck on a "System lock" state, causing huge replication delays?
Let me explain the problem in detail,
We have a Google cloud SQL setup(1 master, 1 failover replica and a read replica). From the last two days, we are facing replication delays on both the replica ...
- 13
0
votes
1
answer
1k
views
MariaDB 10.3 InnoDB random crash due to memory exhaust on VPS
after migrating to new VPS my server occassionally experiences database crashes. This may happen 1-2x in 2-3 days.
Server runs MariaDB 10.3 on CentOS 7 with nginx 1.16.
Virtual server has 1 GB memory,...
- 145
0
votes
2
answers
505
views
Fastest way to move Innodb database from one server to another
I understand I can use mysqldump or xtrabackup to move InnoDB from one db to another.
Considering I am okay with server down time, are there better way to move InnoDB database?
I have tried copying ...
- 6,101
3
votes
4
answers
2k
views
MySQL on VM, High IO Wait but low TPS
We are trying to figure out a strange situation on our MySQL installation on VM. We are on Mysql 5.7 and RHEL 7 with XFS.
We are observing when we run a few select queries the IOWait races up to 40-...
0
votes
1
answer
3k
views
AWS Freeable memory decreases from 16GB to 1GB
I have a MYSQL database hosted on RDS with 16GB RAM. I'm using an r4.large with 20GB General purpose SSD storage. We are using a microservice architecture and has 6 backend containers. So the request ...
- 287
4
votes
1
answer
8k
views
Multiple files found for the same tablespace ID
After a management over-sight, the hard disk utilization of the mysql server went to 100%. In panic, I shut down most of the services - including mysqld, which I guess caused this problem. ...
- 41
1
vote
0
answers
226
views
MariaDB 10.2 crash on innodb table corruption
I'm running MariaDB 10.2 on Windows and had some hard drive corruption on my server that messed up some innodb tables. Unfortunately this also affected backups, so I'm trying to dump the tables and ...
- 123
2
votes
1
answer
641
views
What is the maximum number of words allowed in a MariaDB InnoDB Full Text Search query
I recieved this error when performing a search with 746 words (it wasn't intentional someone pasted text into the wrong box).
HA_ERR_FTS_TOO_MANY_WORDS_IN_PHRASE: Too many words in a FTS phrase or ...
- 774
2
votes
1
answer
8k
views
Mysql high cpu usage (over 600%)
The server has 8core CPU and 22 GB of ram. Every day receives many requests from users, and recently a replication mechanism has been activated, with an implementation of a db master and a slave. Now ...
- 21
2
votes
1
answer
5k
views
MySQL server (mariadb) auto shutdown without overload
With old configs, my VPS (1 CPU, 1 GB RAM, 1 GB swap) works normally for almost 2 years. I didn't change anything from beginning.
But I have this issue several days ago, MySQL server automatically ...
- 21
0
votes
0
answers
248
views
InnoDB recovery from ibdata1 from disk failure
I started recovering data for a non-profit organization from a server that had a hard drive failure.
The mysqldump was not working, but I managed to tar the /var/lib/mysql on time and download via ...
1
vote
0
answers
2k
views
MySQL's innodb_buffer_pool_size doesn't seem to have any effect on RAM usage
(I have checked similar questions, none of them cover my situation)
I'm trying to increase MySQL 5.7 memory usage on my Ubuntu 16.04. Before my changes mysqld was using about 8% of RAM out of 8GB ...
- 121
0
votes
1
answer
622
views
MySQL perf bad on SSD-based server. Small tables. MySQL config? RAID?
I have a couple of servers in an industrial setting (air-gapped network) that do some fairly light telemetry gathering. We generate about 10GB of telemetry history in ~30 days.
All telemetry goes ...
- 31
0
votes
0
answers
417
views
mySQL shutdown "ERROR: 1050 Table 'plugin' already exists"
Have a mySQL DB (running on a Debian LAMP) that seems to do a maintenance shutdown or check each night, and fails with the following. The only "plugin" table is the empty one in the mysql db, and I'm ...
- 11
0
votes
0
answers
177
views
MySQL causing major I/O Lag
I have a mysql server (version 5.1) running Roundcube Webmail client on a plesk box (onyx). We have a few hundred users on it during the day. My hypervisor, Virtuozzo, they have been working with us ...
0
votes
1
answer
481
views
Why are partitioned MySQL InnoDB tables twice as big as unpartitioned ones?
I have two InnoDB tables identical in every respect except for one being partitioned, and the other - not:
DROP TABLE IF EXISTS `simple_table`;
CREATE TABLE `simple_table` (
`date` date NOT NULL,
...
- 173
0
votes
1
answer
1k
views
Mariadb tuning - slow writes
Background:
I have a a website I am migrating from a stand alone MariaDB server to a MariaDB galera cluster. Part of this had me convert the tables from a large number of MyISAM to InnoDB. For what it ...
- 33
-1
votes
1
answer
718
views
Cannot log in to the MySQL server after hosting company restarted my server
Today my hosting company decided to patch their systems due to Meltdown/Spectre and restart all servers.
I had my CentOS 7.2 server running for 280 days with no problems of any kind at all.
But now ...
- 99
3
votes
0
answers
479
views
MariaDB on Windows - InnoDB hang on table conversion to MyISAM
This is a bit of a strange one (and a big post, sorry), i am working with a customer who runs MariaDB on Windows (Server 2008 R2). They have several large MyISAM tables (up to 30 GB each, 200+ ...
- 226
0
votes
1
answer
332
views
How to decrese MySQL Innodb crash recovery time (force MySQL to do it faster)
I'm running MySQL 5.7 with 1GB of innodb_log_file_size on CentOS 7 x64 and I can't change the value of that parameter right now.
In the case of crashes, it takes about 20 minutes to recover, but ...
- 325
0
votes
0
answers
968
views
Occasional slow database queries caused by InnoDB
Update 2017-11-26
The problem seems to be related to InnoDB. The CI as well as the Wordpress installations are using InnoDB Engines while the rest is working with MyISAM. After I converted all MODx ...
1
vote
3
answers
3k
views
innodb-read-only prevent mysql replication? Table 'gtid_slave_pos' is read only
I set up a couple of maraidb v10.2.10 containers with the official docker image. Master can read/write and slave is read-only with this my.cnf.
[mysqld]
server_id=2
read_only=1
innodb_read_only=1
...
- 486