I am dealing with a very large set of databases that are all innodb. I've enountered this on mysql restart too many times for my comfort: ibdata files do not match the log sequence number But I've clearly watched mysql shutdown properly just before the restart when that message happens. Then it "repairs" right up to the original sequence number with nothing lost. What is the best approach to deal with and fix this permanently? Using Percona with innodb_file_per_table=1 Example log: InnoDB: Initializing buffer pool, size = 80.0G InnoDB: Completed initialization of buffer pool InnoDB: Highest supported file format is Barracuda. InnoDB: The log sequence numbers 475575972691 and 475575972691 in ibdata files do not match the log sequence number 925369860131 in the ib_logfiles! InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages InnoDB: from the doublewrite buffer... InnoDB: 128 rollback segment(s) are active. InnoDB: Waiting for purge to start InnoDB: Percona XtraDB started; log sequence number 925369860131 Note how the final log sequence number now matches what it thought was wrong in the first place, so there was 100% recovery? So why is the log sequence not being properly written to ibd? Is it possible shutdown is incomplete somehow? Thank you for any advice. ps. I always wonder if I should be asking this on serverfault or here? Is it okay I asked here?

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, disappearing from our web server (the associated frm file remains intact). This file is related to a well-supported Wordpress plugin but we seem to be the only user affected by this issue. Unfortunately I see nothing in the mysql error logs and neither the plugin developer nor our web host can explain what's going on. Is there any auditing/logging I can enable to give me some clue as to why this may be happening? We're running MySQL Version 10.6.14-MariaDB. Many thanks.

Trying to restore a WAMP database with just .frm and .ibd files. The uninstall of WAMP left .ibd and .frm files, but no ibdata1 file. I have read several posts here to figure out the process for doing this. I read this post: https://dba.stackexchange.com/questions/57120/recover-mysql-database-from-data-folder-without-ibdata1-from-ibd-files/142836?noredirect=1#comment270943_142836 . I also got advice to bring in a ibdata1 file in from a fresh WAMP MySQL installation . (My old WAMP was a 5.6.12, this new one installed 5.6.17). I ran the .frm files through a utility at TwinDB: https://recovery.twindb.com/ which I understand helps get the schema restored. The instructions are this: to run commands through a MySQL utility after copying .ibd files to the data level folder, then moving the .ibd files back to the database level. I assume the .frm files that were run through TwinDB's utility are to replace the existing .frm files in my database folder. There are eleven .ibd files to run through this process. My hope when I'm done is that my 5.6.12 installation will be legitimate, and that WAMP's tray menu will recognize my 5.6.12 installation once again. I have several years of archived posts there that I want to re-use again online. I downloaded the MySQL Utilities (1.5.6) and installed them. I've read here that I should be using mysql.exe. But that file is not in the folder that was installed. I'm stuck. There are 24 .exe files in that folder. The READ ME file gives me no instructions. Which utility am I supposed to use if mysql.exe is not in there? Why didn't the installation of the Utilities create an app that opens up all these tools? My database is located at C:\wamp\bin\mysql\mysql5.6.12\data\wordpress. I know I need that path to log in. What is my command to log into this server path so I can run my commands and restore these connections between my eleven .ibd files? My database user is root. There is no password since this is an internal server. I've read that I need to log in to mysql so I can run these commands to put the database info in the directory with the ibdata file so that everything can "connect" again. The .ibd files have all the data for the internal WordPress site. Their files sizes are clearly indicative that all the data is there.

I tried to deploy MySQL deployment with Kubernetes, having three replicas which are accessing the same storage(PVC). Here is the configuration

apiVersion: v1
kind: PersistentVolume
metadata:
  name: mysql-pv
  labels:
    type: local
spec:
  persistentVolumeReclaimPolicy: Retain
  capacity:
    storage: 1Gi
  accessModes:
  - ReadWriteMany
  hostPath:
    path: "/mnt/data"
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pvc
spec:
  accessModes:
  - ReadWriteMany
  resources:
    requests:
      storage: 1Gi
---
apiVersion: v1
kind: Service
metadata:
  name: mysql-service
