PlanetMysql.ru — информация о СУБД MySQL

Background Knowledge

I using PHP v5.3.3-7 PDO running a MySQL v14.14 Distrib 5.1.49 on Debian v6.0.4 64-bit and executing a SQL load data infile statement.

I received “PHP Warning: PDOStatement::execute(): SQLSTATE[HY000]: General error: 126 Incorrect key file for table ‘/tmp/#sql_66f_0.MYI’; try to repair it”. My database table in this instance is using the storage engine of InnoDB and therefore one can not use the “repair table”.

From my experience I’ve found that this error can mean one of two issues however I have not found information from MySQL confirming this.

Solution – Repair Table

The error message may mean the database table is corrupted and requires a repair.

1. Run repair table on the associated database table.
2. Re-run the previous query.
3. The issue will now be resolved.

Solution – Free Disk Space

The error message may mean the system does not have enough free disk space.

1. Verify the free disk space. On Linux execute at the console “df -h”. On Windows open “My Computer” to see free space available.
2. If low or out of disk space, free up some space or follow the next steps to change MySQL server setting to point to a new temporary path.
3. Edit the MySQL server configuration file, my.cnf (on Debian, /etc/mysql/my.cnf).
4. Change the “tmpdir” path variable to a new location with more available free disk space (ex. /var/tmp or D:\temp).
5. Stop MySQL daemon/service and then start MySQL daemon/service for the configuration changes to take affect.

Background Knowledge

The progress of a task in TaskFreak! is shown as a percentage value and is not exactly visually appealing to quickly spot the progress. With a few minor alterations we can show the percentage completed bar that fills as the task progresses and a gradient bar indicating the progress along with the percentage value.

This solution was posted by Searcher at Re: Taskfreak Customizing Status.

Solution

1. Edit at line #268 as shown below.
   

   Cod Before

   268	<th width="<?php echo FRK_STATUS_LEVELS * 2; ?>%" onclick="freak_sort('statusKey')" colspan="< ?php echo FRK_STATUS_LEVELS ?>" class="sortable">< ?php echo (FRK_STATUS_LEVELS == 1)?'X':$langForm['status']; ?></th>

   Code After

   268	<th width="80" onclick="freak_sort('statusKey')" class="sortable">< ?php echo (FRK_STATUS_LEVELS == 1)?'X':$langForm['status']; ?></th>

2. Edit at line #382
   

   Code Before

   < ?php
                       $s = $objItem->itemStatus->statusKey;
                       for ($i = 0; $i < FRK_STATUS_LEVELS; $i++) {
                           $j = ($i < $s)?(FRK_STATUS_LEVELS - $i):0;
                   ?>
                           <td id="est<?php echo ($i+1).$objItem->id; ?>" class="sts< ?php echo $j; ?>"< ?php
                           if ($objUser->checkLevel(14) || $objItem->checkRights($objUser->id,8,true))  {
                               echo ' onclick="freak_sts('.$objItem->id.','.($i+1).')" style="cursor:pointer"';
                           }
                       ?>>&nbsp;</td>
                   < ?php
                       }
                   ?>

   Code After

   <!-- status bar update -->
             <td>
               <table width="100%" cellpadding="0" cellspacing="0">
                 <tr>
                   < ?php
                   $s = $objItem->itemStatus->statusKey;
                   for ($i = 0; $i < FRK_STATUS_LEVELS; $i++) {
                     $j = ($i < $s)?(FRK_STATUS_LEVELS - $i):0;
                     ?>
                     <td width="10" onmouseover="this.style.cursor='pointer'" id="est<?php echo ($i+1).$objItem->id; ?>" class="sts< ?php echo $j; ?>"< ?php if ($objUser->checkLevel(14) || $objItem->checkRights($objUser->id,8,true))  { echo ' onclick="freak_sts('.$objItem->id.','.($i+1).')" style="cursor:pointer"'; } ?>>&nbsp;</td>
                     < ?php } ?>
                     <td style="border:0"></td>
                     <td id="status_perc_<?php echo $objItem->id; ?>" style="border:0; font-size:8px; color:#000" align="right">&nbsp;< ?php echo ($s*20)."%"; ?></td>
                   </tr>
                   <tr><td style="border:0; height:2px"></td></tr>
                   <tr>
                     <td colspan="7" style="border:0"><img id="status_bar_<?php echo $objItem-/>id; ?>" src="skins/status.jpg" height="5" width="< ?php echo ($s*16); ?>" /></td>
                 </tr>
               </table>
             </td>
   <!-- status bar update -->

