List:German« Previous MessageNext Message »
From:p.schiegg Date:July 24 2003 8:33am
Subject:MySQL 4.1.0 Alpha schmiert bei komplexem Statement ab
View as plain text  
Hallo zusammen

Ich habe einen MySQL-Server 4.1 auf Windows 2000 installiert und es läuft
soweit eigentlich alles; auch die neuen Features wie Subselects etc. gehen
anstandslos.

Nun habe ich aber ein Problem:
Sobald ich ein bestimmtes, zugegeben recht grosses Statement ;-) absetze,
meldet Windows folgenden Fehler und MySQL schmiert ab:
---------------
mysqld-max-nt.exe - Fehler in Anwendung

Die Anweisung in 0x...... verweist auf Speicher........
Der Vorgang "read" konnte nicht auf dem Speicher durchgeführt werden.

Klicken sie auf OK um das Programm zu beenden....
---------------

Das Statement sieht folgendermassen aus:

SELECT * FROM
(
SELECT * FROM User WHERE User_ID IN
(
SELECT Object_ID FROM Group_right INNER JOIN User_group ON Group_right.Group_id
= User_group.Group_id
INNER JOIN Right_type ON Group_right.Right_ID = Right_type.Right_ID WHERE
Right_type.Value >= 10 AND User_ID = 1 AND Physical = 1 AND Object_type_ID
= 1 AND Object_ID NOT IN
(
SELECT Object_ID FROM User_right INNER JOIN Right_type ON User_right.Right_ID
= Right_type.Right_ID WHERE Right_type.Value >= 10 AND User_ID = 1 AND Physical
= 1 AND Object_type_ID = 1
)
UNION
SELECT Object_ID FROM User_right INNER JOIN Right_type ON User_right.Right_ID
= Right_type.Right_ID WHERE Right_type.Value >= 10 AND User_ID = 1 AND Physical
= 1 AND Object_type_ID = 1
)
) AS User
INNER JOIN
(
SELECT * FROM User_group WHERE Group_ID IN
(
SELECT Object_ID FROM Group_right INNER JOIN User_group ON Group_right.Group_id
= User_group.Group_id
INNER JOIN Right_type ON Group_right.Right_ID = Right_type.Right_ID WHERE
Right_type.Value >= 10 AND User_ID = 1 AND Physical = 1 AND Object_type_ID
= 2 AND
Object_ID NOT IN
(
SELECT Object_ID FROM User_right INNER JOIN Right_type ON User_right.Right_ID
= Right_type.Right_ID WHERE Right_type.Value >= 10 AND User_ID = 1 AND Physical
= 1 AND Object_type_ID = 2
)
UNION
SELECT Object_ID FROM User_right INNER JOIN Right_type ON User_right.Right_ID
= Right_type.Right_ID WHERE Right_type.Value >= 10 AND User_ID = 1 AND Physical
= 1 AND Object_type_ID = 2
)
) as view1 ON User.User_ID= view1.User_ID

Gibts bei MySQL irgendeine Statementlängenbeschränkung oder sowas? Hab den
Rechner auch schon neugestartet => gleiches Problem.
Im Anhang befindet sich die Datenbank als db.sql Datei, falls jemand das
Statement bei sich ausprobieren will. Es sind auch Daten drin, allerdings
nur vereinzelte Testdaten.

Hoffe jemand kann helfen.

Greets
Pascal


-- MySQL dump 10.2
--
-- Host: localhost    Database: 
---------------------------------------------------------
-- Server version	4.1.0-alpha-max-nt

--
-- Current Database: mysql
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ mysql;

USE mysql;

--
-- Table structure for table 'columns_priv'
--

DROP TABLE IF EXISTS columns_priv;
CREATE TABLE columns_priv (
  Host char(60) binary NOT NULL default '',
  Db char(64) binary NOT NULL default '',
  User char(16) binary NOT NULL default '',
  Table_name char(64) binary NOT NULL default '',
  Column_name char(64) binary NOT NULL default '',
  Timestamp timestamp NOT NULL,
  Column_priv set('Select','Insert','Update','References') NOT NULL default '',
  PRIMARY KEY  (Host,Db,User,Table_name,Column_name)
) TYPE=MyISAM CHARSET=latin1 COMMENT='Column privileges';

--
-- Dumping data for table 'columns_priv'
--

/*!40000 ALTER TABLE columns_priv DISABLE KEYS */;
LOCK TABLES columns_priv WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE columns_priv ENABLE KEYS */;

