error creating user table: Error: ER_TOO_LONG_KEY: Specified key was too long; max key length is 767 bytes

[pdehaan]

I'm using OSX Yosemite and just did a $ brew update and $ brew install mysql then $ mysql.server start.

Running $ node server/db/create_db gives me some odd errors I can't decipher and stalls my Terminal:

$ node server/db/create_db.js

chronicle.server.db.utils.VERBOSE: pool "connection" event fired, setting timezone
chronicle.server.db.VERBOSE: dropping any existing database
chronicle.server.db.VERBOSE: old database dropped.
chronicle.server.db.VERBOSE: creating chronicle database
chronicle.server.db.VERBOSE: database successfully created
chronicle.server.db.VERBOSE: now using database
chronicle.server.db.VERBOSE: creating user table
chronicle.server.db.WARN: error creating user table: Error: ER_TOO_LONG_KEY: Specified key was too long; max key length is 767 bytes
chronicle.server.db.WARN: something went wrong: Error: ER_TOO_LONG_KEY: Specified key was too long; max key length is 767 bytes

Note that the process doesn't end and I have to Ctrl+C to break free.

Not sure why MySQL hates me in my guts, but I think it's somewhere around this line in /server/db/create_db.js:70.
Maybe we move the pool.end() call from line 80 into the createDatabase() callback function after line 101. If I move that line locally, I still get the errors, but the script ends with this and frees my Terminal up:

chronicle.server.db.utils.VERBOSE: received exit signal, closing pool

[nchapman]

Try brew update and then brew upgrade mysql

On Friday, January 9, 2015, Peter deHaan notifications@github.com wrote:

I'm using OSX Yosemite and just did a $ brew update and $ brew install
mysql then $ mysql.server start.

Running $ node server/db/create_db gives me some odd errors I can't
decipher and stalls my Terminal:

$ node server/db/create_db.js

chronicle.server.db.utils.VERBOSE: pool "connection" event fired, setting timezone
chronicle.server.db.VERBOSE: dropping any existing database
chronicle.server.db.VERBOSE: old database dropped.
chronicle.server.db.VERBOSE: creating chronicle database
chronicle.server.db.VERBOSE: database successfully created
chronicle.server.db.VERBOSE: now using database
chronicle.server.db.VERBOSE: creating user table
chronicle.server.db.WARN: error creating user table: Error: ER_TOO_LONG_KEY: Specified key was too long; max key length is 767 bytes
chronicle.server.db.WARN: something went wrong: Error: ER_TOO_LONG_KEY: Specified key was too long; max key length is 767 bytes

Note that the process doesn't end and I have to Ctrl+C to break free.

Not sure why MySQL hates me in my guts, but I think it's somewhere around
this line in /server/db/create_db.js:70

chronicle/server/db/create_db.js

Line 70 in 465f474

log.warn('error creating user table: ' + err);

.
Maybe we move the pool.end() call from line 80 into the createDatabase()
callback function after line 101. If I move that line locally, I still get
the errors, but the script ends with this and frees my Terminal up:

chronicle.server.db.utils.VERBOSE: received exit signal, closing pool

—
Reply to this email directly or view it on GitHub
#98.

[pdehaan]

but but but... i just installed mysql...

I'm very suspicious because THIS worked like a champ:

'email VARCHAR(128) NOT NULL,' + // was originally VARCHAR(256)

I got the idea from this StackOverflow answer where somebody said that you should switch to 'latin1' instead of 'utf8' since it takes one byte for one character instead of four. Possible BS, but I can probably test the theory by fudging the field length until it passes.
... Well, looks like I can set it to VARCHAR(255) and it works, but VARCHAR(256) goes 💥.

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.22, for osx10.10 (x86_64) using  EditLine wrapper

[pdehaan]

$ brew update
Already up-to-date.

$ brew upgrade mysql
Error: mysql 5.6.22 already installed

[nchapman]

I had that same issue but was running .21 or something. Upgraded and it
fixed it :/

On Friday, January 9, 2015, Peter deHaan notifications@github.com wrote:

$ brew update
Already up-to-date.

$ brew upgrade mysql
Error: mysql 5.6.22 already installed

—
Reply to this email directly or view it on GitHub
#98 (comment).

[pdehaan]

I'm also seeing this when trying to populate w/ dummy data:

chronicle.CRITICAL: Error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2015-01-01T21:26:23.795Z' for column 'visitedAt' at row 1

Full debug log:

$ node server/db/create_test_data.js

