Xenforo Enable Delayed Insert SQL Queries

disabled for me

 

not needed with the improvements in MySQL performance especially in MariaDB 10+ and Xenforo's current usage patterns if you have InnoDB tables in use

see INSERT DELAYED - MariaDB Knowledge Base and MySQL :: MySQL 5.6 Reference Manual :: 13.2.5.2 INSERT DELAYED Syntax

 

much better explanation

 

Is this for Xenforo ? or other web script/app ? What version of MySQL were you coming from (source mysql backup data's MySQL version) ? How are you backing up and importing the mysql data ? exact commands used ?

Googled - https://mariadb.com/kb/en/library/insert-delayed/

Centmin Mod MariaDB has default max_delayed_threads = 20 so is not disabled at MariaDB MySQL global level

Code (Text):

mysqladmin var | grep max_delayed | tr -s ' '
| max_delayed_threads | 20 |

insert delayed only work with MyISAM, MEMORY, ARCHIVE and BLACKHOLE tables but MySQL 5.6.6 already deprecated insert delayed. But Centmin Mod MariaDB MySQL and Xenforo default to creating InnoDB tables by default, so you'd want to disable Xenforo insert delay option.

check your delayed insert counts in mysql status output

Code (Text):

mysqladmin ext | grep -i delayed
| Delayed_errors                                               | 0                                      |
| Delayed_insert_threads                                       | 0                                      |
| Delayed_writes                                               | 0                                      |
| Not_flushed_delayed_rows                                     | 0                                      |

But the MySQL docs left something out MySQL Bugs: #5777: InnoDB do not support INSERT DELAYED that after MySQL 5.6.6 and MariaDB 10, deprecating delayed inserts means just a warning but message you see and the query still goes through replacing DELAYED INSERT as normal INSERT. So unless your import abruptly stops, importing to completion with just that warning message should be ok I think.

MySQL Bugs: #5777: InnoDB do not support INSERT DELAYED

MySQL :: MySQL 5.6 Reference Manual :: 13.2.5.3 INSERT DELAYED Syntax

 

@Tien Nguyen Van if you used phpmyadmin to export your mysql database backup from previous host, ensure you set sql_delayed = false before your export a backup phpMyAdmin set 'INSERT DELAYED statements' not checked

 

Or use mysqldump in SSH command to backup database to sql file as delayed inserts is disabled by default for backups

Code (Text):

 mysqldump --help | grep insert
  -c, --complete-insert
                      Use complete insert statements.
  --delayed-insert    Insert rows with INSERT DELAYED.
  -e, --extended-insert
                      (Defaults to on; use --skip-extended-insert to disable.)
  --insert-ignore     Insert rows with INSERT IGNORE.
                      --quick, --extended-insert, --lock-tables, --set-charset,
                      --create-options, --quick, --extended-insert,
complete-insert                   FALSE
delayed-insert                    FALSE
extended-insert                   TRUE
insert-ignore                     FALSE

http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

backup where /path/to/ is directory you want to save sql backup file to

Code (Text):

mysqldump -u mysqlusername -p mysqldatabasename > /path/to/mysqldatabasename_backup_date.sql

restore

Code (Text):

mysql -u mysqlusername -p mysqldatabasename < /path/to/mysqldatabasename_backup_date.sql

 

on old server try using locate command to find it

Code (Text):

updatedb
locate config.inc.php

 

You're welcome. FYI, my reply in your intro thread has alot of juicy info and tips to get the most out of Centmin Mod LEMP - guaranteed you'd learn something new everyday Introduction