SQL Install Error while trying to create the tables

[hfremuth]

Script URL:
Version of script: 2.3
Hosting company: Hetzner (Germany
URL of phpinfo.php: http://www.cerioussw.com/phpinfo.php
URL of session_test.php: http://www.cerioussw.com/session_test.php

What terms did you try when SEARCHING for a solution:
CURRENT_TIMESTAMP, read the MySQL documentation, tried to put the first occurrence as 'CURRENT_TIMESTAMP' (found this in another post

Write your message below:
On the first try, I ran into an SQL error trying to create a table. I made the changes that you suggested in this post: viewtopic.php?f=13&t=2828&hilit=SQL+install#p12028

Now it creates the first five tables (up to hesk_categories, then I receive the following error:

Can't execute SQL: CREATE TABLE `hesk_logins` ( `ip` varchar(46) NOT NULL, `number` tinyint(3) unsigned NOT NULL DEFAULT '1', `last_attempt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY `ip` (`ip`) ) ENGINE=MyISAM
MySQL said:
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 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY `ip

Any ideas?

Thanks, Hans

[Klemen]

Try this:

1. delete all HESK tables that did install
2. open install/install.php in a text editor
3. add single quotes around every CURRENT_TIMESTAMP you find in the file (search and replace). So instead of CURRENT_TIMESTAMP it should say 'CURRENT_TIMESTAMP'
4. save, test and let me know if it helped

[hfremuth]

Hi Klemen,

thanks for the fast reply. I tried this, here is what I get:

Can't execute SQL: CREATE TABLE `hesk_kb_articles` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `catid` smallint(5) unsigned NOT NULL, `dt` timestamp NOT NULL DEFAULT ''CURRENT_TIMESTAMP'', `author` smallint(5) unsigned NOT NULL, `subject` varchar(255) NOT NULL, `content` text NOT NULL, `rating` float NOT NULL DEFAULT '0', `votes` mediumint(8) unsigned NOT NULL DEFAULT '0', `views` mediumint(8) unsigned NOT NULL DEFAULT '0', `type` enum('0','1','2') NOT NULL DEFAULT '0', `html` enum('0','1') NOT NULL DEFAULT '0', `art_order` smallint(5) unsigned NOT NULL DEFAULT '0', `history` text NOT NULL, `attachments` text NOT NULL, PRIMARY KEY (`id`), KEY `catid` (`catid`), KEY `type` (`type`), FULLTEXT KEY `subject` (`subject`,`content`) ) ENGINE=MyISAM
MySQL said:
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 'CURRENT_TIMESTAMP'', `author` smallint(5) unsigned NOT NULL,


The phpMyAdmin console says I am running MySQL client version: 5.0.51a - could that be the culprit?

Thanks,

Hans

[Klemen]

According to MySQL documentation the original code should work fine in 5.0.51 as well, but since that's not the case - can you try executing these three codes in phpMyAdmin and let me know if any of them works for you?

Code 1:

Code: Select all

CREATE TABLE `hesk_kb_articles` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `catid` smallint(5) unsigned NOT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `author` smallint(5) unsigned NOT NULL,
  `subject` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `rating` float NOT NULL DEFAULT '0',
  `votes` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `views` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `type` enum('0','1','2') NOT NULL DEFAULT '0',
  `html` enum('0','1') NOT NULL DEFAULT '0',
  `art_order` smallint(5) unsigned NOT NULL DEFAULT '0',
  `history` text NOT NULL,
  `attachments` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `catid` (`catid`),
  KEY `type` (`type`),
  FULLTEXT KEY `subject` (`subject`,`content`)
) ENGINE=MyISAM

Code 2:

Code: Select all

CREATE TABLE `hesk_kb_articles` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `catid` smallint(5) unsigned NOT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  `author` smallint(5) unsigned NOT NULL,
  `subject` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `rating` float NOT NULL DEFAULT '0',
  `votes` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `views` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `type` enum('0','1','2') NOT NULL DEFAULT '0',
  `html` enum('0','1') NOT NULL DEFAULT '0',
  `art_order` smallint(5) unsigned NOT NULL DEFAULT '0',
  `history` text NOT NULL,
  `attachments` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `catid` (`catid`),
  KEY `type` (`type`),
  FULLTEXT KEY `subject` (`subject`,`content`)
) ENGINE=MyISAM

Code 3:

Code: Select all

CREATE TABLE `hesk_kb_articles` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `catid` smallint(5) unsigned NOT NULL,
  `dt` timestamp NOT NULL DEFAULT 'CURRENT_TIMESTAMP',
  `author` smallint(5) unsigned NOT NULL,
  `subject` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `rating` float NOT NULL DEFAULT '0',
  `votes` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `views` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `type` enum('0','1','2') NOT NULL DEFAULT '0',
  `html` enum('0','1') NOT NULL DEFAULT '0',
  `art_order` smallint(5) unsigned NOT NULL DEFAULT '0',
  `history` text NOT NULL,
  `attachments` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `catid` (`catid`),
  KEY `type` (`type`),
  FULLTEXT KEY `subject` (`subject`,`content`)
) ENGINE=MyISAM

Code 4:

Code: Select all

CREATE TABLE `hesk_kb_articles` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `catid` smallint(5) unsigned NOT NULL,
  `dt` timestamp NOT NULL DEFAULT 'CURRENT_TIMESTAMP()',
  `author` smallint(5) unsigned NOT NULL,
  `subject` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `rating` float NOT NULL DEFAULT '0',
  `votes` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `views` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `type` enum('0','1','2') NOT NULL DEFAULT '0',
  `html` enum('0','1') NOT NULL DEFAULT '0',
  `art_order` smallint(5) unsigned NOT NULL DEFAULT '0',
  `history` text NOT NULL,
  `attachments` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `catid` (`catid`),
  KEY `type` (`type`),
  FULLTEXT KEY `subject` (`subject`,`content`)
) ENGINE=MyISAM

[Klemen]

P.s.: there was an old post with the same problem and MySQL version. Placing single quotes around 'CURRENT_TIMESTAMP' seemed to work:
viewtopic.php?f=13&t=2828&p=12028&hilit ... AMP#p12028

[hfremuth]

Hi Klemen,

Error when running code snippet #1:

#1064 - 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 'CURRENT_TIMESTAMP,
`author` smallint(5) unsigned NOT NULL,

Error when running code snippet #2:

#1064 - 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 'CURRENT_TIMESTAMP(),
`author` smallint(5) unsigned NOT NULL,

Both code snippet #3 and #4 run through without an error and create the table. Which one is best to use and are there any other parts of the code that I should patch if this is a general 5.0.51 issue ?

Thanks so much again,

Hans

[Klemen]

This means my initial instructions should work fine:

1. delete all HESK tables that did install
2. open install/install.php in a text editor
3. add single quotes around every CURRENT_TIMESTAMP you find in the file (search and replace). So instead of CURRENT_TIMESTAMP it should say 'CURRENT_TIMESTAMP'
4. save, test and let me know if it helped


Just make 101% sure that each CURRENT_TIMESTAMP has only single quotes round them. No double quotes, just single '

[hfremuth]

Hi Klemen,

I searched the MySQL docs and cam across a page that describes compatibility issues re 4.1 / 5.x - maybe that helps:

http://dev.mysql.com/doc/refman/5.0/en/ ... eries.html

Hans

[Klemen]

You have MySQL 5.x and it's a syntax error, not upgrading one, so the issue is somewhere else.

Did the tables install now?

[hfremuth]

I did this before, but for good measure I repeated the steps thoroughly. Same error:

Can't execute SQL: CREATE TABLE `hesk_logins` ( `ip` varchar(46) NOT NULL, `number` tinyint(3) unsigned NOT NULL DEFAULT '1', `last_attempt` timestamp NOT NULL DEFAULT 'CURRENT_TIMESTAMP' ON UPDATE 'CURRENT_TIMESTAMP', UNIQUE KEY `ip` (`ip`) ) ENGINE=MyISAM

MySQL said:
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 'ON UPDATE 'CURRENT_TIMESTAMP', UNIQUE KEY `ip` (`ip`) ) ENGIN


I tried to execute the code directly in phpMySql and received the same error.


Hans

[Klemen]

It just doesn't make sense and now the query string has moved to a different part of the SQL...

Can you update your MySQL?

Maybe a long shot, but does this file help (delete all tables before testing)?
http://www.phpjunkyard.com/extras/install.zip

[hfremuth]

Klemen

Again, thanks for your fast replay. This time, I scrapped the whole darn installation. I copied it with a different FTP client, in a different directory. I created a new DB on that server. After adjusting CHMOD rights, I then copied the install.php from your ZIP and kicked off the install again. Same error:

Can't execute SQL: CREATE TABLE `hesk_logins` ( `ip` varchar(46) NOT NULL, `number` tinyint(3) unsigned NOT NULL DEFAULT '1', `last_attempt` timestamp NOT NULL DEFAULT 'CURRENT_TIMESTAMP' ON UPDATE 'CURRENT_TIMESTAMP', UNIQUE KEY `ip` (`ip`) ) ENGINE=MyISAM
MySQL said:
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 'ON UPDATE 'CURRENT_TIMESTAMP', UNIQUE KEY `ip` (`ip`) ) ENGIN

This is very strange indeed. Any more ideas?


Hans

[hfremuth]

Klemen,

I think I know the solution! I contacted my ISV, and he told me that this server still runs on MqSQL4 - despite the fact that phpMySql reports it as 5.0.51a ....

I guess I will have to prepare to upgrade a bunch of sites on that server for v5 and then make the switch before I can install Hesk, correct?

Thanks again,

Hans

[Klemen]

You have one strange server, I give you that

If you are willing to give me access to the server to test a few things feel free to send me login info in a private message. I only need access to the help desk directory, so you can create a new FTP account with access to that directory only.