February 7, 2010

Changing MySQL parser code on Windows – Build breaks due to Bison

In case if you working on Windows environment for MySQL development (sometimes I use visual studio for easy debugging); and in case if you change the parser code (sql_yacc.yy) or if you are working directly from development branch (bzr launchpad), then the build breaks to generate the yacc files (sql_yacc.h and sql_yacc.cc) with an error bison: M4: Invalid argument as shown below:

1>------ Build started: Project: sql, Configuration: Debug Win32 ------
1>Generating sql_yacc.h, sql_yacc.cc
2>------ Build started: Project: GenServerSource, Configuration: Debug Win32 ------
2>Generating sql_yacc.h, sql_yacc.cc
1>bison: m4: Invalid argument
1>Project : error PRJ0019: A tool returned an error code from "Generating sql_yacc.h, sql_yacc.cc"
1>sql - 1 error(s), 0 warning(s)
2>bison: m4: Invalid argument
2>Project : error PRJ0019: A tool returned an error code from "Generating sql_yacc.h, sql_yacc.cc"
2>GenServerSource - 1 error(s), 0 warning(s)

But if use source zip file for any particular release, then it won’t fail as the files (sql_yacc.cc and sql_yacc.h) are pre-built and copied to the distribution zip file.

But, again if you wanted to change the code or happen to save sql_yacc.yy, then it starts generating the files and build will break. It looks like lot of people are experincing the same problem to build parser code on Windows using any recent version of bison (not just MySQL code base).

Both bison.exe and m4.exe are in the path and they are the latest version; but still it fails.. 

c:\mysql-5.1\sql>which bison
C:\Gnu\GetGnuWin32\gnuwin32\bin\bison.EXE
 
c:\mysql-5.1\sql>which m4
C:\Gnu\GetGnuWin32\gnuwin32\bin\m4.EXE
 
c:\mysql-5.1\sql>bison --version
bison (GNU Bison) 2.4.1
 
c:\mysql-5.1\sql>m4 --version
m4 (GNU M4) 1.4.13

It looks like the problem is with Windows version of bison to pick m4 executable even though m4 is in the path. For example, you can directy try to generate the files from sql directory using bison as… 

c:\mysql-5.1\sql>bison -y -p MYSQL --defines=sql_yacc.h --output=sql_yacc.cc sql_yacc.yy
bison: m4: Invalid argument

The work around what I found is to copy m4.exe to sql directory directly, so that bison can pick from local working directory, then everything starts working as expected.

c:\mysql-5.1\sql>bison -y -p MYSQL --defines=sql_yacc.h --output=sql_yacc.cc sql_yacc.yy
bison: m4: Invalid argument
 
c:\mysql-5.1\sql>ls -al sql_yacc.*
-rw-rw-rw-  1 venu 0 413012 2010-02-07 11:58 sql_yacc.yy
 
c:\mysql-5.1\sql>which m4
C:\Gnu\GetGnuWin32\gnuwin32\bin\m4.EXE
 
c:\mysql-5.1\sql>copy C:\Gnu\GetGnuWin32\gnuwin32\bin\m4.EXE .
        1 file(s) copied.
 
c:\mysql-5.1\sql>which m4
c:\mysql-5.1\sql\m4.EXE
 
c:\mysql-5.1\sql>bison -y -p MYSQL --defines=sql_yacc.h --output=sql_yacc.cc sql_yacc.yy
 
c:\mysql-5.1\sql>ls -al sql_yacc.*
-rw-rw-rw-  1 venu 0 1510389 2010-02-07 14:33 sql_yacc.cc
-rw-rw-rw-  1 venu 0   30532 2010-02-07 14:33 sql_yacc.h
-rw-rw-rw-  1 venu 0  413012 2010-02-07 11:58 sql_yacc.yy

Kind of weired, but atleast there is a work around to change the parser code on Windows now; and it works great including Visual studio also starts building without any errors. But if you remove m4.exe from sql directory, then things starts to break immediately.

