Twitter Text from Java to MySQL: utf8mb4 SQL Error "reportcharsetnr("

Question

For context: Trying to load Twitter Text into a MySQL DB. Some Tweets have utf8mb4 characters which throws a java.SQL.Exception. I solved this and thanks to this post.

Now, the Java code runs with no errors; however, I can't perform a simple select * from test.tweet on my table.

I get the following SQL Error:

Error Code: 0 45) . Please reportcharsetnr (

SQL Code:

create table test.tweet (
    text varchar(200) character set utf8mb4 collate utf8mb4_unicode_ci not null);

Java Code: (I know this works)

// create a mysql database connection
        String myDriver = "com.mysql.jdbc.Driver";
        String myUrl = "jdbc:mysql://localhost:3306/test?";
        Class.forName(myDriver).newInstance();
        Connection conn = DriverManager.getConnection(myUrl, "root", "pass");
        PreparedStatement setNames = conn.prepareStatement("SET NAMES 'utf8mb4'");
        setNames.execute();

//Load the tweets into a mySQL DB
                    loadTweets(jsonObject, conn);

There is NOTHING on the internet about this error. Any thoughts why I can't query this error message:

Error Code: 0 45) . Please reportcharsetnr (

Answer 1

In MySQL Workbench i get that error too when i run SET NAMES without collation.

I fix it executing (change the collation properly):

SET NAMES 'utf8mb4' collate 'utf8mb4_spanish_ci';

You can execute it in your code after creating the connection.