Installation Errors Regarding TYPE=MyISAM

[rtwingfield]

I have just completed a very surly deinstall of conflicting PHP versions and MySQL.  I have PHP v5.2.14, MySQL client and server v5.5.5_1, Apache v2.2.4 running on FreeBSD v7.2.

Additionally, I have install a 3rd party application, WebCalendar, that requires PHP and MySQL, and seems to be working well.

Unfortunately, the SMF Installer is not happy - see the following examples of dozens of the same:


Line #22: 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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci' at line 16
Line #40: 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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci' at line 13
Line #63: 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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci' at line 18
Line #75: 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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci' at line 7
Line #151: Table 'CAP_Forums.CAP_board_permissions' doesn't exist
Line #180: 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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci' at line 23
Line #188: Table 'CAP_Forums.CAP_boards' doesn't exist
Line #207: 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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci' at line 13
Line #219: 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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci' at line 7
Line #336: Table 'CAP_Forums.CAP_calendar_holidays' doesn't exist
Line #396: Table 'CAP_Forums.CAP_calendar_holidays' doesn't exist
Line #409: 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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci' at line 7
Line #416: Table 'CAP_Forums.CAP_categories' doesn't exist
Line #427: 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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci' at line 5

What to do?

[Kill Em All]

What version of SMF are you trying to install?

Although that error is usually shown on old version so MySQL, however, looks like your running one of the latest versions.

[vbgamer45]

Change Type= to Engine= in the .sql file to fix this issue. This has been fixed for our next release issue with new mysql versions.

[rtwingfield]

OK, thanks for the quick response.

FYI, the SMF version came from downloaded smf_1-1-11_install.tar

. . .so I assume the version is SMF v1.1.11?

[vbgamer45]

Yeah it's an issue with the latest version of mysql I believe.

[rtwingfield]

The MySQL's v5.5.5_1 are the latest from the FreeBSD Ports.  The real pain was upgrading PHP from php5 to php52 (from the Ports).  I have to confess that this is my first dance with PHP . . .and so far, I think it should be spelled PHPig  .  Honestly, I've struggled for two weeks . . .two man-weeks trying to sort this this out. 

This morning, (regarding FreeBSD . . .anyway) I finally realized that make deinstall for php5 DOES NOT deinstall the alpha and omega of PHP'dom.  I had to manually find and deinstall all of additional ports and mods, etc., associated with PHP (both versions), before reinstalling fresh from the Ports. 

Before the housecleaning, PHP and MySql modules were being pulled from who-knows-where.  For example, I found two directories for the .so directories:

/usr/local/lib/php/20060613
/usr/local/lib/php/20060613-debug

. . .the mysql.so module was installed in the *-debug directory but not in the other.  Many other weird things, too many to go into (but I do have a "notes" sheet if you're interested.)

I'm in the process of making the recommended changes to the .sql file.   I'll report back (hopefully) a successful install.

Thanks again,
RW

[Kill Em All]

Good luck.

[rtwingfield]

Well . . .much better results, only one error:

Line #563: 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 '(14) /*!40102 NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP */,' at line 3

. . .regarding this function from the install_1-1.sql file

    554 CREATE TABLE {$db_prefix}log_online (
    555   session varchar(32) NOT NULL default '',
    556   logTime timestamp(14) /*!40102 NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP */,
    557   ID_MEMBER mediumint ( 8 ) unsigned NOT NULL default '0',
    558   ip int(10) unsigned NOT NULL default '0',
    559   url text NOT NULL,
    560   PRIMARY KEY (session),
    561   KEY logTime (logTime),
    562   KEY ID_MEMBER (ID_MEMBER)
    563 ) ENGINE=MyISAM;

May you can help me out here, too 

[vbgamer45]

Try removing " /*!40102 NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP */'

[rtwingfield]

. . .removed comment and results are:

Line #563: 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 '(14),
ID_MEMBER mediumint( 8 ) unsigned NOT NULL default '0',
ip int(10) uns' at line 3

I've googled logTime timestamp(14) and found some similar scenarios, but most seem to be associated with a damaged table, rather than during the initial setup.

I've found something about php not able to handle a time-stamp length of 14?

[rtwingfield]

From the MySQL 5.5 Reference Manual:

http://dev.mysql.com/doc/refman/5.5/en/timestamp.html

. . .I changed line #556 as follows:

logTime TIMESTAMP DEFAULT 0,


. . .and the database table creation (apparently) completed successfully.

[Kill Em All]

Interesting, I wonder why they changed it to require that now.

[rtwingfield]

If the world wasn't a moving target, then it wouldn't be interesting. 

OTTF,
RW

[Kill Em All]

haha, no, but it would make life easier.

Anyways, I'm going to mark this as solved.

edit, beat me to it. lol