Page 1 of 1

1067: Invalid default value .......

Posted: Fri Jun 17, 2005 10:05 pm
by aktell
Hi,

The following message comes up if I run my script.(1067: Invalid default value for 'usernumber'). If I try it manual it's the same ???. It create the database but not the tables.

It seems strange that the username has the AUTO_INCREMENT, and not the userid ??? It's only one time possible to state AUTO_INCREMENT in a table isn't it ???.

Code: Select all

<?php
// create_db.php

include "common_db.inc";

$dbname = "sample_db";
$user_tablename = 'user';
$user_table_def = "usernumber MEDIUMINT(10) DEFAULT '0' NOT NULL AUTO_INCREMENT,";
$user_table_def .= "userid VARCHAR(8) BINARY NOT NULL,";
$user_table_def .= "userpassword VARCHAR(20) BINARY NOT NULL,";
$user_table_def .= "username VARCHAR(30) NOT NULL,";
$user_table_def .= "usercountry VARCHAR(50) NOT NULL,";
$user_table_def .= "useremail VARCHAR(50) NOT NULL,";
$user_table_def .= "userprofile TEXT NOT NULL,";
$user_table_def .= "registerdate DATE DEFAULT '0000-00-00' NOT NULL,";
$user_table_def .= "lastaccesstime TIMESTAMP(14),";
$user_table_def .= "PRIMARY KEY (userid),";
$user_table_def .= "UNIQUE usernumber (usernumber)";

$access_log_tablename = "access_log";
$access_log_table_def = "page VARCHAR(250) NOT NULL,";
$access_log_table_def .= "userid VARCHAR(8) BINARY NOT NULL,";
$access_log_table_def .= "visitcount MEDIUMINT(5) DEFAULT '0' NOT NULL,";
$access_log_table_def .= "accessdate TIMESTAMP(14),KEY page (page),";
$access_log_table_def .= "PRIMARY KEY (userid, page)";

$link_id = db_connect();
if(!$link_id) die(sql_error());

if(!mysql_query("CREATE DATABASE $dbname")) die(sql_error());

echo "Successfully created the $dbname database.<BR>";

if(!mysql_select_db($dbname)) die(sql_error());

if(!mysql_query("CREATE TABLE $user_tablename ($user_table_def)"))
                                                      die(sql_error());
// Create table access_log
if(!mysql_query("CREATE TABLE $access_log_tablename ($access_log_table_def)")) die(sql_error());

echo "Successfully created the $user_tablename and $access_log_tablename tables.";
?>

Code: Select all

<?php
// common_db.inc

$dbhost = 'localhost';
$dbusername = 'root';
$dbuserpassword = 'Celine';
$default_dbname = 'mysql';

$MYSQL_ERRNO = '';
$MYSQL_ERROR = '';

function db_connect() {
   global $dbhost, $dbusername, $dbuserpassword, $default_dbname;
   global $MYSQL_ERRNO, $MYSQL_ERROR;

   $link_id = mysql_connect($dbhost, $dbusername, $dbuserpassword);
   if(!$link_id) {
      $MYSQL_ERRNO = 0;
      $MYSQL_ERROR = "Connection failed to the host $dbhost.";
      return 0;
   }
   else if(empty($dbname) && !mysql_select_db($default_dbname)) {
      $MYSQL_ERRNO = mysql_errno();
      $MYSQL_ERROR = mysql_error();
      return 0;
   }
   else return $link_id;
}

function sql_error() {
   global $MYSQL_ERRNO, $MYSQL_ERROR;

   if(empty($MYSQL_ERROR)) {
      $MYSQL_ERRNO = mysql_errno();
      $MYSQL_ERROR = mysql_error();
   }
   return "$MYSQL_ERRNO: $MYSQL_ERROR";
}
?>
Thanks aktell

Posted: Sat Jun 18, 2005 5:11 am
by timvw
default 0 <-> default '0'

Posted: Sat Jun 18, 2005 5:22 am
by aktell
Hi there timvw,

I assume this

Code: Select all

default 0 <-> default '0'
is going istead of

Code: Select all

&#1111;DEFAULT '0'
into line 8 ??? It's just a lucky guess.

Thanks aktell