load_examples in MySQL exceeds max_allowed_packet size

[x4base]

When executing "caravel load_examples" using mysql, the follow error message occurs:

Loading [World Bank's Health Nutrition and Population Stats]
Traceback (most recent call last):
  File "/Users/clu/.pyenv/versions/druid/bin/caravel", line 6, in <module>
    exec(compile(open(__file__).read(), __file__, 'exec'))
  File "/projects/druid/panoramix/caravel/bin/caravel", line 113, in <module>
    manager.run()
  File "/Users/clu/.pyenv/versions/druid/lib/python2.7/site-packages/Flask_Script-2.0.5-py2.7.egg/flask_script/__init__.py", line 412, in run
    result = self.handle(sys.argv[0], sys.argv[1:])
  File "/Users/clu/.pyenv/versions/druid/lib/python2.7/site-packages/Flask_Script-2.0.5-py2.7.egg/flask_script/__init__.py", line 383, in handle
    res = handle(*args, **config)
  File "/Users/clu/.pyenv/versions/druid/lib/python2.7/site-packages/Flask_Script-2.0.5-py2.7.egg/flask_script/commands.py", line 216, in __call__
    return self.run(*args, **kwargs)
  File "/projects/druid/panoramix/caravel/bin/caravel", line 83, in load_examples
    data.load_world_bank_health_n_pop()
  File "/projects/druid/panoramix/caravel/data/__init__.py", line 196, in load_world_bank_health_n_pop
    index=False)
  File "/Users/clu/.pyenv/versions/druid/lib/python2.7/site-packages/pandas-0.18.0-py2.7-macosx-10.9-x86_64.egg/pandas/core/generic.py", line 1160, in to_sql
    chunksize=chunksize, dtype=dtype)
  File "/Users/clu/.pyenv/versions/druid/lib/python2.7/site-packages/pandas-0.18.0-py2.7-macosx-10.9-x86_64.egg/pandas/io/sql.py", line 571, in to_sql
    chunksize=chunksize, dtype=dtype)
  File "/Users/clu/.pyenv/versions/druid/lib/python2.7/site-packages/pandas-0.18.0-py2.7-macosx-10.9-x86_64.egg/pandas/io/sql.py", line 1250, in to_sql
    table.insert(chunksize)
  File "/Users/clu/.pyenv/versions/druid/lib/python2.7/site-packages/pandas-0.18.0-py2.7-macosx-10.9-x86_64.egg/pandas/io/sql.py", line 770, in insert
    self._execute_insert(conn, keys, chunk_iter)
  File "/Users/clu/.pyenv/versions/druid/lib/python2.7/site-packages/pandas-0.18.0-py2.7-macosx-10.9-x86_64.egg/pandas/io/sql.py", line 745, in _execute_insert
    conn.execute(self.insert_statement(), data)
  File "build/bdist.macosx-10.9-x86_64/egg/sqlalchemy/engine/base.py", line 914, in execute
  File "build/bdist.macosx-10.9-x86_64/egg/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
  File "build/bdist.macosx-10.9-x86_64/egg/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
  File "build/bdist.macosx-10.9-x86_64/egg/sqlalchemy/engine/base.py", line 1146, in _execute_context
  File "build/bdist.macosx-10.9-x86_64/egg/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
  File "build/bdist.macosx-10.9-x86_64/egg/sqlalchemy/util/compat.py", line 200, in raise_from_cause
  File "build/bdist.macosx-10.9-x86_64/egg/sqlalchemy/engine/base.py", line 1116, in _execute_context
  File "build/bdist.macosx-10.9-x86_64/egg/sqlalchemy/dialects/mysql/mysqldb.py", line 95, in do_executemany
  File "/Users/clu/.pyenv/versions/druid/lib/python2.7/site-packages/MySQLdb/cursors.py", line 285, in executemany
    r = self._query(qs)
  File "/Users/clu/.pyenv/versions/druid/lib/python2.7/site-packages/MySQLdb/cursors.py", line 378, in _query
    rowcount = self._do_query(q)
  File "/Users/clu/.pyenv/versions/druid/lib/python2.7/site-packages/MySQLdb/cursors.py", line 341, in _do_query
    db.query(q)
  File "/Users/clu/.pyenv/versions/druid/lib/python2.7/site-packages/MySQLdb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (2006, 'MySQL server has gone away') [SQL: u'INSERT INTO wb_health_population (`NY_GNP_PCAP_CD`, `SE_ADT_1524_LT_FM_ZS`, `SE_ADT_1524_LT_MA_ZS`, `SE_ADT_1524_LT_ZS`, `SE_ADT_LITR_FE_ZS`, `SE_ADT_LITR_MA_ZS`, `SE_ADT_LITR_ZS`, `SE_ENR_ORPH`, `SE_PRM_CMPT_FE_ZS`, `SE_PRM_CMPT_MA_ZS`, `SE_PRM_CMPT_ZS`, `SE_PRM_ENRR`, `SE_PRM_ENRR_FE`, `SE_PRM_ENRR_MA`, `SE_PRM_NENR`, `SE_PRM_NENR_FE`, `SE_PRM_NENR_MA`, `SE_SEC_ENRR`, `SE_SEC_ENRR_FE`, `SE_SEC_ENRR_MA`, `SE_SEC_NENR`, `SE_SEC_NENR_FE ....................... (very long statement)

After I increase my configuration of MySQL from max_allowed_packet=1M to 100M, the error is gone. So it should be caused by long statements. And countries.json is so far 98MB, I guess it makes my assumption more solid.
Is it possible to fix this problem without requiring the users to change the MySQL setting?

[mistercrunch]

Mmmh. I don't get that on my mysql instance...

[x4base]

What's your max_allowed_packet value? Is it because it's large enough?

[ouoc]

@mistercrunch I also change database_uri to mysql.
After I loaded the examples, I cannot see these in caravel. "Please define at least one metric" occurs.
But sqlite goes well.
Any help? Thanks a lot

[xrmx]

@x4base is this still an issue for you?

[xrmx]

@x4base if you look at caravel/data/init.py:def load_world_bank_health_n_pop() you'll see that data is uploaded to the server in chunks of 500 rows. Maybe bumping it to 2M would be enough. For reference on debian max_allowed_packet is set by default to 16M.

[xrmx]

Fixed via #1279