slaves (bin-logs?) do not respect --lines-terminated-by switch of mysqlimport

[temu]

Description:
slaves does not honore the mysqlimport --lines-terminated-by='\n\n' switch
How-To-Repeat:
i've set up two machines as master - slave. i start out with a running
replication in a sane state.

now i do on the master:

mysql -e '
drop table TOR5;
create table TOR5 (
lx_company INT NOT NULL,
lx_type INT NOT NULL,
request_min INT NOT NULL,
request_texts tinytext NOT NULL,
INDEX (lx_company),
INDEX (lx_type),
INDEX (request_min));'

 mysqlimport --lines-terminated-by='\n\n' TOR5
 sdb.TOR5: Records: 6488  Deleted: 0  Skipped: 0  Warnings: 8

 using input like this (tab-separated)
 8--
 826 11811263601 Analogplatine mit wand komlett/ Best.-Nr.: 3450 246/7
 Analogplatine komplett mit wand #3450246-7

 147248441263611 Akku 6V 1,2Ah f=FCr Netzteil brille

 797 35031263701 Kapsulotomiespitze Nr.:VE 201726
 Kapsulotomiespitze VE 201726
 Oertli-Spitzen VE 201726
 Kapsolutomie-Spitze VE201726
 8--


 and this is what i get:

 on the master:
 mysql select count(*) from TOR5;
 +--+
 | count(*) |
 +--+
 | 6488 |
 +--+

 and on the slave:
 mysql select count(*) from TOR5;
 +--+
 | count(*) |
 +--+
 |13966 |
 +--+

 for the example above on the master:
 mysql select * from TOR5 where request_min=1263701;
 ++-+-+-+
 | lx_company | lx_type | request_min | request_texts   |
 ++-+-+-
 |797 |3503 | 1263701 | Kapsulotomiespitze Nr.:VE 201726
 Kapsulotomiespitze VE 201726
 Oertli-Spitzen VE 201726
 Kapsolutomie-Spitze VE201726 |
 ++-+-+-+
 1 row in set (0.01 sec)

 and the slave:
 mysql select * from TOR5 where request_min=1263701;
 ++-+-+--+
 | lx_company | lx_type | request_min | request_texts|
 ++-+-+--+
 |797 |3503 | 1263701 | Kapsulotomiespitze Nr.:VE 201726 |
 ++-+-+--+
 1 row in set (0.00 sec)


 the slave does not honore multiline records, but read each line as a
 separate record and create thousands of rows like that:

 mysql select * from TOR5 where lx_company  1 limit 3;
 ++-+-+---+
 | lx_company | lx_type | request_min | request_texts |
 ++-+-+---+
 |  0 |   0 |   0 |   |
 |  0 |   0 |   0 |   |
 |  0 |   0 |   0 |   |
 ++-+-+---+
 

Fix:


Submitter-Id:  
Originator:kai uwe tempel
Organization:  university leipzig
MySQL support: none
Synopsis:  slaves (bin-logs?) do not respect --lines-terminated-by switch of 
mysqlimport
Severity:  serious
Priority:  medium
Category:  mysql
Class: sw-bug
Release:   mysql-3.23.47 (Source distribution)
Server: /usr/bin/mysqladmin  Ver 8.23 Distrib 3.23.47, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB  MySQL Finland AB  TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version  3.23.47-log
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 20 hours 39 min 45 sec

Threads: 1  Questions: 129  Slow queries: 2  Opens: 27  Flush tables: 2  Open tables: 
4 Queries per second avg: 0.002
Environment:

System: Linux pc59 2.4.17 #10 Thu Dec 27 09:05:25 CET 2001 i586 unknown
Architecture: i586

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 20011002 (Debian prerelease)
Compilation info: CC='gcc'  CFLAGS=''  CXX='c++'  CXXFLAGS=''  LDFLAGS=''
LIBC: 
lrwxrwxrwx1 root root   14 Jan 28 18:44 /lib/libc.so.5 - 
libc.so.5.4.46
-rw-r--r--1 root root   563068 Jan 25 13:45 /lib/libc.so.5.4.46
lrwxrwxrwx1 root root   

Mysql syntax

[temu-jin]

I have been trying to figure out how to do this, and I am sure that I am just 
overlooking somthing small.

Suppose I create two tables as follows:

CREATE TABLE system (
sysid INT NOT NULL UNSIGNED AUTO_INCREMENT PRIMARY KEY,
sysname VARCHAR(12),
os VARCHAR(10),
cpumhz INT
);

CREATE TABLE user (
sysid INT REFERENCES system,
username VARCHAR(30)
);

Also suppose that I populated the system table with the following information:

+---+---+++
| sysid | sysname |   os   | cpumhz |
+---+---+---+-+
| 1   |  system1 | Linux |   1000   |
| 2   | system2  | Unix  |300|
+---+---+-++

If I wanted to asssign John Doe to system1, how would I write the statement in sql to 
pull the sysid from the system table via the sysname, and then to update the user 
table with the sysid and John Doe?

Thanks,

Phil

Multiple Tables

[temu-jin]

I have been looking through the documentation and searching through the list for 
answers to this question, but have not yet figured out how to do so.

Basically, I want to have multiple tables linked together.  I have seen a lot of talk 
about foreign keys and that MySQL does not support them.  I am not sure if foreign 
keys is the answer to my problem, but I could definetly use some suggestions from the 
more experienced MySQL users.

I want to have one table with information about computer systems such as system name, 
os, and cpu speed which used an integer , called sysid, for the primary key.  I also 
wanted to have a table which listed the people who use a particular system.  I figured 
that I would need to have the  user table reference the sysid so that I could make 
sure that the right people were listed for the right system.

I also wanted to avoid having to set the sysid manually when inserting data into the 
user table.

Please let me hear what you think the best solution would be.

Thanks,

Phil