Create table with character-set and collate error - FIX

Raven · Posted: Fri Jun 03, 2005 2:37 pm

The nuke.sql file contains this table create statement that is not working correctly.

Code:

CREATE TABLE nuke_bbsearch_wordlist (
word_text varchar(50) character set latin1 collate latin1_bin NOT NULL default '',
word_id mediumint(8) unsigned NOT NULL auto_increment,
word_common tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (word_text),
KEY word_id (word_id)
) ENGINE=MyISAM;

Here is my recommened fix.

Code:

CREATE TABLE nuke_bbsearch_wordlist (
word_text varchar(50) character set latin1 NOT NULL default '',
word_id mediumint(8) unsigned NOT NULL auto_increment,
word_common tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (word_text),
KEY word_id (word_id)
) ENGINE=MyISAM;

That way the system will default the collation to the proper correspondence to the character set.

php_papa · New Member Joined: Mar 17, 2005 Posts: 2

tight work bro... Smile

- PHP PAPA DOC

Guardian2003 · Site Admin Joined: Aug 28, 2003 Posts: 4642

Good catch!

ToolBox · New Member Joined: Mar 16, 2005 Posts: 24

world city lists are stupid.... I took down the table from the nuke.sql file. That's not useful, I found.

Aussie · New Member Joined: Oct 21, 2002 Posts: 7 Location: Denmark

Hi, I have encountered the problem of not being able to build the SQL tables using PHP My Admin.
My system is SQL version 3.23, PHP Version 4.3.10, Apache Version 1.3.29 and phpMyAdmin 2.6.3-pl1
I applied the fix as posted by Raven and still encounted a similar error as shown below.
Also, I had to change ENGINE to TYPE for all tables. I encountered an error using ENGINE.

CREATE TABLE nuke_bbsearch_wordlist(

word_text varchar( 50 ) CHARACTER SET latin1 NOT NULL default '',
word_id mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
word_common tinyint( 1 ) unsigned NOT NULL default '0',
PRIMARY KEY ( word_text ) ,
KEY word_id( word_id )
) TYPE = MYISAM
MySQL said:

#1064 - You have an error in your SQL syntax near 'character set latin1 NOT NULL default '',
word_id mediumint(8) unsigned NOT ' at line 29

Raven · Posted: Tue Aug 09, 2005 2:25 pm

I can run that query w/o any problem so my guess, w/o looking any further, is that your version of MySQL does not support it. Your version of MySQL is very, very old and you really should update to at least 4.0. In any event, you don't need CHARACTER SET latin1 so just delete it.

Aussie · New Member Joined: Oct 21, 2002 Posts: 7 Location: Denmark

Thanks for the bump in the right direction.
Ive upgraded MySQL to version 4.1.13a and everything ran so sweet.
Just as a side note, in the Requirments section of the Install doco, there is no mention of a MySQL version requirement, although there is mention of a PHP version required.
Maybe someone can point this out to the guys?
I do now have a new warning when I use PHP My Admin. I see the following error that has me stumped. Your thoughts would be most welcomed.

"The mbstring PHP extension was not found and you seem to be using a multibyte charset. Without the mbstring extension phpMyAdmin is unable to split strings correctly and it may result in unexpected results."

Note, I did not receive this message with my old version of MySQL.

kingware · New Member Joined: Sep 04, 2005 Posts: 5

I just got 7.8 and I did try this fix and still get same error:

CREATE TABLE nuke_authors(

aid varchar( 25 ) NOT NULL default '',
name varchar( 50 ) default NULL ,
url varchar( 255 ) NOT NULL default '',
email varchar( 255 ) NOT NULL default '',
pwd varchar( 40 ) default NULL ,
counter int( 11 ) NOT NULL default '0',
radminsuper tinyint( 1 ) NOT NULL default '1',
admlanguage varchar( 30 ) NOT NULL default '',
PRIMARY KEY ( aid ) ,
KEY aid( aid )
) ENGINE = MYISAM
MySQL said:

#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 'ENGINE=MyISAM' at line 27

my version is: MySQL 4.0.16 what can I do to fix this please?

Raven wrote:

The nuke.sql file contains this table create statement that is not working correctly.

Code:

CREATE TABLE nuke_bbsearch_wordlist (
word_text varchar(50) character set latin1 collate latin1_bin NOT NULL default '',
word_id mediumint(8) unsigned NOT NULL auto_increment,
word_common tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (word_text),
KEY word_id (word_id)
) ENGINE=MyISAM;

Here is my recommened fix.

Code:

CREATE TABLE nuke_bbsearch_wordlist (
word_text varchar(50) character set latin1 NOT NULL default '',
word_id mediumint(8) unsigned NOT NULL auto_increment,
word_common tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (word_text),
KEY word_id (word_id)
) ENGINE=MyISAM;

That way the system will default the collation to the proper correspondence to the character set.

Raven · Posted: Sun Sep 04, 2005 9:44 pm

Just remove this completely character set latin1