chronicle.server.db.utils.VERBOSE: pool "connection" event fired, setting timezone
chronicle.db.createTestUser.VERBOSE: truncated visits table
chronicle.db.createTestUser.VERBOSE: truncated users table
chronicle.db.createTestUser.VERBOSE: fakeUser is {"id":"a6c9411418f94710a712ab97e7a3541c","email":"fake_user@example.com","oauthToken":"fakeOauthToken"}

chronicle.server.db.db.VERBOSE: db.createUser called. arguments are: a6c9411418f94710a712ab97e7a3541c, fake_user@example.com, fakeOauthToken, function (err) {
    if (err) { throw err; }
    log.verbose('inserted fake user into db');

    // instead of db.createVisit, we'll just issue a bulk insert query directly
    var query = 'INSERT INTO visits (id, visitedAt, fxaId, rawUrl, url, urlHash, title) ' +
                'VALUES ?';
    log.trace('about to insert bulk data');
    log.trace('query is: ' + query);
    log.trace('bulkData.length is ' + bulkData.length);
    pool.query(query, [bulkData], function(err) {
      if (err) { throw err; }
      log.verbose('inserted ' + bulkData.length + ' user visits into db');
      cb && cb(err);
    });
  }

chronicle.server.db.utils.VERBOSE: pool "connection" event fired, setting timezone
chronicle.server.db.db.VERBOSE: db.createUser: created user called. arguments are: undefined
chronicle.db.createTestUser.VERBOSE: inserted fake user into db
chronicle.CRITICAL: Error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2015-01-01T21:26:23.795Z' for column 'visitedAt' at row 1
    at Query.Sequence._packetToError (/Users/pdehaan/dev/github/chronicle/node_modules/mysql/lib/protocol/sequences/Sequence.js:48:14)
    at Query.ErrorPacket (/Users/pdehaan/dev/github/chronicle/node_modules/mysql/lib/protocol/sequences/Query.js:82:18)
    at Protocol._parsePacket (/Users/pdehaan/dev/github/chronicle/node_modules/mysql/lib/protocol/Protocol.js:271:23)
    at Parser.write (/Users/pdehaan/dev/github/chronicle/node_modules/mysql/lib/protocol/Parser.js:77:12)
    at Protocol.write (/Users/pdehaan/dev/github/chronicle/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/Users/pdehaan/dev/github/chronicle/node_modules/mysql/lib/Connection.js:82:28)
    at Socket.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:765:14)
    at Socket.emit (events.js:92:17)
    at emitReadable_ (_stream_readable.js:427:10)
    --------------------
    at Pool.query (/Users/pdehaan/dev/github/chronicle/node_modules/mysql/lib/Pool.js:180:23)
    at /Users/pdehaan/dev/github/chronicle/server/db/create_test_data.js:70:10
    at Query._callback (/Users/pdehaan/dev/github/chronicle/server/db/db.js:36:7)
    at Query.Sequence.end (/Users/pdehaan/dev/github/chronicle/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
    at Query._handleFinalResultPacket (/Users/pdehaan/dev/github/chronicle/node_modules/mysql/lib/protocol/sequences/Query.js:143:8)
    at Query.OkPacket (/Users/pdehaan/dev/github/chronicle/node_modules/mysql/lib/protocol/sequences/Query.js:77:10)
    at Protocol._parsePacket (/Users/pdehaan/dev/github/chronicle/node_modules/mysql/lib/protocol/Protocol.js:271:23)
    at Parser.write (/Users/pdehaan/dev/github/chronicle/node_modules/mysql/lib/protocol/Parser.js:77:12)
    at Protocol.write (/Users/pdehaan/dev/github/chronicle/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/Users/pdehaan/dev/github/chronicle/node_modules/mysql/lib/Connection.js:82:28)
chronicle.server.db.utils.VERBOSE: received exit signal, closing pool
chronicle.server.db.utils.VERBOSE: received exit signal, closing pool

[jaredhirsch]

@pdehaan what my.cnf files do you have on your system?

[jaredhirsch]

@pdehaan After taking a second look at fxa-auth-db-server, it looks like they use varchar(255) for the email field. So we'll use that. I'll just merge it now.

For future reference, your behavior matches up with this mysql bug which was WONTFIX'd with the rationale that innodb_large_prefix is a documented workaround. I cannot figure out what could have modified this across our three installs of homebrew's mysql, except that maybe you have a random my.cnf somewhere in the ridiculous list of places MySQL looks for that file.

[jaredhirsch]

Should be fixed by #99. Reopen if it's still busted for you