Bobby Tables and the lesson he teaches
SQL injection vulnerabilities are often been described as the most serious threat for Web applications, regardless of what language they are written in . Web applications that are vulnerable to SQL injection may allow an attacker to gain complete access to their underlying databases, regardless of if they are using MySQL Server, SQL Server, or Oracle.
SQL-injection attacks are those in which data provided by the user is included in an SQL query in such a way that part of the user’s input is treated as SQL code that is executed on the server. By using this technique, an attacker can submit SQL commands directly to the database. These attacks are a serious threat to any Web application that receives input from users and passes it into SQL queries to an underlying database server. If user input is not santised properly, web applications may result in SQL Injection attacks that allow hackers to view information from the database and/or even wipe it out.
To defend against SQL Injection attacks, user input must not directly be embedded into SQL statements that are executed on the server. Instead, you must use parameterized statements, and Escaping functions to check user input.
Various resource for addressing SQL Injection are as follows:
Bobby Tables provides real-world practical code for addressing SQL Injection - http://bobby-tables.com/
Michal Daw’s Blog Page outlines various SQL Injection vectors - http://michaeldaw.org/sql-injection-cheat-sheet
I have run into many situations where there would be a benefit to not replicate a large INSERT or UPDATE from the MASTER to SLAVE in order to not block replication for a long period of time. Luckily MySQL provides a means to disable binary logging for your current session. Simply execute SET SQL_BIN_LOG=0 before the SQL Statement that you do not want logged to the BINLOG. This is a session variable, meaning it will be re-enabled when you close the session, or you can set it back to 1.
In order to keep the Slave in-sync, you will need to execute the same query on the Slave. You can also use mk-table-sync from the Maatkit toolkit to re-sync your table data at a later time.
I recently had a client who was using a table structure that utilized a self-referential structure using an ID field and Parent_ID fields. After upgrading to Innodb, his DBA was trying to utilize a foreign key constraint to perform cascading UPDATE and DELETE statements. After much heart-ache, they called me in to find out why it was not working. Unfortunately, I had to share with them the fact that MySQL does not support this type of use of constraints on self-referential tables.
This deviation from SQL standards results affects an ON UPDATE CASCADE or ON UPDATE SET NULL that recurses to update the same table it has previously updated during the cascade. Instead of cascading, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations in MySQL. According to the Innodb developers, this is to prevent infinite loops resulting from cascaded updates, although I would think this could be addressed in future versions. A self-referential ON DELETE SET NULL, on the other hand, is still possible, as is a self-referential ON DELETE CASCADE.
The Prodromus Group has announced a plan to perform Pro Bono MySQL DBA work for qualified organizations. As part of our mission, we feel that it is important to give back to our community, both locally and globally. If your organization believes that it may qualify, please contact us at dba@prodromus.com. While we specialize in MySQL, our organization has extensive experience in IT operations and building high quality public facing web sites at an affordable price. We recently helped a local charitable firm develop a web site to solicit on-line donations, and they now receive over 50% of their donations via their website.
Examples of qualified organizations would be those focusing on Environmental and Human Rights initiatives, local schools, fire and police agencies, etc. Our goal is to help these organization receive quality technical advice and service that they may otherwise be unable to afford.
Contact us today at dba@prodromus.com
If you have seen this error, don’t worry, you are not alone. This error is often accompanied by a message that a table or file could not be created, and usually happens when creating a foreign key. In my experience, 99% of the time this is due to an incompatibility between the two fields in the foreign key. Usually it is something simple like unsigned integer to signed integer. The trickiest I have seen is when trying to create a foreign key between two CHAR fields and they do not share the same CHARSET and COLLATE. ALTER the table so that the CHARSET and COLLATE are the same, and try to add the foreign key again.
Let me know if you have other examples of how you have worked around errno: 150 when adding foreign keys.
The number one thing that you can do to speed up Slave Replication is to set innodb_flush_log_at_trx_commit=0 in your my.cnf file. This will make the transactions less recoverable on your Slave in case of a crash, however with a Slave this is usually an acceptable risk. This setting prevents MySQL from forcing a fsync after every transaction, allowing transactions to be batched up and all fsynced in one operation. When using slower HD RAID’s, this is a huge performance benefit.
Setting sync_binlog=0 will also prove to be beneficial, but also at some level of additional risk.
Using ALTER IGNORE TABLE [TABLENAME] ADD UNIQUE INDEX `UNIQUE_INDEX` ([FIELDNAME]) to remove duplicate rows in a table is a fast an efficient process, however on large tables where the physical size is larger than server memory, the ALTER statement can take a long time to run in a production environment.
If you need to remove duplicates on a very large table (we recently used this on a table of 77 million rows), try this method :
delete t1 from table t1, table t2
where t1.duplicate_field= t2.duplicate_field (add more if need ie. and t1.duplicate_field2=t2.duplicate_field2)
and t1.unique_field > t2.unique_field
and breakup into ranges to run faster
If you use an auto-incrementing ID field as the primary key, use this as your unique field, and in the Where clause to run on a range of records to break into smaller operations.
I recently ran into a situation where a Delete across a large time period was taking an extensive amount of time to run when replicated to the Slave, although the query ran very quickly when running on the Master. On the Master, the query took <2 minutes, while on the Slave, we finally killed the slave replication thread after 4 hours of run time, and issued a Stop Slave. As an added challenge, the table we were updating had 77 million rows and was 10G in size.
After much head scratching and verifying that indexes existed on both Master and Slave, we took a few minutes to compare the query execution plan using EXPLAIN. It was quickly obvious that the Slave was using a different index than the Master when executing the query. We quickly update the table statistics using Analyze Table, and re-ran EXPLAIN. After the Analyze Table, the problem was solved, and after restarting replication using Start Slave, the offending query quickly executed in 2 minutes.
Moral of the Story: Running Analyze Table on larger tables every couple days is probably a good idea.
The fastest and easiest way to delete duplicate records is my issuing a very simple command.
alter ignore table [tablename] add unique index `unique_index` ([fieldname])
What this does is create a unique index on the field that you do not want to have any duplicates. The ignore syntax instructs MySQL to not stop and display an error when it hits a duplicate. This is much easier than dumping and reloading a table.
This also will work, but is not as elegant:
delete from [tablename] where fieldname in (select a.[fieldname] from
(select [fieldname] from [tablename] group by [fieldname] having count(*) > 1 ) a )
We have been using Percona 5.1.43 for sometime now and have found it to be a great MySQL Build. It includes many high performance patches, and full support for XtraDB which is a tuned InnoDB implementation.
To install painlessly, create a file called Percona.repo in your /etc/yum.repos.d directory. Add the following to the file:
[percona]
name=CentOS-$releasever – Percona
baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/
gpgcheck=0
Then install using Yum or Apt.
This release includes:
Performance improvements
- Improved buffer_pool scalability
- Fast recovery
- Improved IO path
- Improved rollback segment scalability
- Separate purge thread
- Limited size of data dictionary in memory
- Increased number of concurrent write transactions (undo slots) ( up to 4000 )
- Fast checksums ( in release process )
- Support of different pagesizes ( 4K, 8K, 16K) ( in release process )
Usability / operations
- Show content of buffer_pool
- Import / export of dedicated tables
- Import / export of buffer_pool
- Transactional replication
- Show internal InnoDB data dictionary
- Show InnoDB locking/io profiling in slow.log
