4

The following error message appeared after I installed a plugin on my website. I wonder what query to run in order to resolve this issue with MYSQL/PhpMyadmin?

Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `xf_trophy_category` DROP PRIMARY KEY , CHANGE `trophy_category_id`' at line 1 - library/Zend/Db/Statement/Mysqli.php:77

UPDATE.

This is the stack trace. I have asked the plugin developer but they are not working on it.

0 /home/admin/web/.com/public_html/library/Zend/Db/Statement.php(115):

Zend_Db_Statement_Mysqli->_prepare('ALTER IGNORE TA...')

1 /home/admin/web/.com/public_html/library/Zend/Db/Adapter/Mysqli.php(381):

Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Mysqli), 'ALTER IGNORE TA...')

2 /home/admin/web/.com/public_html/library/Zend/Db/Adapter/Abstract.php(478):

Zend_Db_Adapter_Mysqli->prepare('ALTER IGNORE TA...')

3 /home/admin/web/.com/public_html/library/Waindigo/Install/20150313.php(758):

Zend_Db_Adapter_Abstract->query('ALTER IGNORE TA...')

4 /home/admin/web/.com/public_html/library/Waindigo/Install/20150313.php(216):

Waindigo_Install->_makeTableChanges(Array)

5 /home/admin/web/.com/public_html/library/Waindigo/Install/20150313.php(88):

Waindigo_Install->_install(Array, Object(SimpleXMLElement))

6 /home/admin/web/.com/public_html/library/XenForo/Model/AddOn.php(214):

Waindigo_Install::install(false, Array, Object(SimpleXMLElement))

7 /home/admin/web/.com/public_html/library/XenForo/Model/AddOn.php(169):

XenForo_Model_AddOn->installAddOnXml(Object(SimpleXMLElement), false)

8 /home/admin/web/.com/public_html/library/XenForo/ControllerAdmin/AddOn.php(188):

XenForo_Model_AddOn->installAddOnXmlFromFile('/home/admin/tmp...')

9 /home/admin/web/.com/public_html/library/XenForo/FrontController.php(351):

XenForo_ControllerAdmin_AddOn->actionInstall()

10 /home/admin/web/.com/public_html/library/XenForo/FrontController.php(134):

XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))

11 /home/admin/web/.com/public_html/admin.php(13): XenForo_FrontController->run()

12 {main}

Leo Grace
  • 91
  • 6

2 Answers2

2

Either the plugin isn't compatible with MySQL 5.7, or whoever wrote the code is apparently unaware that ALTER IGNORE was deprecated in MySQL Server 5.6 and has been removed entirely in MySQL Server 5.7. It was always a bad idea, because it facilitated sloppy database management.

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only one row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

As of MySQL 5.6.17, the IGNORE clause is deprecated and its use generates a warning. IGNORE is removed in MySQL 5.7.

https://dev.mysql.com/doc/refman/5.6/en/alter-table.html

The error refers to the correct syntax to use near 'IGNORE ...', the server is telling you that IGNORE was encountered where it is not valid. Everything before that was parsed correctly, and everything after that may be right or may be wrong, but this can't be determined since something unexpected was encountered.

If I am reading your stack trace correctly, it's here:

3 
/home/admin/web/.com/public_html/library/Waindigo/Install/20150313.php(758):
Zend_Db_Adapter_Abstract->query('ALTER IGNORE TA...')

Remove the word IGNORE from Waindigo/Install/20150313.php line 758 so that it starts ALTER TABLE ....

Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427
  • 1
    Thank you very much. You were correct. I am using MySQL 5.7 and IGNORE became depreciated. I removed IGNORE from 'ALTER IGNORE TABLE' in 3 different lines in 20150313.php and the plugin installed fine. I want to you thank you for your time in helping me, I really appreciated it. – Leo Grace Jul 04 '17 at 19:53
0

If only a simple query would put things right, but in this case, I suspect that such a query is non-existent. The good news is that the error is pointing to the software plugin as having a problem. While there might be a bug in the plugin, studying the error message closely suggests a more likely scenario: a software conflict.

The OP states that the current setup uses PHPMyAdmin with MySQL. However, in the world of PHP, MySQL is now considered passe and users are urged to switch to MySQLi. The plugin from Zend (ZendFramework possibly?) expects MySQLi apparently per the error it raises:

Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error:

Per this discussion, you may need to adjust your PHP config file as follows provided you have installed mysqli:

$cfg['Servers'][$i]['extension'] = 'mysqli';

Otherwise you probably just need to install MySQLi to make the plugin work correctly.

The requirements for xerforo mention:

XenForo System Requirements PHP: 5.2.11+ MySQL: 5.0+ PHP extensions: MySQLi, GD (with JPEG support), PCRE, SPL, SimpleXML, DOM, JSON, iconv, ctype PHP safe_mode off

Lastly, you may wish to raise this issue with this community.

slevy1
  • 3,797
  • 2
  • 27
  • 33
  • This is the stack trace. I would ask the plugin developer but they have said they will not work on it. – Leo Grace Jul 01 '17 at 18:50
  • Do you have MySQLi properly installed or are you still using MySQL? – slevy1 Jul 01 '17 at 19:37
  • 1
    Thank you, I had installed MySQLi but the issue was because I was using 5.7 instead of 5.6 MySQL and IGNORE became depreciated as explained by @Michael - sqlbot. Thank you for your help! – Leo Grace Jul 04 '17 at 19:54