Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 7

Displaying posts with tag: error log (reset)

Log Changes with MySQL 5.7
Employee +1 Vote Up -1Vote Down

Most MySQL-ers quickly learn to move logs out of the data directory. Hopefully the logs are being written to a different disk, on a different controller than where the data is being kept. The horror of finding you database server dead to the world because the single partition used for everything was filled up by the error log should be a thing of the past. MySQL 5.7 will give DBAs better control of log files,

As of 5.7.2, we have gained the ability to control the verbosity of error messages with log_error_verbosity. This system variable controls verbosity in writing error, warning, and note messages to the error log. A value of 1 provides errors only, 2 adds warnings, and 3 adds notes. The default value is 3. And with that with level 3, aborted connections and access-denied errors for new connection attempts are written to the error log.

The good

  [Read more...]
Amateurs – They give us professionals a bad name
+3 Vote Up -4Vote Down

Any person with half a brain would see from the error messages below that the MySQL server is not operating optimally, or more specifically the MySQL upgrade has not completely successfully and let users can go happily use the website. It amazing me when web hosting providers tell their paying client that an upgrade has been performed yet they did not have the intelligence to actually look at the error log for confirmation. Got a mysql> prompt, it’s all good. One of the first things I check is the error log.

When will people learn the MySQL error log is a valuable resource both for what it contains, and what it should not contain.

120426 17:36:00 [Note] /usr/libexec/mysqld: Shutdown complete

120426 17:36:00 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
120426 17:36:00 mysqld_safe Starting mysqld daemon with
  [Read more...]
Advanced InnoDB Deadlock Troubleshooting – What SHOW INNODB STATUS Doesn’t Tell You, and What Diagnostics You Should be Looking At
+5 Vote Up -0Vote Down

One common cause for deadlocks when using InnoDB tables is from the existence of foreign key constraints and the shared locks (S-lock) they acquire on referenced rows.

The reason I want to discuss them though is because they are often a bit tricky to diagnose, especially if you are only looking at the SHOW ENGINE INNODB STATUS output (which might be a bit counter-intuitive since one would expect it to contain this info).

Let me show a deadlock error to illustrate (below is from SHOW ENGINE INNODB STATUS\g):

------------------------
LATEST DETECTED DEADLOCK
------------------------
111109 20:10:03
*** (1) TRANSACTION:
TRANSACTION 65839, ACTIVE 19 sec, OS thread id 4264 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 1
MySQL thread id 3, query id 74 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1
  [Read more...]
Do you use MySQL replication? Do you use “FLUSH LOGS”? If yes you might want to read this.
+3 Vote Up -0Vote Down

Scenario: Master-Master replication
Description: Master A is the active db server whilst Master B is a read only swappable db server hence both are creating binary logs. During backup I run “FLUSH LOGS” in order to have a simpler point in time recovery procedure if that case arises.
Problem: Flush logs is mean mean command :) …. it rotates not only my binary logs but my error log too (since I user error-log=blahblahblah in my my.cnf). Well given I flush logs every night my error log is cycled through every night, but unlike binary logs which have an incrimental number attached to the fine, error logs only have a `-log` attached to the filename and a second “FLUSH LOG” would just clear all error logs permanently. That is really not fun believe me!

So what is the solution?



  [Read more...]
Monitoring MySQL – The error log
+3 Vote Up -0Vote Down

It is important that you monitor the MySQL error log. There are a few different options available for defining the details of the log. If not specified the default is [datadir]/[hostname].err. This is not an ideal location for 2 reasons.

First, a correctly configured MySQL datadir will have permissions only for the mysql user, and this is generally restrictive access to the user only and no group or world permissions. Other users/groups should have limited access to the mysql error log.

Second, the datadir is for data, not logs, especially logs that can potentially fill file systems. I am referring here to more then just the error log.

I would recommend you create a separate directory for MySQL logs such as the error, slow and general logs. An example I implement for single installation environments using Linux mysql packages is:

mkdir
  [Read more...]
Writing to the MySQL error log
+0 Vote Up -0Vote Down
In almost all application development situations, one needs to log debug information now and then. In almost all production systems, one needs to log serious error events somewhere too.

So, what can you do? Create a log table? Sprinkle your code with SELECT 'Debug: ...' statements?

At the UDF Repository for MySQL, we now have a solution to log messages to the MySQL error log: a user-defined function called log_error().

Currently it is all very crude: the log_error function takes one argument and writes it to the mysql_error





  [Read more...]
When SHOW SLAVE STATUS and the error log Disagree
+0 Vote Up -0Vote Down

Or, When MySQL Lies!

When I do a show slave status\G, sometimes mysqld will lie to me and give me a wrong Exec_Master_Log_Pos. Let me explain with a situation from last night.

This is the output of show slave status\G from mysql version 5.0.41-community-log:

mysql> show slave status \G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: XXX.XXX.XXX.XXX
                   Master_User: replic_username
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000480
           Read_Master_Log_Pos: 690470773
                Relay_Log_File: db2-relay-bin.000028
                 Relay_Log_Pos: 683977007
      Relay_Master_Log_File:
  [Read more...]
Showing entries 1 to 7

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.