3. Download the status gradient bar image file from “http://demofreak.dracon.biz/skins/status.jpg” and the copy image file to the /taskfreak/skins/ directory.

Source: Taskfreak Customizing Status

Background Knowledge

The progress of a task in TaskFreak! is shown as a percentage value and is not exactly visually appealing to quickly spot the progress. With a few minor alterations we can show the percentage completed bar that fills as the task progresses and a gradient bar indicating the progress along with the percentage value.

This solution was posted by Searcher at Re: Taskfreak Customizing Status.

Solution

1. Edit at line #268 as shown below.
   

   Cod Before

   268	<th width="<?php echo FRK_STATUS_LEVELS * 2; ?>%" onclick="freak_sort('statusKey')" colspan="< ?php echo FRK_STATUS_LEVELS ?>" class="sortable">< ?php echo (FRK_STATUS_LEVELS == 1)?'X':$langForm['status']; ?></th>

   Code After

   268	<th width="80" onclick="freak_sort('statusKey')" class="sortable">< ?php echo (FRK_STATUS_LEVELS == 1)?'X':$langForm['status']; ?></th>

2. Edit at line #382
   

   Code Before

   < ?php
                       $s = $objItem->itemStatus->statusKey;
                       for ($i = 0; $i < FRK_STATUS_LEVELS; $i++) {
                           $j = ($i < $s)?(FRK_STATUS_LEVELS - $i):0;
                   ?>
                           <td id="est<?php echo ($i+1).$objItem->id; ?>" class="sts< ?php echo $j; ?>"< ?php
                           if ($objUser->checkLevel(14) || $objItem->checkRights($objUser->id,8,true))  {
                               echo ' onclick="freak_sts('.$objItem->id.','.($i+1).')" style="cursor:pointer"';
                           }
                       ?>>&nbsp;</td>
                   < ?php
                       }
                   ?>

   Code After

   <!-- status bar update -->
             <td>
               <table width="100%" cellpadding="0" cellspacing="0">
                 <tr>
                   < ?php
                   $s = $objItem->itemStatus->statusKey;
                   for ($i = 0; $i < FRK_STATUS_LEVELS; $i++) {
                     $j = ($i < $s)?(FRK_STATUS_LEVELS - $i):0;
                     ?>
                     <td width="10" onmouseover="this.style.cursor='pointer'" id="est<?php echo ($i+1).$objItem->id; ?>" class="sts< ?php echo $j; ?>"< ?php if ($objUser->checkLevel(14) || $objItem->checkRights($objUser->id,8,true))  { echo ' onclick="freak_sts('.$objItem->id.','.($i+1).')" style="cursor:pointer"'; } ?>>&nbsp;</td>
                     < ?php } ?>
                     <td style="border:0"></td>
                     <td id="status_perc_<?php echo $objItem->id; ?>" style="border:0; font-size:8px; color:#000" align="right">&nbsp;< ?php echo ($s*20)."%"; ?></td>
                   </tr>
                   <tr><td style="border:0; height:2px"></td></tr>
                   <tr>
                     <td colspan="7" style="border:0"><img id="status_bar_<?php echo $objItem-/>id; ?>" src="skins/status.jpg" height="5" width="< ?php echo ($s*16); ?>" /></td>
                 </tr>
               </table>
             </td>
   <!-- status bar update -->

3. Download the status gradient bar image file from “http://demofreak.dracon.biz/skins/status.jpg” and the copy image file to the /taskfreak/skins/ directory.

Source: Taskfreak Customizing Status

This post explains how to debug MySQL Cluster 7.1, installed using the RPM packages, using gdb on a Linux box (Red Hat, Oracle Enterprise Linux, CentOS, ..).

When a data node crashes lots of information goes into the error log, trace files and out log. However, it makes sometimes sense when you can repeat the crash, to run the data node in debug mode, or using gdb.