--
-- Table structure for table 'db'
--

DROP TABLE IF EXISTS db;
CREATE TABLE db (
  Host char(60) binary NOT NULL default '',
  Db char(64) binary NOT NULL default '',
  User char(16) binary NOT NULL default '',
  Select_priv enum('N','Y') NOT NULL default 'N',
  Insert_priv enum('N','Y') NOT NULL default 'N',
  Update_priv enum('N','Y') NOT NULL default 'N',
  Delete_priv enum('N','Y') NOT NULL default 'N',
  Create_priv enum('N','Y') NOT NULL default 'N',
  Drop_priv enum('N','Y') NOT NULL default 'N',
  Grant_priv enum('N','Y') NOT NULL default 'N',
  References_priv enum('N','Y') NOT NULL default 'N',
  Index_priv enum('N','Y') NOT NULL default 'N',
  Alter_priv enum('N','Y') NOT NULL default 'N',
  Create_tmp_table_priv enum('N','Y') NOT NULL default 'N',
  Lock_tables_priv enum('N','Y') NOT NULL default 'N',
  PRIMARY KEY  (Host,Db,User),
  KEY User (User)
) TYPE=MyISAM CHARSET=latin1 COMMENT='Database privileges';

--
-- Dumping data for table 'db'
--

/*!40000 ALTER TABLE db DISABLE KEYS */;
LOCK TABLES db WRITE;
INSERT INTO db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y');
UNLOCK TABLES;
/*!40000 ALTER TABLE db ENABLE KEYS */;

--
-- Table structure for table 'func'
--

DROP TABLE IF EXISTS func;
CREATE TABLE func (
  name char(64) binary NOT NULL default '',
  ret tinyint(1) NOT NULL default '0',
  dl char(128) NOT NULL default '',
  type enum('function','aggregate') NOT NULL default 'function',
  PRIMARY KEY  (name)
) TYPE=MyISAM CHARSET=latin1 COMMENT='User defined functions';

--
-- Dumping data for table 'func'
--

/*!40000 ALTER TABLE func DISABLE KEYS */;
LOCK TABLES func WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE func ENABLE KEYS */;

--
-- Table structure for table 'host'
--

DROP TABLE IF EXISTS host;
CREATE TABLE host (
  Host char(60) binary NOT NULL default '',
  Db char(64) binary NOT NULL default '',
  Select_priv enum('N','Y') NOT NULL default 'N',
  Insert_priv enum('N','Y') NOT NULL default 'N',
  Update_priv enum('N','Y') NOT NULL default 'N',
  Delete_priv enum('N','Y') NOT NULL default 'N',
  Create_priv enum('N','Y') NOT NULL default 'N',
  Drop_priv enum('N','Y') NOT NULL default 'N',
  Grant_priv enum('N','Y') NOT NULL default 'N',
  References_priv enum('N','Y') NOT NULL default 'N',
  Index_priv enum('N','Y') NOT NULL default 'N',
  Alter_priv enum('N','Y') NOT NULL default 'N',
  Create_tmp_table_priv enum('N','Y') NOT NULL default 'N',
  Lock_tables_priv enum('N','Y') NOT NULL default 'N',
  PRIMARY KEY  (Host,Db)
) TYPE=MyISAM CHARSET=latin1 COMMENT='Host privileges;  Merged with database privileges';

--
-- Dumping data for table 'host'
--

/*!40000 ALTER TABLE host DISABLE KEYS */;
LOCK TABLES host WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE host ENABLE KEYS */;

--
-- Table structure for table 'tables_priv'
--

DROP TABLE IF EXISTS tables_priv;
CREATE TABLE tables_priv (
  Host char(60) binary NOT NULL default '',
  Db char(64) binary NOT NULL default '',
  User char(16) binary NOT NULL default '',
  Table_name char(60) binary NOT NULL default '',
  Grantor char(77) NOT NULL default '',
  Timestamp timestamp NOT NULL,
  Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter') NOT NULL default '',
  Column_priv set('Select','Insert','Update','References') NOT NULL default '',
  PRIMARY KEY  (Host,Db,User,Table_name),
  KEY Grantor (Grantor)
) TYPE=MyISAM CHARSET=latin1 COMMENT='Table privileges';

--
-- Dumping data for table 'tables_priv'
--

/*!40000 ALTER TABLE tables_priv DISABLE KEYS */;
LOCK TABLES tables_priv WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE tables_priv ENABLE KEYS */;

--
-- Table structure for table 'user'
--

