Table Installer Error - PN 726 (MySQL)

New Member Joined: Oct 26, 2003 Posts: 6

Everything was progressing perfectly until the last step. When I get this error:

Welcome to the KISSQ MySQL Table Installer!

Unable to Create the table.

Column 'pDate' is used with UNIQUE or INDEX but is not defined as NOT NULL

What have I done wrong? I did search the forum for an answer first.

Thank you,

Joe Dunn
joedunn@fiberpipe.net

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

I know many PN sites use this w/o any problem. What version of MySQL are you running? You should be able to have a NULL column on an index.

You could try this. In kissq-sql.php find this code

Code:

pDate varchar(10) default NULL

and modify it to

Code:

pDate varchar(10) default NOT NULL

Then try install again. Even if that works, the application may not because the application does not exepect to have a value for purchase date. I'm just trying to determine if it's your version of MySQL.

Posted: Wed Oct 29, 2003 7:40 pm

My server has MySQL 3.22.32

Joe Dunn

Posted: Wed Oct 29, 2003 8:27 pm

Well, they're on 3.23.58. Can you upgrade? You could look at the change logs on mysql.com to see if that was changed, which I expect it was.

Posted: Fri Oct 31, 2003 3:30 pm

Thankfully, a more intelligent friend of mine discovered and fixed the problem. I would email or attach the file but it does not seem possible on this forum. Here is the solution, at least for PN 726 users:

The problem was in the code used to create the tables - there was a syntax error.

This code: pDate varchar(10) default NULL,
Should be: pDate varchar(10) NOT NULL default '',

In the file kissq-sql.php you will want to replace line 130 with:

$query = "CREATE TABLE kissq_portfolio (UID varchar(16) NOT NULL default '', Symbol varchar(16) NOT NULL default '', nShares mediumint(9) default '0', pPrice decimal(11,5) unsigned NOT NULL default '0.00000', fees varchar(11) default NULL, uniqueid int(10) unsigned NOT NULL auto_increment, pDate varchar(10) NOT NULL default '', UNIQUE KEY UID (UID,Symbol,uniqueid), KEY pDate (pDate), KEY uniqueid (uniqueid)) TYPE=MyISAM";

Posted: Fri Oct 31, 2003 3:59 pm

As I said in my email to you, that may resolve the issue with your installation because of the version of either MySQL or phpMyAdmin. But the point is, is that it is supposed to be NULL and it works on hundreds of other installs. That's why I believe it may be your version of MySQL or phpMyAdmin.

I just tested this on my system using the original query

Code:




CREATE TABLE kissq_portfolio (UID varchar(16) NOT NULL default '', Symbol varchar(16) NOT NULL default '', nShares mediumint(9) default '0', pPrice decimal(11,5) unsigned NOT NULL default '0.00000', fees varchar(11) default NULL, uniqueid int(10) unsigned NOT NULL auto_increment, pDate varchar(10) default NULL, UNIQUE KEY UID (UID,Symbol,uniqueid), KEY pDate (pDate), KEY uniqueid (uniqueid)) TYPE=MyISAM;

and it installed perfectly

Code:




Your SQL-query has been executed successfully (Query took 0.0067 sec)


 


SQL-query : [Edit] [Create PHP Code]


CREATE TABLE kissq_portfolio( 


UID varchar( 16 ) NOT NULL default '',


Symbol varchar( 16 ) NOT NULL default '',


nShares mediumint( 9 ) default '0',


pPrice decimal( 11, 5 ) unsigned NOT NULL default '0.00000',


fees varchar( 11 ) default NULL ,


uniqueid int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,


pDate varchar( 10 ) default NULL ,


UNIQUE KEY UID( UID, Symbol, uniqueid ) ,


KEY pDate( pDate ) ,


KEY uniqueid( uniqueid ) 


) TYPE = MYISAM

So, the query and file are correct as distributed. You have altered it to work with your system, but it should not be required.