Category Archives: mysql

Pear DB and “DB Error: already exists” On Insert

Note that this post relates to using a MySQL database in conjunction with the PEAR DB module, or any other PEAR module that depends on DB – like DB_TABLE (which I’ve been enjoying).

You might run into this error if you switch over from using PHP’s built-in MySQL functions, over to a database abstraction layer, which is what I did on a recent project. After much banging-the-head-on-the-desk, I finally figured out that PEAR DB doesn’t use the same auto-increment sequence that the actual MySQL engine does.

For example, you may have a database using a CMS that doesn’t use PEAR. You do your various inserts, and inevitably, your deletes. So, your auto-increment column might start out in a perfect sequence like 1 – 2 – 3 – 4 – 5, etc. But then say the first two get deleted, so now your sequence id’s are 3 – 4 – 5. But then you insert again, and your CMS, using the DBMS directly, knows to use 6 for the next auto-increment value.

But then say you switch over to PEAR DB, and why wouldn’t you? It’s great – and readily bundled into most PHP installations. But then you notice, taking the above example, that your next insert starts at 1. Then the next insert produces an id of 2. And then you get to 3 and WHOA what happens, “DB Error: already exists” that’s wtf happens. PEAR DB ignores MySQL’s auto-increment sequence and makes its own (jump down to the notes/warnings in red).

You may also notice that you have table names with “_seq” at the end.

Why is this? I have no idea – but it’s stooooopid. Is there some reason for this? Seriously, please enlighten me! I mean, what’s an example of where you’d like to use more than 1 sequence?

seqSo what to do? You need to alter those “_seq” tables, that’s what. So if you’re having trouble inserting into a table named “products” – get MySQL’s “Next Autoindex” value, which you can easily do in phpMyAdmin on a table’s “structure” tab (see the screenshot, where it’s 20).

Now, go into the products_seq table, and change the id to that number (20 in this case). Now you shouldn’t have any problems.

So the moral of the story is, if you start out using a PEAR database abstraction layer, you’ll never have this problem. But if you’re unlucky enough to not have done that in the first place and switch over to it, you may have this issue and this is a quick fix.

Finding Duplicates in MySQL

I get quite a few Excel files from clients that need to get cleaned up and inserted in to MySQL. Sometimes the import goes smoothly, sometimes it doesn’t. This last time, I had 3,741 rows in an Excel file, but somehow wound up with 3,751 rows in the database – what went wrong? Don’t know, and don’t have time to figure it out – so I just used this query to isolate all the duplicates.

My database table consists of nothing more than an id, a part number (number), a description, and a price. Here’s the query that located all duplicate part numbers – from there I was able to easily delete them:

SELECT number, 
 COUNT(number) AS NumOccurrences
FROM part_prices_new
GROUP BY number
HAVING ( COUNT(number) > 1 )

Using the Shell and Cron to Automate Your MySQL Backups

This little shell script has proven to be a handy way to backup my local databases:

#!/bin/bash
cd '/folder/to/store/your/backups/'
for x in db-name-a db-name-b db-name-c
do
   mysqldump --user=username --password=pass $x --lock-tables --add-drop-table > $x`date +%u`.sql;
done

So what’s up with this little snippet? The first line with the ‘cd’ commad, we’re just changing into the directory where all the sql dumps will be stored. I also store this little script in there (backup.sh).

The next line is a basic loop. we list all of the local database names we want to backup, right after the “for x in” phrase, separated by a space.  The indented line is what actually dumps, names, and saves the file. On my local development computer, I just pass in the root username and pass, so it will work for each database. Each backup file then follows the “db-name” + “day of week” naming convention. For example, db-name-a1.sql would be Monday’s backup, db-name-a2.sql would be Tuesday’s backup, etc. A week’s worth of backups is fine for me, though you can customize this to your liking.

And the command line, you can test if this is working like so (assuming you are in the same directory as the file):

$ sh db-backup.sh

Not Working?

If it’s not working, a couple things may be happening. You can alter the script with more conditionals to output some general errors, but OS X will actually mail you a detailed copy of the error. Getting at it can be a little cryptic though. At the prompt, type mail:

$ mail

If you see a message like “You have mail in /var/mail/your-username” then you should check it out. You can use the terminal to check it (man mail to check the manual entry for mail). Or, what I tend to do is just open the entire mail file in my text editor, which is TextMate – using the command line tool:

$ mate /var/mail/your-username

Some juicy debugging info may be in there. You can delete everything in the file and save it if  you like. This will get rid of the “You have mail in…” message whenever you start up terminal.

Automating It

I have this script run everyday at 3pm – I do that with a cron job, using Cronnix as a graphical interface:

cronnix

One problem you may have is that your crontab’s path is not necessarily the same as your user account’s shell path. For example, if you are seeing an error like “mysqldump – command not found” – simply go back to the terminal, and enter “which mysqldump”:

$ which mysqldump

It will output the full path to this command. Copy that path and paste the full path into your script. So for example, the line following the “do” command may look like:

/Applications/MAMP/Library/bin/mysqldump --user=user --password=pass $x --lock-tables --add-drop-table > $x`date +%u`.sql;

I use the MAMP version of mysql dump, so I need to tell the script exactly what version to use.

From here, I use SVN to keep this folder under version control as well, which makes switching from the desktop to the laptop much easier when developing. Cheers!

phpMyAdmin: Bypass File Upload Size Limit

When you want to import a large SQL query in phpMyAdmin, you can run into problems as the default upload size limit is restricted by whatever it is in the php.ini file – and often, you cannot change this or simply don’t want to. It tends to default to a measly 2MB.

However, there’s a feature that will allow you to FTP your SQL file to an upload directory of your choosing, and then instruct phpMyAdmin to run the query file. Here’s how you do it.

  1. Open up your FTP program, and navigate to the phpmyadmin folder on your server. Inside that folder is a file called config.inc.php – open this up in your trusty text editor.
  2. While you’re still in your FTP program – create a directory inside the phpmyadmin directory called upload:
    phpmyadmin-trans.png
  3. Towards the bottom of the config file, you’ll notice a potential setting for $cfg['UploadDir']. Change it so that it reads the following:
    phpmyadmin-config.png
    Don’t forget to add the “./” before “upload” – this just tells the program to look in the existing directory (phpmyadmin) for a folder called “upload” – there’s no need to change file permissions or anything.
  4. Save it, and then go back to your ftp program, and upload a sql file to that directory.
  5. When you go to the Import tab in phpMyAdmin, you’ll now see a drop-down list of available files within your upload directory. Pick the one you want to run.
    phpmyadmin2.png
  6. Sit back, have some coffee.

Some Notes

  • You can compress the file, which will certainly make your FTP upload time shorter. I found that gzip works best for that. Sometimes a zip file would give me an error.
  • Note that you can still run into trouble if your query is HUGE. If your php execution time and/or timeout settings are not big enough in your php.ini file, executing your query can be problematic. However, you can always chop up your data inserts into numerous files, although that can be a pain. (Or, if you have SSH access, you can execute the query file that way.) I encountered this problem while trying to import a big Drupal database. I solved it be emptying (NOT dropping) all the search-related database tables, and then re-indexing the site once all the other data was inserted.

That’s it – I was pleased to discover this feature and hope it helps someone else out too!