• ERROR 1677: Column of table cannot be converted from type varchar(0) to type varchar(20)

    Error 1677 on Slave Server

    I ran into a 1677 error when trying to set up replication between two MariaDB servers.  They were both running the same versions of MariaDB and I did a physical data copy from one server to the other after I had ran RESET MASTER and FLUSH TABLES WITH READLOCK.

    After copying the files to the Slave server and performing a STOP SLAVE, RESET SLAVE, CHANGE MASTER TO, START SLAVE replication would work for a short period and then it would crash on one table some time later with a  “ERROR 1677: Column of table cannot be converted from type varchar(0) to type varchar(20)” error.

    The error clearly stated the database and table name where so I knew which table was causing the issue and I could easily replicate the issue by making an update or insert to the table that kept crashing after starting over again.  Everything else was replicating fine except that one table.

    I spent hours and hours searching the internet for a solution and nothing I tried worked.

    I was finally able to fix the 1677 table cannot be converted error.

    The table in question needed to be repaired using REPAIR TABLE.  Once I did that, recopied the data and reset the slave, replication has been working fine for almost 24 hours and updates and inserts into the table in question work perfectly now.  UPDATE:  It’s been working perfectly for a month now with no issues!!!

    So, if you are receiving a 1677 error trying to replicate data to a slave server, be sure to check your tables and perform a REPAIR TABLE on that table and see if that solves your issue.  It worked for me and I’m able to sleep a little better at night now knowing I’m replicating to an offsite database server.  I should have done this step when I migrated the data from MySQL to MariaDB but everything worked fine and I honestly forgot to do this.

Comments are closed.