DROP TABLE IF EXISTS user;
CREATE TABLE user (
  Host varchar(60) binary NOT NULL default '',
  User varchar(16) binary NOT NULL default '',
  Password varchar(45) binary NOT NULL default '',
  Select_priv enum('N','Y') NOT NULL default 'N',
  Insert_priv enum('N','Y') NOT NULL default 'N',
  Update_priv enum('N','Y') NOT NULL default 'N',
  Delete_priv enum('N','Y') NOT NULL default 'N',
  Create_priv enum('N','Y') NOT NULL default 'N',
  Drop_priv enum('N','Y') NOT NULL default 'N',
  Reload_priv enum('N','Y') NOT NULL default 'N',
  Shutdown_priv enum('N','Y') NOT NULL default 'N',
  Process_priv enum('N','Y') NOT NULL default 'N',
  File_priv enum('N','Y') NOT NULL default 'N',
  Grant_priv enum('N','Y') NOT NULL default 'N',
  References_priv enum('N','Y') NOT NULL default 'N',
  Index_priv enum('N','Y') NOT NULL default 'N',
  Alter_priv enum('N','Y') NOT NULL default 'N',
  Show_db_priv enum('N','Y') NOT NULL default 'N',
  Super_priv enum('N','Y') NOT NULL default 'N',
  Create_tmp_table_priv enum('N','Y') NOT NULL default 'N',
  Lock_tables_priv enum('N','Y') NOT NULL default 'N',
  Execute_priv enum('N','Y') NOT NULL default 'N',
  Repl_slave_priv enum('N','Y') NOT NULL default 'N',
  Repl_client_priv enum('N','Y') NOT NULL default 'N',
  ssl_type enum('','ANY','X509','SPECIFIED') NOT NULL default '',
  ssl_cipher blob NOT NULL,
  x509_issuer blob NOT NULL,
  x509_subject blob NOT NULL,
  max_questions int(11) unsigned NOT NULL default '0',
  max_updates int(11) unsigned NOT NULL default '0',
  max_connections int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (Host,User)
) TYPE=MyISAM CHARSET=latin1 COMMENT='Users and global privileges';

--
-- Dumping data for table 'user'
--

