FromDual TechFeed (de)

1250 - 1299 1350 - 1399

• Error: 1300 SQLSTATE: HY000 (ER_INVALID_CHARACTER_STRING)

  Message: Invalid %s character string: ‘%s’

• Error: 1301 SQLSTATE: HY000 (ER_WARN_ALLOWED_PACKET_OVERFLOWED)

  Message: Result of %s() was larger than max_allowed_packet (%ld) - truncated

• Error: 1302 SQLSTATE: HY000 (ER_CONFLICTING_DECLARATIONS)

  Message: Conflicting declarations: ‘%s%s’ and ‘%s%s’

• Error: 1303 SQLSTATE: 2F003 (ER_SP_NO_RECURSIVE_CREATE)

  Message: Can’t create a …

1200 - 1249 1300 - 1349

• Error: 1250 SQLSTATE: 42000 (ER_TABLENAME_NOT_ALLOWED_HERE)

  Message: Table ‘%s’ from one of the SELECTs cannot be used in %s

• Error: 1251 SQLSTATE: 08004 (ER_NOT_SUPPORTED_AUTH_MODE)

  Message: Client does not support authentication protocol requested by server; consider upgrading MySQL client

• Error: 1252 SQLSTATE: 42000 (ER_SPATIAL_CANT_HAVE_NULL)

  Message: All parts of a SPATIAL index must be NOT NULL

• Error: 1253 SQLSTATE: 42000 (ER_COLLATION_CHARSET_MISMATCH) …

1150 - 1199 1250 - 1299

  Error: 1200 SQLSTATE: HY000 (ER_BAD_SLAVE)
  Message: The server is not configured as slave; fix in config file or with CHANGE MASTER TO

How does the MySQL error message look like?

  Error: 1201 SQLSTATE: HY000 (ER_MASTER_INFO)
  Message: Could not initialize master info structure; more error messages can be found in the MySQL error log

How does the MySQL error message look like?

  Error: 1202 SQLSTATE: HY000 (ER_SLAVE_THREAD)
  Message: Could not create slave thread; …

1100 - 1149 1200 - 1249

• Error: 1150 SQLSTATE: HY000 (ER_DELAYED_CANT_CHANGE_LOCK)

  Message: Delayed insert thread couldn’t get requested lock for table %s

• Error: 1151 SQLSTATE: HY000 (ER_TOO_MANY_DELAYED_THREADS)

  Message: Too many delayed threads in use

• Error: 1152 SQLSTATE: 08S01 (ER_ABORTING_CONNECTION)

  Message: Aborted connection %ld to db: ‘%s’ user: ‘%s’ (%s)

  Error: 1153 SQLSTATE: 08S01 (ER_NET_PACKET_TOO_LARGE)
  Message: Got a packet bigger than …

1050 - 1099 1150 - 1199

• Error: 1100 SQLSTATE: HY000 (ER_TABLE_NOT_LOCKED)

  Message: Table ‘%s’ was not locked with LOCK TABLES

• Error: 1101 SQLSTATE: 42000 (ER_BLOB_CANT_HAVE_DEFAULT)

  Message: BLOB/TEXT column ‘%s’ can’t have a default value

• Error: 1102 SQLSTATE: 42000 (ER_WRONG_DB_NAME)

  Message: Incorrect database name ‘%s’

• Error: 1103 SQLSTATE: 42000 (ER_WRONG_TABLE_NAME)

  Message: Incorrect table name ‘%s’