First, using RPMs and a Linux distribution, make sure you have the ‘debuginfo’ package installed. For example, for Red Hat or Oracle Enterprise Linux on a 64-bit machine, this package would be called: MySQL-Cluster-gpl-debuginfo-7.1.15-1.rhel5.x86_64.rpm .

Create a file with the following commands, we will name it ‘ndbd.gdb’:

set pagination off
set logging overwrite on
set logging file ndbd_gdb_backtrace.txt
set logging on
run --foreground -c <YourMGM:1186> --ndb-nodeid=<YourID>
thread apply all bt
set logging off

Note line 5: pass the options to ‘run’ which you usually pass when starting ndbd, but leave the –foreground option.
Note line 3: you can save of course the logging file wherever you want to.

Then all you need to do is run gdb with the commands file and the ndbd binary you just created:

shell> gdb /usr/sbin/ndbd -x ndbd.gdb

A full backtrace of threads will be available in the logging file when ndbd crashes. This you can then upload when reporting bugs.

There are probably more options and things you can do, please post them in the comments!

This is a reply to the blog post Poor man’s MySQL replication monitoring. Haidong Ji had a few problems using MySQLdb (could use the ‘dict’ cursor) and apparently he doesn’t want to much dependencies. I agree that using the mysql client tool is a nice alternative if you don’t want to use any 3rd party Python modules. And the MySQL client tools are usually and should be installed with the server.

However, since MySQL Connector/Python only needs itself and Python, dependencies are reduced to a minimum. Here you’ll find a refactored version of Haidong’s version (can of course be made much more sophisticated) using the connector:

import sys
from socket import gethostname
import smtplib
import mysql.connector

emailSubject = "Replication problem on slave %s"
emailTo = "recipient@example.com"
emailFrom = "monitor-tool@example.com"

def runCmd(cmd):
    cnx = mysql.connector.connect(user='root',
                                  unix_socket='/path/to/mysql.sock')
    cur = cnx.cursor(buffered=True)
    cur.execute(cmd)
    columns = tuple( [d[0].decode('utf8') for d in cur.description] )
    row = cur.fetchone()
    if row is None:
        raise StandardError("MySQL Server not configured as Slave")
    result = dict(zip(columns, row))
    cur.close()
    cnx.close()
    return result

try:
    slave_status = runCmd("SHOW SLAVE STATUS")
except mysql.connector.Error, e:
    print >> sys.stderr, "There was a MySQL error:", e
    sys.exit(1)
except StandardError, e:
    print >> sys.stderr, "There was an error:", e
    sys.exit(1)

if (slave_status['Slave_IO_Running'] == 'Yes' and
    slave_status['Slave_SQL_Running'] == 'Yes' and
    slave_status['Last_Errno'] == 0):
    print "Cool"
else:
    emailBody = [
        "From: %s" % emailFrom,
        "To: %s" % emailTo,
        "Subject: %s" % (emailSubject %  gethostname()),
        "",
        '\n'.join([ k + ' : ' + str(v) for k,v in slave_status.iteritems()]),
        "\r\n",
        ]
    server = smtplib.SMTP("localhost")
    server.sendmail(emailFrom, [emailTo], '\r\n'.join(emailBody))
    server.quit()

Setting client flags with MySQL Connector/Python works a bit differently than the other MySQL Python drivers. This blog post describes how to set and unset flags, like the CLIENT_FOUND_ROWS.

The default client flags for the MySQL Client/Server protocol can be retrieved using the constants.ClientFlag class:

>>> from mysql.connector.constants import ClientFlag
>>> defaults = ClientFlag.get_default()
>>> print ClientFlag.get_bit_info(defaults)
['SECURE_CONNECTION', 'TRANSACTIONS', 'CONNECT_WITH_DB',
 'PROTOCOL_41', 'LONG_FLAG', 'MULTI_RESULTS',
 'MULTI_STATEMENTS', 'LONG_PASSWD']

To set an extra flag when connecting to MySQL you use the client_flags argument of connect()-method. For example, you’d like to have the CLIENT_FOUND_ROWS set:

import mysql.connector
from mysql.connector.constants import ClientFlag
extra_flags = [ClientFlag.FOUND_ROWS]
cnx = mysql.connector.connect(client_flags=extra_flags)

