MariaDB support - dbWriteTable (The used command is not allowed with this MariaDB version)

[knight1128]

I have troubling with inserting dataframe to mariaDB.
Except for dbWriteTable function, insert/get commands are working.

> dbWriteTable(con, value = locationtag_read_stats, name = "location_scores", append = TRUE)
Error in .local(conn, statement, ...) : 
  could not run statement: The used command is not allowed with this MariaDB version
> traceback()
9: .Call(RS_MySQL_exec, conn@Id, as.character(statement))
8: .local(conn, statement, ...)
7: dbSendQuery(conn, statement, ...)
6: dbSendQuery(conn, statement, ...)
5: dbGetQuery(conn, sql)
4: dbGetQuery(conn, sql)
3: .local(conn, name, value, ...)
2: dbWriteTable(con, value = locationtag_read_stats, name = "location_scores", 
       append = TRUE)
1: dbWriteTable(con, value = locationtag_read_stats, name = "location_scores", 
       append = TRUE)
> version
               _                           
platform       x86_64-apple-darwin13.4.0   
arch           x86_64                      
os             darwin13.4.0                
system         x86_64, darwin13.4.0        
status                                     
major          3                           
minor          2.1                         
year           2015                        
month          06                          
day            18                          
svn rev        68531                       
language       R                           
version.string R version 3.2.1 (2015-06-18)
nickname       World-Famous Astronaut      
> 

[krlmlr]

Thanks. Which version of MariaDB are you using?

[knight1128]

I am using version 5.5.24, 5.5.30 of MariaDB.

[pantoniades]

Hi,

"delete table" is not valid SQL - It should be "drop table".

thanks,

Philip

On Fri, Oct 30, 2015 at 4:55 AM, KimYongHwan notifications@github.com
wrote:

I am using version 5.5.24, 5.5.30 of MariaDB.

—
Reply to this email directly or view it on GitHub
#79 (comment).

[krlmlr]

@pantoniades Could you please be a bit more specific?

[pantoniades]

Hi,

The code sample given above is:

dbGetQuery(con,"delete table locationtag_scores");

The correct statement (for any version of MySQL) should be:

dbGetQuery(con,"DROP table locationtag_scores");

You can alternately use "DELETE FROM locationtag_scores", though that will
fail if there is no WHERE clause and the variable 'safe_updates' is set to
1.

Philip

On Thu, Nov 5, 2015 at 12:16 PM, Kirill Müller notifications@github.com
wrote:

@pantoniades https://github.com/pantoniades Could you please be a bit
more specific?

—
Reply to this email directly or view it on GitHub
#79 (comment).

[pantoniades]

Sorry, the system variable is 'sql_safe_updates', or --safe-updates as a
mysqld command line switch.

Philip

On Thu, Nov 5, 2015 at 2:26 PM, Philip Antoniades philip@antoniades.com
wrote:

Hi,

The code sample given above is:

dbGetQuery(con,"delete table locationtag_scores");

The correct statement (for any version of MySQL) should be:

dbGetQuery(con,"DROP table locationtag_scores");

You can alternately use "DELETE FROM locationtag_scores", though that will
fail if there is no WHERE clause and the variable 'safe_updates' is set to
1.

Philip

On Thu, Nov 5, 2015 at 12:16 PM, Kirill Müller notifications@github.com
wrote:

@pantoniades https://github.com/pantoniades Could you please be a bit
more specific?

—
Reply to this email directly or view it on GitHub
#79 (comment).

[krlmlr]

Thanks, this makes sense now. I think, DELETE <tablename> is the same as DELETE FROM <tablename>, which seems to be perfectly valid but forbidden without the "safe_updates" option.

[pantoniades]

From my server (mysql 5.7):

mysql> delete from temp;
Query OK, 0 rows affected (0.00 sec)

mysql> delete temp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near '' at line 1

mysql> set session sql_safe_updates = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from temp;
ERROR 1175 (HY000): You are using safe update mode and you tried to update
a table without a WHERE that uses a KEY column

You can see the different messages.

Philip

On Thu, Nov 5, 2015 at 2:39 PM, Kirill Müller notifications@github.com
wrote:

Thanks, this makes sense now. I think, DELETE is the same as DELETE
FROM , which seems to be perfectly valid but forbidden without
the "safe_updates" option
https://mariadb.com/kb/en/mariadb/mysql-command-line-client/#using-the-safe-updates-option
.

—
Reply to this email directly or view it on GitHub
#79 (comment).

[krlmlr]

Moved to rstats-db/RMariaDB (see link above).