Re: Intermittent deadlock/InnoDB
On Wed, 2006-05-10 at 16:26 -0700, Robert DiFalco wrote: > Version 5.0.19. > > We have no autogenerate keys. We perform a single insert and get a lock > timeout. The insert is done with a stored procedure with a single line. > The lockup happens VERY rarely and we have no idea how to reproduce it. Probably best to file a bug report in the bugs system. This is the best way so we can track problem reports. -- Stewart Smith, Software Engineer MySQL AB, www.mysql.com Office: +14082136540 Ext: 6616 VoIP: [EMAIL PROTECTED] Mobile: +61 4 3 8844 332 Jumpstart your cluster: http://www.mysql.com/consulting/packaged/cluster.html signature.asc Description: This is a digitally signed message part
Re: searching for words with special chars
2006-05-10
Thread
Adam i Agnieszka Gąsiorowski FNORD aka ALinkA ak a symbol '( { .A. } )'' ||~> { A.A }
On 2006-04-01, at 13:17, Octavian Rasnita wrote:
Hi,
Is it possible to create a query that searches for records which
contains
words with special chars and with their english correspondents?
For example, if a user searches for "mata", I want to return all
the records
that contain the words:
mata
măta
mâţa
mâţă
(just like Google does).
Is it possible with MySQL, or I need to create all the possible
combinations
in the client program, then search for all those words?
Use REGEX search with collation patterns - that is symbols
that the regular expression engine of MySQL will expand into all the
letters. The syntax is explained in great detail in the manual -
building
a regular expression for that kind of search should be easy - simply
use the letters where the pattern shan't change and collation
constructs
in places where you expect variation (like [ăââ]). I'm sure only
some of the letters of your national alphabet have such variations,
most should be
stable, that is, unchanging. You could use alternative patterns
with OR according to how often a letter "mutates" (crunch a
dictionary) - put those more stable in front of REGEX expression. HTH.
--
Seks, seksić, seksolatki...news:pl.soc.seks.moderowana > <~|
{ A.A }|
When facing my demons, I clothe and feed them and I smile, yes, I smile,
As they're taking me over! End of the night never comes to quickly
for me!
https://hyperreal.info | https://kanaba.info |=> "Gościu! Szanuj
Zieleń!"
smime.p7s
Description: S/MIME cryptographic signature
[JOB] Contract MySQL Specialist (Telecommute)
Contract MySQL Specialist (Telecommute) Los Angeles company looking for a DBA who lives, eats, and breathes MySQL and is familiar with integrating it in a LAMP environment. Ideally someone who is part of that MySQL community, gets their jollies from optimizing it, knows how hardware fits in the mix, spends their free time answering questions in MySQL related blogs, goes to monthly user group meetings for MySQL -- you get the idea. Location: Your home. Telecommute. Prefer to conduct biz/calls during normal biz hours (Pacific Standard Time, California) Compensation: Looking to spend 60-120/hour, but if they are going to pay the high end, they would want someone exceptional (and that's what they REALLY want). Duration: It may be only a couple of hours. After the initial conversation with the consultant they can determine how much more time we'd need. So, if you consider yourself a MySQL Expert, please send me your resume, a paragraph highlighting your MySQL expertise and hourly rate/range to [EMAIL PROTECTED] Thank you, Beau J. Gould Open Source Staffing www.open-source-staffing.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PRINT statement?
>From Transact-SQL Help file: PRINT Returns a user-defined message to the client. Syntax PRINT 'any ASCII text' | @local_variable | @@FUNCTION | string_expr Arguments 'any ASCII text' Is a string of text. @local_variable Is a variable of any valid character data type. @local_variable must be char or varchar, or be able to be implicitly converted to those data types. @@FUNCTION Is a function that returns string results. @@FUNCTION must be char or varchar, or be able to be implicitly converted to those data types. string_expr Is an expression that returns a string. Can include concatenated literal values and variables. The message string can be up to 8,000 characters long; any characters after 8,000 are truncated. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Thursday, 11 May 2006 3:51 p.m. To: Stephen Cook Cc: MySQL List Subject: Re: PRINT statement? I am not familiar with the PRINT command so I don't know what it does. I played with MS SQL Server once for a couple of days a few years back and that is the only contact I've ever had with SQL Server. If you can tell me what PRINT does, in detail, maybe I can suggest another alternative. -- Rhino - Original Message - From: "Stephen Cook" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: "MySQL List" Sent: Wednesday, May 10, 2006 8:09 PM Subject: Re: PRINT statement? > I've started using the SELECT with no other clauses but I am still curious > about a PRINT-like command. It is for SQL scripts. > > Rhino wrote: >> >> - Original Message - From: "Stephen Cook" <[EMAIL PROTECTED]> >> To: "MySQL List" >> Sent: Sunday, May 07, 2006 3:53 AM >> Subject: PRINT statement? >> >> >>> Is there a statement similar to PRINT in T-SQL (MicroSoft SQL Server)? >>> >>> It would be handy to debug some scripts. >>> >> If you're talking about a script that is running SQL, you can simply use >> the SELECT statement without any FROM, WHERE, ORDER BY, GROUP BY or >> HAVING clauses. For example: >> >>select "Creating Foo table" as "Action"; >> >> will produce the following output: >> >>+--+ >>| Action | >>+--+ >>| Creating Foo table | >>+--+ >>1 row in set (0.00 sec) >> >> If you're talking about an OS script, you can use OS commands to display >> things. For example, I have some BASH scripts on our Linux server so I >> can use the BASH echo command, like this: >> >>#!/bin/bash >>report_date=`/bin/date` >>echo "Report Date:" $report_date; >> >> to produce this output: >> >>Report Date: Sun May 7 09:42:57 EDT 2006 >> >> >> -- >> Rhino >> >> >> >> > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006 > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PRINT statement?
I am not familiar with the PRINT command so I don't know what it does. I played with MS SQL Server once for a couple of days a few years back and that is the only contact I've ever had with SQL Server. If you can tell me what PRINT does, in detail, maybe I can suggest another alternative. -- Rhino - Original Message - From: "Stephen Cook" <[EMAIL PROTECTED]> To: "Rhino" <[EMAIL PROTECTED]> Cc: "MySQL List" Sent: Wednesday, May 10, 2006 8:09 PM Subject: Re: PRINT statement? I've started using the SELECT with no other clauses but I am still curious about a PRINT-like command. It is for SQL scripts. Rhino wrote: - Original Message - From: "Stephen Cook" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Sunday, May 07, 2006 3:53 AM Subject: PRINT statement? Is there a statement similar to PRINT in T-SQL (MicroSoft SQL Server)? It would be handy to debug some scripts. If you're talking about a script that is running SQL, you can simply use the SELECT statement without any FROM, WHERE, ORDER BY, GROUP BY or HAVING clauses. For example: select "Creating Foo table" as "Action"; will produce the following output: +--+ | Action | +--+ | Creating Foo table | +--+ 1 row in set (0.00 sec) If you're talking about an OS script, you can use OS commands to display things. For example, I have some BASH scripts on our Linux server so I can use the BASH echo command, like this: #!/bin/bash report_date=`/bin/date` echo "Report Date:" $report_date; to produce this output: Report Date: Sun May 7 09:42:57 EDT 2006 -- Rhino -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is this a bug of stored procedure?
never mind. I found the answer: http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html On 5/10/06, tom soyer <[EMAIL PROTECTED]> wrote: Hi, I tried to pass the name of a table into a procedure and use that in a select statement. Somehow it doesn't work. Here is the code: create procedure sp3(in tablename varchar(10)) begin select count(*) from tablename; end$ When the procedure is called, I got the following error: ERROR 1146 (42S02): Table 'test.tablename' doesn't exist. Does anyone know why my code generated an error? Is this a bug? Thanks, Tom
Is this a bug of stored procedure?
Hi, I tried to pass the name of a table into a procedure and use that in a select statement. Somehow it doesn't work. Here is the code: create procedure sp3(in tablename varchar(10)) begin select count(*) from tablename; end$ When the procedure is called, I got the following error: ERROR 1146 (42S02): Table 'test.tablename' doesn't exist. Does anyone know why my code generated an error? Is this a bug? Thanks, Tom
Is this a bug of stored procedure?
Hi, I tried to pass the name of a table into a procedure and use that in a select statement. Somehow it doesn't work. Here is the code: create procedure sp3(in tablename varchar(10)) begin select count(*) from tablename; end$ When the procedure is called, I got the following error: ERROR 1146 (42S02): Table 'test.tablename' doesn't exist. Does anyone know why my code generated an error? Is this a bug? Thanks, Tom
Re: PRINT statement?
I've started using the SELECT with no other clauses but I am still curious about a PRINT-like command. It is for SQL scripts. Rhino wrote: - Original Message - From: "Stephen Cook" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Sunday, May 07, 2006 3:53 AM Subject: PRINT statement? Is there a statement similar to PRINT in T-SQL (MicroSoft SQL Server)? It would be handy to debug some scripts. If you're talking about a script that is running SQL, you can simply use the SELECT statement without any FROM, WHERE, ORDER BY, GROUP BY or HAVING clauses. For example: select "Creating Foo table" as "Action"; will produce the following output: +--+ | Action | +--+ | Creating Foo table | +--+ 1 row in set (0.00 sec) If you're talking about an OS script, you can use OS commands to display things. For example, I have some BASH scripts on our Linux server so I can use the BASH echo command, like this: #!/bin/bash report_date=`/bin/date` echo "Report Date:" $report_date; to produce this output: Report Date: Sun May 7 09:42:57 EDT 2006 -- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Intermittent deadlock/InnoDB
Version 5.0.19. We have no autogenerate keys. We perform a single insert and get a lock timeout. The insert is done with a stored procedure with a single line. The lockup happens VERY rarely and we have no idea how to reproduce it. Here's the hostname.err data: Alarm status: Active alarms: 0 Max used alarms: 0 Next alarm time: 0 Thread database.table_name Locked/WaitingLock_type 11 te.node Locked - writeConcurrent insert lock And then here is the SHOW INNODB STATUS output. Notice that thread 11 is the one that is WAITING for the lock. However, above it says that it is locked. TRANSACTIONS Trx id counter 0 2912 Purge done for trx's n:o < 0 2834 undo n:o < 0 0 History list length 13 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 1928 MySQL thread id 30, query id 14092 172.18.0.102 root SHOW INNODB STATUS ---TRANSACTION 0 0, not started, OS thread id 1176 MySQL thread id 25, query id 13730 172.18.0.102 root ---TRANSACTION 0 0, not started, OS thread id 3176 MySQL thread id 15, query id 12618 rogerrabbit.tripwire.com 10.150.1.60 root ---TRANSACTION 0 2905, not started, OS thread id 1080 MySQL thread id 13, query id 14038 localhost 127.0.0.1 root ---TRANSACTION 0 2911, ACTIVE 1 sec, OS thread id 2312 inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 320, undo log entries 2 MySQL thread id 11, query id 14091 localhost 127.0.0.1 root update INSERT INTO Node(F_MAKE,F_MODEL,F_VER,oid) VALUES (p_F_MAKE,p_F_MODEL,p_F_VER,p_oid) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 241 n bits 72 index `PRIMARY` of table `te/node` trx id 0 2911 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; -- ---TRANSACTION 0 2791, ACTIVE 4064 sec, OS thread id 3096 2 lock struct(s), heap size 320 MySQL thread id 12, query id 13254 localhost 127.0.0.1 root == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Installing DBD::mysql - problems
Hi Connie, Looks like you have hit http://bugs.mysql.com/bug.php?id=18091 (which hasn't been fixed yet) potential solutions are documented there. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Logg, Connie A. [mailto:[EMAIL PROTECTED] Sent: Thursday, 11 May 2006 5:47 AM To: mysql@lists.mysql.com Subject: Installing DBD::mysql - problems I am trying to install DBD::mysql on the following node: [EMAIL PROTECTED] cal]# uname -a Linux snv1 2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:56:28 EST 2006 x86_64 x86_64 x86_64 GNU/Linux [EMAIL PROTECTED] cal]# more /etc/redhat-release Red Hat Enterprise Linux WS release 4 (Nahant Update 3) [EMAIL PROTECTED] cal]# I have the following rpms installed [EMAIL PROTECTED] cal]# rpm -qa | grep -i mysql MySQL-devel-standard-5.0.20a-0.rhel4 MySQL-client-standard-5.0.20a-0.rhel4 MySQL-shared-standard-5.0.20a-0.rhel4 MySQL-server-standard-5.0.20a-0.rhel4 And DBI installed ok. [EMAIL PROTECTED] utils]$ ./seeperlmods Archive::Tar -- 1.29 CPAN -- 1.87 Compress::Zlib -- 1.41 Cwd -- 3.17 DBD::Multiplex -- 1.98 DBI -- 1.50 <-- DBI::Shell -- 11.93 Data::ShowTable -- undef Digest::MD5 -- 2.36 IO::Tee -- 0.64 IO::Zlib -- 1.04 Net::Daemon -- 0.39 Net::Telnet -- 3.03 Perl -- 5.8.5 RPC::PlServer -- 0.2018 Storable -- 2.15 Term::ReadKey -- 2.30 Term::ReadLine -- 1.01 Text::Reform -- 1.11 When I try to install the DBD::mysql I get errors: [EMAIL PROTECTED] DBD-mysql-3.0002_5]# /usr/bin/perl Makefile.PL I will use the following settings for compiling and testing: cflags(mysql_config) = -I/usr/include/mysql -g -pipe -m64 embedded (mysql_config) = libs (mysql_config) = -L/usr/lib64/mysql -lmysqlclient -lz -lcrypt -lnsl -lm mysql_config (guessed ) = mysql_config nocatchstderr (default ) = 0 nofoundrows (default ) = 0 ps-protocol (default ) = 1 ssl (guessed ) = 0 testdb(default ) = test testhost (default ) = testpassword (default ) = testsocket(default ) = testuser (default ) = To change these settings, see 'perl Makefile.PL --help' and 'perldoc INSTALL'. Multiple copies of Driver.xst found in: /usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ / usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ at Makefile.PL line 741 Using DBI 1.50 (for perl 5.008005 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/site_perl/5.8.5/ x86_64-linux-thread-multi/auto/DBI/ Writing Makefile for DBD::mysql probably ok as it picked one When I run make: [EMAIL PROTECTED] DBD-mysql-3.0002_5]# make gcc -c -I/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ -I/usr/include/mysql -g -pipe - m64 -DDBD_MYSQL_INSERT_ID_IS_GOOD -g -D_REENTRANT -D_GNU_SOURCE -DDEBUGGING -fno-strict-aliasing -pipe -I/usr /local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -m64 -DVERSION=\" 3.0002_5\" -DXS_VERSION=\"3.0002_5\" -fPIC "-I/usr/lib64/perl5/5.8.5/x86_64-linux-thread-multi/CORE" dbdimp. c gcc -c -I/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ -I/usr/include/mysql -g -pipe - m64 -DDBD_MYSQL_INSERT_ID_IS_GOOD -g -D_REENTRANT -D_GNU_SOURCE -DDEBUGGING -fno-strict-aliasing -pipe -I/usr /local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -m64 -DVERSION=\" 3.0002_5\" -DXS_VERSION=\"3.0002_5\" -fPIC "-I/usr/lib64/perl5/5.8.5/x86_64-linux-thread-multi/CORE" mysql.c Running Mkbootstrap for DBD::mysql () chmod 644 mysql.bs rm -f blib/arch/auto/DBD/mysql/mysql.so /usr/bin/perl myld gcc -shared dbdimp.o mysql.o -o blib/arch/auto/DBD/mysql/mysql.so -L/usr/lib64/mysql -l mysqlclient -lz -lcrypt -lnsl -lm /usr/bin/ld: /usr/lib64/mysql/libmysqlclient.a(libmysql.o): relocation R_X86_64_32 against `a local symbol' ca n not be used when making a shared object; recompile with -fPIC /usr/lib64/mysql/libmysqlclient.a: could not read symbols: Bad value collect2: ld returned 1 exit status make: *** [blib/arch/auto/DBD/mysql/mysql.so] Error 1 [EMAIL PROTECTED] DBD-mysql-3.0002_5]# I tried using /usr/bin/perl Makefile.PL --cflags fPIC And doing the make afterword, but that failed miserably with many many errors snip dbdi
Installing DBD::mysql - problems
I am trying to install DBD::mysql on the following node: [EMAIL PROTECTED] cal]# uname -a Linux snv1 2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:56:28 EST 2006 x86_64 x86_64 x86_64 GNU/Linux [EMAIL PROTECTED] cal]# more /etc/redhat-release Red Hat Enterprise Linux WS release 4 (Nahant Update 3) [EMAIL PROTECTED] cal]# I have the following rpms installed [EMAIL PROTECTED] cal]# rpm -qa | grep -i mysql MySQL-devel-standard-5.0.20a-0.rhel4 MySQL-client-standard-5.0.20a-0.rhel4 MySQL-shared-standard-5.0.20a-0.rhel4 MySQL-server-standard-5.0.20a-0.rhel4 And DBI installed ok. [EMAIL PROTECTED] utils]$ ./seeperlmods Archive::Tar -- 1.29 CPAN -- 1.87 Compress::Zlib -- 1.41 Cwd -- 3.17 DBD::Multiplex -- 1.98 DBI -- 1.50 <-- DBI::Shell -- 11.93 Data::ShowTable -- undef Digest::MD5 -- 2.36 IO::Tee -- 0.64 IO::Zlib -- 1.04 Net::Daemon -- 0.39 Net::Telnet -- 3.03 Perl -- 5.8.5 RPC::PlServer -- 0.2018 Storable -- 2.15 Term::ReadKey -- 2.30 Term::ReadLine -- 1.01 Text::Reform -- 1.11 When I try to install the DBD::mysql I get errors: [EMAIL PROTECTED] DBD-mysql-3.0002_5]# /usr/bin/perl Makefile.PL I will use the following settings for compiling and testing: cflags(mysql_config) = -I/usr/include/mysql -g -pipe -m64 embedded (mysql_config) = libs (mysql_config) = -L/usr/lib64/mysql -lmysqlclient -lz -lcrypt -lnsl -lm mysql_config (guessed ) = mysql_config nocatchstderr (default ) = 0 nofoundrows (default ) = 0 ps-protocol (default ) = 1 ssl (guessed ) = 0 testdb(default ) = test testhost (default ) = testpassword (default ) = testsocket(default ) = testuser (default ) = To change these settings, see 'perl Makefile.PL --help' and 'perldoc INSTALL'. Multiple copies of Driver.xst found in: /usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ / usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ at Makefile.PL line 741 Using DBI 1.50 (for perl 5.008005 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/site_perl/5.8.5/ x86_64-linux-thread-multi/auto/DBI/ Writing Makefile for DBD::mysql probably ok as it picked one When I run make: [EMAIL PROTECTED] DBD-mysql-3.0002_5]# make gcc -c -I/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ -I/usr/include/mysql -g -pipe - m64 -DDBD_MYSQL_INSERT_ID_IS_GOOD -g -D_REENTRANT -D_GNU_SOURCE -DDEBUGGING -fno-strict-aliasing -pipe -I/usr /local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -m64 -DVERSION=\" 3.0002_5\" -DXS_VERSION=\"3.0002_5\" -fPIC "-I/usr/lib64/perl5/5.8.5/x86_64-linux-thread-multi/CORE" dbdimp. c gcc -c -I/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBI/ -I/usr/include/mysql -g -pipe - m64 -DDBD_MYSQL_INSERT_ID_IS_GOOD -g -D_REENTRANT -D_GNU_SOURCE -DDEBUGGING -fno-strict-aliasing -pipe -I/usr /local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -m64 -DVERSION=\" 3.0002_5\" -DXS_VERSION=\"3.0002_5\" -fPIC "-I/usr/lib64/perl5/5.8.5/x86_64-linux-thread-multi/CORE" mysql.c Running Mkbootstrap for DBD::mysql () chmod 644 mysql.bs rm -f blib/arch/auto/DBD/mysql/mysql.so /usr/bin/perl myld gcc -shared dbdimp.o mysql.o -o blib/arch/auto/DBD/mysql/mysql.so -L/usr/lib64/mysql -l mysqlclient -lz -lcrypt -lnsl -lm /usr/bin/ld: /usr/lib64/mysql/libmysqlclient.a(libmysql.o): relocation R_X86_64_32 against `a local symbol' ca n not be used when making a shared object; recompile with -fPIC /usr/lib64/mysql/libmysqlclient.a: could not read symbols: Bad value collect2: ld returned 1 exit status make: *** [blib/arch/auto/DBD/mysql/mysql.so] Error 1 [EMAIL PROTECTED] DBD-mysql-3.0002_5]# I tried using /usr/bin/perl Makefile.PL --cflags fPIC And doing the make afterword, but that failed miserably with many many errors snip dbdimp.c:3017: error: dereferencing pointer to incomplete type dbdimp.c: In function `mysql_describe': dbdimp.c:3126: error: dereferencing pointer to incomplete type dbdimp.c: In function `mysql_st_fetch': dbdimp.c:3151: error: `MYSQL_ROW' undeclared (first use in this function) dbdimp.c:3151: error: syntax error before "cols" dbdimp.c:3156: error: dereferencing pointer to incomplete type I am at a loss how to resolve this. I would appreciate any guidance. I have also tried DBD-mysql-3.0002_4 and DBD-mysql-3.0002_3 Connie Logg Connie Logg, Network Analyst Stanford Linear Accelerator Center ph: 650-926-2879 "Happiness is found along the way, not at the end of the road, and 'IF' is the middle word in life." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search for term 'c++'
I think the '+' will not get into the index in the first place. So, there's
probably no way to get a search to use the index. However if you want a
consistant query format for your search you could probably get the correct
result by wrapping the search term in double quotes:
select f1 from t1 where match(f1) against ('"c++"' in boolean mode);
HTH,
James
At 12:30 pm -0700 10/5/06, klute wrote:
>Is there any way I can search for a term such as 'c++'
>using a fulltext search index?
>select f1 from t1 where match(f1) against('c++' in
>boolean mode);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 5 / phpMyAdmin - SOLVED
Thanks to Ing. Edwin Cruz and George Law for pointing me in the right direction, as previous messages in this thread indicate. phpMyAdmin now working great with MySQL 5.0. Have a loot on this: http://dev.mysql.com/doc/refman/5.0/en/old-client.html try a search for "old_password" -- Amer Neely Home of Spam Catcher W: www.softouch.on.ca E: [EMAIL PROTECTED] Perl | MySQL | CGI programming for all data entry forms. "We make web sites work!" -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext search for term 'c++'
Hello all,
Is there any way I can search for a term such as 'c++'
using a fulltext search index?
Here is the query that should return rows but it does
not. I suspect that the trailing "++" are treated as
wildcards and are not fulltext indexed by mysql. It
seems like mysql interprets the search string to be
just a single character ('c') and ignores the query
term since my mysql server is configured to ignore
searches for keywords shorter than 3 characters.
select f1 from t1 where match(f1) against('c++' in
boolean mode);
Any help would be greatly appreciated
James
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql workbench installation issues
Hello, I am trying to install wokbench on Linux box I am running Debian (SID). First I downloaded the file mysql-workbench-1.0.6beta-linux-i386.tar.gz, then I did: 1. $ tar xvzf mysql-workbench-1.0.6beta-linux-i386.tar.gz 2. In the created folder I ran bin$ sh mysql-workbench 3. Nothing happens, and I got several messages, these are the last ones: (mysql-workbench-bin:3083): GLib-GObject-CRITICAL **: g_type_register_static: assertion `parent_type > 0' failed mysql-workbench: line 18: 3083 Segmentation fault $PRG-bin $* 4. Then, trying to work around this I checked my libraries and everything needed is installed like gcc 4.x and glibc-2.2 is installed too. So I modifies the file located at ~/mysql-workbench/share/applications/MySQLWorkbench.desktop to point the file located on ~/mysql-workbench/bin/ then I opened konqueror click to the file MySQLWorkbench.deskto I get feedback from the cursor seconds later it stops bouncing a no application is loaded. What am doing wrong? What am missing? Because the above didn't work I tried the following: 1. Got the file mysql-workbench-1.0.6beta-1.i386.rpm 2. Convert it to a deb package and installed it with # alien -i mysql-workbench-1.0.6beta-1.i386.rpm 3. I can see the shortcut on the main KDE menu and it looks like is loading, but nothing happens, if I do a # dpkg -l '*mysql*' the package is listed and installed, also if I try #dpkg -l mysql-workbench I get this: Desired=Unknown/Install/Remove/Purge/Hold | Status=Not/Installed/Config-files/Unpacked/Failed-config/Half-installed |/ Err?=(none)/Hold/Reinst-required/X=both-problems (Status,Err: uppercase=bad) ||/ Name VersionDescription +++-==-==- ii mysql-workbench1.0.6beta-2A MySQL visual modeling tool. So I don't know what going on, any thoughts Thank you Ivan -- Iván Alemán ~ [[ m o f o ]] ~ Debian (SID) -BEGIN GEEK CODE BLOCK- Version: 3.12 G!>GCM 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+ z*>*$ --END GEEK CODE BLOCK-- bonovoxmofo.blogspot.com 6EA4 BC00 420B 2087 C546 32D6 1258 3492 4220 7E8B
RE: Can I set UNIX_TIMESTAMP() as default?
Nicholas, just found something on google: "In general, this cannot be done. Default values cannot be the return of a MySQL function (as much as I'd love to use NOW() for default values!). However, there's one loophole. When inserting, not specifying a value for the first timestamp field in a table will generate the current timestamp. " Best work around I can think of is to set your field as an int and include unix_timestamp(NOW()) in your inserts -Original Message- From: Nicolas Verhaeghe [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 10, 2006 2:33 PM To: mysql@lists.mysql.com Subject: Can I set UNIX_TIMESTAMP() as default? I would like an integer field to capture the current date as a Unix Timestamp by default. But this will not be accepted at all. I get the error "invalid default value for [field name]" Is there a workaround? 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]
Re: Sum of counts
You could you use UNION to make this all execute in a single query. On 5/10/06, Rhino <[EMAIL PROTECTED]> wrote: Hi Chris, Joerg, and everyone else following this discussion, Joerg, you are correct; the best way to sum the tables is individually and then add the sums together with program logic of some kind, such as might be found in a script or application program or stored procedure. I'm afraid I jumped in and gave correct but irrelevant information. I saw that Chris's query lacked joining conditions so I explained why they were needed and how to write them. Unfortunately, this was premature: I should have thought about the basic problem more carefully first. Joerg, you are absolutely right: if one table contains students and another contains teachers, you don't count the number of people in the school by JOINING the tables together. You count the people in each table separately and add the two sums together. I don't know why that didn't come to me when I read Chris's question but it didn't. My apologies to all for wasting your time with an inappropriate solution. I'll try not to do that again! -- Rhino - Original Message - From: "Joerg Bruehe" <[EMAIL PROTECTED]> To: "Chris Sansom" <[EMAIL PROTECTED]> Cc: "Rhino" <[EMAIL PROTECTED]>; "MySQL List" Sent: Wednesday, May 10, 2006 12:04 PM Subject: Re: Sum of counts > Hi Chris, all, > > > Re-inserting Chris' original question: > | I want to get a total of entries from four tables which all match a > | particular id. The result for the id I'm testing (21) should be 233. > | In my naivety, I thought something like this would work: > | > | select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id) > | from table_a as a, table_b as b, table_c as c, table_d as d > | where a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21 > > > IMO, this is simply no task for a join, > probably not a task for any single SQL statement. > > The easiest way is to have four separate "SELECT count(*) FROM table_?" > with the '?' replaced by 'a' .. 'd'. > > > More explanations below: > > > Chris Sansom wrote: >> At 13:28 -0400 9/5/06, Rhino wrote: >>> The reason you are getting so many rows has nothing to do with the way >>> you are using the count(*) function and adding the different count() >>> results together. The problem is that you are doing your joins >>> incorrectly... In your case, I think you need to change the original >>> query to this: >>> >>>select count(a.id) + count(b.id) + count(c.id) + count(d.id) >>>from table_a as a, table_b as b, table_c as c, table_d as d >>>where a.id = b.id >>>and b.id = c.id >>>and c.id = d.id >>>and a.id = 21 >>>and b.id = 21 >>>and c.id = 21 >>>and d.id = 21 > > First, the transitive equality on the 4 "id" columns (first 3 conditions) > together with one restriction to 21 (say, on "a.id") > has no different effect than the 4 conditions "= 21", > in mathematical view 3 of these 7 conditions can be dropped. > (Not "any 3", but several different combinations.) > > But that is not the cause of the problem - this is the join approach: > Remember that a join does a cartesian product, this is in no way helpful > to the solution of your task! > > > Let us construct a minimized example: Just two tables, each with three > rows, all having that magic value 21: > > Table aTable b > id cntid cnt > 21 1 21 4 > 21 2 21 5 > 21 3 21 6 > > Doing a natural join on the "id" column will yield 9 rows: > > a.id a.cnt b.id b.cnt > 211 214 > 211 215 > 211 216 > 212 214 > 212 215 > 212 216 > 213 214 > 213 215 > 213 216 > > Summing "a.id" and "b.id" results in 18, where the correct value is 6. > > >> >> Hi Rhino >> >> Many thanks for the very full and frank response, but sadly it didn't >> work. I do understand exactly what you said, and I even took it further, >> adding in: >> >> and a.id = c.id >> and a.id = d.id >> and b.id = d.id >> >> ...so that every table is thus related to every other one, but I'm >> /still/ getting that damned eight and a half million instead of the 233 I >> expect! > > See above - > demanding all 4 columns to be equal to 21 > is equivalent to demanding one is 21, and all are equal, > and also equivalent to some other combinations of conditions. > > If you have a mathematical education, apply your knowledge of > "transitivity" to the problem. > >> >> I'm baffled by this, though the version I did with subqueries works very >> nicely (and it's simple enough to do four separate queries and add them >> together in the script for the older MySQL). > > Frankly spoken: This is the way to go! > > > From your problem description, there seems to be no connection between the > tables that would warrant joining them. > > For a very coarse analogy: > If you have separate tables for the teachers and the pupils of a school, > and wa
Can I set UNIX_TIMESTAMP() as default?
I would like an integer field to capture the current date as a Unix Timestamp by default. But this will not be accepted at all. I get the error "invalid default value for [field name]" Is there a workaround? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL:2003 standard
Hello Peng, please have a look at http://www.wiscorp.com/SQLStandards.html There you can download all you need. With best regards, Roland. Peter Brawley wrote on 10.05.2006 16:51: > Peng Yi-fan wrote: >> Hi, >> >> It seems that ISO do not support SQL:2003 standard for free but I really >> need a copy. Does anyone know where I can download it? Or does anyone have >> it? >> Any type will be just OK. >> > There is a 2002 draft at > http://xml.coverpages.org/SQLX-5wd-14-xml-2002-08.pdf. > > PB >> Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 5 / phpMyAdmin
Have a loot on this: http://dev.mysql.com/doc/refman/5.0/en/old-client.html Regards! -Mensaje original- De: Amer Neely [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 10 de Mayo de 2006 12:39 p.m. Para: MySQL List Asunto: MySQL 5 / phpMyAdmin I've finally got MySQL 5.0 going on my Win2K machine, but phpMyAdmin 2.8.0.3 is now giving me grief. It gives me this error: phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. #1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client I'm using the same phpMyAdmin config file as when I had a previous version of MySQL installed, so I'm stumped on what could be the hangup. How do I upgrade my MySQL client? phpinfo tells me the API is 3.23.49. $cfg['Servers'][$i]['auth_type'] = 'config'; I'm not even sure this is a MySQL question, but if someone can provide a clue for me that would be great. -- Amer Neely Home of Spam Catcher W: www.softouch.on.ca E: [EMAIL PROTECTED] Perl | MySQL | CGI programming for all data entry forms. "We make web sites work!" -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 5 / phpMyAdmin
try a search for "old_password" -Original Message- From: Amer Neely [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 10, 2006 1:39 PM To: MySQL List Subject: MySQL 5 / phpMyAdmin I've finally got MySQL 5.0 going on my Win2K machine, but phpMyAdmin 2.8.0.3 is now giving me grief. It gives me this error: phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. #1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client I'm using the same phpMyAdmin config file as when I had a previous version of MySQL installed, so I'm stumped on what could be the hangup. How do I upgrade my MySQL client? phpinfo tells me the API is 3.23.49. $cfg['Servers'][$i]['auth_type'] = 'config'; I'm not even sure this is a MySQL question, but if someone can provide a clue for me that would be great. -- Amer Neely Home of Spam Catcher W: www.softouch.on.ca E: [EMAIL PROTECTED] Perl | MySQL | CGI programming for all data entry forms. "We make web sites work!" -- 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 5 / phpMyAdmin
I've finally got MySQL 5.0 going on my Win2K machine, but phpMyAdmin 2.8.0.3 is now giving me grief. It gives me this error: phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. #1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client I'm using the same phpMyAdmin config file as when I had a previous version of MySQL installed, so I'm stumped on what could be the hangup. How do I upgrade my MySQL client? phpinfo tells me the API is 3.23.49. $cfg['Servers'][$i]['auth_type'] = 'config'; I'm not even sure this is a MySQL question, but if someone can provide a clue for me that would be great. -- Amer Neely Home of Spam Catcher W: www.softouch.on.ca E: [EMAIL PROTECTED] Perl | MySQL | CGI programming for all data entry forms. "We make web sites work!" -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sum of counts
Hi Chris, Joerg, and everyone else following this discussion, Joerg, you are correct; the best way to sum the tables is individually and then add the sums together with program logic of some kind, such as might be found in a script or application program or stored procedure. I'm afraid I jumped in and gave correct but irrelevant information. I saw that Chris's query lacked joining conditions so I explained why they were needed and how to write them. Unfortunately, this was premature: I should have thought about the basic problem more carefully first. Joerg, you are absolutely right: if one table contains students and another contains teachers, you don't count the number of people in the school by JOINING the tables together. You count the people in each table separately and add the two sums together. I don't know why that didn't come to me when I read Chris's question but it didn't. My apologies to all for wasting your time with an inappropriate solution. I'll try not to do that again! -- Rhino - Original Message - From: "Joerg Bruehe" <[EMAIL PROTECTED]> To: "Chris Sansom" <[EMAIL PROTECTED]> Cc: "Rhino" <[EMAIL PROTECTED]>; "MySQL List" Sent: Wednesday, May 10, 2006 12:04 PM Subject: Re: Sum of counts Hi Chris, all, Re-inserting Chris' original question: | I want to get a total of entries from four tables which all match a | particular id. The result for the id I'm testing (21) should be 233. | In my naivety, I thought something like this would work: | | select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id) | from table_a as a, table_b as b, table_c as c, table_d as d | where a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21 IMO, this is simply no task for a join, probably not a task for any single SQL statement. The easiest way is to have four separate "SELECT count(*) FROM table_?" with the '?' replaced by 'a' .. 'd'. More explanations below: Chris Sansom wrote: At 13:28 -0400 9/5/06, Rhino wrote: The reason you are getting so many rows has nothing to do with the way you are using the count(*) function and adding the different count() results together. The problem is that you are doing your joins incorrectly... In your case, I think you need to change the original query to this: select count(a.id) + count(b.id) + count(c.id) + count(d.id) from table_a as a, table_b as b, table_c as c, table_d as d where a.id = b.id and b.id = c.id and c.id = d.id and a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21 First, the transitive equality on the 4 "id" columns (first 3 conditions) together with one restriction to 21 (say, on "a.id") has no different effect than the 4 conditions "= 21", in mathematical view 3 of these 7 conditions can be dropped. (Not "any 3", but several different combinations.) But that is not the cause of the problem - this is the join approach: Remember that a join does a cartesian product, this is in no way helpful to the solution of your task! Let us construct a minimized example: Just two tables, each with three rows, all having that magic value 21: Table aTable b id cntid cnt 21 1 21 4 21 2 21 5 21 3 21 6 Doing a natural join on the "id" column will yield 9 rows: a.id a.cnt b.id b.cnt 211 214 211 215 211 216 212 214 212 215 212 216 213 214 213 215 213 216 Summing "a.id" and "b.id" results in 18, where the correct value is 6. Hi Rhino Many thanks for the very full and frank response, but sadly it didn't work. I do understand exactly what you said, and I even took it further, adding in: and a.id = c.id and a.id = d.id and b.id = d.id ...so that every table is thus related to every other one, but I'm /still/ getting that damned eight and a half million instead of the 233 I expect! See above - demanding all 4 columns to be equal to 21 is equivalent to demanding one is 21, and all are equal, and also equivalent to some other combinations of conditions. If you have a mathematical education, apply your knowledge of "transitivity" to the problem. I'm baffled by this, though the version I did with subqueries works very nicely (and it's simple enough to do four separate queries and add them together in the script for the older MySQL). Frankly spoken: This is the way to go! From your problem description, there seems to be no connection between the tables that would warrant joining them. For a very coarse analogy: If you have separate tables for the teachers and the pupils of a school, and want to know the number of all peoples going there daily, you will not join these two tables, you will rather count them individually and then add these two values. HTH, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mys
Re: slow query
I think you are right about O log N performance when finding matching records within the index - however, the index doesn't contain all the data, so the mysql server has to hit the table as stored on disk to find what you were actually asking for (select *). That becomes time consuming as it sifts through all the data from disk to retrieve matching records starting and ending at the right points. Note in your EXPLAIN output that MySQL thinks it will have to examine about 506222 rows to find the matching one(s); that's a pretty large number of rows to examine (half your table). That's where having a wider distribution of user_id values would help, I think. Of course, you haven't got that wider distribution, so that's a pointless discussion (I suggested looking for other user_ids earlier merely to confirm whether such queries were faster). Not that this solves your problem, but try SELECT user_id, fullname FROM contacts WHERE user_id=1 ORDER BY fullname LIMIT 1 OFFSET 50; as this should not hit the table on disk, only the index, since the user_id_2 index IS the data for this query. I'm more familiar with MyISAM than InnoDB but I think this holds true for both table types. Dan Adam Wolff wrote: Thanks for the response, Dan. I did try ORDER BY on the table. Didn't help -- I presume because the query is using an index. Unfortunately, the point of my current development is to show searches against millions of contacts, so the suggestion about working with other user_ids isn't too practical. I will look into increasing the size of my data cache. I guess what surprises me is that I thought that the index was stored as a BTree in sort order. I'm pretty bad with big-O, but I thought this would suggest O log N performance to find a given offset within the index. A On May 10, Dan Buettner wrote: I would expect the problem to be that the further down in the data you go by using OFFSET, the more records the mysql server has to scan in order to get to what you want. This will produce a fairly linear slowdown the further in you go - it just takes time to check through 1,000,000 matching records. Especially on desktop grade hardware where you probably haven't got the fastest disk subsystem. I think in this case your slow searches may be a result of the heavy bias in your data toward user_id 1. Try your search on some of your other user_ids and see. With so many records for the same user_id, your search for that user_id is necessitating something pretty close to a table scan, even though it's hitting an index. Some suggestions would be to increase the size of your data cache, so that after your first queries, the data (or more of it) is in memory. Assuming you'll be deploying on server hardware, a faster disk system should help quite a bit too. Memory caches on hardware RAID systems can help with this kind of thing too. You might also try ALTER TABLE contacts ORDER BY user_id, fullname to get your data sorted into the same order you're looking through it, though it may well affect other queries you need to run against the same data. I'm not certain whether you can ORDER BY more than one column: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html Also note that as you add or delete rows the table does not stay in order. Hope this helps! Dan Adam Wolff wrote: I have a very simple table that looks like this: CREATE TABLE `contacts` ( `id` int(11) NOT NULL auto_increment, `fullname` varchar(100) default NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `user_id_2` (`user_id`,`fullname`), CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 It's a bit of a lopsided table in that of the 1,000,100 records in the db, 1,000,000 of them belong to user_id 1. But I wouldn't expect this to skew my results. I am writing a little paging server that retrieves pages of data using LIMIT and OFFSET. I'm really surprised by how slowly my queries are running on a relatively fast desktop machine. Records near the top of the list are fine: mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname LIMIT 1 OFFSET 0; ++--+-+-+--+ | id | fullname | email | user_id | nickname | ++--+-+-+--+ | 371543 | Aaron Abbott | [EMAIL PROTECTED] | 1 | aaronab | ++--+-+-+--+ 1 row in set (0.03 sec) But as I move down the list, queries run slower and slower: mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname LIMIT 1 OFFSET 10; ++--+-+-+--+ | id | fullname | email | user_id | nickname | ++--+-+-
Re: Reporting child tables referencing a parnet table
Ben Clewett wrote: Unfortunately I get: mysql> SELECT -> c.table_schema,u.table_name,u.column_name,u.referenced_column_name -> FROM information_schema.table_constraints AS c -> INNER JOIN information_schema.key_column_usage AS u -> USING( constraint_schema, constraint_name ) -> WHERE c.constraint_type = 'FOREIGN KEY' -> AND u.referenced_table_schema='db' -> AND u.referenced_table_name = 'table' -> ORDER BY c.table_schema,u.table_name; Empty set (27.86 sec) Indeed, you didn't substitute your 'db' and 'table' values. PB - mysql> SELECT version(); +-+ | version() | +-+ | 5.1.6-alpha-log | +-+ But thanks, I think I can work with this and get the information I want. Regards, Ben. Peter Brawley wrote: Ben,: Dear MySQL, Can you please tell me if there is a way of listing all child tables which have a foreign key reference to a parent? Find children of db.table: SELECT c.table_schema,u.table_name,u.column_name,u.referenced_column_name FROM information_schema.table_constraints AS c INNER JOIN information_schema.key_column_usage AS u USING( constraint_schema, constraint_name ) WHERE c.constraint_type = 'FOREIGN KEY' AND u.referenced_table_schema='db' AND u.referenced_table_name = 'table' ORDER BY c.table_schema,u.table_name; PB - Therefore I can find and delete a child row, then delete the parent without getting: "a foreign key constraint fails" Regards, Ben Clewett -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 5/9/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unknown option --install
I am trying to install two MySQL servers to run as Windows XP services. One for work with php-gtk+ as mysqld1, MySQL 3.23.55 on port 3306 and another as mysqld2, MySQL 5.0.20 on port 3308 for PHP5 development and testing. I'm following the manual's instructions in section 5.13.1.2. Starting Multiple Windows Servers as Services found at http://dev.mysql.com/doc/refman/5.0/en/multiple-windows-services.html The installation of mysqd1 went fine, but I consistently get an error when trying to install mysqld2, like so: C:\PROGRA~1\xampp\mysql\bin>mysqld-nt --install mysqld2 --defaults-file=C:/Program Files/xampp/mysql/bin/my_opts5.cnf Which returns this error: 060510 12:18:34 [ERROR] mysqld-nt: unknown option '--install' Huh? It's listed as one of the parameters after issuing mysqld-nt --help --verbose. More background: The previous instances of MySQL services have been removed. Have tried both forward "/" and back "\" slashes in the defaults-file path Console window has been closed and reopened. mysql.ini in the \Windows directory has been renamed to mysql.ini.old Does anyone have any suggestions? They will be most welcome. Regards - Miles Thompson -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 5/9/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sum of counts
Hi Chris, all, Re-inserting Chris' original question: | I want to get a total of entries from four tables which all match a | particular id. The result for the id I'm testing (21) should be 233. | In my naivety, I thought something like this would work: | | select count(a.id) + count(b.id) + count(c.id) + count(d.guide_id) | from table_a as a, table_b as b, table_c as c, table_d as d | where a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21 IMO, this is simply no task for a join, probably not a task for any single SQL statement. The easiest way is to have four separate "SELECT count(*) FROM table_?" with the '?' replaced by 'a' .. 'd'. More explanations below: Chris Sansom wrote: At 13:28 -0400 9/5/06, Rhino wrote: The reason you are getting so many rows has nothing to do with the way you are using the count(*) function and adding the different count() results together. The problem is that you are doing your joins incorrectly... In your case, I think you need to change the original query to this: select count(a.id) + count(b.id) + count(c.id) + count(d.id) from table_a as a, table_b as b, table_c as c, table_d as d where a.id = b.id and b.id = c.id and c.id = d.id and a.id = 21 and b.id = 21 and c.id = 21 and d.id = 21 First, the transitive equality on the 4 "id" columns (first 3 conditions) together with one restriction to 21 (say, on "a.id") has no different effect than the 4 conditions "= 21", in mathematical view 3 of these 7 conditions can be dropped. (Not "any 3", but several different combinations.) But that is not the cause of the problem - this is the join approach: Remember that a join does a cartesian product, this is in no way helpful to the solution of your task! Let us construct a minimized example: Just two tables, each with three rows, all having that magic value 21: Table aTable b id cntid cnt 21 1 21 4 21 2 21 5 21 3 21 6 Doing a natural join on the "id" column will yield 9 rows: a.id a.cnt b.id b.cnt 211 214 211 215 211 216 212 214 212 215 212 216 213 214 213 215 213 216 Summing "a.id" and "b.id" results in 18, where the correct value is 6. Hi Rhino Many thanks for the very full and frank response, but sadly it didn't work. I do understand exactly what you said, and I even took it further, adding in: and a.id = c.id and a.id = d.id and b.id = d.id ...so that every table is thus related to every other one, but I'm /still/ getting that damned eight and a half million instead of the 233 I expect! See above - demanding all 4 columns to be equal to 21 is equivalent to demanding one is 21, and all are equal, and also equivalent to some other combinations of conditions. If you have a mathematical education, apply your knowledge of "transitivity" to the problem. I'm baffled by this, though the version I did with subqueries works very nicely (and it's simple enough to do four separate queries and add them together in the script for the older MySQL). Frankly spoken: This is the way to go! From your problem description, there seems to be no connection between the tables that would warrant joining them. For a very coarse analogy: If you have separate tables for the teachers and the pupils of a school, and want to know the number of all peoples going there daily, you will not join these two tables, you will rather count them individually and then add these two values. HTH, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow query
Thanks for the response, Dan. I did try ORDER BY on the table. Didn't help -- I presume because the query is using an index. Unfortunately, the point of my current development is to show searches against millions of contacts, so the suggestion about working with other user_ids isn't too practical. I will look into increasing the size of my data cache. I guess what surprises me is that I thought that the index was stored as a BTree in sort order. I'm pretty bad with big-O, but I thought this would suggest O log N performance to find a given offset within the index. A On May 10, Dan Buettner wrote: > I would expect the problem to be that the further down in the data you go by > using OFFSET, the more records the mysql server has to scan in order to get to > what you want. This will produce a fairly linear slowdown the further in you > go - it just takes time to check through 1,000,000 matching records. > Especially on desktop grade hardware where you probably haven't got the > fastest disk subsystem. > > I think in this case your slow searches may be a result of the heavy bias in > your data toward user_id 1. Try your search on some of your other user_ids > and see. With so many records for the same user_id, your search for that > user_id is necessitating something pretty close to a table scan, even though > it's hitting an index. > > Some suggestions would be to increase the size of your data cache, so that > after your first queries, the data (or more of it) is in memory. Assuming > you'll be deploying on server hardware, a faster disk system should help quite > a bit too. Memory caches on hardware RAID systems can help with this kind of > thing too. > > You might also try > ALTER TABLE contacts ORDER BY user_id, fullname > to get your data sorted into the same order you're looking through it, though > it may well affect other queries you need to run against the same data. I'm > not certain whether you can ORDER BY more than one column: > http://dev.mysql.com/doc/refman/5.0/en/alter-table.html > Also note that as you add or delete rows the table does not stay in order. > > Hope this helps! > > Dan > > > Adam Wolff wrote: > > I have a very simple table that looks like this: > > CREATE TABLE `contacts` ( > > `id` int(11) NOT NULL auto_increment, > > `fullname` varchar(100) default NULL, > > `user_id` int(11) NOT NULL, > > PRIMARY KEY (`id`), > > KEY `user_id` (`user_id`), > > KEY `user_id_2` (`user_id`,`fullname`), > > CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` > > (`id`) > > ENGINE=InnoDB DEFAULT CHARSET=utf8 > > > > > > It's a bit of a lopsided table in that of the 1,000,100 records in the db, > > 1,000,000 of them belong to user_id 1. But I wouldn't expect this to > > skew my results. > > > > I am writing a little paging server that retrieves pages of data using > > LIMIT and OFFSET. > > > > I'm really surprised by how slowly my queries are running on a > > relatively fast desktop machine. Records near the top of the list are > > fine: > > mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname > > LIMIT 1 OFFSET 0; > > ++--+-+-+--+ > > > > | id | fullname | email | user_id | nickname | > > ++--+-+-+--+ > > > > | 371543 | Aaron Abbott | [EMAIL PROTECTED] | 1 | aaronab | > > ++--+-+-+--+ > > > > 1 row in set (0.03 sec) > > > > But as I move down the list, queries run slower and slower: > > mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname > > LIMIT 1 OFFSET 10; > > ++--+-+-+--+ > > > > | id | fullname | email | user_id | nickname | > > ++--+-+-+--+ > > > > | 726543 | Benny Abbott | [EMAIL PROTECTED] | 1 | bennyab | > > ++--+-+-+--+ > > > > 1 row in set (2.94 sec) > > > > mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname > > LIMIT 1 OFFSET 50; > > ++---+--+-+--+ > > > > | id | fullname | email| user_id | nickname > > | > > ++---+--+-+--+ > > > > | 309543 | Jimmie Abbott | [EMAIL PROTECTED] | 1 | jimmieab > > | > > ++---+--+-+--+ > > > > 1 row in set (12.75 sec) > > > > EXPLAIN says: > > ++-+--+--+---+---+-+---++-+ > > > > | id | select_type | table| type | possible_keys | key | > > key_len | ref
Re: Reporting child tables referencing a parnet table
Unfortunately I get: mysql> SELECT -> c.table_schema,u.table_name,u.column_name,u.referenced_column_name -> FROM information_schema.table_constraints AS c -> INNER JOIN information_schema.key_column_usage AS u -> USING( constraint_schema, constraint_name ) -> WHERE c.constraint_type = 'FOREIGN KEY' -> AND u.referenced_table_schema='db' -> AND u.referenced_table_name = 'table' -> ORDER BY c.table_schema,u.table_name; Empty set (27.86 sec) mysql> SELECT version(); +-+ | version() | +-+ | 5.1.6-alpha-log | +-+ But thanks, I think I can work with this and get the information I want. Regards, Ben. Peter Brawley wrote: Ben,: Dear MySQL, Can you please tell me if there is a way of listing all child tables which have a foreign key reference to a parent? Find children of db.table: SELECT c.table_schema,u.table_name,u.column_name,u.referenced_column_name FROM information_schema.table_constraints AS c INNER JOIN information_schema.key_column_usage AS u USING( constraint_schema, constraint_name ) WHERE c.constraint_type = 'FOREIGN KEY' AND u.referenced_table_schema='db' AND u.referenced_table_name = 'table' ORDER BY c.table_schema,u.table_name; PB - Therefore I can find and delete a child row, then delete the parent without getting: "a foreign key constraint fails" Regards, Ben Clewett -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL:2003 standard
Peng Yi-fan wrote: Hi, It seems that ISO do not support SQL:2003 standard for free but I really need a copy. Does anyone know where I can download it? Or does anyone have it? Any type will be just OK. There is a 2002 draft at http://xml.coverpages.org/SQLX-5wd-14-xml-2002-08.pdf. PB Peng No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 5/8/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 5/9/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reporting child tables referencing a parnet table
Ben,: Dear MySQL, Can you please tell me if there is a way of listing all child tables which have a foreign key reference to a parent? Find children of db.table: SELECT c.table_schema,u.table_name,u.column_name,u.referenced_column_name FROM information_schema.table_constraints AS c INNER JOIN information_schema.key_column_usage AS u USING( constraint_schema, constraint_name ) WHERE c.constraint_type = 'FOREIGN KEY' AND u.referenced_table_schema='db' AND u.referenced_table_name = 'table' ORDER BY c.table_schema,u.table_name; PB - Therefore I can find and delete a child row, then delete the parent without getting: "a foreign key constraint fails" Regards, Ben Clewett -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 5/9/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help in recreating .MYD files
*Ok Daniel,* ** * Thank you.* ** *regards,* *bala* **
Re: slow query
I would expect the problem to be that the further down in the data you go by using OFFSET, the more records the mysql server has to scan in order to get to what you want. This will produce a fairly linear slowdown the further in you go - it just takes time to check through 1,000,000 matching records. Especially on desktop grade hardware where you probably haven't got the fastest disk subsystem. I think in this case your slow searches may be a result of the heavy bias in your data toward user_id 1. Try your search on some of your other user_ids and see. With so many records for the same user_id, your search for that user_id is necessitating something pretty close to a table scan, even though it's hitting an index. Some suggestions would be to increase the size of your data cache, so that after your first queries, the data (or more of it) is in memory. Assuming you'll be deploying on server hardware, a faster disk system should help quite a bit too. Memory caches on hardware RAID systems can help with this kind of thing too. You might also try ALTER TABLE contacts ORDER BY user_id, fullname to get your data sorted into the same order you're looking through it, though it may well affect other queries you need to run against the same data. I'm not certain whether you can ORDER BY more than one column: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html Also note that as you add or delete rows the table does not stay in order. Hope this helps! Dan Adam Wolff wrote: I have a very simple table that looks like this: CREATE TABLE `contacts` ( `id` int(11) NOT NULL auto_increment, `fullname` varchar(100) default NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `user_id_2` (`user_id`,`fullname`), CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 It's a bit of a lopsided table in that of the 1,000,100 records in the db, 1,000,000 of them belong to user_id 1. But I wouldn't expect this to skew my results. I am writing a little paging server that retrieves pages of data using LIMIT and OFFSET. I'm really surprised by how slowly my queries are running on a relatively fast desktop machine. Records near the top of the list are fine: mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname LIMIT 1 OFFSET 0; ++--+-+-+--+ | id | fullname | email | user_id | nickname | ++--+-+-+--+ | 371543 | Aaron Abbott | [EMAIL PROTECTED] | 1 | aaronab | ++--+-+-+--+ 1 row in set (0.03 sec) But as I move down the list, queries run slower and slower: mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname LIMIT 1 OFFSET 10; ++--+-+-+--+ | id | fullname | email | user_id | nickname | ++--+-+-+--+ | 726543 | Benny Abbott | [EMAIL PROTECTED] | 1 | bennyab | ++--+-+-+--+ 1 row in set (2.94 sec) mysql> SELECT * FROM contacts WHERE user_id=1 ORDER BY fullname LIMIT 1 OFFSET 50; ++---+--+-+--+ | id | fullname | email| user_id | nickname | ++---+--+-+--+ | 309543 | Jimmie Abbott | [EMAIL PROTECTED] | 1 | jimmieab | ++---+--+-+--+ 1 row in set (12.75 sec) EXPLAIN says: ++-+--+--+---+---+-+---++-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+---+---+-+---++-+ | 1 | SIMPLE | contacts | ref | user_id,user_id_2 | user_id_2 | 4 | const | 506222 | Using where | ++-+--+--+---+---+-+---++-+ In other words, it *is* using an index for this query. Anyone have any advice for me? Thanks, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL:2003 standard
Hi, It seems that ISO do not support SQL:2003 standard for free but I really need a copy. Does anyone know where I can download it? Or does anyone have it? Any type will be just OK. Peng
Re: batch installation of mysql 5.0
On 5/10/06, kamaya <[EMAIL PROTECTED]> wrote: Hi, I want to install mysql 5.0 in batch mode. That is, I want to avoid interaction with windows while installing mysql 5.0. Is there anyone who has an idea? I have used NSIS from Nullsoft to create a custom install of MySQL, just created a config file, copied the needed files to a location and run the appropriate actions accourding to the OS of the user (windows xp, create service, win98, startup item). Its not really difficul if you know how to deal with MySQL. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ 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-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reporting child tables referencing a parnet table
Dear MySQL, Can you please tell me if there is a way of listing all child tables which have a foreign key reference to a parent? Therefore I can find and delete a child row, then delete the parent without getting: "a foreign key constraint fails" Regards, Ben Clewett -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
~Replication errors~
Hi, Iam getting the following error on my Mysql Slave server. This happened when my disk space got full and there was no space left on the device. I managed to free up some space and then ran mysql> STOP SLAVE mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000110', MASTER_LOG_POS=850202232; mysql> START SLAVE I could find that replication started but with the following error. Can anyone help me out in fixing the issue ? I tried increasing the max_allowed_packet on master server but with no luck. 060510 0:56:22 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.000110' at position 850202232 060510 0:56:22 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236) 060510 0:56:22 [ERROR] Got fatal error 1236: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' from master when readingdata from binary log 060510 0:56:22 [Note] Slave I/O thread exiting, read up to log 'mysql- bin.000110', position 850202232 Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1' and '1' or '1
Tahnks all for your responses (so many) I am reading up on it now -- Dave - Original Message - From: "Johan Lundqvist" <[EMAIL PROTECTED]> To: Sent: Wednesday, May 10, 2006 10:26 AM Subject: Re: 1' and '1' or '1 Hi Dave, 1st: Never, never, never store passwords in plain text!! Just don't do it. Store a hash of the password (ie md5 or something else). 2nd: Never pass any input from the Internet directly into a query without first checking it for sql injection. Take a look at Wikipedia article for a brief explanation and several links to further info. http://en.wikipedia.org/wiki/SQL_injection /Johan Critters wrote: Hi A user was able to log into my site using: 1' and '1' or '1 in the username and password box. I ran the query SELECT * FROM members WHERE name = '1' and '1' or '1' AND password = '1' and '1' or '1' And it returned all rows. Can someone explain to me why this happens, and if the steps I took (replacing the ' with a blank space when the user submits the login form) is enough to prevent a similar "hack" Appreciate any feedback. -- Dave -- 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: how to restart mysql and apache?
Hi, If it is your default apache /usr/sbin/apachectl start and mysql /etc/init.d/mysql.server start This might help you out. Daniel da Veiga wrote: On 5/9/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: found this: /etc/rc.d/init.d/mysqld restart /etc/rc.d/init.d/httpd2 restart I think it should work? Yeah, different systems, different locations, but the same purpose... -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ 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-- -- Thanks & Regards, Dilipkumar DBA Support ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail & notify us immediately at [EMAIL PROTECTED] Watch India vs. England LIVE, Hot videos and more only on Sify Max! Click Here. www.sifymax.com Get to see what's happening in your favourite City on Bangalore Live! www.bangalorelive.in -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1' and '1' or '1
Hi Dave, 1st: Never, never, never store passwords in plain text!! Just don't do it. Store a hash of the password (ie md5 or something else). 2nd: Never pass any input from the Internet directly into a query without first checking it for sql injection. Take a look at Wikipedia article for a brief explanation and several links to further info. http://en.wikipedia.org/wiki/SQL_injection /Johan Critters wrote: Hi A user was able to log into my site using: 1' and '1' or '1 in the username and password box. I ran the query SELECT * FROM members WHERE name = '1' and '1' or '1' AND password = '1' and '1' or '1' And it returned all rows. Can someone explain to me why this happens, and if the steps I took (replacing the ' with a blank space when the user submits the login form) is enough to prevent a similar "hack" Appreciate any feedback. -- Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1' and '1' or '1
At 9:53 +0100 10/5/06, Critters wrote: A user was able to log into my site using: 1' and '1' or '1 in the username and password box. I ran the query SELECT * FROM members WHERE name = '1' and '1' or '1' AND password = '1' and '1' or '1' And it returned all rows. Interesting - I found just the same on the site I'm developing and I'll put preventative measures in place straight away! It's obviously the OR that does it, because if I just use: 1' or '1 it works, but if I try: 1' and '1 it doesn't. As Sander Smeenk said, it's the logic in the where clause: if you just did: SELECT * FROM members WHERE '1' (or indeed: SELECT * FROM members WHERE 1) it would find all rows. This is in fact the default SQL that's rather irritatingly always there in phpMyAdmin's SQL text areas. All you're doing is ORing your other criteria with the '1', which effectively makes them irrelevant. In fact, taking this one stage further, you could log in with: anyloadofoldgibberish' or '1 Try it! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Never trust a man who, when left alone in a room with a tea cosy, doesn't try it on. -- Billy Connolly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: comparing postgis with mysql
Hi, I'm a GIS developer, who specialises in the architecture of geospatial databases. PostGIS (a derivitive of PostGreSQL) has been developed with spatial data in mind, and as such is primarily for this use. I have tried MySQL with Spatial extensions, whose implementation is very good I must say, but on doing stress and load tests PostGIS definitely comes out a winner. If you have small spatial databases, I would recommend MySQL though as it is definitely easier to use and implement in my experience. I will try locate some comparison charts and find my comparison tests I have done myself on these two DB's and mail them to you. Thanks, Dewald Troskie GIS Developer / Database Architect GIS Global Image (Pty) Ltd. Helping the world make informed decisions P.O Box 15 The Innovation Hub 0087 Cell: +27 (0)72 685 4246 Tel: +27 (0)12 844 0660 Fax: +27 (0)86 619 3958 Email: [EMAIL PROTECTED] Web: www.globalimage.co.za Web: www.mapme.co.za Blog: http://electronucleus.blogspot.com/ Registered Linux User No: 371874 Office L15 Enterprise Building The Innovation Hub - Hotel Street Lynnwood, Pretoria, 0087 "The are 10 kinds of people, those who understand binary and those who don't" -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Parang Saraf Sent: 09 May 2006 10:06 PM To: mysql@lists.mysql.com Subject: comparing postgis with mysql hello, I am relatively new in this field. I am designing a database to store the events extracted from the oceans. This project later demands of publishing data on web. I am not able to decide which database to use. Mysql with spatial extension or the postgis one. I would prefer to use windows platform. Can someone suggest or can provide me with some links that compare the two databases. Do you think Mysql has a better spatial elements handling capacity in comparison to postgis.? please reply soon. Thanks and Regards Parang Saraf [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1' and '1' or '1
Search the web for something called "sql injection" and do some reading. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Hi A user was able to log into my site using: 1' and '1' or '1 in the username and password box. I ran the query SELECT * FROM members WHERE name = '1' and '1' or '1' AND password = '1' and '1' or '1' And it returned all rows. Can someone explain to me why this happens, and if the steps I took (replacing the ' with a blank space when the user submits the login form) is enough to prevent a similar "hack" Appreciate any feedback. -- Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1' and '1' or '1
On Wednesday 10 May 2006 09:53, Critters wrote: > Hi > A user was able to log into my site using: > 1' and '1' or '1 > in the username and password box. > > I ran the query > > SELECT * FROM members WHERE name = '1' and '1' or '1' AND password = '1' > and '1' or '1' > > And it returned all rows. Can someone explain to me why this happens, and > if the steps I took (replacing the ' with a blank space when the user SQL injection attack. 1) Quote all input from the real world. If you're using any of the PHP abstraction layers (or just the direct api), there's a quote function that can help. Other languages should have the same abilities. 2) The user has (correctly) assumed that your code uses "select '$var'" syntax. Fill in the blanks appropriately and you'll see how the injection works. 3) The and / or sequence takes advantage of mathematical precedence to force always true. Most SELECTs are essentially end up as a boolean evaluation (are all the conditions true or not), and using SELECT .. FROM .. WHERE '1' is a boolean true. The better handling for passwords btw, is to require plain-text from the user, but hash the password in the table and in the code. The injection attack gets hashed, and becomes useless. Mind you that's just -one- input field type, you can't hash everything. Your hack works, but you'd be better off reading up on SQL injection (you can do more than select all records - how's a dropped table strike you?), and looking at the availability of quoting capabilities in your language of choice. -- Duncan Hill - Developer Critical Software +44 (0)870 770 8190 Scanned by mailCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1' and '1' or '1
Quoting Critters ([EMAIL PROTECTED]): > SELECT * FROM members WHERE name = '1' and '1' or '1' AND password = '1' and > '1' or '1' > And it returned all rows. Can someone explain to me why this happens, > and if the steps I took (replacing the ' with a blank space when the > user submits the login form) is enough to prevent a similar "hack" It's the logic in the WHERE statement that makes the query return all rows. You should /never ever/ directly feed user input from websites to your database. Always use prepare() and execute() statements to feed the userdata, or use the proper quote() calls... Or explicitly state what characters you will allow and filter anything but those characters from the user supplied data. Kind regards, Sander. -- | Someone who thinks logically provides a nice contrast to the real world. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1' and '1' or '1
What page architecture are you using: PHP, ASP, .Net 1.1 or .Net 2.0 or are you using one of the standard CMS packages (Mambo, Plone, etc) ?? If you are using ASP.net 2.0 with MySQL (I am using this), I have managed to implement the Membership / Role providers in my site using MySQL as the provider. Using the login credentials you supplied, The site rejects it as you have to have explicit username and password (which is encrypted on creation in the DB) details to login. HTH, Dewald Troskie GIS Developer / Database Architect GIS Global Image (Pty) Ltd. Helping the world make informed decisions P.O Box 15 The Innovation Hub 0087 Cell: +27 (0)72 685 4246 Tel: +27 (0)12 844 0660 Fax: +27 (0)86 619 3958 Email: [EMAIL PROTECTED] Web: www.globalimage.co.za Web: www.mapme.co.za Blog: http://electronucleus.blogspot.com/ Registered Linux User No: 371874 Office L15 Enterprise Building The Innovation Hub - Hotel Street Lynnwood, Pretoria, 0087 "The are 10 kinds of people, those who understand binary and those who don't" -Original Message- From: Critters [mailto:[EMAIL PROTECTED] Sent: 10 May 2006 10:53 AM To: mysql@lists.mysql.com Subject: 1' and '1' or '1 Hi A user was able to log into my site using: 1' and '1' or '1 in the username and password box. I ran the query SELECT * FROM members WHERE name = '1' and '1' or '1' AND password = '1' and '1' or '1' And it returned all rows. Can someone explain to me why this happens, and if the steps I took (replacing the ' with a blank space when the user submits the login form) is enough to prevent a similar "hack" Appreciate any feedback. -- Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
Hello everyone, This problem seems to be solved... > The problem i'm experiencing is that at certain moments, the connection > between my perlscripts and the database gets disconnected. [...] > I thought the query at that spot would be wrong, but it turns out that > this alone is not causing the problem. If i skip over the rest of the > perlscript and jump to that line 672 immediately, it works like expected. It seems to turn out to diskspace problems. The partition that hosted /tmp, in which MySQL creates tempfiles during queries was rather full. But for some strange reason it didn't trigger MySQL to say 'Can't write to file /tmp/mysql.XXX: No space left on device.'. It did that only after the partition was REALLY full. Then I saw the 'No space left on device' message, and cleared up the partition. And now this problem has gone away. Really hard to debug this on a live system, but i thought i'd let you guys know... Thanks again for all the hints and help! Kind regards, Sander Smeenk. -- | Just remember -- if the world didn't suck, we would all fall off. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1' and '1' or '1
Hi A user was able to log into my site using: 1' and '1' or '1 in the username and password box. I ran the query SELECT * FROM members WHERE name = '1' and '1' or '1' AND password = '1' and '1' or '1' And it returned all rows. Can someone explain to me why this happens, and if the steps I took (replacing the ' with a blank space when the user submits the login form) is enough to prevent a similar "hack" Appreciate any feedback. -- Dave
batch installation of mysql 5.0
Hi, I want to install mysql 5.0 in batch mode. That is, I want to avoid interaction with windows while installing mysql 5.0. Is there anyone who has an idea?
Re: novice on SQL
> Hi John, right the problem boils down to this: sitetable tasktable ID taskidtaskid Changes ---- 11010100 21110120 SELECT sitetable.ID, tasktable.Changes FROM sitetable,tasktable WHERE sitetable.taskid = tasktable.taskid; and get the following: IDChanges 1 100 1 120 but what I need is the following format ID Changes1 Changes2 (limits of 5) --- 1 100120etc ps: a collegue said to me that DBs are not design to do what I wanted to do (in 1 sql query anyway). I'm beginning to accept that comment :( Thanks John Tony > > "John Hicks" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > tony yau wrote: > > > Hi John, > > > > > > tried your suggestion but I can't get it to work. This is because I > don't > > > know how to set conditions in the following clauses (because there isn't > > > any) > > > > > >>> and Table1.[condition for Changes1] > > >>> and Table2.[condition for Changes2] > > >>> and Table3.[condition for Changes3] > > > > What values do you want for Changes1, Changes2, etc.? (How are you > > selecting for them.) > > > > Post your SQL here if you need further help. > > > > --J > > > > > > > > > the result I've got was similar to the following (note the ID is pkey of > > > another table) > > > > > > IDChanges1Changes2Changes3 > > > - > > > 1 10.010.0same as > > > 1 10.310.3 > > > 1 12.212.2 > > > 2 31.031.0 > > > 3 1.021.02 > > > 3 4.94.9 > > > > > > thanks for your help anyway > > > > > > Tony > > > > > > "tony yau" <[EMAIL PROTECTED]> wrote in message > > > news:[EMAIL PROTECTED] > > >> Hi John, > > >> > > >> I didn't know you can do that! (such a novice indeed!) > > >> Thank you for your reply, I will put it to the test first thing when i > get > > >> back to the office tomo. > > >> > > >> Cheers > > >> > > >> "John Hicks" <[EMAIL PROTECTED]> wrote in message > > >> news:[EMAIL PROTECTED] > > >>> tony yau wrote: > > Hello, > > > > I can get a select result like the following: (SELECT ID,Changes FROM > > mytable WHERE somecondition;) > > > > IDChanges > > - > > 1 10.0 > > 1 10.3 > > 1 12.2 > > 2 31.0 > > 3 1.02 > > 3 4.9 > > > > how can I get the above result sets into the following format > (columns > > 'Changes1','Changes2',... are all from 'Changes') > > > > IDChanges1Changes2Changes3 (limits of 5) > > > > 1 10.010.312.2 > > 2 31.0 > > 3 1.024.9 > > > > > > I have got a method that works (I think) by first do a SELECT getting > > DISTINCT id values and then foreach of these ID I do another SELECT > to > > >> get > > the Changes values and then just massage the display. > > > > Is there another way of doing this by using a single SQL query? > > >>> There may be a simpler way, but this should work: > > >>> > > >>> select Table.ID, > > >>> Table1.Changes as Changes1, > > >>> Table2.Changes as Changes2, > > >>> Table3.Changes as Changes3 > > >>> > > >>> from Table, > > >>> Table as Table1, > > >>> Table as Table2, > > >>> Table as Table3 > > >>> > > >>> where Table.ID = Table1.ID > > >>> and Table.ID = Table2.ID > > >>> and Table.ID = Table3.ID > > >>> > > >>> and Table1.[condition for Changes1] > > >>> and Table2.[condition for Changes2] > > >>> and Table3.[condition for Changes3] > > >>> > > >>> order by table.ID > > >>> > > >>> > > >>> --J > > >>> > > > > > > -- > 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]
