PP Pro MySQL Install ERROR

[gmatthews]

Support,

During the installation script of PP Pro, we are getting the following error related to MySQL:

==================================
Step 3: MySQL error reported: [CREATE TABLE IF NOT EXISTS pp_photoviews (photoid mediumint(9) NOT NULL default '0') TYPE=HEAP]
Result: 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 'TYPE=HEAP' at line 1
Database: vbforum

Step 3: MySQL error reported: [INSERT INTO pp_currency VALUES (23,'Netherlands Antilles, Guilders/Florins','ANG','ƒ', '2')]
Result: Data too long for column 'name' at row 1
Database: vbforum
==================================

Our environment is as follows:

* Windows Server 2008 R2
* MySQL 5.5.12
- DEFAULT CHARSET=utf8
- COLLATE=utf8_bin
* PHP 5.3.6
* VB 4.1.3 Patch Level 1

Please advise.

G.

[Chuck S]

Try turning off strict mode in mysql and see if that helps.

http://www.photopost.com/forum/1149965-post1.html

[gmatthews]

Support,

To begin, we dropped all pp records from the vbforum table to begin another fresh install attempt.

We disabled strict mode per your recommendation, restarted the MySQL server, verified the changes took effect, and then re-ran the pp install script.

Here is the current MySQL error being reported by the pp installer:

===============================================
Step 3: MySQL error reported: [CREATE TABLE IF NOT EXISTS pp_photoviews (photoid mediumint(9) NOT NULL default '0') TYPE=HEAP]
Result: 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 'TYPE=HEAP' at line 1
Database: vbforum
===============================================

Please review and advise.

G.

[gmatthews]

Support,

To test whether or not it was a collation issue, we dropped the pp tables again, changed the vbforum db collation (including all tables and columns) to general_ci (from bin), and re-ran the pp installer.

Same error and we just gave you.

G.

[gmatthews]

Support,

We are still unable to successfully install this application. Are you guys working on a solution?

G.

[gmatthews]

Support,

Do we need to post this issue somewhere else or email another support address?

G.

[Chuck S]

what is your list of issues what are the errors

You sure you set this line?

1. Replace in my.ini the current sql-mode line with sql-mode="MYSQL40"
2. Start MySQL with the option --sql-mode="MYSQL40"
3. Execute the query SET GLOBAL sql_mode='MYSQL40'

You would need to restart your server after any change. There should be no issues I am aware of but then again your using the latest version of mysql out there which no webhost does simply to let mysql work out there bugs.

You can send us information to access your site and server and we can take a look at your issues.

[gmatthews]

Support,

You've got to be kidding. We waited over five hours for you to simply ask us for information which we already provided? I am the sysadmin and am more than familiar with how MySQL works and how to adjust it's settings.

AGAIN:

We dropped all pp records from the vbforum table to begin another fresh install attempt.

We disabled strict mode per your recommendation, restarted the MySQL server, verified the changes took effect, and then re-ran the pp install script.

Here is the current MySQL error being reported by the pp installer:

===============================================
Step 3: MySQL error reported: [CREATE TABLE IF NOT EXISTS pp_photoviews (photoid mediumint(9) NOT NULL default '0') TYPE=HEAP]
Result: 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 'TYPE=HEAP' at line 1
Database: vbforum
===============================================

To test whether or not it was a collation issue, we dropped the pp tables again, changed the vbforum db collation (including all tables and columns) to general_ci (from bin), and re-ran the pp installer.

Same error and we just gave you.

The version of MySQL we are using is the current, STABLE release. Don't you test your software against these releases as they become available?

Regardless, we need you to either fix your code or provide us with some other sort of direction. We renewed our license yesterday and are still unable to install the current version.

G.

[Chuck S]

This is a weekend after normal business hours being friday and all. I last replied to you before I left the office as I leave the office at 4.

Anyway if your only getting ONE ERROR with the creation of that one table then I think your issue is with TYPE=HEAP. Newer mysqls actually want engine.

Search for TYPE=HEAP in the install.php and change it to ENGINE=HEAP and then run your install script. It is fine to leave the PP tables you will simply get a bunch of duplicate mysql errors they are fine just run through the install and you should be fine. In fact your present install if you went all the way through it should be fully functional.

If you do not want to reinstall and do a quick test if what you already installed will work then just simply create the missing table in mysql command line like so.

Content visible to verified customers only.

[gmatthews]

Support,

We'll go ahead and try that then. Out of curiosity, if you're aware that "newer mysqls actually want engine", then why not put that into your code, if at a minimum, via a conditional based on what version of sql the server has installed?

Just a suggestion.

G.

[Chuck S]

If you look at our install script all the rest have engine. It seems that one table was overlooked

[gmatthews]

Support,

No problem. It fixed the issue.

Thanks.

G.

[Chuck S]

Enjoy your weekend.