5

I want to transfer a production database to my development machine for tests.

It has 6 tables with MEMORY engine for performance purposes.

I did mysqldump --routines -hxxx -uxxx -pxxx prod_database > prod_dump.sql. When I did mysql -hxxx -uxxx -pxx prod_clone_database < prod_dump on the same production server (so I had a clone for UAT, because we haven't yet bought a UAT Mysql virtual server) that works OK.

When I do that same mysql -uroot -proot prod_clone < prod_dump.sql I get an error ERROR 1114 (HY000) at line 138735: The table 'sezione_a' is full

Production server runs Linux with 1GB of RAM, and runs only mysqld daemon. My laptop has 16GB of RAM. Why the heck do I get a table full error? How do I avoid that? Since data in that table is not relevant for tests, can I exclude its DML from the dump? I need the DDL anyways

usr-local-ΕΨΗΕΛΩΝ
  • 26,101
  • 30
  • 154
  • 305
  • 1
    Maybe a stupid question but how much memory is MySQL configured to use on your laptop? If your machine has 16 GB RAM but the server is only configured to use, say, 128 Mb.... – SolarBear Jan 29 '15 at 16:57
  • Not stupid at all, actually. How do I get that value? – usr-local-ΕΨΗΕΛΩΝ Jan 30 '15 at 08:18
  • It may require a bit of tuning. Have a look at that question : http://stackoverflow.com/questions/9842720/how-to-make-the-mysql-memory-engine-store-more-data – SolarBear Jan 30 '15 at 13:54

1 Answers1

4

In MySQL, by default, the temp tables created with the memory engine can quickly grow beyond the 16mb limit of max-heap-table-size and tmp-table-size because more memory is allocated per row than is usually required. For example, if each row requires 16kb, then it only takes 1k rows to reach the limit. For many applications, this issue can be addressed by using ROW_FORMAT=DYNAMIC as explained here:

http://www.percona.com/doc/percona-server/5.5/flexibility/improved_memory_engine.html

CQ Bear
  • 396
  • 2
  • 4