search

anvc / scalar

Born-digital, open source, media-rich scholarly publishing that’s as easy as blogging.
Other
231 stars 73 forks source link

Avoid error on MySQL 5.6: Data truncated for column 'category' #41

Closed burki closed 8 years ago

burki commented 8 years ago

for an query like UPDATE ... SET category = '' (#26)

craigdietrich commented 8 years ago

Thanks @burki!

Curious why 'category' would throw an error if category is "" instead of NULL, while other fields wouldn't? Is the database (scalar_store.sql) incorrect when setting up the field?

burki commented 8 years ago

Hi @craigdietrich: The database settings are correct:

scalar_db_content.category is set as enum('commentary','review','term') COLLATE utf8_unicode_ci DEFAULT NULL

MySQL 5.6 no longer silently truncates the empty string '' to NULL when executing an update like UPDATE scalar_db_content SET category='' Therefore the code needed to be adjusted so the UPDATE query is the following UPDATE scalar_db_content SET category=null

As far as I see scalar_db_content.category is currently the only enum-column that allows NULL values.

craigdietrich commented 8 years ago

@burki Ah, thanks for the details! I'm still getting my head around 5.6 so this is really helpful.

Let me double check that 'category' should indeed be the only enum that allows NULL (which makes sense, since a page typically doesn't have a category), then I'll accept the pull request.

craigdietrich commented 8 years ago

@burki Looks like things are working great after accepting the pull request (NULL gets inserted into the category field). I tightened things up in scalar_store.sql a bit, too (https://github.com/anvc/scalar/commit/a03f35fc9f5213c593d4e0fc6483e9c1b3c9b086).

Thanks for your work on this!