MySQL: ByteString and BLOBs #122

Closed
agrafix opened this Issue Apr 11, 2013 · 11 comments

Projects

None yet

4 participants

agrafix commented Apr 11, 2013

Currently the MySQL Migration creates a BLOB field for ByteStrings. However, if the ByteString is larger than 64kB it get's cut-off, without any warning. Maybe implement MEDIUMBLOB/LARGEBLOB or display warnings?

Owner

It seems to me that biggest problem is deciding what the correct fix would be. Any ideas?

@gregwebs gregwebs added the MySQL label Aug 4, 2014
Member

Does Persistent currently check fields for being longer than the storable value? If not, I think this probably best addressed by using MySQL/InnoDB's strict mode, which raises errors when a value is too large to be stored.

As for MEDIUMBLOB/LONGBLOB, those can be used by specifying the sqltype:

Bar
    test ByteString
    testMedium ByteString sqltype=MEDIUMBLOB
    testLong ByteString sqltype=LONGBLOB
mysql> SHOW CREATE TABLE bar\G
*************************** 1. row ***************************
       Table: bar
Create Table: CREATE TABLE `bar` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `test` blob NOT NULL,
  `test_medium` mediumblob NOT NULL,
  `test_long` longblob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Potentially it would be a good idea to enable strict mode in the scaffolding or something (it's a really good default, imo), but otherwise I think this can be closed.

Owner

Does Persistent currently check fields for being longer than the storable value?

No.

I don't think this should be closed. What is being done for Text? The behavior should be the same.

Member

Text is handled the same way; it defaults to the MySQL TEXT type which has the same maximum length as BLOB (same with MEDIUMTEXT/MEDIUMBLOB and LONGTEXT/LONGBLOB).

Baz
    test Text
    testMedium Text sqltype=MEDIUMTEXT
    testLong Text sqltype=LONGTEXT
mysql> SHOW CREATE TABLE baz\G
*************************** 1. row ***************************
       Table: baz
Create Table: CREATE TABLE `baz` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `test` text NOT NULL,
  `test_medium` mediumtext NOT NULL,
  `test_long` longtext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Owner

Ok, I agree the current behavior is correct if strict mode produces an error that is logged in Haskell at the error level or turned into an exception.

Member

@gregwebs I can confirm that an exception is thrown. Without strict mode the data is truncated, with it enabled (SET GLOBAL sql_mode = 'STRICT_ALL_TABLES';) I get the following exception:

ConnectionError {errFunction = "query", errNumber = 1406, errMessage = "Data too long for column 'test' at row 1"} @(yesod_Her3bQpHkRkDLXxJous6w5:Yesod.Core.Class.Yesod ./Yesod/Core/Class/Yesod.hs:539:5)

Roughly what code I used to test:

{-# LANGUAGE ScopedTypeVariables #-} 
import qualified Data.ByteString as BS

getHomeR :: Handler Html
getHomeR = do

    let big = duplicateUpToSize "bytestring" 65535

    (void $ runDB $ insert $ Bar big big big) `catch` \(e :: SomeException) -> do
        traceM $ "Exception is" ++ show e
        return ()


duplicateUpToSize :: ByteString -> Int -> ByteString
duplicateUpToSize bs int = if BS.length bs > int then bs else duplicateUpToSize (bs <> bs) int
Bar
    test ByteString
    testMedium ByteString sqltype=MEDIUMBLOB
    testLong ByteString sqltype=LONGBLOB
Owner

That looks like a good result to me. Where can we document the need for strict mode such that someone would see it? Putting something in the scaffolding is good, but that would only be for yesod users.

@MaxGabriel MaxGabriel referenced this issue in yesodweb/yesod-scaffold May 26, 2015
Closed

Use MySQL strict mode by default in the scaffolding #74

Member

@gregwebs I'm not sure, tbh. Maybe as a callout or short section titled "Backend-Specific Quirks" or "Database Configuration" or something, within an article or wiki page about integrating Persist manually w/o the scaffolding? Currently I think the only place like that is the Yesod Book, though. If there was sample code giving the best-practice way to use Persistent in a script or something like Scotty, it could be used there?

Whatever ends up addressing this could also mention enabling Foreign Key constraints for SQLite, since there's been interest in that from several Persistent users and it's in the same vein of ensuring data integrity.

It could go in a wiki page specifically about database configuration, but I feel like people would be less likely to see it.

Member

Added a wiki page about this.

Member

When I looked into this before I figured that the mysql package's InitCommand was the right way to enable strict mode, but ran into this issue paul-rouse/mysql#16. Just submitted a PR fixing that: paul-rouse/mysql#17

@MaxGabriel MaxGabriel referenced this issue in yesodweb/yesod-scaffold Aug 23, 2015
Merged

Enable MySQL strict mode from the scaffolding #94

Member

The next version of the scaffolding will enable MySQL strict mode by default, which would prevent this from happening (see yesodweb/yesod-scaffold#94). Thanks for reporting the issue!

@MaxGabriel MaxGabriel closed this Aug 29, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment