I would like to create a copy of a database with approximately 40 InnoDB tables and around 1.5GB of data with mysqldump and MySQL 5.1. What are the best parameters (ie: --single-transaction) that will result in the quickest dump and load of the data? As well, when loading the data into the second DB, is it quicker to: 1) pipe the resu...
I’m trying to get a large result set to stream and having no luck. The MySQL docs are somewhat unclear as to whether this should even work. E.g.: When using versions of the JDBC driver earlier than 3.2.1, and connected to server versions earlier than 5.0.3, the setFetchSize() method has no effect, other than to toggle result set streami...
I have a rails app that I have serving up XML on an infrequent basis. This is being run with mongrel and mysql. I've found that if I don't exercise the app for longer than a few hours it goes dead and starts throwing Errno::EPIPE errors. It seems that the mysql connection get timed out for inactivity or something like that. It can be re...
What is the SQL command to copy a table from one database to another database? I am using MySQL and I have two databases x and y. Suppose I have a table in x called a and I need to copy that table to y database. Sorry if the question is too novice. Thanks. ...
Running OS X Leopard an MacBook Pro from Jan. 2008. I used to run mysql server from a package but then rails started putting a warning that I should install mysql from gem: gem install mysql It did not work, I got the following error message: Building native extensions. This could take a while... ERROR: Error installing mysql: ER...
I need to move entire tables from one MySQL database to another. I don't have full access to the second one, only phpMyAdmin access. I can only upload (compressed) sql files smaller than 2MB. But the compressed output from a mysqldump of the first database's tables is larger than 10MB. Is there a way to split the output from mysqldump i...
Is it possible to do a SELECT statement with a predetermined order, ie. selecting IDs 7,2,5,9 and 8 and returning them in that order, based on nothing more than the ID field? Statements SELECT id FROM table WHERE id in (7,2,5,9,8); and SELECT id FROM table WHERE id in (8,2,5,9,7); both return them in the same order. ...
Can anyone recommend a MySQL front end client or OS X that is AppleScriptable? Preferably low cost or free. ...
What is the best method to backup large (4 GB) MySQL databases. I currently use a cron job every night that mysqldumps everything and I keep 3 days of backups. Also what backup mechanism allows me to restore a backup faster. ...
I have this PHP code that I am trying to use to let a user edit a news record in a form and then when they hit the submit button, it will update the record in a database. The problem is that everything works but the record is not actually updated in the database. Could someone look at my code and see where a problem could occur? <?php ...
I got a datetime column in mysql which I need to convert to mm/dd/yy H:M (AM/PM) using PHP. ...
There are so many Linux distributions to choose from! What is the "best" linux flavor for a web hosting environment running primarily: Apache HTTP, Tomcat or JBoss, MySQL and Alfresco (not necessarily all in the same instance). Are there any significant differences in terms of ease of administration and configuration, performance and s...
For a new project that I'm doing in PHP I've created an SQLMethods class to connect to the database and perform queries. Tonight was the first night that I actually got to test it (I wrote it a week or so ago and forgot about it) and an unexpected error occured: When it was calling my ExecuteQuery() function, it wouldn't use the database...
I have a very simple bit of script that changes the status of an item in a MySql database - it works fine in IE7, but if I try it in Firefox, it looks like it's worked, but hasn't... Which is extremely odd. The code is very simple - first I get the details of the record I'm looking for: <cfscript> // Get the Product Attribute details A...
I need to get UTF-8 working in my Java webapp (servlets + JSP, no framework used) to support äöå etc. for regular Finnish text and Cyrillic alphabets like ЦжФ for special cases. My setup is the following: Development encironment: Windows XP Production encironment: Debian Database used: MySQL 5.x Users mainly use Firefox2 but also O...
It often happens that characters such as é gets transformed to é, even though the collation for the MySQL DB, table and field is set to utf8_general_ci. The encoding in the Content-Type for the page is also set to UTF8. I know about utf8_encode/decode, but I'm not quite sure about where and how to use it. I have read the "The Absolute...
I refactored a slow section of an application we inherited from another company to use an inner join instead of a subquery like where id in (select id from ... ) The refactored query runs about 100x faster. (~50 seconds to ~0.3) I expected an improvement, but can anyone explain why it was so drastic? The columns used in the where clau...
How do the full text search systems of PostgreSQL and MySQL compare? Is any clearly better than the oder? In which way are they different? ...
I have a fairly good feel for what MySQL replication can do. I'm wondering what other databases support replication, and how they compare to MySQL and others? Some questions I would have are: Is replication built in, or an add-on/plugin? How does the replication work (high-level)? MySQL provides statement-based replication (and row-...
I have seen random rows pulled using queries like this, which are quite inefficient for large data sets. SELECT id FROM table ORDER BY RANDOM() LIMIT 1 I have also seen various other RDBMS-specific solutions that don't work with MySQL. The best thing I can think of doing off-hand is using two queries and doing something like this. ...