ERROR mysql: The value NIL is not of type STRING when binding STRING

[gavinwahl]

• pgloader --version

  pgloader version "3.6.86b6a5c"
  compiled with SBCL 1.4.14-3.fc31
  

• did you test a fresh compile from the source tree?
  Yes, tested this on master commit 86b6a5c

$ ./build/bin/pgloader --verbose --debug mysql://root@localhost/XXX pgsql:///XXX
pgloader version 3.6.86b6a5c
compiled with SBCL 1.4.14-3.fc31
sb-impl::*default-external-format* :UTF-8
tmpdir: #P"/tmp/pgloader/"
2020-04-06T18:13:23.022000-06:00 NOTICE Starting pgloader, log system is ready.
2020-04-06T18:13:23.043000-06:00 LOG pgloader version "3.6.86b6a5c"
2020-04-06T18:13:23.044000-06:00 DEBUG LOAD DATA FROM #<PGLOADER.SOURCE.MYSQL:MYSQL-CONNECTION mysql://root@localhost:3306/XXX {100560FA33}>
2020-04-06T18:13:23.074000-06:00 DEBUG CONNECTED TO #<PGLOADER.PGSQL:PGSQL-CONNECTION pgsql://gavin@UNIX:5432/XXX {10058137C3}>
2020-04-06T18:13:23.074000-06:00 DEBUG SET client_encoding TO 'utf8'
2020-04-06T18:13:23.074000-06:00 DEBUG SET application_name TO 'pgloader'
2020-04-06T18:13:23.092000-06:00 LOG Migrating from #<MYSQL-CONNECTION mysql://root@localhost:3306/XXX {100560FA33}>
2020-04-06T18:13:23.092000-06:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://gavin@UNIX:5432/XXX {10058137C3}>
2020-04-06T18:13:23.148000-06:00 DEBUG CONNECTED TO #<MYSQL-CONNECTION mysql://root@localhost:3306/XXX {100560FA33}>
2020-04-06T18:13:23.156000-06:00 SQL MySQL: sending query: -- params: db-name
--         table-type-name
--         only-tables
--         only-tables
--         including
--         filter-list-to-where-clause incuding
--         excluding
--         filter-list-to-where-clause excluding
  select c.table_name, t.table_comment,
         c.column_name, c.column_comment,
         c.data_type, c.column_type, c.column_default,
         c.is_nullable, c.extra
    from information_schema.columns c
         join information_schema.tables t using(table_schema, table_name)
   where c.table_schema = 'XXX' and t.table_type = 'BASE TABLE'
         
         
order by table_name, ordinal_position;
2020-04-06T18:13:23.247000-06:00 SQL MySQL: sending query: -- params: db-name
--         including
--         filter-list-to-where-clause incuding
--         excluding
--         filter-list-to-where-clause excluding
    SELECT table_name,
           cast(data_length/avg_row_length as integer)
      FROM information_schema.tables
    WHERE     table_schema = 'XXX'
          and table_type = 'BASE TABLE'
         
         ;
2020-04-06T18:13:23.254000-06:00 ERROR mysql: The value
         NIL
       is not of type
         STRING
       when binding STRING
2020-04-06T18:13:23.254000-06:00 LOG report summary reset
       table name     errors       read   imported      bytes      total time       read      write
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  fetch meta data          0          0          0                     0.000s    
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------

On a whim I tried this change and it started working, so I think this has something to do with tables with no rows. No idea if this is the correct fix though.

diff --git a/src/sources/mysql/sql/list-table-rows.sql b/src/sources/mysql/sql/list-table-rows.sql
index 82f58c8..657797f 100644
--- a/src/sources/mysql/sql/list-table-rows.sql
+++ b/src/sources/mysql/sql/list-table-rows.sql
@@ -4,7 +4,7 @@
 --         excluding
 --         filter-list-to-where-clause excluding
     SELECT table_name,
-           cast(data_length/avg_row_length as integer)
+           coalesce(cast(data_length/avg_row_length as integer), 0)
       FROM information_schema.tables
     WHERE     table_schema = '~a'
           and table_type = 'BASE TABLE'

In the output of that query, some tables have a avg_row_length of 0 so the division returns NULL.