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 30 of 36 Next 6 Older Entries

Displaying posts with tag: howto (reset)

MySQL Connector/Python on GitHub
Employee +1 Vote Up -0Vote Down

Last week we released Connector/Python v2.0 (alpha); today we publish the source on GitHub. Yes, we are using Git internally and are now able to push it out on each release. Previous versions are still available through LaunchPad.

Here is the full process to get Connector/Python installed in a virtual environment. You’ll need Git installed of course.

shell> git clone https://github.com/oracle/mysql-connector-python.git cpy
shell> virtualenv ENVCPY
shell> source ENVCPY/bin/activate
(ENVCPY)shell> cd cpy
(ENVCPY)shell> python setup.py install
(ENVCPY)shell> python
>>> import
  [Read more...]
How To - Guide to exporting data from Excel to a new MySQL table
Employee +0 Vote Up -0Vote Down

There may be times when you need to create a new table in MySQL and feed it with data from another database, the Internet or from combined data sources. MS Excel is commonly used as the bridge between those data sources and a target MySQL database because of the simplicity it offers to organize the information to then just dump it into a new MySQL table. Although the last bit sounds trivial, it may actually be a cumbersome step, creating ODBC connections within Excel through Microsoft Query may not help since these are normally created to extract data from MySQL into Excel, not the opposite. What if you could do this in a few clicks from within Excel after making your data ready for export to a MySQL database?

With MySQL for Excel you can do this and this guide will teach you how easy it is.

Snippet: Show column information using MySQL Connector/Python
Employee +1 Vote Up -0Vote Down

Problem

You have a query executed by MySQL Connector/Python and would like to show column information nicely on the console.

Solution

Every cursor object has a description property. This can be used to show information about the columns in a result set.

columns = []
maxnamesize = 0
for coldesc in cur.description:
    coldesc = list(coldesc)
    coldesc[2:6] = []
    columns.append(coldesc)
    namesize = len(coldesc[0])
    if namesize > maxnamesize:
        maxnamesize = namesize

fmt = "{{nr:3}} {{name:{0}}} {{type:12}} {{null}}".format(
    maxnamesize+1)
colnr = 1
for column in columns:
    (colname, fieldtype, nullok,
  [Read more...]
Working with comma separated list MySQL options
+1 Vote Up -0Vote Down
Over time, some options have crept into the MySQL server which are comma separated lists of options. These include SQL_MODE, optimizer_switch, optimizer_trace and a few other variables.

Optimizer_switch is particularly problematic to work with as it contains many options and is hard to interpret. This is what you usually see when you examine optimizer_switch:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on



As you can see, seeing which option is on or off is rather difficult. You can use the REPLACE function to make this easier:
mysql> select replace(@@optimizer_switch,






  [Read more...]
Using Connector/Python with SQLAlchemy
Employee +4 Vote Up -0Vote Down

SQLAchemy has support for MySQL Connector/Python for a while now. Here is a little HOWTO showing how install both, and setup a database engine.

There are multiple ways of installing both projects, but here is the simplest using pip, whatever platform you use:

shell> pip install SQLAlchemy
shell> pip install mysql-connector-python 

Start your SQLAlchemy engines using a URL pointing to Connector/Python. Note the connect_args argument which passes extra connection arguments to Connector/Python. In the

  [Read more...]
How to Drop the Trigger
+2 Vote Up -1Vote Down
If we speak about MySQL triggers, does anybody need a "Howto" on this topic? It must be simple if you know the trigger name to drop, just use DROP TRIGGER, as manual explains:

mysql> use test
Database changedmysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
 
mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: tr1
               Event: INSERT













  [Read more...]
How to use PERFORMANCE_SCHEMA to check InnoDB mutex waits
+4 Vote Up -0Vote Down

Summary

To set up InnoDB mutex waits monitoring via PERFORMANCE_SCHEMA in general with MySQL 5.6.9 you should do at least the following:
  • Start MySQL server with all mutex related instruments enabled at startup (performance_schema=ON by default on recent 5.6.x), like this:

    mysqld_safe --performance_schema_instrument='wait/synch/mutex/innodb/%=on' &
  • Connect to server and set up proper consumers explicitly, like this:

    UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name like 'events_waits%';
  • Run your problematic load.
  • Check waits using whatever tables you need, like this:

    mysql>  select event_name, count_star, sum_timer_wait from performance_schema.events_waits_summary_global_by_event_name where event_name like 'wait/synch/mutex/innodb%' and count_star






  •   [Read more...]
    How To - Guide to Importing Data from a MySQL Database to Excel using MySQL for Excel
    Employee +7 Vote Up -0Vote Down

    Fetching data from a database to then get it into an Excel spreadsheet to do analysis, reporting, transforming, sharing, etc. is a very common task among users. This task can be accomplished in several different ways and with different tools getting the same result; but users may find the process rather complicated, too technical and lengthy. With MySQL for Excel the task of importing data from a MySQL database to an Excel spreadsheet becomes an easy one and accessible to all types of users.  Here is a quick guide describing how to import data to Excel using MySQL for Excel.

    Fetching rows as dictionaries with MySQL Connector/Python (revised)
    Employee +4 Vote Up -0Vote Down

    It is possible with MySQL Connector/Python to define your own cursor classes. A very good use case is to return rows as dictionary instead of tuples. This post shows how to do this using MySQL Connector/Python v1.0 and is an update for an older blog entry.

    In the example below we are subclassing the MySQLCursor class to create a new class called MySQLCursorDict. We change the _row_to_python() method to return a dictionary instead of a tuple. The keys of the dictionary will be (unicode) column names.

    from pprint import pprint
    import mysql.connector
    
    class MySQLCursorDict(mysql.connector.cursor.MySQLCursor):
        def _row_to_python(self, rowdata, desc=None):
            row
      [Read more...]
    How to create a MySQL bug report that someone would like to read and comment on
    +4 Vote Up -0Vote Down
    It happens to me almost every day. I note some "bug report" at http://bugs.mysql.com that makes me think that my job is miserable... Like this, Bug #66580. What readers of such a bug report are supposed to do with it? Other than ignore?

    Today I want to stay positive, so instead of cursing in public let me give some advices inspired by this great HOWTO and detailed instructions from MySQL site.

    Before you send a problem report to MySQL public bugs database, please:

    • Try to find similar bugs by searching MySQL bugs database. Google search for site:bugs.mysql.com






      [Read more...]
    Consulting essentials: Building your business
    +0 Vote Up -0Vote Down

    Read the original article at Consulting essentials: Building your business

    In the last two posts on how to build a successful consulting business I shared advice and tips on closing deals and managing and completing your engagements.

    This post will look at where to focus your efforts in order to sustain your consulting business, and build skills.

    Focus on your subject matter expertise

    Being a subject matter expert takes years of education, and professional experience to build. It’s your

      [Read more...]
    How To – Resolve MySQL Error Incorrect Key File for Table
    +0 Vote Up -0Vote Down

    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.

  • Run
  •   [Read more...]
    TaskFreak! v0.6.2 – Customizing Status
    +0 Vote Up -0Vote Down

    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


  • 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'];

  •   [Read more...]
    Debugging MySQL Cluster installed using RPMs using gdb
    Employee +1 Vote Up -0Vote Down

    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
      [Read more...]
    MySQL Cluster: Rotating the log file of the Data Nodes
    Employee +2 Vote Up -0Vote Down

    There is a log file called ndb_<NodeID>_out.log created by the MySQL Cluster (http://www.mysql.com/products/cluster/) data nodes which can become quite big overtime. There is, unlike the cluster logs created by the management nodes, no rotation build in. So you have to revert to the basics and copy the file away, truncating the old one.

    For example, if you want to ‘rotate’ the log file of data node with NodeID 3:

    shell> mv ndb_3_out.log.1.gz ndb_3_out.log.2.gz
    shell> cp ndb_3_out.log ndb_3_out.log.1
    shell> cat /dev/null > ndb_3_out.log
    shell> gzip ndb_3_out.log.1
    

    It’s not elegant, and you might lose some entries, but it will help you keeping disk usage minimal. If you don’t need the log at all, just line 3 would do the trick.

    You can use

      [Read more...]
    Refactored: Poor man’s MySQL replication monitoring
    Employee +2 Vote Up -0Vote Down

    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.

      [Read more...]
    Custom logger for your MySQL Cluster data nodes
    Employee_Team +2 Vote Up -0Vote Down

    The MySQL Cluster data node log files can become very big. The best solution is to actually fix the underlying problem. But if you know what you are doing, you can work around it and filter out these annoying log entries.

    An example of ‘annoying’ entries is when you run MySQL Cluster on virtual machines (not good!) and disks and OS can’t follow any more; a few lines from the ndb_X_out.log:

    2011-04-03 10:52:31 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Scanning Timers elapsed=100
    2011-04-03 10:52:31 [ndbd] INFO     -- timerHandlingLab now: 1301820751642 sent: 1301820751395 diff: 247
    2011-04-03 10:52:31 [ndbd] INFO     -- Watchdog: User time: 296  System time: 536
    2011-04-03 10:52:31 [ndbd] INFO     -- Watchdog: User time: 296  System time: 536
    2011-04-03 10:52:31
      [Read more...]
    Setting client flags with MySQL Connector/Python
    Employee_Team +3 Vote Up -0Vote Down

    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

      [Read more...]
    Query caching with MySQL Connector/Python
    Employee_Team +2 Vote Up -0Vote Down

    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

      [Read more...]
    Buffering results with MySQL Connector/Python
    Employee_Team +2 Vote Up -0Vote Down

    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

      [Read more...]
    Fetching rows as dictionaries with MySQL Connector/Python
    Employee_Team +4 Vote Up -1Vote Down

    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()
    [python]
    
    The above results in an output like this:
    
    [python light="true"]
    [{u'c1': datetime.datetime(2010, 10, 13, 8, 55, 35), u'c2': u'ham'},
      [Read more...]
    MySQL Connector/Python and database pooling
    Employee_Team +8 Vote Up -0Vote Down

    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
      [Read more...]
    MySQL Connector/Python and database pooling
    Employee_Team +0 Vote Up -0Vote Down

    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
      [Read more...]
    TaskFreak! v0.6.2 – Alter Search Plugin
    +0 Vote Up -1Vote Down

    Background Knowledge


    The Search Plugin for TaskFreak! created by DaDaemon and xdu v0.0.1 (March 26, 2007) was designed to create a simple, quick search capability of the tasks title and description. As well it only searched through he current task view (tasks visible at the time) and tasks that are not completed. For some this was not what was desired and would rather have the Search Plugin search through all tasks weather completed or not and as well search through the comments of tasks along with the title and description. I’ll show you how this is done using Searcher, bchristie and davidlmansfield instructions posted on the TaskFreak! Forums.

    Solution – Add the Ability to  [Read more...]

    Running MySQL Cluster without Arbitrator: don't, but if you have to..
    Employee_Team +4 Vote Up -0Vote Down

    This post explains how to disable Arbitration when using MySQL Cluster (http://www.mysql.com/products/database/cluster/). It gives a case where this could be useful.

    First, a piece of advice: you do not want to run MySQL Cluster with arbitration disabled. But if you must, e.g. because of an oversight in your implementation, you can.
    Arbitration is very important in MySQL Cluster. It makes sure you don't end up with a Split Brain situation: 2 halves working independently, continuing changing data, making it impossible for them to work together later on.


      [Read more...]
    Friday Tips #2: Migrating JSF 1.2 + RichFaces to Java EE 6, Embedded and Arquillian, EJB 3.1 Timer, ...
    Employee_Team +0 Vote Up -0Vote Down

    Here are some tips that have been recently published on Java EE 6 & GlassFish:

    Migrating JSF 1.2 + RichFaces 3.x to Java EE 6 / GlassFish v3
    Mercurial and OpenSolaris and GlassFish
    How do I setup a DataSource in Embedded GlassFish when using Arquillian?



      [Read more...]
    Insert data into a VARCHAR field using NDB API: a solution
    Employee_Team +1 Vote Up -0Vote Down

    You are using MySQL Cluster and crazy enough to digest NDB API? Sick of SQL? Here's a treat: a function to make C/C++ strings ready for inserting into a VARCHAR field. The special thing about them is that the length is prefixed in the first 2 bytes.

    void make_ndb_varchar(char *buffer, char *str)
    {
      int len = strlen(str);
      int hlen = (len > 255) ? 2 : 1;
      buffer[0] = len & 0xff;
      if( len > 255 )
        buffer[1] = (len / 256);
      strcpy(buffer+hlen, str);
    }
    

    Yes, you can use memcpy. Whatever floats your boat.

    Lets use this function for a table t1, defined as follows (note: latin1!):

    CREATE TABLE t1 (
      id INT UNSIGNED NOT NULL,
      vc VARCHAR(128),
      vclong VARCHAR(1280),
      PRIMARY KEY (id)
      ) ENGINE=NDB DEFAULT CHARSET=latin1
    

    Here is part of the code, simplified

      [Read more...]
    Python, oursql and MacOS X 10.6 (Snow Leopard)
    Employee_Team +0 Vote Up -0Vote Down

    This post explains how to compile oursql and install it on MacOS 10.6. oursql is a Python database interface for MySQL, an alternative to MySQL for Python (i.e. MySQLdb) and MySQL Connector/Python.

    First, find out which MySQL you installed. This can be either the 32-bit or the 64-bit version. To make sure, find the mysqld (e.g. in /usr/local/mysql/bin) and do the following in a Terminal window:

    shell> file /usr/local/mysql/bin/mysqld
    .../mysqld: Mach-O 64-bit executable x86_64
    

    If you see x86_64, you got 64-bit, otherwise 32-bit. If you see both, then you have a universal build.

      [Read more...]
    Python, oursql and MacOS X 10.6 (Snow Leopard)
    Employee_Team +0 Vote Up -0Vote Down

    This post explains how to compile oursql and install it on MacOS 10.6. oursql is a Python database interface for MySQL, an alternative to MySQL for Python (i.e. MySQLdb) and MySQL Connector/Python.

    First, find out which MySQL you installed. This can be either the 32-bit or the 64-bit version. To make sure, find the mysqld (e.g. in /usr/local/mysql/bin) and do the following in a Terminal window:


    shell> file /usr/local/mysql/bin/mysqld
    .../mysqld: Mach-O 64-bit executable x86_64

    If you see x86_64, you got 64-bit, otherwise 32-bit. If you see both, then you have




      [Read more...]
    Building MySQL universal binaries using MacOS X 10.6 (Snow Leopard)
    Employee +0 Vote Up -0Vote Down

    On the eve of 2010.. and your boss wants to stick to these MacOS X 10.5 machines, too stubborn or chicken to upgrade. Some developers still have their old PowerBook laptops and they need MySQL flying on PowerPC machines. To top it all, one guy said he wanted to have 32 and 64-bit in one bite. *Sigh* .. But there is an easy way out! A universal binary!

    This post shows you a way to create MySQL universal binaries using MacOS X 10.6 so you can run them on MacOS X 10.5/10.6 whether it is PowerPC or Intel, or 32bit or 64bit.

    However, if you need

      [Read more...]
    Showing entries 1 to 30 of 36 Next 6 Older Entries

    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.