PHPJunkyard support forum

Posted: **Sat Feb 18, 2012 5:01 am**

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

Posted: **Sat Feb 18, 2012 10:00 am**

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

Posted: **Mon Feb 20, 2012 1:49 pm**

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

Posted: **Mon Feb 20, 2012 3:05 pm**

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

Posted: **Mon Feb 20, 2012 3:08 pm**

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

Posted: **Mon Feb 20, 2012 3:27 pm**

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

Posted: **Mon Feb 20, 2012 3:44 pm**

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 '

Posted: **Mon Feb 20, 2012 3:46 pm**

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

Posted: **Mon Feb 20, 2012 5:25 pm**

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?

Posted: **Mon Feb 20, 2012 7:26 pm**

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

Posted: **Mon Feb 20, 2012 7:33 pm**

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

Posted: **Mon Feb 20, 2012 9:17 pm**

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

Posted: **Tue Feb 21, 2012 3:41 pm**

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

Posted: **Tue Feb 21, 2012 3:43 pm**

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.

PHPJunkyard support forum

SQL Install Error while trying to create the tables

SQL Install Error while trying to create the tables

Re: SQL Install Error while trying to create the tables

Re: SQL Install Error while trying to create the tables

Re: SQL Install Error while trying to create the tables

Re: SQL Install Error while trying to create the tables

Re: SQL Install Error while trying to create the tables

Re: SQL Install Error while trying to create the tables

Re: SQL Install Error while trying to create the tables

Re: SQL Install Error while trying to create the tables

Re: SQL Install Error while trying to create the tables

Re: SQL Install Error while trying to create the tables

Re: SQL Install Error while trying to create the tables

Re: SQL Install Error while trying to create the tables

Re: SQL Install Error while trying to create the tables