Geek asks and answers

Strange MySQL SELECT behavior

When the NUMBER_OF_ITERATIONS variable is set to 1, everything works fine ... but when I change it to any value greater than 1, I get some problems.

First of all, in this case, when I print the value of res , I get a huge amount (for example, 18446744073709551615).

Secondly, but most importantly, in this case, the script cannot process data, because the length of the value is always 0 ...

if __name__ == '__main__':

    NUMBER_OF_ITERATIONS = 2

    conn = DBconnection()   # return a database connection

    for i in range( NUMBER_OF_ITERATIONS ):
        cursor = conn.cursor()
        res = cursor.execute( 'SELECT field 
                                 FROM table 
                                WHERE other_field = 0 
                                LIMIT 10 LOCK IN SHARE MODE' )
        print '# of selected rows: ' + str(res)

        values = []
        for elem in cursor.fetchall():
            if elem != None:
                values.append( list(elem).pop() )

        if len( values ) != 0:
            # do something...

        else:
            print 'NO VALUES AVAILABLE'
            cursor.close()
            break

    conn.close()
    print 'DONE'

I use the InnoDB storage engine, and at the same time, another script exists on this script

w370> python, which loads data into the same table (using the LOAD DATA INFILE clause).

, , , ( ) ? , 2 . .

+3
1

, . , ?

import config
import MySQLdb
import multiprocessing as mp
import random
import string
import time

def random_string(n):
    return ''.join(random.choice(string.letters) for _ in range(n))

def generate_data():
    conn=MySQLdb.connect(
        host=config.HOST,user=config.USER,
        passwd=config.PASS,db='test')    
    cursor=conn.cursor()
    while True:
        with open('/tmp/test.dat','w') as f:
            for _ in range(20):
                f.write('{b}\n'.format(b=random_string(10)))
        # sql='LOCK TABLES foo WRITE'
        # cursor.execute(sql)
        sql="LOAD DATA INFILE '/tmp/test.dat' INTO TABLE test.foo"
        cursor.execute(sql)
        conn.commit()
        # sql='UNLOCK TABLES'
        # cursor.execute(sql)        
        time.sleep(0.05)

def setup_innodb(connection):
    cursor=connection.cursor()
    sql='DROP TABLE IF EXISTS foo'
    cursor.execute(sql)
    sql='''\
        CREATE TABLE `foo` (
          `bar` varchar(10) NOT NULL
        ) ENGINE=InnoDB  
        '''
    cursor.execute(sql)
    connection.commit()

if __name__ == '__main__':
    NUMBER_OF_ITERATIONS = 20
    conn=MySQLdb.connect(
        host=config.HOST,user=config.USER,
        passwd=config.PASS,db='test')
    setup_innodb(conn)

    # Start a process which is "simultaneously" calling LOAD DATA INFILE
    proc=mp.Process(target=generate_data)
    proc.daemon=True
    proc.start()

    for i in range( NUMBER_OF_ITERATIONS ):
        cursor = conn.cursor()
        # sql='''SELECT field 
        #        FROM table 
        #        WHERE other_field = 0 
        #        LIMIT 10 LOCK IN SHARE MODE'''
        # sql='LOCK TABLES foo READ'
        # cursor.execute(sql)
        sql='''SELECT * 
               FROM foo
               LOCK IN SHARE MODE
               '''
        res = cursor.execute(sql)
        print '# of selected rows: ' + str(res)
        values = cursor.fetchall()
        # http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html
        # Locks set by LOCK IN SHARE MODE and FOR UPDATE reads are released when
        # the transaction is committed or rolled back.
        conn.commit()
        time.sleep(0.1)

    conn.close()
    print 'DONE'


# of selected rows: 0
# of selected rows: 40
# of selected rows: 80
# of selected rows: 120
# of selected rows: 160
# of selected rows: 180
# of selected rows: 220
# of selected rows: 260
# of selected rows: 300
# of selected rows: 340
# of selected rows: 360
# of selected rows: 400
# of selected rows: 440
# of selected rows: 460
# of selected rows: 500
# of selected rows: 540
# of selected rows: 580
# of selected rows: 600
# of selected rows: 640
# of selected rows: 680
DONE
+2

More articles:


All Articles