Orthomcl Incompatibility With Mysql5.5.20

Question

[GR]

Hello Everyone,

Has anyone experienced a problem while running orthomcl with latest version of mysql? When I run the orthomclLoadParser, I got the following errors:

DBD::mysql::st execute failed: The used command is not allowed with this MySQL version at /Users/abc/Desktop/Tools/orthomclSoftware-v2.0.2/bin/orthomclLoadBlast line 39, <f> line 14. The used command is not allowed with this MySQL version

Any solution?

Thanks, R.

[lthiberiol]

Did anyone find a solution yet?

tks,

[GR]

hi tks, if you are getting the same problem then try adding :mysqllocalinfile=1 to the end of the dbConnectString in orthomcl.config file, it was default 0 on my system so orthomcl was not able to load the blast results.

Example: dbConnectString=dbi:mysql:orthomcl:mysqllocalinfile=1

[Jeff Wintersinger]

As per my comment below, the setting should be dbConnectString=dbi:mysql:orthomcl:mysql_local_infile=1 -- note the underscores. This fixed the problem for me on Ubuntu 14.04's mysql-server 5.5.38-0ubuntu0.14.04.1 installation.

[Ginsea Chen]

Great job~ I solved my problem as your description, thanks.

[iakov.davydov]

I'm using mysql 5.6. I had also to add:

loose-local-infile=1

To the [client] section of /etc/mysql/my.cnf.

[lthiberiol]

thanks for the tip, but it didn't work... Did anyone try to install an older MySQL version?

tks,

Answer 1

[JJK]

If you still have issues with MySQL try the following in perl:

system("mysqlimport -u USERNAME -pPASS orthomcl /pathto/SimilarSequences --local");

or from command line:

mysqlimport -u USERNAME -pPASS orthomcl /pathto/SimilarSequences --local

I am also creating an automated application where you have to give in your genome files and a main file takes care of all the steps.

[RvV]

I am having the same problem, too, and this solution does not seem to work for me. I tried the command line above and get the following error message:

mysqlimport: Error: 1146, Table 'orthomcl.SimilarSequences' doesn't exist, when using table: SimilarSequences

Any help would be very much appreciated, thanks!

Answer 2

[peepgina]

I am having the same problem. Did you find a solution? Thanks, G.

Answer 3

[GR]

Hi,

Try adding :mysqllocalinfile=1 to the end of the dbConnectString in orthomcl.config file, it was default 0 on my system so orthomcl was not able to load the blast results.

Example: dbConnectString=dbi:mysql:orthomcl:mysqllocalinfile=1

Let me know if this works well.

Gd luck. Reetu

[Jeff Wintersinger]

I just wish to note that the setting should be dbConnectString=dbi:mysql:orthomcl:mysql_local_infile=1 -- note the underscores were interpreted to indicate italicized text in Reetu's post. This fixed the problem for me on Ubuntu 14.04's mysql-server 5.5.38-0ubuntu0.14.04.1 installation. Before, I was receiving the DBD::mysql::st execute failed: The used command is not allowed with this MySQL version at /home/jeff/.apps/orthomcl/bin/orthomclLoadBlast line 39, <F> line 14. error.

Answer 4

[Juliana Soto]

Hi, I'm having the same problems with mysql and orthomcl. I added :mysqllocalinfile=1 to the end of the dbConnectString in orthomcl.config file, but it did not work for me.

In addition, I also used local-infile=1 in the confi file of mysql using sudo nano /etc/mysql/my.cnf, but still does not wok:

[mysqld]

local-infile=1

[mysql]

local-infile = 1

. other thing was to modify client by adding loose-local-infile=1 [client] loose-local-infile=1

but I have the same error again.

If anyone knows another solution, would be helpful

thanks a lot!

[GR]

Hi Juliana,

Are you getting the same errors?

DBD::mysql::st execute failed: The used command is not allowed with this MySQL version at /Users/abc/Desktop/Tools/orthomclSoftware-v2.0.2/bin/orthomclLoadBlast line 39, <F> line 14. The used command is not allowed with this MySQL version.

