New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MySQL: enabling lease causes errors #3866

Closed
ebruchez opened this Issue Dec 31, 2018 · 13 comments

Comments

Projects
2 participants
@ebruchez
Copy link
Collaborator

ebruchez commented Dec 31, 2018

I am using the latest driver (mysql-connector-java-5.1.47-bin.jar) but I am getting this.

+----------------------------------------------------------------------------------------------------------------------+
|An Error has Occurred                                                                                                 |
|----------------------------------------------------------------------------------------------------------------------|
|This command is not supported in the prepared statement protocol yet                                                  |
|----------------------------------------------------------------------------------------------------------------------|
@ebruchez

This comment has been minimized.

Copy link
Collaborator Author

ebruchez commented Dec 31, 2018

This might not be a regression, I am not sure.

@ebruchez

This comment has been minimized.

Copy link
Collaborator Author

ebruchez commented Dec 31, 2018

We should have automated tests checking that our SQL passes with all databases.

@avernet

This comment has been minimized.

Copy link
Collaborator

avernet commented Dec 31, 2018

Somehow I can't reproduce this. I was using mysql-connector-java-8.0.11.jar against MySQL 5.7, and also tried with mysql-connector-java-5.1.47.jar, but the lease seems to work fine for me. Specifically, a user can acquire a lease, another user see the document has already been leased, and the original user can relinquish the lease.

@avernet

This comment has been minimized.

Copy link
Collaborator

avernet commented Dec 31, 2018

I suggest that for now we leave this open for 2018.2.1, and we can discuss this one in the coming weeks to figure out if there really is an issue, or if it is a just a "local configuration issue".

@avernet

This comment has been minimized.

Copy link
Collaborator

avernet commented Jan 25, 2019

Steps to reproduce:

  1. Set the properties below
  2. In Form Builder, create a form, publish it
  3. Open the /new page, save
  4. Set the header My-Credentials-Header to { "username" : "hsimpson" }
  5. Clear the JSESSIONID
  6. Load the form data previously saved

With this, I am getting the following, which is as expected, and doesn't seem to reproduce the issue @ebruchez is seeing.

screen shot 2019-01-25 at 11 47 11 am

<property
    as="xs:string"
    name="oxf.fr.persistence.provider.*.*.*"
    value="mysql"/>
<property
    as="xs:string"
    name="oxf.fr.authentication.method"
    value="header"/>
<property
    as="xs:boolean"
    name="oxf.fr.detail.lease.enabled.*.*"
    value="true"/>
@ebruchez

This comment has been minimized.

Copy link
Collaborator Author

ebruchez commented Jan 29, 2019

So I did this:

  • created new MySQL database with the 2018.2 DDL
  • downloaded 2018.2 PE
  • setup the properties above
  • followed the steps
  • still getting the same error
@ebruchez

This comment has been minimized.

Copy link
Collaborator Author

ebruchez commented Jan 29, 2019

Maybe @avernet create a new fresh MySQL database?

@ebruchez ebruchez closed this Jan 29, 2019

Orbeon Forms 2018.2.1 automation moved this from To do to Done Jan 29, 2019

@avernet avernet reopened this Jan 30, 2019

@avernet

This comment has been minimized.

Copy link
Collaborator

avernet commented Jan 30, 2019

Still unable to reproduce the problem. So, here is how I do the first step "created new MySQL database with the 2018.2 DDL":

  1. Start a clean container with MySQL 5.7: docker run -d -p 3306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:5.7 --sql-mode=ALLOW_INVALID_DATES --character-set-server=utf8mb4 --collation-server=utf8mb4_bin
  2. Run docker ps to get the container id, then docker exec -i -t CONTAINER_ID bash
  3. Run the 3 commands in Setting up users and schema
  4. Connect as orbeon with mysql -u orbeon -p, enter orbeon as password, USE orbeon, paste the content of mysql-2018_2.sql, finally exit twice
  5. Run Tomcat with the <Resource> below

@ebruchez You'll let me know if you can reproduce the issue with a clean database created this way.

<Resource
    name="jdbc/mysql" driverClassName="com.mysql.jdbc.Driver"
    auth="Container" type="javax.sql.DataSource"
    initialSize="3" maxActive="10" maxIdle="10" maxWait="30000"
    poolPreparedStatements="true" testOnBorrow="true" validationQuery="select 1"
    username="orbeon" password="orbeon"
    url="jdbc:mysql://localhost:3306/orbeon"/>
@avernet

This comment has been minimized.

Copy link
Collaborator

avernet commented Jan 31, 2019

I am wondering if this could be caused by the new call to SHOW VARIABLES LIKE, in commonPart.scala. This should be easy to check, and if the case it will also be easy to change that code to bypass creating a prepared statement. Next step:

  • @ebruchez Check what statement is causing this error

@ebruchez ebruchez moved this from Done to To do in Orbeon Forms 2018.2.1 Jan 31, 2019

@ebruchez

This comment has been minimized.

Copy link
Collaborator Author

ebruchez commented Feb 4, 2019

The error appears to be in the s"LOCK TABLES $tableName WRITE" command.

@ebruchez

This comment has been minimized.

Copy link
Collaborator Author

ebruchez commented Feb 4, 2019

But in this specific case, we don't need a prepared statement I think.

@ebruchez

This comment has been minimized.

Copy link
Collaborator Author

ebruchez commented Feb 4, 2019

Replacing the PreparedStatement with a good old Statement works.

Note that I am using MySQL 5.7.15.

@ebruchez

This comment has been minimized.

Copy link
Collaborator Author

ebruchez commented Feb 4, 2019

So I suggest switching to using createStatement in this case. This should work with the other databases as well, I hope.

@ebruchez ebruchez closed this in 68f9348 Feb 4, 2019

Orbeon Forms 2018.2.1 automation moved this from To do to Done Feb 4, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment