MySQL - How to Cope Without COMMIT/ROLLBACK
The following mostly applies only for ISAM, MyISAM, and
HEAP tables. If you only use transaction-safe tables (BDB
tables) in an a update, you can do COMMIT and ROLLBACK
also with MySQL.
The problem with handling COMMIT-ROLLBACK efficiently
with the above table types would require a completely
different table layout than MySQL uses today. The table
type would also need extra threads that do automatic
cleanups on the tables, and the disk usage would be much
higher. This would make these table types about 2-4
times slower than they are today.
For the moment, we prefer implementing the SQL server
language (something like stored procedures). With this
you would very seldom really need COMMIT-ROLLBACK. This
would also give much better performance.
Loops that need transactions normally can be coded with
the help of LOCK TABLES, and you don't need cursors when
you can update records on the fly.
We at TcX had a greater need for a real fast database
than a 100% general database. Whenever we find a way to
implement these features without any speed loss, we will
probably do it. For the moment, there are many more
important things to do. Check the TODO for how we
prioritize things at the moment. (Customers with higher
levels of support can alter this, so things may be
The current problem is actually ROLLBACK. Without
ROLLBACK, you can do any kind of COMMIT action with LOCK
TABLES. To support ROLLBACK with the above table types,
MySQL would have to be changed to store all old records
that were updated and revert everything back to the
starting point if ROLLBACK was issued. For simple cases,
this isn't that hard to do (the current isamlog could be
used for this purpose), but it would be much more
difficult to implement ROLLBACK for ALTER/DROP/CREATE
To avoid using ROLLBACK, you can use the following
Use LOCK TABLES ... to lock all the tables you want to
Update if everything is okay.
Use UNLOCK TABLES to release your locks.
This is usually a much faster method than using
transactions with possible ROLLBACKs, although not
always. The only situation this solution doesn't handle
is when someone kills the threads in the middle of an
update. In this case, all locks will be released but
some of the updates may not have been executed.
You can also use functions to update records in a single
operation. You can get a very efficient application by
using the following techniques:
Modify fields relative to their current value.
Update only those fields that actually have changed.
For example, when we are doing updates to some customer
information, we update only the customer data that has
changed and test only that none of the changed data, or
data that depend on the changed data, has changed
compared to the original row. The test for changed data
is done with the WHERE clause in the UPDATE statement.
If the record wasn't updated, we give the client a
message: "Some of the data you have changed have been
changed by another user". Then we show the old row
versus the new row in a window, so the user can decide
which version of the customer record he should use.
This gives us something that is similar to column
locking but is actually even better, because we only
update some of the columns, using values that are
relative to their current values. This means that
typical UPDATE statements look something like these:
UPDATE tablename SET pay_back=pay_back+'relative
customer_id=id AND address='old address' AND phone='old
As you can see, this is very efficient and works even if
another client has changed the values in the pay_back or
In many cases, users have wanted ROLLBACK and/or LOCK
TABLES for the purpose of managing unique identifiers
for some tables. This can be handled much more
efficiently by using an AUTO_INCREMENT column and either
the SQL function LAST_INSERT_ID() or the C API function
At MySQL AB, we have never had any need for row-level
locking because we have always been able to code around
it. Some cases really need row locking, but they are
very few. If you want row-level locking, you can use a
flag column in the table and do something like this:
UPDATE tbl_name SET row_flag=1 WHERE id=ID;
MySQL returns 1 for the number of affected rows if the
row was found and row_flag wasn't already 1 in the
You can think of it as MySQL changed the above query to:
UPDATE tbl_name SET row_flag=1 WHERE id=ID and row_flag
MySQL - General Security
Anyone using MySQL on a computer connected to the
Internet should read this section to avoid the most
common security mistakes.
In discussing security, we emphasize the necessity of
fully protecting the entire server host (not simply the
MySQL server) against all types of applicable attacks:
eavesdropping, altering, playback, and denial of
service. We do not cover all aspects of availability and
fault tolerance here.
MySQL uses Access Control Lists (ACLs) security for all
connections, queries, and other operations that a user
may attempt to perform. There is also some support for
SSL-encrypted connections between MySQL clients and
servers. Many of the concepts discussed here are not
specific to MySQL at all; the same general ideas apply
to almost all applications.
When running MySQL, follow these guidelines whenever
DON'T EVER GIVE ANYONE (EXCEPT THE MySQL ROOT USER)
ACCESS TO THE mysql.user TABLE! The encrypted password
is the real password in MySQL. If you know this for one
user you can easily login as him if you have access to
Learn the MySQL access privilege system. The GRANT and
REVOKE commands are used for restricting access to
MySQL. Do not grant any more privileges than necessary.
Never grant privileges to all hosts. Checklist:
Try mysql -u root. If you are able to connect
successfully to the server without being asked for a
password, you have problems. Any user (not just root)
can connect to your MySQL server with full privileges!
Review the MySQL installation instructions, paying
particular attention to the item about setting a root
Use the command SHOW GRANTS and check to see who has
access to what. Remove those privileges that are not
necessary using the REVOKE command.
Do not keep any plain-text passwords in your database.
When your computer becomes compromised, the intruder can
take the full list of passwords and use them. Instead
use MD5() or another one-way hashing function.
Do not use passwords from dictionaries. There are
special programs to break them. Even passwords like
``xfish98'' are very bad. Much better is ``duag98''
which contains the same word ``fish'' but typed one key
to the left on a standard QWERTY keyboard. Another
method is to use ``Mhall'' which is taken from the first
characters of of each word in the sentence ``Mary had a
little lamb.'' This is easy to remember and type, but
hard to guess for someone who does not know it.
Invest in a firewall. This protects from at least 50% of
all types of exploits in any software. Put MySQL behind
the firewall or in a demilitarized zone (DMZ).
Try to scan your ports from the Internet using a tool
such as nmap. MySQL uses port 3306 by default. This port
should be inaccessible from untrusted hosts. Another
simple way to check whether or not your MySQL port is
open is to type telnet server_host 3306 from some remote
machine, where server_host is the hostname of your MySQL
server. If you get a connection and some garbage
characters, the port is open, and should be closed on
your firewall or router, unless you really have a good
reason to keep it open. If telnet just hangs, everything
is OK, the port is blocked.
Do not trust any data entered by your users. They can
try to trick your code by entering special or escaped
character sequences in Web forms, URLs, or whatever
application you have built. Be sure that your
application remains secure if a user enters something
like ``; DROP DATABASE mysql;''. This is an extreme
example, but large security leaks and data loss may
occur as a result of hackers using similar techniques,
if you do not prepare for them. Also remember to check
numeric data. A common mistake is to protect only
strings. Sometimes people think that if a database
contains only publicly available data that it need not
be protected. This is incorrect. At least
denial-of-service type attacks can be performed on such
databases. The simplest way to protect from this type of
attack is to use apostrophes around the numeric
constants: SELECT * FROM table WHERE ID='234' instead of
SELECT * FROM table WHERE ID=234. MySQL automatically
converts this string to a number and strips all
non-numeric symbols from it. Checklist:
All WWW applications:
Try to enter `'' and `"' in all your Web forms. If you
get any kind of MySQL error, investigate the problem
Try to modify any dynamic URLs by adding %22 (`"'), %23
(`#'), and %27 (`'') in the URL.
Try to modify datatypes in dynamic URLs from numeric
ones to character ones containing characters from
Your application should be safe against this and similar
Try to enter characters, spaces, and special symbols
instead of numbers in numeric fields. Your application
should remove them before passing them to MySQL or your
application should generate an error. Passing unchecked
values to MySQL is very dangerous!
Check data sizes before passing them to MySQL.
Consider having your application connect to the database
using a different user name than the one you use for
administrative purposes. Do not give your applications
any more access privileges than they need.
Users of PHP:
Check out the addslashes() function.
Users of MySQL C API:
Check out the mysql_escape() API call.
Users of MySQL++:
Check out the escape and quote modifiers for query
Users of Perl DBI:
Check out the quote() method.
Do not transmit plain (unencrypted) data over the
Internet. These data are accessible to everyone who has
the time and ability to intercept it and use it for
their own purposes. Instead, use an encrypted protocol
such as SSL or SSH. MySQL supports internal SSL
connections as of Version 3.23.9. SSH port-forwarding
can be used to create an encrypted (and compressed)
tunnel for the communication.
Learn to use the tcpdump and strings utilities. For most
cases, you can check whether or not MySQL data streams
are unencrypted by issuing a command like the following:
shell> tcpdump -l -i eth0 -w - src or dst port 3306 |
(This works under Linux and should work with small
modifications under other systems). Warning: If you do
not see data this doesn't always actually mean that it
is encrypted. If you need high security, you should
consult with a security expert.
Page Numbers : 1