use Perl

• corrupted database? (Score:2)

  by IlyaM (2933)

  BTW I tried to reproduce this and I cannot:

  mysql> show create table mail\G
*************************** 1. row ***************************
Table: mail
Create Table: CREATE TABLE `mail` (
`mail_id` int(11) unsigned NOT NULL auto_increment,
`user` varchar(64) default NULL,
`mail_type_id` int(10) unsigned NOT NULL,
`domain_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`mail_id`),
KEY `mail_type_id` (`mail_type_id`),
KEY `domain_id` (`domain_id`,`user`)
) ENGINE=InnoDB AUTO_INCREMENT=25

  --
  

  Ilya Martynov (http://martynov.org/ [martynov.org])
  • Re:corrupted database? (Score:1)

    by jalh (8146) on 2007.09.12 3:55 (#57757)

    It's certainly weird. The database is not corrupted - check table and even using the extended check it says all is ok.
    
    I can reproduce by the following:
    
    mysql> CREATE TABLE `foo` (
            -> `mail_id` int(11) unsigned NOT NULL auto_increment,
        `user` varchar(64) default NULL,
            -> `user` varchar(64) default NULL,
            -> `mail_type_id` int(10) unsigned NOT NULL,
            -> `domain_id` int(10) unsigned NOT NULL,
            -> PRIMARY KEY (`mail_id`),
            -> KEY `mail_type_id` (`mail_type_id`),
            -> KEY `domain_id` (`domain_id`,`user`)
            -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> insert into foo set mail_type_id = 4, domain_id = 6;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into foo set mail_type_id = 4, domain_id = 6, user = '';
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from foo;
    +---------+------+--------------+-----------+
    | mail_id | user | mail_type_id | domain_id |
    +---------+------+--------------+-----------+
    | 1 | NULL | 4 | 6 |
    | 2 | NULL | 4 | 6 |
    +---------+------+--------------+-----------+
    2 rows in set (0.00 sec)
    
    wtf? I guess after the given point, the program which accesses the database was changed to insert user as '' rather than null, but why is mysql displaying a '' varchar string as NULL?
    

    Reply to This

    Parent

    • Oracle like feature? (Score:2)

      by IlyaM (2933)

      Interesting. I tried exactly the same SQL statements and I get different result:

      mysql> CREATE TABLE `foo` (
          ->         `mail_id` int(11) unsigned NOT NULL auto_increment,
          ->         `user` varchar(64) default NULL,
          ->         `mail_type_id` int(10) unsigned NOT NULL,
          ->         `domain_id` int(10) unsigned NOT NULL,
          -> 

      --
      

      Ilya Martynov (http://martynov.org/ [martynov.org])
      • Re: (Score:1)

        by jalh (8146)

        The setup on my test box was a standard centos5 install with no extra config. Perhaps the version is buggy or something?
        • Re: (Score:2)

          by IlyaM (2933)

          Yep, it is buggy. See my other comment [perl.org].

          --
          

          Ilya Martynov (http://martynov.org/ [martynov.org])