mysqldump -Q
Hi, mysqldump has an flag '-Q' which quotes all table and column names. But what about the database names? They will not be quoted (in the CREATE and USE statements)! During transformation of databases from a 3.23 server to 4.1 I have some databases with '-' inside the name (obviously this was possible with mysql-3.23). How can I handle the dump (and restore) of such databases automatically? Regards, Wolfgang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Best options for unique string
HI MD5 would be a good way of doing it. Just add a column to your user table and UPDATE users SET subscribed = 0 WHERE encryptedID = md5 hash here HTH Peter -Original Message- From: Scott Haneda [mailto:[EMAIL PROTECTED] Sent: 04 August 2004 03:37 To: MySql Subject: Best options for unique string I am building a mailing list manager, using mysql 4 at the moment. I want to have a simply web interface where one can remove themselves from a mailing list. This will most likely be supplied as a link in a email that will be sent to them when they email in and request info about a mailing list. What I don't want is to have a link like [EMAIL PROTECTED] but would rather mask that email address as a unique string. I was thiking that on INSERT I can use a timestamp with some random and that should be pretty much guaranteed to be unique, I could just use the PK but then people could fiddle the url and mess with others accounts. So I need something non sequential, rather random looking at least, perhaps somehow make mysql case sensitive on this one as well. (How does one make mysql case senseitive on a field?) Would MD5(user_email_address) pretty much be what I am after? I don't suppose there is any way to un-MD5 something? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- 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]
RE: SQL Syntax Question
Thank you for trying to help me.
The output is wrong
I get either
Event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Or
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
But not what I need
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 2
Details 2 for event 2
Details 3 for event 2
-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 04, 2004 12:08 AM
To: Karl-Heinz Schulz; [EMAIL PROTECTED]
Subject: Re: SQL Syntax Question
- Original Message -
From: Karl-Heinz Schulz [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, August 03, 2004 9:18 PM
Subject: SQL Syntax Question
I tried to get an answer on the PHP mailing list and I was told that this
list would be quicker to get me a solution.
I have two tables Event and Eventdetails (structures dump can be found
at the end of the message).
I want to display all events and the related information from the
eventdetails table like
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 2
Details 2 for event 2
Details 3 for event 2
Etc.
I cannot figure it out.
Here is my PHP code.
--
--
?php
require(../admin/functions.php);
include(../admin/header.inc.php);
?
?
$event_query = mysql_query(select id, inserted, information, eventname,
date, title from event order by inserted desc LIMIT 0 , 30);
while($event = mysql_fetch_row($event_query)){
print(bspan style=\font-family: Arial, Helvetica,
sans-serif;color:#003300;font-size:14px;\.html_decode($event[5])./span
/bbr);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($event[4])./spanbr);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($event[2])./spanp);
$eventdetail_query = mysql_query(select informations, titles, file_name
from eventdetail, event where eventdetail.event =.$event[0]);
//$eventdetail_query = mysql_query(select titles, informations, file_name
from eventdetail, event where eventdetail.event = event.id);
while($eventdetail = mysql_fetch_row($eventdetail_query)){
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[0])./span);
print(nbspspan style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[1])./span);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[2])./spanp);
}
}
?
--
--
What am I missing?
TIA
Karl-Heinz
#
# Table structure for table `event`
#
CREATE TABLE event (
id smallint(2) unsigned NOT NULL auto_increment,
veranstaltung smallint(2) unsigned NOT NULL default '0',
inserted date NOT NULL default '-00-00',
information text NOT NULL,
eventname text NOT NULL,
date varchar(30) NOT NULL default '',
title varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Table structure for table `eventdetail`
#
CREATE TABLE eventdetail (
id smallint(2) unsigned NOT NULL auto_increment,
event smallint(2) NOT NULL default '0',
informations text NOT NULL,
titles varchar(100) NOT NULL default '',
file_name varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
Tracking #: 5CF2A36BDC27D14BA1C3A19CBAC7214ED510CB7E
What you've already given us is great but it would really help if you
described the problem you are encountering. It's not clear whether you are
getting error messages from MySQL or your result sets simply don't match
your expectations or if you are getting compile errors from php.
If you could state just what the problem is, and ideally show the result you
are getting (if any) versus the result you expected, it would be easier to
help you.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB TableSpace Question
Oracle can shrink tablespaces, you can find how to do it searching Metalink for this article: 1029252.6 How to Resize a Datafile Best regards, Antonio Delgado. -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: martes, 03 de agosto de 2004 22:00 To: [EMAIL PROTECTED] Subject: Re: InnoDB TableSpace Question Oracle cannot shrink datafiles (same idea as InnoDB datafiles) when data is deleted either. David Marc Slemko wrote: On Tue, 3 Aug 2004 12:42:03 -0400 , David Seltzer [EMAIL PROTECTED] wrote: Thanks Marc, Is there really no way to reclaim unused space in an InnoDB table space? If not, why is this not considered a tremendous limitation? Some do consider it a tremendous limitation. It all depends on how it is being used. Oh, and one thing I forgot... in newer 4.1 versions, if you set things up so each table has its own file with innodb_file_per_table, then I think if you do an optimize table it will end up shrinking the file for that table since it will recreate it. However that really is just a workaround, and there are a lot of disadvantages to that method ... especially the fact that free space is now per table instead of per tablespace. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. No confidentiality or privilege is waived or lost by any wrong transmission. If you have received this message in error, please immediately destroy it and kindly notify the sender by reply email. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Opinions, conclusions and other information in this message that do not relate to the official business of Ydilo Advanced Voice Solutions, S.A. shall be understood as neither given nor endorsed by it. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Right join after inner join has wrong result
Description: See how-to-repeat. How-To-Repeat: CREATE TABLE A (A int); CREATE TABLE B (B int); CREATE TABLE C (A int, B int); INSERT INTO A VALUES (1),(2); INSERT INTO B VALUES (1),(2); INSERT INTO C VALUES (1,1); SELECT C.B FROM A INNER JOIN C ON C.A = C.A RIGHT JOIN B ON B.B = C.B WHERE C.B IS NULL Expected Result: A3 -- 2 Actual Result: A3 -- 1 2 2 Tried in postgresql and it works like the expected result. Fix: none Submitter-Id: Originator:Alan Tam Organization: MySQL support: none Synopsis: Right join after inner join has wrong result Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.20 (Source distribution) Server: /usr/bin/mysqladmin Ver 8.40 Distrib 4.0.20, for pc-linux-gnu on i386 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 4.0.20-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 1 day 1 hour 3 min 32 sec Threads: 22 Questions: 237517 Slow queries: 0 Opens: 1720 Flush tables: 1 Open tables: 64 Queries per second avg: 2.633 C compiler:i386-linux-gcc (GCC) 3.3.4 (Debian 1:3.3.4-4) C++ compiler: i386-linux-g++ (GCC) 3.3.4 (Debian 1:3.3.4-4) Environment: System: Linux delta 2.6.7-1-686 #1 Thu Jul 8 05:36:53 EDT 2004 i686 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-linux/3.3.4/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,pascal,objc,ada,treelang --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.3 --enable-shared --with-system-zlib --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu --enable-debug --enable-java-gc=boehm --enable-java-awt=xlib --enable-objc-gc i486-linux Thread model: posix gcc version 3.3.4 (Debian 1:3.3.4-3) Compilation info: CC='i386-linux-gcc' CFLAGS='' CXX='i386-linux-g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 2004-06-20 00:46 /lib/libc.so.6 - libc-2.3.2.so -rw-r--r-- 1 root root 1243856 2004-05-26 02:40 /lib/libc-2.3.2.so -rw-r--r-- 1 root root 2640410 2004-05-26 02:40 /usr/lib/libc.a -rw-r--r-- 1 root root 204 2004-05-26 02:16 /usr/lib/libc.so Configure command: ./configure '--build=i386-linux' '--host=i386-linux' '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-raid' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server' '--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline' '--with-extra-charsets=all' '--with-berkeley-db' '--with-innodb' 'build_alias=i386-linux' 'host_alias=i386-linux' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB TableSpace Question
In article [EMAIL PROTECTED], Jeff Mathis [EMAIL PROTECTED] writes: my understanding is that the datafiles are created when the server initializes, and this this is the designed and expected behavior. Most other database products use a similar model. Your scenario cannot happen. You specify how many innodb data files and how large in your config file. when the server starts, it allocates all the space you requested. if the server cannot find the space at startup, you get an error. if during an import the file size is exceeded, you get an error and the import stops. ... except if you use the autoextend clause on your innodb_data_file_path. In this case InnoDB files can grow automatically, but they can't shrink. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert problems with InnoDB (big table)
Luc, do you use the mysql client for the insert operations? And is autocommit set to yes? Then the answer is: turn off autocommit mode and commit every high number but not too high to grow InnoDB's transaction handling resources too big rows. Commit every 100,000 rows for example. The speeds up the whole thing a lot because there is no need for a disk flush after every record insert. commit means the data are on disk for sure now (or after a few seconds (if you set innodb_flush_log_at_trx_commit to 0 or 2 instead of 1)). Regards, Frank. Luc Charland wrote: We are evaluating the replacement of a Sybase database with MySQL. The databases are 60+GB, containing more than 100 tables. Since we need transactions, that implies InnoDB. We were happy with the early results, but we hit a major roadblock when trying to import the biggest table (20+GB, with 4 indexes). We have reproduced the problem with a simpler table on many different servers and MySQL versions (4.X). At first, we easily insert 1600+ lines per second. As the number of lines grows, the performance deteriorate (which I can understand), but it eventually gets so slow that the import would take weeks. Doing a vmstat on the server shows that after a certain limit is reached (index bigger than the total mem ?), mysqld starts reading as much as writing, and the CPU usage goes down as the I/O eventually reach the maximum for the server. If you wait long enough, you get less than 50 lines per second (which is 30+ times slower than the first few million inserts). We have done the same tests on Sybase and another database on the same machines and have not seen this behavior, so it is not hardware related. We have done the same import in a MyISAM table and have not see any slowdown (the whole data was imported very fast, even if we had to wait a very long time --5+ hours-- for the index to rebuild after). We have tried to transform the MyISAM table into a InnoDB (same problem occurs). We have tried to import from the MyISAM table into an empty InnoDB, same problem occurs. SETUP: We have of course changed the following innodb_buffer_pool_size= (50% to 80% of total ram) innodb_log_file_size=(20% to 40% of total ram) we have tried different innodb_flush_method we have tried innodb_flush_log_at_trx_commit (0, 1) we have tried ibdata1:1G:autoextend, and also make it big enough so that all the data will fit without autoextending. we have tried creating the indexes after instead of before the inserts, but like the documentation says, it is not better. Is there an upper limit to the size of the indexes of a single table in InnoDB? Anybody else has seen this kind of slowdown for big InnoDB tables? Here is a small table that reproduce the problem (if you make 5 to 15 million inserts). We wrote a few programs (one in C++, one in Python) that generates random data and insert into the database. __ create table smallest ( id int primary key, name varchar(80), model char(20) , description varchar(255), lastupdate date, price decimal(8,2), cost decimal(8,2)) type=innodb create unique index smallcomplex on smalltest (model, id, name) create index smallprice on smalltest (price) create index smallcost on smalltest (cost) create index smallname on smalltest (name) __ Thanks for any help. Luc Charland -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Question
Karl-Heinz Schulz wrote: Thank you for trying to help me. The output is wrong I get either Event 1 Event 2 Details 1 for event 1 Details 2 for event 1 Details 3 for event 1 that query is wrong : $eventdetail_query = mysql_query(select informations, titles, file_name from eventdetail, event where eventdetail.event =.$event[0]); try : select informations, titles, file_name from eventdetail, event where event.id=.$event[0] AND event.id=eventdetails.event -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Syntax Question
Philippe,
I changed my to the following but the result is now (I deleted the print
stuff for better reading)
?
$event_query = mysql_query(select id, inserted, information, eventname,
date, title from event order by inserted desc LIMIT 0 , 30);
while($event = mysql_fetch_row($event_query)){
$eventdetail_query = mysql_query(select titles, informations, file_name
from eventdetail, event where event.id=eventdetail.event AND
event.id=.$event[0]);
while($eventdetail = mysql_fetch_row($eventdetail_query)){
}
}
?
Event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
But I would need
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 2
Details 2 for event 2
Details 3 for event 2
Is this even possible?
TIA
-Original Message-
From: Philippe Poelvoorde [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 04, 2004 5:52 AM
To: Karl-Heinz Schulz
Cc: [EMAIL PROTECTED]
Subject: Re: SQL Syntax Question
Karl-Heinz Schulz wrote:
Thank you for trying to help me.
The output is wrong
I get either
Event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
that query is wrong :
$eventdetail_query = mysql_query(select informations, titles, file_name
from eventdetail, event where eventdetail.event =.$event[0]);
try :
select informations, titles, file_name
from eventdetail, event where event.id=.$event[0] AND
event.id=eventdetails.event
Tracking #: 3842A5D2EB81014B918FDB71F1DE0830A35E8D56
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE latin1
hi all i'm having the following issue, that i'm not sure how to resolve i have a comma seprated file, which imports fine with; LOAD DATA LOCAL INFILE 'path/top/file.txt' REPLACE INTO TABLE `tablename` FIELDS TERMINATED BY ',' ENCLOSED BY '\' LINES TERMINATED BY '\r'; but lines with latin1 characters do not import correctly eg. 5,België '5', 'Belgi' or 53,Bartók,Béla,,1881,1945,Bartók, Béla '53','Bartók','Béla','','1881','1945','','','','','','','','','Bartk, Bla' i'm using a installation on os x, 4.0.18 Can someone please give some advise on how to proceed? (ps, i can use these characters with clients like (phpmyadmin or CocaoMySQL) thanks bas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table Corruption
Hello DB-users,admins, I have serious problem with a table in a DB that I have. This is on a mysql-4.0.20. mysql CHECK TABLE Users; +--+---+--+--+ | Table| Op| Msg_type | Msg_text | +--+---+--+--+ | atmail.Users | check | warning | Table is marked as crashed | | atmail.Users | check | warning | Size of indexfile is: 23552 Should be: 1024 | | atmail.Users | check | warning | Size of datafile is: 81368 Should be: 0 | | atmail.Users | check | error| Found wrong record at 0 | | atmail.Users | check | error| Corrupt | +--+---+--+--+ 5 rows in set (0.39 sec) Initially ... beastie# ls -al Users.* -rwxr-x--- 1 mysql mysql 81368 Aug 4 11:33 Users.MYD -rwxr-x--- 1 mysql mysql 23552 Aug 4 14:29 Users.MYI -rwxr-x--- 1 mysql mysql 9404 Aug 4 11:33 Users.frm I have read the manuals about myisamchk (-e, -r, -o) and REPAIR TABLE table_name USE_FRM but in both cases when I perform the repair, the Users.MYI is then reset to a value of 0. After I run repair mysql REPAIR TABLE Users USE_FRM; +--++--++ | Table| Op | Msg_type | Msg_text | +--++--++ | atmail.Users | repair | info | Key 1 - Found wrong stored record at 0 | | atmail.Users | repair | status | OK | +--++--++ 2 rows in set (0.05 sec) beastie# ls -al Users.* -rwxr-x--- 1 mysql mysql 0 Aug 4 14:36 Users.MYD -rwxr-x--- 1 mysql mysql 1024 Aug 4 14:36 Users.MYI -rwxr-x--- 1 mysql mysql 9404 Aug 4 11:33 Users.frm Now for sure there is no data at all in the table. mysql select * from Users; Empty set (0.01 sec) Is there hope for me in this situation??? I have googled and googled but all that I see doesn't seem to help me to recover the data in the table. This makes me very desparate. I'd be very thankful for any pointers that would help me out of this successfully. I have backup copies of those files. -Wash http://www.netmeister.org/news/learn2quote.html -- +==+ |\ _,,,---,,_ | Odhiambo Washington[EMAIL PROTECTED] Zzz /,`.-'`'-. ;-;;,_ | Wananchi Online Ltd. www.wananchi.com |,4- ) )-,_. ,\ ( `'-'| Tel: +254 20 313985-9 +254 20 313922 '---''(_/--' `-'\_) | GSM: +254 722 743223 +254 733 744121 +==+ The fact that boys are allowed to exist at all is evidence of a remarkable Christian forbearance among men. -- Ambrose Bierce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
storing 16 Bytes
Hi I need to store a globabl unique identifer value in mysql4.x database table. table field is varchar(16) as the string length is 16 byte or 128 bitsmy string data is unsiged char type.. how can i send that data to mysql table ? will it require binary storage for this unsigned data ? I'm asking this because ...i have 16 bytes unsgined char data in the variablenow i need to pass that to query...but all it seems to pass it like casting with (char *)which i dont want... any help or clue in unsigned char 16 bytes array string into myslq database ? Regards Sheraz ___ Do you Yahoo!? Express yourself with Y! Messenger! Free. Download now. http://messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General questions
I was wondering if anyone could answer a few questions for me. I have to do some research for a school project. 1. What is the user or connection limit for both versions of MySQL (Database Server and MaxDB)? 2. How much memory does MySQL take when started up? 3. Does MySQL take advantage of dual CPU systems? 4. Define referential integrity. I would appreciate it if someone could help. Thanks. - SIUE Web Mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General questions
I was wondering if anyone could answer a few questions for me. I have to do some research for a school project. 1. What is the user or connection limit for both versions of MySQL (Database Server and MaxDB)? 2. How much memory does MySQL take when started up? 3. Does MySQL take advantage of dual CPU systems? 4. Define referential integrity. I would appreciate it if someone could help. Thanks. With the *possible* exception of 4, all of this is in the manual( http://dev.mysql.com/doc/). If someone gives you these answers, great, but as someone much wiser once said: 'Give a man a fish, he eats for a day, teach a man to fish, he eats for a lifetime' and for 4 i put your qestion in google and got over 70,000 hits inclusive of one, on the first page,entittled Referential Integrity Definition HTH Jeff - SIUE Web Mail -- 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]
RE: General questions
1. What is the user or connection limit for both versions of MySQL (Database Server and MaxDB)? Search the web site 2. How much memory does MySQL take when started up? Search the web site 3. Does MySQL take advantage of dual CPU systems? Search the web site 4. Define referential integrity. Do a google search -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
cannot add mysql user on debian sarge
On debian sarge, I'm trying to add a user for MySQL but every time I try, I get the following error /usr/bin/perl: relocation error: /usr/perl5/locale/gettext/gettext.so: unrecognized symbol: Perl_gthr_key_ptr What do I need to do?
Re: Table Corruption
Odhiambo Washington [EMAIL PROTECTED] wrote: +--++--++ | atmail.Users | repair | info | Key 1 - Found wrong stored record at 0 | | atmail.Users | repair | status | OK | +--++--++ 2 rows in set (0.05 sec) It seems that MYD contains incorrect data. This might be just a binary junk. Not sure if that is even possible to fix. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb per table
Carlos Proal [EMAIL PROTECTED] wrote: How stable is this feature? , i have been using it for a few time and works fine, but im thinking to move my current productive enviroment this way and i dont know if its a good choice. Any advise is appreciated. It is stable. :) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_close() problem
Venkateswaran, Bhaskar [EMAIL PROTECTED] wrote: I just upgraded to mysql-4.0.20 compiled using gcc-3.3.3. All my C++ DB interfaces connecting to this mysql database seem to run into a problem - when I try to disconnect, mysql_close() is called but the program just hangs there without being able to close the connection. Can someone please advise? What OS do you run? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Right join after inner join has wrong result
Alan wrote: Description: See how-to-repeat. How-To-Repeat: CREATE TABLE A (A int); CREATE TABLE B (B int); CREATE TABLE C (A int, B int); INSERT INTO A VALUES (1),(2); INSERT INTO B VALUES (1),(2); INSERT INTO C VALUES (1,1); SELECT C.B FROM A INNER JOIN C ON C.A = C.A Perhaps you meant: INNER JOIN C on C.A = A.A RIGHT JOIN B ON B.B = C.B WHERE C.B IS NULL Expected Result: A3 -- 2 Actual Result: A3 -- 1 2 2 Tried in postgresql and it works like the expected result. Fix: none Submitter-Id: Originator: Alan Tam Organization: MySQL support: none Synopsis: Right join after inner join has wrong result Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.20 (Source distribution) Server: /usr/bin/mysqladmin Ver 8.40 Distrib 4.0.20, for pc-linux-gnu on i386 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 4.0.20-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 1 day 1 hour 3 min 32 sec Threads: 22 Questions: 237517 Slow queries: 0 Opens: 1720 Flush tables: 1 Open tables: 64 Queries per second avg: 2.633 C compiler:i386-linux-gcc (GCC) 3.3.4 (Debian 1:3.3.4-4) C++ compiler: i386-linux-g++ (GCC) 3.3.4 (Debian 1:3.3.4-4) Environment: System: Linux delta 2.6.7-1-686 #1 Thu Jul 8 05:36:53 EDT 2004 i686 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-linux/3.3.4/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,pascal,objc,ada,treelang --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.3 --enable-shared --with-system-zlib --enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu --enable-debug --enable-java-gc=boehm --enable-java-awt=xlib --enable-objc-gc i486-linux Thread model: posix gcc version 3.3.4 (Debian 1:3.3.4-3) Compilation info: CC='i386-linux-gcc' CFLAGS='' CXX='i386-linux-g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 2004-06-20 00:46 /lib/libc.so.6 - libc-2.3.2.so -rw-r--r-- 1 root root 1243856 2004-05-26 02:40 /lib/libc-2.3.2.so -rw-r--r-- 1 root root 2640410 2004-05-26 02:40 /usr/lib/libc.a -rw-r--r-- 1 root root 204 2004-05-26 02:16 /usr/lib/libc.so Configure command: ./configure '--build=i386-linux' '--host=i386-linux' '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-raid' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server' '--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline' '--with-extra-charsets=all' '--with-berkeley-db' '--with-innodb' 'build_alias=i386-linux' 'host_alias=i386-linux' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 4.0.2 preoblem
That's how dbs work. When rows are deleted, they reclaim that space when new rows are inserted. Hence, there is no last row (in fact, no order at all) unless you explicitly order the results in your select. That's what ORDER BY is for. Try SELECT autoinc_field, data_field FROM yourtable ORDER BY autoinc_field; By the way, if you are really using mysql 4.0.2, you should consider an upgrade. 4.0.2 is over 2 years old. The current version is 4.0.20, and there have been a lot of improvements and bug fixes since 4.0.2. Michael Cres Justado wrote: can anyone help me with my problem regarding mysql 4.0.2. I'm having a problem with the sequencing of the auto incremnt field. if my application makes an insert mysql inserts the new row somewhere in the middle of the sequence. example autoinc_field data_field 1 qwe 2 ert 3 ou 20 oiu 4 ngf 5 ljn . . . . 18okj 19thd in my example the row 20 was inserted in the middle of the sequence instead of inserting it in the last row. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General questions
I checked. SIUE seems to be an institute of higher learning. Well, here's your chance to do some self-directed,internet-based learning (probably the point of your project, isn't it. :-( ) RTFM my dear student! Then, if you don't understand what you read, use a search engine to find out what other people have said on the same topic. I would concentrate on FAQs, discussion groups, and tutorial articles. If after you have exhausted all other avenues, come back to the list with your *specific* questions and what about that particular subject is confusing you and we will be glad to help. Respecfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 08/04/2004 08:29:58 AM: I was wondering if anyone could answer a few questions for me. I have to do some research for a school project. 1. What is the user or connection limit for both versions of MySQL (Database Server and MaxDB)? 2. How much memory does MySQL take when started up? 3. Does MySQL take advantage of dual CPU systems? 4. Define referential integrity. I would appreciate it if someone could help. Thanks. - SIUE Web Mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Verify the database backup from mysqldump???
Scott Fletcher [EMAIL PROTECTED] wrote: Is it possible to verify the database backup. Like most machines, that make backup of files then verify that all of it is backed up without an error? I use mysqldump to make a backup but I have no idea about the verify Restore the dump into another database and compare the data. Sounds easy, but you'll need to write a script for your structures. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: cannot add mysql user on debian sarge
Are you attempting to add a user in the MySQL database or the mysql user on the system? -Original Message- From: Levi Campbell To: mysql Sent: 8/4/04 8:12 AM Subject: cannot add mysql user on debian sarge On debian sarge, I'm trying to add a user for MySQL but every time I try, I get the following error /usr/bin/perl: relocation error: /usr/perl5/locale/gettext/gettext.so: unrecognized symbol: Perl_gthr_key_ptr What do I need to do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication slave lags way behind master
Jon Drukman [EMAIL PROTECTED] wrote: i've got one master and one slave. the master is VERY busy, tons of inserts/updates/deletes all the time. (it's an extremely high traffic message board system.) we've got a situation right now where the slave starts lagging WAY behind the master. it's as if it simply can't run through the binary log fast enough to keep up. both machines are identical hardware-wise, and very powerful (dual 3ghz P4, 4G RAM, 15K RPM scsi disks in RAID0+1). the slave does not show undue load or anything. mysql is the only process (besides normal linux stuff) running on both machines. disk is fine, cpu is fine. i don't know where to look next. one of my developers says: The only thing I can think of is that on db2, we're running so many inserts/updates/deletes in parallel, while on db3 they have to run sequentially. One set of long-running updates can hose up the whole queue. Even if there's a series of 1-second updates that run on db2 against a table that nobody else is accessing, that would add up on the db3 side. Sounds like reasonable. This might be it! any ideas where to look for tuning/optimization? we've converted some Not at once. You'll definitely need some consulting here. Apply to MySQL payed support to fine-tune MySQL or to a MySQL support consulting partner company (like us ;)) to develop solution for your software. Both links are in signature. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 4.0.2 preoblem
If you want them ordered by autoinc_field then add ORDER BY autoinc_field. Otherwise expect them to be returned in any order. Cres Justado wrote: can anyone help me with my problem regarding mysql 4.0.2. I'm having a problem with the sequencing of the auto incremnt field. if my application makes an insert mysql inserts the new row somewhere in the middle of the sequence. example autoinc_field data_field 1 qwe 2 ert 3 ou 20 oiu 4 ngf 5 ljn . . . . 18okj 19thd in my example the row 20 was inserted in the middle of the sequence instead of inserting it in the last row. _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE performance degradation from 4.0.17 - 4.0.20
Sergei Golubchik [EMAIL PROTECTED] wrote: We're upgrading from 3.23.58 to 4.0.20 and found that that although the ALTER test results of sql-bench had been greatly improved, CREATE has shown nasty performance degradation. Just before needing to make the decision to revert back to 3.23.58, we found a post here where someone had a similar problem when using SAN storage. We see the problem using hardware RAID, shared storage or local SCSI disks. Yes. Since 4.0.17 MySQL sync()'s after it created an .frm file (in CREATE/ALTER TABLE). And note that the sync() call not only physically writes .frm file to disk, but also everything else which is in write cache. If the server is under load, sync() call may take seconds, tens of seconds or even hundreds of seconds. As one usually doesn't create tables at the huge rate, it is not a problem. Unfortunately, it is apparently a problem for sql-bench :( Time to add a NO_SYNC option to CREATE TABLE, Sergei ? :) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[ANN] DBACentral for MySQL 1.0 released
Hello All, DBACentral for MySQL has been released! === The long-awaited headliner of the DBACentral Product Family has been finally released! We are proud to present an absolutely new product which brings MySQL database management to the new level of fastness and simplicity. DBACentral expands the boundaries of MySQL functionality and makes your work with MySQL as easy as you couldn't expect. DBACentral for MySQL allows you to perform every-day tasks as well as create complicate solutions in a most direct way. Our product provides you with several levels of functionality, making it suitable both for MySQL novices and professionals. Download DBACentral for MySQL now and try it yourself: http://microolap.com/dba/mysql/dbacentral/dbacentralmysql.zip Important = The Early Bird coupon program is working till the end of August. You have a unique chance to buy DBACentral for MySQL with 30% off. Please use this coupon code - EarlyBird010904 - at one of the order pages: https://secure.shareit.com/shareit/checkout.html?PRODUCT[197833]=1languageid=1 or https://www.plimus.com/jsp/buynow.jsp?contractId=1637280quantity=1 = Product features: - Easy database management - Managing tables, columns, indexes, and keys - Easy migration from Microsoft Access to MySQL - Customizable Forms - Stored Queries - Logical Data Integrity - Users and Privileges - Table Grid Environment - Useful wizards for constructing databases, tables, forms, queries - Powerful import and export modules - Full InnoDB compliance - Support of all field types - Native MySQL services - And a whole lot more... Take a look at the product page for details: http://microolap.com/dba/mysql/dbacentral/ --- Best regards, MicroOLAP Technologies LTD www.microolap.com mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Default username and pw for MySQL Connector/J
Hi everyone, Is there a standard way to specify a default user name and password for the java driver? For example, will it recognize .my.cnf like most mysql clinets do? Thanks, Sergei Sergei, No there is not. It must be passed in on the command line. Of course your application(s) could always read a 'standard' configuration properties file and add that. Connector/J can't read my.cnf because in many (most) cases, security restrictions prevent JDBC drivers from reading or creating files. -Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Question
- Original Message -
From: Karl-Heinz Schulz [EMAIL PROTECTED]
To: 'Philippe Poelvoorde' [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, August 04, 2004 6:41 AM
Subject: RE: SQL Syntax Question
Philippe,
I changed my to the following but the result is now (I deleted the print
stuff for better reading)
?
$event_query = mysql_query(select id, inserted, information, eventname,
date, title from event order by inserted desc LIMIT 0 , 30);
while($event = mysql_fetch_row($event_query)){
$eventdetail_query = mysql_query(select titles, informations, file_name
from eventdetail, event where event.id=eventdetail.event AND
event.id=.$event[0]);
while($eventdetail = mysql_fetch_row($eventdetail_query)){
}
}
?
Karl-Heinz,
I used the following SQL in a script and got the answer that I think you
want:
select informations, titles, file_name
from eventdetail d inner join event e on e.veranastaltung = d.event
where d.event = 1
This gave me just the eventdetails for event 1.
This is not in php format of course. I don't know php but it looks similar
to other languages I know so I'm guessing that you would write it as follows
in php:
$eventdetail_query = mysql_query(select titles, informations, file_name
from eventdetail d inner join event e on e.veranstaltung = d.event
where event.id=.$event[0]);
Explanation:
Since you named two tables in the 'from' clause of the eventdetail query,
you are clearly attempting to join the tables. I'm assuming you want an
inner join. In other words, you only want to show details if there is a
corresponding event row that matches your detail row. To get a proper join,
you need to identify what the two tables have in common. If I understand
your data correctly, the veranstaltung column in the Event table is going to
have the same value as the event column in the Eventdetail table when the
rows are describing the same event. Therefore, that is what I put in the
'on' clause of the query. The 'where' clause is the one I'm least sure how
to write in php but, based on what you had in your queries, I assume that
this is the way to tell the query to return only rows where the event column
in the join result has the same value as the event value in the event row
currently being processed in the outer loop.
In short, you were doing a join implicitly but hadn't properly specified the
joining condition so you weren't getting the rows you really wanted.
By the way, I really wasn't completely clear on the meaning of the data in
the tables so I made some guesses about the contents of each column. This is
the script I wrote to create and populate the tables. Your original event
query, which is unchanged, appears after that and my best guess for the
eventdetail query is at the end.
-
use tmp;
#Event table contains one row for each event.
select 'Drop/create Event table';
drop table if exists event;
create table if not exists event
(id smallint(2) unsigned not null auto_increment,
veranstaltung smallint(2) not null default '0',
inserted date not null default '-00-00',
information text not null,
eventname text not null,
date varchar(30) not null default '',
title varchar(100) not null default '',
primary key(id)
) TYPE=MyISAM;
select 'Populate Event table';
insert into event (veranstaltung, inserted, information, eventname, date,
title) values
(1, '2004-04-20', 'information-01', 'Canada Day', '2004-07-01', 'title-01'),
(2, '2004-05-03', 'information-02', 'Labour Day', '2004-09-04', 'title-02'),
(3, '2004-08-15', 'information-03', 'Christmas Day', '2004-12-25',
'title-03');
select 'Display Event table';
select * from event;
#Event_Detail table contains one row for each aspect of an event.
select 'Drop/create Eventdetail table';
drop table if exists eventdetail;
create table if not exists eventdetail
(id smallint(2) unsigned not null auto_increment,
event smallint(2) not null default '0',
informations text not null,
titles varchar(100) not null default '',
file_name varchar(100) not null default '',
primary key(id)
) TYPE=MyISAM;
select 'Populate Eventdetail table';
insert into eventdetail (event, informations, titles, file_name) values
(1, 'information-01a', 'title-01a', 'file-01a'),
(1, 'information-01b', 'title-01b', 'file-01b'),
(1, 'information-01c', 'title-01c', 'file-01c'),
(2, 'information-02a', 'title-02a', 'file-02a'),
(2, 'information-02b', 'title-02b', 'file-02b'),
(2, 'information-02c', 'title-02c', 'file-02c'),
(3, 'information-03a', 'title-03a', 'file-03a'),
(3, 'information-03b', 'title-03b', 'file-03b'),
(3, 'information-03c', 'title-03c', 'file-03c');
select 'Display Eventdetail table';
select * from eventdetail;
select 'Event query';
select id, inserted, information, eventname, date, title
from event
order by inserted desc limit 0, 30;
select 'Eventdetail query';
select informations, titles, file_name
Re: Table Corruption
* Egor Egorov [EMAIL PROTECTED] [20040804 16:24]: wrote: Odhiambo Washington [EMAIL PROTECTED] wrote: +--++--++ | atmail.Users | repair | info | Key 1 - Found wrong stored record at 0 | | atmail.Users | repair | status | OK | +--++--++ 2 rows in set (0.05 sec) It seems that MYD contains incorrect data. This might be just a binary junk. Not sure if that is even possible to fix. When I do strings Users.MYD, I can actually see the data!! So there is data in the file. -Wash http://www.netmeister.org/news/learn2quote.html -- +==+ |\ _,,,---,,_ | Odhiambo Washington[EMAIL PROTECTED] Zzz /,`.-'`'-. ;-;;,_ | Wananchi Online Ltd. www.wananchi.com |,4- ) )-,_. ,\ ( `'-'| Tel: +254 20 313985-9 +254 20 313922 '---''(_/--' `-'\_) | GSM: +254 722 743223 +254 733 744121 +==+ Portable, adj.: Survives system reboot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I can not figure out this J Connector problem - a bug???
Yes. Exactly! Thanks so much for pointing this out!
Haitao
On Wed, 4 Aug 2004 08:53:28 -0500 (CDT), Mark Matthews [EMAIL PROTECTED] wrote:
Hi,
I have following code to insert a row into a table - schema follows:
:
PreparedStatement insertData = targetConnection.prepareStatement(insert
into CompanyParticipationLevel
(siteId,nodeId,companyId,editionId,participation
LevelId,participationText,participationLogo) values (?, ?, ?, ?, ?, ?,
?));
Statement sourceData = sourceConnection.createStatement();
ResultSet sourceResultSet = sourceData.executeQuery(select
cpl.siteId,
isnull(cpl.nodeId,0) as nodeId, cpl.companyId, cpl.editionId,
cpl.participationL
evelId, cpl.participationText, cpl.participationLogo FROM
CompanyParticipationLe
vel cpl where cpl.siteId=8 and cpl.participationLevelTypeId=1);
Object value = null;
while (sourceResultSet.next()) {
insertData.clearParameters();
System.out.print(processing ();
for(int i = 1; i = 7; i++){
value = sourceResultSet.getObject(i);
insertData.setObject(i, value);
System.out.print((+value+));
}
System.out.println());
insertData.executeUpdate();
}
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default |
Extra |
+--+-+--+-+-+---+
| siteId | int(10) unsigned| | PRI | 0 |
|
| nodeId | int(10) unsigned| | PRI | 0 |
|
| companyId| int(10) unsigned| | PRI | 0 |
|
| editionId| int(10) unsigned| | PRI | 0 |
|
| participationLevelId | tinyint(3) unsigned | | PRI | 0 |
|
| participationText| text| YES | | NULL|
|
| participationLogo| varchar(128)| YES | | NULL|
|
+--+-+--+-+-+---+
The output of the program:
processing ((8)(0)(56361)(0)(4)(null)( ))
Exception in thread main java.sql.SQLException: Column 'siteId' cannot
be null
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2551)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1443)
at
com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedSt
atement.java:1239)
at
com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPrepared
Statement.java:903)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1871)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1796)
at com.mysql.jdbc.PreparedStatement.executeUpdate
(PreparedStatement.java:1658)
It is obvious that I did setObject correctly, there are 7 columns and
I have 7 value supplied. But JDBC complains that first column is null.
Is it a bug or someone can tell me what is going on?
Thanks
Haitao
What version of Connector/J are you using? This looks like a bug that was
fixed in 3.1.3.
-Mark
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Default username and pw for MySQL Connector/J
The JDBC driver will not recognize the my.cnf file. -Original Message- From: Sergei Skarupo To: Mysql List (E-mail) Sent: 8/3/04 10:51 PM Subject: Default username and pw for MySQL Connector/J Hi everyone, Is there a standard way to specify a default user name and password for the java driver? For example, will it recognize .my.cnf like most mysql clinets do? Thanks, Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UTF8 collations in 4.1.3
On Tue, Aug 03, 2004 at 01:11:44PM -0400, Jeremy March wrote:
Is this for Swedish language data? I don't know Swedish so I don't
actually know where u-diaeresis is sorted in Swedish myself, but
according to the source code (in the file: strings/ctype-uca.c) the
u-diaeresis is sorted as an equivalent of y in utf8_swedish_ci.
I don't know Swedish either but section 11.3.13 of the manual
( http://dev.mysql.com/doc/mysql/en/Charset-collation-effect.html ) says
that it is sorted with y, as you said.
The unicode codepoint for u-diaeresis is 0x00FC and the capital
U-diaeresis is 0x00DC.
I just tested this with 4.1.4 (from the bk tree) and it worked correctly
for me. My keyboard isn't setup to enter u-diaeresis easily so I
entered it in hex. Try this:
Entering it in hex works for me too. So the problem _was_ actually with
the values I inserted into the database.
What's the best way to actually see what is stored in the database,
preferably as hex or something else that a terminal is guaranteed to
display correctly? Clearly, what I was doing earlier was not correct.
Thanks,
Jody
CREATE TABLE swedish (col char(20) COLLATE utf8_swedish_ci);
INSERT INTO swedish VALUES (CONVERT(_ucs2 0x004D00FC006C006C00650072
USING utf8)), ('MySQL'), ('Muffler'), ('MX Systems');
SELECT * FROM swedish ORDER BY col;
++
| col|
++
| Muffler|
| MX Systems |
| M??ller|
| MySQL |
++
4 rows in set (0.00 sec)
--
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]
Design Question
Hi all, I need some advice on a project I have. Basically, I have some tables: CREATE TABLE listings ( ListingID bigint(20) unsigned NOT NULL auto_increment, CatalogNumber varchar(12) NOT NULL default '', PDFLink varchar(100) default NULL, PDFName varchar(80) default NULL, Title varchar(100) NOT NULL default '', ComposerID int(11) default NULL, ArrangerID int(11) default NULL, PublisherID int(11) default NULL, Price double(16,2) NOT NULL default '0.00', DiscountID int(11) default NULL, Description text, NewTitles tinyint(1) default NULL, CategoryID int(11) NOT NULL default '0', PRIMARY KEY (ListingID) ) TYPE=MyISAM; CREATE TABLE categories ( CategoryID int(11) NOT NULL auto_increment, Name varchar(50) NOT NULL default '', Alias varchar(60) default NULL, DiscountID int(11) default NULL, Description text, GroupID int(11) NOT NULL default '0', PRIMARY KEY (CategoryID) ) TYPE=MyISAM; CREATE TABLE groups ( GroupID int(11) NOT NULL auto_increment, Name varchar(50) default NULL, DiscountID int(11) default NULL, PRIMARY KEY (GroupID) ) TYPE=MyISAM; Currently, there is a one-to-one relationship between listings and categories, and listings and groups. Now, the customer is requesting that a listing be included in several categories. I am not quite sure how to do this. My thought was to add a new field to the listings table that would contain a comma-separated list of CategoryIDs, but something doesn't feel right about this solution. What would be a good approach to this problem? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CREATE performance degradation from 4.0.17 - 4.0.20
-Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 04, 2004 8:33 AM To: [EMAIL PROTECTED] Subject: Re: CREATE performance degradation from 4.0.17 - 4.0.20 Sergei Golubchik [EMAIL PROTECTED] wrote: As one usually doesn't create tables at the huge rate, it is not a problem. Unfortunately, it is apparently a problem for sql-bench :( Time to add a NO_SYNC option to CREATE TABLE, Sergei ? :) Setting the sync_frm option to 0 (that was added in 4.0.18) corrected the issue we saw with the test as a temporary solution. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Right join after inner join has wrong result
Hi, On 2004-08-04 21:20, gerald_clark wrote: [...] SELECT C.B FROM A INNER JOIN C ON C.A = C.A Perhaps you meant: INNER JOIN C on C.A = A.A RIGHT JOIN B ON B.B = C.B WHERE C.B IS NULL [...] Yes, thanks. I didn't realize that mysqlbug sends the report to a mailing list. I have proceeded to submit it as bug 4893 anyway. The syntax there should be correct. -- Regards, Alan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiled c++ and mysql codes run in python cgi script
Hi, All: We have a network management system written in C++, MysQL, and Hp SNMP. It works in Solaris command line. When I wrote a similar python codes which call compiled C++ and mysql codes in solaris command line, the comipled codes work fine in wraped python file. When I change the python codes to web interface in python CGI script, I got message that Can't connect to local MySQL server through socket '/tmp/mysql.sock' How can I fix this problem because we want to migrate solaris command line system to web access system ? The easiest way for me is directly to call compiled C++ and mysql codes (excutable) in python CGI scripts, then post the run results in website. Thank you very much in advance and I am looking forward to your reply. Yong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing 16 Bytes
Sheraz [EMAIL PROTECTED] wrote on 04/08/2004 13:14:26: Hi I need to store a globabl unique identifer value in mysql4.x database table. table field is varchar(16) as the string length is 16 byte or 128 bitsmy string data is unsiged char type.. how can i send that data to mysql table ? will it require binary storage for this unsigned data ? I'm asking this because ...i have 16 bytes unsgined char data in the variablenow i need to pass that to query...but all it seems to pass it like casting with (char *)which i dont want... any help or clue in unsigned char 16 bytes array string into myslq database ? You need to specify which language you are using. I could tell you the answer in Java, but I suspect that you are using C/C++ because of the way you are using char to store binary data. I would suggest that you ought to be using a column type of BLOB(16), and investigating you set a BLOB in your preferred language. In Java I would use a PreparedStatement and setBlob(). Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design Question
EB My thought was to add a new field to the listings table that would EB contain a comma-separated list of CategoryIDs, but something doesn't EB feel right about this solution. This would break the first normalization form and is extremely bad Okay - I thought something was off... First of all ask your customer - what is the relation between listings and categories - is it one-to-many or many-to-one or many-to-many relation The current relationship is one to one - each listing can only have one category. The customer is requesting a change to this, so that each listing can have many categories. if it is one-to-many (many-to-one) then you should add a field to details table that constitutes a primary key in the main table and define a foreign key. That means having either CategoryID in listings table or ListingID in categories table. My current table definition for the listings already has the foreign key of CategoryID. What you are saying is that the categories table should have a field for ListingID? Thanks! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design Question
You are right, a comma separated list won't work since you won't be able to do joins on it. To create a one to many relation, you actually need to create another table to hold the relation. CREATE TABLE listCatLink ( ListingID bigint(20) unsigned NOT NULL, CategoryID int(11) NOT NULL ) On Aug 4, 2004, at 10:35 AM, Erich Beyrent wrote: Hi all, I need some advice on a project I have. Basically, I have some tables: CREATE TABLE listings ( ListingID bigint(20) unsigned NOT NULL auto_increment, CatalogNumber varchar(12) NOT NULL default '', PDFLink varchar(100) default NULL, PDFName varchar(80) default NULL, Title varchar(100) NOT NULL default '', ComposerID int(11) default NULL, ArrangerID int(11) default NULL, PublisherID int(11) default NULL, Price double(16,2) NOT NULL default '0.00', DiscountID int(11) default NULL, Description text, NewTitles tinyint(1) default NULL, CategoryID int(11) NOT NULL default '0', PRIMARY KEY (ListingID) ) TYPE=MyISAM; CREATE TABLE categories ( CategoryID int(11) NOT NULL auto_increment, Name varchar(50) NOT NULL default '', Alias varchar(60) default NULL, DiscountID int(11) default NULL, Description text, GroupID int(11) NOT NULL default '0', PRIMARY KEY (CategoryID) ) TYPE=MyISAM; CREATE TABLE groups ( GroupID int(11) NOT NULL auto_increment, Name varchar(50) default NULL, DiscountID int(11) default NULL, PRIMARY KEY (GroupID) ) TYPE=MyISAM; Currently, there is a one-to-one relationship between listings and categories, and listings and groups. Now, the customer is requesting that a listing be included in several categories. I am not quite sure how to do this. My thought was to add a new field to the listings table that would contain a comma-separated list of CategoryIDs, but something doesn't feel right about this solution. What would be a good approach to this problem? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE performance degradation from 4.0.17 - 4.0.20
Hi! On Aug 04, Egor Egorov wrote: Sergei Golubchik [EMAIL PROTECTED] wrote: We're upgrading from 3.23.58 to 4.0.20 and found that that although the ALTER test results of sql-bench had been greatly improved, CREATE has shown nasty performance degradation. Just before needing to make the decision to revert back to 3.23.58, we found a post here where someone had a similar problem when using SAN storage. We see the problem using hardware RAID, shared storage or local SCSI disks. Yes. Since 4.0.17 MySQL sync()'s after it created an .frm file (in CREATE/ALTER TABLE). And note that the sync() call not only physically writes .frm file to disk, but also everything else which is in write cache. If the server is under load, sync() call may take seconds, tens of seconds or even hundreds of seconds. As one usually doesn't create tables at the huge rate, it is not a problem. Unfortunately, it is apparently a problem for sql-bench :( Time to add a NO_SYNC option to CREATE TABLE, Sergei ? :) There is --skip-sync-frm option. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design Question
As posted, your data structure supports two one-to-many relationships, not the one-to-one relationships as you described. You can have multiple Listings per Category and multiple Categories per Group. What it sounds like you have been asked to do is to support a many-to-many relationship. You need to support both multiple Listings per Category and multiple Categories per Listing. As you have it now: Groups (1..*) Categories (1..*) Listings As you need it to be: Groups (1..*) Categories (*..*) Listings To create a (*..*) relationship between two tables, you need a third table. Each entry in this table represents one Listing-Category association (relationship). CREATE TABLE listings_projects ( ListingID bigint not null , CategoryID int , ... any additional fields as needed ... , PRIMARY KEY (ListingID, CategoryID) ) The primary key ensures that at each Listing/Category combination appears only once (no duplicate assignments). I showed you where additional fields can fit into the relation table because sometimes there are facts about relationships that do not fit into either of the tables they relate A recent example in this list was a relation table between chemical compounds and the various plants in which those compounds could be found. A fact that belongs to the *relationship* could be the concentration of that chemical in that plant. That concentration value would not belong to the plants table nor would it belong to the compounds table but does belong to the relationship of plant to compound. Make sense? I have personally used additional fields like those to indicate deletion. That way old values are no longer available for new relationships (in my applications) and my queries won't break as I didn't actually get rid of any information. My historical reports still function as the old names are still in the system, even if you can't use the the old names for any current purposes. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Erich Beyrent [EMAIL PROTECTED] wrote on 08/04/2004 10:35:33 AM: Hi all, I need some advice on a project I have. Basically, I have some tables: CREATE TABLE listings ( ListingID bigint(20) unsigned NOT NULL auto_increment, CatalogNumber varchar(12) NOT NULL default '', PDFLink varchar(100) default NULL, PDFName varchar(80) default NULL, Title varchar(100) NOT NULL default '', ComposerID int(11) default NULL, ArrangerID int(11) default NULL, PublisherID int(11) default NULL, Price double(16,2) NOT NULL default '0.00', DiscountID int(11) default NULL, Description text, NewTitles tinyint(1) default NULL, CategoryID int(11) NOT NULL default '0', PRIMARY KEY (ListingID) ) TYPE=MyISAM; CREATE TABLE categories ( CategoryID int(11) NOT NULL auto_increment, Name varchar(50) NOT NULL default '', Alias varchar(60) default NULL, DiscountID int(11) default NULL, Description text, GroupID int(11) NOT NULL default '0', PRIMARY KEY (CategoryID) ) TYPE=MyISAM; CREATE TABLE groups ( GroupID int(11) NOT NULL auto_increment, Name varchar(50) default NULL, DiscountID int(11) default NULL, PRIMARY KEY (GroupID) ) TYPE=MyISAM; Currently, there is a one-to-one relationship between listings and categories, and listings and groups. Now, the customer is requesting that a listing be included in several categories. I am not quite sure how to do this. My thought was to add a new field to the listings table that would contain a comma-separated list of CategoryIDs, but something doesn't feel right about this solution. What would be a good approach to this problem? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
time zone leap seconds
we just upgraded from 4.0.4 to 4.1.3, and are getting this warning. is there a script somewhere we can run to create the alleged missing time zone table? 040804 10:09:49 Warning: Can't open time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them thanks jeff -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedures in 5.0.0-alpha
Hi There,
I was wondering if anyone has used the Stored Procedures in MySQL
version 5.0.0-alpha. I have setup a MySQL server version 5.0 alpha and
was trying out the stored procedures. I noticed some strange behaviour
in using some of the function calls from within the stored procedures. I
am facing problems mainly with LAST_INSERT_IDENITY() and MAX. For
example tried the following
Step 1
--
mysqlcreate table test
-(
- id int auto_increment primary key,
- namevarchar(50)
-);
Query OK, 0 rows affected (0.01 sec)
mysqlDelimiter //
Step 2
--
CREATE PROCEDURE insert_test(SomeName varchar(50), OUT identity int)
begin
insert into test (id, name) values (Null, SomeName);
-- select LAST_INSERT_ID() into identity;
-- OR
-- set identity = LAST_INSERT_ID();
end
//
Delimiter ;
Step 3
--
Call insert_test('Naresh', @a);
Query OK, 0 rows affected (0.00 sec)
Step 4
--
Select @a;
+--+
| @a |
+--+
| NULL |
+--+
1 row in set (0.00 sec)
Step 5
--
Select * from test
+++
| id | name |
+++
| 1 | Naresh |
+++
1 row in set (0.00 sec)
Simillary there is a problem with the max function also. Seems like that
when the procedure is created at step 2 the value of LAST_INSERT_ID is
calculated at that point and stored within the procedure defination. If
after step 5 I were to drop the stored procedure and create it again,
and run the step 3 and step 4 again, it will return the value 1 for the
command select @a.
Any ideas? Comments? Is it a bug ...?
Regards
Naresh
--
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom
they are addressed.
If you have received this email in error please notify the
originator of the message. This footer also confirms that this
email message has been scanned for the presence of computer viruses.
Any views expressed in this message are those of the individual
sender, except where the sender specifies and with authority,
states them to be the views of DA Group.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design Question
I think I understand. So instead of my queries being centered around the listings table, they will be centered around this new table? Currently, I pull the records for each category like so: $query = select l.CatalogNumber, l.PDFLink, l.PDFName, l.MP3Name, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, l.DiscountID, l.DiscountType, l.DiscountAmount, o.Alias, l.Description from listings l, publishers p, composers c, arrangers a, categories o where l.CategoryID=o.CategoryID and o.Name='.$Category.' and l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID order by .$OrderBy; To follow your example, I would add these other fields to the listings_projects table you defined below, and restructure the query around that? Thanks for your insight! -Erich- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 04, 2004 11:51 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Design Question As posted, your data structure supports two one-to-many relationships, not the one-to-one relationships as you described. You can have multiple Listings per Category and multiple Categories per Group. What it sounds like you have been asked to do is to support a many-to-many relationship. You need to support both multiple Listings per Category and multiple Categories per Listing. As you have it now: Groups (1..*) Categories (1..*) Listings As you need it to be: Groups (1..*) Categories (*..*) Listings To create a (*..*) relationship between two tables, you need a third table. Each entry in this table represents one Listing-Category association (relationship). CREATE TABLE listings_projects ( ListingID bigint not null , CategoryID int , ... any additional fields as needed ... , PRIMARY KEY (ListingID, CategoryID) ) The primary key ensures that at each Listing/Category combination appears only once (no duplicate assignments). I showed you where additional fields can fit into the relation table because sometimes there are facts about relationships that do not fit into either of the tables they relate A recent example in this list was a relation table between chemical compounds and the various plants in which those compounds could be found. A fact that belongs to the *relationship* could be the concentration of that chemical in that plant. That concentration value would not belong to the plants table nor would it belong to the compounds table but does belong to the relationship of plant to compound. Make sense? I have personally used additional fields like those to indicate deletion. That way old values are no longer available for new relationships (in my applications) and my queries won't break as I didn't actually get rid of any information. My historical reports still function as the old names are still in the system, even if you can't use the the old names for any current purposes. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Erich Beyrent [EMAIL PROTECTED] wrote on 08/04/2004 10:35:33 AM: Hi all, I need some advice on a project I have. Basically, I have some tables: CREATE TABLE listings ( ListingID bigint(20) unsigned NOT NULL auto_increment, CatalogNumber varchar(12) NOT NULL default '', PDFLink varchar(100) default NULL, PDFName varchar(80) default NULL, Title varchar(100) NOT NULL default '', ComposerID int(11) default NULL, ArrangerID int(11) default NULL, PublisherID int(11) default NULL, Price double(16,2) NOT NULL default '0.00', DiscountID int(11) default NULL, Description text, NewTitles tinyint(1) default NULL, CategoryID int(11) NOT NULL default '0', PRIMARY KEY (ListingID) ) TYPE=MyISAM; CREATE TABLE categories ( CategoryID int(11) NOT NULL auto_increment, Name varchar(50) NOT NULL default '', Alias varchar(60) default NULL, DiscountID int(11) default NULL, Description text, GroupID int(11) NOT NULL default '0', PRIMARY KEY (CategoryID) ) TYPE=MyISAM; CREATE TABLE groups ( GroupID int(11) NOT NULL auto_increment, Name varchar(50) default NULL, DiscountID int(11) default NULL, PRIMARY KEY (GroupID) ) TYPE=MyISAM; Currently, there is a one-to-one relationship between listings and categories, and listings and groups. Now, the customer is requesting that a listing be included in several categories. I am not quite sure how to do this. My thought was to add a new field to the listings table that would contain a
RE: time zone leap seconds
There is are five new time_% tables. You could install 4.1.3 in a clean area, export the tables and import the tables into the upgraded environment. You could also export your 4.0.x data and import this data into the newly created 4.1.3 environment. -Original Message- From: Jeff Mathis To: mysql Sent: 8/4/04 11:14 AM Subject: time zone leap seconds we just upgraded from 4.0.4 to 4.1.3, and are getting this warning. is there a script somewhere we can run to create the alleged missing time zone table? 040804 10:09:49 Warning: Can't open time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them thanks jeff -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- 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]
Re: NULL values
For certain columns i am completely sold on not using nulls, for others, i cant see any conceptual reason to favor either way, so i thought i'd tap you all for some insight. A NULL is the equivalent of saying I don't know whereas a 0 means the value between -1 and 1 and a blank means 'no value'. For example... suppose you're recording temperatures at various locations. Atlanta blank Boston 0 Chicago null It pretty clear that the temperature in Boston is 0, which is not the same as being empty. The difference between null and blank is a bit more subtle. null means that you don't know what the value is - if the folks in Chicago say that their thermometer is broken, they have reported their results, but their results are we have no idea how cold it is here. You use a blank in Atlanta until you hear from them, at which time you either have a temperature (an actual number), or you have another We have no idea how cold it is outside because our thermometer is broken too. which means null again. Does that help? -- Whil Moving to Linux: Freedom, Choice, Security, Opportunity http://www.hentzenwerke.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design Question
I think you understand. Here is how I would re-write the query to use the new table: $query = SELECT l.CatalogNumber, l.PDFLink, l.PDFName, l.MP3Name, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, l.DiscountID, l.DiscountType, l.DiscountAmount, o.Alias, l.Description FROM listings l INNER JOIN publishers p ON l.PublisherID=p.PublisherID INNER JOIN composers c ON l.ComposerID=c.ComposerID INNER JOIN arrangers a ON l.ArrangerID=a.ArrangerID INNER JOIN listings_categories lc ON l.ListingID = lc.ListingID INNER JOIN categories o ON lc.CategoryID = o.CategoryID WHERE o.Name='.$Category.' ORDER BY .$OrderBy; (That's just the style I prefer as I can more easily spot which match-up conditions belong to which sets of tables. That way I am less likely to leave one out and accidentally create a cartesian product of any two tables. The comma separated style you use is absolutely, perfectly valid.) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Erich Beyrent [EMAIL PROTECTED] wrote on 08/04/2004 12:39:55 PM: I think I understand. So instead of my queries being centered around the listings table, they will be centered around this new table? Currently, I pull the records for each category like so: $query = select l.CatalogNumber, l.PDFLink, l.PDFName, l.MP3Name, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, l.DiscountID, l.DiscountType, l.DiscountAmount, o.Alias, l.Description from listings l, publishers p, composers c, arrangers a, categories o where l.CategoryID=o.CategoryID and o.Name='.$Category.' and l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID order by .$OrderBy; To follow your example, I would add these other fields to the listings_projects table you defined below, and restructure the query around that? Thanks for your insight! -Erich- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 04, 2004 11:51 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Design Question As posted, your data structure supports two one-to-many relationships, not the one-to-one relationships as you described. You can have multiple Listings per Category and multiple Categories per Group. What it sounds like you have been asked to do is to support a many-to-many relationship. You need to support both multiple Listings per Category and multiple Categories per Listing. As you have it now: Groups (1..*) Categories (1..*) Listings As you need it to be: Groups (1..*) Categories (*..*) Listings To create a (*..*) relationship between two tables, you need a third table. Each entry in this table represents one Listing-Category association (relationship). CREATE TABLE listings_projects ( ListingID bigint not null , CategoryID int , ... any additional fields as needed ... , PRIMARY KEY (ListingID, CategoryID) ) The primary key ensures that at each Listing/Category combination appears only once (no duplicate assignments). I showed you where additional fields can fit into the relation table because sometimes there are facts about relationships that do not fit into either of the tables they relate A recent example in this list was a relation table between chemical compounds and the various plants in which those compounds could be found. A fact that belongs to the *relationship* could be the concentration of that chemical in that plant. That concentration value would not belong to the plants table nor would it belong to the compounds table but does belong to the relationship of plant to compound. Make sense? I have personally used additional fields like those to indicate deletion. That way old values are no longer available for new relationships (in my applications) and my queries won't break as I didn't actually get rid of any information. My historical reports still function as the old names are still in the system, even if you can't use the the old names for any current purposes. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Erich Beyrent [EMAIL PROTECTED] wrote
Re: Best options for unique string
Hashing algorithms like MD5 are good answers to what you're
looking for, but first, I'd recommend SHA1. MD5 is known to
have some weaknesses, and SHA1 produces a longer, more
secure 160-bit string (called a message digest). MySQL
versions 4.0.2 and up have SHA1 built-in.
Whether you use MD5 or SHA1, be careful! What you're doing
is simply transforming some plaintext string into a
fixed-length set of bits. That transformation process is
constant, though, so if you simply rely on this:
mysql SELECT SHA1('[EMAIL PROTECTED]');
+--+
| SHA1('[EMAIL PROTECTED]') |
+--+
| 6d01e80554a8a6c560bfb6a47aede430b98189fe |
+--+
I might see that authentication string and think to myself,
Self, doesn't that look like a SHA1 digest? If I run my
email address through the SHA1 algorithm, I'll get the same
result:
$ perl -MDigest::SHA1=sha1_hex -e print sha1_hex('[EMAIL PROTECTED]')
6d01e80554a8a6c560bfb6a47aede430b98189fe
Since the resulting strings match, I know that all you're
doing is printing an MD5 digest of the email address. I can
then use that knowledge to generate a digest of your email
address:
$ perl -MDigest::SHA1=sha1_hex -e print sha1_hex('[EMAIL PROTECTED]')
885e8118a0de793e9158b9bc31ac9db33fc6308d
and use that to subscribe you to alt.hot.nasty.yeti or
whathaveyou.
There are several solutions: the easiest is simply prefixing
a string (called a salt) to the email address:
mysql select SHA1(CONCAT('SEEKRIT', '[EMAIL PROTECTED]'));
+--+
| SHA1(CONCAT('SEEKRIT', '[EMAIL PROTECTED]')) |
+--+
| 158670f2bc972e8147c6e33764e27e69b315 |
+--+
So without knowing that you prefix email addresses with that
string, I can't create the same digest. Of course, if I get
you drunk and you confess the secret string, then all bets
are off. The advantage here is that you don't need to store
anything: as long as you have the email address and the
secret string, you can recreate the digest. So, in this
scenario:
1) The user requests a subscription change.
2) A digest is generated as above and mailed to user.
3) The user clicks on the link.
4) The user submits her email address and the mailed digest,
confirming the subscription change.
5) A digest is generated from the submitted email address
and compared against the digest submitted.
6) If they match, the subscription change is made.
The weakness here is that the digest is /always/ valid; that
is, the user can use that same digest over and over again.
Thus, if a blackhat intercepts this mail or stumbles over it
after the fact, he or she has carte blanche to make
subscription changes for that person for all eternity.
A slightly more complicated but somewhat safer method is to
generate a one-time digest using RAND() or similar, plus the
email. This gives you a new digest every time you request
one:
mysql select SHA1(CONCAT(RAND(), '[EMAIL PROTECTED]'));
+--+
| SHA1(CONCAT(RAND(), '[EMAIL PROTECTED]')) |
+--+
| 8b79c52a35a613fd6a014a66c608c9d98c95372e |
+--+
mysql select SHA1(CONCAT(RAND(), '[EMAIL PROTECTED]'));
+--+
| SHA1(CONCAT(RAND(), '[EMAIL PROTECTED]')) |
+--+
| eff49d94ccc1f3524e828cb91195767628c7bc43 |
+--+
mysql select SHA1(CONCAT(RAND(), '[EMAIL PROTECTED]'));
+--+
| SHA1(CONCAT(RAND(), '[EMAIL PROTECTED]')) |
+--+
| 0a56be414d9be2f4676fff90836c72e695f1a013 |
+--+
so you /can't/ recreate the digest. It'd work like this:
1) The user requests a subscription change.
2) A random digest is generated and stored along with the
user's email address in a requests pending table.
3) The digest is mailed to user.
4) The user clicks on the link.
5) The user submits her email address and the mailed digest,
confirming the subscription change.
6) The email address and digest from the link is checked
against the the requests pending table.
7) If found, the digest is deleted from requests pending.
8) The subscription change is made.
If a blackhat intercepts this email, they can only make one
change. If a blackhat finds the email after the real user
has made the change, the blackhat can't do anything, because
the digest is no longer in the table. Plus, you can add a
timestamp to the requests pending table and delete digests
older than one day, further limiting treachery.
Most importantly, you should know that no hashing algorithm
is perfect, RAND() isn't entirely random, and security is
Not Easy. Here's some reading material:
Re: Table Corruption
On Wednesday 04 August 2004 06:44 am, Odhiambo Washington wrote: Is there hope for me in this situation??? I have googled and googled but all that I see doesn't seem to help me to recover the data in the table. This makes me very desparate. I'd be very thankful for any pointers that would help me out of this successfully. I have backup copies of those files. Backup?? Time for a restore there bud. -- === Jabber: tradergt@(smelser.org|jabber.org) Quote: Fried but surviving. === pgpWpN4iErJc5.pgp Description: PGP signature
Re: CREATE performance degradation from 4.0.17 - 4.0.20
On Mon, 2004-08-02 at 14:57, Tinley, Jeremy wrote: We're upgrading from 3.23.58 to 4.0.20 and found that that although the ALTER test results of sql-bench had been greatly improved, CREATE has shown nasty performance degradation. Just before needing to make the decision to revert back to 3.23.58, we found a post here where someone had a similar problem when using SAN storage. We see the problem using hardware RAID, shared storage or local SCSI disks. The machine in question is a 3ghz, 4GB RAM, reiserfs. The data and application reside on local SCSI disks, 10k rpm. All installations are the MySQL provided linux-binary (x86), Standard releases. Hi, This is the known issue. In MySQL 4.0.17 calling fsync() on frm files was added during table creation. This was done so create table is more durable if used with transactional tables such as Innodb. It however affects all tables at this point. In most cases new tables are created rarely so it is not the problem, if it is for you case --skip-sync-frm option can be used to avoid such behavior. On other hand B-C changes for some tests surprise me. Are the results stable if you repeat the run ? In some cases especially for short tests deviation can be pretty large. Here is an excerpt of sql-bench results: TestABC DE -- alter_table_add6026 88 alter_table_drop 4315 88 create+drop12 11 11 240 223 create_MANY_tables 10 11 10 220 228 create_index111 11 create_key+drop14 15 15 231 221 create_table000 00 select_1_row088 89 select_2_rows 199 99 select_column+column199 99 select_group_when_MANY_tables 59 1110 10 Column A is MySQL 3.23.58 Column B is MySQL 4.0.15 Column C is MySQL 4.0.16 Column D is MySQL 4.0.17 Column E is MySQL 4.0.20 The biggest problem is the create set. That's a HUGE difference in the exact same hardware. Thoughts? -J -- Peter Zaitsev, Senior Support Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CREATE performance degradation from 4.0.17 - 4.0.20
On other hand B-C changes for some tests surprise me. Are the results stable if you repeat the run ? In some cases especially for short tests deviation can be pretty large. The results are stable, sadly. The bigger surprise was the select deviation from 3 to 4, but so far, in application testiong, it doesn't seem to be an issue. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB TableSpace Question
David Griffiths writes: Oracle cannot shrink datafiles (same idea as InnoDB datafiles) when data is deleted either. Actually, Oracle has been able to resize data files since 7.2. It is usually done with an 'alter tablespace ... coalesce' followed by an 'alter tablespace datafile ... resize nM' command. But the resize will choke on a datafile with active extents in that datafile. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backing Up Innodb table with individual tablespaces
If I use innodb_file_per_table, how do I properly back-up that table so it could be imported to another server should something happen to my main server if I do not have the hot-back-up utility? If I lock then copy the table, can I simply use ALTER TABLE tbl_name IMPORT TABLESPACE for it to work with another mysql server? Thanks for any help. Best Regards, Andrew
[mysql] Question about the reference manual
Is the MySQL Reference Manual, by Widenius and Axmark (the one to which there is a link on the Documentation page of the mysql website, for sale by Barnes and Noble) the same reference manual that you can download? The PDF version has 1310 pages, the softcover book has 712 pages, and I'm not sure that they're the same. I like to have an actual book so that I can take it with me and study in places where I have to waste some time waiting. But I suspect the online manual is more up to date than the published book, and wanted to confirm whether that is true. rdo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
load data infile question
I haven't used load data infile much, mainly because of issues like this question. I want to load a bunch of data for our data warehouse into about 10 different tables. But when I load a parent table, an auto_increment column autogenerates a value that will be a foreign key in a child table. So i cant create the file to load into the child table until after the parent table has been loaded. Then i'll need to get back all the auto increment values just created, and put them into the load file for the child tables. Is this how it has to be, or am i missing something? thanks much sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: load data infile question
For this type of custom loading you may want to explore a programming language such as Java or C/C++ or Perl. Depending on your platform you could even explore some third party tools. -Original Message- From: sean c peters To: [EMAIL PROTECTED] Sent: 8/4/04 3:27 PM Subject: load data infile question I haven't used load data infile much, mainly because of issues like this question. I want to load a bunch of data for our data warehouse into about 10 different tables. But when I load a parent table, an auto_increment column autogenerates a value that will be a foreign key in a child table. So i cant create the file to load into the child table until after the parent table has been loaded. Then i'll need to get back all the auto increment values just created, and put them into the load file for the child tables. Is this how it has to be, or am i missing something? thanks much sean peters [EMAIL PROTECTED] -- 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]
RE: Insert problems with InnoDB (big table)
Are you disabling autocommit before doing the inserts? And committing after all inserts are complete? -Original Message- From: Luc Charland [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 03, 2004 7:54 PM To: [EMAIL PROTECTED] Subject: Insert problems with InnoDB (big table) We are evaluating the replacement of a Sybase database with MySQL. The databases are 60+GB, containing more than 100 tables. Since we need transactions, that implies InnoDB. We were happy with the early results, but we hit a major roadblock when trying to import the biggest table (20+GB, with 4 indexes). We have reproduced the problem with a simpler table on many different servers and MySQL versions (4.X). At first, we easily insert 1600+ lines per second. As the number of lines grows, the performance deteriorate (which I can understand), but it eventually gets so slow that the import would take weeks. Doing a vmstat on the server shows that after a certain limit is reached (index bigger than the total mem ?), mysqld starts reading as much as writing, and the CPU usage goes down as the I/O eventually reach the maximum for the server. If you wait long enough, you get less than 50 lines per second (which is 30+ times slower than the first few million inserts). We have done the same tests on Sybase and another database on the same machines and have not seen this behavior, so it is not hardware related. We have done the same import in a MyISAM table and have not see any slowdown (the whole data was imported very fast, even if we had to wait a very long time --5+ hours-- for the index to rebuild after). We have tried to transform the MyISAM table into a InnoDB (same problem occurs). We have tried to import from the MyISAM table into an empty InnoDB, same problem occurs. SETUP: We have of course changed the following innodb_buffer_pool_size= (50% to 80% of total ram) innodb_log_file_size=(20% to 40% of total ram) we have tried different innodb_flush_method we have tried innodb_flush_log_at_trx_commit (0, 1) we have tried ibdata1:1G:autoextend, and also make it big enough so that all the data will fit without autoextending. we have tried creating the indexes after instead of before the inserts, but like the documentation says, it is not better. Is there an upper limit to the size of the indexes of a single table in InnoDB? Anybody else has seen this kind of slowdown for big InnoDB tables? Here is a small table that reproduce the problem (if you make 5 to 15 million inserts). We wrote a few programs (one in C++, one in Python) that generates random data and insert into the database. __ create table smallest ( id int primary key, name varchar(80), model char(20) , description varchar(255), lastupdate date, price decimal(8,2), cost decimal(8,2)) type=innodb create unique index smallcomplex on smalltest (model, id, name) create index smallprice on smalltest (price) create index smallcost on smalltest (cost) create index smallname on smalltest (name) __ Thanks for any help. Luc Charland -- 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]
Re: [mysql] Question about the reference manual
On Wed, Aug 04, 2004 at 02:46:50PM -0500, rdo mail list address wrote: Is the MySQL Reference Manual, by Widenius and Axmark (the one to which there is a link on the Documentation page of the mysql website, for sale by Barnes and Noble) the same reference manual that you can download? The PDF version has 1310 pages, the softcover book has 712 pages, and I'm not sure that they're the same. I like to have an actual book so that I can take it with me and study in places where I have to waste some time waiting. But I suspect the online manual is more up to date than the published book, and wanted to confirm whether that is true. The MySQL Reference Manual published by O'Reilly is the same reference manual as the website -- as it existed when it was published in June 2002. The manual has been updated quite extensively since then. Two new books have just been published under the MySQL Press imprint that are derived from the online manual. You can find more information about them at http://www.mysqlpress.com/ Jim Winstead MySQL AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert problems with InnoDB (big table)
Also, are the indexes in place when you start your inserts? Constantly updating those indexes will be slow; try inserting without indexes, and then building the indexes. You can also limit the size of your index file by, 1) Making sure all columns are as small as possible (ie MEDIUMINT rather than INT) 2) If possible, consider using partial indexes on VARCHAR or CHAR columns (see http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html). 3) Make sure you have enough tablespace. If your last datafile specified is autoextend, see if you are using it. InnoDB seems to use tablespace temporarily during index creation; if you don't have enough, and have an autoextend, it will start growing the autoextend-datafile for the index creation. This slows things down quite a bit. Sounds like this is not the case, however. 4) And, as mentioned below, turn autocommit off. Index creation with InnoDB and large tables is very very slow. Heikki Tuuri has a faster-index creation on his TODO list (http://www.innodb.com/todo.php) but it's marked as Long Term David [EMAIL PROTECTED] wrote: Are you disabling autocommit before doing the inserts? And committing after all inserts are complete? -Original Message- From: Luc Charland [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 03, 2004 7:54 PM To: [EMAIL PROTECTED] Subject: Insert problems with InnoDB (big table) We are evaluating the replacement of a Sybase database with MySQL. The databases are 60+GB, containing more than 100 tables. Since we need transactions, that implies InnoDB. We were happy with the early results, but we hit a major roadblock when trying to import the biggest table (20+GB, with 4 indexes). We have reproduced the problem with a simpler table on many different servers and MySQL versions (4.X). At first, we easily insert 1600+ lines per second. As the number of lines grows, the performance deteriorate (which I can understand), but it eventually gets so slow that the import would take weeks. Doing a vmstat on the server shows that after a certain limit is reached (index bigger than the total mem ?), mysqld starts reading as much as writing, and the CPU usage goes down as the I/O eventually reach the maximum for the server. If you wait long enough, you get less than 50 lines per second (which is 30+ times slower than the first few million inserts). We have done the same tests on Sybase and another database on the same machines and have not seen this behavior, so it is not hardware related. We have done the same import in a MyISAM table and have not see any slowdown (the whole data was imported very fast, even if we had to wait a very long time --5+ hours-- for the index to rebuild after). We have tried to transform the MyISAM table into a InnoDB (same problem occurs). We have tried to import from the MyISAM table into an empty InnoDB, same problem occurs. SETUP: We have of course changed the following innodb_buffer_pool_size= (50% to 80% of total ram) innodb_log_file_size=(20% to 40% of total ram) we have tried different innodb_flush_method we have tried innodb_flush_log_at_trx_commit (0, 1) we have tried ibdata1:1G:autoextend, and also make it big enough so that all the data will fit without autoextending. we have tried creating the indexes after instead of before the inserts, but like the documentation says, it is not better. Is there an upper limit to the size of the indexes of a single table in InnoDB? Anybody else has seen this kind of slowdown for big InnoDB tables? Here is a small table that reproduce the problem (if you make 5 to 15 million inserts). We wrote a few programs (one in C++, one in Python) that generates random data and insert into the database. __ create table smallest ( id int primary key, name varchar(80), model char(20) , description varchar(255), lastupdate date, price decimal(8,2), cost decimal(8,2)) type=innodb create unique index smallcomplex on smalltest (model, id, name) create index smallprice on smalltest (price) create index smallcost on smalltest (cost) create index smallname on smalltest (name) __ Thanks for any help. Luc Charland -- 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]
Re: UTF8 collations in 4.1.3
Entering it in hex works for me too. So the problem _was_ actually with the values I inserted into the database. What's the best way to actually see what is stored in the database, preferably as hex or something else that a terminal is guaranteed to display correctly? Clearly, what I was doing earlier was not correct. SELECT hex(your_column) FROM your_table; I usually convert utf8 to ucs2 so that I can recognize the codepoints easier. SELECT hex(CONVERT(your_column USING ucs2)) FROM your_table; There is also a new UNHEX() function which appeared in 4.1.2. best, Jeremy March -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile question
sean c peters wrote: But when I load a parent table, an auto_increment column autogenerates a value that will be a foreign key in a child table. So i cant create the file to load into the child table until after the parent table has been loaded. Then i'll need to get back all the auto increment values just created, and put them into the load file for the child tables. If no one else is going to be adding rows to the tables while you're doing the loading, then you can make your own values for the auto_increment column and include them in the text file rather than letting MySQL generate them. Just find the max current value and start counting from there, and using the same values in the child tables. I do something similar for one of my databases, and it works because there's no other process for inserting rows into those tables. That may not apply to your situation, though. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql-4.0.20 configure fails mac os x 10.3.1 client
1. I re-examined the error messages in config.log. I
couldn't find anything obvious to fix.
2. I decided to gamble and run make, compile went OK
3. Ran make install, install went OK
4. Ran mysql_install_db, went OK
5. Changed file permissions
drwxr-xr-x 13 root mysql 442 30 Jul 05:29 ./
drwxr-xr-x 5 root wheel 170 25 Jul 20:32 ../
drwxr-xr-x 48 root mysql 1632 30 Jul 05:23 bin/
drwxr-xr-x 6 mysql mysql 204 30 Jul 05:43 data/
drwxr-xr-x 3 root mysql 102 30 Jul 05:22
include/
drwxr-xr-x 4 root mysql 136 30 Jul 05:22 info/
drwxr-xr-x 3 root mysql 102 30 Jul 05:22 lib/
drwxr-xr-x 3 root mysql 102 30 Jul 05:22
libexec/
drwxr-xr-x 3 root mysql 102 30 Jul 05:22 man/
drwxr-xr-x 9 root mysql 306 30 Jul 05:23
mysql-test/
drwxr-xr-x 3 mysql mysql 102 4 Aug 15:17 run/
drwxr-xr-x 3 root mysql 102 30 Jul 05:22 share/
drwxr-xr-x 29 root mysql 986 30 Jul 05:22
sql-bench/
6. Ran mysqld_safe OK,
[1]+ Running sudo
/usr/local/mysql/bin/mysqld_safe --user=mysql (wd:
/usr/local/mysql/bin)
7. Attempted to set password on database, with the
following error.
/usr/local/mysql/bin/mysqladmin: connect to server at
'localhost' failed
error: 'Can't connect to local MySQL server through
socket '/usr/local/mysql/run/mysql_socket' (38)'
Check that mysqld is running and that the socket:
'/usr/local/mysql/run/mysql_socket' exists!
Instructions at
http://developer.apple.com/internet/opensource/osdb.html
recommended running configure with the following
option:
--with-unix-socket-path=/usr/local/mysql/run/mysql_socket
First attempt at setting the password failed, I then
created mysql_socket directory to see if that would
make any difference, same message.
Thanks for any suggestions and for the help to get me
this far.
Ron
--- Ron Phelps [EMAIL PROTECTED] wrote:
I executed the following preparation work:
1. Upgraded Mac OS X 10.3.1 to 10.3.4
2. Installed two additional security updates
3. Examined the status of the Xcode packages
pre-installed on the box Documentation states it is
Xcode 1.0 for Mac OS 10.3 Panther
4. I ran all installers except, CHUD, DevExamples,
cross-development and x11
5. I upgraded Xcode 1.0 to Xcode 1.1
6. Added FLAG options to a .config wrapper for
configure
Ran .config with the following results:
1. configure terminates with exit 0 (successful
return
?)
2. Welcome message printed to terminal
3. Many error messages were logged, first and second
of shown below
4. Approximately 208 Makefiles were created
What is the current state of the configure
environment
- success as indicated by the return code? But what
of these error messages?
Since the state of the Xcode environment may be
suspect, I wonder if it makes sense to flush it, and
start over with a clean setup - which of course
raises
the question how to clean up the current environment
correctly in order to start over.
Thanks for any suggestions.
Ron
configure:2913: checking for gcc option to accept
ANSI
C
configure:2974: gcc -c -O3 -fno-omit-frame-pointer
conftest.c 5
configure:2977: $? = 0
configure:2980: test -s conftest.o
configure:2983: $? = 0
configure:3001: result: none needed
configure:3019: gcc -c -O3 -fno-omit-frame-pointer
conftest.c 5
conftest.c:2: error: parse error before me
configure:3022: $? = 1
configure: failed program was:
| #ifndef __cplusplus
| choke me
| #endif
.
.
.
configure:3586: gcc -c -O3 -fno-omit-frame-pointer
-felide-constructors -fno-exceptions
-fno-rtti conftest.cc 5
configure: In function `int main()':
configure:3587: error: `exit' undeclared (first use
this function)
configure:3587: error: (Each undeclared identifier
is
reported only once for
each function it appears in.)
configure:3589: $? = 1
configure: failed program was:
| #line 3569 configure
| /* confdefs.h. */
|
| #define PACKAGE_NAME
| #define PACKAGE_TARNAME
| #define PACKAGE_VERSION
| #define PACKAGE_STRING
| #define PACKAGE_BUGREPORT
| #define PACKAGE mysql
| #define VERSION 4.0.20
| #define PROTOCOL_VERSION 10
| #define DOT_FRM_VERSION 6
| #define SYSTEM_TYPE apple-darwin7.4.0
| #define MACHINE_TYPE powerpc
| /* end confdefs.h. */
|
| int
| main ()
| {
| exit (42);
| ;
| return 0;
| }
--- Michael Stassen [EMAIL PROTECTED]
wrote:
First, I should point out that the simplest course
would be to download the
precompiled binary from mysql. That said, I admit
I
like to build from
source, largely because whenever things go wrong,
I
always learn something.
On first glance, I see a few problems:
1) You are running OS X 10.3.1, but current is
10.3.4. I don't believe
that's the cause of the problem here, but there
are
some important security
updates you are missing. I'd recommend running
Software Update to install
at least the security patches.
2) You appear to have Xcode 1.1, based on your gcc
help with optimizing insert speed
Hi everyone, I hope you can give me some pointers to speed up the inserts for a simple InnoDB table. We are running MySQL 4.0.16-Max on a machine with 4 Intel Xeon 2.8 GHz CPU's, 2 GB RAM, Red Hat 9 Linux kernel 2.4.20. The innodb_buffer_pool_size is set to 512 MB. This is the create statement for the table in question: CREATE TABLE `sensortest_rawdata` ( `db_test_id` int(10) unsigned NOT NULL default '0', `measurement_no` int(10) unsigned NOT NULL default '0', `point_no` smallint(6) unsigned NOT NULL default '0', `sweep_no` tinyint(3) unsigned default '0', `source_voltage` float default NULL, `gate_voltage` float default '0', `source_current` float default '0', `gate_current` float default NULL, PRIMARY KEY (`measurement_no`,`point_no`,`db_test_id`), KEY `db_test_id` (`db_test_id`,`measurement_no`) ) TYPE=InnoDB COMMENT='raw measurements from FTB'; According to SHOW TABLE STATUS, it has approximately 200 million records and takes 25 GB. One block of measurements typically consists of 256 records with the same values in db_test_id and measurement_no. It takes 200 - 300 ms to insert. At first, I was inserting the records one by one, then switched to this syntax: INSERT INTO table (field1, field2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value 2_2), ... This did not seem to affect the speed. I could store the measurements in BLOB columns and thus have a single record per block of measurements, as I did in another table in this database, but right now that would make viewing the results a lot more complicated. The program that performs the inserts is written in Java and uses the MySQL Connector/J 3.0.9 driver. The inserts happen through a PerparedStatement object, so, theoretically, the query should be compiled only once, when the object is instantiated. I'm not sure whether the driver and the database take advantage of that... Originally it was running on another machine, and all the upload traffic went through Ethernet. I thought that might be the bottleneck and tried to run it on the same machine that hosts the database, with no noticeable improvement in speed. As far as I understand, it still goes through TCP/IP. In any case, by my calculations, the network overhead should be orders of magnitude less than the 200 - 300 ms it takes to insert one block of measurements. I noticed that the CPU usage by mysqld-max during these inserts is about 10%. I thought one possible reason was updating the index on the db_test_id and measurement_no columns, but I cannot remove it, as the other queries would becme very slow... In fact, I'd like to add one more index, just on db_test_id column. Can I increase some buffer size or do anything else to speed up these inserts? Ideally, I'd like them to be faster by an order of magnitude... Below is the output of show innodb status command issued during the upload. Sorry about the length e-mail... Thanks in advance for your help, Sergei = 040804 16:17:46 INNODB MONITOR OUTPUT = Per second averages calculated from the last 10 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 89061, signal count 88892 --Thread 622604 has waited at btr0cur.c line 390 for 0.00 seconds the semaphore: X-lock on RW-latch at 6142b5b4 created in file buf0buf.c line 444 a writer (thread id 622604) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file btr0cur.c line 3555 Last time write locked in file buf0buf.c line 1404 Mutex spin waits 1024927, rounds 5574408, OS waits 23954 RW-shared spins 74298, OS waits 35231; RW-excl spins 21136, OS waits 20743 TRANSACTIONS Trx id counter 0 2055621 Purge done for trx's n:o 0 2055606 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 19835, OS thread id 573455 MySQL thread id 26, query id 85617 dba2.nano.covalentmaterials.co 192.168.1.231 dba SHOW INNODB STATUS ---TRANSACTION 0 0, not started, process no 19833, OS thread id 540686 MySQL thread id 24, query id 84974 dba2.nano.covalentmaterials.co 192.168.1.231 dba ---TRANSACTION 0 2055613, not started, process no 19750, OS thread id 294925 MySQL thread id 9, query id 59947 localhost.localdomain 127.0.0.1 dba ---TRANSACTION 0 2055620, ACTIVE 29 sec, process no 19857, OS thread id 622604 inserting, thread declared inside InnoDB 445 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320, undo log entries 34370 MySQL thread id 29, query id 85614 faster.nano.com 192.168.1.21 dba update insert into sensortest_rawdata (db_test_id, measurement_no, point_no, sweep_no, source_voltage, gate_voltage, source_current) values (12773, 267, 1, 1 FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for
Re: using mysql in commercial software
Greetings All, I walked through the mailing list thread earlier today. The big issues that I believe that people raised were: * Incorrect information on distribution as it applies to the GPL in our licensing documents * Lack of clarity from MySQL on what is and not acceptable use of GPL-licensed MySQL * Lack of concrete examples of suitable/unsuitable use of GPL-licensed MySQL I opened up a new ticket at http://zak.greant.com:/licensing/tktview?tn=40 to cover the last two of issues. The ticket is rather terse, but I think that we all understand the issues. The existing ticket http://zak.greant.com:/licensing/tktview?tn=32 seems to cover the first issue. Also, I have started discussing these issues with the other MySQLers and hope to have something useful to report soon. I understand that this is not optimal because it does not involve all of the people who have a stake in the licensing. Frankly licensing is a tough issue for us - it is the base of our revenue and it affects many people within and without the company. Additionally, it is a legal issue - something that makes many people, including us, very cautious. Please continue to provide feedback on this area using any channel that you feel comfortable using. I admit that I prefer knowing about feedback on MySQL, so a courtesy Cc to me at [EMAIL PROTECTED] and/or to our community list ([EMAIL PROTECTED]) and/or an open ticket in the licensing issue tracking system (http://licensing.zak.greant.com) are all greatly appreciated. Cheers! --zak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Export and destroy relation
A client wants access to some data in mysql 3, it is a simple case of a user table and a registered_serials table, there is always one user, and there can be many resistered serials. (One to many) They want to somehow get this data into Excel, so I want to give them one record per user, even though there can be many registered_serials. The result would be something like: Firsttablasttabemailtabserial1,serial2,serial3 Is this possible? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQLException: Unable to connect to any hosts due to exception: java.security.AccessControlException: access denied (java.net.SocketPermission 127.0.0.1:3306 connect,resolve)
I am trying to connect to a database which is being served off of my machine (same one I am using to connect to it). It works fine when I do- System.out.println(rs.getString(D.Name) );- and it prints it out in an application. But when I try to output it in an applet, it gives me this error: SQLException: Unable to connect to any hosts due to exception: java.security.AccessControlException: access denied (java.net.SocketPermission 127.0.0.1:3306 connect,resolve) SQLState: 08S01 VendorError: 0 Does anyone know what this means, or how to fix this? Thanks in advance, Nathan E. Pierce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Jeremy Zawodny's gcc flags or MySQL AB' for a FreeBSD?
Good day. From these sources: http://jeremy.zawodny.com/blog/archives/000458.html http://dev.mysql.com/doc/mysql/en/FreeBSD.html Jeremy is using -O -march=pentiumpro and MySQL AB -O2 -fno-strength-reduce. Does anyone compared this? Or it make no sense? PS. MySQL 4.x and FreeBSD 4.x -- Best regards, Evgeny. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQLException: Unable to connect to any hosts due to exception: java.security.AccessControlException: access denied (java.net.SocketPermission 127.0.0.1:3306 connect,resolve)
Generally applets are anly allowed to access the host that served them. Try to use the host name (localhost or whetever your machine is called) rather than the IP address, or conversely, give the IP address rather than localhost to the browser. I don't know whether applets are restiricted to the same port or not. If they are, you're out of luck. You can ask for permissions on the client machine, but that's really not worth the trouble. In any case, a better solution would be to let the web server access the database. -Original Message- From: Nathan Pierce [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 04, 2004 5:57 PM To: [EMAIL PROTECTED] Subject: SQLException: Unable to connect to any hosts due to exception: java.security.AccessControlException: access denied (java.net.SocketPermission 127.0.0.1:3306 connect,resolve) I am trying to connect to a database which is being served off of my machine (same one I am using to connect to it). It works fine when I do- System.out.println(rs.getString(D.Name) );- and it prints it out in an application. But when I try to output it in an applet, it gives me this error: SQLException: Unable to connect to any hosts due to exception: java.security.AccessControlException: access denied (java.net.SocketPermission 127.0.0.1:3306 connect,resolve) SQLState: 08S01 VendorError: 0 Does anyone know what this means, or how to fix this? Thanks in advance, Nathan E. Pierce -- 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]
using NOW() as a default..
hi... a simple question... i'm creating a table and want to set a column to have the default of the current day/time when the row is created... i've tried... create table foo( dog int, timestamp1 default NOW() ); with no success. i've also tried various iterations.. any ideas/solutions as to what i'm missing... thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
