shntnu
1/21/2017 - 7:00 PM

We get an error when trying to load a table that's too wide for MySQL (InnoDB storage engine) ERROR 1117 (HY000) at line 26: Too many column

We get an error when trying to load a table that's too wide for MySQL (InnoDB storage engine) ERROR 1117 (HY000) at line 26: Too many columns We are aware that this wide a table was a bad idea (http://dba.stackexchange.com/questions/3972/too-many-columns-in-mysql) but need to get this to work because of legacy data.

natprot_mysql_too_wide.sql
content_copyfile_download
# Dummy example
wget https://gist.githubusercontent.com/shntnu/2063640233023746222ce83bdb954e12/raw/33574c30f0638f7ac8dfca3e6ad8abbb83c4003e/wide_table.sql

mysql -u root --execute="create database wide_test;"

mysql -u root wide_test < wide_table.sql
#> ERROR 1117 (HY000) at line 1: Too many columns

# Real example
echo "http://pubs.broadinstitute.org/bray_natprot_2016/suppl/online/seedseq_data/seedseq_per_cells.sql.gz
http://pubs.broadinstitute.org/bray_natprot_2016/suppl/online/seedseq_data/seedseq_per_cytoplasm.sql.gz
http://pubs.broadinstitute.org/bray_natprot_2016/suppl/online/seedseq_data/seedseq_per_nuclei.sql.gz
http://pubs.broadinstitute.org/bray_natprot_2016/suppl/online/seedseq_data/seedseq_per_image.sql.gz
https://github.com/carpenterlab/2016_bray_natprot/raw/master/supplementary_files/seedseq_per_object.sql.gz" > files.txt

wget -i files.txt

cat files.txt | sed 's/\(.*\)\(seedseq.*\)/\2/g'|xargs -I % echo gunzip %

#mysql  Ver 14.14 Distrib 5.5.54, for debian-linux-gnu (x86_64) using readline 6.3

mysql -u root --execute="create database natprot;"
mysql -u root natprot < seedseq_per_image.sql
mysql -u root natprot < seedseq_per_nuclei.sql 
mysql -u root natprot < seedseq_per_cells.sql
mysql -u root natprot < seedseq_per_cytoplasm.sql 

# this fails ERROR 1117 (HY000) at line 26: Too many columns
mysql -u root natprot < seedseq_per_object.sql