Open Stack

On 09/11/2013 12:28 PM, Monty Taylor wrote:
>
>
> On 09/11/2013 11:09 AM, David Ripton wrote:
>> On 09/11/2013 06:37 AM, Steven Hardy wrote:
>>
>>> I'm investigating some issues, where data stored to a text column in
>>> mysql
>>> is silently truncated if it's too big.
>>>
>>> It appears that the default configuration of mysql, and the sessions
>>> established via sqlalchemy is to simply warn on truncation rather than
>>> raise an error.
>>>
>>> This seems to me to be almost never what you want, since on retrieval the
>>> data is corrupt and bad/unexpected stuff is likely.
>>>
>>> This AFAICT is a mysql specific issue[1], which can be resolved by
>>> setting
>>> sql_mode to "traditional"[2,3], after which an error is raised on
>>> truncation,
>>> allowing us to catch the error before the data is stored.
>>>
>>> My question is, how do other projects, or oslo.db, handle this atm?
>>>
>>> It seems we either have to make sure the DB enforces the schema/model, or
>>> validate every single value before attempting to store, which seems
>>> like an
>>> unreasonable burden given that the schema changes pretty regularly.
>>>
>>> Can any mysql, sqlalchemy and oslo.db experts pitch in with opinions on
>>> this?
>>
>> Nova has a PostgreSQL devstack gate, which occasionally catches errors
>> that MySQL lets through.  For example,
>> https://bugs.launchpad.net/nova/+bug/1217167
>>
>> Unfortunately we have some MySQL-only code, and PostgreSQL obviously
>> can't catch such errors there.
>>
>> I think we should consider turning off auto-truncation for MySQL on our
>> CI boxes.
>
> Should turn it off everywhere - same as how we auto-configure to use
> InnoDB and not MyISAM, we should definitely set strict sql_modes
> strings. There is not an operational concern - sql_modes affect app
> developers, of which we are they. :)

If it's our DB, we can configure it however we want.  If it's a user's 
DB, and it's potentially also used by other programs, then we need to be 
careful.

We can set strict mode either globally for the DB server, or 
per-session.  My gut says we should do it per-session, even though it's 
a bit annoying to run the code every time we start a session rather than 
once at setup, Just In Case someone is running OpenStack on a MySQL 
server that also does other things, and might not appreciate excessive 
global meddling.

Anyway, I'll propose a patch for this in Icehouse.

-- 
David Ripton   Red Hat   dripton at redhat.com