Tag Archive: MySQL


no such file to load — mysql

from http://blogs.sun.com/divas/entry/no_such_file_to_load

Rails never fails me. As soon as I publish a series of tutorials for a NetBeans release, a new Rails release comes out which breaks my tutorials (and, apparently, most everyone else’s). Gratefully, some customers took the time to click the Feedback button at the end of our tutorials to let us know the tutorials were no longer working. One customer wrote:

“When trying to create the database following the steps indicated, I got an error indicating the mysql gem had been removed from Rails 2.2.2 and that I should install the mysql gem. (Note from me: the actual error message is “The bundled mysql.rb driver has been removed from Rails 2.2. Please install the mysql gem and try again: gem install mysql.”) However, that gem is also native and can’t be installed with JRuby (the misleading directive to do so from NetBeans was really annoying).”

I have to agree that this is indeed REALLY annoying. However, the “misleading directive” comes from Rails, and not from NetBeans. And, since Rails 2.2 came out after NetBeans 6.5, this was an unexpected message.

While the directive may be true for Ruby, it is not true for JRuby, because the the MySQL adapter is included with JRuby. In addition, as the user pointed out, you can’t install the mysql gem with JRuby because you cannot use any gems that build native C libraries during the install process, and that includes the mysql gem.

I did some googling and found two solutions:

  • Use the activerecord-jdbcmysql-adapter. You can find the instructions on how to do that here.
  • Copy the MySQL driver to your project’s lib folder. If you have an older Rails version, you can copy the Ruby MySQL driver (mysql.rb) from rails-install-dir/gems/activerecord-version/lib/active_record/vendor/mysql.rb to the project’s lib directory. Unfortunately, you have to do this for every JRuby project.

If anyone else has any other solutions, please post a comment.

P.S. For all you Ruby programmers, when you try out a tutorial, you really should pay attention to what gem versions the tutorial was written for. Gem releases such as Rails and RSpec are not always backwards compatible, so there is a chance that the tutorial will not work with releases that came out after the tutorial was written.

Happen on version 5.1.7, so switch back to 5.1.6 is the solution…

From http://bugs.mysql.com/bug.php?id=41448

Bug #41448 java.sql.SQLException: !Statement.GeneratedKeysNotRequested!
Submitted: 13 Dec 2008 18:22 Modified: 28 May 20:29
Reporter: Rico H.
Status: Documenting
Category: Connector/J Severity: S1 (Critical)
Version: 5.1.7 OS: Linux
Assigned to: Target Version:
[13 Dec 2008 18:22] Rico H.
Description:
Hello,

Consider the following code:

PreparedStatement ps = connection.prepareStatement("INSERT INTO table values(?,?)");
ps.setInt(1,value1);
ps.setInt(2,value2);
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys(); // This throws a java.sql.SQLException:
!Statement.GeneratedKeysNotRequested!

Even when creating the PreparedStament in this other way the same exception is thrown:

PreparedStatement ps = connection.prepareStatement("INSERT INTO table
values(?,?)",PreparedStatement.RETURN_GENERATED_KEYS);
ps.setInt(1,value1);
ps.setInt(2,value2);
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys(); // This ALSO throws a java.sql.SQLException:
!Statement.GeneratedKeysNotRequested!

According to some posts I read on the Internet, this might be related to Bug #34185, as it
seems was pushed into the current 5.1.7 version and the problem didn't happen before.

I guess the problem just occurs when using PreparedStaments, as with Statements you can
provide the RETURN_GENERATED_KEYS flag when executing the query. However, flags for
PreparedStaments can only be provided when "Preparing the Statement". So the following
code works well:

Statement stmt =
connection.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_
READ_ONLY);
stmt.executeUpdate("INSERT INTO table values(1,2)",Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys(); //This works

This is breaking lots of apps, and should be fixed as soon as possible.

Thank you very much!!

How to repeat:
See description!

from http://ondra.zizka.cz/stranky/programovani/java/index.texy

When working with MySQL over JDBC and the driver encounters a zero DATE, TIME, or DATETIME value (that is, e.g, ‚0000–00–00‘ for DATE), an exception is thrown:

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 4 to TIMESTAMP.

In this case, using SQL commands like

SET GLOBAL sql_mode = 'NO_ZERO_DATE';

does not help much, because that works only in „strict SQL mode“, and needs to be set for every connection, or globally for the whole server.

What helps is setting JDBC driver’s zeroDateTimeBehavior property to convertToNull:

What should happen when the driver encounters DATETIME values that are composed entirely of zeroes (used by MySQL to represent invalid dates)? Valid values are “exception”, “round” and “convertToNull”.

The way to set it depends on the way you configure JDBC driver. The most common case is to use connection URL parameters. In my case it reads:
jdbc:mysql://localhost/test?zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=UTF-8&characterSetResults=UTF-8

MySQL: Get Next Auto_increment

MySQL: Get Next Auto_increment
Thursday April 24th, 2003

** It is not recommended to use this method for generating foreign keys for lookups. In a multiuser environment with simultaneous requests the results will be utterly useless and unreliable. OK? **

So I was working on the preview feature of the home grown content management system for this site, and I came across an interesting problem for which my friend Google had no answers.

The way the preview feature works is that when editing an entry, you hit the PREVIEW button, and the script posts the edited data unto itself and then writes it to a temp file named {section}_{entry ID}.php. When the posted page loads, it opens a new window with the template for the given section, and the POST data is processed in the content well, just as any other entry would be.

The problem is that if you want to create a NEW entry, you don’t have an entry ID to assign to the tempfile name, and therefore the preview feature does not work. So I needed a way of finding out what MySQL would assign as the next auto_increment entry ID if I were to actually insert a record. My first thought was to simply get the highest existing ID from the table and add 1, but that is not a perfect solution. For one thing, there is the possibility that another author might insert a new record during the time that I’m previewing, which will cause some confusion — we might end up with two instances of the temp file overwriting one another. No good. Also, the highest existing ID + 1 is NOT necessarily the next auto increment ID, because when entries are deleted, the entry ID is not reused. So it’s conceivable that if your highest ID is 500, the next ID might be 501, or it might be 5001 for all we know.

I was on a mission.

So, I looked all over the web for a solution, and found several similar scenarios described, but no answers. In fact, I came across several forums and help sites where it was actually stated as fact that you CANNOT get the next auto_increment. I knew this to be false, because this information is displayed in the table structure view of phpMyAdmin. So I scoured the source code of phpMyAdmin, and after many desperate combinations of GREPs, FINDs, and XARGSs, I finally came up with this: (assumes we’re already connected to the dB)

And the rundown:

“SHOW TABLE STATUS” produces a two-dimensional array with one row for each table, and a slew of columns: Name, Type, Row_format, Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, etc.

So what I’m doing here is using mysql_fetch_assoc() to get the “Auto_increment” value.

And there you have it. Our next likely entry ID, without inserting anything.

edited by JD on 2/14/06

Oh the drama

Posted by Jamie
Comments: 65

MySQL error 1017: errno: 24

From Re: Mysqldump error 1017: errno: 24 - help?
Yep, that seems to have done it, at least when I'm testing it.  I'm
pretty sure the problem is fixed, but the next couple of days of
automated backups will tell.

Thanks!

On Tue, 2005-01-11 at 00:43, Gleb Paharenko wrote:
> Hello.
>
> Try '--open-files-limit=8192' at least. Check the real value of
> open_file_limits with such statement:
>   show variables like '%open_f%';
>
> You can find some recommendations for SuSe Linux at:
>   http://dev.mysql.com/doc/mysql/en/Linux-post-install.html
>
> Mysql user <mysql@stripped> wrote:
> > Hi..
> >
> > I've got an ISP, and all of our customers have databases in our mysql
> > system.
> >
> > My backup command is:
> >
> > mysqldump --force --opt -A -pxxxxxxxx' | gzip -c >
> > /var/sqlbackup/mysqldump-`date +\%A`.sql.gz
> >
> > This has worked fine for some time.
> >
> > Now, however, I get an error message:
> >
> > mysqldump: Got error: 1017: Can't find file:
> > './usr_web22_1/invoices_va.frm' (errno: 24) when using LOCK TABLES
> >
> > On different runs, it reports different files, even right after
> > restarting mysqld.
> >
> > I've looked at the mysql documentation for this error; it's at
> > http://dev.mysql.com/doc/mysql/en/Not_enough_file_handles.html
> >
> > I've looked at the startup script, put in '--open-files-limit=1024',
> > restarted mysql, and the error is the same.
> >
> > I've looked at the table_cache and max_connections system variables,
> > which are 64 and 100, respectively.
> >
> > My question is: what should I do now?
> >
> > Since table_cache and max_connections are far less than 1024, do I
> > reduce them even further?
> > Is there something else I should be looking at?
> >
> > This is mysql-3.23.52-106 on SuSE Linux 8.1, running on a dual PIII
> > 866Mhz system, with 512M of ram and 1G of swap, on a RAID-1 pair of 17G
> > hard drives.
> >
> > Thanks!
> >
> >
>
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
>    __  ___     ___ ____  __
>   /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
>  / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
>        <___/   www.mysql.com
> 
Powered by WordPress. Theme: Motion by 85ideas.