Posted @ 2:00 pm :: Database, MySQL, Windows :: 3 comments

February 3, 2010

SHOW TEMPORARY TABLES

I had this patch for a while where one can get listing of both session and global temporary tables across all sessions. It really helped lot of times to understand the bottlenecks of some of the temporary table issues as MySQL never exposed them in the form of SHOW TABLES.

I also added a new status variable called ‘Created_tmp_heap_to_disk_tables‘, which keeps track of how many memory based temp tables are re-created back to disk based.

The patch is now ported to newer MySQL versions, both 5.0 and 5.1; and it works great on most of the platforms that I tested (Mac, Linux and Windows)

It introduces two new INFORMATION_SCHEMA tables, TEMPORARY_TABLES and GLOBAL_TEMPORARY_TABLES along with supporting regular SHOW syntax

 
SHOW [SESSION/GLOBAL] TEMPORARY TABLES [FROM db]

Some examples of how it works at present

Session Temp Tables

mysql> show session temporary tables;
+----+------+-------+--------+--------------+
| Id | Db   | Table | Engine | Name         |
+----+------+-------+--------+--------------+
|  1 | test | t2    | MEMORY | #sql29da_1_3 |
|  1 | test | t1    | MyISAM | #sql29da_1_2 |
+----+------+-------+--------+--------------+
2 rows in set (0.00 sec)
 
mysql> show temporary tables;
+----+------+-------+--------+--------------+
| Id | Db   | Table | Engine | Name         |
+----+------+-------+--------+--------------+
|  1 | test | t2    | MEMORY | #sql29da_1_3 |
|  1 | test | t1    | MyISAM | #sql29da_1_2 |
+----+------+-------+--------+--------------+
2 rows in set (0.00 sec)
 
mysql> select * from information_schema.temporary_tables;
+------------+----------+------------+--------+--------------+
| SESSION_ID | DATABASE | TABLE_NAME | ENGINE | NAME         |
+------------+----------+------------+--------+--------------+
|          1 | test     | t2         | MEMORY | #sql29da_1_3 |
|          1 | test     | t1         | MyISAM | #sql29da_1_2 |
+------------+----------+------------+--------+--------------+
2 rows in set (0.00 sec)

Global Temp Tables Across All Sessions:

mysql> select * from information_schema.global_temporary_tables;
+------------+----------+------------+--------+--------------+
| SESSION_ID | DATABASE | TABLE_NAME | ENGINE | NAME         |
+------------+----------+------------+--------+--------------+
|          6 | test     | t3         | MyISAM | #sql29da_6_0 |
|          5 | test     | t2         | MEMORY | #sql29da_5_3 |
|          5 | test     | t1         | MyISAM | #sql29da_5_2 |
|          4 | venu     | v1         | InnoDB | #sql29da_4_0 |
+------------+----------+------------+--------+--------------+
4 rows in set (0.00 sec)
 
mysql> show global temporary tables;
+----+------+-------+--------+--------------+
| Id | Db   | Table | Engine | Name         |
+----+------+-------+--------+--------------+
|  6 | test | t3    | MyISAM | #sql29da_6_0 |
|  5 | test | t2    | MEMORY | #sql29da_5_3 |
|  5 | test | t1    | MyISAM | #sql29da_5_2 |
|  4 | venu | v1    | InnoDB | #sql29da_4_0 |
+----+------+-------+--------+--------------+
4 rows in set (0.00 sec)
  • Id: Session ID
  • Db: Database Name
  • Engine: Engine Type
  • Name: Internal Name, how its stored in the file system

Will publish the patch once am done with porting internal temp tables listing by introducing two new columns Table_Type and Info where Table_Type will indicate if its internal or external and Info will have initial 1024 bytes of the query that caused the internal temp table.

Posted @ 1:30 am :: Database, MySQL :: 11 comments