Re: Can't Show Warnings
For LOAD DATA, it works better in 4.1.1, as indicated here: I read that and immediately went looking on the MySQL site for 4.1.1. All I can find is 4.1.0-alpha. Where can I get 4.1.1? Thanks. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question
SQL Andrey Kotrekhov [EMAIL PROTECTED] wrote: Sorry. I am wrong. Situation is worse. Both tables aren't replicate to the slave. But query try run it on slave :( slave: mysql-4.0.13 master: mysql-4.0.14 What replication options do you use? Full scheme is: | +-A| C | -| | +-B Configuration A: server-id = 4 master-host=XXX master-user=XXX master-password=XXX master-port=3306 replicate-do-db=ZZ master-connect-retry=60 log-slave-updates = Configuration B: server-id = 3 user= mysql master-host = 195.248.191.22 master-user = ghsdr_repl master-password = Dtp6TrH master-port = 3306 replicate-do-db = ZZ replicate-do-table = ZZ.counter replicate-do-table = ZZ.counter_price replicate-do-table = ZZ.reserv_unit replicate-do-table = ZZ.service replicate-do-table = ZZ.ppp_utmp replicate-do-table = ZZ.net replicate-do-table = ZZ.service_net But there are no tables A and B in replicate-do-table list. I have 2 servers. 1-st is master, 2-d - slave. But slave store only some tables of master. For example, master has tables A, B But slave has only A table The problem: query: UPDATE A,B SET A.a=B.a WHERE A.c=B.c; the result: crash replication on slave; -- 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] Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't Show Warnings
Hi Randy, 4.1.1 hasn't been released yet unfortunately. The devs have said that they've started release testing or whatever it's called. I think they said it will hopefully be released in 4-6 weeks. :-) Matt - Original Message - From: Randy Chrismon Sent: Thursday, September 18, 2003 5:39 PM Subject: Re: Can't Show Warnings For LOAD DATA, it works better in 4.1.1, as indicated here: I read that and immediately went looking on the MySQL site for 4.1.1. All I can find is 4.1.0-alpha. Where can I get 4.1.1? Thanks. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Foreign Key Constraint Issue
David, - Original Message - From: David Griffiths [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, September 19, 2003 4:07 AM Subject: InnoDB Foreign Key Constraint Issue The following constraint is failing: ALTER TABLE address_list ADD CONSTRAINT FOREIGN KEY (address_type_id) REFERENCES address_type (address_type_id); ... mysql select distinct address_type_id from address_list; +-+ | address_type_id | +-+ | 100 | | 101 | | 102 | | 104 | | 105 | +-+ And here are all the address_type.address_type_id values: mysql select address_type_id from address_type; +-+ | address_type_id | +-+ | 101 | | 102 | | 103 | | 104 | | 105 | +-+ As you can see, there are no null or 0 address_list.address_type_id, and the address_type_id-values in address_list are the same as the address_type.address_type_id values. as we can see, there is a value 100 in address_list which does not appear in address_type :). ... MySQL thread id 28, query id 1476997 localhost mysql copy to tmp table ALTER TABLE address_list ADD CONSTRAINT FOREIGN KEY (address_type_id) REFERENCES address_type (address_type_id) Foreign key constraint fails for table benchtest/#sql-166d_1c: , CONSTRAINT `0_144` FOREIGN KEY (`ADDRESS_TYPE_ID`) REFERENCES `address_type` (`ADDRESS_TYPE_ID`) Trying to add in child table, in index IF_ADDRESS_LIST_1 tuple: 0: len 4; hex 8064; asc ...d;; 1: len 4; hex 800f4240; asc ..B@;; InnoDB sets the highest bit in positive integers. Above we have a positive integer 0x64 == 100 in decimal. But in parent table benchtest/address_type, in index PRIMARY, the closest match we can find is record: RECORD: info bits 0 0: len 4; hex 8065; asc ...e;; 1: len 6; hex The closest match is 0x65 == 101 in decimal. ac16; asc ..;; 2: len 7; hex 80002d0084; asc -..;; 3: len 8; hex 427573696e657373; asc Business;; Can anyone tell me what the issue might be? According to the manual, everything should work. It doesn't look like a data issue David. P.S. Since many people are waiting for multiple tablespaces, I am posting the status here: I have now got also crash recovery working with multiple tablespaces. I spent this day tracking a memory corruption bug, which turned out to be an unfreed semaphore when I drop a tablespace. There is still a simple bug that all secondary indexes get created in the system tablespace, but that should be easy to fix. ALTER TABLE fails in an error 030919 3:40:22 InnoDB: Error creating file ./test/#sql-15f_3.ibd. 030919 3:40:22 InnoDB: Operating system error number 17 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: Error number 17 means 'File exists'. ALTER TABLE apparently does not work because RENAME TABLE does not work yet with .ibd files, they do not get renamed. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fragmentation problem with MYD/MYI files
In the last episode (Sep 18), Jeremy Zawodny said: On Thu, Sep 18, 2003 at 01:57:17PM +0300, Mikko Noromaa wrote: Is it possible to configure MySQL so that it would allocate a large amount of extra space for the MYD/MYI files, and then use this space as necessary? I don't know of any. If you have a blob field in your table, you can create a record with a 100MB blob, then delete it. Or how about just dd'ing 100MB into the end of an existing MYI or MYD file? Mysql may not care that the filesize is different than what its headers say. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: random access denied
Does anyone think I would have better luck installing MySQL with something other than an RPM?? -Original Message- From: James M Kupernik [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:59 AM To: 'mos' Cc: [EMAIL PROTECTED] Subject: RE: random access denied Thank you, I have checked all of the above. I have set the max connections to 200, but that didn't help any. The connection per hour is not a factor because it doesn't lock out the user, it just fails to authenticate, but as soon as you refresh the page or try to connect via the command line to goes through. Jamie -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:37 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: random access denied At 11:17 AM 9/18/2003, you wrote: It's not a continued access denied, because it can accept connections all day long, but randomly it will just deny one, but the accept it on the next try. I've googled this problem to death, I don't know what else I can do James, Are you sure it's not something simple like exceeding the Max_Connections? (Default is 100) I'm sure you've checked this but there's no harm in asking. For something a little more esoteric, check out the user's Grant properties. It is possible to define Max_Connections_Per_Hour, Max_Updates_Per_Hour or Max_Queries_Per_Hour for the user. So he could be prohibited one hour, but next hour he regains access. Mike -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 5:20 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: random access denied At 03:09 PM 9/17/2003, you wrote: Anything that anyone can suggest that might point me in some direction would be very helpful. I'm just at a complete lose right now ... James, See the Google Group search: http://groups.google.ca/groups?hl=enlr=ie=UTF-8oe=UTF-8safe=offq=m ysql+ %22Access+denied+for+user%22btnG=Google+Search There are quite a few suggestions listed there. Mike -Original Message- From: James M Kupernik [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 16, 2003 3:58 PM To: [EMAIL PROTECTED] Subject: random access denied Hello, I am having a frustrating problem with MySQL. I don't consider myself a newbie, nor am I a master, but either way I can't figure out this problem and I'm hoping someone here has an idea of what is going wrong. Every so often MySQL decides it doesn't want to authenticate a user, doesn't matter on the user/db, etc;. The error message is: 030916 16:10:50 39 Connect Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have a whole log full of these messages. Now and soon as the page is refreshed or you try logging in again (from the command line) it goes through. It's only a temp error. If anyone has any suggestion I'm more than willing to try them. This server was supposed to be in production about a week ago and I can't let it go like this. Thanks very much!! Jamie -- 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] -- 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]
MySQL index implementation on MyISAM tables
Hi, Can someone point to a location where the implementation of indexes on MyISAM tables is explained? I'm trying to understand exactly (not roughly) how the indexes are created and used. Harald
Re: Can't Show Warnings
Randy Chrismon [EMAIL PROTECTED] wrote: For LOAD DATA, it works better in 4.1.1, as indicated here: I read that and immediately went looking on the MySQL site for 4.1.1. All I can find is 4.1.0-alpha. Where can I get 4.1.1? You can install it from development source tree: http://www.mysql.com/doc/en/Installing_source_tree.html or wait when binaries will be available. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
log-warning entries questions
Hi, buh/var/db/mysql# tail buh.XXX.XX 030919 0:31:00 Slave I/O thread exiting, read up to log 'cm-bin.086', position 16233275 030919 0:31:00 Error reading relay log event: slave SQL thread was killed 030919 11:08:46 Slave SQL thread initialized, starting replication in log 'cm-bin.086' at position 16233275, relay log './buh-relay-bin.004' position: 1798782 030919 11:08:46 Slave I/O thread: connected to master '[EMAIL PROTECTED]', replication started in log 'cm-bin.086' at position 16233275 030919 11:08:47 Slave: load data infile at position 16309655 in log 'cm-bin.086' produced 11 warning(s) 030919 11:08:47 Slave: load data infile at position 16318421 in log 'cm-bin.086' produced 4 warning(s) 030919 11:08:47 Slave: load data infile at position 16352824 in log 'cm-bin.086' produced 2 warning(s) 030919 11:08:48 Slave: load data infile at position 16414682 in log 'cm-bin.086' produced 10 warning(s) 030919 11:08:48 Slave: load data infile at position 16429049 in log 'cm-bin.086' produced 6 warning(s) 030919 11:08:48 Slave: load data infile at position 16448729 in log 'cm-bin.086' produced 14 warning(s) -- Query: -- SHOW BINLOG EVENTS IN 'cm-bin.086' FROM 16309655 limit 4 -- 'Pos','Event_type','Orig_log_pos','Info' '16309655','Create_file','16309655','db=hoasc_server;table=tecrdata_43;file_id=916;block_len=8194' '16318084','Exec_load','16318084',';file_id=916' '16318107','Intvar','16318107','INSERT_ID=69780' '16318135','Query','16318135','use hoasc_server; INSERT INTO server_tmessages VALUES (NULL, 'INFO', 'End importing tecrdata', NOW())' Questions: 1. How can I find out what is wrong ? 2. Are there anywhere explained what the 'Event_type' and 'Info' mean ? I didn't found anything in docs. 3. in the master error-log I have: 030919 11:20:54 Error in Log_event::read_log_event(): 'Event too big', data_len=2157824,event_type=63 Tnx, IOnut -- IOnut FreeBSD unregistered ;) user -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Failed connectionattempts
Anders Bruun Olsen [EMAIL PROTECTED] wrote: I have a server which has been running for about a year now. It started with kernel 2.4.19 and mysql 3.x it has since been continually upgraded so that it is now kernel 2.4.20 and mysql 4.0.13. The server is a Compaq server with dual Pentium II (Deschutes) 400MHz 512Kb cache CPUs, 320Mb RAM and SCSI drives. The OS is Gentoo Linux. From the beginning it has behaved strangely - connections to mysql (be that with the mysql textclient or from PHP) fails about 2 out of 3 times in periods. Some times there are no problems, other times it's almost impossible to connect. This behavior has been consistent from the start, so it has been a problem when running both mysql 3.x and 4.0.x. I have monitored the servers load and the loads are the same in the periods when it fails as in the periods when it doesn't. I have tried monitoring the mysql logs, but no entries show up upon the failed connection attempts. No other services malfunctions in this way, or indeed at all on the server. I have run out of ideas on how to debug this problem, can anybody here help me? What error did you receive? -- 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]
[Fwd: Fwd: MySQL segmentation faults...]
Jochen Wiedman asked that I post this stack trace.
Please cc me on any replies, as I'm not subscribed to the list.
The original problem description is at the end.
Cheers, Dave
Original Message
Subject: Re: MySQL segmentation faults...
Date: Thu, 18 Sep 2003 20:59:57 +0200
From: Jochen Wiedmann [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
References: [EMAIL PROTECTED]
[EMAIL PROTECTED] [EMAIL PROTECTED]
Excellent stack trace, post this to the MySQL mailing list. It is a bug
in the C driver.
Jochen
Dave Howorth wrote:
On the mysqlcc list Adam Hooper just wrote:
In general, running strace program will give some useful output;
it'll give developers a good idea of where the problem is. Beyond
that, a backtrace is useful. To produce one:
1. Run gdb program (gdb is available for EVERY distribution)
2. Type 'run'
3. Get a segfault. The window won't close, and gdb will pop up a
warning of sorts.
4. Type 'bt' and email the output here :).
This seems like it might be relevant to diagnosing my
DBD::mysql::db::_login crash, so in the hope it may help ...
The strace output is:
open(/var/lib/mysql/my.cnf, O_RDONLY|O_LARGEFILE) = 3
fstat64(3, {st_mode=S_IFREG|0644, st_size=48, ...}) = 0
old_mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS,
-1, 0) = 0x4001a000
read(3, # This file must exist - CAN-200..., 4096) = 48
read(3, , 4096) = 0
close(3)= 0
munmap(0x4001a000, 4096)= 0
open(/home/dhoworth/.my.cnf, O_RDONLY|O_LARGEFILE) = -1 ENOENT (No
such file or directory)
--- SIGSEGV (Segmentation fault) ---
+++ killed by SIGSEGV +++
The gdb output is:
Program received signal SIGSEGV, Segmentation fault.
0x401cc030 in strcend () from /usr/lib/libmysqlclient.so.10
(gdb) bt
#0 0x401cc030 in strcend () from /usr/lib/libmysqlclient.so.10
#1 0x401bd922 in simple_command () from /usr/lib/libmysqlclient.so.10
#2 0x401be31d in mysql_real_connect () from
/usr/lib/libmysqlclient.so.10
#3 0x4019ebd4 in mysql_dr_connect ()
from /usr/lib/perl5/auto/DBD/mysql/mysql.so
#4 0x4019ef40 in mysql_dr_connect ()
from /usr/lib/perl5/auto/DBD/mysql/mysql.so
#5 0x4019efb3 in mysql_db_login () from
/usr/lib/perl5/auto/DBD/mysql/mysql.so
#6 0x401a3114 in XS_DBD__mysql__db__login ()
from /usr/lib/perl5/auto/DBD/mysql/mysql.so
#7 0x0809c70c in Perl_pp_entersub ()
#8 0x08097090 in Perl_runops_standard ()
#9 0x0805cdca in perl_call_sv ()
#10 0x0805c981 in perl_call_sv ()
#11 0x401910d1 in XS_DBI_dispatch () from /usr/lib/perl5/auto/DBI/DBI.so
#12 0x0809c70c in Perl_pp_entersub ()
#13 0x08097090 in Perl_runops_standard ()
#14 0x0805c565 in perl_run ()
#15 0x0805c2cb in perl_run ()
#16 0x08059ca0 in main ()
#17 0x4005b0bf in __libc_start_main () from /lib/libc.so.6
HTH, Dave
==
Dave Howorth wrote:
I'm trying to use LOAD DATA and it is returning Warnings: 12. I can't
use SHOW WARNINGS because I'm using mysql 3.23.49 since I'm on
Debian/Woody. A search of mysql list archives showed up a Perl script
called load_diag.pl by Paul Dubois that diagnoses problems in data files
so I've downloaded that.
Sadly, it is crashing. When I run it like this:
~/bin/load_diag.pl -u dhoworth attributes attributes.txt
it reports Segmentation fault. When I run it in the debugger it
reports Aborted. By single-stepping, I've tracked the failure to this
call:
DBD::mysql::dr::connect(/usr/lib/perl5/DBD/mysql.pm:131):
131: DBD::mysql::db::_login($this, $dsn, $username, $password)
132: or $this = undef;
It aborts when I try to single-step into it.
perl is v5.6.1, examining the modules shows
# $Id: DBI.pm,v 11.7 2002/02/07 03:00:53 timbo Exp $
$DBI::VERSION = 1.21; # == ALSO update the version in the pod text
below!
# $Id: dbd.pm.in,v 1.6 1999/10/21 20:05:43 joe Exp $
$VERSION = '2.0416';
I've written a small Perl program to test my installation (see below)
and that works, as does the mysql client and mysqlcc, and I'm not sure
where to look next.
All suggestions welcome.
Thanks, Dave
===
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dsn = 'dbi:mysql:t1';
my $user = 'dhoworth';
my $password = '';
my $dbh = DBI-connect($dsn, $user, $password,
{ RaiseError = 1 });
my $sth = $dbh-prepare('SELECT * FROM regions');
$sth-execute();
while (my @row = $sth-fetchrow_array)
{
print @row\n;
}
--
Dave Howorth
MRC Centre for Protein Engineering
Hills Road, Cambridge, CB2 2QH
01223 252960
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: MySQL segmentation faults...
Jochen Wiedman asked that I post this stack trace.
Please cc me on any replies, as I'm not subscribed to the list.
The original problem description is at the end.
Cheers, Dave
Original Message
Subject: Re: MySQL segmentation faults...
Date: Thu, 18 Sep 2003 20:59:57 +0200
From: Jochen Wiedmann [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
References: [EMAIL PROTECTED]
[EMAIL PROTECTED] [EMAIL PROTECTED]
Excellent stack trace, post this to the MySQL mailing list. It is a bug
in the C driver.
Jochen
Dave Howorth wrote:
On the mysqlcc list Adam Hooper just wrote:
In general, running strace program will give some useful output;
it'll give developers a good idea of where the problem is. Beyond
that, a backtrace is useful. To produce one:
1. Run gdb program (gdb is available for EVERY distribution)
2. Type 'run'
3. Get a segfault. The window won't close, and gdb will pop up a
warning of sorts.
4. Type 'bt' and email the output here :).
This seems like it might be relevant to diagnosing my
DBD::mysql::db::_login crash, so in the hope it may help ...
The strace output is:
open(/var/lib/mysql/my.cnf, O_RDONLY|O_LARGEFILE) = 3
fstat64(3, {st_mode=S_IFREG|0644, st_size=48, ...}) = 0
old_mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS,
-1, 0) = 0x4001a000
read(3, # This file must exist - CAN-200..., 4096) = 48
read(3, , 4096) = 0
close(3)= 0
munmap(0x4001a000, 4096)= 0
open(/home/dhoworth/.my.cnf, O_RDONLY|O_LARGEFILE) = -1 ENOENT (No
such file or directory)
--- SIGSEGV (Segmentation fault) ---
+++ killed by SIGSEGV +++
The gdb output is:
Program received signal SIGSEGV, Segmentation fault.
0x401cc030 in strcend () from /usr/lib/libmysqlclient.so.10
(gdb) bt
#0 0x401cc030 in strcend () from /usr/lib/libmysqlclient.so.10
#1 0x401bd922 in simple_command () from /usr/lib/libmysqlclient.so.10
#2 0x401be31d in mysql_real_connect () from
/usr/lib/libmysqlclient.so.10
#3 0x4019ebd4 in mysql_dr_connect ()
from /usr/lib/perl5/auto/DBD/mysql/mysql.so
#4 0x4019ef40 in mysql_dr_connect ()
from /usr/lib/perl5/auto/DBD/mysql/mysql.so
#5 0x4019efb3 in mysql_db_login () from
/usr/lib/perl5/auto/DBD/mysql/mysql.so
#6 0x401a3114 in XS_DBD__mysql__db__login ()
from /usr/lib/perl5/auto/DBD/mysql/mysql.so
#7 0x0809c70c in Perl_pp_entersub ()
#8 0x08097090 in Perl_runops_standard ()
#9 0x0805cdca in perl_call_sv ()
#10 0x0805c981 in perl_call_sv ()
#11 0x401910d1 in XS_DBI_dispatch () from /usr/lib/perl5/auto/DBI/DBI.so
#12 0x0809c70c in Perl_pp_entersub ()
#13 0x08097090 in Perl_runops_standard ()
#14 0x0805c565 in perl_run ()
#15 0x0805c2cb in perl_run ()
#16 0x08059ca0 in main ()
#17 0x4005b0bf in __libc_start_main () from /lib/libc.so.6
HTH, Dave
==
Dave Howorth wrote:
I'm trying to use LOAD DATA and it is returning Warnings: 12. I can't
use SHOW WARNINGS because I'm using mysql 3.23.49 since I'm on
Debian/Woody. A search of mysql list archives showed up a Perl script
called load_diag.pl by Paul Dubois that diagnoses problems in data files
so I've downloaded that.
Sadly, it is crashing. When I run it like this:
~/bin/load_diag.pl -u dhoworth attributes attributes.txt
it reports Segmentation fault. When I run it in the debugger it
reports Aborted. By single-stepping, I've tracked the failure to this
call:
DBD::mysql::dr::connect(/usr/lib/perl5/DBD/mysql.pm:131):
131: DBD::mysql::db::_login($this, $dsn, $username, $password)
132: or $this = undef;
It aborts when I try to single-step into it.
perl is v5.6.1, examining the modules shows
# $Id: DBI.pm,v 11.7 2002/02/07 03:00:53 timbo Exp $
$DBI::VERSION = 1.21; # == ALSO update the version in the pod text
below!
# $Id: dbd.pm.in,v 1.6 1999/10/21 20:05:43 joe Exp $
$VERSION = '2.0416';
I've written a small Perl program to test my installation (see below)
and that works, as does the mysql client and mysqlcc, and I'm not sure
where to look next.
All suggestions welcome.
Thanks, Dave
===
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dsn = 'dbi:mysql:t1';
my $user = 'dhoworth';
my $password = '';
my $dbh = DBI-connect($dsn, $user, $password,
{ RaiseError = 1 });
my $sth = $dbh-prepare('SELECT * FROM regions');
$sth-execute();
while (my @row = $sth-fetchrow_array)
{
print @row\n;
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA
hi load data from master doesnt work on mysql 3.23.52 version is there any alternative?
load data
hi load data from master doesnt work on mysql 3.23.52 version how can i solve this? thanks
Re: innodb and fragmentation
Hello Heikki, Heikki Tuuri [EMAIL PROTECTED] writes: I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! It did the job just fine. Thanks. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT fails - return primary key?
Hi, Say I have a table with 2 columns. The first is the primary key (int(11), unique, auto-increment). The second column is a varchar(20) (also unique and indexed). This is part of a search engine. Whenever a new document is indexed then for each word I have to do 2 queries: look up in the table if the word exists, if yes - use key, if no - insert and use key. I want to be able to combine these actions into one INSERT query which returns the primary key whenever a word already exists, or insert the word and then returns the (newly created) primary key. Anyone any idea if this is possible? If yes, how this is possible? Cheers, Harald
Re: Failed connectionattempts
On Fri, Sep 19, 2003 at 11:47:38AM +0300, Egor Egorov wrote: I have a server which has been running for about a year now. It started with kernel 2.4.19 and mysql 3.x it has since been continually upgraded so that it is now kernel 2.4.20 and mysql 4.0.13. The server is a Compaq server with dual Pentium II (Deschutes) 400MHz 512Kb cache CPUs, 320Mb RAM and SCSI drives. The OS is Gentoo Linux. From the beginning it has behaved strangely - connections to mysql (be that with the mysql textclient or from PHP) fails about 2 out of 3 times in periods. Some times there are no problems, other times it's almost impossible to connect. This behavior has been consistent from the start, so it has been a problem when running both mysql 3.x and 4.0.x. I have monitored the servers load and the loads are the same in the periods when it fails as in the periods when it doesn't. I have tried monitoring the mysql logs, but no entries show up upon the failed connection attempts. No other services malfunctions in this way, or indeed at all on the server. I have run out of ideas on how to debug this problem, can anybody here help me? What error did you receive? In PHP it just says connection failed. I can't remember the exact message the textclient gave, and it is in a period of not failing right now (typical!) so I can't get the message right now. I think it gave a message about connection aborted. As soon as it does it again I'll write the correct message to the list. -- Anders -BEGIN GEEK CODE BLOCK- Version: 3.12 GCS/O d--@ s:+ a-- C++ UL+++$ P++ L+++ E- W+ N(+) o K? w O-- M- V PS+ PE@ Y+ PGP+ t 5 X R+ tv+ b++ DI+++ D+ G e- h !r y? --END GEEK CODE BLOCK-- PGPKey: http://pgp.mit.edu:11371/pks/lookup?op=getsearch=0x8BFECB41 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext and Soundex
Hi All, Has anyone had any experience of using soundex with fulltext searching? I have looked through the archives and cannot find anything.. I want to avoid building my own idicies for soundex. Thanks for any help Andrew Sql,query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
apology for double post
Please accept my apologies for the double-post earlier. The system sent me a 'self-moderation' challenge requiring me to click on a link. When I did so, the page said there was no outstanding challenge and that I should send the message again. Sorry, Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
XMLType, queries to XML
I would like to do queries to a field TEXT that contains an XML text. If for example I have an entry with the text: titleMyTitle/title When i do the query to search words containing the word title I would like to obtain MyTitle and not title /title because these are tags of the XMLType I think this is similar to XMLType in Oracle. Someone knows how can i do it, if there is some software or utility. Thanks: Enrique Excuseme by my english ___ Yahoo! Messenger - Nueva versión GRATIS Super Webcam, voz, caritas animadas, y más... http://messenger.yahoo.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
test
is it work? --- Gustavo Polillo Correa - Analista de Sistemas Instituto de Biociencias - USP Sao Paulo - Brasil Tel/fax : 55-11-3091-7436 --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with LIMIT
Hi, I have a question for the LIMIT clause. I have two table with relation 1:n and a query like this : SELECT tableA.id, tableB.name FROM tableA INNER JOIN tableB ON tableA.id=tableB.id WHERE something LIMIT x, 20 I want to restrict the result to 20 row from tableA and n row from tableB and not 20 row total. It's possible to restrict the above query without have to select 20 id from tableA and query again the DB to retrieve other n records? Daniel
C mysql functino problem
Hello,
I tried to code a (very) small program under windows, this is my first with
C mysql functions :
#include stdio.h
#include mysql.h
int main(void) {
MYSQL *mysrv = NULL;
mysrv = mysql_init(mystruct);
if ( mysrv == NULL )
{
perror(Struct initialize failed\n);
exit (-1);
}
mysql_close(mystruct);
return (0);
}
I compiled this with Dev-C++ v 4.9.8.0 after being configure the header
files path.
It give me a lot of errors, and i don't know why. Any help would be very
great!
Compiler: Default compiler
Executing gcc.exe...
gcc.exe U:\console\toto.c -o U:\console\toto.exe
-IC:\Dev-Cpp\include -Ic:\mysql\include -LC:\Dev-Cpp\lib
In file included from c:/mysql/include/mysql.h:57,
from U:/console/toto.c:2:
c:/mysql/include/mysql_com.h:116: parse error before SOCKET
c:/mysql/include/mysql_com.h:116: warning: no semicolon at end of struct or
union
c:/mysql/include/mysql_com.h:135: parse error before '}' token
c:/mysql/include/mysql_com.h:135: warning: data definition has no type or
storage class
c:/mysql/include/mysql_com.h:167: parse error before '*' token
[...]
c:/mysql/include/mysql_com.h:180: parse error before s
In file included from U:/mep/Dev/console/toto.c:2:
c:/mysql/include/mysql.h:165: parse error before NET
c:/mysql/include/mysql.h:165: warning: no semicolon at end of struct or
union
c:/mysql/include/mysql.h:202: parse error before '}' token
c:/mysql/include/mysql.h:202: warning: data definition has no type or
storage class
c:/mysql/include/mysql.h:211: parse error before MYSQL
c:/mysql/include/mysql.h:211: warning: no semicolon at end of struct or
union
c:/mysql/include/mysql.h:217: parse error before '}' token
c:/mysql/include/mysql.h:217: warning: data definition has no type or
storage class
c:/mysql/include/mysql.h:232: parse error before NET
c:/mysql/include/mysql.h:232: warning: no semicolon at end of struct or
union
c:/mysql/include/mysql.h:238: conflicting types for `last_errno'
c:/mysql/include/mysql_com.h:118: previous declaration of `last_errno'
c:/mysql/include/mysql.h:241: conflicting types for `last_error'
c:/mysql/include/mysql_com.h:121: previous declaration of `last_error'
c:/mysql/include/mysql.h:242: parse error before '}' token
c:/mysql/include/mysql.h:242: warning: data definition has no type or
storage class
c:/mysql/include/mysql.h:266: parse error before '*' token
[...]
U:/console/toto.c: In function `main':
U:/console/toto.c:7: `mysrv' undeclared (first use in this function)
U:/console/toto.c:7: (Each undeclared identifier is reported only once
U:/console/toto.c:7: for each function it appears in.)
Execution terminated
--
Vincent
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Foreign Key Constraint Issue
I can't believe I missed that. The insert statement is in the script - not sure why it wasn't added - not sure why I missed something so obvious. David. - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:50 PM Subject: Re: InnoDB Foreign Key Constraint Issue David, - Original Message - From: David Griffiths [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, September 19, 2003 4:07 AM Subject: InnoDB Foreign Key Constraint Issue The following constraint is failing: ALTER TABLE address_list ADD CONSTRAINT FOREIGN KEY (address_type_id) REFERENCES address_type (address_type_id); ... mysql select distinct address_type_id from address_list; +-+ | address_type_id | +-+ | 100 | | 101 | | 102 | | 104 | | 105 | +-+ And here are all the address_type.address_type_id values: mysql select address_type_id from address_type; +-+ | address_type_id | +-+ | 101 | | 102 | | 103 | | 104 | | 105 | +-+ As you can see, there are no null or 0 address_list.address_type_id, and the address_type_id-values in address_list are the same as the address_type.address_type_id values. as we can see, there is a value 100 in address_list which does not appear in address_type :). ... MySQL thread id 28, query id 1476997 localhost mysql copy to tmp table ALTER TABLE address_list ADD CONSTRAINT FOREIGN KEY (address_type_id) REFERENCES address_type (address_type_id) Foreign key constraint fails for table benchtest/#sql-166d_1c: , CONSTRAINT `0_144` FOREIGN KEY (`ADDRESS_TYPE_ID`) REFERENCES `address_type` (`ADDRESS_TYPE_ID`) Trying to add in child table, in index IF_ADDRESS_LIST_1 tuple: 0: len 4; hex 8064; asc ...d;; 1: len 4; hex 800f4240; asc ..B@;; InnoDB sets the highest bit in positive integers. Above we have a positive integer 0x64 == 100 in decimal. But in parent table benchtest/address_type, in index PRIMARY, the closest match we can find is record: RECORD: info bits 0 0: len 4; hex 8065; asc ...e;; 1: len 6; hex The closest match is 0x65 == 101 in decimal. ac16; asc ..;; 2: len 7; hex 80002d0084; asc -..;; 3: len 8; hex 427573696e657373; asc Business;; Can anyone tell me what the issue might be? According to the manual, everything should work. It doesn't look like a data issue David. P.S. Since many people are waiting for multiple tablespaces, I am posting the status here: I have now got also crash recovery working with multiple tablespaces. I spent this day tracking a memory corruption bug, which turned out to be an unfreed semaphore when I drop a tablespace. There is still a simple bug that all secondary indexes get created in the system tablespace, but that should be easy to fix. ALTER TABLE fails in an error 030919 3:40:22 InnoDB: Error creating file ./test/#sql-15f_3.ibd. 030919 3:40:22 InnoDB: Operating system error number 17 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: Error number 17 means 'File exists'. ALTER TABLE apparently does not work because RENAME TABLE does not work yet with .ibd files, they do not get renamed. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- 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: Re: innodb and fragmentation
Per Andreas Buer [EMAIL PROTECTED] wrote: Hello Heikki, Heikki Tuuri writes: I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! It did the job just fine. Thanks. and it seems to be what I asked for as well, thanks! Franky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT search oddity
I have identified a strange case that seems to give false matches when
performing a FULLTEXT IN BOOLEAN search.
Please consider the following:
---
CREATE TABLE `fttest` (
`id` int(10) unsigned NOT NULL auto_increment,
`text` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `text_index` (`text`)
) TYPE=MyISAM;
INSERT INTO `fttest` (`id`, `text`) VALUES(1, overandover andover);
---
SELECT *
FROM fttest
WHERE MATCH (text) AGAINST ('overandover andover' IN BOOLEAN MODE)
Matches - OK as expected
---
SELECT *
FROM fttest
WHERE MATCH (text) AGAINST ('verandover andover' IN BOOLEAN MODE)
Does not match - OK as expected
---
SELECT *
FROM fttest
WHERE MATCH (text) AGAINST ('andover andover' IN BOOLEAN MODE)
Does match - I would expect this NOT to. Am I missing something or is this
erroneous?
Thanks
Dave
_
Tired of 56k? Get a FREE BT Broadband connection
http://www.msn.co.uk/specials/btbroadband
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data
Aris Santillan [EMAIL PROTECTED] wrote: load data from master doesnt work on mysql 3.23.52 version how can i solve this? You can use mysqldump program or archive master's data dir. Here you can find more info: http://www.mysql.com/doc/en/Replication_HOWTO.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
Doubles and selects
All - We're using MySQL under Linux to capture and analyze network traffic. We have two tables that store all the messages sent, and, another table that stores all the messages received. The sent table has an transmit column (as a double) and the received table has a received time sent time (from the incoming packet) both stored as doubles (seconds and fractions of seconds). In addition, there are a few other fields that distinctly define each message (e.g., source IP, source port, etc.). We're trying to perform some analysis on the data and seem to be having trouble matching columns of type double. For example, we loop through all of the received messages and try to find the corresponding message in the transmit table based on the transmit time (which appears on both tables). However, when the double transmit time is part of the query, we can't seem to find matches. Wrapping the transmit time in the select statement with ROUND() finds the matching records. Is it possible that some rounding is taking place here? We've considered storing time as two separate INT columns (like the timeval struct), however, we'd prefer to have 1 time column. Any suggestions? Thanks, Mark Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED]
support fulltext search on innodb
Hi everybody! This is for Heikki Tuuri: Do you intend to add fulltext search in innodb tables? If yes, can you tell me a deadline? Tanks. Fernando Bernardino. __ Acabe com aquelas janelinhas que pulam na sua tela. AntiPop-up UOL - É grátis! http://antipopup.uol.com.br/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug in replication on HP-UX 64 bit binaries?
We have two 4.0.15 mysql servers set up as master and slave for each other on HP-UX PA-RISC and Itanium. A-B When using the HP-UX PA-RISC 2.0, 64-bit only release, we have problems with replication. Even though it should have (and SHOW SLAVE STATUS reports) the other server as master, the indexes reported in SHOW SLAVE STATUS is the same as the ones in SHOW MASTER STATUS on the same server. No errors are reported in the error files. The same problem exists using the HP-UX 11.22 (IA64, 64bit) release on Itanium. When the HP-UX 11.11 (PA-RISC 1.1 and 2.0) release is used, with the same configuration, on the same servers, everything works perfectly. We can not use this workaround on Itanium though, since there only exists one binary (64 bit) for Itanium. Values extracted on the hosts: A: bash-2.04# netstat -n -i Name Mtu Network Address Ipkts Ierrs Opkts Oerrs Coll lan0 1500 192.168.1.0 192.168.1.163 2922857 0 2532087 0 0 my.cnf: [mysqld] server-id=1 log-bin=/usr/local/mysql/data/query-bin master-port=3306 master-host=192.168.1.164 master-user=repluser master-password=repluser master-connect-retry=30 master.info: query-bin.001 1499005 192.168.1.164 repluser repluser 3306 30 SHOW SLAVE STATUS: | 192.168.1.164 | repluser | 3306| 30| query-bin.001 | 1499339 | A-relay-bin.001 | 1499379 | | Yes | Yes | | | 0 || 0| 1499339 | 1499379 | SHOW MASTER STATUS: | query-bin.001 | 1499339 | | | B: bash-2.04# netstat -n -i Name Mtu Network Address Ipkts Ierrs Opkts Oerrs Coll lan0 1500 192.168.1.0 192.168.1.164 2388705 0 2092210 0 0 lo0 4136 127.0.0.0 127.0.0.1 26767 0 26767 0 0 my.cnf: server-id=2 log-bin=/usr/local/mysql/data/query-bin master-port=3306 master-host=192.168.1.163 master-user=repluser master-password=repluser master-connect-retry=30 master.info: query-bin.001 681 192.168.1.163 repluser repluser 3306 30 SHOW SLAVE STATUS: | 192.168.1.163 | repluser | 3306| 30| query-bin.001 | 681 | B-relay-bin.001 | 1801 | | Yes | Yes | | | 0 || 0| 681 | 1801 | SHOW MASTER STATUS: | query-bin.001 | 681 | | | Running a simple master - slave replication (log-bin on A and master-host and so on on B), the slave on B will fail with the following reason, even if server A is shut down: 030919 16:11:37 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 030919 16:11:37 Error reading packet from server: Binary log is not open (server_errno=1236) 030919 16:11:37 Got fatal error 1236: 'Binary log is not open' from master when reading data from binary log There shouldn't be anything wrong with the configuration, since the same configuration works with the other binary. Has anyone else had the same problem? Is it a bug? Regards, Lars-Göran Forsberg lars-göran forsberg software engineer incomit ab p.o. box 83 se-651 03 karlstad sweden phone +46 54 17 67 36 fax + 46 54 17 67 99 [EMAIL PROTECTED] www.incomit.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character set problem Linux - Windows
From a quick search of the online docs, it seems this is what you need: http://www.mysql.com/doc/en/Charset-CONVERT.html but I think it is only in 4.1.0 alpha. Feel free to correct me, I'm no guru here. Adam On 09/18/2003 12:06 PM Marcin Giedz wrote: - Original Message - From: Adam Hardy [EMAIL PROTECTED] To: Marcin Giedz [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:34 AM Subject: Re: Character set problem Linux - Windows Hi Marcin, have you tried using unicode? Not yet Adam but though about it!!! If I change to unicode how to change existing strings with Polish letters in mysql tables?? Marcin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and mysqldump issues
It looks like my first issue with slave replication is a verified bug #1345. I believe the second one is also because -Q does not quote db names so I opened a new bug #1348. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Do I use Except?
Hi, I'm building an online fantasy sports game. I want to present a list of players available to purchase. HOwever I need to filter out the players the user already has. I have a table which includes all of the players' information - name, position, price, etc I have a table which includes all of my transactions - managerid, playerid, dateofpurchase, dateofsale I need to select all players in the players table except those that occur in the transactions table which match the 'managerid'. I'm stuck! Any help would be greatfully received! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: load data
Copy the db from the master to the slave. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Aris Santillan [mailto:[EMAIL PROTECTED] --Sent: Friday, September 19, 2003 3:10 AM --To: [EMAIL PROTECTED] --Subject: load data -- --hi -- -- --load data from master doesnt work on mysql 3.23.52 version -- -- --how can i solve this? -- --thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search oddity
This doesn't surprise me. I haven't looked at the code, but I'd bet
that double-quoted, exact phrase matches are handled the usual way by
mysql: First, do a regular fulltext search (using the index) to find
rows with your search words, then check the found rows to see if they
exactly contain the double-quoted string. Your result seems to show
that no additional constraints are placed on the second step, or at
least that the beginning of the phrase doesn't have to be a word boundary.
Hence, verandover andover does not match overandover andover because
it doesn't conatain the word verandover. It would have passed the
second step, as it does contain the quoted string.
On the other hand, andover andover does match overandover andover
because the fulltext search is looking for andover, which it finds,
and the exact phrase andover andover can be found in the row.
If I'm right, I'd expect andover and to match, but andover ando
would not.
Whether it should work this way is a philosophical matter, I suppose.
Michael
David Beavan wrote:
I have identified a strange case that seems to give false matches when
performing a FULLTEXT IN BOOLEAN search.
Please consider the following:
---
CREATE TABLE `fttest` (
`id` int(10) unsigned NOT NULL auto_increment,
`text` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `text_index` (`text`)
) TYPE=MyISAM;
INSERT INTO `fttest` (`id`, `text`) VALUES(1, overandover andover);
---
SELECT *
FROM fttest
WHERE MATCH (text) AGAINST ('overandover andover' IN BOOLEAN MODE)
Matches - OK as expected
---
SELECT *
FROM fttest
WHERE MATCH (text) AGAINST ('verandover andover' IN BOOLEAN MODE)
Does not match - OK as expected
---
SELECT *
FROM fttest
WHERE MATCH (text) AGAINST ('andover andover' IN BOOLEAN MODE)
Does match - I would expect this NOT to. Am I missing something or is
this erroneous?
Thanks
Dave
_
Tired of 56k? Get a FREE BT Broadband connection
http://www.msn.co.uk/specials/btbroadband
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Do I use Except?
Look up Left JOIN. This join will solve your problem. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Matt MacLeod [mailto:[EMAIL PROTECTED] --Sent: Friday, September 19, 2003 8:38 AM --To: [EMAIL PROTECTED] --Subject: Do I use Except? -- --Hi, -- --I'm building an online fantasy sports game. I want to present a list of --players available to purchase. HOwever I need to filter out the players --the user already has. -- --I have a table which includes all of the players' information - name, --position, price, etc --I have a table which includes all of my transactions - managerid, --playerid, dateofpurchase, dateofsale -- --I need to select all players in the players table except those that --occur in the transactions table which match the 'managerid'. -- --I'm stuck! Any help would be greatfully received! -- --Matt -- -- -- --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: Do I use Except?
Matt, On most platforms, you would generally do a sub select of the form select playerid from players p where not exists ( select * from myplayers m where m.player_id = p.player_id ) However, as sub selects are only supported in mysql 4.1, you'll need to see section 1.7.4.1 Sub queries in the manual on how to change this into a join supported in mysql prior to 4.1 Andy -Original Message- From: Matt MacLeod [mailto:[EMAIL PROTECTED] Sent: 19 September 2003 16:38 To: [EMAIL PROTECTED] Subject: Do I use Except? Hi, I'm building an online fantasy sports game. I want to present a list of players available to purchase. HOwever I need to filter out the players the user already has. I have a table which includes all of the players' information - name, position, price, etc I have a table which includes all of my transactions - managerid, playerid, dateofpurchase, dateofsale I need to select all players in the players table except those that occur in the transactions table which match the 'managerid'. I'm stuck! Any help would be greatfully received! Matt -- 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: Help with LIMIT
At 3:08 PM +0200 9/19/03, Daniele Tagliavini wrote: Hi, I have a question for the LIMIT clause. I have two table with relation 1:n and a query like this : SELECT tableA.id, tableB.name FROM tableA INNER JOIN tableB ON tableA.id=tableB.id WHERE something LIMIT x, 20 I want to restrict the result to 20 row from tableA and n row from tableB and not 20 row total. It's possible to restrict the above query without have to select 20 id from tableA and query again the DB to retrieve other n records? No. LIMIT is not handled that way. You could select with LIMIT from one table into a temporarary table, then join that with your second table and apply the second limit. Daniel -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C mysql functino problem
At 3:09 PM +0200 9/19/03, [EMAIL PROTECTED] wrote:
Hello,
I tried to code a (very) small program under windows, this is my first with
C mysql functions :
#include stdio.h
#include mysql.h
Try including my_global.h before mysql.h and see if that helps.
int main(void) {
MYSQL *mysrv = NULL;
mysrv = mysql_init(mystruct);
if ( mysrv == NULL )
{
perror(Struct initialize failed\n);
exit (-1);
}
mysql_close(mystruct);
return (0);
}
I compiled this with Dev-C++ v 4.9.8.0 after being configure the header
files path.
It give me a lot of errors, and i don't know why. Any help would be very
great!
Compiler: Default compiler
Executing gcc.exe...
gcc.exe U:\console\toto.c -o U:\console\toto.exe
-IC:\Dev-Cpp\include -Ic:\mysql\include -LC:\Dev-Cpp\lib
In file included from c:/mysql/include/mysql.h:57,
from U:/console/toto.c:2:
c:/mysql/include/mysql_com.h:116: parse error before SOCKET
c:/mysql/include/mysql_com.h:116: warning: no semicolon at end of struct or
union
c:/mysql/include/mysql_com.h:135: parse error before '}' token
c:/mysql/include/mysql_com.h:135: warning: data definition has no type or
storage class
c:/mysql/include/mysql_com.h:167: parse error before '*' token
[...]
c:/mysql/include/mysql_com.h:180: parse error before s
In file included from U:/mep/Dev/console/toto.c:2:
c:/mysql/include/mysql.h:165: parse error before NET
c:/mysql/include/mysql.h:165: warning: no semicolon at end of struct or
union
c:/mysql/include/mysql.h:202: parse error before '}' token
c:/mysql/include/mysql.h:202: warning: data definition has no type or
storage class
c:/mysql/include/mysql.h:211: parse error before MYSQL
c:/mysql/include/mysql.h:211: warning: no semicolon at end of struct or
union
c:/mysql/include/mysql.h:217: parse error before '}' token
c:/mysql/include/mysql.h:217: warning: data definition has no type or
storage class
c:/mysql/include/mysql.h:232: parse error before NET
c:/mysql/include/mysql.h:232: warning: no semicolon at end of struct or
union
c:/mysql/include/mysql.h:238: conflicting types for `last_errno'
c:/mysql/include/mysql_com.h:118: previous declaration of `last_errno'
c:/mysql/include/mysql.h:241: conflicting types for `last_error'
c:/mysql/include/mysql_com.h:121: previous declaration of `last_error'
c:/mysql/include/mysql.h:242: parse error before '}' token
c:/mysql/include/mysql.h:242: warning: data definition has no type or
storage class
c:/mysql/include/mysql.h:266: parse error before '*' token
[...]
U:/console/toto.c: In function `main':
U:/console/toto.c:7: `mysrv' undeclared (first use in this function)
U:/console/toto.c:7: (Each undeclared identifier is reported only once
U:/console/toto.c:7: for each function it appears in.)
Execution terminated
--
Vincent
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doubles and selects
* Mark Riehl All - We're using MySQL under Linux to capture and analyze network traffic. We have two tables that store all the messages sent, and, another table that stores all the messages received. The sent table has an transmit column (as a double) and the received table has a received time sent time (from the incoming packet) both stored as doubles (seconds and fractions of seconds). In addition, there are a few other fields that distinctly define each message (e.g., source IP, source port, etc.). We're trying to perform some analysis on the data and seem to be having trouble matching columns of type double. For example, we loop through all of the received messages and try to find the corresponding message in the transmit table based on the transmit time (which appears on both tables). However, when the double transmit time is part of the query, we can't seem to find matches. Wrapping the transmit time in the select statement with ROUND() finds the matching records. Is it possible that some rounding is taking place here? We've considered storing time as two separate INT columns (like the timeval struct), however, we'd prefer to have 1 time column. Any suggestions? Using approximate numbers is tricky, I usually avoid it, and would probably have used a separate column for the fractions in this case, maybe a tinyint, if two digits for the fractions is sufficient. Take a look at this: URL: http://www.mysql.com/doc/en/Problems_with_float.html At the end of the page there is an example using ABS(), I guess this is a relatively easy way for you to fix your problem, if you decide to keep your DOUBLE column. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf and binary distributions
is there any way to get mysqld 4.0.15a from the linux binary distribution to use another path for my.cnf apart from /etc/my.cnf /usr/local/mysql/data/my.cnf ~/.my.cnf the problem is at my company i am not allowed to install things as root, so i can't put files in /etc or /usr/local/mysql/data. the problem with the home directory option is the mysql user is a role account with no home directory. also the server is usually started via sudo which doesn't necessarily set the home directory properly. i could write a shell script that sets $HOME and calls mysqld but then that's another thing to maintain. the company likes to build their own rpm's of mysql but their track record hasn't been so good lately (the 4.0.14 rpm crashed and burned instantly.) i've got an app which is suffering from random table corruption, which is supposedly fixed in 4.0.15a. i'd like to stick with the official binaries (that seems like a really good idea to me in general anyway) but this my.cnf issue is kind of a drag. suggestions? -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there a way to find out if a table exists?
I am trying to make my PHP script autodetect when a table in a mySQL database exists, and when it doesn't, create it. Is there some way to do something like: SELECT * FROM tables WHERE name = table_name; And get a result I could test for truth, and thus run my script? Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf and binary distributions
At 10:42 AM -0700 9/19/03, Jon Drukman wrote: is there any way to get mysqld 4.0.15a from the linux binary distribution to use another path for my.cnf apart from /etc/my.cnf /usr/local/mysql/data/my.cnf ~/.my.cnf No, but if you have control over how the server gets started, you can invoke it with a --defaults-file option to tell it to ignore the standard option files and specify a file of your own choosing. the problem is at my company i am not allowed to install things as root, so i can't put files in /etc or /usr/local/mysql/data. the problem with the home directory option is the mysql user is a role account with no home directory. also the server is usually started via sudo which doesn't necessarily set the home directory properly. i could write a shell script that sets $HOME and calls mysqld but then that's another thing to maintain. the company likes to build their own rpm's of mysql but their track record hasn't been so good lately (the 4.0.14 rpm crashed and burned instantly.) i've got an app which is suffering from random table corruption, which is supposedly fixed in 4.0.15a. i'd like to stick with the official binaries (that seems like a really good idea to me in general anyway) but this my.cnf issue is kind of a drag. suggestions? -jsd- -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there a way to find out if a table exists?
* Dan Anderson I am trying to make my PHP script autodetect when a table in a mySQL database exists, and when it doesn't, create it. Is there some way to do something like: SELECT * FROM tables WHERE name = table_name; And get a result I could test for truth, and thus run my script? SHOW TABLES LIKE table\_name; Note that the underscore must be escaped with a backslash, because the LIKE operator use underscore as a wildcard for a single character. In other words, this statement: SHOW TABLES LIKE table_name; ... would match table_name, but also tableAname and tableBname. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RPM Upgrade from 3.23.52 to 4.0.15 :: all my database show up as empty.
Sorry for the double post. I sent this yesterday without any replies, so I'm wondering if it made it out there? -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:29 AM To: [EMAIL PROTECTED] Subject: Upgrade from 3.23.52 to 4.0.15 all my database show up as empty. Importance: High After the rpm upgrade, all my databases show up (ie. In phpMyAdmin for example), but they all show (-) for the tables -- in otherwords, they're aren't any tables. /var/lib/mysql/ shows all the databases and there appears to be data and files in the directories. Reverting back to the 3.23 version they all work. No errors shown in the .err file either... [EMAIL PROTECTED] mysql]# cat daevid.err 030918 11:29:50 mysqld started 030918 11:29:50 InnoDB: Started /usr/sbin/mysqld: ready for connections. Version: '4.0.15-standard' socket: '/tmp/mysql.sock' port: 3306 I have a RedHat 8 system that's run solid for years. I was running the 3.23.52 RPMS and recently a project required me to have 4.0.15 (for cascading updates). I removed all the old rpms, then installed the new rpms. No problems encountered except I can't seem to remove mysql-server-3.23.52-3 from the rpm list but I don't think it's really there on my system. I performed this same update last week on another RH8 system running 2.23.56. in fact I'm using the exact same 4.0.15 rpms copied from the other machine. Google search didn't turn up any solutions that I saw. [EMAIL PROTECTED] mysql]# rpm -qa | grep mysql -i libdbi-dbd-mysql-0.6.5-2 MySQL-shared-compat-4.0.15-0 qt-MySQL-3.0.5-17 php-mysql-4.1.2-7.3.4 mysql-server-3.23.52-3 MySQL-python-0.9.1-4 MySQL-client-4.0.15-0 perl-DBD-MySQL-2.1017-3 mod_auth_mysql-1.11-1 arkpmysql-5.1.7-1 MySQL-devel-4.0.15-0 MySQL-shared-4.0.15-0 MySQL-server-4.0.15-0 [EMAIL PROTECTED] mysql]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 to server version: 4.0.15-standard [EMAIL PROTECTED] mysql]# mysql --version; mysql Ver 12.21 Distrib 4.0.15, for pc-linux (i686) [EMAIL PROTECTED] mysql]# cat /etc/my.cnf [client] socket=/tmp/mysql.sock [mysqld] port=3306 socket=/tmp/mysql.sock innodb_data_home_dir= # Data file(s) must be able to # hold your data and indexes. # Make sure you have enough # free disk space. innodb_data_file_path = ibdata1:10M:autoextend # Set buffer pool size to # 50 - 80 % of your computer's # memory set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M # Set the log file size to about # 25 % of the buffer pool size set-variable = innodb_log_file_size=20M set-variable = innodb_log_buffer_size=8M # Set ..flush_log_at_trx_commit # to 0 if you can afford losing # some last transactions innodb_flush_log_at_trx_commit=1 set-variable = innodb_lock_wait_timeout=50 #innodb_flush_method=fdatasync #set-variable = innodb_thread_concurrency=5 skip-locking set-variable = max_connections=200 #set-variable = read_buffer_size=1M set-variable = sort_buffer=1M # Set key_buffer to 5 - 50% # of your RAM depending on how # much you use MyISAM tables, but # keep key_buffer + InnoDB # buffer pool size 80% of # your RAM set-variable = key_buffer=10M -- 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: Errors compiling mySQL 4.0 on Solaris 8 with Forte 7
Rudolphe, I saw your posting on the website: [EMAIL PROTECTED] List Archive at : http://www.jsw4.net/info/list-archives/mysql/02-wk41/index.html#00206 We are experiencing the same sort of problem. Did you ever get a response and/or solve this problem? Cristl G. Weckenmann Senior Software Engineer General Dynamics Advanced Information Systems Office: 407-658-0044 x263
RE: Is there a way to find out if a table exists?
Is there some way to do something like: SELECT * FROM tables WHERE name = table_name; And get a result I could test for truth, and thus run my script? Show tables like 'table_name'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf and binary distributions
At 11:13 AM 9/19/2003, Paul DuBois wrote: At 10:42 AM -0700 9/19/03, Jon Drukman wrote: is there any way to get mysqld 4.0.15a from the linux binary distribution to use another path for my.cnf apart from /etc/my.cnf /usr/local/mysql/data/my.cnf ~/.my.cnf No, but if you have control over how the server gets started, you can invoke it with a --defaults-file option to tell it to ignore the standard option files and specify a file of your own choosing. thank you very much. that's exactly what i've been looking for! i tried grepping for config file and my.cnf all over the place. you might want to somehow indicate that in the help message. there's no indication that defaults file = my.cnf anywhere. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How Fast is COUNT()
I am creating a script which will grab a random row from a mySQL table. Right now the way it is set up each time it calls COUNT(). I was thinking of creating a script and running it as a cron job to update count once every 10 minutes and store the number as an indexed array in the table. It seems like a lot of trouble, so how much overhead does COUNT have? Thanks in Advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld will not start
Folks, I am trying to start mysqld and when I do the deamon crashes. mysqld.log has the following: 030919 14:25:06 mysqld started 030919 14:25:08 Can't start server : Bind on unix socket: Permission denied 030919 14:25:08 Do you already have another mysqld server running on socket: /usr/lib/mysql/mysql.sock ? 030919 14:25:08 Aborting 030919 14:25:08 /usr/libexec/mysqld: Shutdown Complete 030919 14:25:08 mysqld ended which says to me that something is already listening on that port. Acording to netstat nothing is running. Does any one know what is going on with this. Joseph Donato __ Joseph E. Donato New Brunswick Computing Services RUCS-NB Help Desk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fragmentation
this looks great. will this work with version 4.04 on solaris? as a side comment, anyone know when 4.1 will get out of the alpha stage? very much looking forward to upgrading, but only when its relatively stable. jeff Per Andreas Buer wrote: Hello Heikki, Heikki Tuuri [EMAIL PROTECTED] writes: I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! It did the job just fine. Thanks. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The 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]
Re: RPM Upgrade from 3.23.52 to 4.0.15 :: all my database show up as empty.
On Fri, Sep 19, 2003 at 11:20:13AM -0700, Daevid Vincent wrote: Sorry for the double post. I sent this yesterday without any replies, so I'm wondering if it made it out there? Next time, visit lists.mysql.com and check the archives. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 5 days, processed 218,628,995 queries (438/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RADIUS is a Wh*%@
I am using MySQL 4.0.15 on Win2K (not by choice, vendor chose this platform)
I user the Database to store user information to Allow RADIUS to authenticate
users against the tables. I have to encrypt the password fields, If I use
MD5 it is a one way algorithm, enable for me to user MD5 I have to compare
the hashes if hashes match then I'm golden and user is granted access,
however the flavor of RADIUS the Vendor has chosen to use can not by it's self
do a hash comparison thus all Hash encrypted passwords can't be read by
Radius and users are denied access.
Has any one had experience with this and know of a way to fix this
My other thought was to use:
INSERT INTO user_profile (userid, password)
VALUES ('sam', AES_ENCRYPT(sam,password));
this will allow me to encrypt the password field, but I still need to get RADIUS to
do the AES_DECRYPT any thoughts.
ugh
Jeff Stout
CSG Systems, Inc.
303-200-3204
Re: Is there a way to find out if a table exists?
On 19-Sep-2003 Dan Anderson wrote:
I am trying to make my PHP script autodetect when a table in a mySQL
database exists, and when it doesn't, create it.
snip
function tableexists($tbl) {
$res = @mysql_query(SELECT 1 FROM $tbl);
return ($res ? true : false);
}
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf and binary distributions
At 11:30 AM -0700 9/19/03, Jon Drukman wrote: At 11:13 AM 9/19/2003, Paul DuBois wrote: At 10:42 AM -0700 9/19/03, Jon Drukman wrote: is there any way to get mysqld 4.0.15a from the linux binary distribution to use another path for my.cnf apart from /etc/my.cnf /usr/local/mysql/data/my.cnf ~/.my.cnf No, but if you have control over how the server gets started, you can invoke it with a --defaults-file option to tell it to ignore the standard option files and specify a file of your own choosing. thank you very much. that's exactly what i've been looking for! i tried grepping for config file and my.cnf all over the place. you might want to somehow indicate that in the help message. there's no indication that defaults file = my.cnf anywhere. mysqld --help -jsd- -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How Fast is COUNT()
At 2:37 PM -0400 9/19/03, Dan Anderson wrote: I am creating a script which will grab a random row from a mySQL table. Right now the way it is set up each time it calls COUNT(). I was thinking of creating a script and running it as a cron job to update count once every 10 minutes and store the number as an indexed array in the table. It seems like a lot of trouble, so how much overhead does COUNT have? MyISAM and ISAM: practically none InnoDB: causes full table scan, not a good idea. :-) -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fragmentation
At 12:40 PM -0600 9/19/03, Jeff Mathis wrote: this looks great. will this work with version 4.04 on solaris? as a side comment, anyone know when 4.1 will get out of the alpha stage? very much looking forward to upgrading, but only when its relatively stable. 4.0 went beta in 4.0.3, gamma in 4.0.6, stable in 4.0.12. 3.23 went beta in 3.23.20, gamma in 3.23.28, and stable in 3.23.31. 3.22: 3.22.4 / 3.22.14 / 3.22.17 So, it varies. It'll happen faster if people try the alphas and pound hard on them. :-) jeff Per Andreas Buer wrote: Hello Heikki, Heikki Tuuri [EMAIL PROTECTED] writes: I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! It did the job just fine. Thanks. -- Per Andreas Buer -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Syntax Query Help DECRYPT
I have added data into MySQL server 4.0.15
INSERT INTO userdata (userid, password)
VALUES ('user', AES_ENCRYPT (user,password)
I'm having trouble doing the decrypt
SELECT userid, password (AES_DECRYPT)
FROM userdata blah blah blah
Something this this
Thanks
Jeff Stout
Re: mysqld will not start
Joseph Donato wrote: Folks, I am trying to start mysqld and when I do the deamon crashes. mysqld.log has the following: 030919 14:25:06 mysqld started 030919 14:25:08 Can't start server : Bind on unix socket: Permission denied 030919 14:25:08 Do you already have another mysqld server running on socket: /usr/lib/mysql/mysql.sock ? 030919 14:25:08 Aborting 030919 14:25:08 /usr/libexec/mysqld: Shutdown Complete 030919 14:25:08 mysqld ended which says to me that something is already listening on that port. Not port, socket. Acording to netstat nothing is running. Does any one know what is going on with this. This is not a network issue. Does the socket /usr/lib/mysql/mysql.sock exist? Does mysql own /usr/lib/mysql, and all its files? Is /usr/lib/mysql world searchable? Is /usr/lib/mysql/mysql.sock work writeable? Joseph Donato __ Joseph E. Donato New Brunswick Computing Services RUCS-NB Help Desk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT join
Hi, SELECT A.fld1, A.fld2, B.fld1 FROM A LEFT JOIN B ON A.lnkfld = B.lnkfld LIMIT 100; A and B are big table and this command is very slow. If I use INNER JOIN, is very fast but I loose some records. How I can write a faster LEFT JOIN command? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax Query Help DECRYPT
At 1:08 PM -0600 9/19/03, Stout, Jeff wrote:
I have added data into MySQL server 4.0.15
INSERT INTO userdata (userid, password)
VALUES ('user', AES_ENCRYPT (user,password)
I'm having trouble doing the decrypt
SELECT userid, password (AES_DECRYPT)
FROM userdata blah blah blah
Something this this
Thanks
Jeff Stout
The syntax for both is the same:
AES_ENCRYPT(string,key_string)
AES_DECRYPT(string,key_string)
See:
http://www.mysql.com/doc/en/Miscellaneous_functions.html
Please note that internals is not for questions of this type.
I've removed it from the cc: list.
Thanks.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT join
sorry, I guess I got my answer. the example that I wrote was that exactly the same query that I had in my program. in the real query I had 3 tables. I read the mysql note and I found that location of the table to write the link is important for optimization. by changing the sequence of the tables, the query worked faster. - Original Message - From: Mojtaba Faridzad [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 19, 2003 3:20 PM Subject: LEFT join Hi, SELECT A.fld1, A.fld2, B.fld1 FROM A LEFT JOIN B ON A.lnkfld = B.lnkfld LIMIT 100; A and B are big table and this command is very slow. If I use INNER JOIN, is very fast but I loose some records. How I can write a faster LEFT JOIN command? Thanks -- 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]
join optimization
I have two tables and am running a simple join between them to get questions and their repsective response averages from a survey. The question table has 49 rows and the Response table has 126,732. I'd like to cut down on the time its taking to run this specific query...as i'll be running many like it to generate reports. The query below is the selecting the most data, normally this will be limited to specific groups by joining more tables. I am executing the following query SELECT Question.Text_Long, AVG( Response ) FROM `Response` INNER JOIN Question ON Question.Question_Key = Response.Question_Key WHERE Question.Question_Key LIKE '2003%' GROUP BY Response.Question_Key ORDER BY Question.Question_Key ASC Everything i've done so far leaves this query taking about 7-8 seconds to excecute...and i'm trying to cut that time down. If i leave out the join and just execute --SELECT Question_Key, AVG( Response ) FROM `Response` GROUP BY Question_Key it takes about 3 seconds...is there anything i can do to speed the join up? i've tried using string functions instead of LIKE, but none of them proved to be faster. i've also changed the table that i'm requesing the data from and grouping by(Question and response)...all with mimimal impact. I'm running MySQL. 3.23 Thanks for any help/thoughts you may have. have a good weekend. Jeff the table layout is mysql describe Response; +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | Question_Key | int(11)| | PRI | 0 | | | Survey_Key | int(11)| | PRI | 0 | | | Response | tinyint(4) | | MUL | 0 | | +--++--+-+-+---+ 3 rows in set (0.00 sec) mysql describe Question; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | Question_Number | int(11) | | | 0 | | | Text_Long | varchar(255) | YES | | NULL| | | Text_Short | varchar(255) | YES | | NULL| | | Category_ID | int(11) | YES | | NULL| | | SurveyID| int(11) | YES | | NULL| | | End_Date| datetime | YES | | NULL| | | Question_Key| int(11) | | PRI | 0 | | +-+--+--+-+-+---+ 7 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]
Re: join optimization
[EMAIL PROTECTED] wrote: I have two tables and am running a simple join between them to get questions and their repsective response averages from a survey. The question table has 49 rows and the Response table has 126,732. I'd like to cut down on the time its taking to run this specific query...as i'll be running many like it to generate reports. The query below is the selecting the most data, normally this will be limited to specific groups by joining more tables. I am executing the following query SELECT Question.Text_Long, AVG( Response ) FROM `Response` INNER JOIN Question ON Question.Question_Key = Response.Question_Key WHERE Question.Question_Key LIKE '2003%' GROUP BY Response.Question_Key ORDER BY Question.Question_Key ASC You are doing a string compare on an integer field. Why? Everything i've done so far leaves this query taking about 7-8 seconds to excecute...and i'm trying to cut that time down. If i leave out the join and just execute --SELECT Question_Key, AVG( Response ) FROM `Response` GROUP BY Question_Key it takes about 3 seconds...is there anything i can do to speed the join up? i've tried using string functions instead of LIKE, but none of them proved to be faster. i've also changed the table that i'm requesing the data from and grouping by(Question and response)...all with mimimal impact. I'm running MySQL. 3.23 Thanks for any help/thoughts you may have. have a good weekend. Jeff the table layout is mysql describe Response; +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | Question_Key | int(11)| | PRI | 0 | | | Survey_Key | int(11)| | PRI | 0 | | | Response | tinyint(4) | | MUL | 0 | | +--++--+-+-+---+ 3 rows in set (0.00 sec) mysql describe Question; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | Question_Number | int(11) | | | 0 | | | Text_Long | varchar(255) | YES | | NULL| | | Text_Short | varchar(255) | YES | | NULL| | | Category_ID | int(11) | YES | | NULL| | | SurveyID| int(11) | YES | | NULL| | | End_Date| datetime | YES | | NULL| | | Question_Key| int(11) | | PRI | 0 | | +-+--+--+-+-+---+ 7 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]
Re: join optimization
ahhh yes, thanks for pointing that out. it was not origianlly designed as such and then the codes changed. time is about the same though Thanks gerald_clark [EMAIL PROTECTED]To: [EMAIL PROTECTED] ystems.comcc: [EMAIL PROTECTED] Subject: Re: join optimization 09/19/2003 04:05 PM [EMAIL PROTECTED] wrote: I have two tables and am running a simple join between them to get questions and their repsective response averages from a survey. The question table has 49 rows and the Response table has 126,732. I'd like to cut down on the time its taking to run this specific query...as i'll be running many like it to generate reports. The query below is the selecting the most data, normally this will be limited to specific groups by joining more tables. I am executing the following query SELECT Question.Text_Long, AVG( Response ) FROM `Response` INNER JOIN Question ON Question.Question_Key = Response.Question_Key WHERE Question.Question_Key LIKE '2003%' GROUP BY Response.Question_Key ORDER BY Question.Question_Key ASC You are doing a string compare on an integer field. Why? Everything i've done so far leaves this query taking about 7-8 seconds to excecute...and i'm trying to cut that time down. If i leave out the join and just execute --SELECT Question_Key, AVG( Response ) FROM `Response` GROUP BY Question_Key it takes about 3 seconds...is there anything i can do to speed the join up? i've tried using string functions instead of LIKE, but none of them proved to be faster. i've also changed the table that i'm requesing the data from and grouping by(Question and response)...all with mimimal impact. I'm running MySQL. 3.23 Thanks for any help/thoughts you may have. have a good weekend. Jeff the table layout is mysql describe Response; +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | Question_Key | int(11)| | PRI | 0 | | | Survey_Key | int(11)| | PRI | 0 | | | Response | tinyint(4) | | MUL | 0 | | +--++--+-+-+---+ 3 rows in set (0.00 sec) mysql describe Question; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | Question_Number | int(11) | | | 0 | | | Text_Long | varchar(255) | YES | | NULL| | | Text_Short | varchar(255) | YES | | NULL| | | Category_ID | int(11) | YES | | NULL| | | SurveyID| int(11) | YES | | NULL| | | End_Date| datetime | YES | | NULL| | | Question_Key| int(11) | | PRI | 0 | | +-+--+--+-+-+---+ 7 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]
dates difference
I´d like to write a mysql statemant which takes two dates: for example:2003-11-10 2003-11-19 and results ...9 days something like :select 2003-11-19 - 2003-11-10 thanks
Changing the data directory.
We've got mysql 3.23 installed on a redhat system via the rpm's that come with RedHat 8.0. I'd like to change the default data directory so something other than /var/lib/mysql. I know this is supposed to be possible with a start switch of --datadir=/path/to/data but it doesn't seem to work. The startup of mysql uses the /etc/init.d/mysqld script so I suppose I'll need to change something in that and add the switch, I just can't seem to figure out where. Any suggestions? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dates difference
select to_days('2003-11-10') - to_days('2003-11-19')
- Original Message -
From: Fabio Bernardo [EMAIL PROTECTED]
To: Mysql (E-mail) [EMAIL PROTECTED]
Sent: Friday, September 19, 2003 4:13 PM
Subject: dates difference
I´d like to write a mysql statemant which takes two dates:
for example:2003-11-10 2003-11-19
and results ...9 days
something like :select 2003-11-19 - 2003-11-10
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there a way to find out if a table exists?
On 19-Sep-2003 Don Read wrote:
On 19-Sep-2003 Dan Anderson wrote:
I am trying to make my PHP script autodetect when a table in a mySQL
database exists, and when it doesn't, create it.
snip
function tableexists($tbl) {
$res = @mysql_query(SELECT 1 FROM $tbl);
return ($res ? true : false);
}
Err ... make that
$res = @mysql_query(SELECT 1 FROM $tbl LIMIT 1);
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query performance
Hi,
I have a table with 18 million of rows. The table structure is
describe user_att
+-+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+-+---+
| user_id | int(11) | | PRI | 0 | |
| att_id | int(11) | | PRI | 0 | |
| value | varchar(200) | | | | |
| date| datetime | YES | | NULL| |
+-+--+--+-+-+---+
2 index on this table:
- one unique index on user_id and att_id (pk)
- one index on att_id and user_id.
I need to have the following query:
select value from user_att where att_id = ? and value like '?' (no wildcard)
My question is
1. when I do a explain, this query use the second index. But, if I change my
second index to att_id and value, will the performance improve?
2. what is the difference if I change the query to
select value from user_att where att_id = ? and lower(value) = lower('?')
will this query slower?
3. when compare string, is mysql sql case sensitive? It seems that it is
case in-sensitive. If case in-sensitive, the following query will be faster?
select value from user_att where att_id = ? and value = '?'
Thanks
Hsiu-Hui
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
explain
Hello, When I do a explain on a query, I got the following: +-+ | Comment | +-+ | Impossible WHERE noticed after reading const tables | +-+ does this say that my query is using Primary key? Thanks Hsiu-Hui -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: explain
No that says that it can figure out the where and prob wont use a key. The like is probably the cause. If it was going to use the primary key then the FIELD Key_used (something like that) would say PRIMARY. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Hsiu-Hui Tseng [mailto:[EMAIL PROTECTED] --Sent: Friday, September 19, 2003 2:12 PM --To: [EMAIL PROTECTED] --Subject: explain -- --Hello, -- --When I do a explain on a query, I got the following: --+-+ --| Comment | --+-+ --| Impossible WHERE noticed after reading const tables | --+-+ -- --does this say that my query is using Primary key? -- --Thanks -- --Hsiu-Hui -- --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: innodb and fragmentation
Paul DuBois wrote: At 12:40 PM -0600 9/19/03, Jeff Mathis wrote: this looks great. will this work with version 4.04 on solaris? as a side comment, anyone know when 4.1 will get out of the alpha stage? very much looking forward to upgrading, but only when its relatively stable. 4.0 went beta in 4.0.3, gamma in 4.0.6, stable in 4.0.12. 3.23 went beta in 3.23.20, gamma in 3.23.28, and stable in 3.23.31. 3.22: 3.22.4 / 3.22.14 / 3.22.17 So, it varies. It'll happen faster if people try the alphas and pound hard on them. :-) I wish we had the luxury. But, I may try and force the issue anyway. set up another instance. jeff -- Jeff Mathis, Ph.D. 505-955-1434 The 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]
Re: Changing the data directory.
At 4:22 PM -0400 9/19/03, Jeff McKeon wrote: We've got mysql 3.23 installed on a redhat system via the rpm's that come with RedHat 8.0. I'd like to change the default data directory so something other than /var/lib/mysql. I know this is supposed to be possible with a start switch of --datadir=/path/to/data but it doesn't seem to work. It should work. The startup of mysql uses the /etc/init.d/mysqld script so I suppose I'll need to change something in that and add the switch, I just can't seem to figure out where. I wouldn't change the script, it'll get overwritten if you upgrade. You might try editing /etc/my.cnf (create it if it doesn't exist) and add this to it: [mysqld] datadir=/path/to/data Alternatively, rename /var/lib/mysql to something else (or remove it) and recreate /var/lib/mysql as a symlink to where you really want the data directory. Make sure the target of the symlink exists. Any suggestions? Thanks, Jeff -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there a way to find out if a table exists?
At 3:37 PM -0500 9/19/03, Don Read wrote:
On 19-Sep-2003 Don Read wrote:
On 19-Sep-2003 Dan Anderson wrote:
I am trying to make my PHP script autodetect when a table in a mySQL
database exists, and when it doesn't, create it.
snip
function tableexists($tbl) {
$res = @mysql_query(SELECT 1 FROM $tbl);
return ($res ? true : false);
}
Err ... make that
$res = @mysql_query(SELECT 1 FROM $tbl LIMIT 1);
Better yet, SELECT 1 FROM $tbl WHERE 1 = 0, which returns even
fewer rows. :-)
Or use SHOW TABLES LIKE 'name of table here' or
SHOW TABLE STATUS LIKE 'name of table here' and see if you
get any row back.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Loading GIS data - how?
Im a newbie to MySQL and am trying to evaluate it for our use. Ive been studying the 4.1 GIS features and cant figure out some relatively simple things: - How do I load data (e.g., POINTs) into a GEOMETRY column, either from a file or from other (NUMERIC) database columns? It appears that I have to reformat my data into either WKT or WKB, and then issue a series of INSERTS, since the POINTS(x,y) function is listed as not being implemented. Is this correct? If so, when will POINTS() and other MySQL GIS functions be implemented in a released binary? Does anyone have some simple scripts or source code available that will do this (on a Windows box) in the mean time? An additional stupid question: - Do you have a simple way of loading data from a tab-delimited file where the first row DOES contain the column names? I would like to ensure that the files columns correspond to correct columns in the database, rather than loading them blind. Thanks, Thomas
Re: explain
On Fri, Sep 19, 2003 at 02:12:02PM -0700, Hsiu-Hui Tseng wrote: Hello, When I do a explain on a query, I got the following: +-+ | Comment | +-+ | Impossible WHERE noticed after reading const tables | +-+ does this say that my query is using Primary key? It says that MySQL doesn't believe the query you've asked makes sense. You could try posting the query here in the hopes that someone will provide a more detailed answer... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 5 days, processed 222,651,539 queries (435/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Loading GIS data - how?
Im a newbie to MySQL and am trying to evaluate it for our use. Ive been studying the 4.1 GIS features and cant figure out some relatively simple things: - How do I load data (e.g., POINTs) into a GEOMETRY column, either from a file or from other (NUMERIC) database columns? It appears that I have to reformat my data into either WKT or WKB, and then issue a series of INSERTS, since the POINTS(x,y) function is listed as not being implemented. Is this correct? If so, when will POINTS() and other MySQL GIS functions be implemented in a released binary? Does anyone have some simple scripts or source code available that will do this (on a Windows box) in the mean time? An additional stupid question: - Do you have a simple way of loading data from a tab-delimited file where the first row DOES contain the column names? I would like to ensure that the files columns correspond to correct columns in the database, rather than loading them blind. Thanks, Thomas
Data store/extract help !!
I'm still unclear on how to do the decrypt syntax,
forgive me I'm new to DB work, my background is
more security and UNIX admin.
I need help with the data extraction/decryption
SELECT userid, password
FROM user_profile AES_DECRYPT(user,password)
??
What I'm trying to accomplish is I'm using RADIUS to
Authenticate users for Network Access, the user info
is stored via MySQL DB, I have the need to ENCRYPT
the Password Field and Possibly the UserName Field.
I need to DECRYPT so RADIUS can read the Password and
accept users, I thought of MD5 but this is a one way
hash, I'd have to compare the hashes and haven't fiqured
out a way to do that through SQL.
Any help or suggestions
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Friday, September 19, 2003 1:31 PM
To: Stout, Jeff; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Syntax Query Help DECRYPT
At 1:08 PM -0600 9/19/03, Stout, Jeff wrote:
I have added data into MySQL server 4.0.15
INSERT INTO userdata (userid, password)
VALUES ('user', AES_ENCRYPT (user,password)
I'm having trouble doing the decrypt
Something this this
Thanks
Jeff Stout
The syntax for both is the same:
AES_ENCRYPT(string,key_string)
AES_DECRYPT(string,key_string)
See:
http://www.mysql.com/doc/en/Miscellaneous_functions.html
Please note that internals is not for questions of this type.
I've removed it from the cc: list.
Thanks.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query performance
2 index on this table:
- one unique index on user_id and att_id (pk)
- one index on att_id and user_id.
I need to have the following query:
select value from user_att where att_id = ? and value like '?'
(no wildcard)
1. when I do a explain, this query use the second index. But, if
I change my
second index to att_id and value, will the performance improve?
You could add it as a third index and see which works better, but on 18
million rows that's going to probably take quite a bit of time. From
looking at your query it seems like it would be a better index than the
current one.
2. what is the difference if I change the query to
select value from user_att where att_id = ? and lower(value) =
lower('?')
will this query slower?
I could be wrong, but I believe the query won't use the index if you use
lower(). Run an explain on this query and see.
3. when compare string, is mysql sql case sensitive? It seems that it is
case in-sensitive. If case in-sensitive, the following query will
be faster?
select value from user_att where att_id = ? and value = '?'
Mysql is only case sensitive on binary and blob fields.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld consumes 1.3Gb of swap for simple query on solaris
Description: A particular simple mysql query, including FORMAT, count, and group commands, run on a very small table, causes mysqld to consume about 1300 Mbytes of swap space on our sparc solaris systems. Killing and restarting mysqld frees up that space. All other routine mysql queries seem to behave normally. How-To-Repeat: Here's an example that reproduces the problem: mysql create database test1; Query OK, 1 row affected (0.05 sec) mysql use test1; Database changed mysql create table table1 (length double, id int); Query OK, 0 rows affected (0.04 sec) mysql insert into table1 (length, id) VALUES (1000, 1); Query OK, 1 row affected (0.00 sec) mysql insert into table1 (length, id) VALUES (1010, 1); Query OK, 1 row affected (0.00 sec) mysql insert into table1 (length, id) VALUES (1020, 2); Query OK, 1 row affected (0.00 sec) mysql select FORMAT(length, 0) as len, count(distinct id) - from table1 group by len; +---++ | len | count(distinct id) | +---++ | 1,000 | 1 | | 1,010 | 1 | | 1,020 | 1 | +---++ 3 rows in set (0.00 sec) Here are 'top' snapshots, and mysqld memory usage as shown by 'ps', both before and after the query was made. Note that the 'swap free' was reduced by 1366M, and the memory size (SZ) reported for mysqld by ps went from 1578 pages (~13M) to 176368 pages (~1400M). Before query: top: load averages: 0.01, 0.03, 0.04 14:14:31 173 processes: 172 sleeping, 1 on cpu CPU states: % idle, % user, % kernel, % iowait, % swap Memory: 1024M real, 499M free, 281M swap in use, 2335M swap free ps -efl: F S UID PID PPID C PRI NI ADDR SZWCHANSTIME TTY T IME CMD 8 Smysql 5884 4997 0 48 20? 1578? 14:14:13 pts/13 0:00 /usr/local/mysql-3.23.49/bin/mysqld After query: top: load averages: 0.02, 0.03, 0.04 14:15:55 175 processes: 174 sleeping, 1 on cpu CPU states: % idle, % user, % kernel, % iowait, % swap Memory: 1024M real, 498M free, 1647M swap in use, 969M swap free ps -efl: F S UID PID PPID C PRI NI ADDR SZWCHANSTIME TTY T IME CMD 8 Smysql 5884 4997 0 48 20? 176368? 14:14:13 pts/13 0:00 /usr/local/mysql-3.23.49/bin/mysqld Mysql versions tried: % mysql --version mysql Ver 11.18 Distrib 3.23.55, for sun-solaris2.7 (sparc) (Also tried mysql-4.0.13, it behaves the same way). Solaris versions tried: % uname -a SunOS test 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 (Also tried an Ultra-2 running 5.7, behaved the same). Thanks, Tom Kilsdonk Fix: Submitter-Id: submitter ID Originator: Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: mysqld consumes 1.3Gb of swap for simple query on solaris Severity: Priority: Category: mysql Class: sw-bug Release: mysql-3.23.55 (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.55, for sun-solaris2.7 on sparc Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.49 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 41 min 43 sec Threads: 2 Questions: 11 Slow queries: 0 Opens: 9 Flush tables: 1 Open tables: 1 Queries per second avg: 0.004 Environment: System: SunOS matrix 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 Architecture: sun4 Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/gcc3.3/bin/gcc /usr/ucb/cc GCC: Reading specs from /usr/local/gcc3.3/bin/../lib/gcc-lib/sparc-sun-solaris2.8/3.3/specs Configured with: ../gcc-3.3/configure --prefix=/home/kilsdonk/gcc3.3/install Thread model: posix gcc version 3.3 Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' LIBC: -rw-r--r-- 1 root bin 1764552 Jul 17 2002 /lib/libc.a lrwxrwxrwx 1 root root 11 Sep 18 2001 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146204 Jul 17 2002 /lib/libc.so.1 -rw-r--r-- 1 root bin 1764552 Jul 17 2002 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Sep 18 2001 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146204 Jul 17 2002 /usr/lib/libc.so.1 Configure command: ./configure '--without-docs' '--enable-thread-safe-client' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld consumes 1.3Gb of swap for simple query on solaris
Description: A particular simple mysql query, including FORMAT, count, and group commands, run on a very small table, causes mysqld to consume about 1300 Mbytes of swap space on our sparc solaris systems. Killing and restarting mysqld frees up that space. All other routine mysql queries seem to behave normally. How-To-Repeat: Here's an example that reproduces the problem: mysql create database test1; Query OK, 1 row affected (0.05 sec) mysql use test1; Database changed mysql create table table1 (length double, id int); Query OK, 0 rows affected (0.04 sec) mysql insert into table1 (length, id) VALUES (1000, 1); Query OK, 1 row affected (0.00 sec) mysql insert into table1 (length, id) VALUES (1010, 1); Query OK, 1 row affected (0.00 sec) mysql insert into table1 (length, id) VALUES (1020, 2); Query OK, 1 row affected (0.00 sec) mysql select FORMAT(length, 0) as len, count(distinct id) - from table1 group by len; +---++ | len | count(distinct id) | +---++ | 1,000 | 1 | | 1,010 | 1 | | 1,020 | 1 | +---++ 3 rows in set (0.00 sec) Here are 'top' snapshots, and mysqld memory usage as shown by 'ps', both before and after the query was made. Note that the 'swap free' was reduced by 1366M, and the memory size (SZ) reported for mysqld by ps went from 1578 pages (~13M) to 176368 pages (~1400M). Before query: top: load averages: 0.01, 0.03, 0.04 14:14:31 173 processes: 172 sleeping, 1 on cpu CPU states: % idle, % user, % kernel, % iowait, % swap Memory: 1024M real, 499M free, 281M swap in use, 2335M swap free ps -efl: F S UID PID PPID C PRI NI ADDR SZWCHANSTIME TTY T IME CMD 8 Smysql 5884 4997 0 48 20? 1578? 14:14:13 pts/13 0:00 /usr/local/mysql-3.23.49/bin/mysqld After query: top: load averages: 0.02, 0.03, 0.04 14:15:55 175 processes: 174 sleeping, 1 on cpu CPU states: % idle, % user, % kernel, % iowait, % swap Memory: 1024M real, 498M free, 1647M swap in use, 969M swap free ps -efl: F S UID PID PPID C PRI NI ADDR SZWCHANSTIME TTY T IME CMD 8 Smysql 5884 4997 0 48 20? 176368? 14:14:13 pts/13 0:00 /usr/local/mysql-3.23.49/bin/mysqld Mysql versions tried: % mysql --version mysql Ver 11.18 Distrib 3.23.55, for sun-solaris2.7 (sparc) (Also tried mysql-4.0.13, it behaves the same way). Solaris versions tried: % uname -a SunOS test 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 (Also tried an Ultra-2 running 5.7, behaved the same). Thanks, Tom Kilsdonk Fix: Submitter-Id: submitter ID Originator: Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: mysqld consumes 1.3Gb of swap for simple query on solaris Severity: Priority: Category: mysql Class: Release: mysql-3.23.55 (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.55, for sun-solaris2.7 on sparc Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.49 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 1 hour 17 min 38 sec Threads: 2 Questions: 12 Slow queries: 0 Opens: 9 Flush tables: 1 Open tables: 1 Queries per second avg: 0.003 Environment: System: SunOS matrix 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 Architecture: sun4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld consumes 1.3Gb of swap for simple query on solaris
Hi Tom, Sounds odd... Do other queries that behave normally use GROUP BY or DISTINCT? What are your configuration variables? e.g. SHOW VARIABLES or mysqladmin variables. Is sort_buffer_size set to some huge value? Matt - Original Message - From: [EMAIL PROTECTED] Sent: Friday, September 19, 2003 5:19 PM Subject: mysqld consumes 1.3Gb of swap for simple query on solaris Description: A particular simple mysql query, including FORMAT, count, and group commands, run on a very small table, causes mysqld to consume about 1300 Mbytes of swap space on our sparc solaris systems. Killing and restarting mysqld frees up that space. All other routine mysql queries seem to behave normally. How-To-Repeat: Here's an example that reproduces the problem: mysql create database test1; Query OK, 1 row affected (0.05 sec) mysql use test1; Database changed mysql create table table1 (length double, id int); Query OK, 0 rows affected (0.04 sec) mysql insert into table1 (length, id) VALUES (1000, 1); Query OK, 1 row affected (0.00 sec) mysql insert into table1 (length, id) VALUES (1010, 1); Query OK, 1 row affected (0.00 sec) mysql insert into table1 (length, id) VALUES (1020, 2); Query OK, 1 row affected (0.00 sec) mysql select FORMAT(length, 0) as len, count(distinct id) - from table1 group by len; +---++ | len | count(distinct id) | +---++ | 1,000 | 1 | | 1,010 | 1 | | 1,020 | 1 | +---++ 3 rows in set (0.00 sec) Here are 'top' snapshots, and mysqld memory usage as shown by 'ps', both before and after the query was made. Note that the 'swap free' was reduced by 1366M, and the memory size (SZ) reported for mysqld by ps went from 1578 pages (~13M) to 176368 pages (~1400M). Before query: top: load averages: 0.01, 0.03, 0.04 14:14:31 173 processes: 172 sleeping, 1 on cpu CPU states: % idle, % user, % kernel, % iowait, % swap Memory: 1024M real, 499M free, 281M swap in use, 2335M swap free ps -efl: F S UID PID PPID C PRI NI ADDR SZWCHANSTIME TTY T IME CMD 8 Smysql 5884 4997 0 48 20? 1578? 14:14:13 pts/13 0:00 /usr/local/mysql-3.23.49/bin/mysqld After query: top: load averages: 0.02, 0.03, 0.04 14:15:55 175 processes: 174 sleeping, 1 on cpu CPU states: % idle, % user, % kernel, % iowait, % swap Memory: 1024M real, 498M free, 1647M swap in use, 969M swap free ps -efl: F S UID PID PPID C PRI NI ADDR SZWCHANSTIME TTY T IME CMD 8 Smysql 5884 4997 0 48 20? 176368? 14:14:13 pts/13 0:00 /usr/local/mysql-3.23.49/bin/mysqld Mysql versions tried: % mysql --version mysql Ver 11.18 Distrib 3.23.55, for sun-solaris2.7 (sparc) (Also tried mysql-4.0.13, it behaves the same way). Solaris versions tried: % uname -a SunOS test 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 (Also tried an Ultra-2 running 5.7, behaved the same). Thanks, Tom Kilsdonk Fix: Submitter-Id: submitter ID Originator: Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: mysqld consumes 1.3Gb of swap for simple query on solaris Severity: Priority: Category: mysql Class: sw-bug Release: mysql-3.23.55 (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.55, for sun-solaris2.7 on sparc Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.49 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 41 min 43 sec Threads: 2 Questions: 11 Slow queries: 0 Opens: 9 Flush tables: 1 Open tables: 1 Queries per second avg: 0.004 Environment: System: SunOS matrix 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 Architecture: sun4 Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/gcc3.3/bin/gcc /usr/ucb/cc GCC: Reading specs from /usr/local/gcc3.3/bin/../lib/gcc-lib/sparc-sun-solaris2.8/3.3/specs Configured with: ../gcc-3.3/configure --prefix=/home/kilsdonk/gcc3.3/install Thread model: posix gcc version 3.3 Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' LIBC: -rw-r--r-- 1 root bin 1764552 Jul 17 2002 /lib/libc.a lrwxrwxrwx 1 root root 11 Sep 18 2001 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146204 Jul 17 2002 /lib/libc.so.1 -rw-r--r-- 1 root bin 1764552 Jul 17 2002 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Sep 18 2001 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146204 Jul 17 2002
Re: explain
Hi, - Original Message - From: Jeremy Zawodny Cc: [EMAIL PROTECTED] Sent: Friday, September 19, 2003 5:12 PM Subject: Re: explain On Fri, Sep 19, 2003 at 02:12:02PM -0700, Hsiu-Hui Tseng wrote: Hello, When I do a explain on a query, I got the following: +-+ | Comment | +-+ | Impossible WHERE noticed after reading const tables | +-+ does this say that my query is using Primary key? It says that MySQL doesn't believe the query you've asked makes sense. If the query didn't make sense such as if the WHERE contained 1=0, it would just say Impossible WHERE. The additional noticed after reading const tables is just that; an impossible WHERE detected after reading the const tables (= 1 row or where the WHERE refers all parts of a unique key with constant values). So yes, to answer the original question, it is using the PRIMARY KEY (or a UNIQUE one) or the table has less than 2 rows. Since only a single key lookup is needed, it can quickly tell if there's a matching row or not. If there is a matching row, EXPLAIN will output the usual columns of information. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: Platform vs. Performance
First, Thanks for all replies, I'll be going with XP Pro on a P4 machine. The next question is RAM, I know more is better, but the access database file I'm using now is 120Megs and will stay about the same size. Do I need more than 1G, can I get by with 512M Ram? I will of course turn virtual memory off and will also be using this computer for file sharing and burning cd's as backup's - or using a tape drive. Thanks Again! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: Platform vs. Performance
I'm running 1Gb on my normal desktop with XP pro...it's sufficient, but just, if you've got swap disabled. Still gets bogged down sometimes, almost like it's trying to swap and getting frustrated because it's not allowed to. I'd be happier with 1.5 or even 2Gb of nice hot DDR333...these days, ram is cheap enough you can really load up a machine and not hurt too much. D -Original Message- From: Jeremy Proffitt [mailto:[EMAIL PROTECTED] Sent: Friday, September 19, 2003 8:46 PM To: Mysql List Subject: Re: RE: Platform vs. Performance First, Thanks for all replies, I'll be going with XP Pro on a P4 machine. The next question is RAM, I know more is better, but the access database file I'm using now is 120Megs and will stay about the same size. Do I need more than 1G, can I get by with 512M Ram? I will of course turn virtual memory off and will also be using this computer for file sharing and burning cd's as backup's - or using a tape drive. Thanks Again! -- 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: explain
These 2 are the queries explain select * from user where user_id = 123; - where user_id is the primary key of user table with 2178576 rows. explain select * from user_att where user_id = 123 and att_id = 123; - where user_id and att_id is the primary key of user_att table with 18513726 rows. -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Friday, September 19, 2003 3:13 PM To: Hsiu-Hui Tseng Cc: [EMAIL PROTECTED] Subject: Re: explain On Fri, Sep 19, 2003 at 02:12:02PM -0700, Hsiu-Hui Tseng wrote: Hello, When I do a explain on a query, I got the following: +-+ | Comment | +-+ | Impossible WHERE noticed after reading const tables | +-+ does this say that my query is using Primary key? It says that MySQL doesn't believe the query you've asked makes sense. You could try posting the query here in the hopes that someone will provide a more detailed answer... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 5 days, processed 222,651,539 queries (435/sec. avg) -- 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: Simple Stored Procedure Emulation with PHP/mySQL
Hi, myphp looks like it's pretty slow going by the times reported in the examples. :-/ Probably something to do with all that PHP junk loaded into MySQL. :-) And no, I don't think you can do any stored procs with it; only apply PHP functions etc. to database values. Matt - Original Message - From: [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 8:04 PM Subject: Re: Simple Stored Procedure Emulation with PHP/mySQL Have you seen this? http://www.sklar.com/page/article/myphp Man this is a filth idea, there is potential here until stored procs come into effect, can it be used to create php stored procedure functions at all ? Like is there a better example than that, like its obvious its creating a function call php returning string sonmae but where is soname. and is this even available in 4 yet CREATE FUNCTION ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
