Tag Archives: MySQL

Drop MySQL Tables by Table Prefix

Posted on by
Reply

Problem:

I’ve access to only 1 MySQL database, but I have multiple Magento Installations in it – so naturally, I’m using Table Prefixes to keep them separated. In my case, I have Magento Community Editions 1.6.2.0 and 1.7.0.2.

My table prefixes are ce1620_ and ce1702_, which is simple and easy to understand. The problem is how can I simply remove all tables for only 1 installation?

Solution:

The answer came in the form of a stored procedure. Most of the heavy lifting was done by a nice poster over on StackOverflow.

My little extra ingredient was to disable foreign key constraints, so I could obliterate the whole darn thing in one pass.

As always, be careful and back up before doing something as drastic as this.

DROP PROCEDURE IF EXISTS droplike;
delimiter //
CREATE PROCEDURE droplike(pattern VARCHAR(20))
BEGIN
 SET foreign_key_checks = 0;
 SET group_concat_max_len = 65535;
 SELECT @DROP:= concat( 'drop table ', group_concat(TABLE_NAME) , ';' ) FROM information_schema.TABLES WHERE table_schema = "YOUR_DATABASE_NAME_HERE" AND TABLE_NAME LIKE pattern;
 PREPARE statement FROM @DROP;
 EXECUTE statement;
 SET foreign_key_checks = 1;
END //
delimiter ;

Get An Email List Out of Your osCommerce Database

Posted on by
1

I’ve found it necessary to get an email list out of a few osCommerce stores so that I can export that data to a mailing house.

Please note that this SQL doesn’t look at the customer’s newsletter selection – our external company handles whether or not they wish to receive e-mail from us. So you may have to join the customers table in here.


SELECT DISTINCT customers_company, customers_name, customers_email_address
FROM orders
WHERE date_purchased <= '2008-11-10 23:59:59'
ORDER BY customers_company, customers_name, customers_email_address

It’s probably overkill to have all those fields specified in ORDER BY, but I only have a couple thousand addresses and a big powerful machine. I’m sure MySQL can handle it.

I used phpMyAdmin to execute this query and export to a tab delimited csv.

Request Tracker 3.8.0 vs. MXWest

Posted on by
2

This was an upgrade – well, no, upgrade and move (to another server) – that actually went fairly well. I was able to download the latest version, and export my “old” (RT 3.4.x) database, re-import and upgrade it.

The trick, it seemed, was to use mysqldump instead of phpMyAdmin. Oh, that and also to do increase /etc/my.cnf to increase “max_allowed_packet.” 

I’m pretty impressed with 3.8.0. There’s a lot to recommend it, not least of which is the configurable “Quick Search” which let’s me choose which statuses (statii?) I want to see. “stalled” is a big deal to me. I like being able to quickly see what’s stalled. In my world, that means “something is needed from someone external to us, and until we get it, we stalled.” Being able to see what’s there quickly lets me know who I need to start e-mail-ing or otherwise start annoying to Get Things Done.

The new web2 theme is very nice, but I did go in and change the default font from Helvetica to Arial. Much nicer presentation under Google Chrome (and IE8 and FF3, in my opinion). The Helvetica default is, I guess, a nod to the Mighty Open Sourcers using a Linux desktop, but it does not render well at all on any of my Windows machines.

If you need any help, drop me a line. Request Tracker is a deep, powerful package, capable of Mighty Transformations to Your Business.