All,
ok, I found the reason why the non-latin1 bug of 4.0.12 affects also the
default latin1 charset in 4.1.0.
The reason is that the charset 'name' (really the collation name) is
internally 'latin1_swedish_ci', and InnoDB thinks it is a non-'latin1'
charset!
(gdb) print *default_charset_info
$2 = {number = 8, primary_number = 0, binary_number = 0, state = 33,
csname = 0x8389ef0 "latin1", name = 0x8389ede "latin1_swedish_ci"
Current InnoDB-4.1 still does not support multiple charsets, but uses always
the default charset and collation of mysqld. We will see if the support
makes it to 4.1.1.
This also means that if you have created tables with 4.1.0, then you must
not change the default charset later, because InnoDB does not remember the
charset in the table yet. Let us hope InnoDB-4.1.1 will.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL
----- Original Message -----
From: "Paul DuBois" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Tuesday, September 30, 2003 1:31 AM
Subject: Re: FOREIGN KEY Weirdness in mySQL 4.1 with VARCHAR
> At 15:59 +0300 9/28/03, Victoria Reznichenko wrote:
> >Ed Smith <[EMAIL PROTECTED]> wrote:
> >> Greetings. When I execute the SQL script below in
> >> mySQL 4.1, I get
> >>
> >> ERROR 1216: Cannot add or update a child row: a
> >> foreign key constraint fails
> >>
> >> It is, of course, choking on the enroll row insert.
> >> Why is this happening? Here are some things that make
> >> the problem go away:
> >>
> >> 1. Take out the "name VARCHAR(30)" attribute from
> >> student OR change the type to CHAR(30): In Section
> >> 6.5.3.1 (second bullet), it says that if one attribute
> >> is variable length, all attributes "silently" become
> >> variable length. Does this mean student.sid is really
> >> a VARCHAR? Could this be related to the problem?
> >>
> >> 2. Change type of enroll.sid to VARCHAR(5).
> >>
> >> 3. Change "Earl" to "Early"
> >>
> >> My theory: student.sid get "silently changed" from
> >> CHAR to VARCHAR since student.name is VARCHAR;
> >> however, enroll.sid does not change because there are
> >> no variable length fields in enroll. This means that
> >>
> >> enroll.sid = "Earl "
> >> student.sid = "Earl"
> >>
> >> Consequently, there is no match.
> >>
> >> Note that I did try specifying a length for the index
> >> on sid (i.e., "INDEX sidindex (sid(5)),") but that
> >> didn't help. Even shortening to 4 doesn't help, which
> >> doesn't jive with my cohersion theory, assuming I
> >> understand the index length specification.
> >>
> >
> >I tested your example and it worked fine for me. I didn't get any error.
> >What is value of default-character-set?
>
> I tried it and it worked for me as well -- with MySQL 4.1.1.
> With 4.1.0, I get the same error. Looks like a problem with
> 4.1.0 that is fixed in 4.1.1.
>
> --
> Paul DuBois, Senior Technical Writer
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com
>
> Are you MySQL certified? http://www.mysql.com/certification/
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
