Re: Mysql server NetBSD 6.1 RC4 it does not works

[Franck Lesage <francksys%free.fr@localhost>]

I installed NetBSD 6.1 RC4 since few times. I wanted to install mysql.
And I faced also a problem. First, I install mysql with pkgin:

pkgin in mysql-server-5.5.30

Then I try to initialize the mysql database:

cd /usr/pkg/share/mysql && cp my-medium.cnf my-default.cnf
mysql_install_db

The output of mysql_install_db for me is:

Installing MySQL system tables...130610 16:40:45 InnoDB: The InnoDB memory 
heap is disabled
130610 16:40:45 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130610 16:40:45 InnoDB: Compressed tables use zlib 1.2.3
130610 16:40:45 InnoDB: Initializing buffer pool, size = 128.0M
130610 16:40:45 InnoDB: Completed initialization of buffer pool
130610 16:40:45 InnoDB: highest supported file format is Barracuda.
130610 16:40:45  InnoDB: Waiting for the background threads to start
130610 16:40:46 InnoDB: 5.5.30 started; log sequence number 1595675
ERROR: 1064  You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 
'' at line 1
130610 16:40:46 [ERROR] Aborting

130610 16:40:46  InnoDB: Starting shutdown...
130610 16:40:46  InnoDB: Shutdown completed; log sequence number 1595675
130610 16:40:46 [Note] /usr/pkg/sbin/mysqld: Shutdown complete


I have a SQL syntax error difficult to understand without further 
invistigation. It seems to be line 1 which is wrong. The pertinent file 
where the SQL commands are is mysql_system_tables.sql. The line 1 of this
file is a comment. A comment cannot cause the probleme. OTOH in the perl 
script mysql_install_db, we have some code:

open(SQL, $create_system_tables)
  or error($opt,"can't open $create_system_tables for reading: $!");
open(SQL2, $fill_system_tables)
  or error($opt,"can't open $fill_system_tables for reading: $!");
# FIXME  > /dev/null ?
if ( open(PIPE, "| $mysqld_install_cmd_line") )
{
  print PIPE "use mysql;\n";
  while ( <SQL> )
  {
    # When doing a "cross bootstrap" install, no reference to the current
    # host should be added to the system tables.  So we filter out any
    # lines which contain the current host name.
    next if $opt->{'cross-bootstrap'} and /\@current_hostname/;

    print PIPE $_;
  }

We can see that "use mysql;\n" is added just before the stream of
mysql_system_tables.sql. But it's not "use mysql;" which cause the
trouble.

If we make a test file like this one we can find the line which
is problematic:

echo "use mysql;" > /tmp/my_mysql_system_tables.sql
cat /usr/pkg/share/mysql/mysql_system_tables.sql >> /tmp/my_mysql*sql

And then we try different n threshold with:

head -n n /tmp/my_mysql_system_tables.sql | \
/usr/pkg/sbin/mysqld --bootstrap --basedir=/usr/pkg --datadir=/var/mysql \
--log-warnings=0 --loose-skip-ndbcluster --max_allowed_packet=8M \
--default-storage-engine=MyISAM --net_buffer_length=16K

Until n = 102 everything goes ok.
I found that's the line 103 of mysql_system_tables.sql which is
problematic. See this line:

CREATE TABLE IF NOT EXISTS innodb_table_stats (
        database_name                   VARCHAR(64) NOT NULL,
        table_name                      VARCHAR(64) NOT NULL,
        last_update                     TIMESTAMP NOT NULL NOT NULL 
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        n_rows                          BIGINT UNSIGNED NOT NULL,
        clustered_index_size            BIGINT UNSIGNED NOT NULL,
        sum_of_other_index_sizes        BIGINT UNSIGNED NOT NULL,
        PRIMARY KEY (database_name, table_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

I didn't investigate the problem further due to a lack of energy.
I just share my experience.

Regards,

Franck