R Markdown, SQL code chunk generates Syntax Error

Question

Goal: I am trying to compile a markdown document in R that includes some SQL code chunks with CREATE TABLE IF NOT EXISTS statements. These are sent to a MySQL database on localhost. In effect, they should serve to initialize a database, and being in Markdown gives me room to explain the structure of that database.

Problem: Unfortunately, the SQL code is generating syntax errors [1064] when compiled in R Markdown. However, if I run the same SQL code in MySQL Workbench, it executes properly. I believe the issue lies somewhere in the chain from knitr - rDBI - RMariaDB - MySQL, though I can't rule out user-error either.

Here is my MWE:

1. SQL

In the file SQL/mysqlcode.sql I have this code:

CREATE TABLE IF NOT EXISTS rt_states (
    stateId INT AUTO_INCREMENT NOT NULL, 
    stateName VARCHAR(32) NOT NULL, 
    PRIMARY KEY (stateId)
);

CREATE TABLE IF NOT EXISTS rt_counties (
    countyId INT AUTO_INCREMENT NOT NULL, 
    countyName VARCHAR(75), 
    stateId INT, 
    PRIMARY KEY (countyId), 
        FOREIGN KEY (stateId) 
        REFERENCES rt_states (stateId) 
        ON DELETE RESTRICT ON UPDATE CASCADE
);

2. R Markdown

```{r dbconn}

library(DBI)
library(RMariaDB)
library(here)  

db <- DBI::dbConnect(
  drv = RMariaDB::MariaDB(),
  dbname = "my_db",
  host = "localhost",
  username = "my_username",
  password = "my_password"
)

knitr::opts_chunk$set(connection = "db")

```

```{sql my_sql_chunk, code = readLines(here("SQL/mysqlcode.sql"))}
```

For what it's worth, I've gotten these syntax errors trying to execute the code with RStudio's SQL integration, too.

Edit. Here is the SQL error:

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 'CREATE TABLE IF NOT EXISTS rt_counties ( countyId INTO AUTO_INCREMENT NOT NUL' at line 7 [1064]

Answer 1

Only one statement at a time is allowed.