Similar, you can unset a flag passing a list of negative values, or all at the same time. For example, you’d like the CLIENT_FOUND_ROWS set, but you don’t want CLIENT_MULTI_STATEMENTS:

import mysql.connector
from mysql.connector.constants import ClientFlag
extra_flags = [ClientFlag.FOUND_ROWS, -ClientFlag.MULTI_STATEMENTS]
cnx = mysql.connector.connect(client_flags=extra_flags)

It is also possible to pass the client_flags an integer, but you need to get first the defaults, and do bitwise operations to set/unset flags yourself. At the moment of writing, there is a bug about this, see lp:695514, but I recommend using the list-method.

This blog post shows how to create a cursor class for MySQL Connector/Python which will allow you to cache queries. It will hold the query itself and the result in a global variable.

Note: this is a proof of concept and is only meant as a demonstration on how to extend MySQL Connector/Python.

Why query caching?

You are doing lots of queries that have the same result. It would be expensive to always run the same exact query. MySQL has already a query cache, and there is also memcached. But you like MySQL Connector/Python so much you’d like to do it yourself.

A cursor caching queries and their result

To demonstrate a simple implementation of a query cache, we inherit from an existing class: MySQLCursorBuffered. It will save the executed operation with their results in a ‘global’ variable. We call this cursor MySQLCursorQueryCache.

We take the buffered cursor because we’d like to save the result right away. Below you see we only changed two methods for MySQLCursorBuffered:

• .execute(): it will now first check using an md5 checksum whether we executed the query before. If we did, we set the make the cached result active. If not, we simply executed.
• ._handle_resultset(): called when .execute() did an operation which has a result set. The result we know save in the QUERY_CACHE global dict.

from hashlib import md5
import mysql.connector

QUERY_CACHE = dict()

class MySQLCursorQueryCache(mysql.connector.cursor.MySQLCursorBuffered):
    def execute(self, operation, params=None):
        self._qmd5 = md5(operation).digest()
        if QUERY_CACHE.has_key(self._qmd5):
            (self._rows, eof) = QUERY_CACHE[self._qmd5]
            self.rowcount = len(self._rows)
            self._handle_eof(eof)
            self._next_row = 0
        else:
            super(MySQLCursorQCache, self).execute(operation, params)

    def _handle_resultset(self):
        (self._rows, eof) = self.db().protocol.get_rows()
        self.rowcount = len(self._rows)
        self._handle_eof(eof)
        self._next_row = 0
        QUERY_CACHE[self._qmd5] = (self._rows, eof)
        try:
            self.db().unread_result = False
        except:
            pass
        self._qmd5 = None

The above code is a proof of concept, there is lots of room for improvement. For example, you need something to invalidate entries in the query cache.

How to use MySQLCursorQueryCache

def main():

    cnx = mysql.connector.connect(database='test')
    cur = cnx.cursor(cursor_class=MySQLCursorQueryCache)

    cur.execute("SELECT NOW()")
    print cur.fetchone()
    time.sleep(2)
    cur.execute("SELECT NOW()")
    print cur.fetchone()

    cur.close()
    cnx.close()

When you would use the default cursor, both executed SQL statements would produce a different result. The above produces the following output:

(datetime.datetime(2010, 11, 22, 21, 20, 4),)
(datetime.datetime(2010, 11, 22, 21, 20, 4),)

When you’d like to have some statements cached, and some not, just create a second cursor cursor_class=MySQLCursorBuffered (see Buffering results with MySQL Connector/Python).

MySQL Connector/Python doesn’t buffer results by default. This means you have to fetch the rows when you issued a SELECT. This post describes how you can change this behavior.

Why buffering result sets?

Buffering or storing the result set on the client side is handy when you, for example, would like to use multiple cursors per connection and you’de like to traverse each one interleaved.

Keep in mind that with bigger result sets, the client side will use more memory. You just need to find out for yourself what’s best. When you know result sets are mostly small, you might opt to buffer.

MySQLdb by default buffers results and you need to use a different cursor to disable it. oursql does not buffer by default. This is good to know when you start using a different MySQL DB API for Python.

Use case: 1 connection, fetch from 2 cursors

