dbWriteTable fails without row_names column

[ghost]

Hi all, Just wanted to share a session that shows how dbWriteTable requires row_names table on a Windows 7 computer. Thanks!

---------------------------- MySQL Command Line Client -----------------------

show create table value;

create table ‘value’ (
‘id’ int(11) not null auto_increment,
‘idRun’ int(11) not null,
‘timestep’ int(11) not null,
‘row’ int(11) not null,
‘col’ int(11) not null,
‘value’ double not null,
‘row_names’ text,
primary key (‘id’),
key ‘idrun’ (‘idRun’),
constraint ‘value_ibfk_1’ foreign key (‘idrun’) references ‘run’(‘id) on delete cascade
) engine=inno_db auto_increment=742330 default charset=utf8

---------------------- RStudio ----------------------

df
idRun timeStep row col value
1 4 0 14 37 1
2 4 0 15 35 1
3 4 0 15 38 3
4 4 0 15 39 3
5 4 0 15 40 2
6 4 0 15 41 1
7 4 0 15 42 1
8 4 0 16 37 2
9 4 0 16 38 3
10 4 0 16 39 20

dbWriteTable (db, ‘value’, df, append = TRUE, row_names = FALSE)
TRUE

(It is working great. At this point in the MySQL Command Line Client I run ‘alter table value drop column row_names;’)

dbWriteTable (db, ‘value’, df, append = TRUE, row_names = FALSE)
Error in .local(conn, statement, ...) :
could not run statement: Unknown column 'row_names' in 'field list'
Error in .local(conn, statement, ...) :
could not run statement: Unknown column 'row_names' in 'field list'
Error in .local(conn, statement, ...) :
could not run statement: Unknown column 'row_names' in 'field list'
.
.
.
(At this point I hit Stop button to stop the infinite loop)

dbWriteTable (db, ‘value’, df, append = TRUE, row_names = TRUE)
Error in .local(conn, statement, ...) :
could not run statement: Unknown column 'row_names' in 'field list'
Error in .local(conn, statement, ...) :
could not run statement: Unknown column 'row_names' in 'field list'
Error in .local(conn, statement, ...) :
could not run statement: Unknown column 'row_names' in 'field list'
.
.
.
(At this point I hit Stop button to stop the infinite loop)

(At this point in the MySQL Command Line Client I run ‘alter table value add column column_names text)

dbWriteTable (db, ‘value’, df, append = TRUE, row_names = FALSE)

TRUE

dbWriteTable (db, ‘value’, df, append = TRUE, row_names = TRUE)
TRUE

[llorban]

I'd like more clarification on this issue