kingware · New Member Joined: Sep 04, 2005 Posts: 5

thanks for trying to help and I did take whole thing out still got same error:

CREATE TABLE nuke_authors(

aid varchar( 25 ) NOT NULL default '',
name varchar( 50 ) default NULL ,
url varchar( 255 ) NOT NULL default '',
email varchar( 255 ) NOT NULL default '',
pwd varchar( 40 ) default NULL ,
counter int( 11 ) NOT NULL default '0',
radminsuper tinyint( 1 ) NOT NULL default '1',
admlanguage varchar( 30 ) NOT NULL default '',
PRIMARY KEY ( aid ) ,
KEY aid( aid )
) ENGINE = MYISAM
MySQL said:

#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 'ENGINE=MyISAM' at line 27

Raven · Posted: Sun Sep 04, 2005 10:16 pm

Sorry! Misread your message. Change engine=myisam to TYPE=MyISAM;

kingware · New Member Joined: Sep 04, 2005 Posts: 5

did not work Sad

same error

CREATE TABLE nuke_authors(

aid varchar( 25 ) NOT NULL default '',
name varchar( 50 ) default NULL ,
url varchar( 255 ) NOT NULL default '',
email varchar( 255 ) NOT NULL default '',
pwd varchar( 40 ) default NULL ,
counter int( 11 ) NOT NULL default '0',
radminsuper tinyint( 1 ) NOT NULL default '1',
admlanguage varchar( 30 ) NOT NULL default '',
PRIMARY KEY ( aid ) ,
KEY aid( aid ) TYPE = MYISAM

MySQL said:

#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 'TYPE=MyISAM' at line 27

Raven wrote:

Sorry! Misread your message. Change engine=myisam to TYPE=MyISAM;

Raven · Posted: Sun Sep 04, 2005 10:42 pm

You haven't edited that right. It should look like

Code:

CREATE TABLE nuke_authors(
aid varchar( 25 ) NOT NULL default '',
name varchar( 50 ) default NULL ,
url varchar( 255 ) NOT NULL default '',
email varchar( 255 ) NOT NULL default '',
pwd varchar( 40 ) default NULL ,
counter int( 11 ) NOT NULL default '0',
radminsuper tinyint( 1 ) NOT NULL default '1',
admlanguage varchar( 30 ) NOT NULL default '',
PRIMARY KEY ( aid ) ,
KEY aid( aid )
) TYPE = MYISAM;

kingware · New Member Joined: Sep 04, 2005 Posts: 5

now I got this error Sad

CREATE TABLE nuke_autonews(

anid int( 11 ) NOT NULL AUTO_INCREMENT ,
catid int( 11 ) NOT NULL default '0',
aid varchar( 30 ) NOT NULL default '',
title varchar( 80 ) NOT NULL default '',
`time` varchar( 19 ) NOT NULL default '',
hometext text NOT NULL ,
bodytext text NOT NULL ,
topic int( 3 ) NOT NULL default '1',
informant varchar( 20 ) NOT NULL default '',
notes text NOT NULL ,
ihome int( 1 ) NOT NULL default '0',
alanguage varchar( 30 ) NOT NULL default '',
acomm int( 1 ) NOT NULL default '0',
associated text NOT NULL ,
PRIMARY KEY ( anid ) ,
KEY anid( anid )
) ENGINE = MYISAM
MySQL said:

#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 'ENGINE=MyISAM' at line 28

kingware · New Member Joined: Sep 04, 2005 Posts: 5

I got it working something I did Sad

Thanks for all help Raven.......

Guardian2003 · Site Admin Joined: Aug 28, 2003 Posts: 4642

You were missing the semi-colon at the end of that last example kingware
) ENGINE=MYISAM;

nipu · New Member Joined: Dec 13, 2005 Posts: 1

I need help i got the error:
There seems to be a problem with the MySQL server, sorry for the inconvenience.

We should be back shortly.

and ive done everything thts been instructed to me and even change tht peice of phpcode raven found out and it still doesnt work can any1 help me pm?

Raven · Posted: Tue Dec 13, 2005 10:32 pm

Most always that error means that you are not connecting to your database. The MySQL settings in config.php are not entirely correct. Also, turn $display_errors=TRUE; in config.php to see if there's any more information that is revealed. I would recommend that you forego 7.8 and use RavenNuke76. it's already secured.

sixonetonoffun · Posted: Wed Dec 14, 2005 9:21 am

I've found if I use phpmyadmin on my local win2k server to save an .sql file it adds the collation. If I use gzip compression it does not add the collation whats up with that?

Raven · Posted: Wed Dec 14, 2005 10:20 am

Well that's interesting! Have you tried it on a real server: *nix and Apache ROTFL

?

sixonetonoffun · Posted: Wed Dec 14, 2005 12:53 pm

Shush now ya know we can't all get away from the memory hogging blazing speed Bill's dug so deep into our pockets to provide us with. Sheesh and we complain about a little thing like a property tax increase every year. Whats capitalism coming to?