XF 1.5 Fresh installation, new DB, MySQLi prepare error

[Christian Emmerich]

Hello!

I am new to XenForo and made my purchase just today. I plan to enroll the XenForo platform to an existing web server, based on a Debian system. Environment consists of

• Apache/2.4.12
• PHP/5.6.8
  
• MySQL 5.0.51a

During the installation process, after entering SQL credentials and database name, this comes up:

Server Error
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 'USING BTREE, KEY view_date (view_date) USING BTREE ) ENGINE = MEMORY CHARAC' at line 11

1. Zend_Db_Statement_Mysqli->_prepare() in Zend/Db/Statement.php at line 115
2. Zend_Db_Statement->__construct() in Zend/Db/Adapter/Mysqli.php at line 381
3. Zend_Db_Adapter_Mysqli->prepare() in Zend/Db/Adapter/Abstract.php at line 478
4. Zend_Db_Adapter_Abstract->query() in XenForo/Install/Model/Install.php at line 255
5. XenForo_Install_Model_Install->createApplicationTables() in XenForo/Install/Controller/Install.php at line 216
6. XenForo_Install_Controller_Install->actionStep2() in XenForo/FrontController.php at line 351
7. XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 134
8. XenForo_FrontController->run() in /opt/lampp/htdocs/initiative/install/index.php at line 18

Any help is appreciated!

Kind regards

Christian Emmerich

 

[Mike]

I believe we've run into this once before -- though the fact that it was just once is very surprising. I think this is actually an issue with running MySQL 5.0 versus anything newer. It looks like MySQL is very inconsistent with this particular bit of syntax.

Any particular reason you're using MySQL 5.0? It hasn't had a release since 2012 (and your version is from 2008). Using a newer version would be the simplest resolution I think. Alternatively, a small file modification can be done (as this is just a quirk with MySQL's syntax).

 

[Christian Emmerich]

Good Morning Mike!

Thanks for your reply. As I am stuck with this MySQL version (at least for the moment), this file modifications you mentioned are the thing I would go for. What has to be done exactly?

Kind regards

Christian

 

[Mike]

In library/XenForo/Install/Data/MySql.php, you need to change 2 lines:

PRIMARY KEY (user_id, unique_key) USING BTREE,
KEY view_date (view_date) USING BTREE

to:

PRIMARY KEY USING BTREE (user_id, unique_key),
KEY view_date USING BTREE (view_date)

This should resolve the problem.

 

[Christian Emmerich]

This fixed it.

Thank you so much!