WARNING !!!
Before you do anything, mysqldump the entire database or tarball /var/lib/mysql
If you are concerned about case sensitivity, you need to hunt down the usernames in a special way
I tried this little experiment
use test
DROP TABLE rolando;
CREATE TABLE rolando
(id int not null auto_increment,
name varchar(20),primary key (id));
INSERT INTO rolando (name) VALUES
('rolando'),('Rolando'),
('ROLANDO'),('ROLANDO'),
('rolando'),('rolando');
I loaded the sample data
mysql> use test
Database changed
mysql> DROP TABLE rolando;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE rolando
-> (id int not null auto_increment,
-> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO rolando (name) VALUES
-> ('rolando'),('Rolando'),
-> ('ROLANDO'),('ROLANDO'),
-> ('rolando'),('rolando');
Query OK, 6 rows affected (0.08 sec)
Records: 6 Duplicates: 0 Warnings: 0
I ran these queries (please note subtle differences)
mysql> SELECT name,COUNT(1) FROM rolando GROUP BY name;
+---------+----------+
| name | COUNT(1) |
+---------+----------+
| rolando | 6 |
+---------+----------+
1 row in set (0.00 sec)
mysql> SELECT name,MD5(name),COUNT(1) FROM rolando GROUP BY name,MD5(name);
+---------+----------------------------------+----------+
| name | MD5(name) | COUNT(1) |
+---------+----------------------------------+----------+
| ROLANDO | b3f7ba680fe83ab0b5174737e8d536a2 | 2 |
| Rolando | e0732d78dc135b8fcc33ec79bfa89d1f | 1 |
| rolando | e80d4ab77eb18a4ca350157fd487d7e2 | 3 |
+---------+----------------------------------+----------+
3 rows in set (0.00 sec)
mysql> SELECT name,MD5(name)=MD5(LOWER(name)) from rolando;
+---------+----------------------------+
| name | MD5(name)=MD5(LOWER(name)) |
+---------+----------------------------+
| rolando | 1 |
| Rolando | 0 |
| ROLANDO | 0 |
| ROLANDO | 0 |
| rolando | 1 |
| rolando | 1 |
+---------+----------------------------+
6 rows in set (0.00 sec)
mysql> SELECT name,COUNT(1) FROM rolando GROUP BY name;
+---------+----------+
| name | COUNT(1) |
+---------+----------+
| rolando | 6 |
+---------+----------+
1 row in set (0.00 sec)
mysql> SELECT name,(name LIKE lower(name)) like1,
-> (name = binary LOWER(name)) like2 FROM rolando;
+---------+-------+-------+
| name | like1 | like2 |
+---------+-------+-------+
| rolando | 1 | 1 |
| Rolando | 1 | 0 |
| ROLANDO | 1 | 0 |
| ROLANDO | 1 | 0 |
| rolando | 1 | 1 |
| rolando | 1 | 1 |
+---------+-------+-------+
6 rows in set (0.00 sec)
mysql>
Give this view of my experiment, a username with all uppercase or mixed case can be equal to an all lowercase UNLESS YOU USE BINARY
operator with the comparison
I do not think BINARY
can be applied to ON DELETE CASCADE
.
However, you could something like this: To delete all usernames that are lowercase only, try running one of the following:
DELETE FROM logins WHERE username = BINARY LOWER(username);
or
DELETE FROM logins WHERE MD5(username) = MD5(LOWER(username));
If you run this now, you might nix all usernames in the other tables. Run this instead
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM logins WHERE username = BINARY LOWER(username);
or
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM logins WHERE MD5(username) = MD5(LOWER(username));
You will have to go to all tables that have a username in it and execute
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM <whatevertable1> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
DELETE FROM <whatevertable2> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
DELETE FROM <whatevertable3> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
.
.
.
DELETE FROM <whatevertableN> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
Since you have a ton of other tables, use the INFORMATION_SCHEMA to script it for you:
DELETE_SQLFILE=/root/SensitiveDelete.sql
MYSQL_CONN="-uroot -ppassword"
echo "SET FOREIGN_KEY_CHECKS=0;" > ${DELETE_SQLFILE}
mysql ${MYSQL_CONN} -ANe"SELECT CONCAT('DELETE FROM ',table_schema,'.',table_name,' WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP ''\^.*[A-Z].+$''\) FROM information_schema.columns WHERE column_name='user_name'" >> ${DELETE_SQLFILE}
cat ${DELETE_SQLFILE}
If the file is correct to you, run it in the mysql client like this
mysql ${MYSQL_CONN} -A < ${DELETE_SQLFILE}
Give it a Try !!!
Best Answer
I'm only familiar with SQL Server:
Each operation is atomic. If you run a delete, and it cascades to other tables, those records are gone, too, as soon as the statement is over. They don't magically come back into existence unless the transaction is rolled back.
If you're relying on the ID values and don't want to cascade the related tables, consider switching to a merge strategy (where you use
UPDATE
or, preferablyMERGE
) instead of blowing everything away and starting from scratch.I suppose you could try something like disabling the relationships during data loading, but given what I think you're trying to do, that seems fraught with peril. Definitely try a merge strategy first.