MySQL MEDIUMTEXT and NULL/Blank values

[BillBuilt]

Hi.
Ran into a wierd scenario today, and was wondering if anyone else has had this problem. When using MySQL MEDIUMTEXT field, either allowing null values or not, if sent a blank value, MySQL is saving this as "\0" (ASCIICHAR(0)), which IS the equivalent to NULL in the same way "\r" is ASCIICHAR(13) or "\n" is ASCIICHAR(10). So in order to only return records where that field has a value i have to construct a query like so:


{ 'SELECT * FROM `theField` WHERE
`thefield` != \'\' AND
`thefield` != \'\\0\' AND
`thefield` IS NOT NULL;' }



I can't find anything on MySQL's site about this, so i was wondering if maybe it was the Empressa connector sending ASCIICHAR(0) for null and/or blank values?

Using MySQL 5.1 and Empressa's MySQL v5 connector.

I do not believe i've had this problem on MySQL versions < v5 and I am 95% sure it isnt happening with CHAR/VARCHAR fields, only TEXT types.


Thanks,
Bill

[Nerd Boy Inc]

Dear Bill,

What OS are you using?

[BillBuilt]

Oh yea, right, sorry.... Linux (CentOS 5)

Have not been able to test on windows via ODBC.

[mvmarkus]

Hi Bill,

I wouldn't be surprised if this is either an error in the connector or in the way MySQL handles Null values. MySQL introduced a lot of nasty changes with version 5.

One question: can it be that your Mediumtext-field is set to BINARY or that you may have a zerofill or something in there?

When you update the field, try to set the value to NULL (instead of leaving it blank). Maybe that can fix it?

Best,

Markus

[BillBuilt]

Hey Markus!

Been a while :) Hope all is well with you.

Just a simple mediumtext field.

`thefield` mediumtext NOT NULL,

I tried letting it be null, same thing. Is there a way to set a variable to NULL in Empressa?

You're right though, a lot changed in MySQL V5. More testing is in order here.

Bill

[mvmarkus]

Hey Bill,

Great to hear from you. Yeah, things are real good on this side of the Atlantic.

To setting a null value try: <MyQUERY NAME="schema" QUERY="UPDATE tablename SET (thefield=NULL) WHERE ...">

Alternatively, you could also try two quotes (asciichar(39)$asciichar(39) )for '', like this:

<MvASSIGN NAME = "l.query" VALUE = "UPDATE table SET (field=?) WHERE....." >
<MvASSIGN NAME = "l.val" VALUE = "{ asciichar(39)$asciichar(39) }" >
<MvQUERY NAME = "schema" QUERY = "{l.query}" FIELDS="l.val">

By the way, I made the experience that it is more reliable to use the ? for the values/parameters in the query and then use the field-attribute in the MvQUERY (or MvOPENVIEW)-tag instead of writing the values directly into the statement. As far as I know this is also more efficient, because the database server can compile the query.


Let me know if this works.

Best,

Best,

Markus

[BillBuilt]

As it turns out, it is because of using the FIELDS="" in the MvQUERY that causes the issue.

When using phpMyAdmin's INSERT or via Empressa like so:


<MvASSIGN NAME="g.tmp" VALUE="">
<MvQUERY
NAME="ww"
QUERY="{'INSERT INTO `table` (`thefield`) VALUES (\''$g.tmp$'\');'}">

Everyhing works as expected, however, when using the FIELDS="" like so:



<MvASSIGN NAME="g.tmp" VALUE="">
<MvQUERY
NAME="ww"
QUERY="{'INSERT INTO `table` (`theField`) VALUES (?);'}"
FIELDS="g.tmp">

`theField` is populated with "\0". Although, you have to do an export from phpMyAdmin to see the actual value.

So would this be a connector issue?

[mvmarkus]

I am not sure, if this is a connector issue: As an untyped language, Miva translates (casts) numbers into integers or decimals or, if a letter is present, into a string. An empty variable may therefore be translated into the value 0. MySQL 5 got more strict about it in its default configuration (this can probably be changed in the my.conf - file).

Did you actually try NULL or the double single quotes?

Markus

[BillBuilt]

<MvASSIGN NAME="g.tmp" VALUE="{asciichar(39)$asciichar(39)}">
<MvQUERY
NAME="ww"
QUERY="{'INSERT INTO `table` (`thefield`) VALUES(?);'}"
FIELDS="g.tmp">


Actually places two single quotes into the field as the value.

