MySQL 5.1 data/field corruption

[Campion]

Hey guys.

So I was doing some touching up on a program last night, and while I did not change how the database was manipulated, I found that 1, and only 1 field on a table I was working with was being corrupted on insert and/or update. This is a TEXT field.

When I query the database in MySQL, the data for this field is missing, mixed up, or both. When I query it in VB, the field returns a an empty string. It's the strangest thing. It really screws up the screen when I do a query in the MySQL interpreter.

Every other field on the table acts the way they should be.

VB6 SP6 using ADO/ODBC
MySQL 5.1 connector
MySQL 5.1 on Linux

Anyone have any ideas?

[MaximilianMayrhofer]

Is it only corrupted when you attempt to manipulate its data via code, or is it physically corrupted? As in, when you open the table in MySQL, it's screwy.

[Campion]

Is it only corrupted when you attempt to manipulate its data via code, or is it physically corrupted? As in, when you open the table in MySQL, it's screwy.

Running a general query (in this case, "Select ID, PlayerName, PlayerID from Players") in the mySQL terminal, the first record will be fine, as it was added a while ago. However, with any subsequent records added, the PlayerName field will be corrupted. What should be "Campion" would end up looking like " ampCn" or missing altogether. This ends up skewing the table display in mySQL as well.

I don't have actual data at the moment, since I don't have access to my server, but this is an example:

Without PlayerName:

---------------------------------
ID | PlayerID
----------------------------------
4 | PlayerID1
7 | PlayerID2
11 | PlayerID3

And with the field in the query.

---------------------------------
ID | PlayerName | PlayerID
----------------------------------
4 | Player1 | IDString1
7 | lay | IDString2
| |


As far as code, I haven't modified it in a long time.

[MaximilianMayrhofer]

That's very unusual.. you might want to simply create a new identical table, and drop the current one.

[Campion]

I'll try that when I get home. Maybe it's a messed up index?

[Campion]

Well, I did several things. Along the way, I found and corrected a serious series of database errors.

1) rebuilt the table several times.
2) Installed 5.1 connecter up form 3.51.

These didn't work, so I downgraded back to 3.51. It seems that the formatting issue just has something do with the dimensions of the console. All data is transferring just fine now.

I found a wierd error with the connector, and read that if you have a table that has ANY date/time type fields that do not have a default date in them (i.e., if you add nothing, and it puts in #00-00-0000 00:00:00#,) any updates past adding a record will fail with the error code "E_FAIL", and the program will crash. So, just to note, make sure you put in a date into ANY date field. Even NULLS will crash.