Spring replication issue with read only user (command denied)

Question

I have a Spring application with replication setup (a master database for rw operations and and a slave database for r operations).

So far so good. I thought, it might be a good idea, to have a user for the rw operations and one for the read only operations.

So I added a read only user, which only has SELECT command granted.

Now, when I test my application, I get the following error:

java.sql.SQLSyntaxErrorException: SELECT with locking clause command denied to user 'testreader'@'localhost' for table 'hibernate_sequence'

I suppose I could get rid of this by granting LOCK TABLES as right, but is this what I need to do? Or is it true, that the hibernate_sequence (which I suppose is some internal Spring database) is needed to be locked by this read only user?

Or am I missing something else entirely?

If needed, I can post some code here.

Thanks for any help!

Edit 1: If I grant LOCK TABLES I get this error: java.sql.SQLSyntaxErrorException: UPDATE command denied to user 'testreader'@'localhost' for table 'hibernate_sequence'

So either I need to grant all rights for this spring table, or I am doing something (totally) wrong.

Answer 1

Okay, I have found the solution. Turned out, that my issue was due to the autogenerated ids in my entities.

When replacing:

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)

with:

    @Id
    @GeneratedValue(
        strategy= GenerationType.AUTO,
        generator="native"
    )
    @GenericGenerator(
        name = "native",
        strategy = "native"
    )

I get rid of this hibernate_sequence table and this problem perishes. Regards