A Problems and Common Errors

This chapter lists some common problems and error messages that users have run into. You will learn how to figure out what the problem is, and what to do to solve it. You will also find proper solutions to some common problems.

Subsections

• A.1 How to Determine What Is Causing Problems
• A.2 Common Errors When Using MySQL
• A.2.1 Access denied Error
• A.2.2 MySQL server has gone away Error
• A.2.3 Can't connect to [local] MySQL server Error
• A.2.4 Client does not support authentication protocol error
• A.2.5 Host '...' is blocked Error
• A.2.6 Too many connections Error
• A.2.7 Some non-transactional changed tables couldn't be rolled back Error
• A.2.8 Out of memory Error
• A.2.9 Packet too large Error
• A.2.10 Communication Errors / Aborted Connection
• A.2.11 The table is full Error
• A.2.12 Can't create/write to file Error
• A.2.13 Commands out of sync Error in Client
• A.2.14 Ignoring user Error
• A.2.15 Table 'xxx' doesn't exist Error
• A.2.16 Can't initialize character set xxx error
• A.2.17 File Not Found
• A.3 Installation Related Issues
• A.3.1 Problems When Linking with the MySQL Client Library
• A.3.2 How to Run MySQL As a Normal User
• A.3.3 Problems with File Permissions
• A.4 Administration Related Issues
• A.4.1 What To Do If MySQL Keeps Crashing
• A.4.2 How to Reset a Forgotten Root Password
• A.4.3 How MySQL Handles a Full Disk
• A.4.4 Where MySQL Stores Temporary Files
• A.4.5 How to Protect or Change the MySQL Socket File `/tmp/mysql.sock'
• A.4.6 Time Zone Problems
• A.5 Query Related Issues
• A.5.1 Case-Sensitivity in Searches
• A.5.2 Problems Using DATE Columns
• A.5.3 Problems with NULL Values
• A.5.4 Problems with alias
• A.5.5 Deleting Rows from Related Tables
• A.5.6 Solving Problems with No Matching Rows
• A.5.7 Problems with Floating-Point Comparison
• A.6 Optimiser Related Issues
• A.6.1 How to avoid table scan,,,
• A.7 Table Definition Related Issues
• A.7.1 Problems with ALTER TABLE.
• A.7.2 How To Change the Order of Columns in a Table
• A.7.3 TEMPORARY TABLE problems

User Comments

To encrypt replication over an insecure (inter)net: Get <a href=http://stunnel.org>stunnel</a> and install it (after <a href=http://openssl.org>OpenSSL</a> if you don't already have it) on both machines.

On the slave's my.cnf under [mysqld] put:
master-host=localhost
master-port=3307

Start stunnel on the slave with:
stunnel -c -d 3307 -r mastername.com:3307

Start stunnel on the master with:
stunnel -p /path/to/ca.pem -d 3307 -r localhost:3306

Make any necessary adjustments to your firewalls to allow tcp port 3307 in and out. Restart mysql on both systems. With any luck you're in business.

The instructions for this select statement appear
to be incorrect, thus generating the 1052 error
just mentioned. Try the following, it seemed to
produce the correct result:
select shop.article, dealer, shop.price,
tmp.article, tmp.price from shop, tmp where
shop.article = tmp.article and shop.price =
tmp.price;

Do not forget add function definition to
item_create.h ;)

I would recommend the web interface. Connecting to the server from a remote host is possible, but client app would have to use a username and password to do so. If the client app resides on a web browser machine (like a Java applet), it can be captured and decompiled, thus acquiring the username and password. Also, the username and password could be intercepted in transit. The best idea IMHO is to use a CGI program resident on the same machine as MySQL to interface with the database.

You'll get crazy if you try to do the Master-Slave / Slave-Master... I can't see the point of doing it... but the H.A. for MySQL above is quite simple if you use Non-MySQL resources to do it. Like (slave-ping-master, master-is-down, slave-get-master's-name-and-IP, slave-send-email-to-sysadm)
then try to do'it with MySQL resorces...

Please, add a comment for every server parameter;
is really difficult to tune a MySQL server in low memory conditions. An accurate description can be useful. A few parameters are described, a lot of them are simply reported.

Hi, very cool stuff except for one little thing :)

Transactions dont work with replication :(

Maybe you were working on the 2 projects concurrently, or maybe you wanted to get the basics right first.

Or maybe someone forgot :(

Either way, the BEGIN, ROLLBACK and COMMIT statements are not Logged and therefore not replicated.

This means that if you do an insert on one database it is either automatically committed (autocommit on) to the other or never committed (autocommit=0). whether you commit or rollback has no effect.

Any likely hood of a fix quickly?

cheers and keep up the good work!

The part of the manual that covers mysqldump is lacking an example with the syntax usage. For instance I have been trying to dump a table to a text CSV file with the command :
mysqldump -h localhost -u root -ppassword --field-terminated-by=';' db_parts
tbl_id > tbl_id.csv
and it gives an error saying that "mysqldump: You must use
option --tab with --fields-..."
Therefore, it is hard to get the correct syntax usage.
I hope that it fixed in the future, specialy for new users.
Thank you,
Carlos Fernando Scheidecker Antunes.

This section needs a couple good examples. A good SELECT .. INTO and slurp the resulting file would help. An example of the using various FIELD TERMINATED style options on the command line would be nice.

# to get you started!jpyeron@pyerotechnics.com
gcc -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME="\"/\"" -
DDATADIR="\"/var/lib/mysql\""
-DSHAREDIR="\"/usr/share/mysql\"" -
DHAVE_CONFIG_H -DDBUG_OFF -
I/usr/src/redhat/BUILD/mysql-
3.23.43/bdb/build_unix -
I/usr/src/redhat/BUILD/mysql-
3.23.43/innobase/include -
I/usr/src/redhat/BUILD/mysql-3.23.43/include -
I/usr/src/redhat/BUILD/mysql-3.23.43/regex -
I/usr/src/redhat/BUILD/mysql-3.23.43/sql -
I/usr/src/redhat/BUILD/mysql-3.23.43/include -
I/usr/src/redhat/BUILD/mysql-3.23.43 -O3 -felide-
constructors -fno-exceptions -fno-rtti -fno-
implicit-templates -fno-exceptions -fno-rtti -
shared -o myfile.so src src src obj src obj etc

Hi Everyone,

We need to switch to MySql from MS Access. I am not able to update the date column in my recordset. I use the following :
With mDb
.CursorLocation = adUseClient
.ConnectionString = "Driver={MySQL ODBC 3.51 Driver};SERVER=;DATABASE = XXX;UID=;PWD=;option =2"
.Mode = adModeReadWrite
.Open "XXX"
End With

Dim mRS As Recordset
Set mRS = New ADODB.Recordset
mRS.CursorLocation = adUseClient

With mRS
.Source = "SELECT * From Student where StudentNo = 50"
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.ActiveConnection = mDb
End With

mRS.Open
mRS.MoveFirst
mRS("BirthDate").Value = Date
mRS.Update

It gives me this error "Row cannot be loacated for updating. Some values may have been changed since it was last read."

Has anyone come across anything like this. I am able to update other columns of type (String / VarChar / Number), But updating a date gives me this error. Please let me know if someone has come accross this situation. It will be really appreciated.

Add your own comment.