MySQL warnings when mysqlimport'ing

[dank]

I haven't managed to find any documentation on this or whether there are MySQL error logs to look in.[nbsp][nbsp]When uploading a file to the server and issuing the following command via telnet:

mysqlimport -uxdomain -p -hMySQL.domain.com xdomain ./Results.txt

to dump the contents of the tab delimited file into table Results in database xdomain, I consistently get the following message:

xdomain.Results: Records: 162 Deleted: 0 Skipped: 0 Warnings 163

where "Records" is the number of lines in the particular Results.txt file being imported.[nbsp][nbsp]I believe "Warnings" has been the same number as "Records" in the other imports, but it was 1 higher this time.

Everything is being imported just fine, as far as I can tell, but I don't like being warned that many times...[nbsp][nbsp]Am I doing something wrong, or is that standard feedback?

Thanks,
Decrement

[Justin]

Warnings occurr when something didn't quite match the column type -- trying to insert a string into a numeric field, or NULL into a 'not null' column... I would check the resulting data, and if it looks okay I say run with it. If not, make sure your columns are the correct type, size, etc, and try again...

------------------
Justin Nelson
FutureQuest (http://www.FutureQuest.net/index.php) Support

[dank]

Ah, I think you might have just provided the hint.[nbsp][nbsp]I have the ID field set to NOT NULL, but I import it as a blank column (of tabs) to allow for auto incrementing.[nbsp][nbsp]That might explain one warning per line...[nbsp][nbsp]Is there a better way to do that, or is that ok?

Dan

[Justin]

Hm, you shouldn't get a warning passing NULL to an auto-increment field -- NULL is it's trigger to increment (assuming the ID field is in fact auto-increment)... There are a few cases where it's okay to pass NULL to a not-null field, auto-increment being one of them...

I suppose it's possible that the mysqlimport function still feels the need to warn you -- if all of the other fields look okay once inserted into the database, then you will be okay. It shouldn't be too difficult to spot check the data...

------------------
Justin Nelson
FutureQuest (http://www.FutureQuest.net/index.php) Support

[dank]

Everything looks aok -- the correct number of lines are imported from each file, everything is auto-incremented as it should be, all the fields look to be filled out correctly, and all the searches appear to be functioning fine -- so I guess I'll mop up my spilt milk and carry on![nbsp][nbsp]:)

Dairy

[PaulKroll]

I spent some time trying to duplicate this, and then, when that was darned easy, trying to figure out exactly why it's giving the warnings. I've failed. Explicitly, there's no place the warnings are sent, they're only tallied, so it's just a tad hard to figure out why the darn thing is being so retentive. I tried adding extra tabs after the valid fields, specifically using 'NULL' or \N for the autoincrement field, and pretty much everything but chanting, incense, and a circle of sulphur.

Looks like with autoincrement fields, you're going to get these.[nbsp][nbsp]For the "1 extra" warning, I'm guessing you have an extra newline in the textfile, which won't affect your data at all of course.

Thus another hour of my life is thrown away trying to make sense of it all... :)

[dank]

:)

I believe you're right about a newline causing the extra warning somewhere, although I thought I was careful to not have any of those...

Now I find myself with a new perplexer:[nbsp][nbsp]I deleted some records (two files' worth) and re-uploaded/imported them, only to find that the auto-incrementing is anything but.[nbsp][nbsp]:([nbsp][nbsp]I can't figure out any pattern to how it's importing the rows.[nbsp][nbsp]The closest I can find to a pattern is that it picks a row somewhere between the middle and end, then imports those in reverse order to the beginning, then picks up the last ones in the correct order...[nbsp][nbsp]From what I can tell, in versions of MySQL < 3.23, auto-increment is not fully supported.[nbsp][nbsp]However, I can't see what would be causing this sort of behavior, regardless of how it chooses to fill numbering gaps.[nbsp][nbsp]Any thoughts?

Before deleting and reloading the two files, I had changed a few fields from SMALLINT to VARCHAR (I realized that for some of the files which have no values for some integer-only fields, it defaults to &quot;0&quot; instead of being left blank), but those don't have anything to do with the ID field (auto-increment) and didn't seem to kick back any errors...

Dlete

edit:[nbsp][nbsp]I've ruled out it being anything related to the column changes.[nbsp][nbsp]Loading a new file of results (rows), they import in the correct order, as they did the first time around.[nbsp][nbsp]It is only when deleting rows within the auto-incremented numbering sequence and then adding them back in that it seems to go goofy.

[This message has been edited by dank (edited 11-29-00@12:06 pm)]

[dank]

On closer examination, I think I see what it is doing.[nbsp][nbsp]It appears to be correctly incrementing and assigning the ID numbers for the primary field, but it is putting them into the table in some non-numerical order.

I have one section that looks like:

3717[nbsp][nbsp]>
3718[nbsp][nbsp]> original imports
3719[nbsp][nbsp]>
4275[nbsp][nbsp]*
4274
4273
4271
4270
4269 *
4272 *
4266 *
4268 *
4267
4265
4264
4263
4262
4261
4260
4259 *
4257 *
4258 *
4256
4255
4254
4253
4000 *
3998
3997
etc.

The 42xx sequence is part of one of the re-imports, and the <4000 sequence begins part of the other re-import.

Those ID's that are out of order would be the correct ID's if they were added to the end of the table, but it feels this crazy need to jump around haphazardly with the only semblance of a pattern being that it's got it all backward...

Even filling in the ID numbers (as opposed to letting the auto-increment assign them) before re-importing had the same result.[nbsp][nbsp]It seems that as long as there are gaps in the numbering sequence, MySQL gets tripped up.[nbsp][nbsp]At least that got rid of the warnings.[nbsp][nbsp]:)

Have I gone completely insane, or are the MySQL munchkins having a great deal of fun at my expense?[nbsp][nbsp]Surely something this simple isn't beyond the abilities of something so powerful?

Dranged