Hi all!
Look:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.13-rc |
+-----------+
1 row in set (0.00 sec)
mysql> create database test1 charset='utf8';
Query OK, 1 row affected (0.01 sec)
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE TPais (
-> idTPais TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> pais CHAR(100) NULL,
-> activo CHAR(1) NULL DEFAULT 'A',
-> PRIMARY KEY(idTPais)
-> )
-> TYPE=InnoDB;
CREATE TABLE TEstado (
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> CREATE TABLE TEstado (
-> idTEstado INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> TPais_idTPais TINYINT UNSIGNED NOT NULL,
-> estado CHAR(100) NULL,
-> activo CHAR(1) NULL DEFAULT 'A',
-> PRIMARY KEY(idTEstado),
-> INDEX TEstado_FKIndex1(TPais_idTPais),
-> FOREIGN KEY(TPais_idTPais)
-> REFERENCES TPais(idTPais)
-> ON DELETE NO ACTION
-> ON UPDATE NO ACTION
-> )
-> TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> CREATE TABLE TCiudad (
-> idTCiudad INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> TEstado_idTEstado INTEGER UNSIGNED NOT NULL,
-> ciudad CHAR(100) NULL,
-> activo CHAR(1) NULL DEFAULT 'A',
-> PRIMARY KEY(idTCiudad),
-> INDEX TCiudad_FKIndex1(TEstado_idTEstado),
-> FOREIGN KEY(TEstado_idTEstado)
-> REFERENCES TEstado(idTEstado)
-> ON DELETE NO ACTION
-> ON UPDATE NO ACTION
-> )
-> TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> CREATE TABLE TRuta (
-> idTRuta INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> TCiudad_destino INTEGER UNSIGNED NOT NULL,
-> TCiudad_origen INTEGER UNSIGNED NOT NULL,
-> nombre CHAR(100) NULL,
-> km REAL(9,2) NULL,
-> PRIMARY KEY(idTRuta),
-> INDEX TRuta_FKIndex1(TCiudad_origen),
-> INDEX TRuta_FKIndex2(TCiudad_destino),
-> FOREIGN KEY(TCiudad_origen)
-> REFERENCES TCiudad(idTCiudad)
-> ON DELETE NO ACTION
-> ON UPDATE NO ACTION,
-> FOREIGN KEY(TCiudad_destino)
-> REFERENCES TCiudad(idTCiudad)
-> ON DELETE NO ACTION
-> ON UPDATE NO ACTION
-> )
-> TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| TCiudad |
| TEstado |
| TPais |
| TRuta |
+-----------------+
4 rows in set (0.00 sec)
mysql> create or replace view VRuta as
-> SELECT a.idTRuta,a.nombre,a.km, concat(c.estado,', ',b.ciudad)
origen,
-> concat(e.estado,', ',d.ciudad) Destino
-> FROM TRuta a,TCiudad b, TEstado c, TCiudad d, TEstado e
-> where a.TCiudad_origen = b.idTCiudad
-> and b.TEstado_idTEstado = c.idTEstado
-> and a.TCiudad_destino = d.idTCiudad
-> and d.TEstado_idTEstado = e.idTEstado;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| TCiudad |
| TEstado |
| TPais |
| TRuta |
| VRuta |
+-----------------+
5 rows in set (0.00 sec)
mysql> quit
Bye
apps# mysqldump --databases test1
-- MySQL dump 10.9
--
-- Host: localhost Database: test1
-- ------------------------------------------------------
-- Server version 5.0.13-rc
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `test1`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test1` /*!40100 DEFAULT CHARACTER
SET utf8 */;
USE `test1`;
--
-- Table structure for table `TCiudad`
--
DROP TABLE IF EXISTS `TCiudad`;
CREATE TABLE `TCiudad` (
`idTCiudad` int(10) unsigned NOT NULL auto_increment,
`TEstado_idTEstado` int(10) unsigned NOT NULL,
`ciudad` char(100) default NULL,
`activo` char(1) default 'A',
PRIMARY KEY (`idTCiudad`),
KEY `TCiudad_FKIndex1` (`TEstado_idTEstado`),
CONSTRAINT `TCiudad_ibfk_1` FOREIGN KEY (`TEstado_idTEstado`) REFERENCES
`TEstado` (`idTEstado`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `TCiudad`
--
/*!40000 ALTER TABLE `TCiudad` DISABLE KEYS */;
LOCK TABLES `TCiudad` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `TCiudad` ENABLE KEYS */;
--
-- Table structure for table `TEstado`
--
DROP TABLE IF EXISTS `TEstado`;
CREATE TABLE `TEstado` (
`idTEstado` int(10) unsigned NOT NULL auto_increment,
`TPais_idTPais` tinyint(3) unsigned NOT NULL,
`estado` char(100) default NULL,
`activo` char(1) default 'A',
PRIMARY KEY (`idTEstado`),
KEY `TEstado_FKIndex1` (`TPais_idTPais`),
CONSTRAINT `TEstado_ibfk_1` FOREIGN KEY (`TPais_idTPais`) REFERENCES
`TPais` (`idTPais`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `TEstado`
--
/*!40000 ALTER TABLE `TEstado` DISABLE KEYS */;
LOCK TABLES `TEstado` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `TEstado` ENABLE KEYS */;
--
-- Table structure for table `TPais`
--
DROP TABLE IF EXISTS `TPais`;
CREATE TABLE `TPais` (
`idTPais` tinyint(3) unsigned NOT NULL auto_increment,
`pais` char(100) default NULL,
`activo` char(1) default 'A',
PRIMARY KEY (`idTPais`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `TPais`
--
/*!40000 ALTER TABLE `TPais` DISABLE KEYS */;
LOCK TABLES `TPais` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `TPais` ENABLE KEYS */;
--
-- Table structure for table `TRuta`
--
DROP TABLE IF EXISTS `TRuta`;
CREATE TABLE `TRuta` (
`idTRuta` int(10) unsigned NOT NULL auto_increment,
`TCiudad_destino` int(10) unsigned NOT NULL,
`TCiudad_origen` int(10) unsigned NOT NULL,
`nombre` char(100) default NULL,
`km` double(9,2) default NULL,
PRIMARY KEY (`idTRuta`),
KEY `TRuta_FKIndex1` (`TCiudad_origen`),
KEY `TRuta_FKIndex2` (`TCiudad_destino`),
CONSTRAINT `TRuta_ibfk_1` FOREIGN KEY (`TCiudad_origen`) REFERENCES
`TCiudad` (`idTCiudad`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `TRuta_ibfk_2` FOREIGN KEY (`TCiudad_destino`) REFERENCES
`TCiudad` (`idTCiudad`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `TRuta`
--
/*!40000 ALTER TABLE `TRuta` DISABLE KEYS */;
LOCK TABLES `TRuta` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `TRuta` ENABLE KEYS */;
--
-- Table structure for table `VRuta`
--
DROP TABLE IF EXISTS `VRuta`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
VIEW `VRuta` AS select `a`.`idTRuta` AS `idTRuta`,`a`.`nombre` AS
`nombre`,`a`.`km` AS `km`,concat(`c`.`estado`,_utf8', ',`b`.`ciudad`) AS
`origen`,concat(`e`.`estado`,_utf8', ',`d`.`ciudad`) AS `Destino` from
((((`TRuta` `a` join `TCiudad` `b`) join `TEstado` `c`) join `TCiudad` `d`)
join `TEstado` `e`) where ((`a`.`TCiudad_origen` = `b`.`idTCiudad`) and
(`b`.`TEstado_idTEstado` = `c`.`idTEstado`) and (`a`.`TCiudad_destino` =
`d`.`idTCiudad`) and (`d`.`TEstado_idTEstado` = `e`.`idTEstado`));
Segmentation fault (core dumped)
apps# uname -a
FreeBSD apps.medel.com.mx 5.4-RELEASE FreeBSD 5.4-RELEASE #0: Sun May 8
10:21:06 UTC 2005
root@stripped:/usr/obj/usr/src/sys/GENERIC i386
apps# tail -f apps.medel.com.mx.err
Table status:
Opened tables: 12
Open tables: 6
Open files: 12
Open streams: 0
Alarm status:
Active alarms: 0
Max used alarms: 0
Next alarm time: 0
I've had a look at bugs.mysql.com but I wasn't able to find anything
similar, What could be the problem?
Regards! Tanks in advance.
+----------------------------------------+
| ISC Edwin Cruz <ecruz@stripped> |
| IT Manager |
| Transportes Medel Rogero SA de CV |
| Desk: 01 (449) 910 30 90 x3054 |
| MX Mobile: 01 (449) 111 29 03 |
| Skype: softr8 |
+----------------------------------------+
