mysql 5.0: ERROR 1005 (HY000)

[saiph]

create database url;
use url



create table cath
(
name varchar(10) primary key,
) type=innodb;

create table site
(
url varchar(40) primary key,
aline varchar(40),
cath varchar(10),

constraint fk_cath foreign key(cath) references cath(name)
on delete set null on update cascade
) type=innodb;


ERROR 1005 (HY000): Can't create table './url/site.frm' (errno: 150)

the perms are correct indeed without the constraint there are no problems.

can you help me in testing?



mororover when running the test suite i have:

Errors are (from /usr/local/mysql/mysql-test/var/log/mysqltest-time) :
/usr/local/mysql/bin/mysqltest: At line 26: query 'CREATE TABLE `ÔÁÂÌÉÃÁ`
(
ÐÏÌÅ CHAR(32) CHARACTER SET koi8r NOT NULL COMMENT ËÏÍÍÅÎÔÁÒÉÊ ÐÏÌÑ
) COMMENT ËÏÍÍÅÎÔÁÒÉÊ ÔÁÂÌÉÃÙ' failed: 1: Can't create/write to file 
'./test/ÑÐ
°Ð±Ð»Ð¸Ñа.frm' (Errcode: 22)
(the last line(s) may be the ones that caused the die() in mysqltest)



and this:

Errors are (from /usr/local/mysql/mysql-test/var/log/mysqltest-time) :
/usr/local/mysql/bin/mysqltest: At line 41: Result length mismatch
(the last line(s) may be the ones that caused the die() in mysqltest)
Below are the diffs between actual and expected results:
---
*** r/timezone.result   Mon Dec 22 18:41:49 2003
--- r/timezone.reject   Sun Jun  6 09:03:22 2004
***
*** 1,7 
  DROP TABLE IF EXISTS t1;
  show variables like timezone;
  Variable_name Value
! timezone  MET
  select @a:=FROM_UNIXTIME(1);
  @a:=FROM_UNIXTIME(1)
  1970-01-01 01:00:01
--- 1,7 
  DROP TABLE IF EXISTS t1;
  show variables like timezone;
  Variable_name Value
! timezone  MEST
  select @a:=FROM_UNIXTIME(1);
  @a:=FROM_UNIXTIME(1)
  1970-01-01 01:00:01
---

Failed 2/229 tests, 99.12% successful.




-- 
here are more things in heaven and earth,
horatio, than are dreamt of in your philosophy.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: update synopsis error

[saiph]

 What version of MySQL are you using? 

i use:
mysql  Ver 12.22 Distrib 4.0.18, for pc-linux-gnu (i386).

 UPDATE t1, t2 SET t1.name=t2.name WHERE t1.id=1 AND t2.id=1;

it works :), tnx again.



-- 
here are more things in heaven and earth,
horatio, than are dreamt of in your philosophy.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

update synopsis error

[saiph]

hi,

mysql update table1
set c = (select c2 from table2 where c2 = 'value')
where  id = 123;

give me an ERROR 1064.


i.e.

create table t1 ( id int primary key, name varchar(20) );
create table t2 ( id int primary key, name varchar(20) );

insert into t1 values(1, 'not right')
insert into t2 values(1, 'right')

update t1 set name = (select name from t2 where id = 1) where id = 1;

how i can update right?


tnx.

--

here are more things in heaven and earth,

horatio, than are dreamt of in your philosophy.

---

-- 
here are more things in heaven and earth,
horatio, than are dreamt of in your philosophy.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: foreign keys help

[saiph]

- snip -
Because your tables are not InnoDB.
Check if InnoDB is enabled:
SHOW VARIABLES LIKE have_innodb;

- snip -

no, InnoDB is not enabled. how can i enable it?

the referece manual show a my.cnf configuration for a machine with at least 
2gb of ram and 60 of hard disk. 
how can i adapt this configuration for an home usage?

is this a sufficient condition to emerge innodb tables?


tnx a lot

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: foreign keys help

[saiph]

alea mysql -V
mysql  Ver 12.22 Distrib 4.0.17, for pc-linux-gnu (i386)

but 

mysql  SHOW VARIABLES LIKE have_innodb;
+---+---+
| Variable_name | Value |
+---+---+
| have_innodb   | NO|
+---+---+
1 row in set (0.09 sec)

why? 

the gentoo ebuild configure mysql with innodb support: 
where i m getting wrong? how can i see a yes  working 'value'?

tnx again

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

foreign keys help

[saiph]

hi list,

i'm studing foreign keys but my sql code does not react as
would imagine when i insert inconsistent data.

i.e.

create database urls;
use urls;

create table caths
(
name varchar(7) primary key
) type = innodb;

create table urls
(
name varchar(10) primary key,
home varchar(30) unique,
cath varchar(7),
constraint fk foreign key(cath) references caths(name) 
on update cascade on delete set null
) type = innodb;

load data local infile caths.lst into table caths;
load data local infile urls.lst into table urls;

why it it possible to insert urls such as:
mysql   mysql.com   dev-null

when dev-null in not a value of the attribute caths.name?


thanks.


-- 
here are more things in heaven and earth,
horatio, than are dreamt of in your philosophy.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]