3

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 (
MySQL error message

Community
  • 1
  • 1
sully_r
  • 131
  • 1
  • 4
  • 14
  • I actually solved my own question and I want to provide the answer despite a low reputation, as I think this is unique. First, I had to delete the entire SCHEMA. Second, I executed the SQL Statement: set names 'utf8mb4'. Third, I created a table: CREATE table test.test (id primary key not null, text varchar(64) charset utf8mb4 collate utf8mb4_unicode_ci) character set utf8mb4. FOURTH, every time I want execute my Java PreparedStatement, I actually execute a "set names 'utf8mb4' immediately prior (as seen above)....that's it. hope this helps someone. – sully_r Jan 15 '15 at 12:28

2 Answers2

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.

aanton
  • 5,572
  • 1
  • 23
  • 15
  • I used this variation of @aanton's answer, which worked in my case: SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'; – Aaron Reed Dec 27 '16 at 22:04
  • @AaronReed be careful, chartset "utf8" in MySQL does not include 4-byte UTF-8 characters, you should use "utf8mb4" – aanton Dec 30 '16 at 13:40
0

I was getting this error after I created and attempted to work with a database in MySQL Workbench. After I restarted Workbench my problem disappeared.

Josh C
  • 7,461
  • 3
  • 24
  • 21