spec:
  type: NodePort
  ports:
  - protocol: TCP
    port: 3307
    targetPort: 3306
    nodePort: 30091
  selector:
    app: mysql
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql
spec:
  replicas: 2
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - image: mysql:latest
        name: mysql
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: pwd
        ports:
        - containerPort: 3306
        volumeMounts:
        - name: mysql-storage
          mountPath: /var/lib/mysql
      volumes:
      - name: mysql-storage
        persistentVolumeClaim:
          claimName: mysql-pvc

When you apply this configuration file kubectl apply -f file_name.yaml, you can create three pods, which are accessing the same storage for the databases. When you check the pods' status kubectl get pods, you can see only one pod becomes running and others be in a CrashLoop state. What is happening is, when creating more than one instance to use a common storage, only one instance can acquire the lock of the ibdata1 file. That's why only one pod becomes healthy and others in CrashLoop.( you can see this using kubectl logs pod-name). What I want is, 1. Can I release the lock of the ibdata file and use the storage for all the pods?(this mostly can not, because of consistency issues) 2. If not, how can I create the proposed idea?( accessing a single storage/volume using multiple pod instances)? 3. Would you suggest other ideas to achieve accessing a single storage using multiple pod instances? Your answers and help are welcomed.

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 exhausting the disk space. Advise any change or suggestion

I found that My MariaDB's ibdata file keep increasing. So, I've searched for this, and found that innodb_file_per_table should be set as 1. but, my DBMS's configuration has already set as 1; Why ibdata file size keep increasing and what else should I do for this. These below are my dbms info.

DBMS: MariaDB
engine: InnoDB Engine
version: 10.3

** my.cnf **
innodb_file_per_table=ON
transaction-isolation=READ-COMMITTED

Is it possible to purge old records from an ibdata1 file without taking down the database attached to it? Records that have been deleted from the database are still present when I scan ibdata1 for strings. I am somewhat aware of how the ibdata1 file works and realize that those portions of the file will probably be overwritten in time, but I need to zero out or otherwise remove the old data so it is no longer stored on the server in any form. Is there anyway to do this without taking down the database?

I implemented this solution for big database, only keep data for 14 days (remove data daily based on date). When I run optimize TABLE table1; The size supposes to decrease but in my case, it increases. /etc/my.cnf contains:

innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

ll --block-size=GB /var/lib/mysql/mydb/ print:

-rw-r-----. 1 mysql mysql  17GB Nov 20 15:45 table1.ibd
-rw-r-----. 1 mysql mysql   5GB Nov 20 15:30 table2.ibd
-rw-r-----. 1 mysql mysql 181GB Nov 20 15:29 table3.ibd

I am unable to find the solution alone, so I decided to ask a question after two or three days of exeperimentation and research. I had a mysql (MariaDB) install on a server under Ubuntu 20.04 with a few databases (nextcloud, wordpress) and had a regular backup scheduled with mariabackup run as a cronjob. I wanted to reinstalled the server recently so I made backups of everything manually, and copied to a remote location with rsync -avx backup_folder user@remotehost:/backups and also on my own laptop (my tables aren't that big). I then (after an upgrade failure) reinstalled the original setup with Ubuntu 20.04 and mariadb-server and mariabackup from the official repos, but when I run mariabackup --prepare --target-dir=/path/to/my/backup/ the procedure fails with output :

mariabackup based on MariaDB server 10.3.34-MariaDB debian-linux-gnu (x86_64)
 2022-07-19 13:06:53 cd to /home/simon/db.bak/
 2022-07-19 13:06:53 open files limit requested 0, set to 1024
 2022-07-19 13:06:53 This target seems to be already prepared.
 2022-07-19 13:06:53 mariabackup: using the following InnoDB configuration for recovery:
 2022-07-19 13:06:53 innodb_data_home_dir = .
 2022-07-19 13:06:53 innodb_data_file_path = ibdata1:12M:autoextend
 2022-07-19 13:06:53 innodb_log_group_home_dir = .
 2022-07-19 13:06:53 InnoDB: Using Linux native AIO
 2022-07-19 13:06:53 Starting InnoDB instance for recovery.
 2022-07-19 13:06:53 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2022-07-19 13:06:53 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-07-19 13:06:53 0 [Note] InnoDB: Uses event mutexes
2022-07-19 13:06:53 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-07-19 13:06:53 0 [Note] InnoDB: Number of pools: 1
2022-07-19 13:06:53 0 [Note] InnoDB: Using SSE2 crc32 instructions
2022-07-19 13:06:53 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
2022-07-19 13:06:53 0 [Note] InnoDB: Completed initialization of buffer pool
2022-07-19 13:06:53 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2022-07-19 13:06:53 0 [ERROR] InnoDB: Invalid flags 0x15 in ./ibdata1
2022-07-19 13:06:53 0 [ERROR] InnoDB: Plugin initialization aborted with error Data structure corruption
 FATAL ERROR: 2022-07-19 13:06:53 mariabackup: innodb_init() returned 39 (Data structure corruption).

Now what really surprises me, is that the same thing would happen on an Ubuntu 20.04 server virtual machine running on my laptop with kvm, but not on my laptop itself running Fedora 26 for which the output is a success :

mariabackup based on MariaDB server 10.5.16-MariaDB Linux (x86_64)
 2022-07-19 15:07:24 cd to /home/sfouilleul/db.bak/
 2022-07-19 15:07:24 open files limit requested 0, set to 1024
 2022-07-19 15:07:24 This target seems to be already prepared.
 2022-07-19 15:07:24 mariabackup: using the following InnoDB configuration for recovery:
 2022-07-19 15:07:24 innodb_data_home_dir = .
 2022-07-19 15:07:24 innodb_data_file_path = ibdata1:12M:autoextend
 2022-07-19 15:07:24 innodb_log_group_home_dir = .
 2022-07-19 15:07:24 InnoDB: Using Linux native AIO
 2022-07-19 15:07:24 Starting InnoDB instance for recovery.
 2022-07-19 15:07:24 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2022-07-19 15:07:24 0 [Note] InnoDB: Uses event mutexes
2022-07-19 15:07:24 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-07-19 15:07:24 0 [Note] InnoDB: Number of pools: 1
2022-07-19 15:07:24 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2022-07-19 15:07:24 0 [Note] InnoDB: Using Linux native AIO
2022-07-19 15:07:24 0 [Note] InnoDB: Initializing buffer pool, total size = 104857600, chunk size = 104857600
2022-07-19 15:07:24 0 [Note] InnoDB: Completed initialization of buffer pool
 2022-07-19 15:07:24 Last binlog file , position 0
 2022-07-19 15:07:24 completed OK!

I am stucked at the invalid flags 0x15 in the ibdata1 file because I don't know exactly what this is, and absolutely not why it happens, and even less how to fix it. I also tried to performe that mariabackup --prepare step on erlier database backups, but all failed in the same manner. I was ultimately trying to convert my nextcloud database from mysql to postgresql so I could, maybe, import it in a Nextcloud AIO Docker setup. Needless to say that the conversion also failed, but that's another topic. If somebody could give me a hint of why this is happening... Thanks to all of you!

I'm having a Galera cluster (3 nodes, but currently using only one), MariaDB 10.0 Galera. However, my ibdata1 is growing HUGE (although using inno_file_per_table=1). Now i want to "shrink" it by using mysqldump (see here ) but this is a really time consuming task for my load (around 12-15 hours, tried it with an equal server) The database size we are talking about is around 150G (and ibdata1 grew to 163G over years due to unclever decisions). Now my main goal is to reduce downtime, say: Only the time it takes to create the dump. My idea was to use mysqldump as SST method, but: How long is the cluster blocked? Only the time it takes to create the mysqldump or the time it takes to create + recover on the joiner? Do you have another idea to do this with a small downtime?

One of our MySQL servers died but somehow we were able to recover all the files from it. This server was a slave at one time however we stopped it being a slave so we could use it for housekeeping tasks and reports, etc. Can someone tell me how to recover with this? I now have these files and a backup of my.cnf ll /root/home/recovered/mysql/ -rw-r----- 1 root root 117 Jun 1 09:30 rabney-relay-bin.000001 -rw-r----- 1 root root 26 Jun 1 09:30 rabney-relay-bin.index -rw-r----- 1 root root 113055367168 Jun 1 10:42 ibdata1 -rw-r----- 1 root root 1073741824 Jun 1 09:33 ib_logfile0 -rw-r----- 1 root root 1073741824 Jun 1 09:33 ib_logfile1 drwx------ 2 root root 4096 Jun 1 09:30 mysql drwx------ 2 root root 16384 Jun 1 09:30 production_copy -rw-r----- 1 root root 3253518648 Jun 1 09:32 slowquery.log drwx------ 2 root root 4096 Jun 1 09:30 test I've been reading articles on Google regarding this but not sure how to do it yet.

I was importing an old SQL into existing schema. But the MySQL service was stopped suddenly. Now I'm not able to start the service. It seems that the ibdata1 file is somehow corrupted. Here are the logs I got: 2017-12-08T17:56:24.927955Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-12-08T17:56:24.929802Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-12-08T17:56:24.942256Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-12-08T17:56:24.961017Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-12-08T17:56:24.962154Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 16417491586
2017-12-08T17:56:24.962169Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 16417493278
2017-12-08T17:56:24.963433Z 0 [Note] InnoDB: Database was not shutdown normally!
2017-12-08T17:56:24.963443Z 0 [Note] InnoDB: Starting crash recovery.
2017-12-08T17:56:24.996387Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
2017-12-08T17:56:25.501869Z 0 [Note] InnoDB: Apply batch completed
2017-12-08T17:56:25.626479Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-12-08T17:56:25.626508Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-12-08T17:56:25.626637Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-12-08T17:56:25.649790Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-12-08T17:56:25.650460Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-12-08T17:56:25.650469Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-12-08T17:56:25.650670Z 0 [Note] InnoDB: Waiting for purge to start
2017-12-08T17:56:25.655806Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2017-12-08T17:56:25.655825Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2017-12-08 12:56:25 0x7000049f8000 InnoDB: Assertion failure in thread 123145379872768 in file fil0fil.cc line 896
InnoDB: Failing assertion: success
InnoDB: We intentionally generate a memory trap.

InnoDB: Submit a detailed bug report to http://bugs.mysql.com .
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
17:56:25 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68218 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f9792000000
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7000049f7e20 thread_stack 0x40000
0 mysqld 0x000000010caa714a my_print_stacktrace + 58
1 mysqld 0x000000010ca039d0 handle_fatal_signal + 688
2 libsystem_platform.dylib 0x00007fffb8d52b3a _sigtramp + 26
3 mysqld 0x000000010d3bac17 _ZZN10binary_log4Uuid9to_stringEPKhPcE11byte_to_hex + 41879
4 libsystem_c.dylib 0x00007fffb8bd7420 abort + 129
5 mysqld 0x000000010ccfd9c1 _Z23ut_dbg_assertion_failedPKcS0_m + 161
6 mysqld 0x000000010cb623bc _ZL18fil_node_open_fileP10fil_node_t + 3948
7 mysqld 0x000000010cb6d962 _ZL23fil_node_prepare_for_ioP10fil_node_tP12fil_system_tP11fil_space_t + 194
8 mysqld 0x000000010cb6e418 _Z6fil_ioRK9IORequestbRK9page_id_tRK11page_size_tmmPvS8_ + 1096
9 mysqld 0x000000010cb27073 _ZL17buf_read_page_lowP7dberr_tbmmRK9page_id_tRK11page_size_tb + 387
10 mysqld 0x000000010cb27288 _Z13buf_read_pageRK9page_id_tRK11page_size_t + 56
11 mysqld 0x000000010cb0bb7a _Z16buf_page_get_genRK9page_id_tRK11page_size_tmP11buf_block_tmPKcmP5mtr_tb + 1290
12 mysqld 0x000000010cae91e8 _Z27btr_cur_search_to_nth_levelP12dict_index_tmPK8dtuple_t15page_cur_mode_tmP9btr_cur_tmPKcmP5mtr_t + 4008
13 mysqld 0x000000010cc97824 _Z21row_search_on_row_refP10btr_pcur_tmPK12dict_table_tPK8dtuple_tP5mtr_t + 164
14 mysqld 0x000000010cc956a6 _ZL34row_purge_remove_clust_if_poss_lowP12purge_node_tm + 438
15 mysqld 0x000000010cc93a68 _Z14row_purge_stepP9que_thr_t + 1944
16 mysqld 0x000000010cc55919 _Z15que_run_threadsP9que_thr_t + 937
17 mysqld 0x000000010ccd9c4d _Z9trx_purgemmb + 2973
18 mysqld 0x000000010ccc2d57 srv_purge_coordinator_thread + 2871
19 libsystem_pthread.dylib 0x00007fffb8d5c93b _pthread_body + 180
20 libsystem_pthread.dylib 0x00007fffb8d5c887 _pthread_body + 0
21 libsystem_pthread.dylib 0x00007fffb8d5c08d thread_start + 13

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 0
Status: NOT_KILLED
I've tried several ways, such as adding the innodb_force_recovery in my.cnf and killing all of the MySQL process. None of them works. Could anyone help?

I need to analyze the client's data and for that requirement client has provided us only ibdata1 file having size around 150MB. I am not aware of table structure and number of tables as of now. After some research I found that I will need .frm files in order to recreate the tables. I have below questions: 1. Is there a way I can restore the database with only ibdata1 file? 2. Shall I need any other files apart from .frm files and ibdata1 file in order to restore the database? 3. Suppose I can restore the database using just ibdata1 file or I get .frm files in future, Do I need to install a version that matches the client's MySQL version? Thank you!

I'm trying to run a tiny (~100-200 MB) database in-memory, but ibdata1 grows out of control. Situation: MariaDB is running on a resource constrained HW (Raspberry, 1GB RAM), storing non-critical sensory data (like room temperature). The real file system is an SD card, so frequent writes destroy it. Storing data in-memory would be a good option. What is not a solution: - Memory storage engine can't handle TEXT columns - Aria storage engine can't handle foreign keys - MyRocks storage engine is not available for 32-bit platforms - SQLite ///:memory: can't handle the "load", throws uncountable 'cannot commit - no transaction is active' errors What I've tried: - using tmpfs for datadir and tmpdir - tweaking InnoDB to minimal resource usage - disabling doublewrite buffering, disabling change buffering, using READ-UNCOMMITTED isolation See [configuration](https://github.com/lmagyar/homeassistant-addon-mariadb-inmemory/blob/master/mariadb/rootfs/etc/my.cnf.d/mariadb-server.cnf) But when I purge old data, the DELETE causes ibdata1 to grow nearly to the 100% size of the table I delete 10-15% of the rows from. My **guess** is that Rollbacks Segments and Undo Tablespace is still very active, but I can't "disable" MVCC altogether. **How to configure InnoDB/MariaDB to not fill ibdata1 with (rollback/undo?) data that is useless in an in-memory database?**

Related to a previous issue , I'm trying to recover some data from existing ib* files from a crashed server. The database version is 5.1.69, so quite old. One of the responses was to install MySQL 5.5, because it should still be able to import 5.1, but even that is too old on current systems, and one just runs into ever deeper compatibility issues. “NBK” in the same issue suggested in a comment to use a docker file with an older version of MySQL. I decided to try this approach. I was able to install docker and pull the vsamov/mysql-5.1.73 image, but I'm stuck now at how to get the ib* files into the docker container. I think I need the image to run so that it has a container ID, but if it's running, then the ib* files are locked, so I'm not sure how to proceed. If anyone has experience in this, or can provide a reference, it would be much appreciated.

I've followed all of the instructions for enabling the one file per table in MySQL (v8, on Ubuntu 20): - Added innodb_file_per_table=ON - Restarted MySQL - Dumped all databases, one by one. However, when I import the databases back, everything is written to ibdata1 file. But, When I create a brand new DB via MySQL Client, and add a random table to it, the data is written to its own folder + table.ibd file. Any idea what I could be doing wrong? Few troubleshooting steps I tried: - Reboot - show variables like 'innodb_file_per_table'; (Value = ON)

I'm facing a strange behavior with MySQL 8 running in docker: it has a constant 7MB/s write to ibdata1 when no queries are being made (monitored with netdata and [filetop-bcc](https://github.com/iovisor/bcc/blob/master/tools/filetop.py)) . As soon as I start inserting some data, the write drops, and resume as soon as I stop the inserting process. I've looked at several other threads about ibdata1, but none seems to give a reasonable explanation of why there's such behavior. Here's a graph of disk usage. You can see at first and at last the constant disk write, and in the middle the small amount of time an application inserting/selecting data was running. SHOW FULL PROCESSLIST lists no on-going transaction SELECT * FROM information_schema.innodb_trx returns an empty result My config:

[mysqld]
max_binlog_size = 1048576
innodb_sort_buffer_size = 20971520
sync_binlog = 0
join_buffer_size = 1G
innodb_buffer_pool_size = 22G
innodb_log_file_size = 3G
innodb_buffer_pool_instances = 23
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2

Does anyone have a clue about what could be going on ? MySQL is giving me quite a high disk PSI (from 3 to 10%), even if my computer has a nvme SSD. EDIT: Apparently it has to do with InnoDB Buffer Pool Flush Pages Requests, which is around 200 requests/s on idle, and drops to 0 as soon as the process connects to mysql. However, I don't understand what it means

I've tried multiple different suggestions from other questions on here, with no luck... This is a MySQL database running version 5.6.41, using the InnoDB engine, and the instance is running out of a Docker image. (https://hub.docker.com/_/mysql?tab=tags&page=1&name=5.6.41) The image has my main database files (ibd and frm files) symlinked outside of the instance. Here's the error:

InnoDB: Error: trying to access page number 0 in space 842,
InnoDB: space name /,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
2019-09-12 14:59:26 7f2fe639e700  InnoDB: Assertion failure in thread 139843702744832 in file fil0fil.cc line 5666`

I attempted to use the innodb_force_recovery flag at all values (including 5-6). The error above happens when I use the mysql command line tool, and enter DROP TABLE .. The main problem is I cannot access the database to even attempt any of these recovery options any more. This likely started happening following a host (MacOS 10.13.6) system crash which caused an unexpected shutdown of my Docker container during file write. I believe either my ibdata or ib_logfiles are corrupt, but I can't figure out how to recover the database without wiping everything clean and recovering from a mysqldump (which may not even work). I don't care about the data in this table, I can recover data from a development/testing MySQL server. I'd just like to not go through the hassle of starting everything from scratch. Here are a few other stackex q's I've looked at: https://dba.stackexchange.com/questions/183200/mysql-cant-delete-table https://dba.stackexchange.com/questions/183862/trying-to-solve-outside-tablespace-bounds-mysql-error-but-recovering-table-wi Any suggestions? (If you need more info, like my variable settings/my.cnf files, or database structure info, please ask)

After restoring from a percona-xtrabackup without a preparation step, ib_logfile0 and ib_logfile1 were not present in the restore data. Therefor during startup the following message is observed: 140223 04:10:24 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 140223 04:46:51 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 140223 4:46:51 InnoDB: Initializing buffer pool, size = 18.0G 140223 4:46:54 InnoDB: Completed initialization of buffer pool 140223 4:46:54 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 140223 4:46:54 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 140223 4:46:54 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 140223 4:46:55 InnoDB: Error: page 7 log sequence number 45 3368182252 InnoDB: is in the future! Current system log sequence number 42 975484940. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html How can I fix this?

I'm running mysql 5.6. For reasons entirely my own fault, I've got some missing ibd that make up partitions for several tables. When I try to mysql I get the following messages for the missing tables/partitions: > 2018-10-24 01:41:06 3895 [ERROR] InnoDB: Table DATABASE/TABLE/PARTITION in the InnoDB data dictionary has tablespace id 16080, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary. I don't really want those partitions back as they contain old data that we don't want any more. Is there any way to tell mysql that those partitions are gone, or to remove them from mysql's expected partition list. Or is there any other way to fix this without a restore?