You want to fetch data from two tables and process the data using one connection. If you do it without buffering, you would run into the following:

>>> cnx = mysql.connector.connect(database='test')
>>> cur1 = cnx.cursor()
>>> cur2 = cnx.cursor()
>>> cur1.execute("SELECT c1 FROM t1")
-1
>>> cur2.execute("SELECT c1 FROM t1")
..
mysql.connector.errors.InternalError: Unread result found.

MySQL Connector/Python offers two ways to turn buffering on or off. Either per connection or per cursor using the buffered argument set to True

Set buffering per connection

If you want all the cursors for a particular connection to be buffered, you can turn it on when connecting to MySQL setting the buffered-argument to True.

>>> import mysql.connector
>>> cnx = mysql.connector.connect(database='test',buffered=True)
>>> cur1 = cnx.cursor()
>>> cur1.__class__

>>> cur2 = cnx.cursor()
>>> cur1.execute("SELECT c1 FROM t1")
3
>>> cur2.execute("SELECT c1 FROM t1")
3
>>> cur1.fetchone()
(u'Geert',)
>>> cur2.fetchone()

Set buffering per cursor

You can request a buffering cursor buffering from the connection object. Do this use the buffered-argument set to True:

>>> import mysql.connector
>>> cnx = mysql.connector.connect(database='test')
>>> cur1 = cnx.cursor(buffered=True)
>>> cur1.__class__

Note: the above should work with MySQL Connector/Python v0.2 or greater

This post describes how to make a custom cursor returning rows as dictionaries using MySQL Connctor/Python v0.2 (or later).

Problem: you want to fetch rows from the database and return them as a dictionary with keys being the column names.

First, lets check how you would do it without any custom cursor.

cnx = mysql.connector.connect(host='localhost',database='test')
cur = cnx.cursor()
cur.execute("SELECT c1, c2 FROM t1")
result = []
columns = tuple( [d[0].decode('utf8') for d in cur.description] )
for row in cur:
  result.append(dict(zip(columns, row)))
pprint(result)
cur.close()
cnx.close()

The above results in an output like this:

[{u'c1': datetime.datetime(2010, 10, 13, 8, 55, 35), u'c2': u'ham'},
 {u'c1': datetime.datetime(2010, 10, 13, 8, 55, 38), u'c2': u'spam'}]

Alternatively, you can code your own cursor which will return a row as a dictionary. Here is an example on how to do this:

import mysql.connector

class MySQLCursorDict(mysql.connector.cursor.MySQLCursor):

  def fetchone(self):
    row = self._fetch_row()
      if row:
        return dict(zip(self.column_names, self._row_to_python(row)))
      return None

What we did is subclassing MySQLCursor and overload the fetchone()-method. To use the above, you can do following:

cnx = mysql.connector.connect(host='localhost',database='test')
cur = cnx.cursor(cursor_class=MySQLCursorDict)
cur.execute("SELECT c1, c2 FROM t1")
rows = cur.fetchall()
pprint(rows)
cur.close()
cnx.close()

The trick is to pass the cursor_class-argument when creating the cursor. (At the time of writing, I realize that the cursor_class should be part of the connection arguments to set a default for all requested cursors. We’ll change that.)

It’s a bit more elegant coding and possibilities are endless. Maybe you’d like a cursor which returns a row in XML for example.

MySQL Connector/Python is (or should be) compliant with the Python DB-API 2.0 specification. This means that you can use DBUtils' PooledDB module to implement database connection pooling.

Here below you'll find an example which will output the connection ID of each connection requested through the pooling mechanism.

from DBUtils.PooledDB import PooledDB
import mysql.connector

def main():
    pool_size = 3
    pool = PooledDB(mysql.connector, pool_size,
        database='test', user='root', host='127.0.0.1')
    
    cnx = [None,] * pool_size
    for i in xrange(0,pool_size):
        cnx[i] = pool.connection()
        cur = cnx[i].cursor()
        cur.execute("SELECT CONNECTION_ID()")
        print "Cnx %d has ID %d" % (i+1,cur.fetchone()[0])
        cur.close()
    
    for c in cnx:
        c.close()

The output will be something like this:

Cnx 1 has ID 42
Cnx 2 has ID 41
Cnx 3 has ID 40