Re: Best Practices
Hello all, I am using this script and it takes 100 % of the process, can anyone tell me how to optimize this, insert into incoming select s.Date as Datein, s.Time as Timein, e.Date as Dateend, e.Time as Timeend, s.CallingStationId, s.CalledStationId, SEC_TO_TIME(unix_timestamp(concat(e.Date,' ',e.Time)) - unix_timestamp(concat(s.Date,' ',s.Time))) as time from VOIP s left join VOIP e on ( s.CallingStationId=e.CallingStationId and s.CalledStationId=e.CalledStationId and e.AcctStatusType='Stop' ) where s.AcctStatusType='Start' and s.Time e.Time and s.Date = e.Date and length(s.CallingStationId) 8 group by s.Time,s.CallingStationId,s.CalledStationId,e.CalledStationId,e.CallingStati onId order by s.Date,s.Time ASC; Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error after installing mysql 4.1
Hello. Not enough info to make a conclusion. Send us information about version of Windows you use, contents of my.ini. Dave Dyer [EMAIL PROTECTED] wrote: After installing mysql 4.1 over an existing mysql 4.0.x, the service refuses to start with this complaint: Fatal error: Can't open privilege tables: File '\\pumpkin\f\mysql\share\charsets\?.conf' not found (Errcode: 22) This is windows, with mysql installed in a nondefault directory. -- 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: using mysqldump to export BLOB tables
Hello. What version of MySQL do you use? In my practice mysqldump works well enough with tables which have fields of blob data types. Caron, Christian [EMAIL PROTECTED] wrote: Hi, I have been looking around (manual, google, etc.) to find out if I can use mysqldump with BLOB tables, but to no avail. Is it possible to do so, and if yes, is there any flag I need to give to mysqldump? Or can you point me to the manual page that describes that? The page http://dev.mysql.com/doc/mysql/en/mysqldump.html did not enlight me. Thanks! - Christian Caron -- 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: Trans.: Re: Read locks with InnoDB?
In article [EMAIL PROTECTED], Julien ALLANOS [EMAIL PROTECTED] writes: Thanks, I've already read these pages. Here is a test example I've done: 1/ User A: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 2/ User B: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 3/ User A: START TRANSACTION; 4/ User B: START TRANSACTION; 5/ User A: SELECT * FROM MyTable LIMIT = 1; I got a one-row result. 6/ User B: SELECT * FROM MyTable LIMIT = 1; I got the same one-row result, and I need to get the next one instead, because this one is already being treated by user A! I really want to have a SELECT FOR DELETE thing: as soon as a row is read, it is deleted. Is this possible? Not quite, but close. You need a SELECT.. FOR UPDATE. Then in step 6/ above the B connection will get blocked. Now you can delete the row in the A connection and commit. Then B will get unblocked and return the next row. Just two things: 1. Use ORDER BY pkey LIMIT 1 instead of just LIMIT 1. You might get away without the ORDER, but the behavior is undefined. 2. Remember the result of the SELECT.. FRO UPDATE, do the DELETE and COMMIT immediately, and process the row values afterwards if possible. This will cut down the time B gets blocked. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to bring up local replication / socket-error
Hello Gleb, thanks for your hint - I will try this workaround to get the replication up and running. Is there a point on your TODO-List that sounds like 'splitting binary logs per database to enable replicating a single database instead of transfering Gigs of traffic and using only few bytes of it'? :-) It also would be very useful to add a feature on the master to define which databases/tables should or shouldn't be logged into the bin-log. Greetings from Germany Lutz - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 09, 2004 3:49 PM Subject: Re: How to bring up local replication / socket-error Hello. See: http://bugs.mysql.com/bug.php?id=3310 Lutz Maibach [EMAIL PROTECTED] wrote: Hi, I'm trying to set up a replication client on the same server as the master is running. Master is port 3306 and its socket /mymaster/mysql.sock. I set up the repl-client to connect to localhost (tried the ip-address too) on port 3306. When I start the replication I get the following error: Error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' errno: 2002 The question is how to tell the replication client that the right socket is located in /mysqlmaster. I tried to set a symbolic link from /tmp/mysql.sock to mysqlmaster.sock but only got a fatal error 1236: Could not open logfile.. Help would be appreciated Greetz from Germany Lutz Maibach P.S: I only try to get up this strange looking constellation cause I have to replicate a single database from the master to a customers server. The customer may not have a look at the other databases running on the master, but with normal replication he would get all the data into his relaylog. So I want to replicate the customers database to the local replication and the the customers server to replicate this local replication where only his data is stored in the binlog. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sequencial Replication
Hello, I understand that the slave executes the sqls from binlog in sequence, but are there any plans to make it multi-threaded so that it executes multiple sqls from binlog. Thanks Arvind. -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: 08 November 2004 13:02 To: [EMAIL PROTECTED] Subject: Re: Sequencial Replication Hello. MySQL slave receives all updates, but executes them in queue. More about implementation of replication you can read at: http://dev.mysql.com/doc/mysql/en/Replication_Implementation_Details.html Arvind Gangal [EMAIL PROTECTED] wrote: Hi Gurus, As I understand the slave replicates with the master reading one query at a time from the binlog files. And at times the slave comes across a long update query and unless this has completed it will not pick up the next sql even though the other sql is on other table or may be another database. Is there a mechanism of running multiple sql slave threads in order to keep slave in sync to the master to the max. I reckon this would mean remembering multiple positions on the binlog. TIA Arvind Gangal http://www.lastminute.com ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** __ __ This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk __ __ -- 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] This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication of Data
Hi Chums, Can any body tell me if the following case is possible ? Actually , I have Oracle Server , and I want to replicate the data across the other servers, but at the other end ( replicated servers) , I want to have MySQL Database Server too, and What all I wanna know is if it is possible to sync the data from Oracle Database to MySQL Database Server , If yes Can you tell me the way or the instructions to tell me how ? :) Or Else , Can any body tell me the procedure from migrating the database from Oracle to MySQL Server ? --NIkhil
GIS - NULL columns
I installed mySQL server from Wizard and then i create table:
create table geom ( g POINT) ENGINE = MYISAM;
but i can't add any object to the table. I wrote:
insert into geom values(PointFromText('POINT(1,1)'));
and then i saw in the table NULL values:
SELECT AsText(g) FROM geom;
| g|
| NULL |
| NULL |
What can i do?? Do i set some variables??
My ini files:
#This File was made using the WinMySQLAdmin 1.4 Tool
#2004-11-08 23:39:15
#Uncomment or Add only the keys that you know how works.
#Read the MySQL Manual for instructions
[mysqld]
basedir=D:/MySQL/MySQL Server 4.1
#bind-address=10.1.10.34
datadir=D:/MySQL/MySQL Server 4.1/data
#language=D:/MySQL/MySQL Server 4.1/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
#set-variable=key_buffer=16M
[WinMySQLadmin]
Server=D:/MySQL/MySQL Server 4.1/bin/mysqld-nt.exe
user=
password=
/tmp/mysql.sock disappeared on mac 10.3
Hello, OS : macos X 10.3 mysql version 4.0.21, for apple-darwin6.8 (powerpc) it's seems that after my last crash, the socket of mysql '/tmp/mysql.sock' has been deleted (and i have read afterwards that it should have been protected with a sticky bit) i thought that restarting mysql daemon would fix this problem but i encounter the following problem : when starting Raspoutine3:/usr/local/mysql admin$ bin/mysqld_safe --user=mysql --log i get touch: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied chown: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied Starting mysqld daemon with databases from /usr/local/mysql/data bin/mysqld_safe: line 302: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied rm: /usr/local/mysql/data/Raspoutine3.local.pid: Permission denied bin/mysqld_safe: line 1: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied tee: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied 041110 14:03:44 mysqld ended tee: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied though user 'mysql' is the owner of directory '/usr/local/mysql/data/' indeed Raspoutine3:/usr/local/mysql admin$ ls -ld data/ drwxr-x--- 13 mysql wheel 442 10 Nov 00:34 data/ How can i fix this two problems : recovering socket and starting daemon thanks in advance, Kenji. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: List annoyance
On Wed, 10 Nov 2004 07:42:29 +, Stephen Moretti (cfmaster) wrote: Why is this list reply to sender and not reply to list? Why don't you read the FAQ? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sequencial Replication
As I understand it... The SQL must be executed in sequence, otherwise you'll end up with incosistency between master and slave. an example: If thread 1 is inserting data, and thread 2 is running updates based on the inserted data then you could end up with different results on the slave than on the master. I'm sure someone else could explain it better.. or possibly correctly :) Chris -Original Message- From: Arvind Gangal [mailto:[EMAIL PROTECTED] Sent: 10 November 2004 10:28 To: 'Gleb Paharenko'; [EMAIL PROTECTED] Subject: RE: Sequencial Replication Hello, I understand that the slave executes the sqls from binlog in sequence, but are there any plans to make it multi-threaded so that it executes multiple sqls from binlog. Thanks Arvind. -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: 08 November 2004 13:02 To: [EMAIL PROTECTED] Subject: Re: Sequencial Replication Hello. MySQL slave receives all updates, but executes them in queue. More about implementation of replication you can read at: http://dev.mysql.com/doc/mysql/en/Replication_Implementation_Details.htm l Arvind Gangal [EMAIL PROTECTED] wrote: Hi Gurus, As I understand the slave replicates with the master reading one query at a time from the binlog files. And at times the slave comes across a long update query and unless this has completed it will not pick up the next sql even though the other sql is on other table or may be another database. Is there a mechanism of running multiple sql slave threads in order to keep slave in sync to the master to the max. I reckon this would mean remembering multiple positions on the binlog. TIA Arvind Gangal http://www.lastminute.com ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** __ __ This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk __ __ -- 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] This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- 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: using mysqldump to export BLOB tables
That's because there's nothing particularly remarkable about dumping BLOB values. Possibly you could have problems if they're really large. However, I take it from your message that you're trying to dump a table with BLOB columns and not having success? If so, it'd be a good idea to post a summary of what you're trying and what the results are. Excuse me, I did not clearly explained my problem. I indeed used mysqldump to backup BLOB columns and it did not returned errors, but when I restored the information I got an error about a repeated index number. When I tried to open the dump file to correct the problem, I could not do anything with it because of the binary data inside. I used vi to do so. I had to manually copy the files from the old directory to the new, and then correct the problem. That's why I asked if mysqldump was handling this type of data... So I guess my real question is: which software (on Unix) will let me open a dump of mysqldump containing BLOB data, if possible? Thanks and sorry for the misleading question. Christian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: /tmp/mysql.sock disappeared on mac 10.3
The socket file is created by mysqld when it starts, and goes away when mysqld shuts down. That is, you have no socket file because mysqld is not running, not the other way around. You appear to be trying to start mysqld as OS user admin. Normally, only root has the power make the switch to user mysql called for by --user=mysql. Thus, mysqld starts running as user admin, who has no permission to touch data files owned by mysql. Try `sudo bin/mysqld_safe --user=mysql --log`. Better yet, do sudo -v sudo bin/mysqld_safe --user=mysql --log Michael Kenji LEFEVRE wrote: Hello, OS : macos X 10.3 mysql version 4.0.21, for apple-darwin6.8 (powerpc) it's seems that after my last crash, the socket of mysql '/tmp/mysql.sock' has been deleted (and i have read afterwards that it should have been protected with a sticky bit) i thought that restarting mysql daemon would fix this problem but i encounter the following problem : when starting Raspoutine3:/usr/local/mysql admin$ bin/mysqld_safe --user=mysql --log i get touch: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied chown: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied Starting mysqld daemon with databases from /usr/local/mysql/data bin/mysqld_safe: line 302: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied rm: /usr/local/mysql/data/Raspoutine3.local.pid: Permission denied bin/mysqld_safe: line 1: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied tee: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied 041110 14:03:44 mysqld ended tee: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied though user 'mysql' is the owner of directory '/usr/local/mysql/data/' indeed Raspoutine3:/usr/local/mysql admin$ ls -ld data/ drwxr-x--- 13 mysql wheel 442 10 Nov 00:34 data/ How can i fix this two problems : recovering socket and starting daemon thanks in advance, Kenji. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trans.: Re: Read locks with InnoDB?
Selon Harald Fuchs [EMAIL PROTECTED]: In article [EMAIL PROTECTED], Julien ALLANOS [EMAIL PROTECTED] writes: Thanks, I've already read these pages. Here is a test example I've done: 1/ User A: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 2/ User B: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 3/ User A: START TRANSACTION; 4/ User B: START TRANSACTION; 5/ User A: SELECT * FROM MyTable LIMIT = 1; I got a one-row result. 6/ User B: SELECT * FROM MyTable LIMIT = 1; I got the same one-row result, and I need to get the next one instead, because this one is already being treated by user A! I really want to have a SELECT FOR DELETE thing: as soon as a row is read, it is deleted. Is this possible? Not quite, but close. You need a SELECT.. FOR UPDATE. Then in step 6/ above the B connection will get blocked. Now you can delete the row in the A connection and commit. Then B will get unblocked and return the next row. Just two things: 1. Use ORDER BY pkey LIMIT 1 instead of just LIMIT 1. You might get away without the ORDER, but the behavior is undefined. 2. Remember the result of the SELECT.. FRO UPDATE, do the DELETE and COMMIT immediately, and process the row values afterwards if possible. This will cut down the time B gets blocked. Well, I've tried the following scenario: 1/ User A: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 2/ User B: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 3/ User A: START TRANSACTION; 4/ User B: START TRANSACTION; 5/ User A: SELECT * FROM MyTable LIMIT = 1 ORDER BY pkey FOR UPDATE; I got a one-row result. 6/ User B: SELECT * FROM MyTable LIMIT = 1 ORDER BY pkey FOR UPDATE; I got the same one-row result again! B isn't blocked and gets the same result than A, though A hasn't commited/rollbacked yet. Furthermore, I don't want B to be blocked: I want B to get the next result while A is processing data and deleting the current row. Any solution to this please? Thanks. -- Julien ALLANOS Silicomp-AQL The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email in error please notify the system manager or the sender immediately and do not disclose the contents to anyone or make copies. * email scanned for viruses, vandals and malicious content * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trans.: Re: Read locks with InnoDB?
In article [EMAIL PROTECTED], Julien ALLANOS [EMAIL PROTECTED] writes: Well, I've tried the following scenario: 1/ User A: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 2/ User B: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 3/ User A: START TRANSACTION; 4/ User B: START TRANSACTION; 5/ User A: SELECT * FROM MyTable LIMIT = 1 ORDER BY pkey FOR UPDATE; I got a one-row result. 6/ User B: SELECT * FROM MyTable LIMIT = 1 ORDER BY pkey FOR UPDATE; I got the same one-row result again! B isn't blocked and gets the same result than A, though A hasn't commited/rollbacked yet. That's probably because you changed the transaction isolation level. Just leave it at repeatable-read, and B will block. By the way, your SELECT syntax is wrong. You need SELECT * FROM MyTable ORDER BY pkey LIMIT 1 FOR UPDATE Furthermore, I don't want B to be blocked: I want B to get the next result while A is processing data and deleting the current row. I don't know any way how to get this. That's why I proposed to do the data processing after the DELETE and COMMIT which means that B will get blocked only for a few microseconds or so. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: List annoyance
Jochem van Dieten wrote: On Wed, 10 Nov 2004 07:42:29 +, Stephen Moretti (cfmaster) wrote: Why is this list reply to sender and not reply to list? Why don't you read the FAQ? Ah right. I see - a 2 year old article - http://www.unicom.com/pw/reply-to-harmful.html Completely disagree with this in terms of mail list management, especially when you read through the interesting summary, but I'll bow to mysql.com feelings on this. Still it : It is really bl annoying Does mean that the list archive is incomplete and therefore less useful. Does mean that I have to waste time cleaning up after this one list when I have quite enough email to handle as it is. But as I say I'll bow to mysql.com's beliefs and drop it. Stephen -- Registration for MX Europe 2005 is now open. http://www.mxeurope.org/go/registration Early bird discounts available. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GIS - NULL columns
In article [EMAIL PROTECTED],
Rafal K. [EMAIL PROTECTED] writes:
I installed mySQL server from Wizard and then i create table:
create table geom ( g POINT) ENGINE = MYISAM;
but i can't add any object to the table. I wrote:
insert into geom values(PointFromText('POINT(1,1)'));
and then i saw in the table NULL values:
SELECT AsText(g) FROM geom;
| g|
| NULL |
| NULL |
You need 'POINT(1 1)' instead of 'POINT(1,1)'. It's a pity that MySQL
silently does something else instead of complaining loudly if the
input is incorrect.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subject Headings in Tables
With all possible respect, what you posted aren't tables, those are lists. At a minimum, tables have names and one or more fields; each field will have a data type. Please post your table structure(s) and we can suggest methods you can use to generate the output you desire. Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Blomstrom [EMAIL PROTECTED] wrote on 11/09/2004 06:16:26 PM: Oops, I forgot my follow up question... I now have two tables: TABLE 1 Alabama Alaska Arizona Arkansas California Colorado Connecticut TABLE 2 Far North New England Pacific Coast South Southwest Rocky Mountains If I had put all of the above in one table, then it would be easy to display them like this: FAR NORTH Alaska NEW ENGLAND Connecticut But if I normalize my data, then I have to figure out how to make FAR NORTH from Table 2 display in a row just ahead of Alaska. How do you do that? Should I ask this on a PHP forum, or is this something people do with MySQL? Or can you use either one? Thanks. --- [EMAIL PROTECTED] wrote: Normalize, David. Normalize. Your Regions get their own table. If a country/state can belong to more than one region you will need another table to hold that association. I can't remember if this was ever recommended to you but I had a chance to re-read it recently and think this could help you to better understand the basic concepts of normalization. http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html Don't get caught up in the terminology he uses but pay close attention to how he organizes his sample data. Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Blomstrom [EMAIL PROTECTED] wrote on 11/08/2004 09:30:41 PM: I have a series of database tables focusing on the world's nations. There are basic tables that give each nation's abbreviation and parent (e.g. Eurasia) and other tables that focus on information about the people, government, etc. Now I want to introduce some regional headers. For example, current displays might look something like this: Denmark Finland France Germany Portugal Spain Alaska Arizona Florida Montana Wyoming I want to somehow modify my MySQL tables and/or the PHP scripts I use to display data so that I have the option of displaying data just like above, OR like this: NORTHERN EUROPE Denmark Finland WESTERN EUROPE France Germany SOUTHERN EUROPE Portugal Spain FAR NORTH Alaska SOUTHWEST Arizona SOUTH Florida ROCKY MOUNTAINS Montana Wyoming You can see an example of my ultimate goal at http://www.geoworld.org/about/guide/world/eur/remote.php? order=2direction=0submit=Submit I'm designing this page to function as a pop-up window that stays open as people click from nation to nation. It uses a PHP script to order the nations alphabetically or by population or other topics. Eventually, I'd like to learn how to modify the switch so that it can display nations or states with headings - like ROCKY MOUNTAINS - then if you click another setting, the nations are rearranged and the headings aren't even visible. Anyway, I just wanted to ask for tips about incorporating these regional headings into my database. My first hunch is to simply add them to one of basics tables, like this: NAMEIDTYPE United States usa Nation Rocky Mountains rm Region Colorado co State Montana mt State That way, I can simply display the entire table as is - complete with the headins - or I can use PHP to block every row where TYPE = Region. Does this sound like sensible plan, or are there better options? Thanks. __ 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] __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com
Re: error importing from mysqldump output
I agree that using a reserved word for any purpose than that for which it is reserved is a poor design choice. I also strongly encourage you to change the name of that field and any others that conflict with the reserved words list (the field name desc is another name that frequently causes this problem for just the same reason) http://dev.mysql.com/doc/mysql/en/Reserved_words.html However, mysqldump does have the option to backtick-quote all field names in its CREATE TABLE statements. The full list of options is available if you run mysqldump --help and the option you are interested in can be turned on either with -Q or --quote-names. That makes your dump command read daemon0% mysqldump -Q -S mysqld-daemon0.sock --master-data --all-databases daemin0-dump.sql Shawn Green Database Administrator Unimin Corporation - Spruce Pine Daniel Kasak [EMAIL PROTECTED] wrote on 11/09/2004 06:16:38 PM: Russell E Glaue wrote: snipped ERROR 1064 at line 14071: 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 'order int(1) NOT NULL default '0', PRIMARY KEY (moduleID), snipped Would this possibly be a bug with mysqldump ? -RG 'order' is a reserved word. I would rename that field, pronto! In my opinion the bug is not in mysqldump, but in mysql allowing you to use a fieldname that is a reserved word. And yes I know about the backticks that mysqlcc uses, but surely this causes more problems than it solves. See above example. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
enum TRUE/FALSE
I'm trying to figure out how to make Active's null as FALSE and '1' as TRUE,
in enum point of view
Can anyone help me out here, trying to learn enum's phenomenon? I'm not sure
I understood document quite clear -- as of yet :(
mysql desc Associate;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| ID | int(11) | | PRI | NULL| auto_increment |
| LocationID | int(11) | YES | | NULL||
| NTLogon | varchar(8) | YES | | NULL||
| DomainID | int(11) | YES | | NULL||
| LastName | varchar(30) | YES | | NULL||
| FirstName| varchar(30) | YES | | NULL||
| Shift| int(11) | YES | | NULL||
| QADE | enum('','1') | YES | | NULL||
| DataEntry| enum('','1') | YES | | NULL||
| QAMR | enum('','1') | YES | | NULL||
| MailRoom | enum('','1') | YES | | NULL||
| QAT | enum('','1') | YES | | NULL||
| Taping | enum('','1') | YES | | NULL||
| QAF | enum('','1') | YES | | NULL||
| Filming | enum('','1') | YES | | NULL||
| CustomerContact | enum('','1') | YES | | NULL||
| Trainee | enum('','1') | YES | | NULL||
| Active | enum('','1') | YES | | NULL||
| Creator | varchar(8) | YES | | NULL||
| NewAssociateDate | date | YES | | NULL||
+--+--+--+-+-++
20 rows in set (0.00 sec)
mysql select count(*) from Associate where Active=FALSE;
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate where Active=TRUE;
+--+
| count(*) |
+--+
| 2611 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate;
+--+
| count(*) |
+--+
| 3947 |
+--+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
finding backslash
I am trying to find everything in a column that has a backslash in it and the select statement that I am trying to use doesn't find any of them. My statement is: SELECT * FROM `team` WHERE `name` LIKE '%\%' I have also tried: SELECT * FROM `team` WHERE `name` LIKE '%\\%' Any idea how to search for a backslash in a column of a table? Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trans.: Re: Read locks with InnoDB?
Selon Harald Fuchs [EMAIL PROTECTED]: In article [EMAIL PROTECTED], Julien ALLANOS [EMAIL PROTECTED] writes: Well, I've tried the following scenario: 1/ User A: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 2/ User B: SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; 3/ User A: START TRANSACTION; 4/ User B: START TRANSACTION; 5/ User A: SELECT * FROM MyTable LIMIT = 1 ORDER BY pkey FOR UPDATE; I got a one-row result. 6/ User B: SELECT * FROM MyTable LIMIT = 1 ORDER BY pkey FOR UPDATE; I got the same one-row result again! B isn't blocked and gets the same result than A, though A hasn't commited/rollbacked yet. That's probably because you changed the transaction isolation level. Just leave it at repeatable-read, and B will block. well, i don't know what i did to make it fail but now, in repeatable-read or in read-commited mode, B blocks. By the way, your SELECT syntax is wrong. You need SELECT * FROM MyTable ORDER BY pkey LIMIT 1 FOR UPDATE yeah, mine was obviously wrong. Furthermore, I don't want B to be blocked: I want B to get the next result while A is processing data and deleting the current row. I don't know any way how to get this. That's why I proposed to do the data processing after the DELETE and COMMIT which means that B will get blocked only for a few microseconds or so. Thanks for your advice, i'm going to set the thing up this way, and, eventually re-INSERT (outside the transaction) the record if data processing returns with errors. -- Julien ALLANOS Silicomp-AQL The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email in error please notify the system manager or the sender immediately and do not disclose the contents to anyone or make copies. * email scanned for viruses, vandals and malicious content * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: enum TRUE/FALSE
[snip]
I'm trying to figure out how to make Active's null as FALSE and '1' as
TRUE,
in enum point of view
| Active | enum('','1') | YES | | NULL|
|
[/snip]
I have not tested this but have you tried enum('NULL', '1') ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: union, intersct and except operation?
Lana,
You have been asking this question for quite a while now. I think that you
do not have a satisfactory answer yet because I do not believe there is an
EXCEPT operator in the MySQL vocabulary. If you could post a link to the
page from the MySQL manual that shows this operator, we can help you
understand how to use it. Otherwise you need to take a little extra time
to explain what you want out of your data as many of us may not be
familiar with how the EXCEPT operator works in other database systems. (I
know I do not recognize the operator.)
You also need to tell us what version of MySQL you are using as many of
the possible suggested solutions to your query problem could use
version-dependent features of MySQL (like subqueries). Please post the
structure of the table or tables involved in your query. The easiest way
to do that is to use the command SHOW CREATE TABLE with the \G option (not
the ; option). A sample command would be
SHOW CREATE TABLE table \G
(documentation here:
http://dev.mysql.com/doc/mysql/en/SHOW_CREATE_TABLE.html)
Please help us to help you. Thank you for your patience.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
L a n a [EMAIL PROTECTED] wrote on 11/09/2004 08:49:18 PM:
Hello,
I've had a problem trying to get working except operator in mysql:
statement SELECT study from table WHERE keyword = 'chemistry' NOT
keyword
= 'computers' returns SQL error
I've got the following not working solutions to my problem:
SELECT [distinct] study FROM table WHERE keyword = 'chemistry' AND
keyword
(or !=) 'computers'
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT (or OR NOT)
keyword = 'computers'
To explain better what result I need to get, there is table
Id Study keyword
1 Achemistry
2 Acomputers
3 Bchemistry
4 Bcomputers
5 Cchemistry
I need to return study C, because it has only 'chemistry' keyword(not
'computers'). However, all the suggested solutions return me wrong
results:
SELECT study FROM table WHERE keyword = 'chemistry' AND keyword
'computers'
returns A, B, C results
SELECT study FROM table WHERE keyword = 'computers' AND keyword
'chemistry'
returns A,B
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT keyword =
'computers'
returns 0 results
I tried SELECT T.data_id from table T WHERE T.keyword = 'chemistry'
EXCEPT
SELECT T2.data_id from table T2 WHERE T2.keyword =computers . But it
gives
SQL error.
I, also, tried SELECT * from table as t1 left join table as t2 on
t1.study=t2.study and not t2.keyword ='computers' where t1.keyword =
'chemistry' - gives SQL error as well.
Is there a way to get exception (not) operator working in mysql at all,
does it mean that only Union (or) and Intersect (and) available?
I know that in fulltext search it's possible to do: sql = SELECT ...
MATCH...AGAINST ('+chemestry -omputers IN BOOLEAN MODE)
What about one field search?
Thank you,
Lana
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Suppress the 0 value
Hi MySQL-Users I have a simple select statement like 'select id from table'. The result is 0, becaues the id field is 0 (int-field). My problem is now how to suppress the 0 and give NULL instead of 0 as the result (i.E. everytime the result from the select is 0 it should be NULL). How can this be done within the select-statement? Thank you in advance, Martin Rytz
RE: Suppress the 0 value
From: Martin Rytz [mailto:[EMAIL PROTECTED] Hi MySQL-Users I have a simple select statement like 'select id from table'. The result is 0, becaues the id field is 0 (int-field). My problem is now how to suppress the 0 and give NULL instead of 0 as the result (i.E. everytime the result from the select is 0 it should be NULL). How can this be done within the select-statement? SELECT IF(id = 1, NULL, id) AS id ... The usage of IF() is IF(expression, true value, false value). -- Mike Johnson Smarter Living, Inc. Web Developerwww.smarterliving.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Suppress the 0 value
From: Mike Johnson [mailto:[EMAIL PROTECTED] From: Martin Rytz [mailto:[EMAIL PROTECTED] Hi MySQL-Users I have a simple select statement like 'select id from table'. The result is 0, becaues the id field is 0 (int-field). My problem is now how to suppress the 0 and give NULL instead of 0 as the result (i.E. everytime the result from the select is 0 it should be NULL). How can this be done within the select-statement? SELECT IF(id = 1, NULL, id) AS id ... The usage of IF() is IF(expression, true value, false value). Oops, my mistake. Should have been IF(id = 0, NULL, id). I was testing on a table without `0' values. Sorry for the mix-up. -- Mike Johnson Smarter Living, Inc. Web Developerwww.smarterliving.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: enum TRUE/FALSE
Made changes, now the problem has reversed:
mysql select count(*) from Associate where Active=FALSE;
+--+
| count(*) |
+--+
| 2611 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate where Active=TRUE;
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate where Active='1';
+--+
| count(*) |
+--+
| 1336 |
+--+
1 row in set (0.02 sec)
mysql desc Associate;
+--+--+--+-++---
-+
| Field| Type | Null | Key | Default| Extra |
+--+--+--+-++---
-+
| ID | int(11) | | PRI | NULL |
auto_increment |
| LocationID | int(11) | YES | | NULL | |
| NTLogon | varchar(8) | YES | | NULL | |
| DomainID | int(11) | YES | | NULL | |
| LastName | varchar(30) | | || |
| FirstName| varchar(30) | | || |
| Shift| int(11) | | | 1 | |
| QADE | enum('null','1') | YES | | NULL | |
| DataEntry| enum('null','1') | YES | | NULL | |
| QAMR | enum('null','1') | YES | | NULL | |
| MailRoom | enum('null','1') | YES | | NULL | |
| QAT | enum('null','1') | YES | | NULL | |
| Taping | enum('null','1') | YES | | NULL | |
| QAF | enum('null','1') | YES | | NULL | |
| Filming | enum('null','1') | YES | | NULL | |
| CustomerContact | enum('null','1') | YES | | NULL | |
| Trainee | enum('null','1') | YES | | null | |
| Active | enum('null','1') | YES | | NULL | |
| Creator | varchar(8) | | || |
| NewAssociateDate | date | | | -00-00 | |
+--+--+--+-++---
-+
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 10, 2004 9:35 AM
To: Scott Hamm; 'Mysql ' (E-mail)
Subject: RE: enum TRUE/FALSE
[snip]
I'm trying to figure out how to make Active's null as FALSE and '1' as
TRUE,
in enum point of view
| Active | enum('','1') | YES | | NULL|
|
[/snip]
I have not tested this but have you tried enum('NULL', '1') ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Practices
It sounds to me like they want two databases (they probably need to be on two separate servers) and that your logging application may need to pull double duty. You are being asked to keep an OLTP database in sync with an OLAP database in real time. That means that you probably need to commit changes to both at the same time OR somehow queue up a list of updates for a third-party process (any process that is NOT your application) to come back and make the batch changes you need. I had a similar situation once, thousands of ad clicks per minute had to be both logged (OLTP) and aggregated (OLAP) for billing. The solution we used was to build a raw log table (only one index) and hit that table once every minute or so with an application (not the logging application) that first took a snapshot of the records it was going to process, copied them into a long-term log, and aggregated them into the OLAP tables. Then the raw log was purged of the processed records to keep it small. We used multiple parallel processes to aggregate the raw results. What we ended up doing was running 4 aggregating processes with each process working only it's section of our client list. That way no two threads could collide on processing raw records from the same client. I know it sounds rather complex but it was able to keep up with almost 25 click-throughs per day. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Michael Haggerty [EMAIL PROTECTED] wrote on 11/09/2004 07:06:18 PM: I am working on a data warehousing solution involving mysql and have a question about best practices. We are standardized on mysql 4.1, and this is for a rather picky client. We have a relational transaction database that stores the results of customer calls and a dimensional reporting database used as a data mart by several applications. Each night, we run a process that aggregates the number of calls, the subjects of each call, and various other data to populate the reporting database. We would like to move to a real time solution, and are struggling with the best way to implment it. What we are considering is a solution where we mirror the transactional database and repopulate key tables in the reporting database every minute or few minutes. I am loathe to do this, mainly because it would add to our server load and could possibly lead to 'dirty reads' (i.e. where one table in the reporting database is populated with fresh data but others are not). At the same time, the client is demanding we implement something. Does anyone have any war stories or suggestions for how to accomplish this? Thank You, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: enum TRUE/FALSE
If you want an enum to have the possible values of NULL or 1
alter table `Associate` modify `Active` enum('1');
from the mysql manual
http://dev.mysql.com/doc/mysql/en/ENUM.html
If an ENUM column is declared to allow NULL, the NULL value is a legal
value for the column, and the default value is NULL. If an ENUM column
is declared NOT NULL, its default value is the first element of the list
of allowed values.
If you simply want a column to hold boolean data I prefer to use
tiyint(1) unsigned not null
In my application I then treat 0 as False and 1 (or any other number) as
True
I do not believe you can make mysql evaluate NULL to be False, mysql
will evaluate 1 = True and 0 = False,
TRUE And FALSE are simply aliases for 1 and 0
you have two options
1) You could simply update your table
alter table `Associate` modify `Active` enum('0','1');
(or 1 then 0 if you want true to be the default)
then
update `Associate` set `Active` = 0 where isNull(Active);
2) modify your queries
select count(*) from Associate where Active!=1 OR isNull(Active);
select count(*) from Associate where Active=1;
Chris
-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED]
Sent: 10 November 2004 14:25
To: 'Mysql ' (E-mail)
Subject: enum TRUE/FALSE
I'm trying to figure out how to make Active's null as FALSE and '1' as
TRUE, in enum point of view
Can anyone help me out here, trying to learn enum's phenomenon? I'm not
sure I understood document quite clear -- as of yet :(
mysql desc Associate;
+--+--+--+-+-+--
--+
| Field| Type | Null | Key | Default | Extra
|
+--+--+--+-+-+--
--+
| ID | int(11) | | PRI | NULL|
auto_increment |
| LocationID | int(11) | YES | | NULL|
|
| NTLogon | varchar(8) | YES | | NULL|
|
| DomainID | int(11) | YES | | NULL|
|
| LastName | varchar(30) | YES | | NULL|
|
| FirstName| varchar(30) | YES | | NULL|
|
| Shift| int(11) | YES | | NULL|
|
| QADE | enum('','1') | YES | | NULL|
|
| DataEntry| enum('','1') | YES | | NULL|
|
| QAMR | enum('','1') | YES | | NULL|
|
| MailRoom | enum('','1') | YES | | NULL|
|
| QAT | enum('','1') | YES | | NULL|
|
| Taping | enum('','1') | YES | | NULL|
|
| QAF | enum('','1') | YES | | NULL|
|
| Filming | enum('','1') | YES | | NULL|
|
| CustomerContact | enum('','1') | YES | | NULL|
|
| Trainee | enum('','1') | YES | | NULL|
|
| Active | enum('','1') | YES | | NULL|
|
| Creator | varchar(8) | YES | | NULL|
|
| NewAssociateDate | date | YES | | NULL|
|
+--+--+--+-+-+--
--+
20 rows in set (0.00 sec)
mysql select count(*) from Associate where Active=FALSE;
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate where Active=TRUE;
+--+
| count(*) |
+--+
| 2611 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate;
+--+
| count(*) |
+--+
| 3947 |
+--+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: enum TRUE/FALSE
I would suggest that if you want to compare against FALSE that you make
that one of your enumerated values. I would also make FALSE your default
value and the field not nullable. That way you don't have 3 possible
values to compare against in your field (null, empty string, and 1). If
you need three values (like: true, false, don't know) then make 3 enum
values. But, regardless of the contents of your ENUM list, I would still
make an ENUM field as NOT NULL and would set its default value to one
of its enumerated values. That way, the field should only hold what I said
it could hold.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Hamm [EMAIL PROTECTED] wrote on 11/10/2004 09:24:33 AM:
I'm trying to figure out how to make Active's null as FALSE and '1' as
TRUE,
in enum point of view
Can anyone help me out here, trying to learn enum's phenomenon? I'm not
sure
I understood document quite clear -- as of yet :(
mysql desc Associate;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| ID | int(11) | | PRI | NULL|
auto_increment |
| LocationID | int(11) | YES | | NULL| |
| NTLogon | varchar(8) | YES | | NULL| |
| DomainID | int(11) | YES | | NULL| |
| LastName | varchar(30) | YES | | NULL| |
| FirstName| varchar(30) | YES | | NULL| |
| Shift| int(11) | YES | | NULL| |
| QADE | enum('','1') | YES | | NULL| |
| DataEntry| enum('','1') | YES | | NULL| |
| QAMR | enum('','1') | YES | | NULL| |
| MailRoom | enum('','1') | YES | | NULL| |
| QAT | enum('','1') | YES | | NULL| |
| Taping | enum('','1') | YES | | NULL| |
| QAF | enum('','1') | YES | | NULL| |
| Filming | enum('','1') | YES | | NULL| |
| CustomerContact | enum('','1') | YES | | NULL| |
| Trainee | enum('','1') | YES | | NULL| |
| Active | enum('','1') | YES | | NULL| |
| Creator | varchar(8) | YES | | NULL| |
| NewAssociateDate | date | YES | | NULL| |
+--+--+--+-+-++
20 rows in set (0.00 sec)
mysql select count(*) from Associate where Active=FALSE;
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate where Active=TRUE;
+--+
| count(*) |
+--+
| 2611 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate;
+--+
| count(*) |
+--+
| 3947 |
+--+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Suppress the 0 value
- Original Message - From: Martin Rytz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 10, 2004 9:45 AM Subject: Suppress the 0 value Hi MySQL-Users I have a simple select statement like 'select id from table'. The result is 0, becaues the id field is 0 (int-field). My problem is now how to suppress the 0 and give NULL instead of 0 as the result (i.E. everytime the result from the select is 0 it should be NULL). How can this be done within the select-statement? I'm curious about why you would want to do such a thing. Are you aware of the meaning of NULL? NULL is generally understood in the relational database community to mean unknown or not applicable and is NOT the same as 0. As an example to make the distinction clear, I would store a 0 in a student test score table if the student actually got every answer on the test wrong. I would store a NULL if the student didn't write the test because of illness. Then, when calculating the class average for the test, the students who had null scores would be ignored so that they wouldn't bring down the class average while students who really did get every question wrong would have their grades lower the class average. That makes more sense than having the class average lowered also by students who simply didn't write the test for some reason. I think the question you have to ask yourself is which you want to store in the table itself: 0 or NULL. If you want to see NULL when you display the data, maybe you should be storing NULL, not zero. While there are ways to do what you want to do, I think you need to be sure that you really want to translate a 0 to a NULL before going ahead and doing it. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: enum TRUE/FALSE
[snip]
mysql select count(*) from Associate where Active=FALSE;
mysql select count(*) from Associate where Active=TRUE;
[/snip]
Why don't you set enum('TRUE','FALSE')? I ask this because normally you
would query, when using NULL (all caps), WHERE Active IS NULL or IS NOT
NULL.
I believe that you have a fundamental misunderstanding about ENUM. You
can set the values, and subsequently the default value of the field as
you wish and then construct your queries based on that.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Suppress the 0 value
You could use the IF() function and key off of the id value like this: SELECT if(id=0, null, id) as id, if(id=0,null, field2) as field2, if(id=0, null, field3) as field3 FROM tablename However, if you don't want any rows with 0 as an ID, you take care of that in the WHERE clause SELECT id FROM tablename WHERE id 0 Shawn Green Database Administrator Unimin Corporation - Spruce Pine Martin Rytz [EMAIL PROTECTED] wrote on 11/10/2004 09:45:05 AM: Hi MySQL-Users I have a simple select statement like 'select id from table'. The result is 0, becaues the id field is 0 (int-field). My problem is now how to suppress the 0 and give NULL instead of 0 as the result (i.E. everytime the result from the select is 0 it should be NULL). How can this be done within the select-statement? Thank you in advance, Martin Rytz
Re: Best Practices
Can there be a small lag between servers? If a second or two is acceptable, this sounds like a perfect environment for replication: http://dev.mysql.com/doc/mysql/en/Replication.html Basically, when the master writes something to the database, it also logs the transaction to a log file. The slave simply reads that log file and executes the same transaction locally. The additional load is very very small, your tables will all be consistent, and you can index the reporting database six ways from Sunday without touching the master. Eamon Daly - Original Message - From: Michael Haggerty [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 09, 2004 6:06 PM Subject: Best Practices I am working on a data warehousing solution involving mysql and have a question about best practices. We are standardized on mysql 4.1, and this is for a rather picky client. We have a relational transaction database that stores the results of customer calls and a dimensional reporting database used as a data mart by several applications. Each night, we run a process that aggregates the number of calls, the subjects of each call, and various other data to populate the reporting database. We would like to move to a real time solution, and are struggling with the best way to implment it. What we are considering is a solution where we mirror the transactional database and repopulate key tables in the reporting database every minute or few minutes. I am loathe to do this, mainly because it would add to our server load and could possibly lead to 'dirty reads' (i.e. where one table in the reporting database is populated with fresh data but others are not). At the same time, the client is demanding we implement something. Does anyone have any war stories or suggestions for how to accomplish this? Thank You, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: enum TRUE/FALSE
I got it made, Active enum('1') works. Thanks everyone!
I had to import old database into MySQL with '1','0' as default. I wanted to
enumerate it so that I can easily use False/true without altering anything
in the current database configurations.
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 10, 2004 10:22 AM
To: Scott Hamm; 'Mysql ' (E-mail)
Subject: RE: enum TRUE/FALSE
[snip]
mysql select count(*) from Associate where Active=FALSE;
mysql select count(*) from Associate where Active=TRUE;
[/snip]
Why don't you set enum('TRUE','FALSE')? I ask this because normally you
would query, when using NULL (all caps), WHERE Active IS NULL or IS NOT
NULL.
I believe that you have a fundamental misunderstanding about ENUM. You
can set the values, and subsequently the default value of the field as
you wish and then construct your queries based on that.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: enum TRUE/FALSE
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: I would suggest that if you want to compare against FALSE that you make that one of your enumerated values. I would also make FALSE your default value and the field not nullable. That way you don't have 3 possible values to compare against in your field (null, empty string, and 1). If you need three values (like: true, false, don't know) then make 3 enum values. But, regardless of the contents of your ENUM list, I would still make an ENUM field as NOT NULL and would set its default value to one of its enumerated values. That way, the field should only hold what I said it could hold. I agree except that I wouldn't use an ENUM for that. Firstly, it's nonstandard, and secondly, there's a column type which explicitly says what you want, namely BOOL. (Well, it maps to a TINYINT, but that's another story...) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication: bin logs not transferred, load data gives error
I am setting up a master-master replication. I have the masters set up correctly (I guess), and they update their position when changes occur. 'show slave status' and 'show master status' both show the correct positions between each server. However. Although the slave position increments to match the position on the master on both servers, the data is NOT getting updated. The position continues to increment up and up, but nothing is changed. When I had successful replication in the past, I remeber the bin-logs being transfered from the master to the slave for the updates. This is currently NOT happenning on both servers. When I execute the 'LOAD DATA FROM MASTER' I ALWAYS get an error. ERROR 1189: Net error reading from master And I get the same error on both servers. Now I know the replication configuration is correct, because when changes occur on one server, the 'Read_Master_Log_Pos' changes on the second server to match the first server. However, the data is NOT getting updated. There is no firewall between the two machines. And we are running MySQL 4.0.20 Is there a reason why the bin-logs are not being transferred? I suspect this is the reason why the data is not getting updated. And I suspect the error from 'LOAD DATA FROM MASTER' demonstrates the problem I am having. What is wrong? Any help? -RG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: enum TRUE/FALSE
I think you started with good advice then took a strange turn.
Chris Blackwell wrote:
If you want an enum to have the possible values of NULL or 1
alter table `Associate` modify `Active` enum('1');
from the mysql manual
http://dev.mysql.com/doc/mysql/en/ENUM.html
If an ENUM column is declared to allow NULL, the NULL value is a legal
value for the column, and the default value is NULL. If an ENUM column
is declared NOT NULL, its default value is the first element of the list
of allowed values.
If you simply want a column to hold boolean data I prefer to use
tiyint(1) unsigned not null
In my application I then treat 0 as False and 1 (or any other number) as
True
This is probably the best way to go.
I do not believe you can make mysql evaluate NULL to be False, mysql
will evaluate 1 = True and 0 = False,
TRUE And FALSE are simply aliases for 1 and 0
Correct. NULL is no value, while FALSE (0) and TRUE (not 0) are values.
Rows with NULL for Active will not match comparisons against TRUE or FALSE
(or any other value).
you have two options
1) You could simply update your table
alter table `Associate` modify `Active` enum('0','1');
(or 1 then 0 if you want true to be the default)
then
update `Associate` set `Active` = 0 where isNull(Active);
2) modify your queries
select count(*) from Associate where Active!=1 OR isNull(Active);
select count(*) from Associate where Active=1;
This makes no sense to me. More to the point, it won't work. ENUMs hold
strings, not numbers. Each string, however, is stored as an integer in your
rows, starting with 1. That's important, because when used in numeric
context you get the position number of the string. In other words, with
Active ENUM('0', '1'), the string '0' is 1 in numeric context and the string
'1' is 2 in numeric context. In other words,
WHERE Active=1
will return the rows with Active = '0', the opposite of what you intended.
The main advantage of using ENUM for boolean values is for human
readability. That is, you define something like
Active ENUM('T', 'F')
or
Active ENUM('yes', 'no')
so that when viewing the data you see the values of Active as text. The
disadvantage of this method is that you must do a string comparison in your
WHERE clause to select rows based on this value (Active = 'T', or Active =
'yes'). If you are going to use '0' and '1' in your ENUM, that advantage
disappears, and you might as well use a TINYINT as you originally suggested,
particularly as it makes your queries simpler (and slightly more efficient).
I recommend you define Active as a TINYINT, then store 1 for true and 0 for
false. Then your queries become
SELECT COUNT(*) FROM Associate WHERE Active;
to find all rows with Active set to true (1), and
SELECT COUNT(*) FROM Associate WHERE NOT Active;
to find all rows with Active set to false (0).
Chris
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: enum TRUE/FALSE
TRUE and FALSE are the integers 1 and 0, respectively. ENUMs hold strings
which are assigned numbers starting with 1. That means that
WHERE enum_col = TRUE
will match rows whose enum_col has the *first* value defined in the ENUM list.
Also, every ENUM has the special error value '' in position 0. When you
assign a value to enum_col which isn't in the predefined list, that's the
value you get. Hence,
WHERE enum_col = FALSE
will match rows whose enum_col were assigned values not in the ENUM list.
Note that that means the ENUM column will not only hold what I said it
could hold, as erroneous input is stored as '' (0).
http://dev.mysql.com/doc/mysql/en/ENUM.html
The one way this will work is if you define only the value which should be
true in your ENUM. That is, Active ENUM('1') or Active ENUM('True') or
the like. Then setting Active = TRUE will set Active to the first (only)
string, which is meant to be true, and setting Active = FALSE will set
Active to the empty, error string in position 0. Then
WHERE enum_col = TRUE
and
WHERE enum_col = FALSE
will work as expected.
That said, I still think TINYINT is the way to go.
Michael
[EMAIL PROTECTED] wrote:
I would suggest that if you want to compare against FALSE that you make
that one of your enumerated values. I would also make FALSE your default
value and the field not nullable. That way you don't have 3 possible
values to compare against in your field (null, empty string, and 1). If
you need three values (like: true, false, don't know) then make 3 enum
values. But, regardless of the contents of your ENUM list, I would still
make an ENUM field as NOT NULL and would set its default value to one
of its enumerated values. That way, the field should only hold what I said
it could hold.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Hamm [EMAIL PROTECTED] wrote on 11/10/2004 09:24:33 AM:
I'm trying to figure out how to make Active's null as FALSE and '1' as
TRUE,
in enum point of view
Can anyone help me out here, trying to learn enum's phenomenon? I'm not
sure
I understood document quite clear -- as of yet :(
mysql desc Associate;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| ID | int(11) | | PRI | NULL|
auto_increment |
| LocationID | int(11) | YES | | NULL| |
| NTLogon | varchar(8) | YES | | NULL| |
| DomainID | int(11) | YES | | NULL| |
| LastName | varchar(30) | YES | | NULL| |
| FirstName| varchar(30) | YES | | NULL| |
| Shift| int(11) | YES | | NULL| |
| QADE | enum('','1') | YES | | NULL| |
| DataEntry| enum('','1') | YES | | NULL| |
| QAMR | enum('','1') | YES | | NULL| |
| MailRoom | enum('','1') | YES | | NULL| |
| QAT | enum('','1') | YES | | NULL| |
| Taping | enum('','1') | YES | | NULL| |
| QAF | enum('','1') | YES | | NULL| |
| Filming | enum('','1') | YES | | NULL| |
| CustomerContact | enum('','1') | YES | | NULL| |
| Trainee | enum('','1') | YES | | NULL| |
| Active | enum('','1') | YES | | NULL| |
| Creator | varchar(8) | YES | | NULL| |
| NewAssociateDate | date | YES | | NULL| |
+--+--+--+-+-++
20 rows in set (0.00 sec)
mysql select count(*) from Associate where Active=FALSE;
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate where Active=TRUE;
+--+
| count(*) |
+--+
| 2611 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate;
+--+
| count(*) |
+--+
| 3947 |
+--+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading MySql on OSX 10.3.6
I was running ver 4.0.15 and just upgraded to the latest version of 4.1.7. In between I had done an upgrade to ver. 4.0.20. I can get the server to start and stop via the Preferences panel but before and after any upgrade it indicates that 4.0.15 is running. In Navicat which I use as the gui front end it also indicates that 4.0.15 is running. In /usr/local I have 3 MySql related directories: mysql mysql-max-4.1.7-apple-darwin7.5.0-powerpc mysql-max-4.0.20-apple-darwin7.3.0-powerpc My question is how do I get Navicat and the preferences panel to recognize 4.1.7 and do I need to remove 4.0.20 and 4.0.15? Thanks. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Practices
Yes, there can be a small lag in data updates, in fact I believe the lag time will be less than a second considering our architecture. We have been considering replication as a solution but have been hesitant to do so because I have heard there are problems with data inserted through a LOAD DATA INFILE command. We regularly import csv data from spreadsheets from people working offline and have some pretty sophisticated processes built around this requirement. Has anyone run into this problem, and are there any solutions? Thanks, Michael Haggerty --- Eamon Daly [EMAIL PROTECTED] wrote: Can there be a small lag between servers? If a second or two is acceptable, this sounds like a perfect environment for replication: http://dev.mysql.com/doc/mysql/en/Replication.html Basically, when the master writes something to the database, it also logs the transaction to a log file. The slave simply reads that log file and executes the same transaction locally. The additional load is very very small, your tables will all be consistent, and you can index the reporting database six ways from Sunday without touching the master. Eamon Daly - Original Message - From: Michael Haggerty [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 09, 2004 6:06 PM Subject: Best Practices I am working on a data warehousing solution involving mysql and have a question about best practices. We are standardized on mysql 4.1, and this is for a rather picky client. We have a relational transaction database that stores the results of customer calls and a dimensional reporting database used as a data mart by several applications. Each night, we run a process that aggregates the number of calls, the subjects of each call, and various other data to populate the reporting database. We would like to move to a real time solution, and are struggling with the best way to implment it. What we are considering is a solution where we mirror the transactional database and repopulate key tables in the reporting database every minute or few minutes. I am loathe to do this, mainly because it would add to our server load and could possibly lead to 'dirty reads' (i.e. where one table in the reporting database is populated with fresh data but others are not). At the same time, the client is demanding we implement something. Does anyone have any war stories or suggestions for how to accomplish this? Thank You, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: /tmp/mysql.sock disappeared on mac 10.3
Hello. Check if another copy of mysqld process is running (may be it runs with lost mysql.sock file?). Check also permissions for .err file. And be sure you are starting MySQL as root, because it will suid to the user you have specified. See: http://dev.mysql.com/doc/mysql/en/Starting_server.html Kenji LEFEVRE [EMAIL PROTECTED] wrote: Hello, OS : macos X 10.3 mysql version 4.0.21, for apple-darwin6.8 (powerpc) it's seems that after my last crash, the socket of mysql '/tmp/mysql.sock' has been deleted (and i have read afterwards that it should have been protected with a sticky bit) i thought that restarting mysql daemon would fix this problem but i encounter the following problem : when starting Raspoutine3:/usr/local/mysql admin$ bin/mysqld_safe --user=mysql --log i get touch: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied chown: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied Starting mysqld daemon with databases from /usr/local/mysql/data bin/mysqld_safe: line 302: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied rm: /usr/local/mysql/data/Raspoutine3.local.pid: Permission denied bin/mysqld_safe: line 1: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied tee: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied 041110 14:03:44 mysqld ended tee: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied though user 'mysql' is the owner of directory '/usr/local/mysql/data/' indeed Raspoutine3:/usr/local/mysql admin$ ls -ld data/ drwxr-x--- 13 mysql wheel 442 10 Nov 00:34 data/ How can i fix this two problems : recovering socket and starting daemon thanks in advance, Kenji. -- 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: Sequencial Replication
Hi. As Shawn Green said: I read through every TODO listed in the manual and didn't see it there. I also queried the Bugs list looking for any other issue or feature requests that were similar but didn't find any others. So, I assume that the problem is still in the analysis phase (or is still waiting to be analyzed) and once that is finished, the development team will make a choice of whether or not to implement the suggestion. When that decision occurs, there could be a note added to the Bug (feature request) explaining the decision (especially if the decision was no). Also he pointed me to http://bugs.mysql.com/bug.php?id=6256. Thanks to Shawn for good advices. Arvind Gangal [EMAIL PROTECTED] wrote: Hello, I understand that the slave executes the sqls from binlog in sequence, but are there any plans to make it multi-threaded so that it executes multiple sqls from binlog. Thanks Arvind. -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: 08 November 2004 13:02 To: [EMAIL PROTECTED] Subject: Re: Sequencial Replication Hello. MySQL slave receives all updates, but executes them in queue. More about implementation of replication you can read at: http://dev.mysql.com/doc/mysql/en/Replication_Implementation_Details.html Arvind Gangal [EMAIL PROTECTED] wrote: Hi Gurus, As I understand the slave replicates with the master reading one query at a time from the binlog files. And at times the slave comes across a long update query and unless this has completed it will not pick up the next sql even though the other sql is on other table or may be another database. Is there a mechanism of running multiple sql slave threads in order to keep slave in sync to the master to the max. I reckon this would mean remembering multiple positions on the binlog. TIA Arvind Gangal http://www.lastminute.com ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** __ __ This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk __ __ -- 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 -- 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: finding backslash
Hello. To search for `\', specify it as `' (the backslashes are stripped once by the parser and another time when the pattern match is done, leaving a single backslash to be matched). Steve Buehler [EMAIL PROTECTED] wrote: I am trying to find everything in a column that has a backslash in it and the select statement that I am trying to use doesn't find any of them. My statement is: SELECT * FROM `team` WHERE `name` LIKE '%\%' I have also tried: SELECT * FROM `team` WHERE `name` LIKE '%\\%' Any idea how to search for a backslash in a column of a table? Thanks Steve -- 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: GIS - NULL columns
Hello.
Remove coma from 'POINT(1,1)', instead use 'POINT(1 1)';
See:
http://dev.mysql.com/doc/mysql/en/Populating_spatial_columns.html
I installed mySQL server from Wizard and then i create table:
create table geom ( g POINT) ENGINE = MYISAM;
but i can't add any object to the table. I wrote:
insert into geom values(PointFromText('POINT(1,1)'));
and then i saw in the table NULL values:
SELECT AsText(g) FROM geom;
| g|
| NULL |
| NULL |
What can i do?? Do i set some variables??
My ini files:
#This File was made using the WinMySQLAdmin 1.4 Tool
#2004-11-08 23:39:15
#Uncomment or Add only the kRafal K. [EMAIL PROTECTED] wrote:
--
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]
Help using SSL from VB client, using MyODBC
I'm kind of new to the SSL scene. I've read all I can find on MySQL.org about setting up and using SSL. I'm on MySLQ 4.20 and have built mysql after configuring with --use-vio and --use-openssl. HAVE_OPENSSL = YES. I can handle setting up the user talbe and GRANTS to require SSL for users and connections. What I don't know how to do it make my client use SSL with MySQL. Can you help me? or give me some direction? My application runs on M$ Windows. It's written in M$ Visual Basic 6 from Visual Studio 6. I'm using MDAC 2.7 and M$ ADO. I'm using MyODBC 2.50 but can easily and happily upgrade to 3.51 (is 3.51 needed?) I have no idea what to do (set properties?) to cause my VB client to connect to the MySQL DB server using SSL? Any help would be huge! Thanx. -- William Blair Wagner : Education is not always knowing the answer, [EMAIL PROTECTED] : ...but rather knowing where to look for it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: /tmp/mysql.sock disappeared on mac 10.3
Thanks you for your answer. I have just tried what you said. It doesn't work. I type : Raspoutine3:/usr/local/mysql/bin admin$ sudo ./mysqld_safe --user=mysql --log Starting mysqld daemon with databases from /usr/local/mysql/data 041110 20:33:42 mysqld ended Here are the line in the file 'Raspoutine3:/usr/local/mysql/bin/data/Raspoutine3.local.err' 041110 20:33:41 mysqld started 041110 20:33:41 Warning: Setting lower_case_table_names=2 because file system for /usr/ local/mysql/data/ is case ins ensitive 041110 20:33:42 Can't start server : Bind on unix socket: Permission denied 041110 20:33:42 Do you already have another mysqld server running on socket: /tmp/ mysql.sock ? 041110 20:33:42 Aborting 041110 20:33:42 /usr/local/mysql/bin/mysqld: Shutdown Complete 041110 20:33:42 mysqld ended Though i can't find any mysqld running on my computer. Can someone helps ? Thanks, kenji. Message du 10/11/04 14:06 De : Michael Stassen [EMAIL PROTECTED] A : [EMAIL PROTECTED] Copie agrave; : [EMAIL PROTECTED] Objet : Re: /tmp/mysql.sock disappeared on mac 10.3 The socket file is created by mysqld when it starts, and goes away when mysqld shuts down. That is, you have no socket file because mysqld is not running, not the other way around. You appear to be trying to start mysqld as OS user admin. Normally, only root has the power make the switch to user mysql called for by --user=mysql. Thus, mysqld starts running as user admin, who has no permission to touch data files owned by mysql. Try `sudo bin/mysqld_safe --user=mysql --log`. Better yet, do sudo -v sudo bin/mysqld_safe --user=mysql --log Michael Kenji LEFEVRE wrote: Hello, OS : macos X 10.3 mysql version 4.0.21, for apple-darwin6.8 (powerpc) it's seems that after my last crash, the socket of mysql '/tmp/mysql.sock' has been deleted (and i have read afterwards that it should have been protected with a sticky bit) i thought that restarting mysql daemon would fix this problem but i encounter the following problem : when starting Raspoutine3:/usr/local/mysql admin$ bin/mysqld_safe --user=mysql --log i get touch: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied chown: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied Starting mysqld daemon with databases from /usr/local/mysql/data bin/mysqld_safe: line 302: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied rm: /usr/local/mysql/data/Raspoutine3.local.pid: Permission denied bin/mysqld_safe: line 1: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied tee: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied 041110 14:03:44 mysqld ended tee: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied though user 'mysql' is the owner of directory '/usr/local/mysql/data/' indeed Raspoutine3:/usr/local/mysql admin$ ls -ld data/ drwxr-x--- 13 mysql wheel 442 10 Nov 00:34 data/ How can i fix this two problems : recovering socket and starting daemon thanks in advance, Kenji. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tricky self join query help?
I have this table of events. Each event has an owner id and the time that it happened. What I want to do is delete all events more than three months old but only if the owner does not own any newer events. The coolest would just be a single DELETE query. Can this be done? Mysql 4.0.18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C api incompatability from 3.x to 4.1
I have a family of applications which use the C api to access mysql. I found by doing a test upgrade to 4.1 that all of these applications crash, apparently because the structures passed between my applications and libmysql.dll are incompatible. Recompiling the applications fixes the problem, but I'm disturbed that these broken applications didn't fail gracefully, when stale applications presented unusable structures to the new libmysql. 1) Is there no automatic check for compatibility that I should be encountering, and somehow am not? 2) In the absense of such a check, what should I build into my old applications so they will either continue to work or at least fail gracefully when the server is permanantly upgraded to 4.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
deleting from multiple tables syntax error
I had a working query that suddenly doesn't work anymore. It follows the syntax found in the documentation at http://dev.mysql.com/doc/mysql/en/DELETE.html specifically, DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id; my query is: DELETE FROM rsrc_linx_specialty USING rsrc_linx_specialty rls, rsrc_linx rl, rsrc_linx_type rlt WHERE rls.specialtyID = '$specialtyIDd' AND rls.sectorID = '$sectorIDd' AND rls.linxID = rl.linxID AND rl.linxID = rlt.linxID AND rlt.typeID = '2'; I'd been using this query successfully for a while. Then we upgraded to 4.0.17 and now it doesn't work neither from a PHP page, nor a terminal session. I get an error found to start at the word 'USING'. I've checked/optimized the table. I've searched on the web for faqs and everything, but haven't found any pertinent information. So if it isn't a corrupt table, I'm wondering if the USING parameter happened to be deprecated, or something.. Any suggestions would be appreciated 'cause right now I'm stumped. Cameron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: /tmp/mysql.sock disappeared on mac 10.3
Two possibilities: 1) mysqld could not create /tmp/mysql.sock because it is already there, which would mean mysqld is already running. It appears you've ruled this out. 2) User mysql does not have permission to write to /tmp. In Mac OS X, /tmp is a symlink to /private/tmp, so this really means mysql does not have permission to write to /private/tmp. The fix: sudo chmod 1777 /private/tmp Michael Kenji LEFEVRE wrote: Thanks you for your answer. I have just tried what you said. It doesn't work. I type : Raspoutine3:/usr/local/mysql/bin admin$ sudo ./mysqld_safe --user=mysql --log Starting mysqld daemon with databases from /usr/local/mysql/data 041110 20:33:42 mysqld ended Here are the line in the file 'Raspoutine3:/usr/local/mysql/bin/data/Raspoutine3.local.err' 041110 20:33:41 mysqld started 041110 20:33:41 Warning: Setting lower_case_table_names=2 because file system for /usr/ local/mysql/data/ is case ins ensitive 041110 20:33:42 Can't start server : Bind on unix socket: Permission denied 041110 20:33:42 Do you already have another mysqld server running on socket: /tmp/ mysql.sock ? 041110 20:33:42 Aborting 041110 20:33:42 /usr/local/mysql/bin/mysqld: Shutdown Complete 041110 20:33:42 mysqld ended Though i can't find any mysqld running on my computer. Can someone helps ? Thanks, kenji. Message du 10/11/04 14:06 De : Michael Stassen [EMAIL PROTECTED] A : [EMAIL PROTECTED] Copie agrave; : [EMAIL PROTECTED] Objet : Re: /tmp/mysql.sock disappeared on mac 10.3 The socket file is created by mysqld when it starts, and goes away when mysqld shuts down. That is, you have no socket file because mysqld is not running, not the other way around. You appear to be trying to start mysqld as OS user admin. Normally, only root has the power make the switch to user mysql called for by --user=mysql. Thus, mysqld starts running as user admin, who has no permission to touch data files owned by mysql. Try `sudo bin/mysqld_safe --user=mysql --log`. Better yet, do sudo -v sudo bin/mysqld_safe --user=mysql --log Michael Kenji LEFEVRE wrote: Hello, OS : macos X 10.3 mysql version 4.0.21, for apple-darwin6.8 (powerpc) it's seems that after my last crash, the socket of mysql '/tmp/mysql.sock' has been deleted (and i have read afterwards that it should have been protected with a sticky bit) i thought that restarting mysql daemon would fix this problem but i encounter the following problem : when starting Raspoutine3:/usr/local/mysql admin$ bin/mysqld_safe --user=mysql --log i get touch: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied chown: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied Starting mysqld daemon with databases from /usr/local/mysql/data bin/mysqld_safe: line 302: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied rm: /usr/local/mysql/data/Raspoutine3.local.pid: Permission denied bin/mysqld_safe: line 1: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied tee: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied 041110 14:03:44 mysqld ended tee: /usr/local/mysql/data/Raspoutine3.local.err: Permission denied though user 'mysql' is the owner of directory '/usr/local/mysql/data/' indeed Raspoutine3:/usr/local/mysql admin$ ls -ld data/ drwxr-x--- 13 mysql wheel 442 10 Nov 00:34 data/ How can i fix this two problems : recovering socket and starting daemon thanks in advance, Kenji. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deleting from multiple tables syntax error
Did you upgrade to 4.0.17 or 4.1.7? In 4.1, you have to use the alias between FROM and USING: DELETE FROM rls USING rsrc_linx_specialty rls,... This is documented at the bottom of the manual page you referenced. Michael [EMAIL PROTECTED] wrote: I had a working query that suddenly doesn't work anymore. It follows the syntax found in the documentation at http://dev.mysql.com/doc/mysql/en/DELETE.html specifically, DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id; my query is: DELETE FROM rsrc_linx_specialty USING rsrc_linx_specialty rls, rsrc_linx rl, rsrc_linx_type rlt WHERE rls.specialtyID = '$specialtyIDd' AND rls.sectorID = '$sectorIDd' AND rls.linxID = rl.linxID AND rl.linxID = rlt.linxID AND rlt.typeID = '2'; I'd been using this query successfully for a while. Then we upgraded to 4.0.17 and now it doesn't work neither from a PHP page, nor a terminal session. I get an error found to start at the word 'USING'. I've checked/optimized the table. I've searched on the web for faqs and everything, but haven't found any pertinent information. So if it isn't a corrupt table, I'm wondering if the USING parameter happened to be deprecated, or something.. Any suggestions would be appreciated 'cause right now I'm stumped. Cameron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of my depth.
Hi, Is there anything your mysql errog log? Did MySQL crashed or stopped unexpectedly while the script was running? I guess it is probably something with your memory usage configuration - please send your my.cnf file and on what machine you are running your MySQL server. -- Dobromir Velev [EMAIL PROTECTED] http://www.websitepulse.com/ On Tuesday 02 November 2004 12:45, John Smith wrote: Hi All, I have built a search application in php/libcurl and I store its results in MySQL. The problem is that I am not used to dealing with the sizes of tables my search application produces, roughly around 400,000 rows in a table the last time I got ran it correctly. Right to my problem. I fork 20 versions of my spider at a time, these forks all have a connection to the database and do selects, inserts and updates on the same table at once. When its going at full steam I can be inserts 1000s of rows a minute. I am using MyISAM as I need its fulltext search cabablities. I remove the fulltext index before I start any changes to the table. I am finding that my php script is not being able to select from the database at random points, I have tracked this down to a 127 erros, the table it corrupt. Before I start my spiders (before it forks) I run myisamck -r on my .MYI file but it corrupts during the scripts execution time and this means it is no longer able to select from the DB (Curcial to know if its needing updated or inserted as a new record) Any hints, any more information needed from me etc would be great. My table struture is: CREATE TABLE thetable ( id int(11) NOT NULL auto_increment, sid int(11) NOT NULL default '1', pid varchar(14) NOT NULL default '0', tid varchar(255) NOT NULL default '', cid varchar(255) NOT NULL default '', location text NOT NULL, number int(14) NOT NULL default '0', image text NOT NULL, description text NOT NULL, link text NOT NULL, uo tinyint(1) NOT NULL default '0', sd tinyint(1) NOT NULL default '0', added int(14) NOT NULL default '0', new tinyint(4) NOT NULL default '1', old tinyint(4) NOT NULL default '0', PRIMARY KEY (id), KEY sid (sid), KEY old (old), KEY new (new), KEY sd (sd), KEY uo (uo), KEY pid (pid), KEY tid (tid), KEY cid (cid) ) Ta, John ___ Have your own email and web address for life. http://www.homemaster.net - Homemaster. Come Together. Online. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api incompatability from 3.x to 4.1
I provided the list below for our programmers, who also are dealing with a switch from 3.23 to 4.1. Perhaps it would be of some help for you. Cheers, --V - We've already hit a couple of API-related problems with the new version of MySQL. To try to make things a little easier, and because I love our programmers, I've sifted through the change history of MySQL 4.1 to pick out all the changes specifically related to the C API. Most won't apply to us. For a complete list of all MySQL changes, hit this link: http://dev.mysql.com/doc/mysql/en/News.html And now for the list: * Added new mysql_get_server_version() C API client function. * Added mysql_set_server_option() C API client function to allow multiple statement handling in the server to be enabled or disabled. * The mysql_next_result() C API function now returns -1 if there are no more result sets. * Warning: Incompatible change! Renamed the C API mysql_prepare_result() function to mysql_get_metadata() as the old name was confusing. * Added mysql_sqlstate() and mysql_stmt_sqlstate() C API client functions that return the SQLSTATE error code for the last error. * Warning: Incompatible change! Renamed prepared statements C API functions: Old Name New Name mysql_bind_param() mysql_stmt_bind_param() mysql_bind_result() mysql_stmt_bind_result() mysql_prepare() mysql_stmt_prepare() mysql_execute() mysql_stmt_execute() mysql_fetch() mysql_stmt_fetch() mysql_fetch_column() mysql_stmt_fetch_column() mysql_param_count() mysql_stmt_param_count() mysql_param_result() mysql_stmt_param_metadata() mysql_get_metadata() mysql_stmt_result_metadata() mysql_send_long_data() mysql_stmt_send_long_data() Now all functions that operate with a MYSQL_STMT structure begin with the prefix mysql_stmt_. * Warning: Incompatible change! The signature of the mysql_stmt_prepare() function was changed to int mysql_stmt_prepare(MYSQL_STMT *stmt, const char *query, unsigned long length). To create a MYSQL_STMT handle, you should use the mysql_stmt_init() function, not mysql_stmt_prepare(). * C API enhancement: SERVER_QUERY_NO_INDEX_USED and SERVER_QUERY_NO_GOOD_INDEX_USED flags are now set in the server_status field of the MYSQL structure. It is these flags that make the query to be logged as slow if mysqld was started with --log-slow-queries --log-queries-not-using-indexes. * Added support for unsigned integer types to prepared statement API (Bug #3035). * Warning: Incompatible change! C API change: mysql_shutdown() now requires a second argument. This is a source-level incompatibility that affects how you compile client programs; it does not affect the ability of compiled clients to communicate with older servers. See section 21.2.3.51 mysql_shutdown(). * Fixed a bug in client-side conversion of string column to MYSQL_TIME application buffer (prepared statements API). (Bug #4030) * Fixed a buffer overflow in prepared statements API (libmysqlclient) when a statement containing thousands of placeholders was executed. (Bug #5194) * The mysql_change_user() C API function now frees all prepared statements associated with the connection. (Bug #5315) * Fixed bug in libmysqlclient that fetched column defaults. * Fixed mysql_stmt_send_long_data() behavior on second execution of prepared statement and in case when long data had zero length. (Bug #1664) * You can now call mysql_stmt_attr_set(..., STMT_ATTR_UPDATE_MAX_LENGTH) to tell the client library to update MYSQL_FIELD-max_length when doing mysql_stmt_store_result(). (Bug #1647). * Fixed memory leak in the client library when statement handle was freed on closed connection (call to mysql_stmt_close after mysql_close). (Bug #3073) * Fixed mysql_stmt_affected_rows() call to always return number of rows affected by given statement. (Bug #2247) * Fix for a bug that caused client/server communication to be broken when mysql_set_server_option() or mysql_get_server_option() were invoked. (Bug #2207) * The MySQL server did not report any error if a statement (submitted through mysql_real_query() or mysql_stmt_prepare()) was terminated by garbage characters. This can happen if you pass a wrong length parameter to these functions. The result was that the garbage characters were written into the binary log. (Bug #2703) * Fixed bug in client library that caused mysql_stmt_fetch and mysql_stmt_store_result() to hang if they were called without prior call of mysql_stmt_execute(). Now they give an error instead. (Bug #2248) * Fixed a bug in mysql_stmt_close(), which hung up when attempting to close statement after failed mysql_stmt_fetch(). (Bug #4079) * Fixed potential memory overrun in mysql_real_connect() (which required a compromised DNS server and certain operating systems). (Bug #4017) * Fixed a bug that caused libmysql to crash when attempting to fetch a value of MEDIUMINT column. (Bug #5126) * Fixed that
Re: Upgrading MySql on OSX 10.3.6
The directory mysql is a symbolic link to one of the other dirs. If mysql points to old one rename it and make a new one: mv mysql mysqlold ls -s mysql-max-4.1.7-apple-darwin7.5.0-powerpc mysql Do not remove mysql-max-4.0.20-apple-darwin7.3.0-powerpc/data it contains your old databases!!! Santino At 11:17 -0600 10-11-2004, [EMAIL PROTECTED] wrote: I was running ver 4.0.15 and just upgraded to the latest version of 4.1.7. In between I had done an upgrade to ver. 4.0.20. I can get the server to start and stop via the Preferences panel but before and after any upgrade it indicates that 4.0.15 is running. In Navicat which I use as the gui front end it also indicates that 4.0.15 is running. In /usr/local I have 3 MySql related directories: mysql mysql-max-4.1.7-apple-darwin7.5.0-powerpc mysql-max-4.0.20-apple-darwin7.3.0-powerpc My question is how do I get Navicat and the preferences panel to recognize 4.1.7 and do I need to remove 4.0.20 and 4.0.15? Thanks. Kevin -- 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: C api incompatability from 3.x to 4.1
In the last episode (Nov 10), Dave Dyer said: I have a family of applications which use the C api to access mysql. I found by doing a test upgrade to 4.1 that all of these applications crash, apparently because the structures passed between my applications and libmysql.dll are incompatible. Recompiling the applications fixes the problem, but I'm disturbed that these broken applications didn't fail gracefully, when stale applications presented unusable structures to the new libmysql. 1) Is there no automatic check for compatibility that I should be encountering, and somehow am not? On Unix, shared libraries bump their version number when the ABI changes to prevent this from happening. Maybe libmysql.dll should have the same protection? One workaround is to link that library statically. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Practices
Starting with 4.0, when you do a LOAD DATA INFILE on the master, it actually writes the full insert in the binary log, which the slave then reproduces. And if any gurus are listening, I /believe/ that setting max_allowed_packet on the master and slave to the same value prevents any Packet too large problems, but I couldn't find confirmation in the docs. If I set max_allowed_packet to, say, 16M on the master, does it write the data from a LOAD DATA INFILE command in 16M chunks to the binary log? Eamon Daly - Original Message - From: Michael Haggerty [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 10, 2004 11:27 AM Subject: Re: Best Practices Yes, there can be a small lag in data updates, in fact I believe the lag time will be less than a second considering our architecture. We have been considering replication as a solution but have been hesitant to do so because I have heard there are problems with data inserted through a LOAD DATA INFILE command. We regularly import csv data from spreadsheets from people working offline and have some pretty sophisticated processes built around this requirement. Has anyone run into this problem, and are there any solutions? Thanks, Michael Haggerty --- Eamon Daly [EMAIL PROTECTED] wrote: Can there be a small lag between servers? If a second or two is acceptable, this sounds like a perfect environment for replication: http://dev.mysql.com/doc/mysql/en/Replication.html Basically, when the master writes something to the database, it also logs the transaction to a log file. The slave simply reads that log file and executes the same transaction locally. The additional load is very very small, your tables will all be consistent, and you can index the reporting database six ways from Sunday without touching the master. Eamon Daly - Original Message - From: Michael Haggerty [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 09, 2004 6:06 PM Subject: Best Practices I am working on a data warehousing solution involving mysql and have a question about best practices. We are standardized on mysql 4.1, and this is for a rather picky client. We have a relational transaction database that stores the results of customer calls and a dimensional reporting database used as a data mart by several applications. Each night, we run a process that aggregates the number of calls, the subjects of each call, and various other data to populate the reporting database. We would like to move to a real time solution, and are struggling with the best way to implment it. What we are considering is a solution where we mirror the transactional database and repopulate key tables in the reporting database every minute or few minutes. I am loathe to do this, mainly because it would add to our server load and could possibly lead to 'dirty reads' (i.e. where one table in the reporting database is populated with fresh data but others are not). At the same time, the client is demanding we implement something. Does anyone have any war stories or suggestions for how to accomplish this? Thank You, M -- 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, intersct and except operation?
Lana, You have been asking this question for quite a while now. I think that you do not have a satisfactory answer yet because I do not believe there is an EXCEPT operator in the MySQL vocabulary. If you could post a link to the page from the MySQL manual that shows this operator, we can help you understand how to use it. Otherwise you need to take a little extra time to explain what you want out of your data as many of us may not be familiar with how the EXCEPT operator works in other database systems. (I know I do not recognize the operator.) INTERSECT and EXCEPT are set operators, similar to UNION, although UNION is the only one currently supported in MySQL (all are ANSI SQL92, but union is most widely supported). Other databases, e.g. Postgres and Oracle among others, do support INTERSECT and EXCEPT. To the best of my knowledge, Michael Stassen's suggestion earlier to use a subquery (meaning you need =4.1) is the only way out in MySQL, although the left join solution is intriguing, and I'd love to hear more about it. So, SELECT T.data_id from table T WHERE T.keyword = 'chemistry' EXCEPT SELECT T2.data_id from table T2 WHERE T2.keyword = 'computers' Would become SELECT T.data_id FROM table T WHERE T.keyword = 'chemistry' AND NOT EXISTS ( SELECT T2.data_id FROM table T2 WHERE T2.keyword = 'computers' AND T2.data_id = T1.data_id ) For more on this workaround, see: http://www.winnetmag.com/Windows/Article/ArticleID/40321/40321.html http://www-db.stanford.edu/~ullman/fcdb/oracle/my-nonstandard.html#intersect http://www.oracle.com/technology/products/rdb/pdf/new_except.pdf And, if you have it, Joe Celko's SQL for Smarties, pp. 414-419. Andy Crain NewsLogic, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql c client library for AIX 5.2
Hi there, I need to use the mysql.h library in an AIX version 5.2 machine. First, I tried downloading the precompiled binaries for AIX, but when I tried to compile a simple .c file, I got the following errror: cc mysql_test.c -o mysql_test -I/usr/local/include/mysql -lmysqlclient ld: 0711-317 ERROR: Undefined symbol: .mysql_connect I imagined that the problem was that I was using a compiler different than the one used for compiling the library(C for AIX Compiler, Version 6). So, I've downloaded the mysql source file (mysql-4.1.7.tar.gz). I've uncompressed the file and followed the mysql manual installation procedure: shell gunzip mysql-4.1.7.tar.gz | tar -xvf - shell cd mysql-4.1.7 shell ./configure --prefix=/usr/local/mysql shell make but, I got the following error: cc -qlanglvl=ansi -DDEFAULT_CHARSET_HOME=\/usr/local/mysql\ -DDATADIR=\/usr/local/mysql/var\ -DSHAREDIR=\/usr/local/mysql/share/mysql\ -DUNDEF_THREADS_HACK -DDONT_USE_RAID -I. -I. -I.. -I../include -I../zlib -O -DDBUG_OFF -c my_gethostbyname.c -DPIC -o .libs/my_gethostbyname.o my_gethostbyname.c, line 73.5: 1506-068 (S) Operation between types struct hostent* and int is not allowed. make: 1254-004 The error code from the last command is 1. Finally, I followed the manual's aix notes: export CC=xlc_r -ma -O3 -qstrict -qoptimize=3 -qmaxmem=8192 export CXX=xlC_r -ma -O3 -qstrict -qoptimize=3 -qmaxmem=8192 export CFLAGS=-I /usr/local/include export LDFLAGS=-L /usr/local/lib export CPPFLAGS=$CFLAGS export CXXFLAGS=$CFLAGS ./configure --prefix=/usr/local \ --localstatedir=/var/mysql \ --sbindir='/usr/local/bin' \ --libexecdir='/usr/local/bin' \ --enable-thread-safe-client \ --enable-large-files being carefull enough to change the paths with my machine correct paths(also tried using cc compiler). But, when I run the configure command I get the following error message: configure: error: C++ preprocessor /lib/cpp fails sanity check See `config.log' for more details. Can anybody provide me with a mysql client library compiled with cc compiler for AIX 5.2? Or, at least, guide me on what procedure should I run in order to succesfully compile the mysql source files? thanks in advance. Pablo Salinas _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deleting from multiple tables syntax error
Turns out to be 4.1.7 This suggestion fixed my query, thanks. I'd tried that approach yesterday, but it was on an older version of MySQL (on our non-production server; I wasn't paying attention to the versions of MySQL betw. our test and production servers) - so my previous attempts with this syntax didn't work, because the environment didn't support it; chalk it up to 'human error' this time 'round. I appreciate the quick reply. Cameron Did you upgrade to 4.0.17 or 4.1.7? In 4.1, you have to use the alias between FROM and USING: DELETE FROM rls USING rsrc_linx_specialty rls,... This is documented at the bottom of the manual page you referenced. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting count() to include 0 counts in select
Hi, The following select returns how many times an id from table _objectives is used in table _iso which it does fine but I need it to return _objectives.id that are not used in table _iso. SELECT _objectives.id, _objectives.name, COUNT(go._iso._objective_id) FROM go._objectives, go._subjectHeadings, go._subjects, go._iso WHERE go._subjectHeadings.id = 276 AND go._subjects.id = 44 AND go._subjectHeadings.id = go._objectives.subjectHeadings_id AND go._subjects.id = go._objectives.subjects_id AND go._iso._objective_id = _objectives.id GROUP BY go._iso._objective_id ORDER BY go._objectives.displayOrder What I originally had was the following select and then on each record returned I did a 2nd select that does a count but this is very slow. SELECT _objectives.id, _objectives.subjects_id, _objectives.subjectHeadings_id, _objectives.name, _objectives.active, _objectives.displayOrder FROM go._objectives, go._subjectHeadings, go._subjects WHERE go._subjectHeadings.id = 276 AND go._subjects.id = 44 AND go._subjectHeadings.id = go._objectives.subjectHeadings_id AND go._subjects.id = go._objectives.subjects_id ORDER BY go._objectives.displayOrder This returns 58 records. I then on each record do the following to determine if this id is in use. SELECT COUNT(*) FROM go._iso, go._ltaForm WHERE _objective_id = $objectiveID AND _ltaForm.id = _iso.ltaForm_id ($objectiveID = $row[0] from 1st query) -- Mark Worsdall https://www.paypal.com/refer/pal=LS79YHQ9VUGLJ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: improving query responce time
At 01:36 AM 11/9/2004, you wrote: 25 lakh records.. What is lakh? Thousand? Million? What does your query look like? Did you put Explain in front of your query to determine which indexes are being used? How many rows is the query returning? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication: bin logs not transferred, load data gives error
Just so it is known, repl_user, the replication account, has full access on the master database to do everything from any host '%'. Here is my log output on server2: 041110 8:59:01 Slave SQL thread initialized, starting replication in log 'server1-bin.054' at position 3646268, relay log './server2-relay-bin.002' position: 27198 041110 8:59:01 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3307', replication started in log 'server1-bin.054' at position 3646268 041110 9:01:46 Slave I/O thread killed while reading event 041110 9:01:46 Slave I/O thread exiting, read up to log 'server1-bin.054', position 3647181 041110 9:01:46 Error reading relay log event: slave SQL thread was killed The log output on server1 for the slave failure is the same And on both servers, I believe this is the error in the logs when I issue 'LOAD DATA FROM MASTER' create_table_from_dump: failed in handler::net_read_dump() Any help from any one? -RG Russell E Glaue wrote: I am setting up a master-master replication. I have the masters set up correctly (I guess), and they update their position when changes occur. 'show slave status' and 'show master status' both show the correct positions between each server. However. Although the slave position increments to match the position on the master on both servers, the data is NOT getting updated. The position continues to increment up and up, but nothing is changed. When I had successful replication in the past, I remeber the bin-logs being transfered from the master to the slave for the updates. This is currently NOT happenning on both servers. When I execute the 'LOAD DATA FROM MASTER' I ALWAYS get an error. ERROR 1189: Net error reading from master And I get the same error on both servers. Now I know the replication configuration is correct, because when changes occur on one server, the 'Read_Master_Log_Pos' changes on the second server to match the first server. However, the data is NOT getting updated. There is no firewall between the two machines. And we are running MySQL 4.0.20 Is there a reason why the bin-logs are not being transferred? I suspect this is the reason why the data is not getting updated. And I suspect the error from 'LOAD DATA FROM MASTER' demonstrates the problem I am having. What is wrong? Any help? -RG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fast method needed to determine if a table is corrupt
Hi All, InnoDB tables as the solution is incorrect. I've been running some fairly large InnoDB databases, and crashes using InnoDB are probably ALOT worse than with MyIsam tables. InnoDB tables tend to corrupt very easily on such things as power outages, with corrupted page data error which means that mysql doesn't start at all, and the only option is to start InnoDB in recovery mode, dump and hope for the best. How big is your database? If it can fit in RAM, I'd suggest using a ramdisk to store your database with snapshots taken every X minutes and stored to disk (or using NVRAM to store the database). Other than that, there's no quick way to check for corruption that I know of. Cheers, Mark Steele Implementation Director CDT Inc. -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: November 8, 2004 12:43 PM To: Tim Murtaugh Cc: '[EMAIL PROTECTED]' Subject: Re: Fast method needed to determine if a table is corrupt In the last episode (Nov 08), Tim Murtaugh said: I'm using MySQL server version 4.0.15a in an embedded envirionment (as a standalone server, I'm not using the embedded server library). I have 128 MB of memory and disk space is tight. I'm using MyISAM tables. If my system loses power, some tables are left in a corrupt state. As stated in the MySQL documentation, I think the data tables are OK, its just that the tables were not closed properly and are considered corrupt by MySQL. I need a FAST way to determine if a table is corrupt. I've tried myisamcheck --fast and --check-only-changed options, and increased the buffer sizes (-O key_buffer_size and -O sort_buffer_size), as mentioned in the documentation. The fastest time I can achieve is 6:55. I've also tried CHECK TABLE tablename FAST QUICK on a table I know is marked as corrupt, and the fastest time I can achieve is 6:58. I need to detemine if a table is corrupt within a few SECONDS, not minutes. How can I do this? Make your tables smaller? :) You have to check each record to see that it's okay. If your tables are big, you have to spend time reading them. The documentation says there is a flag in myisam tables that indicates when a table is corrupt. Is there a way I can quickly check this flag? If mysql tries to read a record or index and can't, it sets this flag to keep you from accessing the table until you repair it. You may be better off using InnoDB tables and taking the space/performance hit. InnoDB uses a logfile to allow it to roll back partially-commited transactions after a crash, so you never have to check or repair your tables. -- Dan Nelson [EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
Re: Getting count() to include 0 counts in select
Change one of your INNER JOINS to a LEFT JOIN. (The comma separated list of table names is actually a sneaky way to declare INNER JOINS). That way you will see all of the _objectives records whether or not they appear in _iso or any of the other tables. SELECT_objectives.id, _objectives.name, COUNT(go._iso._objective_id) FROM go._objectives INNER JOIN go._subjectHeadings ON go._subjectHeadings.id = go._objectives.subjectHeadings_id INNER JOIN go._subjects ON go._subjects.id = go._objectives.subjects_id LEFT JOIN go._iso ON go._iso._objective_id = _objectives.id WHERE go._subjectHeadings.id = 276 AND go._subjects.id = 44 GROUP BY 1,2 ORDER BY go._objectives.displayOrder Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mark Worsdall [EMAIL PROTECTED] wrote on 11/10/2004 02:53:29 PM: Hi, The following select returns how many times an id from table _objectives is used in table _iso which it does fine but I need it to return _objectives.id that are not used in table _iso. SELECT _objectives.id, _objectives.name, COUNT(go._iso._objective_id) FROM go._objectives, go._subjectHeadings, go._subjects, go._iso WHERE go._subjectHeadings.id = 276 AND go._subjects.id = 44 AND go._subjectHeadings.id = go._objectives.subjectHeadings_id AND go._subjects.id = go._objectives.subjects_id AND go._iso._objective_id = _objectives.id GROUP BY go._iso._objective_id ORDER BY go._objectives.displayOrder What I originally had was the following select and then on each record returned I did a 2nd select that does a count but this is very slow. SELECT _objectives.id, _objectives.subjects_id, _objectives.subjectHeadings_id, _objectives.name, _objectives.active, _objectives.displayOrder FROM go._objectives, go._subjectHeadings, go._subjects WHERE go._subjectHeadings.id = 276 AND go._subjects.id = 44 AND go._subjectHeadings.id = go._objectives.subjectHeadings_id AND go._subjects.id = go._objectives.subjects_id ORDER BY go._objectives.displayOrder This returns 58 records. I then on each record do the following to determine if this id is in use. SELECT COUNT(*) FROM go._iso, go._ltaForm WHERE _objective_id = $objectiveID AND _ltaForm.id = _iso.ltaForm_id ($objectiveID = $row[0] from 1st query) -- Mark Worsdall https://www.paypal.com/refer/pal=LS79YHQ9VUGLJ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky self join query help?
If you post the table structure (SHOW CREATE TABLE tablename\G) we could help you write this statement. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Gerald Taylor [EMAIL PROTECTED] wrote on 11/10/2004 11:52:35 AM: I have this table of events. Each event has an owner id and the time that it happened. What I want to do is delete all events more than three months old but only if the owner does not own any newer events. The coolest would just be a single DELETE query. Can this be done? Mysql 4.0.18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting count() to include 0 counts in select
In message [EMAIL PROTECTED], [EMAIL PROTECTED] writes Change one of your INNER JOINS to a LEFT JOIN. (The comma separated list of table names is actually a sneaky way to declare INNER JOINS). That way you will see all of the _objectives records whether or not they appear in _iso or any of the other tables. SELECT_objectives.id, _objectives.name, COUNT(go._iso._objective_id) FROM go._objectives INNER JOIN go._subjectHeadings ON go._subjectHeadings.id = go._objectives.subjectHeadings_id INNER JOIN go._subjects ON go._subjects.id = go._objectives.subjects_id LEFT JOIN go._iso ON go._iso._objective_id = _objectives.id WHERE go._subjectHeadings.id = 276 AND go._subjects.id = 44 GROUP BY 1,2 ORDER BY go._objectives.displayOrder [snip] The group by 1,2 what is that all about the 1,2? is it join 1 and then join 2? -- Work:- postmasterAThinwick.demon.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky self join query help?
Try something like this: SELECT Events.ID, Events.ownerID, Owners.ownerID FROM Events LEFT JOIN Events AS Owners ON Events.ownerID=Owners.ownerID AND Events.eventData 3 months ago WHERE Owners.ownerID IS NULL I know you want to do a delete, but play with SELECT first to make sure it's doing what you want. What the above query is doing is a self left join on owner IDs in the past three months. Thus, any owner with an event in the past three months will have a matching owner ID from the Owners table. You then create a filter for all events without a matching owner ID, value of NULL in the Owners.ownerID column. So your result will look like this: Events.ID Events.ownerID Owners.ownerID 1 1 1 2 1 1 3 2 NULL 4 3 3 So your DELETE statement would look something like this: DELETE FROM Events USING Events LEFT JOIN Events AS Owners ON Events.ownerID=Owners.ownerID AND Events.eventData 3 months ago WHERE Owners.ownerID IS NULL No guarantee that this is totally correct, but some quick testing seems to indicate it works. On Nov 10, 2004, at 11:52 AM, Gerald Taylor wrote: I have this table of events. Each event has an owner id and the time that it happened. What I want to do is delete all events more than three months old but only if the owner does not own any newer events. The coolest would just be a single DELETE query. Can this be done? Mysql 4.0.18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting count() to include 0 counts in select
It's a shorthand way to say that I wanted to group on the first two columns of my select statement. Here's a quote from the manual: http://dev.mysql.com/doc/mysql/en/SELECT.html Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1: mysql SELECT college, region, seed FROM tournament - ORDER BY region, seed; mysql SELECT college, region AS r, seed AS s FROM tournament - ORDER BY r, s; mysql SELECT college, region, seed FROM tournament - ORDER BY 2, 3; Since he had 3 columns in his SELECT clause but only the third one had an aggregate function applied to it, I grouped on the other two. I could have written it the long way as: GROUP BY _objectives.id, _objectives.name Make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mark Worsdall [EMAIL PROTECTED] 11/10/2004 04:05 PM To [EMAIL PROTECTED] cc Subject Re: Getting count() to include 0 counts in select In message [EMAIL PROTECTED], [EMAIL PROTECTED] writes Change one of your INNER JOINS to a LEFT JOIN. (The comma separated list of table names is actually a sneaky way to declare INNER JOINS). That way you will see all of the _objectives records whether or not they appear in _iso or any of the other tables. SELECT_objectives.id, _objectives.name, COUNT(go._iso._objective_id) FROM go._objectives INNER JOIN go._subjectHeadings ON go._subjectHeadings.id = go._objectives.subjectHeadings_id INNER JOIN go._subjects ON go._subjects.id = go._objectives.subjects_id LEFT JOIN go._iso ON go._iso._objective_id = _objectives.id WHERE go._subjectHeadings.id = 276 AND go._subjects.id = 44 GROUP BY 1,2 ORDER BY go._objectives.displayOrder [snip] The group by 1,2 what is that all about the 1,2? is it join 1 and then join 2? -- Work:- postmasterAThinwick.demon.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Specific issues with MySQL 4.0.16 on Linux or Windows 2000/3 related to tmp tables?
Hi Everyone, I've been Googling unsuccessfully for specific issues relating to queries run on MySQL version 4.0.16 against tmp tables. I have witnessed several occurrences where queries running on various platforms hang in a Copying to tmp table state for hours or days at a time. When the same query is manually run from the MySQL command line client, the query returns, even on very large tables. Could someone please give me a hint as to possible tuning (or upgrade?) ideas to fix this situation? I can't find anything else on each problem system that would give me a hint as to why this problem randomly occurs. I've made attempts at adjusting the tmp_table_size limits to have the system try to do order by queries in memory, but this does not seem to help the situation. Any help would be appreciated! -Kevin show full processlist output (notice that the query has been running for 7.9 days!!!): | 33 | someuser | localhost:34329 | sometable | Query | 687465 | Copying to tmp table | select date_add( date_format(time, '%Y-%c-%d 00:00:00'), INTERVAL 0 HOUR) time, ... group by 1 order by 1 __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting count() to include 0 counts in select
In message [EMAIL PROTECTED], [EMAIL PROTECTED] writes It's a shorthand way to say that I wanted to group on the first two columns of my select statement. Here's a quote from the manual: http://dev.mysql.com/doc/mysql/en/SELECT.html Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1: mysql SELECT college, region, seed FROM tournament - ORDER BY region, seed; mysql SELECT college, region AS r, seed AS s FROM tournament - ORDER BY r, s; mysql SELECT college, region, seed FROM tournament - ORDER BY 2, 3; Since he had 3 columns in his SELECT clause but only the third one had an aggregate function applied to it, I grouped on the other two. I could have written it the long way as: GROUP BY _objectives.id, _objectives.name Make sense? [snip] Yes, cheers. I shall make sure I avoid doing this, for readability's sake:-) Looks like a crunch / packing program has been set loose on it, like when wrote stuff on BBC's etc and crunched the code so it would fit:-) But I get it. I just love mySQL, it has removed so many layers of complication from my coding life and left only 1 layer of not too much complicatedness:-) Site below is powered by mySQL. M. -- Mark Worsdall http://www.vote4president.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Syntax Problem
This may be a purely PHP problem, but the error
message says SQL syntax. Check the manual that
corresponds to your MySQL server version...
More important, I haven't been able to find a solution
on any PHP forums. :)
This is the complete error message:
Failed to run SELECT F.IDArea, C.IDArea, C.Name,
C.Pop, C.Nationality, C.NationalityPlural,
C.NationalityAdjective FROM cia_people C, famarea2 F
WHERE (C.Nationality is not null) AND (F.IDArea =
'eur') ORDER BY $_POST['order'], $_POST['direction'] -
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 '['order'],
$_POST['direction']' at line 11
But the line it references isn't really line 11. This
is it:
$_POST[\'order\'], $_POST[\'direction\']';
$res = mysql_query($sql) or die('Failed to run ' .
$sql . ' - ' . mysql_error());
Someone suggested the problem is the word order. So
I replaced every instance of order with reorder
and got the same results. Another individual suggested
I remove the backward slashes in the first line, but I
had to add those to get rid of a series of parse
errors.
Does anyone have a clue what the problem/solution is?
Or can you tell me exactly what I'm supposed to look
up in the manual?
Thanks.
head[DATABASE CONNECTION]/head
body
div class=formdiv
form action=remote.php method=GET
select name=order
option value=cia_people.Name'Country,
etc./option
option
value=cia_people.Pop'Population/option
option
value=cia_people.Nationality'Nationality/option
option
value=cia_people.NationalityPlural'Nationality:
Plural/option
option
value=cia_people.NationalityAdjective'Nationality:
Adjective/option
option
value=famarea2.IDParentRegGeographic
Region/option
/select
input type=radio name=direction
value=0+
input type=radio name=direction
value=1-
input type=submit name=submit
value=Submit
/form
/div
?php
$colors = array( '#eee', '', '#ff9', '', '#cff', '',
'#cfc', '' );
$n=0;
$size=count($colors);
$result = mysql_query('select count(*) FROM cia_people
C, famarea2 F
WHERE C.IDArea = F.IDArea AND F.IDParent = eur
AND C.Nationality is not null');
if (($result) (mysql_result ($result , 0) 0)) {
// continue here with the code that starts
//$res = mysql_query (SELECT * FROM type.
} else {
die('Invalid query: ' . mysql_error());
}
switch($order)
{
case 1:
$order = 'cia_people.Name';
break;
case 2:
$order = 'cia_people.Pop';
break;
case 3:
$order = 'cia_people.Nationality';
break;
case 4:
$order = 'cia_people.NationalityPlural';
break;
case 5:
$order = 'cia_people.NationalityAdjective';
break;
case 6:
$order = 'famarea2.IDParentReg';
break;
default:
$order = 'cia_people.Name';
break;
}
switch($direction)
{
case 0:
$direction = 'ASC';
break;
case 1:
$direction = 'DESC';
break;
default:
$direction = 'ASC';
break;
}
//-- [...]
$sql = 'SELECT
F.IDArea,
C.IDArea, C.Name, C.Pop, C.Nationality,
C.NationalityPlural, C.NationalityAdjective
FROM
cia_people C, famarea2 F
WHERE
(C.Nationality is not null)
AND
(F.IDArea = \'eur\')
ORDER BY
$_POST[\'order\'], $_POST[\'direction\']';
$res = mysql_query($sql) or die('Failed to run ' .
$sql . ' - ' . mysql_error());
echo 'table class=sortphp id=tab_cia_people_peo
thead
trthCountry/ththX/th/tr
/thead
tbody';
//!-- BeginDynamicTable --
$rowcounter=0;
while ($row = mysql_fetch_array ($res)) {
$c=$colors[$rowcounter++%$size];
echo tr style=\background-color:$c\ class='.
$row['Name'] .'. $_SERVER['PHP_SELF'] .'?id='.
$row['IDArea'] .
td class='tdname' '. $row['Name'] .'.
$row['Name'] ./td
tdnbsp;/td/tr\n;
}
?
/tr
/tbody
/table
/body
/html
?
__
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]
Re: Specific issues with MySQL 4.0.16 on Linux or Windows 2000/3 related to tmp tables?
Please include the full query you're running, the table structure, and the number of rows in the table. A dump of 'show variables;' would be helpful too. On Wed, 2004-11-10 at 21:44, foo bar wrote: Hi Everyone, I've been Googling unsuccessfully for specific issues relating to queries run on MySQL version 4.0.16 against tmp tables. I have witnessed several occurrences where queries running on various platforms hang in a Copying to tmp table state for hours or days at a time. When the same query is manually run from the MySQL command line client, the query returns, even on very large tables. Could someone please give me a hint as to possible tuning (or upgrade?) ideas to fix this situation? I can't find anything else on each problem system that would give me a hint as to why this problem randomly occurs. I've made attempts at adjusting the tmp_table_size limits to have the system try to do order by queries in memory, but this does not seem to help the situation. Any help would be appreciated! -Kevin show full processlist output (notice that the query has been running for 7.9 days!!!): | 33 | someuser | localhost:34329 | sometable | Query | 687465 | Copying to tmp table | select date_add( date_format(time, '%Y-%c-%d 00:00:00'), INTERVAL 0 HOUR) time, ... group by 1 order by 1 __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Memory used by each open table?
Hi, If I set the table cache to 2 how much memory will it consume? And how much latency is there when mysql has to open a table before executing a query? Some background: I have a database with around 1000 tables. I'll have roughly 20 concurrent connections to the DB. And in my queries I'll have a max of 10 tables in a join. So according to http://dev.mysql.com/doc/mysql/en/Table_cache.html I should set my table_cache to 20*10 as a minimum. This db is going to be hit very hard though and I'd like to avoid the cost of opening/closing tables. So I'd like to have each mysql thread have a full cache of all tables. That's 1000*20=20,000. Thanks, Mark. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Problem
It's not translating your vars to their respective values. I didn't look to see why... But MySQL doesn't know what $_POST['order'] is. David Blomstrom wrote: This may be a purely PHP problem, but the error message says SQL syntax. Check the manual that corresponds to your MySQL server version... Failed to run SELECT F.IDArea, C.IDArea, C.Name, C.Pop, C.Nationality, C.NationalityPlural, C.NationalityAdjective FROM cia_people C, famarea2 F WHERE (C.Nationality is not null) AND (F.IDArea = 'eur') ORDER BY $_POST['order'], $_POST['direction'] - 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 '['order'], $_POST['direction']' at line 11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
quote and null
Hi all.
I have question about how the function quote() works with
NULL values. Here is what the mysql manual say:
---
QUOTE(str)
Quotes a string to produce a result that can be used as a
properly escaped data value in an SQL statement. The string
is returned surrounded by single quotes and with each
instance of single quote (`''), backslash (`\'), ASCII NUL,
and Control-Z preceded by a backslash. If the argument is
NULL, the return value is the word ``NULL'' without
surrounding single quotes. The QUOTE() function was added in
MySQL 4.0.3.
mysql SELECT QUOTE('Don\'t!');
- 'Don\'t!'
mysql SELECT QUOTE(NULL);
- NULL
---
Now after reading this I thought that select quote(NULL)
would return the string 'NULL' (without quotes) and not the
NULL value. However, this is not true on the version of
mysql that I'm using:
mysql select version();
+-+
| version() |
+-+
| 4.0.22-standard-log |
+-+
1 row in set (0.00 sec)
mysql select isnull(quote(NULL));
+-+
| isnull(quote(NULL)) |
+-+
| 1 |
+-+
1 row in set (0.01 sec)
mysql select isnull(quote('not null'));
+---+
| isnull(quote('not null')) |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
So I guess I'm just confused by the documentation. It's easy
enough to get the string 'NULL' by using select
ifnull(quote(NULL), 'NULL') or something similiar.
Based on the functionality I experienced I thought that the
documentation should read something like:
---
QUOTE(str)
... If the argument is NULL, the return value is NULL. ...
---
Does this seem correct? Have I missed or overlooked something?
Cheers.
Toro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky self join query help?
The parts I am interested in: (I won't bore you with the fields not relevant to this problem ) CREATE TABLE events ( e_id int(15) NOT NULL auto_increment, e_owner int(15) NOT NULL default '0', e_time int(15) NOT NULL default '0', other junk omitted PRIMARY KEY (e_id) ) TYPE=MyISAM; Thanks And I am liking that other answer although it has all nulls in the second owner column and I don't get how it works. When I write applications that delete I always back up the table and use a copy or a small sample on a play database. [EMAIL PROTECTED] wrote: If you post the table structure (SHOW CREATE TABLE tablename\G) we could help you write this statement. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Gerald Taylor [EMAIL PROTECTED] wrote on 11/10/2004 11:52:35 AM: I have this table of events. Each event has an owner id and the time that it happened. What I want to do is delete all events more than three months old but only if the owner does not own any newer events. The coolest would just be a single DELETE query. Can this be done? Mysql 4.0.18 -- 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: quote and null
String operations with null values always result in null.
(none) select 'tacos' = null;
++
| 'tacos' = null |
++
| NULL |
++
1 row in set (0.00 sec)
-Eric
On Thu, 11 Nov 2004 11:35:58 +1300, Toro Hill [EMAIL PROTECTED] wrote:
Hi all.
I have question about how the function quote() works with
NULL values. Here is what the mysql manual say:
---
QUOTE(str)
Quotes a string to produce a result that can be used as a
properly escaped data value in an SQL statement. The string
is returned surrounded by single quotes and with each
instance of single quote (`''), backslash (`\'), ASCII NUL,
and Control-Z preceded by a backslash. If the argument is
NULL, the return value is the word ``NULL'' without
surrounding single quotes. The QUOTE() function was added in
MySQL 4.0.3.
mysql SELECT QUOTE('Don\'t!');
- 'Don\'t!'
mysql SELECT QUOTE(NULL);
- NULL
---
Now after reading this I thought that select quote(NULL)
would return the string 'NULL' (without quotes) and not the
NULL value. However, this is not true on the version of
mysql that I'm using:
mysql select version();
+-+
| version() |
+-+
| 4.0.22-standard-log |
+-+
1 row in set (0.00 sec)
mysql select isnull(quote(NULL));
+-+
| isnull(quote(NULL)) |
+-+
| 1 |
+-+
1 row in set (0.01 sec)
mysql select isnull(quote('not null'));
+---+
| isnull(quote('not null')) |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
So I guess I'm just confused by the documentation. It's easy
enough to get the string 'NULL' by using select
ifnull(quote(NULL), 'NULL') or something similiar.
Based on the functionality I experienced I thought that the
documentation should read something like:
---
QUOTE(str)
... If the argument is NULL, the return value is NULL. ...
---
Does this seem correct? Have I missed or overlooked something?
Cheers.
Toro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Eric Bergen
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB data files keep growing with innodb_file_per_table
John, the output shows that there are no dangling transactions, and purge is not lagging behind. If you update a secondary index column, that requires purge to clean up the index. Please shut down mysqld, remove innodb_file_per_table from my.cnf, and restart mysqld. Then do CREATE TABLE test.t(a INT) TYPE=InnoDB; Then do SHOW TABLE STATUS FROM test; What does it print as the InnoDB free space for the table test.t? That is the free space in the system tablespace. After this, you can shut down mysqld, return my.cnf to what it was, and restart mysqld. Regards, Heikki - Original Message - From: John B. Ivski [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, November 10, 2004 4:25 AM Subject: Re: InnoDB data files keep growing with innodb_file_per_table Heikki, Heikki Tuuri wrote: John, please post what SHOW INNODB STATUS says. The probable reason is that there are long transactions, or that purge is falling behind. Best regards, Heikki Tuuri Actually, I have no idea why purge was falling behind, since no row has ever been deleted from the database, and as far as I understand (and the docs say so) purge lag only concerns rows marked for deletion. Sorry, I forgot to attach 'SHOW INNODB STATUS' output to my first post, probably the main reason being that it never reported any errors or warnings, so it slipped off my mind ;) Here's some more info: The database accumulates price changes over time on foreign exchange market. 99% of all operations are single-row INSERTs/UPDATEs that happen 2-3 times/second. Once a day there're 'INSERT ... ON DUPLICATE KEY UPDATE' of several thousand rows, which are performed in 1000-row batches. All inserts use primary key and there're no auto_increment columns. SELECTs are rare (~once/hour) but can result in up to 500,000 rows returned. SELECTs use primary keys as well - there's no JOINs of any kind, only 'ORDER BY'. 'SHOW INNODB STATUS' output below. Good luck, Ivan P.S. Is there any way to see the structure/contents of idbdata files? It would probably shed some light on the subject... = 041110 5:12:05 INNODB MONITOR OUTPUT = Per second averages calculated from the last 14 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 33099, signal count 33069 Mutex spin waits 106616, rounds 265594, OS waits 2863 RW-shared spins 53566, OS waits 26538; RW-excl spins 2871, OS waits 2672 TRANSACTIONS Trx id counter 0 17800155 Purge done for trx's n:o 0 17800149 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 3696 MySQL thread id 534, query id 7111938 localhost 127.0.0.1 root show innodb status ---TRANSACTION 0 0, not started, OS thread id 896 MySQL thread id 511, query id 7080473 localhost 127.0.0.1 root ---TRANSACTION 0 17800153, not started, OS thread id 640 MySQL thread id 421, query id 7111935 localhost 127.0.0.1 mysql ---TRANSACTION 0 17800110, not started, OS thread id 2480 MySQL thread id 22, query id 7111918 localhost 127.0.0.1 mysql FILE I/O I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 226645 OS file reads, 1093510 OS file writes, 599391 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 3.93 writes/s, 1.64 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, is empty Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 553253, used cells 26, node heap has 1 buffer(s) 1.36 hash searches/s, 86.64 non-hash searches/s --- LOG --- Log sequence number 1 1826613855 Log flushed up to 1 1826613855 Last checkpoint at 1 1826612159 0 pending log writes, 0 pending chkp writes 125358 log i/o's done, 0.43 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 153403523; in additional pool allocated 918656 Buffer pool size 8192 Free buffers 0 Database pages 8191 Modified db pages 20 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 226501, created 8080, written 939024 0.00 reads/s, 0.00 creates/s, 3.43 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread id 956, state: sleeping Number of rows inserted 593305, updated 1606540, deleted 0, read 453171610 1.79 inserts/s, 4.00 updates/s, 0.00 deletes/s, 571.89 reads/s
Re: quote and null
At 11:35 +1300 11/11/04, Toro Hill wrote:
Hi all.
I have question about how the function quote() works with NULL
values. Here is what the mysql manual say:
---
QUOTE(str)
Quotes a string to produce a result that can be used as a properly
escaped data value in an SQL statement. The string is returned
surrounded by single quotes and with each instance of single quote
(`''), backslash (`\'), ASCII NUL, and Control-Z preceded by a
backslash. If the argument is NULL, the return value is the word
``NULL'' without surrounding single quotes. The QUOTE() function was
added in MySQL 4.0.3.
mysql SELECT QUOTE('Don\'t!');
- 'Don\'t!'
mysql SELECT QUOTE(NULL);
- NULL
---
Now after reading this I thought that select quote(NULL) would
return the string 'NULL' (without quotes) and not the NULL value.
However, this is not true on the version of mysql that I'm using:
mysql select version();
+-+
| version() |
+-+
| 4.0.22-standard-log |
+-+
1 row in set (0.00 sec)
mysql select isnull(quote(NULL));
+-+
| isnull(quote(NULL)) |
+-+
| 1 |
+-+
1 row in set (0.01 sec)
mysql select isnull(quote('not null'));
+---+
| isnull(quote('not null')) |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
So I guess I'm just confused by the documentation. It's easy enough
to get the string 'NULL' by using select ifnull(quote(NULL), 'NULL')
or something similiar.
Based on the functionality I experienced I thought that the
documentation should read something like:
---
QUOTE(str)
... If the argument is NULL, the return value is NULL. ...
---
Does this seem correct? Have I missed or overlooked something?
No, I think this is a bug. QUOTE(NULL) should return a string,
not a NULL value. I've filed a bug report:
http://bugs.mysql.com/bug.php?id=6564
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
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: InnoDB data files keep growing with innodb_file_per_table
John, I can think of another explanation. Are you sure that all your tables really are in .ibd files? Maybe some older tables are actually in the ibdata files? Please use the innodb_tablespace_monitor to print the contents of the ibdata files. Do like this: mysql create table innodb_tablespace_monitor(a int); Query OK, 0 rows affected (0.07 sec) wait 60 seconds here mysql drop table innodb_tablespace_monitor; Query OK, 0 rows affected (0.00 sec) mysqld prints: 04 1:39:22 INNODB TABLESPACE MONITOR OUTPUT FILE SPACE INFO: id 0 size 1152, free limit 320, free extents 1 not full frag extents 2: used pages 71, full frag extents 0 first seg id not used 0 81 SEGMENT id 0 1 space 0; page 2; res 2 used 2; full ext 0 fragm pages 2; free extents 0; not full extents 0: pages 0 SEGMENT id 0 2 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 3 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 4 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 5 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 6 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 7 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 8 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 9 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 10 space 0; page 2; res 3 used 3; full ext 0 fragm pages 3; free extents 0; not full extents 0: pages 0 SEGMENT id 0 11 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 12 space 0; page 2; res 4 used 4; full ext 0 fragm pages 4; free extents 0; not full extents 0: pages 0 SEGMENT id 0 13 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 14 space 0; page 2; res 3 used 3; full ext 0 fragm pages 3; free extents 0; not full extents 0: pages 0 SEGMENT id 0 15 space 0; page 2; res 160 used 160; full ext 2 fragm pages 32; free extents 0; not full extents 0: pages 0 SEGMENT id 0 65 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 17 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 18 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 19 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 20 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 21 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 22 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 23 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 24 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 0 73 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 68 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 70 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 75 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 33 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 35 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 79 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 80 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 40 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 59 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 60 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 NUMBER of file segments: 35 Validating tablespace Validation ok --- END OF INNODB TABLESPACE MONITOR
Re: Specific issues with MySQL 4.0.16 on Linux or Windows 2000/3 related to tmp tables?
Here's the whole the query, table structure, table length and show variables output: mysql desc summary; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | host_id | int(10) unsigned | | MUL | 0 || | alias_domain_id | int(10) unsigned | YES | MUL | NULL|| | domain_id | int(10) unsigned | YES | MUL | NULL|| | alias_id| int(10) unsigned | YES | MUL | NULL|| | sender_domain_id| int(10) unsigned | YES | MUL | NULL|| | sender_alias_id | int(10) unsigned | YES | MUL | NULL|| | time| datetime | | MUL | -00-00 00:00:00 || | val1| int(10) unsigned | | | 0 || | val2| int(10) unsigned | | | 0 || | processed | int(10) unsigned | | | 0 || +-+--+--+-+-++ select count(*) from summary; +--+ | count(*) | +--+ | 34759085 | +--+ 1 row in set (0.05 sec) Query: create temporary table tmp (PRIMARY KEY(alias_id,domain_id)) select alias_id, domain_id, sum(val1) rank from summary where 1=1 and time = '2004-11-01 11:00:00' and time = '2004-11-09 11:00:00' group by 1, 2 order by rank desc limit 5 'show variables;' output: Variable_name Value back_log50 basedir /opt/mysql-pro-4.0.16/ binlog_cache_size 32768 bulk_insert_buffer_size 8388608 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert ON connect_timeout 5 convert_character_set datadir /opt/mysql-pro-4.0.16/data/ default_week_format 0 delay_key_write ON delayed_insert_limit100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 0 ft_boolean_syntax + -()~*:| ft_min_word_len 4 ft_max_word_len 254 ft_max_word_len_for_sort20 ft_stopword_file(built-in) have_bdbNO have_crypt YES have_innodb YES have_isam YES have_raid NO have_symlinkYES have_opensslNO have_query_cacheYES init_file innodb_additional_mem_pool_size 1048576 innodb_buffer_pool_size 8388608 innodb_data_file_path ibdata1:10M:autoextend innodb_data_home_dir innodb_file_io_threads 4 innodb_force_recovery 0 innodb_thread_concurrency 8 innodb_flush_log_at_trx_commit 1 innodb_fast_shutdownON innodb_flush_method innodb_lock_wait_timeout50 innodb_log_arch_dir ./ innodb_log_archive OFF innodb_log_buffer_size 1048576 innodb_log_file_size5242880 innodb_log_files_in_group 2 innodb_log_group_home_dir ./ innodb_mirrored_log_groups 1 innodb_max_dirty_pages_pct 90 interactive_timeout 28800 join_buffer_size131072 key_buffer_size 134217728 language /opt/mysql-pro-4.0.16/share/mysql/english/ large_files_support ON local_infileON locked_in_memoryOFF log ON log_update OFF log_bin OFF log_slave_updates OFF log_slow_queriesOFF log_warningsOFF long_query_time 10 low_priority_updatesOFF lower_case_table_names OFF max_allowed_packet 1048576 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connections 100 max_connect_errors 10 max_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 1024 max_user_connections0 max_tmp_tables 32 max_write_lock_count4294967295 myisam_max_extra_sort_file_size 268435456 myisam_max_sort_file_size 2147483647 myisam_repair_threads 1 myisam_recover_options OFF myisam_sort_buffer_size 8388608 net_buffer_length 16384 net_read_timeout30 net_retry_count 10 net_write_timeout 60 new OFF open_files_limit1024 pid_file /opt/mysql-pro-4.0.16/data/testsystem.pid log_error port3306 protocol_version10 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_size0 query_cache_typeON query_prealloc_size 8192 range_alloc_block_size 2048 read_buffer_size131072 read_only
Re: Specific issues with MySQL 4.0.16 on Linux or Windows 2000/3 related to tmp tables?
In my experience, inserting into a table with a unique key and more than 5 million records can be very slow because (AFAIK) it has to scan the entire index to check if the new record is unique from the PRIMARY KEY's point of view. (I think) If you don't have much ram (your 128 meg key buffer suggests you dont) and your disk channel isn't particularly fast, this may take a very long time. Your tmp_table_size of 200 Megs is large considering your key buffer is 128M. If the machine doesn't have enough RAM, it may end up swapping heavily trying to keep the tmp table in memory and get stuck they way you've described - although I must admit I'm not sure what mysql does in a situation where tmp_table_size is too large. How much RAM does the machine have? Can you check swap activity? On Thu, 2004-11-11 at 00:27, foo bar wrote: Here's the whole the query, table structure, table length and show variables output: mysql desc summary; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | host_id | int(10) unsigned | | MUL | 0 || | alias_domain_id | int(10) unsigned | YES | MUL | NULL|| | domain_id | int(10) unsigned | YES | MUL | NULL|| | alias_id| int(10) unsigned | YES | MUL | NULL|| | sender_domain_id| int(10) unsigned | YES | MUL | NULL|| | sender_alias_id | int(10) unsigned | YES | MUL | NULL|| | time| datetime | | MUL | -00-00 00:00:00 || | val1| int(10) unsigned | | | 0 || | val2| int(10) unsigned | | | 0 || | processed | int(10) unsigned | | | 0 || +-+--+--+-+-++ select count(*) from summary; +--+ | count(*) | +--+ | 34759085 | +--+ 1 row in set (0.05 sec) Query: create temporary table tmp (PRIMARY KEY(alias_id,domain_id)) select alias_id, domain_id, sum(val1) rank from summary where 1=1 and time = '2004-11-01 11:00:00' and time = '2004-11-09 11:00:00' group by 1, 2 order by rank desc limit 5 'show variables;' output: Variable_name Value back_log50 basedir /opt/mysql-pro-4.0.16/ binlog_cache_size 32768 bulk_insert_buffer_size 8388608 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert ON connect_timeout 5 convert_character_set datadir /opt/mysql-pro-4.0.16/data/ default_week_format 0 delay_key_write ON delayed_insert_limit100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 0 ft_boolean_syntax + -()~*:| ft_min_word_len 4 ft_max_word_len 254 ft_max_word_len_for_sort20 ft_stopword_file(built-in) have_bdbNO have_crypt YES have_innodb YES have_isam YES have_raid NO have_symlinkYES have_opensslNO have_query_cacheYES init_file innodb_additional_mem_pool_size 1048576 innodb_buffer_pool_size 8388608 innodb_data_file_path ibdata1:10M:autoextend innodb_data_home_dir innodb_file_io_threads 4 innodb_force_recovery 0 innodb_thread_concurrency 8 innodb_flush_log_at_trx_commit 1 innodb_fast_shutdownON innodb_flush_method innodb_lock_wait_timeout50 innodb_log_arch_dir ./ innodb_log_archive OFF innodb_log_buffer_size 1048576 innodb_log_file_size5242880 innodb_log_files_in_group 2 innodb_log_group_home_dir ./ innodb_mirrored_log_groups 1 innodb_max_dirty_pages_pct 90 interactive_timeout 28800 join_buffer_size131072 key_buffer_size 134217728 language /opt/mysql-pro-4.0.16/share/mysql/english/ large_files_support ON local_infileON locked_in_memoryOFF log ON log_update OFF log_bin OFF log_slave_updates OFF log_slow_queriesOFF log_warningsOFF long_query_time 10 low_priority_updatesOFF lower_case_table_names OFF max_allowed_packet 1048576 max_binlog_cache_size
Re: SQL Syntax Problem
First echo out the SQL and verify it is what you are expecting. If it
isn't try changing it to:
$sql =
'SELECT F.IDArea,
C.IDArea,
C.Name,
C.Pop,
C.Nationality,
C.NationalityPlural,
C.NationalityAdjective
FROM cia_people C, famarea2 F
WHERE (C.Nationality is not null)
AND (F.IDArea = \'eur\')
ORDER BY ' . {$_POST['order']} . ',' . {$_POST['direction']};
and try it again. Note the variables are outside the string and
surounded by brackets.
Respectfully,
Ligaya Turmelle
Michael J. Pawlowsky wrote:
It's not translating your vars to their respective values.
I didn't look to see why...
But MySQL doesn't know what
$_POST['order']
is.
David Blomstrom wrote:
This may be a purely PHP problem, but the error
message says SQL syntax. Check the manual that
corresponds to your MySQL server version...
Failed to run SELECT F.IDArea, C.IDArea, C.Name,
C.Pop, C.Nationality, C.NationalityPlural,
C.NationalityAdjective FROM cia_people C, famarea2 F
WHERE (C.Nationality is not null) AND (F.IDArea =
'eur') ORDER BY $_POST['order'], $_POST['direction'] -
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 '['order'],
$_POST['direction']' at line 11
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RAID Question
Hello List: System: RH9, MySQL 4.1.7 I am in the process of re-setting up (I have test setup 4-5 times) a data server with the above software. This server consists of 2-CPU (Intel) RAID-1, 1-40GB IDE HDD for O/S 2-250GB IDE HDD for storing data. 250 GB IDE HDD are mirrored (RAID-1). Previously, I setup RAID while setting up RH9. Recently, while reviewing the MySQL, documentation, I noticed the following directive for configure command: # ./configure prefix=/usr/local/mysql with-raid While researching on GOOGLE, I did find quite a bit of information on MySQL RAID HOWTO search, but nothing to answer my question. Can someone explain how the aboce directive in configure works? In my setup, do I need the above directive as shown? Thanks. Kirti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Specific issues with MySQL 4.0.16 on Linux or Windows 2000/3 related to tmp tables?
Hi Mark, The system in question has 1GB of RAM in it. As far as I can tell, the box does not get stuck swapping when the system has a query (or several) in this state. If I log in via the command line client and kill the query, the system continues on it's way like nothing was wrong in the first place. It almost seems to me that the server is getting into some strange state while writing to temporary table. For some reason, this fails and instead of erroring out, the query stays in a perpetual running state. On MySQL 4.0.16, a key_buffer_size of 134217728 translates into 134217728/1024 bytes per key = 131072 possible cached key values. Because this table (in addition to several others) may have several million rows, in order to cache say 3 million keys, I'd have to allocate about 3GB worth of ram just to this single buffer. Thanks for the info though! --- Mark Maunder [EMAIL PROTECTED] wrote: In my experience, inserting into a table with a unique key and more than 5 million records can be very slow because (AFAIK) it has to scan the entire index to check if the new record is unique from the PRIMARY KEY's point of view. (I think) If you don't have much ram (your 128 meg key buffer suggests you dont) and your disk channel isn't particularly fast, this may take a very long time. Your tmp_table_size of 200 Megs is large considering your key buffer is 128M. If the machine doesn't have enough RAM, it may end up swapping heavily trying to keep the tmp table in memory and get stuck they way you've described - although I must admit I'm not sure what mysql does in a situation where tmp_table_size is too large. How much RAM does the machine have? Can you check swap activity? On Thu, 2004-11-11 at 00:27, foo bar wrote: Here's the whole the query, table structure, table length and show variables output: mysql desc summary; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL| auto_increment | | host_id | int(10) unsigned | | MUL | 0 || | alias_domain_id | int(10) unsigned | YES | MUL | NULL|| | domain_id | int(10) unsigned | YES | MUL | NULL|| | alias_id| int(10) unsigned | YES | MUL | NULL|| | sender_domain_id| int(10) unsigned | YES | MUL | NULL|| | sender_alias_id | int(10) unsigned | YES | MUL | NULL|| | time| datetime | | MUL | -00-00 00:00:00 || | val1| int(10) unsigned | | | 0 || | val2| int(10) unsigned | | | 0 || | processed | int(10) unsigned | | | 0 || +-+--+--+-+-++ select count(*) from summary; +--+ | count(*) | +--+ | 34759085 | +--+ 1 row in set (0.05 sec) Query: create temporary table tmp (PRIMARY KEY(alias_id,domain_id)) select alias_id, domain_id, sum(val1) rank from summary where 1=1 and time = '2004-11-01 11:00:00' and time = '2004-11-09 11:00:00' group by 1, 2 order by rank desc limit 5 'show variables;' output: Variable_name Value back_log50 basedir /opt/mysql-pro-4.0.16/ binlog_cache_size 32768 bulk_insert_buffer_size 8388608 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert ON connect_timeout 5 convert_character_set datadir /opt/mysql-pro-4.0.16/data/ default_week_format 0 delay_key_write ON delayed_insert_limit100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 0 ft_boolean_syntax + -()~*:| ft_min_word_len 4 ft_max_word_len 254 ft_max_word_len_for_sort20 ft_stopword_file(built-in) have_bdbNO have_crypt YES have_innodb YES have_isam YES have_raid NO have_symlinkYES have_opensslNO have_query_cacheYES init_file innodb_additional_mem_pool_size 1048576
SELECT on string
hi i have a recordset of about 4 mil records, SELECT * from rec where string_field=somestring takes very long time (30+ sec). string_field is indexed MUL. Is there way to make it faster?
Re: SELECT on string
What does EXPLAIN SELECT * from rec where string_field='somestring'; say? Michael Dan Sashko wrote: hi i have a recordset of about 4 mil records, SELECT * from rec where string_field=somestring takes very long time (30+ sec). string_field is indexed MUL. Is there way to make it faster? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: quote and null
Toro Hill wrote:
Hi all.
I have question about how the function quote() works with NULL values.
Here is what the mysql manual say:
---
QUOTE(str)
Quotes a string to produce a result that can be used as a properly
escaped data value in an SQL statement. The string is returned
surrounded by single quotes and with each instance of single quote
(`''), backslash (`\'), ASCII NUL, and Control-Z preceded by a
backslash. If the argument is NULL, the return value is the word
``NULL'' without surrounding single quotes. The QUOTE() function was
added in MySQL 4.0.3.
mysql SELECT QUOTE('Don\'t!');
- 'Don\'t!'
mysql SELECT QUOTE(NULL);
- NULL
---
Now after reading this I thought that select quote(NULL) would return
the string 'NULL' (without quotes) and not the NULL value. However, this
is not true on the version of mysql that I'm using:
You've misunderstood, then. Without the quotes, NULL is the NULL value, not
a string. You have to have quotes to be a string! NULL is NULL, 'NULL' is
a string. MySQL is doing exactly what the manual says.
mysql select version();
+-+
| version() |
+-+
| 4.0.22-standard-log |
+-+
1 row in set (0.00 sec)
mysql select isnull(quote(NULL));
+-+
| isnull(quote(NULL)) |
+-+
| 1 |
+-+
1 row in set (0.01 sec)
mysql select isnull(quote('not null'));
+---+
| isnull(quote('not null')) |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
So I guess I'm just confused by the documentation. It's easy enough to
get the string 'NULL' by using select ifnull(quote(NULL), 'NULL') or
something similiar.
You don't need IFNULL for that.
mysql SELECT QUOTE('NULL');
+---+
| QUOTE('NULL') |
+---+
| 'NULL'|
+---+
1 row in set (0.00 sec)
Based on the functionality I experienced I thought that the
documentation should read something like:
---
QUOTE(str)
... If the argument is NULL, the return value is NULL. ...
---
That is what it says, just not in those words. Your wording is better,
though, in my opinion.
Does this seem correct? Have I missed or overlooked something?
It is correct in that it behaves as documented.
Cheers.
Toro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: quote and null
Paul DuBois wrote:
At 11:35 +1300 11/11/04, Toro Hill wrote:
Hi all.
I have question about how the function quote() works with NULL values.
Here is what the mysql manual say:
---
QUOTE(str)
Quotes a string to produce a result that can be used as a properly
escaped data value in an SQL statement. The string is returned
surrounded by single quotes and with each instance of single quote
(`''), backslash (`\'), ASCII NUL, and Control-Z preceded by a
backslash. If the argument is NULL, the return value is the word
``NULL'' without surrounding single quotes. The QUOTE() function was
added in MySQL 4.0.3.
mysql SELECT QUOTE('Don\'t!');
- 'Don\'t!'
mysql SELECT QUOTE(NULL);
- NULL
snip
Does this seem correct? Have I missed or overlooked something?
No, I think this is a bug. QUOTE(NULL) should return a string,
not a NULL value. I've filed a bug report:
http://bugs.mysql.com/bug.php?id=6564
Why? It is doing exactly as documented. Quote is supposed to
backslash-escape the given string. NULL is not a string. How do you
backslash-escape a NULL string? I think NULL is the only proper output of
QUOTE(NULL), just as NULL is the only proper output of most functions when
given NULL input (with the exception of the NULL-specific functions, of course).
mysql CREATE TABLE qt (s CHAR(10));
Query OK, 0 rows affected (0.01 sec)
mysql INSERT INTO qt VALUES
- ('a string'),
- ('doesn\'t'),
- (NULL),
- ('C:\\dir1');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql SELECT s, QUOTE(s) FROM qt;
+--++
| s| QUOTE(s) |
+--++
| a string | 'a string' |
| doesn't | 'doesn\'t' |
| NULL | NULL |
| C:\dir1 | 'C:\\dir1' |
+--++
4 rows in set (0.00 sec)
Makes sense to me.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: quote and null
At 21:11 -0500 11/10/04, Michael Stassen wrote:
Toro Hill wrote:
Hi all.
I have question about how the function quote() works with NULL
values. Here is what the mysql manual say:
---
QUOTE(str)
Quotes a string to produce a result that can be used as a properly
escaped data value in an SQL statement. The string is returned
surrounded by single quotes and with each instance of single quote
(`''), backslash (`\'), ASCII NUL, and Control-Z preceded by a
backslash. If the argument is NULL, the return value is the word
``NULL'' without surrounding single quotes. The QUOTE() function
was added in MySQL 4.0.3.
mysql SELECT QUOTE('Don\'t!');
- 'Don\'t!'
mysql SELECT QUOTE(NULL);
- NULL
---
Now after reading this I thought that select quote(NULL) would
return the string 'NULL' (without quotes) and not the NULL value.
However, this is not true on the version of mysql that I'm using:
You've misunderstood, then. Without the quotes, NULL is the NULL
value, not a string. You have to have quotes to be a string! NULL
is NULL, 'NULL' is a string. MySQL is doing exactly what the manual
says.
It's not. The manual says that if the argument is NULL, the return
value is _the word_ NULL without quotes. In other words, it's a string
but the string doesn't include surrounding quotes.
QUOTE() is supposed to produce values similar to what you get with the
DBI quote() function.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
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: SELECT on string
i was mistaking before, query is more like : SELECT MyField, count(id) from MyRec where string_field=somestring group by field; and it's explain is : +-+--+---++-+++-+ | table | type | possible_keys | key| key_len | ref| rows | Extra | +-+--+---++-+++-+ | MyRec| ref | MyField | MyField | 21 | const | 151609 | Using where | +-+--+---++-+++-+ takes about minute and a half. - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Dan Sashko [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, November 10, 2004 6:01 PM Subject: Re: SELECT on string What does EXPLAIN SELECT * from rec where string_field='somestring'; say? Michael Dan Sashko wrote: hi i have a recordset of about 4 mil records, SELECT * from rec where string_field=somestring takes very long time (30+ sec). string_field is indexed MUL. Is there way to make it faster? -- 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]
improving query response time
Hi mike, as i have mentioned in my previous mail the table structure which i am using is as fallows, CREATE TABLE IND_KAR_BNG_Metallica_PS_RT_4 ( gan_id INTEGER NOT NULL, bsc_id INTEGER NOT NULL, bts_id INTEGER NOT NULL, bd_type VARCHAR(10) NOT NULL, bd_id INTEGER NOT NULL, duplex VARCHAR(10) NOT NULL, data_GenTime DATETIME NOT NULL, item_id INTEGER NOT NULL, M0 INTEGER NOT NULL, M1 INTEGER NOT NULL, M2 INTEGER NOT NULL, M3 INTEGER NOT NULL, M4 INTEGER NOT NULL, M5 INTEGER NOT NULL, M6 INTEGER NOT NULL, M7 INTEGER NOT NULL, M8 INTEGER NOT NULL, M9 INTEGER NOT NULL, M10 INTEGER NOT NULL, M11 INTEGER NOT NULL, M12 INTEGER NOT NULL, M13 INTEGER NOT NULL, M14 INTEGER NOT NULL, M15 INTEGER NOT NULL, M16 INTEGER NOT NULL, M17 INTEGER NOT NULL, M18 INTEGER NOT NULL, M19 INTEGER NOT NULL, M20 INTEGER NOT NULL, M21 INTEGER NOT NULL, M22 INTEGER NOT NULL, M23 INTEGER NOT NULL, M24 INTEGER NOT NULL, M25 INTEGER NOT NULL, M26 INTEGER NOT NULL, M27 INTEGER NOT NULL, M28 INTEGER NOT NULL, M29 INTEGER NOT NULL, M30 INTEGER NOT NULL, M31 INTEGER NOT NULL, INDEX RetreiveIndex (data_GenTime,gan_id ,bsc_id ,bts_id ,bd_type ,bd_id ,item_id)); the type of query that is executed is as below mysql select Sum(m0),Avg(m1),Max(m5),Min(m6) from ind_kar_bng_robocop_gan_0_pm_ipc_0 where bsc_id = 255 and data_Gentime between 2004-11-09 00:00:00 and 2004-11-10 19:41:44 and item_id = 0; +-+--+-+-+ | Sum(m0) | Avg(m1) | Max(m5) | Min(m6) | +-+--+-+-+ | 3899200 | 256. | 0 | 0 | +-+--+-+-+ 1 row in set (5.67 sec) this query executed when he record count in the table ind_kar_bng_robocop_gan_0_pm_ipc_0 was 1096650 records as the number of record in the table keep on growing the query response time increases.. the explain select result of the same query is here mysql explain select Sum(m0),Avg(m1),Max(m5),Min(m6) from ind_kar_bng_robocop_gan_0_pm _ipc_0 where - bsc_id = 255 and data_Gentime between 2004-11-09 00:00:00 and 2004-11-10 19: 41:44 - and item_id = 0 \G *** 1. row *** id: 1 select_type: SIMPLE table: ind_kar_bng_robocop_gan_0_pm_ipc_0 type: ALL possible_keys: RetreiveIndex key: NULL key_len: NULL ref: NULL rows: 1096650 Extra: Using where 1 row in set (0.00 sec) these are my system variables. mysql show variables; +-+---+ | Variable_name | Value | +-+---+ | back_log| 50 | | basedir | C:\mysql\ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client| latin1 | | character_set_connection| latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server| latin1 | | character_set_system| utf8 | | character_sets_dir | C:\mysql\share\charsets/ | | collation_connection| latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server| latin1_swedish_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | C:\mysql\data\ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | expire_logs_days| 0 | | flush | OFF | | flush_time | 1800 | | ft_boolean_syntax
Re: quote and null
Paul DuBois wrote:
At 21:11 -0500 11/10/04, Michael Stassen wrote:
Toro Hill wrote:
Hi all.
I have question about how the function quote() works with NULL
values. Here is what the mysql manual say:
---
QUOTE(str)
Quotes a string to produce a result that can be used as a properly
escaped data value in an SQL statement. The string is returned
surrounded by single quotes and with each instance of single quote
(`''), backslash (`\'), ASCII NUL, and Control-Z preceded by a
backslash. If the argument is NULL, the return value is the word
``NULL'' without surrounding single quotes. The QUOTE() function was
added in MySQL 4.0.3.
mysql SELECT QUOTE('Don\'t!');
- 'Don\'t!'
mysql SELECT QUOTE(NULL);
- NULL
---
Now after reading this I thought that select quote(NULL) would return
the string 'NULL' (without quotes) and not the NULL value. However,
this is not true on the version of mysql that I'm using:
You've misunderstood, then. Without the quotes, NULL is the NULL
value, not a string. You have to have quotes to be a string! NULL is
NULL, 'NULL' is a string. MySQL is doing exactly what the manual says.
It's not. The manual says that if the argument is NULL, the return
value is _the word_ NULL without quotes. In other words, it's a string
but the string doesn't include surrounding quotes.
Without quotes, it's not a string. I agree the manual is not worded as
clearly as it should be, but everywhere else in MySQL, NULL without quotes
means NULL, not a string. Why should here be any different.
INSERT INTO mytable VALUES
('NULL'),
(NULL);
The first is a string, the second is NULL.
The manual is quite clear that QUOTE expects a string as input. Strings
require quotes.
mysql SELECT QUOTE(a string);
ERROR 1064 (42000): 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 'string)' at line 1
mysql SELECT QUOTE(string);
ERROR 1054 (42S22): Unknown column 'string' in 'field list'
Interestingly, MySQL will attempt to convert non-string values to strings so
that QUOTE can work on them.
mysql SELECT QUOTE(2);
+--+
| QUOTE(2) |
+--+
| '2' |
+--+
1 row in set (0.00 sec)
What string should NULL be converted to?
QUOTE() is supposed to produce values similar to what you get with the
DBI quote() function.
And if the value should be NULL? 'NULL' won't do, then. I believe
$dbh-quote(UNDEF) will return NULL, not 'NULL'.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB data files keep growing with innodb_file_per_table
Heikki, the output shows that there are no dangling transactions, and purge is not lagging behind. Yes, that's what I thought... weird, huh :/ If you update a secondary index column, that requires purge to clean up the index. The tables have structure similar to the following: create table table1 ( key1 tinyint unsigned not null default 0, key2 int unsigned not null default 0, data1 int not null default 0, dataN int not null default 0, primary key(key1,key2) ) engine=innodb; updates are performed like this: insert into table table1 (key1,key2,data1,dataN) values (...),...,(...) on duplicate key update data1=values(data1),dataN=values(dataN) so primary keys don't get updated... Please shut down mysqld, remove innodb_file_per_table from my.cnf, and restart mysqld. Then do CREATE TABLE test.t(a INT) TYPE=InnoDB; Then do SHOW TABLE STATUS FROM test; What does it print as the InnoDB free space for the table test.t? That is the free space in the system tablespace. Unfortunately I won't be able to shut down the server until this weekend. Will let you know the results. Thanks for the advice. Good luck, Ivan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: quote and null
At 21:37 -0500 11/10/04, Michael Stassen wrote:
Paul DuBois wrote:
At 21:11 -0500 11/10/04, Michael Stassen wrote:
Toro Hill wrote:
Hi all.
I have question about how the function quote() works with NULL
values. Here is what the mysql manual say:
---
QUOTE(str)
Quotes a string to produce a result that can be used as a
properly escaped data value in an SQL statement. The string is
returned surrounded by single quotes and with each instance of
single quote (`''), backslash (`\'), ASCII NUL, and Control-Z
preceded by a backslash. If the argument is NULL, the return
value is the word ``NULL'' without surrounding single quotes. The
QUOTE() function was added in MySQL 4.0.3.
mysql SELECT QUOTE('Don\'t!');
- 'Don\'t!'
mysql SELECT QUOTE(NULL);
- NULL
---
Now after reading this I thought that select quote(NULL) would
return the string 'NULL' (without quotes) and not the NULL value.
However, this is not true on the version of mysql that I'm using:
You've misunderstood, then. Without the quotes, NULL is the NULL
value, not a string. You have to have quotes to be a string!
NULL is NULL, 'NULL' is a string. MySQL is doing exactly what the
manual says.
It's not. The manual says that if the argument is NULL, the return
value is _the word_ NULL without quotes. In other words, it's a string
but the string doesn't include surrounding quotes.
Without quotes, it's not a string. I agree the manual is not worded
as clearly as it should be, but everywhere else in MySQL, NULL
without quotes means NULL, not a string. Why should here be any
different.
You're overthinking it. Strings don't need quotes unless you're writing
them as string literals. The quotes aren't _part of the string_.
QUOTE() is intended for generating string values to be used for
constructing SQL statements. Consider the following sequence of
statements:
SET @a = 'abc', @b = 'def';
SET @stmt = CONCAT('INSERT INTO t VALUES(', QUOTE(@a), ',', QUOTE(@b), ');');
SELECT @stmt;
SET @a = 'abc', @b = NULL;
SET @stmt = CONCAT('INSERT INTO t VALUES(', QUOTE(@a), ',', QUOTE(@b), ');');
SELECT @stmt;
The intended result is:
++
| @stmt |
++
| INSERT INTO t VALUES('abc','def'); |
++
+---+
| @stmt |
+---+
| INSERT INTO t VALUES('abc',NULL); |
+---+
That only works if QUOTE(NULL) returns the word NULL without quotes.
That's why it's a bug for it to actually return a NULL value. The
actual result from the preceding statements is:
++
| @stmt |
++
| INSERT INTO t VALUES('abc','def'); |
++
+---+
| @stmt |
+---+
| NULL |
+---+
INSERT INTO mytable VALUES
('NULL'),
(NULL);
The first is a string, the second is NULL.
The manual is quite clear that QUOTE expects a string as input.
Strings require quotes.
String literals do.
mysql SELECT QUOTE(a string);
ERROR 1064 (42000): 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 'string)' at line 1
mysql SELECT QUOTE(string);
ERROR 1054 (42S22): Unknown column 'string' in 'field list'
Interestingly, MySQL will attempt to convert non-string values to
strings so that QUOTE can work on them.
mysql SELECT QUOTE(2);
+--+
| QUOTE(2) |
+--+
| '2' |
+--+
1 row in set (0.00 sec)
What string should NULL be converted to?
The string consisting of the four characters N U L L.
QUOTE() is supposed to produce values similar to what you get with the
DBI quote() function.
And if the value should be NULL? 'NULL' won't do, then. I believe
$dbh-quote(UNDEF) will return NULL, not 'NULL'.
It returns a string consisting of the four characters N U L L.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
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: InnoDB data files keep growing with innodb_file_per_table
Heikki, I can think of another explanation. Are you sure that all your tables really are in .ibd files? Maybe some older tables are actually in the ibdata files? Yes I'm sure they're all in .ibd files (and I've just checked just in case - they are indeed). Please use the innodb_tablespace_monitor to print the contents of the ibdata files. I feel uncomfortable about pasting here the whole 10MB output ;), but it went like this: 04 5:35:51 INNODB TABLESPACE MONITOR OUTPUT FILE SPACE INFO: id 0 size 120832, free limit 120064, free extents 3 not full frag extents 1: used pages 20, full frag extents 1462 first seg id not used 0 2 SEGMENT id 0 5635 space 0; page 461; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 5636 space 0; page 461; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 5637 space 0; page 461; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 ... the numbers increase by 1 all the way up from 5637 to 88879; res and used values are mostly 1, sometimes 2 or 3. Rarely (in like, 50 cases) they're pretty big, e.g. SEGMENT id 0 75994 space 0; page 82136; res 2720 used 2680; full ext 41 fragm pages 32; free extents 0; not full extents 1: pages 24 SEGMENT id 0 76006 space 0; page 82136; res 2592 used 2569; full ext 39 fragm pages 32; free extents 0; not full extents 1: pages 41 ... SEGMENT id 0 88879 space 0; page 119802; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 0 space 0; page 119802; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 0 1 space 0; page 119802; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 NUMBER of file segments: 82815 Validating tablespace Validation ok --- END OF INNODB TABLESPACE MONITOR OUTPUT === Let me know if you need the whole output - I'll zip it and send it to you. Good luck, Ivan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with distinct not solved by group by
I have three tables: reviews users movies I am trying to select the latest 4 reviews for DIFFERENT movies. I can use the following query: SELECT reviews.movies_id, movies.movie_title, users.name, reviews.rating, reviews.post_d FROM reviews, users, movies WHERE reviews.user_id = users.user_id and reviews.movies_id = movies.movie_id ORDER BY post_d DESC LIMIT 4 However, this can return the same movie twice if any of the last 4 reviews are of the same movie. DISTINCT is no help because I only want a distinct on movies_id, not the whole row. GROUP BY movies_id is no help because it takes the oldest review from the unique movies_id. Does anyone have an idea where I can take the most recent 4 rows that have a different movies_id without doing extra processing work in PHP? Thanks, Seth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: quote and null
Ah, I see now. That makes sense. Sorry to have been so dense.
Michael
Paul DuBois wrote:
At 21:37 -0500 11/10/04, Michael Stassen wrote:
Paul DuBois wrote:
At 21:11 -0500 11/10/04, Michael Stassen wrote:
Toro Hill wrote:
Hi all.
I have question about how the function quote() works with NULL
values. Here is what the mysql manual say:
---
QUOTE(str)
Quotes a string to produce a result that can be used as a properly
escaped data value in an SQL statement. The string is returned
surrounded by single quotes and with each instance of single quote
(`''), backslash (`\'), ASCII NUL, and Control-Z preceded by a
backslash. If the argument is NULL, the return value is the word
``NULL'' without surrounding single quotes. The QUOTE() function
was added in MySQL 4.0.3.
mysql SELECT QUOTE('Don\'t!');
- 'Don\'t!'
mysql SELECT QUOTE(NULL);
- NULL
---
Now after reading this I thought that select quote(NULL) would
return the string 'NULL' (without quotes) and not the NULL value.
However, this is not true on the version of mysql that I'm using:
You've misunderstood, then. Without the quotes, NULL is the NULL
value, not a string. You have to have quotes to be a string! NULL
is NULL, 'NULL' is a string. MySQL is doing exactly what the manual
says.
It's not. The manual says that if the argument is NULL, the return
value is _the word_ NULL without quotes. In other words, it's a string
but the string doesn't include surrounding quotes.
Without quotes, it's not a string. I agree the manual is not worded
as clearly as it should be, but everywhere else in MySQL, NULL without
quotes means NULL, not a string. Why should here be any different.
You're overthinking it. Strings don't need quotes unless you're writing
them as string literals. The quotes aren't _part of the string_.
QUOTE() is intended for generating string values to be used for
constructing SQL statements. Consider the following sequence of
statements:
SET @a = 'abc', @b = 'def';
SET @stmt = CONCAT('INSERT INTO t VALUES(', QUOTE(@a), ',', QUOTE(@b),
');');
SELECT @stmt;
SET @a = 'abc', @b = NULL;
SET @stmt = CONCAT('INSERT INTO t VALUES(', QUOTE(@a), ',', QUOTE(@b),
');');
SELECT @stmt;
The intended result is:
++
| @stmt |
++
| INSERT INTO t VALUES('abc','def'); |
++
+---+
| @stmt |
+---+
| INSERT INTO t VALUES('abc',NULL); |
+---+
That only works if QUOTE(NULL) returns the word NULL without quotes.
That's why it's a bug for it to actually return a NULL value. The
actual result from the preceding statements is:
++
| @stmt |
++
| INSERT INTO t VALUES('abc','def'); |
++
+---+
| @stmt |
+---+
| NULL |
+---+
INSERT INTO mytable VALUES
('NULL'),
(NULL);
The first is a string, the second is NULL.
The manual is quite clear that QUOTE expects a string as input.
Strings require quotes.
String literals do.
mysql SELECT QUOTE(a string);
ERROR 1064 (42000): 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 'string)' at line 1
mysql SELECT QUOTE(string);
ERROR 1054 (42S22): Unknown column 'string' in 'field list'
Interestingly, MySQL will attempt to convert non-string values to
strings so that QUOTE can work on them.
mysql SELECT QUOTE(2);
+--+
| QUOTE(2) |
+--+
| '2' |
+--+
1 row in set (0.00 sec)
What string should NULL be converted to?
The string consisting of the four characters N U L L.
QUOTE() is supposed to produce values similar to what you get with the
DBI quote() function.
And if the value should be NULL? 'NULL' won't do, then. I believe
$dbh-quote(UNDEF) will return NULL, not 'NULL'.
It returns a string consisting of the four characters N U L L.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
