Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11238

CONNECT engine table_type=JDBC causes mysqldump --all-databases to fail

Details

    Description

      When CONNECT is set up for use with JDBC table types it causes mysqldump with the option --all-databases to fail.

      It appears the reason is that CONNECT with JDBC creates a database called:

      #mysql50#.oracle_jre_usage
      

      The above shows up as a database but cannot be accessed via MySQL Workbench nor apparently backed up by mysqldump --all-databases

      Attachments

        1. bogusdb.jpg
          187 kB
          Robert Dyas
        2. Workbench.PNG
          174 kB
          Olivier Bertrand
        3. 2016-11-06 (4).png
          228 kB
          Robert Dyas

        Activity

          rdyas Robert Dyas created issue -
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          Assignee Olivier Bertrand [ bertrandop ]

          I see nothing in CONNECT that creates such a database as "#mysql50#.oracle_jre_usage".
          Without more details about this and what is done mysqldump, I am afraid I can cannot do anything about this case.

          bertrandop Olivier Bertrand added a comment - I see nothing in CONNECT that creates such a database as "#mysql50#.oracle_jre_usage". Without more details about this and what is done mysqldump, I am afraid I can cannot do anything about this case.
          rdyas Robert Dyas made changes -
          Attachment bogusdb.jpg [ 42863 ]
          rdyas Robert Dyas added a comment -

          Yes, very strange. I'll attach a MySQL Workbench screen shot.

          You don't see that database on your CONNECT JDBC install?

          I have two separate servers, and the #mysql50#.oracle_jre_usage database shows on both.

          rdyas Robert Dyas added a comment - Yes, very strange. I'll attach a MySQL Workbench screen shot. You don't see that database on your CONNECT JDBC install? I have two separate servers, and the #mysql50#.oracle_jre_usage database shows on both.
          bertrandop Olivier Bertrand added a comment - - edited

          No. Even after executing some JDBC queries, some against the Oracle JDBC driver, no such data base appears in the database list.
          (See attachment Workbench.JPG)
          I am running on Windows 7 32bits.

          bertrandop Olivier Bertrand added a comment - - edited No. Even after executing some JDBC queries, some against the Oracle JDBC driver, no such data base appears in the database list. (See attachment Workbench.JPG) I am running on Windows 7 32bits.
          bertrandop Olivier Bertrand made changes -
          Attachment Workbench.PNG [ 42864 ]
          rdyas Robert Dyas made changes -
          Attachment 2016-11-06 (4).png [ 42865 ]
          rdyas Robert Dyas added a comment - - edited

          Very strange... but I think I have figured out what is happening (but don't know what to do about it yet).

          Take a look at the following...It appears the #mysql50# is an internal MySQL thing:

          http://g33kinfo.com/info/archives/5080

          Then take a look at the following... it appears that .oracle_jre_usage is a folder created by the JRE in your the user's home directory when it runs:

          https://community.oracle.com/thread/3783686

          I think what is happening, on Linux anyway, is that when the JRE is started by CONNECT for JNI it is running as the mysql user and therefore creating a folder called .oracle_jre_usage in the home directory of the mysql user. On centOS 7 it is in fact sitting right there alongside real database folders:

          /var/lib/mysql/.oracle_jre_usage

          Since its a folder it thinks its a database, and since it can't figure it out it is labeling it as incompatiable so putting the #mysql50# prefix on it. The question now is, how to prevent it from creating that when it runs, or have it place it in a different folder.

          It appears to be created by the Java Usage Tracker which is turned off by default. See details here:

          https://docs.oracle.com/javacomponents/usage-tracker/overview/

          Since this is off by default, I'm guessing CONNECT is turning it on with a command like the following when starting Java:

          java -Dcom.oracle.usagetracker.config.file=/path/usagetracker.properties MyApplication

          And the path is the same directory on linux that holds the databases.

          Does that make sense? Can it be fixed in time for 10.1.19?
          We are excited to push this into production.
          Thank you!

          rdyas Robert Dyas added a comment - - edited Very strange... but I think I have figured out what is happening (but don't know what to do about it yet). Take a look at the following...It appears the #mysql50# is an internal MySQL thing: http://g33kinfo.com/info/archives/5080 Then take a look at the following... it appears that .oracle_jre_usage is a folder created by the JRE in your the user's home directory when it runs: https://community.oracle.com/thread/3783686 I think what is happening, on Linux anyway, is that when the JRE is started by CONNECT for JNI it is running as the mysql user and therefore creating a folder called .oracle_jre_usage in the home directory of the mysql user. On centOS 7 it is in fact sitting right there alongside real database folders: /var/lib/mysql/.oracle_jre_usage Since its a folder it thinks its a database, and since it can't figure it out it is labeling it as incompatiable so putting the #mysql50# prefix on it. The question now is, how to prevent it from creating that when it runs, or have it place it in a different folder. It appears to be created by the Java Usage Tracker which is turned off by default. See details here: https://docs.oracle.com/javacomponents/usage-tracker/overview/ Since this is off by default, I'm guessing CONNECT is turning it on with a command like the following when starting Java: java -Dcom.oracle.usagetracker.config.file=/path/usagetracker.properties MyApplication And the path is the same directory on linux that holds the databases. Does that make sense? Can it be fixed in time for 10.1.19? We are excited to push this into production. Thank you!
          rdyas Robert Dyas added a comment -

          Any progress on fixing this?
          It is kind of a major bug as it causes full server backups to fail.

          Possibly just removing the usage tracker might be enough (assuming you only use it for debug).

          rdyas Robert Dyas added a comment - Any progress on fixing this? It is kind of a major bug as it causes full server backups to fail. Possibly just removing the usage tracker might be enough (assuming you only use it for debug).

          To my knowledge, CONNECT does not do anything concerning the usage tracker.

          bertrandop Olivier Bertrand added a comment - To my knowledge, CONNECT does not do anything concerning the usage tracker.
          rdyas Robert Dyas added a comment -

          I've found how to fix this problem, but I really think it needs to be documented for CONNECT JDBC for others.

          Per the Oracle documentation (https://docs.oracle.com/javacomponents/usage-tracker/overview/) the
          "Usage Tracker is disabled by default. Enable it by creating the properties file <JRE directory>/lib/management/usagetracker.properties". This turns out to be WRONG on their part as the file does exist by default on a new installation, and the existence of this file enables the usage tracker.

          The solution on CentOS 7 with the Oracle JVM is to rename or delete the usagetracker.properties file (to disable it) and then delete the bogus folder it created in the mysql database directory, then restart.

          For example, the following works:

          sudo mv /usr/java/default/jre/lib/management/management.properties /usr/java/default/jre/lib/management/management.properties.TRACKER-OFF
          sudo reboot
          sudo rm -rf  /var/lib/mysql/.oracle_jre_usage
          sudo reboot
          

          If this is not done, the Oracle JVM will start the usage tracker, which will create the hidden folder .oracle_jre_usage in the mysql home directory, which will cause a mysql dump of the server to fail.

          So I think you can just document this and call it resolved.

          rdyas Robert Dyas added a comment - I've found how to fix this problem, but I really think it needs to be documented for CONNECT JDBC for others. Per the Oracle documentation ( https://docs.oracle.com/javacomponents/usage-tracker/overview/ ) the "Usage Tracker is disabled by default. Enable it by creating the properties file <JRE directory>/lib/management/usagetracker.properties". This turns out to be WRONG on their part as the file does exist by default on a new installation, and the existence of this file enables the usage tracker. The solution on CentOS 7 with the Oracle JVM is to rename or delete the usagetracker.properties file (to disable it) and then delete the bogus folder it created in the mysql database directory, then restart. For example, the following works: sudo mv /usr/java/ default /jre/lib/management/management.properties /usr/java/ default /jre/lib/management/management.properties.TRACKER-OFF sudo reboot sudo rm -rf /var/lib/mysql/.oracle_jre_usage sudo reboot If this is not done, the Oracle JVM will start the usage tracker, which will create the hidden folder .oracle_jre_usage in the mysql home directory, which will cause a mysql dump of the server to fail. So I think you can just document this and call it resolved.

          A special paragraph was added to the documentation.

          bertrandop Olivier Bertrand added a comment - A special paragraph was added to the documentation.
          bertrandop Olivier Bertrand made changes -
          issue.field.resolutiondate 2016-12-16 11:38:24.0 2016-12-16 11:38:24.497
          bertrandop Olivier Bertrand made changes -
          Fix Version/s 10.0.29 [ 22312 ]
          Fix Version/s 10.1.21 [ 22113 ]
          Fix Version/s 10.2.3 [ 22115 ]
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          rdyas Robert Dyas added a comment -

          I made a mistake; this hidden folder comes back after some usage.
          I'm still trying to figure out how to prevent this from happening.

          rdyas Robert Dyas added a comment - I made a mistake; this hidden folder comes back after some usage. I'm still trying to figure out how to prevent this from happening.
          rdyas Robert Dyas added a comment -

          FYI After much looking I cannot find a way to prevent this hidden folder from being created by the java usage tracker in a Linux environment (not an issue on windows).

          However, I have found and tested extensively a fairly simple solution: just move the mysql data dir to another location. set the datadir var in /etc/my.cnf to another location (not the home folder) and the problem appears solved.

          rdyas Robert Dyas added a comment - FYI After much looking I cannot find a way to prevent this hidden folder from being created by the java usage tracker in a Linux environment (not an issue on windows). However, I have found and tested extensively a fairly simple solution: just move the mysql data dir to another location. set the datadir var in /etc/my.cnf to another location (not the home folder) and the problem appears solved.

          I don't understand. Do you mean that even you disabled the usage tracker by renaming or deleting the usagetracker.properties file after the Java installation, this fake folder is still created?

          bertrandop Olivier Bertrand added a comment - I don't understand. Do you mean that even you disabled the usage tracker by renaming or deleting the usagetracker.properties file after the Java installation, this fake folder is still created?
          rdyas Robert Dyas added a comment -

          Yes, exactly.
          People complained and they moved the folder on osx and Windows but not Linux apparently.
          It appears to create the folder even when disabled. This behavior apparently started around java 1.8u60. It persists in 1.8u112.

          rdyas Robert Dyas added a comment - Yes, exactly. People complained and they moved the folder on osx and Windows but not Linux apparently. It appears to create the folder even when disabled. This behavior apparently started around java 1.8u60. It persists in 1.8u112.

          Because this folder is recognizable by its funny name, another solution would be to post a request to MariaDB to ignore it, in particular when doing a mysqldump.

          bertrandop Olivier Bertrand added a comment - Because this folder is recognizable by its funny name, another solution would be to post a request to MariaDB to ignore it, in particular when doing a mysqldump.
          rdyas Robert Dyas added a comment -

          It shows up in show databases as well.
          You might try passing the parameter that specifies the property file on jni startup with a blank parameter. Specifics are listed in the file itself. This may, or may not, prevent the folder from being created.

          rdyas Robert Dyas added a comment - It shows up in show databases as well. You might try passing the parameter that specifies the property file on jni startup with a blank parameter. Specifics are listed in the file itself. This may, or may not, prevent the folder from being created.
          bertrandop Olivier Bertrand added a comment - - edited

          Possible but as the Oracle documentation says that usage tracker is disabled by default this is probably because on Linux it creates the folder unconditionally.

          However, I still think that it is a flow in MariaDB to regard any sub folder of the data directory as a database. There should be a way to eliminate not real ones, for instance by adding a specific file in the folder when the CREATE DATABASE is executed.

          Meanwhile, if it is possible to manually remove this folder, a workaround could be to do it before executing a mysqldump, which by the way is probably not executed so frequently.

          bertrandop Olivier Bertrand added a comment - - edited Possible but as the Oracle documentation says that usage tracker is disabled by default this is probably because on Linux it creates the folder unconditionally. However, I still think that it is a flow in MariaDB to regard any sub folder of the data directory as a database. There should be a way to eliminate not real ones, for instance by adding a specific file in the folder when the CREATE DATABASE is executed. Meanwhile, if it is possible to manually remove this folder, a workaround could be to do it before executing a mysqldump , which by the way is probably not executed so frequently.
          rdyas Robert Dyas added a comment - - edited

          Sort of agree on " I still think that it is a flaw in MariaDB to regard any sub folder of the data directory as a database." but also think that it would not be an issue if MariaDB default install didn't use the Linux home folder as its data directory. It could use a subdir of its home dir like ~/data or ~/mysqldata or maybe use /var/lib/mysqldata ... not sure. But using the home directory itself seems like a bad default install practice.

          So far, the work around that seems to be not a kludge for me is simply to change the location of the mysql data dir.

          rdyas Robert Dyas added a comment - - edited Sort of agree on " I still think that it is a flaw in MariaDB to regard any sub folder of the data directory as a database." but also think that it would not be an issue if MariaDB default install didn't use the Linux home folder as its data directory. It could use a subdir of its home dir like ~/data or ~/mysqldata or maybe use /var/lib/mysqldata ... not sure. But using the home directory itself seems like a bad default install practice. So far, the work around that seems to be not a kludge for me is simply to change the location of the mysql data dir.
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 78213 ] MariaDB v4 [ 151191 ]

          People

            bertrandop Olivier Bertrand
            rdyas Robert Dyas
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.