I am continuous getting above error in mysqld log. Please tell me what is this error about and how to solve it. I am using mysql 5.7 (not mariadb)
Mysql – how to resolve “InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table” in thesql
innodbMySQL
Related Solutions
I have very bad news for you.
You should not have deleted the ibdata1 file. Here is why:
ibdata1 contains four type of information:
- table metadata
- MVCC data
- data pages (with innodb_file_per_table enabled)
- index pages (with innodb_file_per_table enabled)
Each InnoDB table created has a numercial id assigned to it via some auto increment metadata feature to each ibd file. That internal tablespace id (ITSID) is embedded in the .ibd file. That number is checked against the list of ITSIDs maintained, guess where, ... ibdata1.
I also have very good news for you along with some bad news.
It is possible to reconstruct ibdata1 to have the correct ITSIDs but it takes work to do it. While I personally have not done procedure alone, I assisted a client at my employer's web hosting to do this. We figured this out together but since the client hosed ibdata1, I let him do most of the work (30 InnoDB tables).
To cut right to the chase, here is the article explaining what to do with reference to ITSID and how to massage ibdata1 into acknowledging the presence of the ITSID contained within the .ibd file.
I am sorry there is no quick-and-dirty method for recovering the .ibd file other than playing games with ITSIDs.
UPDATE 2011-10-17 06:19 EDT
Here is your original innodb configuration from your question:
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000
Please notice that innodb_log_file_size is there twice. Look carefully...
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G <----
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M <----
innodb_lock_wait_timeout = 18000
The last setting of innodb_log_file_size takes precedence. MySQL expected to start up with the log files being 5M. Your ib_logfile0 and ib_logfile1 were 1G when you tried to start up mysqld. It saw a size conflict and took the path of least resistance, which was to disable InnoDB. That's why InnoDB was missing from show engines;
. Mystery solved !!!
UPDATE 2011-10-17 11:07 EDT
The error message was deceptive because innodb_log_file_size was smaller than the log files (ib_logfile0 and ib_logfile1), which were 1G at the time. What's interesting is this: Corruption was reported because the file was expected to be 5M and the files were bigger. If the situation were reversed and the innodb log files were smaller than the declared size in my.cnf you should get something like this in the error log:
110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
In this example, the log files were already existing as 5M and the setting for innodb_log_file_size was bigger (in this case, 32M).
For this particular question, I blame MySQL (eh Oracle [still hate saying it]) for the inconsistent error message protocol.
You didn't say, in your question what the "file not present" message was, but I'm guessing it's this:
ERROR 1126 (HY000) at line 29: Can't open shared library 'lib_mysqludf_sys.so'
(errno: 0 /usr/local/mysql/lib/plugin/lib_mysqludf_sys.so: cannot open shared
object file: No such file or directory)
ERROR: unable to install the UDF
That makes sense, because you're telling gcc to write the file to /usr/lib/lib_mysqludf_sys.so... that's the -o
option in your command line. Give gcc the path MySQL is expecting and the rest of the installation should work.
gcc -m64 -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c \
-o /usr/local/mysql/lib/plugin/lib_mysqludf_sys.so \
-L/usr/lib/x86_64-linux-gnu/libstdc++.so.6
Also, whatever you're planning to do with this... don't say I didn't recommend against it. You're introducing both a potential security vulnerability and potentional performance and stability liability if these tools are deployed. This is not because there's anything wrong with the utility, but because of the amount of somewhat unorthodox functionality it opens up.
It's pretty cool, I admit. The lib_mysqludf_sys group of user-defined functions allow some interesting but easily-misappropriated capabilities, letting you spawn system commands and get either their generated output with sys_eval()
or their return value with sys_exec()
but really, I tend to suspect there's a reason why MySQL doesn't have these capabilities built in.
mysql> select sys_eval('df -k | grep xvda | tr -d "\n"') as cool_function_but_bad_idea;
+-----------------------------------------------------+
| cool_function_but_bad_idea |
+-----------------------------------------------------+
| /dev/xvda1 8256952 1216636 6620888 16% / |
+-----------------------------------------------------+
1 row in set (0.81 sec)
mysql> select sys_exec('/bin/false'), sys_exec('/bin/true');
+------------------------+-----------------------+
| sys_exec('/bin/false') | sys_exec('/bin/true') |
+------------------------+-----------------------+
| 256 | 0 |
+------------------------+-----------------------+
1 row in set (1.59 sec)
Update to address this error message:
gcc -Wall -m64 -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o
/usr/lib/lib_mysqludf_sys.so -fPIC gcc: error: lib_mysqludf_sys.c: No such
file or directory gcc: fatal error: no input files compilation terminated
The statement in question is directing "gcc" to compile a 64-bit version from the source code, which is found in lib_mysqludf_sys.c. This is one of the files you downloaded, so, to compile it, you need to be inside the directory where you downloaded the lib_mysqludf_sys files. This error suggests that you aren't. The download package isn't limited to 32 bit, it's just that it only builds a 32 bit version unless you use the gcc -m64 -fPIC ...
statement.
Related Question
- Mysql – Inserting into thesql table with archive engine “duplicate key” error
- MySQL Replication – Error 1236 ‘Found Old Binary Log Without GTIDs’ After Master Restart
- MySQL RDS – Fixing ‘Cannot Resolve Table Name’ Error
- Mysql – Query execution was interrupted, max_statement_time exceeded
- Mysql – How to fix `The table ‘/tmp/thesql/#sql_xxxxx’ is full`
- MySQL MariaDB – What is a Semaphore Wait?
- MySQL 8.0 RDS – Fixing Error 1022 Duplicate Key in Table
Best Answer
A guess: A
FULLTEXT
index on that table is corrupted.should rebuild the FT index, thereby fixing the problem.
If that is not sufficient, please provide
SHOW CREATE TABLE
; there may be some other clues there.