few more things to check:-

1. Which version of mysql and perl DBI you are using? Are they both compatible? If your mysql is 5.6 then there are some new features/modifications for InnoDB and orthomcl is dependent on InnoDB. Orthomcl is dependent on innodb and I am not sure if they have modified Innodb in latest version and that's the reason of incompatibility. Please check this.

2. My recent problem was related to InnoDB so I went to /etc/my.cnf and increased the InnoDBbufferPool_size to 1.5G from 8M (but I got this problem at orthomclPairs step).

3. If you have put the orthomcl scripts in /usr/local/bin then it won't be able to work. It is mandatory to keep the software in the same directory. Please check.

Let me now if any of these works for you.

Best, R.

[SK]

Hi RT, I am facing the sme error while using orthomclLoadBlast. DBD::mysql::st execute failed: The used command is not allowed with this MySQL version at /home/shg29ny/opt/orthomcl/orthomclLoadBlast line 39, <f> line 14. I am using suse 12.1 and installed mysql with binaries. To troubleshoot I also tried the option dbConnectString=dbi:mysql:orthomcl:mysqllocalinfile=1 and adding [client] loose-local-infile=1 and others but nothing worked for me. local-infile is set to TRUE. Please help me to debug this error. Many thanks and best regards.

Answer 5

[Juliana Soto]

Hi, thanks a lot for your answer.

the mysql version is 5.5 in ubuntu and the perl DBI is libclass-dbi-sweet perl 0.10-1 (I installed it using Ubuntu Software Center)

I check the /etc/my.cnf and appears this information related with InnoDB:

* InnoDB
 #
 # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
 # Read the manual for more InnoDB related options. There are many!

and i do not see the option "InnoDBbufferPool_size"

about my orthomcl scripts, i have them in the same directory.

I keep on getting the same error.

best regards,

[GR]

try writing to orthomcl help desk. I got problems in mac but not in ubuntu..

Answer 6

[lthiberiol]

I got it!!

What you should append on the dbConnectString is ";mysqllocalinfile=1"! This is how the DBI module understands the --local-infile setting...

Just like this: dbConnectString=dbi:mysql:orthomcl;mysqllocalinfile=1

Answer 7

[kmkocot]

Hi all. I'm also having this problem when I try to run orthomclLoadBlast. I'm using OrthoMCL 2.0.9 on a new system running Ubuntu 13 and I'm using the most recent version of mysql server and client (5.5.35).

Editing orthomcl.config to have a dbConnectString as described above did not work for me. My database is called orthomcl_basal_metazoans so I used that instead of orthomcl. It should be a semicolon and not a colon, right? I tried both and neither worked but I'm just making sure I'm at least doing that right. Here's what that line looks like: dbConnectString=dbi:mysql:orthomcl_basal_metazoans;mysqllocalinfile=1. What am I missing?

Thanks, Kevin

[GR]

If I correctly remember then it should be a colon. This is what I use- dbConnectString=dbi:mysql:orthomcl:mysqllocalinfile=1

[Lisa]

I also recently had the same problem and found that this connect string worked for me, as some of the others suggested just wouldn't work for me for some reason. : dbConnectString=dbi:mysql:orthomcl:mysql_local_infile=1:localhost:3306

[kmkocot]

Thanks Lisa! That worked for me!

Answer 8

[kmkocot]

I wrote to OrthoMCL help and got a helpful reply but I still don't understand. This is what I was told:

Thanks for writing. Other users have encountered this problem as well. In the Orthomcl UserGuide, under step 9, we offer this suggestion, which I hope might help:

NOTE: You might get the following error when you run this command:

"The used command is not allowed with this MySQL version."

The SQL that causes this is LOAD DATA LOCAL INFILE. MySql needs specific configuration to enable this command. See these two pages:

http://dev.mysql.com/doc/refman/5.1/en/load-data-local.html http://dev.mysql.com/doc/refman/5.0/en/loading-tables.html

[kmkocot]

this worked for me: dbConnectString=dbi:mysql:orthomcl_basal_metazoans:mysql_local_infile=1:localhost:3306