3,067 reputation
1720
bio website karwin.com
location California
age 47
visits member for 3 years
seen 11 hours ago

I'm Senior Knowledge Manager at Percona, a services company specializing in the MySQL database.

I've been a software engineer since 1987, and my specialty is as an SQL maven. I also have experience programming in Java, PHP, Perl, C, JavaScript, and I have many other coding skills.

I've written a book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming from Pragmatic Bookshelf, based on the most common SQL problems I've answered on Stack Overflow and other forums, mailing lists, and newsgroups over the past 15 years.


Jul
15
answered Percona MySQL 5.5 Unique key is duplicated
Jul
15
revised Converting to MyISAM, `foreign_key_checks = 0` Not Working
added 13 characters in body
Jul
14
answered Converting to MyISAM, `foreign_key_checks = 0` Not Working
Jul
7
awarded  Custodian
Jul
7
reviewed Approve suggested edit on InnoDB create table error: “Row size too large”
Jul
3
answered mysqld is not producing binary log
Jul
2
comment How to transfer 70GB MySQL with no downtime?
Another option for quick backups of MyISAM databases is LVM snapshots. A good tool to help is mylvmbackup. But your data directory needs to be on a volume capable of snapshots already.
Jun
30
awarded  Revival
Jun
20
comment Database Normalization - Can one field point to another?
As ypercube and mustaccio mentioned, a physical locator to the row is subject to change, as InnoDB moves rows around during updates. Besides, a foreign key typically references the primary key of the parent table, which is the clustered index of that table, and therefore pretty quick for lookups.
Jun
20
revised Converting MSSQL Stored Procedure to MySQL - Syntax Error
added 90 characters in body
Jun
20
comment Database Normalization - Can one field point to another?
And how large would such a pointer need to be, given that it would have to identify the tablespace, the page, the row, and column it references? Hint: almost certainly larger than the value you're likely to be referencing.
Jun
20
answered Converting MSSQL Stored Procedure to MySQL - Syntax Error
Jun
20
answered Database Normalization - Can one field point to another?
Jun
18
comment mysql replication missed quite a lot SQL statement
Keep in mind that replicate-ignore-db causes statements to be skipped if the current default db was ignored. So if you use mysql then insert into otherdb.sometable then that insert will be skipped.
Jun
18
answered MySQL Foreign Key Prevents Deleting Table — But Table is Empty
Jun
12
comment Cannot add foreign key constraint while creating table
@Lennart, in standard SQL, you have to use the whole key and it must be a unique or primary key. InnoDB is more permissive, allowing the referenced index to be non-unique, or allowing the referenced columns to be the left-prefix subset of an index. That's non-standard behavior. I don't recommend doing that, because you get into weird situations like a child row that references multiple rows in the parent. Down that path lies only madness!
Jun
12
comment Cannot add foreign key constraint while creating table
I tried your case in MySQL 5.6 and it worked fine, but I had to create also tables Movie and Rent_Status. InnoDB does support NO ACTION syntax. Note that the column referenced by a foreign key must be the leftmost column in a key in its table.
Jun
8
comment Amount of data in a database table
This is tangential to your question, but in most cases a FLOAT or DOUBLE column should be DECIMAL instead.
Jun
4
revised Cannot execute statement: impossible to write to binary log?
edited tags
Jun
4
comment Only “grant usage”, but can still select, drop, create?
FWIW, this is fixed in Percona Server. I.e. we just don't add those rows in our distribution.