RE: Need a query to get the difference of two tables
Hi All, This query worked for me. SELECT a.address FROM a LEFT JOIN b ON a.id != b.iid AND b.message='y' Thanks, Harish -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, January 31, 2005 12:23 AM To: Roger Baklund Cc: mysql; Harish Subject: Re: Need a query to get the difference of two tables Roger Baklund wrote: Harish wrote: Hi, I apprecaite anybody replying me with an equvalent query for this: I am using mysql 4.0.21 select a.address from a where a.id not in (select b.iid from b where b.message='y') This can be done with a left join: select a.address from a left join b on b.iid=a.id where b.iid is null; That's not equivalent, because it leaves out a condition. I think it should be SELECT a.address FROM a LEFT JOIN b ON a.id = b.iid AND b.message='y' WHERE b.iid IS NULL; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: utf8 x latin
I think you should use: latin1_general_ci and on columns with spanish you should use latin1_spanish_ci. English, French, Portuguese not having a separate collation I believe that they are included into latin1_general_ci ? Please observe the last part from a collation name: case sensitive (_cs) ... case insensitive (_ci) ... and binary (_bin) ! UTF8 consumes more space on disk ! If you issue a SHOW CHARACTER SET command mysql SHOW CHARACTER SET; You will notice the last column named Maxlen that defines how many bytes takes to store a letter. On utf8_general_ci you will notice 3... as on latin1__xx you will only see 1... Although these values represent a MAX value... what you need will use at most 2 bytes per letter in UTF-8... but in latin1_xxx_xx will, for sure, will use one byte... on long texts this will count ! In my opinion you should stick to latin1_xxx_xx ! Afther some thought and aditional computation I declared myself satisfied with: latin2 - ISO 8859-2 Central European - latin2_general_ci - 1byte but I'm having Romanian + English + French columns. Try to read http://dev.mysql.com/doc/mysql/en/charset.html with no distraction arround you ! Gabriel - Original Message - From: Andre Matos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, January 30, 2005 9:29 PM Subject: utf8 x latin Hi list, I was wondering which one is better to use, utf8_general_ci or latin1_general_cs, with English, French, Portuguese, and Spanish? Thanks. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
client_test fails on Solaris 9 x86
Description: Running mysql-test fails test client_test with segv. Several other tests fail after this and it may be due to the segv. here is the output from the test: client_test[ fail ] Errors are (from /opt/csw/mysql4/mysql-test/var/log/mysqltest-time) : client_test.c:9730: check failed: 'udouble_val == ulonglong2double(uint64_val)' Abort - core dumped /opt/csw/mysql4/bin/mysqltest: command $TESTS_BINDIR/client_test --no-defaults --testcase --user=root --socket=$MASTER_MYSOCK --port=$MYSQL_TCP_PORT --silent failed (the last lines may be the most important ones) Ending Tests Shutting-down MySQL daemon Master shutdown finished Slave shutdown finished Resuming Tests comments [ pass ] 53 ... drop [ fail ] Errors are (from /opt/csw/mysql4/mysql-test/var/log/mysqltest-time) : /opt/csw/mysql4/bin/mysqltest: At line 58: Result length mismatch (the last lines may be the most important ones) Below are the diffs between actual and expected results: --- *** r/drop.result Wed Jan 26 07:07:36 2005 --- r/drop.reject Wed Jan 26 14:52:01 2005 *** *** 31,36 --- 31,37 create database mysqltest; show databases; Database + client_test_db mysql mysqltest test *** *** 41,46 --- 42,48 drop database mysqltest; show databases; Database + client_test_db mysql test drop database mysqltest; --- Please follow the instructions outlined at http://www.mysql.com/doc/en/Reporting_mysqltest_bugs.html to find the reason to this problem and how to report this. Ending Tests Shutting-down MySQL daemon Master shutdown finished Slave shutdown finished Resuming Tests drop_temp_table[ pass ] 77 How-To-Repeat: Just running mysql-test. The daemon appears to run ok, but I have not tried very many exercises to verify this. Fix: Unknown Submitter-Id: Originator:Alex Moore Organization: MySQL support: none Synopsis: mysql-test-run fails client_test on Solaris x86 Severity: critical Priority: medium Category: mysql Class: sw-bug Release: mysql-4.1.9 (Source distribution) C compiler:cc: Sun C 5.5 Patch 112761-10 2004/08/10 C++ compiler: | doc-bug | change-request | support ] (one line) Environment: Dell 2300, 2x450Mhz pentium, Solaris 9 9/04 with recommended patches System: SunOS mcsun5 5.9 Generic_117172-12 i86pc i386 i86pc Architecture: i86pc Some paths: /usr/bin/perl /usr/ccs/bin/make /opt/csw/bin/gmake /opt/SUNWspro/bin/cc Compilation info: CC='cc' CFLAGS='-O -DDBUG_OFF -Xa -xstrconst -mt -D_FORTEC_ -xO3 -xtarget=386-D_FILE_OFFSET_BITS=64 -DBIG_TABLES -DHAVE_CURSES_H -I/export/medusa/asmoore/build/mysql-4.1.9-i386/include -DHAVE_RWLOCK_T' CXX='CC' CXXFLAGS='-O3 -DDBUG_OFF -noex -mt -D_FORTEC_ -xO3 -xtarget=386-D_FILE_OFFSET_BITS=64 -DBIG_TABLES -DHAVE_CURSES_H -I/export/medusa/asmoore/build/mysql-4.1.9-i386/include -DHAVE_RWLOCK_T' LDFLAGS='-L/opt/csw/lib ' ASFLAGS='' LIBC: -rw-r--r-- 1 root bin 1908016 Nov 21 15:56 /lib/libc.a lrwxrwxrwx 1 root root 11 Dec 23 06:40 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 805364 Nov 21 15:56 /lib/libc.so.1 -rw-r--r-- 1 root bin 1908016 Nov 21 15:56 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Dec 23 06:40 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 805364 Nov 21 15:56 /usr/lib/libc.so.1 Configure command: ./configure '--prefix=/opt/csw/mysql4' '--disable-assembler' '--without-libedit' '--enable-thread-safe-client' '--with-named-z-libs=no' '--with-berkeley-db' '--with-openssl' '--with-innodb' '--enable-local-infile' '--with-extra-charsets=all' 'CC=cc' 'CFLAGS=-Xa -xstrconst -mt -D_FORTEC_ -xO3 -xtarget=386 ' 'CPPFLAGS=-I/opt/csw/include' 'CXXFLAGS=-noex -mt -D_FORTEC_ -xO3 -xtarget=386 ' 'CXX=CC' 'LDFLAGS=-L/opt/csw/lib ' 'CFLAGS=-O -DDBUG_OFF -Xa -xstrconst -mt -D_FORTEC_ -xO3 -xtarget=386 -D_FILE_OFFSET_BITS=64 -DBIG_TABLES -DHAVE_CURSES_H -I/export/medusa/asmoore/build/mysql-4.1.9-i386/include -DHAVE_RWLOCK_T' 'CXXFLAGS=-O3 -DDBUG_OFF -noex -mt -D_FORTEC_ -xO3 -xtarget=386 -D_FILE_OFFSET_BITS=64 -DBIG_TABLES -DHAVE_CURSES_H -I/export/medusa/asmoore/build/mysql-4.1.9-i386/include -DHAVE_RWLOCK_T' Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query and password
Hello.
Looks like passwords in your table are stored in old short format.
Use old_password() instead of password(). See:
http://dev.mysql.com/doc/mysql/en/password-hashing.html
Lancer Emotion 16 [EMAIL PROTECTED] wrote:
Hello everbody,i have a problem with mysql and i wish you could help me.
I have this table named users :
+++--+--+---+-+-
+
| ID | user | pass | thegroup | firstname | surname |
enabled
|
+++--+--+---+-+-
+
| 1 | Admin | *4ACFE3202A5FF5CF467 | 1 | Mr| Admin | 1
|
| 2 | admin2 | *4ACFE3202A5FF5CF467 | 1 | Mr| Admin | 1
|
+++--+--+---+-+-
The password in both cases are : admin .
When i do any of this querys :
select * from users where pass=password(admin);
select * from users where pass='admin';
select * from users where pass=password('admin');
i get an empty set, i dont know why.
When i do :
select password('admin')
i get : *4ACFE3202A5FF5CF467898FC58AAB1D615029441
Please help me.
Thanks
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupted auto_increment?
Hello. Not enough information to make a conlusion. For example this can be a bug: http://bugs.mysql.com/bug.php?id=6784 Which is fixed in 4.1.8. Send us information about exact version of MySQL that you use, an output of SHOW CREATE TABLE on your tables, a config file, a version of operating system, a sql statement you use. Can you reproduce a problem on the latest release (4.1.9 now)? Jim McAtee [EMAIL PROTECTED] wrote: A server running MySQL 3.23 crashed yesterday. Since bringing it back online, doing inserts into a particular table will throw an error regarding a duplicate key value in an auto_increment field. I assume that something is corrupted. There are 779239 records, the most recently added having an auto_increment ID field of 779239. When a new record is added, MySQL attempts to give it an ID of 779240, but then throws the error that this ID is a duplicate. How to fix? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump unusable, bugged?
I've seen this quite a few times, but never been able to reproduce it properly. I'm assuming you're running on Windows, correct? I'd search your dump-file for DATA DIRECTORY the problem on Windows is that it uses \ instead of / in the path names there, which makes it use it as an escape character. Since dATA/INDEX DIRECTORY in CREATE TABLE is ignored on Windows when importing anyways, it's safe to remove those clauses. If you can find a way to reproduce it, feel free to add it to my old bug report about this at: http://bugs.mysql.com/bug.php?id=6660 cheers, Yes running windows I set all the sizes to match, 256mb and I still get the same error, tried dumping 2 different databases from 2 different machines, identical structures but different data, same error I checked line 2153 and found no '\m' or anything else out of the ordinary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Administrator not working correctly 2nd Request
OK I have a Fedora Core 3 Box running MySQL-server-4.1.8-0, when I try to
manage the users on that box using the windows version of MySQL
administrator version 1.0.19 I get errors.
When I add a new user it tells me:
error while storing user information
If I look at the mysql log file is shows this:
050121 17:19:02 25917 Query set @new_user='bob'
25917 Query INSERT INTO mysql.user(User, Host,
Password) VALUES(@new_user, '%', Password('testtest'))
25917 Query INSERT INTO mysql.user_info(User,
Full_name, Description, Email, Contact_information, Icon) VALUES(@new_user,
'Bob the tester', '', '', '', '')
25917 Query DELETE FROM mysql.db WHERE
[EMAIL PROTECTED]
25917 Query DELETE FROM mysql.tables_priv WHERE
[EMAIL PROTECTED]
I create a new schema / db called testing with NO ERRORS
050121 17:22:41 25917 Query CREATE DATABASE `testing`
25917 Query show databases
25917 Query show databases
25917 Query show databases
25917 Query show databases
25917 Query show databases
Then I assign security to bob for the db testing:
And I get error while storing user information
Which shows this in the log:
050121 17:23:34 25917 Query set @new_user='bob'
25917 Query set @goal_user='bob'
25917 Query SELECT Full_name, Description, Email,
Contact_information, Icon FROM mysql.user_info WHERE cast(cast(User AS
BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
CHARACTER SET utf8)
25917 Query select cast(cast(host AS BINARY) AS CHAR
CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
utf8) as o, _utf8'Select_priv' as pn, cast(cast(Select_priv AS BINARY) AS
CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
CHARACTER SET utf8)
25917 Query select cast(cast(host AS BINARY) AS CHAR
CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
utf8) as o, _utf8'Insert_priv' as pn, cast(cast(Insert_priv AS BINARY) AS
CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
CHARACTER SET utf8)
25917 Query select cast(cast(host AS BINARY) AS CHAR
CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
utf8) as o, _utf8'Update_priv' as pn, cast(cast(Update_priv AS BINARY) AS
CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
CHARACTER SET utf8)
25917 Query select cast(cast(host AS BINARY) AS CHAR
CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
utf8) as o, _utf8'Delete_priv' as pn, cast(cast(Delete_priv AS BINARY) AS
CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
CHARACTER SET utf8)
25917 Query select cast(cast(host AS BINARY) AS CHAR
CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
utf8) as o, _utf8'Create_priv' as pn, cast(cast(Create_priv AS BINARY) AS
CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
CHARACTER SET utf8)
25917 Query select cast(cast(host AS BINARY) AS CHAR
CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
utf8) as o, _utf8'Drop_priv' as pn, cast(cast(Drop_priv AS BINARY) AS CHAR
CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS BINARY) AS
CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER
SET utf8)
25917 Query select cast(cast(host AS BINARY) AS CHAR
CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
utf8) as o, _utf8'Reload_priv' as pn, cast(cast(Reload_priv AS BINARY) AS
CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
CHARACTER SET utf8)
25917 Query select cast(cast(host AS BINARY) AS CHAR
CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
utf8) as o, _utf8'Shutdown_priv' as pn, cast(cast(Shutdown_priv AS BINARY)
AS CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
CHARACTER SET utf8)
25917 Query select cast(cast(host AS BINARY) AS CHAR
CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
utf8) as o,
Re: Mysqldump unusable, bugged?
matt_lists wrote: I've seen this quite a few times, but never been able to reproduce it properly. I'm assuming you're running on Windows, correct? I'd search your dump-file for DATA DIRECTORY the problem on Windows is that it uses \ instead of / in the path names there, which makes it use it as an escape character. Since dATA/INDEX DIRECTORY in CREATE TABLE is ignored on Windows when importing anyways, it's safe to remove those clauses. If you can find a way to reproduce it, feel free to add it to my old bug report about this at: http://bugs.mysql.com/bug.php?id=6660 cheers, Yes running windows I set all the sizes to match, 256mb and I still get the same error, tried dumping 2 different databases from 2 different machines, identical structures but different data, same error I checked line 2153 and found no '\m' or anything else out of the ordinary You might find this useful: http://www.vim.org/tips/tip.php?tip_id=26 Vim uses the ^M notation, but I imagine that corresponds to \m. I have a program which can strip various newline characters out and replace with specified ones. If you'd like a copy, I could fish it out? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sub query Help
Hello I have written a report generator in PHP that handles creating and paginating the results from queries into a nice, user-friendly, HTML format. Because it's a generic report generator, it has some quirks about it, but I have managed to modify queries enough so that it works in almost all cases for me. There is one exception: My database has the following tables: States - A list of US States Orders - A list of orders that have been placed OrderItems - A list of the individual items that have been purchased on each order My report generator has create the following query to view a summary of how much merchandise was purchased by customers, grouped by state: select (select Abbreviation from States where States.ID = Orders.BillingState) as `State`, count(*) as `Count`, (select sum(Price) from OrderItems where OrderItems.Order = Orders.ID) as `Price`, `BillingState` from Orders group by `BillingState` order by `State` The query executes, but it does not return the correct results. It seems to be returning the totals for one individual order rather than the sum of all the orders. I know there are ways I can change the overall query to fix the problem, but I can't just change that overall query: remember, this is in a report generator, and arguably a simple one at that. I have to fit everything I need to do into the column sub queries. Is this possible? Is there any way to modify the sub query so that it will include all orders rather than just one? Thanks! Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 480-1870 Mobile/Emergencies (516) 908-4185 Fax http://www.meitech.com/ smime.p7s Description: S/MIME cryptographic signature
Re: Replication suddenly stops on mysql 4.1.7 with Slave_IO_Running: No
Hi, My problem still goes on... After having had the problem 2 more times within 1 day, I decided to re-do the replication (copy the whole database onto the slave with rsync and reset master and slave). That only lasted for little more than 1 day and I ended up with the same error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. I can look at the binlog with mysqlbinlog on the master and the slave; no errors or problems. After a simple SLAVE START without having done any changes to the database, the slave thread startet again and caught up with the master. I've been using mysql's replication-feature since it first came up in 1999 or 2000 and dealt with lots of problems and workarounds, but this one is weird. Any ideas anybody? Jan Hello, I have a replication setup on to linux boxes (debian woody, kernel 2.4.21-xfs, mysql 4.1.7-standard official intel-compiler binary from mysql.com). master:~# mysqladmin status Uptime: 464848 Threads: 10 Questions: 296385136 Slow queries: 1752 Opens: 2629 Flush tables: 1 Open tables: 405 Queries per second avg: 637.596 slave:~# mysqladmin status Uptime: 463460 Threads: 2 Questions: 292885156 Slow queries: 6 Opens: 2510 Flush tables: 1 Open tables: 327 Queries per second avg: 631.953 both systems have identical hardware (P4 2.4ghz, 3GB RAM, SCSI-Hardware-RAID) connection is gigabit-ethernet. Everything used to work fine, but I wanted to get rid of InnoDB since I did only use that for very big table containing historical data and those tables were moved to another server. I ran out of discspace, innodb-datafiles can only grow but not shrink and i didn't need it anyway, so it had to go. I stopped the slave, changed all left over innodb-tables to myisam, added skip-innodb to my.cnf on the master and the slave, restarted the server, renewed the replication by doing it the classical way: flush tables with read log, copy the /var/lib/mysql on the slave (not much, just around 20GB), reset master, unlock tables. Then start the slave-mysqld, reset slave, slave start. Everything was fine and very fast for 4 days (from saturday till wednesday afternoon), then suddenly the slave stopped. this is where the weird stuff starts: show slave status tells me everything is fine, just Slave_IO_Running: No is wrong. After typing slave start, it says Slave_IO_Running: Yes, and Slave_SQL_Running: No. Very strange. Now i did a slave stop;slave start; and everything is fine again, the slave catches up and goes on. Today (thursday afternoon), the same thing happens again and can be solved again by slave stop;slave start;. Now it happened again around 10pm. Again, the stop-start-trick made it working again. I add the output of my mysql-shell Can anybody help me with that? This is a production system under heavy load and I can't play around with different mysql-versions and such... If I don't find a solution really quick, I'll have to do help myself with some shell-skript-daemon checking if replication is running and issuing stop slave;start slave-commands otherwise... not really the way it should be :( Thanks Jan SLAVE: slave:~# cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 2 model name : Intel(R) Pentium(R) 4 CPU 2.40GHz stepping: 7 cpu MHz : 2392.077 cache size : 512 KB fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm bogomips: 4771.02 slave:~# free total used free sharedbuffers cached Mem: 31051042355364 749740 04401514104 -/+ buffers/cache: 8408202264284 Swap: 779144 428072 351072 MASTER master:~# cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 2 model name : Intel(R) Pentium(R) 4 CPU 2.40GHz stepping: 7 cpu MHz : 2392.163 cache size : 512 KB fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm bogomips: 4771.02 master:~# free
Re: Need a query to get the difference of two tables
Michael Stassen wrote: Roger Baklund wrote: [...] select a.address from a left join b on b.iid=a.id where b.iid is null; That's not equivalent, because it leaves out a condition. I think it should be SELECT a.address FROM a LEFT JOIN b ON a.id = b.iid AND b.message='y' WHERE b.iid IS NULL; You are of course right, thank you! :) ...and the important thing here is to put this extra condition in the ON clause, not in the WHERE clause. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need a query to get the difference of two tables
Hi All, This query worked for me. SELECT a.address FROM a LEFT JOIN b ON a.id != b.iid AND b.message='y' Thanks, Harish -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, January 31, 2005 12:23 AM To: Roger Baklund Cc: mysql; Harish Subject: Re: Need a query to get the difference of two tables Roger Baklund wrote: Harish wrote: Hi, I apprecaite anybody replying me with an equvalent query for this: I am using mysql 4.0.21 select a.address from a where a.id not in (select b.iid from b where b.message='y') This can be done with a left join: select a.address from a left join b on b.iid=a.id where b.iid is null; That's not equivalent, because it leaves out a condition. I think it should be SELECT a.address FROM a LEFT JOIN b ON a.id = b.iid AND b.message='y' WHERE b.iid IS NULL; Michael -- 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]
RE: disappearing data - please help!
Lee wrote: Are you escaping the data you insert? If you have apostrophies eg. Sheryl's Website, you could have problem if you do not escape special chars. See if it happens with just aa (without the quotes) in all the fields in the record...then try unescaped words with apostrophies. I'm not, and I should be. It not the cause of my data disappearing problem because my test data was safe, but I shouldn't assume that customer-entered data will be safe. Thanks for the reminder. A nasty is case sensitivity. Some MYSQl vers. (esp. unix vers.) install with case sensivity turned on - so make sure all your table names and stuff match case. I'm used to that with Unix - everything is case-matched. Par down the PHP or what ever script you're using to the minimum ie. make the test case as blatently simple SQL as possible. I can't reproduce it with a simple case. It's an intermittant problem, and it seems to be related to some complexity buried in my program. - Sheryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump unusable, bugged?
Found the problem now we have this added on a few tables in the dump DATA DIRECTORY='E:\mysql\data\campbell\' INDEX DIRECTORY='E:\mysql\data\campbell\' the restore barfs on this not sure how to remove this, looking at options now, it only puts this on a couple tables, not all of them -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need a query to get the difference of two tables
Hi All, Sorry, I got a autoreply from [EMAIL PROTECTED] I was not able to understand the autoreply and thought that the mail bouced. Please ignore my duplicate mail. Thanks again, Harish -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Monday, January 31, 2005 6:15 PM To: mysql Cc: Michael Stassen Subject: Re: Need a query to get the difference of two tables Michael Stassen wrote: Roger Baklund wrote: [...] select a.address from a left join b on b.iid=a.id where b.iid is null; That's not equivalent, because it leaves out a condition. I think it should be SELECT a.address FROM a LEFT JOIN b ON a.id = b.iid AND b.message='y' WHERE b.iid IS NULL; You are of course right, thank you! :) ...and the important thing here is to put this extra condition in the ON clause, not in the WHERE clause. -- Roger -- 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: Need a query to get the difference of two tables
Harish wrote:
Hi All,
This query worked for me.
SELECT a.address
FROM a
LEFT JOIN b ON a.id != b.iid AND b.message='y'
Strange... that query should give far too many and wrong rows as a
result, and it would take a long time to run on a big dataset... you are
joining each row in table a with every row in table b with message='y',
except that one potential row where id=iid...
I did this test:
mysql use test;
Database changed
mysql create table a(id int not null primary key,address varchar(80));
Query OK, 0 rows affected (0.02 sec)
mysql create table b(iid int,message enum('y','n') not null);
Query OK, 0 rows affected (0.00 sec)
mysql insert into a values (1,'addr 1'),(2,'addr 2'),
(3,'addr 3'),(4,'addr 4'),(5,'addr 5');
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql insert into b values (1,'y'),(3,'y');
Query OK, 2 rows affected (0.13 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql select a.address from a left join b on a.id!=b.iid
and b.message='y';
+-+
| address |
+-+
| addr 1 |
| addr 2 |
| addr 2 |
| addr 3 |
| addr 4 |
| addr 4 |
| addr 5 |
| addr 5 |
+-+
8 rows in set (0.00 sec)
How does my data differ from yours, as you got the result you wanted
from this query?
From your original post, I got the impression that this was what you
wanted:
mysql select a.address
-from a
-left join b on b.iid=a.id and message=y
-where b.iid is null;
+-+
| address |
+-+
| addr 2 |
| addr 4 |
| addr 5 |
+-+
3 rows in set (0.02 sec)
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump unusable, bugged?
matt_lists wrote: Found the problem now we have this added on a few tables in the dump DATA DIRECTORY='E:\mysql\data\campbell\' INDEX DIRECTORY='E:\mysql\data\campbell\' the restore barfs on this not sure how to remove this, looking at options now, it only puts this on a couple tables, not all of them Definitly the problem, but I cant find the option to remove it If I replace it with the following it works fine DATA DIRECTORY='E:\\mysql\\data\\campbell\\' INDEX DIRECTORY='E:\\mysql\\data\\campbell\\' works great in test, wont work in production, the backup sql file is 20-60 gig depending on the site, there's no way I can search and replace that size a file -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
WHERE ... IN ()
Greetings, list ! ANSI SQL has the WHERE key IN (value1, value2, ...) MySQL doesn't seem to support this. Is this a faulty assumption, or am i stuck with WHERE key = value1 OR key = value2 ... TIA, Stijn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: disappearing data - please help!
-Original Message- From: Sheryl (Permutations Software) [mailto:[EMAIL PROTECTED] Sent: maandag 31 januari 2005 14:44 To: 'leegold'; mysql@lists.mysql.com Subject: RE: disappearing data - please help! Par down the PHP or what ever script you're using to the minimum ie. make the test case as blatently simple SQL as possible. I can't reproduce it with a simple case. It's an intermittant problem, and it seems to be related to some complexity buried in my program. Hello Sheryl, Well, just to rule out the obvious, what client library did you link your PHP to? You can see that running phpinfo(). I mean, if you ever upgraded from, say, MySQL 3.23, to, say, 4.0.18, the C headers of your client libraries will have changed, and everything which is statically linked to them, like PHP, should be recompiled. If your client library is at 4.0.18, and MySQL itself at, say, 4.0.23, you would still be okay; but with an older client library, you may expect weirdness. ;) I'm sure you already looked into this; but just in case you didn't... - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WHERE ... IN ()
[snip] ANSI SQL has the WHERE key IN (value1, value2, ...) MySQL doesn't seem to support this. Is this a faulty assumption, or am i stuck with WHERE key = value1 OR key = value2 ... [/snip] It is a faulty assumption, see http://dev.mysql.com/doc/mysql/en/comparison-operators.html SELECT stuff FROM table WHERE this IN (value1, value2, ...) works just fine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE ... IN ()
Jay, thx for your speedy reply. MySQL does indeed know the WHERE IN syntax (bad coding on my part). I have another question. There are two tables in the db from which I want to gather information. These tables are identical, except for two fields that have different names, the same types (but other length constraints, although i don't think that is the problem here), and except for 1 field that is only present in the first table. Here is my solution: (SELECT ID, referentie, postcode, gemeente, kadastrale_opp,prijs_zichtbaar, verkoopprijs, status, adres, '' AS hoofdtype FROM gronden WHERE status = 'te koop' ) UNION (SELECT ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, verkoopprijs, doel AS status, straat AS adres, hoofdtype FROM panden WHERE doel = 'te koop' ) ORDER BY verkoopprijs ASC As u can see, I am trying to schmuck up the field that is only present in one table, by selecting nothing and naming it as that field name (hoofdtype). The query succeeds, but the row contains no data for hoofdtype. Can somebody please enlighten me as to the correct answer. TIA, Stijn Jay Blanchard wrote: [snip] ANSI SQL has the WHERE key IN (value1, value2, ...) MySQL doesn't seem to support this. Is this a faulty assumption, or am i stuck with WHERE key = value1 OR key = value2 ... [/snip] It is a faulty assumption, see http://dev.mysql.com/doc/mysql/en/comparison-operators.html SELECT stuff FROM table WHERE this IN (value1, value2, ...) works just fine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNION (was: WHERE ... IN () )
Here is my solution: (SELECT ID, referentie, postcode, gemeente, kadastrale_opp,prijs_zichtbaar, verkoopprijs, status, adres, '' AS hoofdtype FROM gronden WHERE status = 'te koop' ) UNION (SELECT ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, verkoopprijs, doel AS status, straat AS adres, hoofdtype FROM panden WHERE doel = 'te koop' ) ORDER BY verkoopprijs ASC As u can see, I am trying to schmuck up the field that is only present in one table, by selecting nothing and naming it as that field name (hoofdtype). The query succeeds, but the row contains no data for hoofdtype. UNION simply adds the records from each part to the result so far. So instead of adding hoofdtype data to the corresponding records that were already present in the result from the first query, the new records are simply appended to the record set so far. I gues you want to use a JOIN (probably a LEFT JOIN in your case) and tell MySQL which columns to use to find matching records in the second table and then combine the data from both tables in one record set. Suppose `referentie` is the column to use for matching data from both tables: SELECT g.`ID`, g.`referentie`, g.`postcode`, g.`gemeente`, g.`kadastrale_opp`, g.`prijs_zichtbaar`, g.`verkoopprijs`, g.`status`, g.`adres`, p.`hoofdtype` FROM `gronden` AS g LEFT JOIN `panden` AS p ON g.`referentie` = p.`referentie` WHERE g.`status` = 'te koop' ORDER BY g.`verkoopprijs` ASC ON g.`referentie` = p.`referentie` could be replaced with USING (`referentie`) since in both tables the field has the same name. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How-to copy a column
Does anyone know the easiest way to copy a column in mysql? I have a table (table1) which has 4 columns, I want to copy all the contents of col1 into col2. Col3 is the primary unique key, so the copy has to keep the data matched with col3. I guess one possible way is to do a select and output to a file and then read from the file for input into col2. Thank you.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How-to copy a column
[snip] Does anyone know the easiest way to copy a column in mysql? I have a table (table1) which has 4 columns, I want to copy all the contents of col1 into col2. Col3 is the primary unique key, so the copy has to keep the data matched with col3. [/snip] UPDATE table1 SET col2=col1; --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How-to copy a column
Hi! Am Mo, den 31.01.2005 schrieb Syed Ali um 17:14: Does anyone know the easiest way to copy a column in mysql? I have a table (table1) which has 4 columns, I want to copy all the contents of col1 into col2. Col3 is the primary unique key, so the copy has to keep the data matched with col3. UPDATE table1 SET col2 = col1 is the SQL way of doing that. Requirements: compatible column types. If you want to do it for some rows only, add a WHERE condition. I guess one possible way is to do a select and output to a file and then read from the file for input into col2. Yes, if you want to do it the hard way. HTH, Joerg Bruehe -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 Are you MySQL certified? 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: UNION
Jigal van Hemert wrote: Here is my solution: (SELECT ID, referentie, postcode, gemeente, kadastrale_opp,prijs_zichtbaar, verkoopprijs, status, adres, '' AS hoofdtype FROM gronden WHERE status = 'te koop' ) UNION (SELECT ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, verkoopprijs, doel AS status, straat AS adres, hoofdtype FROM panden WHERE doel = 'te koop' ) ORDER BY verkoopprijs ASC As u can see, I am trying to schmuck up the field that is only present in one table, by selecting nothing and naming it as that field name (hoofdtype). The query succeeds, but the row contains no data for hoofdtype. UNION simply adds the records from each part to the result so far. So instead of adding hoofdtype data to the corresponding records that were already present in the result from the first query, the new records are simply appended to the record set so far. I gues you want to use a JOIN (probably a LEFT JOIN in your case) and tell MySQL which columns to use to find matching records in the second table and then combine the data from both tables in one record set. Suppose `referentie` is the column to use for matching data from both tables: SELECT g.`ID`, g.`referentie`, g.`postcode`, g.`gemeente`, g.`kadastrale_opp`, g.`prijs_zichtbaar`, g.`verkoopprijs`, g.`status`, g.`adres`, p.`hoofdtype` FROM `gronden` AS g LEFT JOIN `panden` AS p ON g.`referentie` = p.`referentie` WHERE g.`status` = 'te koop' ORDER BY g.`verkoopprijs` ASC ON g.`referentie` = p.`referentie` could be replaced with USING (`referentie`) since in both tables the field has the same name. Regards, Jigal. Thx for your reply. AFAIK, JOINs are used for combining data from tables that have corresponding values. This is not the case here. The tables are very much alike in structure, but not in content. I've tried your statement, and now it only returns data from one of the tables. My guess is I'm stuck with the UNION solution. When I leave out the schmucking-up things, I get an error saying that the number of tables do not correspond. When I enter the '' selector again, the query succeeds, but with no values for 'hoofdtype'. I've also tried making sure that every field is getting it's own column by adding extra '' selectors, to no avail. Any ideas ? TIA, Stijn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need a query to get the difference of two tables
Hi,
You are absolutely right as per the test. I am confused. Infact, I had run a
limit on the query. So, I got the result in the first two records. So, i was
able to get a single ID which I was looking at.
My original requirement was a bit more complicated than the one I posted. I
had to check 1 million records (a table) and half a million (b table). And,
due to transactions the a table gets updated frequently resulting in
different number of records.
- Harish
-Original Message-
From: Roger Baklund [mailto:[EMAIL PROTECTED]
Sent: Monday, January 31, 2005 6:48 PM
To: mysql
Cc: Harish; Michael Stassen
Subject: Re: Need a query to get the difference of two tables
Harish wrote:
Hi All,
This query worked for me.
SELECT a.address
FROM a
LEFT JOIN b ON a.id != b.iid AND b.message='y'
Strange... that query should give far too many and wrong rows as a
result, and it would take a long time to run on a big dataset... you are
joining each row in table a with every row in table b with message='y',
except that one potential row where id=iid...
I did this test:
mysql use test;
Database changed
mysql create table a(id int not null primary key,address varchar(80));
Query OK, 0 rows affected (0.02 sec)
mysql create table b(iid int,message enum('y','n') not null);
Query OK, 0 rows affected (0.00 sec)
mysql insert into a values (1,'addr 1'),(2,'addr 2'),
(3,'addr 3'),(4,'addr 4'),(5,'addr 5');
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql insert into b values (1,'y'),(3,'y');
Query OK, 2 rows affected (0.13 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql select a.address from a left join b on a.id!=b.iid
and b.message='y';
+-+
| address |
+-+
| addr 1 |
| addr 2 |
| addr 2 |
| addr 3 |
| addr 4 |
| addr 4 |
| addr 5 |
| addr 5 |
+-+
8 rows in set (0.00 sec)
How does my data differ from yours, as you got the result you wanted
from this query?
From your original post, I got the impression that this was what you
wanted:
mysql select a.address
-from a
-left join b on b.iid=a.id and message=y
-where b.iid is null;
+-+
| address |
+-+
| addr 2 |
| addr 4 |
| addr 5 |
+-+
3 rows in set (0.02 sec)
--
Roger
--
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: UNION
Stijn Verholen wrote: Jigal van Hemert wrote: Here is my solution: (SELECT ID, referentie, postcode, gemeente, kadastrale_opp,prijs_zichtbaar, verkoopprijs, status, adres, '' AS hoofdtype FROM gronden WHERE status = 'te koop' ) UNION (SELECT ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, verkoopprijs, doel AS status, straat AS adres, hoofdtype FROM panden WHERE doel = 'te koop' ) ORDER BY verkoopprijs ASC As u can see, I am trying to schmuck up the field that is only present in one table, by selecting nothing and naming it as that field name (hoofdtype). The query succeeds, but the row contains no data for hoofdtype. UNION simply adds the records from each part to the result so far. So instead of adding hoofdtype data to the corresponding records that were already present in the result from the first query, the new records are simply appended to the record set so far. I gues you want to use a JOIN (probably a LEFT JOIN in your case) and tell MySQL which columns to use to find matching records in the second table and then combine the data from both tables in one record set. Suppose `referentie` is the column to use for matching data from both tables: SELECT g.`ID`, g.`referentie`, g.`postcode`, g.`gemeente`, g.`kadastrale_opp`, g.`prijs_zichtbaar`, g.`verkoopprijs`, g.`status`, g.`adres`, p.`hoofdtype` FROM `gronden` AS g LEFT JOIN `panden` AS p ON g.`referentie` = p.`referentie` WHERE g.`status` = 'te koop' ORDER BY g.`verkoopprijs` ASC ON g.`referentie` = p.`referentie` could be replaced with USING (`referentie`) since in both tables the field has the same name. Regards, Jigal. Thx for your reply. AFAIK, JOINs are used for combining data from tables that have corresponding values. This is not the case here. The tables are very much alike in structure, but not in content. I've tried your statement, and now it only returns data from one of the tables. My guess is I'm stuck with the UNION solution. When I leave out the schmucking-up things, I get an error saying that the number of tables do not correspond. You mean number of columns, don't you? When I enter the '' selector again, the query succeeds, but with no values for 'hoofdtype'. I've also tried making sure that every field is getting it's own column by adding extra '' selectors, to no avail. Any ideas ? I have one. Prior to mysql 4.1.1, column widths were determined by the first SELECT in the UNION. If you are using an earlier version, your first SELECT sets the column width for hoofdtype to 0, because you are selecting an empty string. You should be able to fix this by either reversing the order of your two SELECTs, or by replacing the empty string with a string filled with sufficient spaces. That is, change your query to (SELECT ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, verkoopprijs, doel AS status, straat AS adres, hoofdtype FROM panden WHERE doel = 'te koop') UNION (SELECT ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, verkoopprijs, status, adres, '' AS hoofdtype FROM gronden WHERE status = 'te koop') ORDER BY verkoopprijs ASC or something like: (SELECT ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, verkoopprijs, status, adres, '' AS hoofdtype FROM gronden WHERE status = 'te koop') UNION (SELECT ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, verkoopprijs, doel AS status, straat AS adres, hoofdtype FROM panden WHERE doel = 'te koop') ORDER BY verkoopprijs ASC TIA, Stijn Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNION
Ha! Thx Michael, your first suggestion (reversing the order of the UNION terms) worked like a charm. greetz, Stijn Michael Stassen wrote: Stijn Verholen wrote: Jigal van Hemert wrote: Here is my solution: (SELECT ID, referentie, postcode, gemeente, kadastrale_opp,prijs_zichtbaar, verkoopprijs, status, adres, '' AS hoofdtype FROM gronden WHERE status = 'te koop' ) UNION (SELECT ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, verkoopprijs, doel AS status, straat AS adres, hoofdtype FROM panden WHERE doel = 'te koop' ) ORDER BY verkoopprijs ASC As u can see, I am trying to schmuck up the field that is only present in one table, by selecting nothing and naming it as that field name (hoofdtype). The query succeeds, but the row contains no data for hoofdtype. UNION simply adds the records from each part to the result so far. So instead of adding hoofdtype data to the corresponding records that were already present in the result from the first query, the new records are simply appended to the record set so far. I gues you want to use a JOIN (probably a LEFT JOIN in your case) and tell MySQL which columns to use to find matching records in the second table and then combine the data from both tables in one record set. Suppose `referentie` is the column to use for matching data from both tables: SELECT g.`ID`, g.`referentie`, g.`postcode`, g.`gemeente`, g.`kadastrale_opp`, g.`prijs_zichtbaar`, g.`verkoopprijs`, g.`status`, g.`adres`, p.`hoofdtype` FROM `gronden` AS g LEFT JOIN `panden` AS p ON g.`referentie` = p.`referentie` WHERE g.`status` = 'te koop' ORDER BY g.`verkoopprijs` ASC ON g.`referentie` = p.`referentie` could be replaced with USING (`referentie`) since in both tables the field has the same name. Regards, Jigal. Thx for your reply. AFAIK, JOINs are used for combining data from tables that have corresponding values. This is not the case here. The tables are very much alike in structure, but not in content. I've tried your statement, and now it only returns data from one of the tables. My guess is I'm stuck with the UNION solution. When I leave out the schmucking-up things, I get an error saying that the number of tables do not correspond. You mean number of columns, don't you? When I enter the '' selector again, the query succeeds, but with no values for 'hoofdtype'. I've also tried making sure that every field is getting it's own column by adding extra '' selectors, to no avail. Any ideas ? I have one. Prior to mysql 4.1.1, column widths were determined by the first SELECT in the UNION. If you are using an earlier version, your first SELECT sets the column width for hoofdtype to 0, because you are selecting an empty string. You should be able to fix this by either reversing the order of your two SELECTs, or by replacing the empty string with a string filled with sufficient spaces. That is, change your query to (SELECT ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, verkoopprijs, doel AS status, straat AS adres, hoofdtype FROM panden WHERE doel = 'te koop') UNION (SELECT ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, verkoopprijs, status, adres, '' AS hoofdtype FROM gronden WHERE status = 'te koop') ORDER BY verkoopprijs ASC or something like: (SELECT ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, verkoopprijs, status, adres, '' AS hoofdtype FROM gronden WHERE status = 'te koop') UNION (SELECT ID, referentie, postcode, gemeente, kadastrale_opp, prijs_zichtbaar, verkoopprijs, doel AS status, straat AS adres, hoofdtype FROM panden WHERE doel = 'te koop') ORDER BY verkoopprijs ASC TIA, Stijn Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bash powered MySQL Queries
I just wanted to know what would be the easiest way to retrieve simple data from a MySQL database from a bash script. Easiest way I've used to do it is: mysql EOQ select count(1) from tables; EOQ Which allows you to feed random queries to mysql from a bash script. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bash powered MySQL Queries
On Sun, 30 Jan 2005, Andy wrote: Hi all I just wanted to know what would be the easiest way to retrieve simple data from a MySQL database from a bash script. I do this a lot - just construct the query and dump it into a file from within the script, eg: echo select * from widgets where colour = 'red'; /tmp/query Then pipe the query into the mysql command line client and the result is echoed to stdin: $RESULT=`mysql -u user -ppassword widget_sales /tmp/query` and the variable $RESULT contains the result of your query. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How-to copy a column
I have a table (table1) which has 4 columns, I want to copy all the contents of col1 into col2. Col3 is the primary unique key, so the copy has to keep the data matched with col3. Sounds like what you want to do is update table1 set col2 = col1; which will copy the contents of col1 in each record to col2. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bash powered MySQL Queries
Thank you all for your replies. I think that ShellSQL is really the thing I am looking for. With kind regards Andy On Sun January 30 2005 23:50, Andy wrote: Hi all I just wanted to know what would be the easiest way to retrieve simple data from a MySQL database from a bash script. With kind regards Andy -- --- Registered Linux user number 379093 --- -- -- --- Registered Linux user number 379093 --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bash powered MySQL Queries
On Mon, Jan 31, 2005 at 06:57:58PM +, Edward Macnaghten wrote: 1 - The output is not cluttered with headers, and a means exist to easily separate fields when there is more than one column or row in the query. The mysql --batch option should take care of that for you. -Jason Martin -- If your attack is going well, then it's an ambush.. This message is PGP/MIME signed. pgpGbq3SC6aXz.pgp Description: PGP signature
Re: ANNOUNCE: ShellSQL 0.7
Forgive me replying to my own post There is a bug in the MySQL engine of this suite (shmysql). This has been fixed in ShellSQL-0.7.1 at the below mentioned places. If you have downloaded 0.7 you should download 0.7.1 and re-install (only shmysql is effected here, all the other programs are the same). Thanks for your patience Eddy Edward Macnaghten wrote: Hi Announcing ShellSQL 0.7 to an unsuspecting world... ShellSQL is a utility to allow SQL to be intergrated easily into UNIX/LINUX shell scripts. The web page is at http://www.edlsystems.com/shellsql - and at sourceforge at http://sourceforge.net/projects/shellsql . Version 0.7 includes many bug fixes and tidy ups. A new utility to import a file into a table (or use it to update tables), more input/output formats (CSV, Tab delimited etc) and a new engine (freetds for MS-SQL/Sybase engines) - (The MySQL engine was one of the originals there). Enjoy Eddy Macnaghten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication suddenly stops on mysql 4.1.7 with Slave_IO_Running: No
Hello. I've looked through the bug database, and the only thing that I've found was an already-closed bug: http://bugs.mysql.com/bug.php?id=6148 Check that your server passes rpl_relayspace.test. Go to the mysql-test directory and execute: ./mysql-test-run t/rpl_relayspace.test Jan Kirchhoff [EMAIL PROTECTED] wrote: Hi, My problem still goes on... After having had the problem 2 more times within 1 day, I decided to re-do the replication (copy the whole database onto the slave with rsync and reset master and slave). That only lasted for little more than 1 day and I ended up with the same error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. I can look at the binlog with mysqlbinlog on the master and the slave; no errors or problems. After a simple SLAVE START without having done any changes to the database, the slave thread startet again and caught up with the master. I've been using mysql's replication-feature since it first came up in 1999 or 2000 and dealt with lots of problems and workarounds, but this one is weird. Any ideas anybody? Jan Hello, I have a replication setup on to linux boxes (debian woody, kernel 2.4.21-xfs, mysql 4.1.7-standard official intel-compiler binary from mysql.com). master:~# mysqladmin status Uptime: 464848 Threads: 10 Questions: 296385136 Slow queries: 1752 Opens: 2629 Flush tables: 1 Open tables: 405 Queries per second avg: 637.596 slave:~# mysqladmin status Uptime: 463460 Threads: 2 Questions: 292885156 Slow queries: 6 Opens: 2510 Flush tables: 1 Open tables: 327 Queries per second avg: 631.953 both systems have identical hardware (P4 2.4ghz, 3GB RAM, SCSI-Hardware-RAID) connection is gigabit-ethernet. Everything used to work fine, but I wanted to get rid of InnoDB since I did only use that for very big table containing historical data and those tables were moved to another server. I ran out of discspace, innodb-datafiles can only grow but not shrink and i didn't need it anyway, so it had to go. I stopped the slave, changed all left over innodb-tables to myisam, added skip-innodb to my.cnf on the master and the slave, restarted the server, renewed the replication by doing it the classical way: flush tables with read log, copy the /var/lib/mysql on the slave (not much, just around 20GB), reset master, unlock tables. Then start the slave-mysqld, reset slave, slave start. Everything was fine and very fast for 4 days (from saturday till wednesday afternoon), then suddenly the slave stopped. this is where the weird stuff starts: show slave status tells me everything is fine, just Slave_IO_Running: No is wrong. After typing slave start, it says Slave_IO_Running: Yes, and Slave_SQL_Running: No. Very strange. Now i did a slave stop;slave start; and everything is fine again, the slave catches up and goes on. Today (thursday afternoon), the same thing happens again and can be solved again by slave stop;slave start;. Now it happened again around 10pm. Again, the stop-start-trick made it working again. I add the output of my mysql-shell Can anybody help me with that? This is a production system under heavy load and I can't play around with different mysql-versions and such... If I don't find a solution really quick, I'll have to do help myself with some shell-skript-daemon checking if replication is running and issuing stop slave;start slave-commands otherwise... not really the way it should be :( Thanks Jan SLAVE: slave:~# cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 2 model name : Intel(R) Pentium(R) 4 CPU 2.40GHz stepping: 7 cpu MHz : 2392.077 cache size : 512 KB fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm bogomips: 4771.02 slave:~# free total used free sharedbuffers cached Mem: 31051042355364 749740 04401514104 -/+ buffers/cache: 8408202264284 Swap: 779144 428072 351072 MASTER master:~# cat
Re: disappearing data
Sheryl Canter wrote: I've got a weird problem that's driving me nuts. I'm updating a set of scripts for a customer database that supports sending out software registration codes in real time. I've had this working for some time now, but I'm having the most frustrating problem. Data I've inserted simply VANISHES. Sheryl: A sure way to fix weird problems like this is to get organized a bit. Here is how I would approach this problem: * Make sure all of your queries are being sent through the same interface in your script. The way I accomplish this in mine is to create a subroutine called safe_query() and use only that to issue a query. This way you can control a lot of things, such as error checking, logging, timing, etc. * Make sure to check for errors diligently. * Make your version of safe_query() logs everything query it is sending to the client * Study the logs thoroughly and with a clear mind ( good sleep strongly recommended) * If after implementing the above steps the problem does not become obvious, run mysqld with --log option and capture every command it receives. You could indeed have a goblin script/cron-job that deletes the records or something crazy like that, and this will help you detect it. -- Sasha Pachev AskSasha Linux Consulting http://www.asksasha.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing own password on 3.x
I'm asking on behalf of someone; I don't have access to the
machine in question:
How do you change your own password in MySQL 3.23.58, without
access to the mysql table?
The user in question discovered that he was only able to change
the password from the particular machine he was logged in from,
not for 'user'@'%'.
I see that the docs say that the SET PASSWORD FOR format is
possible only for clients with access to the mysql table. But
when the user logged into machine 'server1' and issued SET
PASSWORD = PASSWORD('newpassw0rd'), he set the password for
[EMAIL PROTECTED] only, not for [EMAIL PROTECTED],
[EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], etc.
Is there any way he can do this, or does he need an admin
to issue a SET PASSWORD FOR command?
Thanks. I didn't see this raised on the mailing lists
recently, or in Paul's book, but I'd think it would be
something people would want to do fairly commonly.
Jesse Sheidlower
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing own password on 3.x
Jesse Sheidlower wrote:
I'm asking on behalf of someone; I don't have access to the
machine in question:
How do you change your own password in MySQL 3.23.58, without
access to the mysql table?
The user in question discovered that he was only able to change
the password from the particular machine he was logged in from,
not for 'user'@'%'.
I see that the docs say that the SET PASSWORD FOR format is
possible only for clients with access to the mysql table. But
when the user logged into machine 'server1' and issued SET
PASSWORD = PASSWORD('newpassw0rd'), he set the password for
[EMAIL PROTECTED] only, not for [EMAIL PROTECTED],
[EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], etc.
Is there any way he can do this, or does he need an admin
to issue a SET PASSWORD FOR command?
Jesse:
[EMAIL PROTECTED] is not the same as [EMAIL PROTECTED] They are separate entities and have
their own sets of privileges, unless the user was created with a wild card in
the host, which does not appear to be the case in your situation.
Therefore, in order to change the password for all the personalities of this
user, he needs to log in from each of his personality hosts, and set it on a
case-by-case basis.
--
Sasha Pachev
AskSasha Linux Consulting
http://www.asksasha.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL syntax error: help a noob
Dear list, My ColdFusion server tells me I have an error in my query syntax, but I can't work out what it is - because I'm working with code that someone very kindly gave me and I only have a vague idea of what the first line's doing! Can anyone see the problem here? SELECT DATEDIFF(leadtime_type, GETDATE(), deadline)'Difference', tasks.leadtime, tasks.lead_time_type_id, leadtime_type.leadtime_type FROM tasks JOIN leadtime_type ON tasks.lead_time_type_id = leadtime_type.leadtime_type_id Thanks in advance, CK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax error: help a noob
Chris Kavanagh wrote: My ColdFusion server tells me I have an error in my query syntax, but I can't work out what it is - because I'm working with code that someone very kindly gave me and I only have a vague idea of what the first line's doing! Can anyone see the problem here? SELECT DATEDIFF(leadtime_type, GETDATE(), deadline)'Difference', tasks.leadtime, tasks.lead_time_type_id, leadtime_type.leadtime_type FROM tasks JOIN leadtime_type ON tasks.lead_time_type_id = leadtime_type.leadtime_type_id It would be helpfll if you told us what error message you got, and what version of MySQL you are using. The only obvious error I can spot is GETDATE(), this is not a standard MySQL function. Try CURDATE(). -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL syntax error: help a noob
[snip] My ColdFusion server tells me I have an error in my query syntax, but I can't work out what it is - because I'm working with code that someone very kindly gave me and I only have a vague idea of what the first line's doing! Can anyone see the problem here? SELECT DATEDIFF(leadtime_type, GETDATE(), deadline)'Difference', tasks.leadtime, tasks.lead_time_type_id, leadtime_type.leadtime_type FROM tasks JOIN leadtime_type ON tasks.lead_time_type_id = leadtime_type.leadtime_type_id [/snip] The datediff() function is new to version 4.1. What version of mysql are you running? --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL + PHP - Search Engine question!
Hi All - I'm building a search engine and what I would like to do is run a search and get the number of results, but still use the LIMIT command so I am not returning a ton of rows all at once. Is this the best way to go about searching? Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED]
Re: SQL syntax error: help a noob
Thanks very much for the replies, guys. My version is 4.1.7-max. The error message I get is: -- Error Executing Database Query. Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' deadline)'Difference', tasks.leadtime, tasks.lead_time_type_id, leadtime_' at line 1 -- I changed GETDATE() to CURDATE() but it still gives me the same error. Thanks for the suggestion anyway, Roger. Best regards, CK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql + PHP - Search
I just answered my own question actually! - snip from php.net - MySQL 4.0 supports a fabulous new feature that allows you to get the number of rows that would have been returned if the query did not have a LIMIT clause. To use it, you need to add SQL_CALC_FOUND_ROWS to the query, e.g. $sql = Select SQL_CALC_FOUND_ROWS * from table where state='CA' limit 50; $result = mysql_query($sql); $sql = Select FOUND_ROWS(); $count_result = mysql_query($sql); You now have the total number of rows in table that match the criteria. This is great for knowing the total number of records when browsing through a list. -- Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED]
RE: SQL syntax error: help a noob
I think datediff only takes two arguments and you have three listed. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Chris Kavanagh Sent: Monday, January 31, 2005 5:33 PM To: mysql@lists.mysql.com Subject: Re: SQL syntax error: help a noob Thanks very much for the replies, guys. My version is 4.1.7-max. The error message I get is: -- Error Executing Database Query. Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' deadline)'Difference', tasks.leadtime, tasks.lead_time_type_id, leadtime_' at line 1 -- I changed GETDATE() to CURDATE() but it still gives me the same error. Thanks for the suggestion anyway, Roger. Best regards, CK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SOLVED: SQL syntax error: help a noob
On 31 Jan 2005, at 11:39 pm, Tom Crimmins wrote: I think datediff only takes two arguments and you have three listed. Nailed it! Thanks, Tom. Best regards, CK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a query using multiple LEFT JOINS
I'm hoping someone can help with a little problem I'm having with a query. In the query below I wish to return as least one row per tbl1, however I am only getting rows where there is at least an entry for tbl2 : SELECT ... FROM tbl1 as d LEFT JOIN tbl2 as r ON d.dcode=r.dcode LEFT JOIN tbl3 as pc ON (r.code=pc.code AND pc.from_period = 200412 AND pc.to_period 200412) LEFT JOIN tbl4 as st ON st.scode=r.scode WHERE r.mcode='AB' AND d.status!='X' AND d.region='1A' AND r.year=2004 AND r.month IN (1,2,3,4,5,6,7,8,9,10,11,12) AND st.group = 'B' GROUP BY d.dcode, r.code Can anyone help me see the light and show me where I'm being stupid? TIA Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
missing my.cnf file?
I have inhertited this old Solaris x86 system with MYSQL installed on it. mdb:/tmp 15:38:08 [539]# uname -a SunOS mdb.hitbox.com 5.7 Generic_106542-24 i86pc i386 i86pc I would rate myself as an advanced novice WRT MYSQL To a large deree this system is running OK. However, it does seem to be smacking into the limit of 100 concurrent connections. AFAIK, no /etc/my.cnf file no $HOME/.my.cnf file exist. I've looked looked at the startup scripts do not see any --config-file qualifier be specified. datadir=/a/mysqldata/ and there is no *cnf file in this folder either # find / -name \*cnf -ls 941333 -rw-rw 1 root dba 2179 Apr 9 2002 /a/home/mysql/mysql-3.23.49/support-files/my-small.cnf 941343 -rw-rw 1 root dba 2464 Apr 9 2002 /a/home/mysql/mysql-3.23.49/support-files/my-medium.cnf 941353 -rw-rw 1 root dba 2480 Apr 9 2002 /a/home/mysql/mysql-3.23.49/support-files/my-large.cnf 941363 -rw-rw 1 root dba 2502 Apr 9 2002 /a/home/mysql/mysql-3.23.49/support-files/my-huge.cnf 3826293 -rw-r--r-- 1 root dba 2179 Nov 15 09:57 /a/home/mysql/mysql-3.23.49/share/mysql/my-small.cnf 3826303 -rw-r--r-- 1 root dba 2464 Nov 15 09:57 /a/home/mysql/mysql-3.23.49/share/mysql/my-medium.cnf 3826313 -rw-r--r-- 1 root dba 2480 Nov 15 09:57 /a/home/mysql/mysql-3.23.49/share/mysql/my-large.cnf 3826323 -rw-r--r-- 1 root dba 2502 Nov 15 09:57 /a/home/mysql/mysql-3.23.49/share/mysql/my-huge.cnf 6962157 -rw-r--r-- 1 root other7146 Jul 11 2002 /a/local/ssl/openssl.cnf I may be wrong but I am convinced that MYSQL is reading some file to acquire values for its varialbles. How do I identify which file it is using? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with a query using multiple LEFT JOINS
If you mean that you want to get a row even if tbl2 does not have a matching row for dcode, then move the conditions into the ON clause. Example based off of what you had: SELECT FROM tbl1 as d LEFT JOIN tbl2 as r ON (d.dcode=r.dcode AND r.mcode='AB' AND r.year=2004 AND r.month IN (1,2,3,4,5,6,7,8,9,10,11,12)) LEFT JOIN tbl3 as pc ON (r.code=pc.code AND pc.from_period = 200412 AND pc.to_period 200412) LEFT JOIN tbl4 as st ON st.scode=r.scode WHERE d.status!='X' AND d.region='1A' AND st.group = 'B' GROUP BY d.dcode, r.code You may want to do the same for tbl4 depending on the behavior you are looking for. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Graham Cossey Sent: Monday, January 31, 2005 5:48 PM To: mysql@lists.mysql.com Subject: Help with a query using multiple LEFT JOINS I'm hoping someone can help with a little problem I'm having with a query. In the query below I wish to return as least one row per tbl1, however I am only getting rows where there is at least an entry for tbl2 : SELECT ... FROM tbl1 as d LEFT JOIN tbl2 as r ON d.dcode=r.dcode LEFT JOIN tbl3 as pc ON (r.code=pc.code AND pc.from_period = 200412 AND pc.to_period 200412) LEFT JOIN tbl4 as st ON st.scode=r.scode WHERE r.mcode='AB' AND d.status!='X' AND d.region='1A' AND r.year=2004 AND r.month IN (1,2,3,4,5,6,7,8,9,10,11,12) AND st.group = 'B' GROUP BY d.dcode, r.code Can anyone help me see the light and show me where I'm being stupid? TIA Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table scan in join on primary keys??
I'm updating one table with data from another and finding that the server is doing a table scan on the second table, even though it's a simple join on the primary keys. This doesn't seem right. The query looks liket this: UPDATE a SET a.y TO b.a WHERE a.primary_key = b.primary_key and if I do the equivalent SELECT: EXPLAIN SELECT * FROM a, b WHERE a.primary_key = b.primary_key it shows that it's doing a table scan on table b. Is this normal? Is it because primary keys are unique? I had imagined that this would be a very fast operation, but it's not, it's surprisingly slow. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Log data transfer amount?
Anyone have any ideas on this one? : I've scoured the MySQL manuals... does anyone know if there's any way to log the amount of data that individual queries produce? Eg: like bytes transferred in a web server log? - John -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] Professional Lasso / PHP / MySQL / FileMaker Pro Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I want to get schema infomation in MySQL DB for schema.
Hi. ORACLE DBMS have a information for table or column in database dictionary, such as, USER_TAB_COLUMNS, USER_TABLES. Don't have any data base dictionary in MySQL, as ORACLE? thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subquery Oddity? Or where is the error I'm missing?
While working tonight on some query work I came across the following situation. Bare with me as I build up all the pieces of the final two queries, then notice how the subqueried version fails, but the substituted version doesn't. What am I missing in here: mysql SELECT parent_path FROM category WHERE id=2; +-+ | parent_path | +-+ | 1 | +-+ 1 row in set (0.00 sec) mysql SELECT CONCAT((SELECT parent_path FROM category WHERE id=2), ,2); +-+ | CONCAT((SELECT parent_path FROM category WHERE id=2), ,2) | +-+ | 1,2 | +-+ 1 row in set (0.00 sec) mysql SELECT * FROM category WHERE parent_path=CONCAT((SELECT parent_path FROM category WHERE id=2), ,2); Empty set (0.00 sec) mysql SELECT * FROM category WHERE parent_path=1,2; ++++--+---+-+--+ | id | name | parent | children | leafs | parent_path | seq | ++++--+---+-+--+ | 3 | Developers | 2 |0 | 0 | 1,2 |0 | | 5 | Users | 2 |0 | 0 | 1,2 |0 | ++++--+---+-+--+ 2 rows in set (0.00 sec) mysql SELECT * FROM category WHERE parent_path LIKE CONCAT((SELECT parent_path FROM category WHERE id=2), ,2); Empty set (0.00 sec) mysql SELECT VERSION(); +-+ | VERSION() | +-+ | 4.1.5-gamma | +-+ 1 row in set (0.00 sec) -- Ryan Sommers [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table scan in join on primary keys??
If those were your real queries, I'd say a table scan is appropriate, as you are asking for every row (no WHERE condition, just a join). This would be a lot easier to answer if you'd actually show us the EXPLAIN output. Michael Nick Arnett wrote: I'm updating one table with data from another and finding that the server is doing a table scan on the second table, even though it's a simple join on the primary keys. This doesn't seem right. The query looks liket this: UPDATE a SET a.y TO b.a WHERE a.primary_key = b.primary_key and if I do the equivalent SELECT: EXPLAIN SELECT * FROM a, b WHERE a.primary_key = b.primary_key it shows that it's doing a table scan on table b. Is this normal? Is it because primary keys are unique? I had imagined that this would be a very fast operation, but it's not, it's surprisingly slow. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table scan in join on primary keys??
If those were your real queries, I'd say a table scan is appropriate, as you are asking for every row (no WHERE condition, just a join). This would be a lot easier to answer if you'd actually show us the EXPLAIN output. Michael Nick Arnett wrote: I'm updating one table with data from another and finding that the server is doing a table scan on the second table, even though it's a simple join on the primary keys. This doesn't seem right. The query looks liket this: UPDATE a SET a.y TO b.a WHERE a.primary_key = b.primary_key and if I do the equivalent SELECT: EXPLAIN SELECT * FROM a, b WHERE a.primary_key = b.primary_key it shows that it's doing a table scan on table b. Is this normal? Is it because primary keys are unique? I had imagined that this would be a very fast operation, but it's not, it's surprisingly slow. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Column Permissions?
Hello, I am wondering if it is possible to limit privileges for a user account based on information stored within specific columns. For example, I plan to include a `username` column on a table with the hope of having each user log into the database using their own credentials and only be able to INSERT, UPDATE, SELECT, DELETE rows where their username is what the `username` column is populated with. Keep in mind that the users will have access to issue any query they wish against the database so I would like to restrict this at the server level. Thank you in advance. -- Thomas Matthews [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bash powered MySQL Queries
Forgive me for blowing my own trumpet here. The advantage with ShellSQL over this method is 1 - The output is not cluttered with headers, and a means exist to easily separate fields when there is more than one column or row in the query. 2 - The connection is persistant, whereas running mysql for each command will open and close a connection with all the overhead that comes with that. ShellSQL also enables you to do transactions begin and commit at the beginning and end of the script. Eddy andy thomas wrote: On Sun, 30 Jan 2005, Andy wrote: Hi all I just wanted to know what would be the easiest way to retrieve simple data from a MySQL database from a bash script. I do this a lot - just construct the query and dump it into a file from within the script, eg: echo select * from widgets where colour = 'red'; /tmp/query Then pipe the query into the mysql command line client and the result is echoed to stdin: $RESULT=`mysql -u user -ppassword widget_sales /tmp/query` and the variable $RESULT contains the result of your query. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