The 2 examples i listed earlier, at least on paper, should produce the same results, but they don't. I even tried:


<MvQUERY
NAME="ww"
QUERY="{'INSERT INTO `table` (`thefield`) VALUES(?);'}"
FIELDS="g.tmp">


thinking maybe by not even assigning g.tmp a value, that would in fact be a true null, but it did the same thing.
It would seem to me that Empressa is sending a null "character" instead of simply "nothing" when using the FIELDS parameter.

It's not converting it into the value of "0" (zero), it's actually saving it as "\0" (backslash zero). That is the exact value in the sql when exporting the record. When exporting after saving via MvQUERY w/o the FIELDS parameter, the exported value is '' (blank).

[BillBuilt]

I've just confirmed that this is also happening with VARCHAR() fields.

Seems the only times it works like it should is when the fields are omitted from the query altogether, and they result to their default values.

[BillBuilt]

Just tested on MySQL v4.1.22-standard/Linux without any problems.

[mvmarkus]

Did you try

<MvASSIGN NAME = "l.query" VALUE = "UPDATE table SET (field=?) WHERE....." >
<MvASSIGN NAME = "l.val" VALUE = "NULL" >
<MvQUERY NAME = "schema" QUERY = "{l.query}" FIELDS="l.val">

By the way, because of a similar problem with boolean values I reverted my programs back to version v.4.xxx. It turned out that I had to write a complete compatibility-wrapper if used with MySQL 4 or 5. The fact that there are now two different connectors doesn't make things much easier.

[eMediaSales]

Hi Markus & Bill,

First, a path to the solution:

1) Make absolutely certain you're using the MivaVM-MySQL connector library v2.016 beta. The v1.00 connector that comes with MivaVM 5.06 is way out date and does NOT work with MySQLv5. Note that differences in the MySQLv4 and MySQLv5 client libraries dictated a mysql4.so and mysql5.so connector for the VM. If your VM is using the mysql.so database library, you're probably on the v1.00 connectory. The only way to tell you have the right library is with the litmus test of checking the file-size against that of the binary distribution. Second would be a binary diff test or md5sum to make certain.

2) If this is a custom application, instead of using a field declaration with just NOT NULL use NOT NULL DEFAULT ''. This way the what NULL vs empty strings are handled won't affect your queries. Otherwise, find the discussion on the MySQL connector v2.016b library, and you'll get a lot of background on what's going on here which I discuss briefly below.

Second, the discussion:

I spent a LOT of time trying to get MySQLv5 to behave well from the untyped language interface (eg: MivaScript) layer to the strictly typed interface layer (c CLI). Nulls, zeros and empty strings are simply a real pain to make work right when the language itself has no real facility to declare types. It turns out the major difference between MySQLv4 and MySQLv5 is (by my recollection) that SQL strict_mode is Off by default in MySQLv4, and On by default in MySQLv5, but this even seems to differ based on MySQLv5 distribution version AND operating system. The strict mode setting handles implicit conversions in queries. You need to make sure the server is NOT in strict mode so that an empty string mivascript variable can be interpreted correctly. I don't believe MivaScript uses any concept of NULL at the db/api layer.

Long story short, you CAN write your application to work properly on both MySQLv4 and MySQLv5. Most likely the quickest path is to ensure you have the right library installed. It works well with Miva Merchant, which runs on MivaSQL, MySQLv4 and MySQL5.

Best,

[BillBuilt]

James,
Thanks for chiming in. This is causing some headaches here.

If this is a custom application, instead of using a field declaration with just NOT NULL use NOT NULL DEFAULT ''

I don't believe that MySQL uses DEFAULT '' with MEDIUMTTEXT types, does it? I always use DEFAULT '' on all other string types. The VARCHAR fields where this is happening definately have the DEFAULT '' declaration, which makes it even more problematic.

MySQL 5 Manual:
BLOB and TEXT columns cannot have DEFAULT values

I'll have to check on the other items, but i do believe we are using the latest connector...we just set this server up about a month ago... migrated from MySQL 4, where these issues were non-existent. I'll have to inquire about the strict_mode. Let me ask this, if it were ON, would (or could) that cause this behavior?


Bill

[BillBuilt]

D/L 2.016 just to be sure and that seemd to fix the issue. At least initial testing looks good. Thanks to all for your help! Don't know what version was on there initially.

FYI: SQL MODE is not set at all.
(We tried various settings. Still not clear on what the default mode is when the value is not set)
MySQL 5.0.45


Bill M.