/*!40000 ALTER TABLE user DISABLE KEYS */;
LOCK TABLES user WRITE;
INSERT INTO user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0),('%','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0),('localhost','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0),('%','','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0);
UNLOCK TABLES;
/*!40000 ALTER TABLE user ENABLE KEYS */;

--
-- Current Database: schlaffhausen
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ schlaffhausen;

USE schlaffhausen;

--
-- Table structure for table 'group_right'
--

DROP TABLE IF EXISTS group_right;
CREATE TABLE group_right (
  Group_ID mediumint(8) unsigned NOT NULL default '0',
  Physical tinyint(1) NOT NULL default '0',
  Object_type_ID mediumint(8) unsigned NOT NULL default '0',
  Object_ID mediumint(8) unsigned NOT NULL default '0',
  Right_ID mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (Group_ID,Physical,Object_type_ID,Object_ID)
) TYPE=MyISAM CHARSET=latin1;

--
-- Dumping data for table 'group_right'
--

/*!40000 ALTER TABLE group_right DISABLE KEYS */;
LOCK TABLES group_right WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE group_right ENABLE KEYS */;

--
-- Table structure for table 'object_pair'
--

DROP TABLE IF EXISTS object_pair;
CREATE TABLE object_pair (
  Physical_parent tinyint(1) NOT NULL default '0',
  Object_type_ID_parent mediumint(8) unsigned NOT NULL default '0',
  Object_ID_parent mediumint(8) unsigned NOT NULL default '0',
  Physical_child tinyint(1) NOT NULL default '0',
  Object_type_ID_child mediumint(8) unsigned NOT NULL default '0',
  Object_ID_child mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (Physical_parent,Object_type_ID_parent,Object_ID_parent,Physical_child,Object_type_ID_child,Object_ID_child)
) TYPE=MyISAM CHARSET=latin1;

--
-- Dumping data for table 'object_pair'
--

/*!40000 ALTER TABLE object_pair DISABLE KEYS */;
LOCK TABLES object_pair WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE object_pair ENABLE KEYS */;

--
-- Table structure for table 'object_relation'
--

DROP TABLE IF EXISTS object_relation;
CREATE TABLE object_relation (
  Physical_parent tinyint(1) NOT NULL default '0',
  Object_type_ID_parent mediumint(8) unsigned NOT NULL default '0',
  Physical_child tinyint(1) NOT NULL default '0',
  Object_type_ID_child mediumint(8) unsigned NOT NULL default '0',
  Pair_type tinyint(1) NOT NULL default '0',
  Pair_table varchar(20) default '',
  Parent_table varchar(20) default '',
  Child_table varchar(20) default '',
  Parent_key varchar(20) default '',
  Child_key varchar(20) default '',
  PRIMARY KEY  (Physical_parent,Object_type_ID_parent,Physical_child,Object_type_ID_child)
) TYPE=MyISAM CHARSET=latin1;

--
-- Dumping data for table 'object_relation'
--

/*!40000 ALTER TABLE object_relation DISABLE KEYS */;
LOCK TABLES object_relation WRITE;
INSERT INTO object_relation VALUES (1,1,1,2,3,'User_group','User','Group','User_ID','Group_ID'),(1,2,1,1,3,'User_group','Group','User','Group_ID','User_ID');
UNLOCK TABLES;
/*!40000 ALTER TABLE object_relation ENABLE KEYS */;

--
-- Table structure for table 'particular'
--

DROP TABLE IF EXISTS particular;
CREATE TABLE particular (
  User_ID mediumint(8) unsigned NOT NULL default '0',
  First_name varchar(15) NOT NULL default '',
  Surname varchar(15) NOT NULL default '',
  Country enum('Schweiz','Deutschland','Österreich') NOT NULL default 'Schweiz',
  EMail varchar(40) NOT NULL default '',
  Village varchar(30) default '',
  ICQ varchar(20) default '',
  Phone varchar(20) default '',
  Postal_code varchar(100) default '',
  PRIMARY KEY  (User_ID)
) TYPE=InnoDB CHARSET=latin1;

--
-- Dumping data for table 'particular'
--

/*!40000 ALTER TABLE particular DISABLE KEYS */;
LOCK TABLES particular WRITE;
INSERT INTO particular VALUES (1,'Pascal','Schiegg','Schweiz','asdkfj@stripped','Dachsen','12424','235136','3452365'),(2,'Azrael','Sur_Azrael','Deutschland','asdlkf@strippedh','village','442','dfg',NULL);
UNLOCK TABLES;
/*!40000 ALTER TABLE particular ENABLE KEYS */;

--
-- Table structure for table 'physical_object'
--

DROP TABLE IF EXISTS physical_object;
CREATE TABLE physical_object (
  Object_type_ID mediumint(8) unsigned NOT NULL auto_increment,
  Object_name varchar(20) NOT NULL default '',
  Table_name varchar(20) NOT NULL default '',
  Primary_key varchar(20) NOT NULL default '',
  Description varchar(100) default '',
  PRIMARY KEY  (Object_type_ID),
  UNIQUE KEY Object_name (Object_name)
) TYPE=MyISAM CHARSET=latin1;

--
-- Dumping data for table 'physical_object'
--

/*!40000 ALTER TABLE physical_object DISABLE KEYS */;
LOCK TABLES physical_object WRITE;
INSERT INTO physical_object VALUES (1,'User','User','User_ID',NULL),(2,'Group','Group','Group_ID',NULL);
UNLOCK TABLES;
/*!40000 ALTER TABLE physical_object ENABLE KEYS */;

--
-- Table structure for table 'rank'
--

DROP TABLE IF EXISTS rank;
CREATE TABLE rank (
  Rank_ID mediumint(8) unsigned NOT NULL auto_increment,
  Rank varchar(20) NOT NULL default '',
  Description varchar(100) NOT NULL default '',
  PRIMARY KEY  (Rank_ID)
) TYPE=MyISAM CHARSET=latin1;

--
-- Dumping data for table 'rank'
--

/*!40000 ALTER TABLE rank DISABLE KEYS */;
LOCK TABLES rank WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE rank ENABLE KEYS */;

--
-- Table structure for table 'rfc_group'
--

DROP TABLE IF EXISTS rfc_group;
CREATE TABLE rfc_group (
  Group_ID mediumint(9) unsigned NOT NULL auto_increment,
  Name varchar(15) NOT NULL default '',
  Description varchar(100) default '',
  PRIMARY KEY  (Group_ID)
) TYPE=MyISAM CHARSET=latin1;

--
-- Dumping data for table 'rfc_group'
--

/*!40000 ALTER TABLE rfc_group DISABLE KEYS */;
LOCK TABLES rfc_group WRITE;
INSERT INTO rfc_group VALUES (1,'Admin','asdf'),(2,'Benutzer','asdöklfjaad');
UNLOCK TABLES;
/*!40000 ALTER TABLE rfc_group ENABLE KEYS */;

--
-- Table structure for table 'right_pair'
--

DROP TABLE IF EXISTS right_pair;
CREATE TABLE right_pair (
  Physical tinyint(1) NOT NULL default '0',
  Object_type_ID mediumint(8) unsigned NOT NULL default '0',
  Right_ID mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (Physical,Object_type_ID,Right_ID)
) TYPE=MyISAM CHARSET=latin1;

--
-- Dumping data for table 'right_pair'
--

/*!40000 ALTER TABLE right_pair DISABLE KEYS */;
LOCK TABLES right_pair WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE right_pair ENABLE KEYS */;

--
-- Table structure for table 'right_type'
--

DROP TABLE IF EXISTS right_type;
CREATE TABLE right_type (
  Right_ID mediumint(8) unsigned NOT NULL auto_increment,
  Description varchar(100) NOT NULL default '',
  Value int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (Right_ID)
) TYPE=MyISAM CHARSET=latin1;

--
-- Dumping data for table 'right_type'
--

/*!40000 ALTER TABLE right_type DISABLE KEYS */;
LOCK TABLES right_type WRITE;
INSERT INTO right_type VALUES (1,'Read',10);
UNLOCK TABLES;
/*!40000 ALTER TABLE right_type ENABLE KEYS */;

--
-- Table structure for table 'user'
--

DROP TABLE IF EXISTS user;
CREATE TABLE user (
  User_ID mediumint(8) unsigned NOT NULL auto_increment,
  Username varchar(20) NOT NULL default '',
  Password varchar(32) NOT NULL default '',
  Registered_since datetime NOT NULL default '0000-00-00 00:00:00',
  Rank_ID mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (User_ID)
) TYPE=InnoDB CHARSET=latin1;

--
-- Dumping data for table 'user'
--

/*!40000 ALTER TABLE user DISABLE KEYS */;
LOCK TABLES user WRITE;
INSERT INTO user VALUES (1,'Webmaster','test','2000-01-01 09:45:00',1),(2,'Azrael','123','2003-07-24 09:00:00',1),(3,'Neo','123','0000-00-00 00:00:00',1),(4,'Gabriel','123','2002-05-25 12:56:00',1);
UNLOCK TABLES;
/*!40000 ALTER TABLE user ENABLE KEYS */;

--
-- Table structure for table 'user_group'
--

DROP TABLE IF EXISTS user_group;
CREATE TABLE user_group (
  User_ID mediumint(8) unsigned NOT NULL default '0',
  Group_ID mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (Group_ID,User_ID)
) TYPE=MyISAM CHARSET=latin1;

--
-- Dumping data for table 'user_group'
--

/*!40000 ALTER TABLE user_group DISABLE KEYS */;
LOCK TABLES user_group WRITE;
INSERT INTO user_group VALUES (1,1);
UNLOCK TABLES;
/*!40000 ALTER TABLE user_group ENABLE KEYS */;

--
-- Table structure for table 'user_right'
--

DROP TABLE IF EXISTS user_right;
CREATE TABLE user_right (
  User_ID mediumint(8) unsigned NOT NULL default '0',
  Physical tinyint(1) NOT NULL default '0',
  Object_type_ID mediumint(8) unsigned NOT NULL default '0',
  Object_ID mediumint(8) unsigned NOT NULL default '0',
  Right_ID mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (User_ID,Physical,Object_type_ID,Object_ID)
) TYPE=MyISAM CHARSET=latin1;

--
-- Dumping data for table 'user_right'
--

/*!40000 ALTER TABLE user_right DISABLE KEYS */;
LOCK TABLES user_right WRITE;
INSERT INTO user_right VALUES (1,1,1,1,1),(1,1,1,2,1),(1,1,1,3,1),(1,1,2,1,1),(1,1,2,2,1);
UNLOCK TABLES;
/*!40000 ALTER TABLE user_right ENABLE KEYS */;

--
-- Current Database: test
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ test;

USE test;

Thread
MySQL 4.1.0 Alpha schmiert bei komplexem Statement abp.schiegg24 Jul
  • Re: MySQL 4.1.0 Alpha schmiert bei komplexem Statement abGeorg Richter24 Jul
Re: MySQL 4.1.0 Alpha schmiert bei komplexem Statement abthomas-lists24 Jul
  • Re: MySQL 4.1.0 Alpha schmiert bei komplexem Statement abGeorg Richter25 Jul