• Error: 1104 SQLSTATE: 42000 ( …

1000 - 1049 1100 - 1149

Error: 1050 SQLSTATE: 42S01 (ER_TABLE_EXISTS_ERROR)

Message: Table ‘%s’ already exists

• Error: 1051 SQLSTATE: 42S02 (ER_BAD_TABLE_ERROR)

  Message: Unknown table ‘%s’

• Error: 1052 SQLSTATE: 23000 (ER_NON_UNIQ_ERROR)

  Message: Column ‘%s’ in %s is ambiguous

• Error: 1053 SQLSTATE: 08S01 (ER_SERVER_SHUTDOWN)

  Message: Server shutdown in progress

• Error: 1054 SQLSTATE: 42S22 (ER_BAD_FIELD_ERROR)

  Message: Unknown column ‘%s’ in …

1050 - 1099

• Error: 1000 SQLSTATE: HY000 (ER_HASHCHK)

  Message: hashchk

• Error: 1001 SQLSTATE: HY000 (ER_NISAMCHK)

  Message: isamchk

• Error: 1002 SQLSTATE: HY000 (ER_NO)

  Message: NO

• Error: 1003 SQLSTATE: HY000 (ER_YES)

  Message: YES

• Error: 1004 SQLSTATE: HY000 (ER_CANT_CREATE_FILE)

  Message: Can’t create file ‘%s’ (errno: %d)

• Error: 1005 SQLSTATE: HY000 (ER_CANT_CREATE_TABLE)

  Message: Can’t create table ‘%s’ (errno: %d)

  Error: 1006 SQLSTATE: HY000 …

Just a general rule with error messages: Usually they are not as good as they could be. But nevertheless read them carefully. Most of the time they tell you what you need to know…

And an other wisdom: Errors are really errors and they have to be solved. Warnings are warnings and you should investigate if they appear. Do not ignore both of them unless you have clearly understood what you are doing.

With MySQL:

mysql> SHOW WARNINGS

is often your friend! Further sources to find the problems are: …

FromDual is a support partner of MariaDB Corp. and Oracle Corp.. This gives us the opportunity to offer you customized enterprise support subscriptions for your mission-critical MariaDB, Galera or MySQL databases for all three vendors.

Alternatively, we can offer you our remote DBA services (without SLA) or our MariaDB, Galera and MySQL trainings.

MariaDB and MariaDB Cluster Support Subscriptions

• MariaDB Enterprise support subscription: 7x24 support subscription, duration: 1 to 3 years, unlimited number of …

We dug some further with this problem of too big rows. The results you can find below.

Further discussion we had about PBXT MEMORY tables partitioned:

Q: Is there a good case where partitioned PBXT MEMORY tables could make sense?

A: To have some partitions on disk and other in memory could be useful.

Unfortunately MySQL (up to 5.6.1) and MariaDB (up to 5.2.3) do not support mixing different Storage Engines for a partitioned table yet:

ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL

Lit: CREATE TABLE

Hi Stewart,

Thanks for your comment. Paul made some similar conclusions:

The problem could be that the fixed length record component is too large. PBXT tries to estimate the average row length of a table when it is created. It then sets the fixed length record component to the estimated average row length value.

If the estimate is not good, you can set it explicitly by setting AVG_ROW_LENGTH.

Optimizing the average row length is explained in my talk.

For the moment you can just try AVG_ROW_LENGTH = 12. …

At the DOAG 2010 we found that there are already a lot of Plugins etc. for MySQL available. Therefor it will be a good time to start with a collection of Plugins, UDF’s etc. available.

Because we have done already a collection of MySQL Storage Engines earlier please look there form more information.

Hi Baomin Wang,

After some thinking an talking to some colleagues I found the answer:

When MySQL Cluster writes LCP’s it first writes LCP 1, then LCP 2. After LCP 2 it OVERwrites the LCP 1 again and then overwrites LCP 2 again and so on…

This assures that we have always ONE complete and good LCP from where we can recover in case of a complete Cluster crash.

When we would have only 1 LCP file this cannot be assured.

Regards, Oli

Hi Baomin Wang,

1. I come back with an answer later…

2. MySQL Cluster is mainly an In-Memory-Database. In a typical set-up we do not have such things like tablespaces or data files as in other MySQL Storage engines. Just the LCP- and GCP-files (and some others files related to the MySQL Cluster). MySQL Cluster also knows disk based data (which is a less common set-up). There you have indeed such tablespace and UNDO log files. But I did not consider it in this picture because it is a less often used …

With a partner we are planning and Advanced MySQL DBA workshop. As the name says it should be a workshop. Its planned duration is 2 days. So within 2 days we can cover 4 to 8 topics more deeply.

Requirements: VirtualBox, VMware, own Laptop?

Possible exercises during the workshop

• Set-up a Master-Master replication with 2 Slaves
• Load balance on master with MySQL Proxy and on Slaves with LVS.
• Design a little schema and load with data from foodmart
• Do a backup with XtraDB and LVM
• Do a PITR and and InnoDB crash …

Because I have seen this week again a similar situation I thought about what could cause such InnoDB block corruptions. I came to the following evil-doers:

• Editing the InnoDB tablespace file with a normal text editor.
• Virus checker
• fsck / chkdsk
• Active/active DRBD/SAN (2 mysqld processes accessing the same InnoDB tablespace files at the same time)
• Human interaction (on purpose?)
• Broken I/O controller or other issues with the I/O system

If you have some more ideas. I would be happy to …

What customers say about FromDual

To find out more about the VIEW’s behaviour I was playing around with the ALGORITHM = TEMPTABLE feature [1]. It was also related to a customers question of how VIEW’s effect performance of queries.

The results are ambivalent:

EXPLAIN
SELECT * FROM v LIMIT 10;

CREATE ALGORITHM = MERGE VIEW v
+----+-------------+-------+--------+---------------+---------+---------+-------------+--------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows …

Justin Swanhart from Percona just poked me an told me about FlexViews, a package for Materialized Views he built, based on my ideas. Please have also a look there, possibly it can help you any further…