List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 13 2002 10:09pm
Subject:Re: AUTO_INCREMENT= ?
View as plain text  
At 18:03 -0400 5/13/02, Amer Neely wrote:
>Paul DuBois wrote:
>>
>>  At 17:53 -0400 5/13/02, Amer Neely wrote:
>>  >Paul DuBois wrote:
>>  >  > >>  >>  AN> The values I'm inserting for
>>  >>  >>  >>  AN> UserID are all 'null' (without quotes).
>>  >>  >>  >>
>>  >>  >>  >>  How did you inserted values? Manually or from a
> file?
>>  >>  >>  >
>>  >>  >>  >As I indicated above, from a file.
>>  >>  >>
>>  >>  >>  Let's see a sample of the file.  Without that, we're just
> guessing.
>>  >>  >>  mysql won't interpret INSERT statements differently in batch
> mode
>>  >>  >>  than in interactive mode.
>>  >>  >
>>  >>  >Hmm. I'm not using INSERT, but LOAD DATA. Maybe that's the
> culprit?
>>  >>
>>  >>  Maybe.  In data files loaded with LOAD DATA, NULL values 
>>should be specified
>>  >>  as \N, not as the word NULL.
>>  >>  I'm not sure that that will make a difference, though. MySQL 
>>will see NULL,
>>  >>  perform a string-to-number conversion and end up with a value of
> zero.
>>  >>  And inserting 0 into an AUTO_INCREMENT column should be the same
>>  >>as inserting
>>  >>  NULL.  Still, it'd be worth a try to convert NULL to \N and see
>>  >>what happens.
>>  >>
>>  >>  This might be interacting with another problem, which is that if your
>>  >>  data file likes are CRLF terminated, *you need to say so*.  The
> default
>>  >>  is LF-terminated, so your LOAD DATA statement needs to have a
>>  >>
>>  >>  LINES TERMINATED BY '\r\n'
>>  >>
>>  >>  clause at the end.
>>  >
>>  >Tried both suggestions - still getting 1..7 as UserID.
>>
>>  What does this statement show after creating the table, and after the
>>  LOAD DATA statement:
>>
>>  SHOW TABLE STATUS LIKE 'yourtablename'\G
>
>mysql> show table status like 'respondents' \G
>*************************** 1. row ***************************
>            Name: respondents
>            Type: MyISAM
>      Row_format: Dynamic
>            Rows: 7
>  Avg_row_length: 102
>     Data_length: 716
>Max_data_length: 4294967295
>    Index_length: 2048
>       Data_free: 0
>  Auto_increment: 8
>     Create_time: 2002-05-13 17:51:28
>     Update_time: 2002-05-13 17:54:52
>      Check_time: NULL
>  Create_options:
>         Comment:
>1 row in set (0.05 sec)

What does it show if you drop and create the table again (that's the first
half of what my question asked for?)

By the way, did you perhaps execute

DELETE FROM respondents;

at any point after creating the table?  That can knock the AUTO_INCREMENT
value back down to zero.

>mysql> \t
>
>--
>/* All outgoing email scanned by Norton Antivirus 2002 */
>Amer Neely, Softouch Information Services
>W: www.softouch.on.ca
>E: aneely@stripped
>V: 519.438.5887
>Perl | PHP | MySQL | CGI programming for shopping carts, data entry
>forms.
>"We make web sites work!"

Thread
AUTO_INCREMENT= ?Amer Neely13 May
  • Re: AUTO_INCREMENT= ?Victoria Reznichenko13 May
Re: AUTO_INCREMENT= ?Amer Neely13 May
  • Re: AUTO_INCREMENT= ?Luciano Barcaro13 May
  • Re: AUTO_INCREMENT= ?Paul DuBois13 May
  • Re: AUTO_INCREMENT= ?Curtis Maurand13 May
  • Re: AUTO_INCREMENT= ?Amer Neely13 May
    • Re: AUTO_INCREMENT= ?Paul DuBois13 May
  • Re: AUTO_INCREMENT= ?Amer Neely13 May
  • Re: AUTO_INCREMENT= ?Amer Neely13 May
    • Re: AUTO_INCREMENT= ?Paul DuBois13 May
  • Re: AUTO_INCREMENT= ?Amer Neely13 May
    • Re: AUTO_INCREMENT= ?Paul DuBois13 May