Example UDF for anyone interested
If anyone's looking for another nice user-defined function example, I've just set one up at work and am happy to share the wealth :). They're really quite cool, and this one is a good deal simpler than udf_example.cc. The function is at: http://www.adamhooper.com:4242/ccode/project_view.php?project=mysql_stripchars It's a useful function that can take the place of a dozen REPLACE()'s -- it can be run either like Perl's tr/// or as a simple thing that just removes single characters from the string (its primary use is for removing gibberish characters such as "-" or "#"). It's all in the README linked there. Really it's more for example purposes than anything else -- I woulda liked having more than one example to refer to when I was coding it. Do whatever you want with it but sell it, and most of all have fun :). Hope it helps someone out. Adam Hooper [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FW: I need an example on DELETE /UPDATE with JOIN syntax
-Original Message- From: Vadim [mailto:[EMAIL PROTECTED]] Sent: Saturday, April 27, 2002 4:49 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: I need an example on DELETE /UPDATE with JOIN syntax Greetings: My company migrated to MySQL a few months ago from Sybase Adaptive Server Anywhere. Where I'm very impressed with the performance and the simplicity of management I find its very difficult, however, to run regular updates or deletes because joins are not supported on delete and update. I can live without stored procedures, triggers and views (although, they are very useful simply can not function as a DBA /SQL developer without being able to perform simple delete on a table set based on the result of a join. Can someone, please, show me an example on how can I delete/update table based on the records in another tables. This is query that works on ASA: delete from t1 from t1, t2 where t1.col1 = t2.col1 and t2.col2 is NULL ; update t1, t2 set t1.col='' where t1.col1 = t2.col1 and t2.col2 is NULL This syntax does not work in MySQL. # I need help! # Thank you very much for you time. Vadim Kulikov 877-428-3279 [EMAIL PROTECTED] P.S. Someone left a comment with the example on how you can do delete with join under MYSQL help site. But its not working. These are the comments I copied from the http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html (MySQL documentation site): (the problem is that the following example with a flag doesn't work due to inability to do update with a join) Comments: - John Gwilliam: Deletes and sub-selects. I have found a convenient way of avoiding sub- selects for deleting as follows - 1/ Add a DELETE_FLAG column to the table involved. 2/ Set the DELETE_FLAG using UPDATE, where joins can be used. 3/ DELETE from the table using a simple WHERE clause to select rows where the DELETE_FLAG is set. <[EMAIL PROTECTED]>: I'm having trouble with your delete suggestion, can you give an example of the update query you use for your sub-selects for deleting purposes? While SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; works fine, I can't get UPDATE table1 set deleteflag = 1 where table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL to work. Does anyone know how to use LEFT JOINs in an update statement? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Blobs+ASP+MySQL
Why store the pic in the DB? I know there is an ASP upload utility avaliable. Try www.asp101.com or www.hotscripts.com HTH Mark -Original Message- From: Zill-e-Hassan <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Thursday, April 25, 2002 1:48 PM Subject: Blobs+ASP+MySQL >Dear All > >I have been trying to find out if there is a way of actually storing >pictures as Blobs in MySql . Eventually i want to upload and download >pictures to/from the database through the use of ASP. I would also welcome >any suggestions that you guys may have regarding how to upload the image >file from my ASP page. I do not want to store the reference. I want to store >the whole picture. > >In addition to that , i would also like to find out if I can store blobs in >the database through SQL command. Any Ideas ??? > >Insert .. > > > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql read only ? sql,query
[EMAIL PROTECTED] wrote: > > Your message cannot be posted because it appears to be either spam or > simply off topic to our filter. To bypass the filter you must include > one of the following words in your message: > > sql,query > > If you just reply to this message, and include the entire text of it in the > reply, your reply will go through. However, you should > first review the text of the message to make sure it has something to do > with MySQL. Just typing the word MySQL once will be sufficient, for example. > > You have written the following: > > How to enable the table for read write again ? > Best regards, Ridwan Goldbase Technology http://www.pembukuan.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Stuck Some More
In reply to Egor's request... Egor> > Ian, show me the contents of your .err file (the last 20-40 rows) This is the total contents of this file. /nfs/usr/libexec/mysqld: ready for connections /nfs/usr/libexec/mysqld: ready for connections /nfs/usr/libexec/mysqld: ready for connections /nfs/usr/libexec/mysqld: ready for connections Can't start server: Bind on TCP/IP port: Address already in use Can't start server: Bind on TCP/IP port: Address already in use Can't start server: Bind on TCP/IP port: Address already in use Can't start server: Bind on TCP/IP port: Address already in use Can't start server: Bind on TCP/IP port: Address already in use Can't start server: Bind on TCP/IP port: Address already in use Can't start server: Bind on TCP/IP port: Address already in use Ian - Original Message - From: "Egor Egorov" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, April 27, 2002 7:16 PM Subject: Re: Stuck Some More > Ian, > Saturday, April 27, 2002, 2:31:28 AM, you wrote: > > IP> Mysql has not been used on my server before, and I am the only user who has > IP> ssh access to the system, (any mysqld processes are mine) and have been > IP> trying to get mysql set up and working properly. > > IP> Currenlty I have two mysqld processes idle on my system > IP> 5404 p2- I 0:00.00 (mysqld) > IP> 15488 p4- I 0:00.00 (mysqld) > > IP> I have tried to end these (kill -s HUP 5404 / kill -s HUP 15488) - but this > IP> does not have any affect. > > IP> I have tried to start the mysql server (currently it's not running | ERROR > IP> 2002: Can't connect to local MySQL server ), using the safe_mysqld& > IP> command - with this result... > IP> [1] 19882 > IP> user_obscured:/# Starting mysqld demon with databases from /usr/local/var > IP> nohup > IP> /nfs/usr/libexec/mysqld --basedir=/usr/local --datadir=/usr/local/var --port > IP> =30100 & > IP> mysqld demon ended > > IP> (enter) > > IP> [1]+ Donesafe_mysqld > > IP> and mysql does not restart. > > IP> Can anyone explain what may be going on here? > IP> How can I clear the mysqld processes, and restart mysql so that I can use > IP> it? > > Ian, show me the contents of your .err file (the last 20-40 rows) > > IP> Ian > > > > > > > -- > For technical support contracts, goto https://order.mysql.com/ > This email is sponsored by Ensita.net http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Egor Egorov > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ><___/ www.mysql.com > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: What next
In reply to Egor's question: > Execute: >ps ax | grep mysql > > Is there MySQL in the process list? Only mysqld (2 of them) 5404 p2- I 0:00.00 (mysqld) 15488 p4- I 0:00.00 (mysqld) Ian - Original Message - From: "Egor Egorov" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, April 27, 2002 7:16 PM Subject: Re: What next > Ian, > Saturday, April 27, 2002, 1:42:46 AM, you wrote: > > IP> I was able to start the mysql server using this suugested by Egor > > Egor >> safe_mysqld& > IP> [1] 15476 > IP> user_obscured:/# Starting mysqld demon with databases from /usr/local/var > IP> nohup > IP> /nfs/usr/libexec/mysqld --basedir=/usr/local --datadir=/usr/local/var --port > IP> =30100 & > > IP> but after this response, the ssh session 'just sits there' - seems as if > IP> it's waiting for further input - is ther a way to exit ther session - or is > IP> this what I should / shouldn't be doing at this point? > > press "Enter" :) > > Execute: >ps ax | grep mysql > > Is there MySQL in the process list? > > IP> Ian > > > > > > > -- > For technical support contracts, goto https://order.mysql.com/ > This email is sponsored by Ensita.net http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Egor Egorov > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ><___/ www.mysql.com > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL, mm.MySQL, and SQL Problems
Mark Matthews wrote: > First off, let me say "Thanks" for the work you've done with the XML Parser. > I used it heavily on a project last year and it performed admirably. Cool. :) > Hmm. MM.MySQL will do this for you. Seeing your next comment leads me to > believe that you've found yourself a very old version of MM.MySQL that > doesn't do this or PreparedStatements. Both character set translation and > PreparedStatements have been features of MM.MySQL for more than a couple of > years now :) Hmmm... Well, I just downloaded it from the link on the MySQL web page so I guess it's out of date. I'll download the latest from the link you provided. BTW, thanks for taking the trouble of writing this JDBC driver for MySQL. :) > Un-jar it. Look at the README, especially the part about the URL paremeters > "useUnicode" and "characterEncoding". By setting these in your JDBC URL, the > driver will automagically do the right thing. I saw those options but without the prepared statements, it didn't matter too much. Once I get the right version every- thing should work great. I'm really looking forward to it. :) Now I just need some help with my SQL related problems... > -Mark (The "MM" behind MM.MySQL) That's a good way to make yourself famous -- just put your name in the product's name! Perhaps I should have tried harder to convince everyone to rename XML4J to "AndyC" instead of "Xerces". Oh well... ;) -- Andy Clark * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
I need an example on DELETE /UPDATE with JOIN syntax
Greetings: My company migrated to MySQL a few months ago from Sybase Adaptive Server Anywhere. Where I'm very impressed with the performance and the simplicity of management I find its very difficult, however, to run regular updates or deletes because joins are not supported on delete and update. I can live without stored procedures, triggers and views (although, they are very useful simply can not function as a DBA /SQL developer without being able to perform simple delete on a table set based on the result of a join. Can someone, please, show me an example on how can I delete/update table based on the records in another tables. This is query that works on ASA: delete from t1 from t1, t2 where t1.col1 = t2.col1 and t2.col2 is NULL ; update t1, t2 set t1.col='' where t1.col1 = t2.col1 and t2.col2 is NULL This syntax does not work in MySQL. # I need help! # Thank you very much for you time. Vadim Kulikov 877-428-3279 [EMAIL PROTECTED] P.S. Someone left a comment with the example on how you can do delete with join under MYSQL help site. But its not working. These are the comments I copied from the http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html (MySQL documentation site): (the problem is that the following example with a flag doesn't work due to inability to do update with a join) Comments: - John Gwilliam: Deletes and sub-selects. I have found a convenient way of avoiding sub- selects for deleting as follows - 1/ Add a DELETE_FLAG column to the table involved. 2/ Set the DELETE_FLAG using UPDATE, where joins can be used. 3/ DELETE from the table using a simple WHERE clause to select rows where the DELETE_FLAG is set. <[EMAIL PROTECTED]>: I'm having trouble with your delete suggestion, can you give an example of the update query you use for your sub-selects for deleting purposes? While SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; works fine, I can't get UPDATE table1 set deleteflag = 1 where table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL to work. Does anyone know how to use LEFT JOINs in an update statement? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
GUI managers for Linux
Ive downloaded and sucessfully installed both mysqlgui, and mycc from the mysql.com site for some wierd reason I cant connect to the database with either of them I can with the text mysql client, and the text mysqladmin I have created a database, and granted rights to that database to a user. that user can log in using "mysql -u username -p" This is a Mandrake Linux 8.2 box running mysql 3.23.47 mysqlgui version 1.7.4 mycc version 0.8.2 alpha any hints much appreciated Rance _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FW: Does MySQL provide an access to system tables ? Are there any ?
Greetings: I'm a SYBASE DBA and very familiar with Sybase implementation of system catalog. (systables; syscolumns, etc) There is a lot of information that can be derived from these tables. MySQL, however, doesn't appear to have any system tables. When use command "describe" it shows the information some what similar to what Sybase's set of system tables would produce. The question is where does MySQL get this information. Do users have any access to it ? Also, can I write to that filed EXTRA (see the example below) ? I'm just trying to build a data dictionary based on the info MySQL's DESCRIBE command generates. Can anyone suggest a better way to create a data dictionary ? mysql> describe groupcode; +---+-+++---+--- --+ | Field | Type| Null | Key| Default | Extra | +---+-+++---+--- --+ | group_code| varchar(30) || PRI| | | | group_id | varchar(20) | YES| MUL| NULL | | :: Thank you very much for your time. Vadim Kulikov 877-428-3279 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FW: Does MySQL provide an access to system tables ? Are thereany ?
At 16:19 -0700 4/27/02, Vadim wrote: >Greetings: > >I'm a SYBASE DBA and very familiar with Sybase implementation of system >catalog. (systables; syscolumns, etc) There is a lot of information that >can be derived from these tables. MySQL, however, doesn't appear to have >any system tables. Right, there are none. > When use command "describe" it shows the information >some what similar to what Sybase's set of system tables would produce. >The question is where does MySQL get this information. From the .frm file. > Do users have any >access to it ? Yes, use the DESCRIBE statement. :-) (Or SHOW COLUMNS, which is synonymous.) > Also, can I write to that filed EXTRA (see the example >below) No. > ? I'm just trying to build a data dictionary based on the info >MySQL's DESCRIBE command generates. Can anyone suggest a better way to >create a data dictionary ? >mysql> describe groupcode; > > >+---+-+++---+--- >--+ >| Field | Type| Null | Key| Default | >Extra | >+---+-+++---+--- >--+ >| group_code| varchar(30) || PRI| | >| >| group_id | varchar(20) | YES| MUL| NULL | >| > >:: > >Thank you very much for your time. > >Vadim Kulikov >877-428-3279 >[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ANNOUNCE: mytop 1.0 is released...
I have just released version 1.0 of mytop, a "top"-like command for monitoring your MySQL server. The mytop home page (with screenshot, docs, mailing list) is now here: http://jeremy.zawodny.com/mysql/mytop/ which is different than the old location, which also points to that URL now. Changes in version 1.0 -- Provided a fix for cases when we try remove the domain name from the display even if it is actually an IP address. Fixed a ton of formatting bugs and "use of uninitialized value" errors. Adjusted column widths and headinds a bit to fit the common cases that I usually see. Added "Now/Sec" to the header, next to "Avg/Sec". The former is the "real-time" queries per second since the last mytop refresh, while the later is the queries per second since the server was last reset. Added the `o' key to toggle the sort order. Changed the `h' key to `H' for toggling the header. Added the `h' key to filter based on hostname. Changed the "Query Info" colum to "Query or State" because if there is no query running (like when a slave is replicating off the master and you're watching the master), it'll show the state in that column. The real-time queries/sec are computed using Time::HiRes if it is available, making the numbers much more accurate. Added the `e' key to EXPLAIN a query. It works a lot like the `f' key to get the full SQL of a query. Also, if you're already looking at the full SQL (having pressed `f'), you can hit `e' to get it explained if you'd like. The web site has moved to: http://jeremy.zawodny.com/mysql/mytop/ Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 79 days, processed 2,068,381,093 queries (301/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Which Index is better ?
Hi, I wonder which index will be the most efficient for this query : SELECT numreponse FROM searchhardwarefr7 WHERE pseudo='joce' AND date>'2001-01-01 00:00:00'; I tested a compound index on (pseudo,date,numreponse), and explain return : mysql> EXPLAIN SELECT numreponse FROM searchhardwarefr7 WHERE pseudo='joce' AND date>'2001-01-01 00:00:00'; +---+---+---++-+--+- -+-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---+---+---++-+--+- -+-+ | searchhardwarefr7 | range | pseudo| pseudo | 43 | NULL |1 | where used; Using index | +---+---+---++-+--+- -+-+ 1 row in set (0.01 sec) But I also tested an index on (pseudo,numreponse,date), and explain return : mysql> EXPLAIN SELECT numreponse FROM searchhardwarefr7 WHERE pseudo='joce' AND date>'2001-01-01 00:00:00'; +---+--+---+--+-+---+--- ---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+--- ---+-+ | searchhardwarefr7 | ref | pseudo_2 | pseudo_2 | 35 | const | 1 | where used; Using index | +---+--+---+--+-+---+--- ---+-+ 1 row in set (0.00 sec) It seems ref type is better than range type, but I was thinking the (pseudo,date,numreponse) index was better the second one, as it matched exactly the query (or perhaps explain simply doesn't write the first ref match made before the range match). It's why I need in this case some clarification about indexes :) Thank you in advance, Jocelyn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Compressed MASTER-SLAVE replication
On Sat, Apr 27, 2002 at 07:40:51PM -, [EMAIL PROTECTED] wrote: > Hi Jeremy, > > you wrote: > > > If you send the traffic thru an ecnrypted tunnel, such as with SSH, > > you'll get decent compression and security to boot! :-) > > Yep, that's a bright idea in fact... I wanted to make sure that we didn't > already have that feature built-in. > > Now I am just curious to know if the SSL-options for replication (added in > mysql 4) will also take care of compression. I would guess that yes, they > should -- but I am not sure at all :) They will, yes. I haven't tried 'em with replication yet. But I suspect it'll work. :-) Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 79 days, processed 2,068,045,145 queries (301/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL, mm.MySQL, and SQL Problems
> Message-ID: <[EMAIL PROTECTED]> > Date: Sat, 27 Apr 2002 18:28:57 +0900 > From: Andy Clark <[EMAIL PROTECTED]> > MIME-Version: 1.0 > To: MySQL <[EMAIL PROTECTED]> > Subject: MySQL, mm.MySQL, and SQL Problems > Content-Type: text/plain; charset=us-ascii > Content-Transfer-Encoding: 7bit > So I'm jumping back into database stuff after a long absence. > (I guess you could say that I was never really *in* databases > to begin with. Anyway...) I'm working with various databases > and have settled on MySQL but am encountering a variety of > problems that I hope the MySQL community can help me solve. First off, let me say "Thanks" for the work you've done with the XML Parser. I used it heavily on a project last year and it performed admirably. > First, a comment. In dealing with a variety of databases, I > was surprised to find the apparent lack of direct support for > international characters. So I've basically resolved to insert > all of my international text as UTF-8 encoded BLOBs. Is this > the general approach people use for this problem? Hmm. MM.MySQL will do this for you. Seeing your next comment leads me to believe that you've found yourself a very old version of MM.MySQL that doesn't do this or PreparedStatements. Both character set translation and PreparedStatements have been features of MM.MySQL for more than a couple of years now :) > Second, I'm programming in Java and want to use JDBC. So I > downloaded mm.MySQL but was dismayed to find that it doesn't > support prepared statements. Is there any particular reason > why? See my above comment. PreparedStatement support has been available for quite some time in MM.MySQL. > I wanted to modify the source for mm.MySQL to convert to and > from UTF-8 streams when calling PreparedStatement#setString > and ResultSet#getString so that I can write my program as if > the underlying database *did* support international chars. > But without support for prepared statements, I don't know if > I want to go through the trouble. Don't go through the trouble :) Download version 2.0.13 from http://mmmysql.sourceforge.net/ Un-jar it. Look at the README, especially the part about the URL paremeters "useUnicode" and "characterEncoding". By setting these in your JDBC URL, the driver will automagically do the right thing. -Mark (The "MM" behind MM.MySQL) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Character ² 2 m² meter to square hangs odbc insert into varchar VB 6 SP5 and access 2000/2002 XP
Character ² 2 m² meter to square hangs odbc insert into varchar VB 6 SP5
and access 2000/2002 XP
Myodbc 2.50.37 hangs insert into varchar(16) values ('m²'):
Access 2002 SR1 myodbc (MYSQL 2.50.37)
Mysql on SuSE-linux 3.23.41
But m3 dosn't make any prob.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table corrupted after myisampack
Hi. It sounds as if you run myisampack/myisamchk while the server was running. This is not supported and has a high probability to corrupt tables (http://www.mysql.com/doc/R/e/Repair.html) Bye, Benjamin. On Fri, Apr 26, 2002 at 09:50:18PM -0400, [EMAIL PROTECTED] wrote: > Hi, > > I am running mysql alpha 4.0.2 on solaris 8. I had the following problem: > > - I have a table called "call_record" and it has 153501 records. The > call_record.MYD file is of size 72,605,973 bytes. I performed myisampack on > this table, which brings down the table size to 11,411,888. After > myisampack, I also performed myisamchk -rq on the same table. [...] > I dropped the table and recreated the table, then after performing > myisampack and myisamchk, my table is corrupted again. This happened to me > 3 times in a row and I wonder if this is a known problem or if I did not do > things correctly. > > Any help would be appreciated, > Ning -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: WHERE ignored
Hi. First a praticial hint to prevent further harm: I presume you can set an upper limit for how many rows should be returned (say 1000?). If there are more, complain and refuse to do anything in your script. Or also select the "sent" value and compare in your script that it is 0 before sending mail. I doubt you would really get a wrong value in the selected column, too. What you describe sounds like a corrupt index or something alike. It is not uncommon that _if_ an index is corrupted, you get far more or less results than expected. So check your tables. I don't think that getting too many rows for a hardcoded query is possible by an error in your script. But to be sure: From what you said, you seem to have the query log. Check that the log really contains what should be there for the time window in question. I.e. that the hardcoded query was sent undamaged to MySQL and so on. Bye, Benjamin. On Fri, Apr 26, 2002 at 11:04:00PM +0100, [EMAIL PROTECTED] wrote: > > Is there any chance at all that a field name is appearing in your > > WHERE clause? For example: > > > > UPDATE foo SET flag = 0 WHERE foo_id = foo_id; > > > > foo_id = foo_id of course matches all. > > No, the query is hard coded to "WHERE Sent = 0". > > > Not much room for that kind of error in this example, but > > perhaps in other scripts that deal with the table in question? > > No other scripts perform UPDATEs on that table, only INSERTs. But MySQL > returned the correct data at 8:55 and again at 9:05. It was only when it ran > at 9am that it appeared to ignore the WHERE. According to my binary log, the > Mail table wasn't changed at all during this time - so the results of all > three queries should have been identical. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Potential bug
Hi. On Fri, Apr 26, 2002 at 04:19:37PM -0400, [EMAIL PROTECTED] wrote: > I'm writing to you about a potential bug. Not that this is a public list for "users help users". > I am using (trying) the gui for managing the dbs, and it freezes if i > hit a keystroke while looking at a query result. Sorry, but this is a bit unspecific. Which GUI? There are several. Which version? And so on... Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump generates "create table" commands with wrong table name case.
Hello. To answer only a part: On Fri, Apr 26, 2002 at 03:26:15PM -0400, [EMAIL PROTECTED] wrote: [...] > Having loaded these tables, the mysql > monitor appears not to care about case: > > mysql> select ID from Maps; > ++ > | ID | > ++ > | 1 | > ++ > 1 row in set (0.00 sec) > > mysql> select ID from maps; > ++ > | ID | > ++ > | 1 | > ++ > 1 row in set (0.00 sec) This part is expected behaviour: http://www.mysql.com/doc/N/a/Name_case_sensitivity.html It may even affect your original problem. See lower_case_table_names. Anyhow, the behaviour of mysqldump you described is incosistent. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: a graph theory problem
Hello. On Fri, Apr 26, 2002 at 01:43:21AM +0200, [EMAIL PROTECTED] wrote: > Hello folks, > > here is a pretty original (I think) problem for your minds! :-) Well, to be true, not really. :-) > I'd like to store a graph in a MySQL database. By graph, I mean the > graph theory meaning (i.e., "a set of connections between pairs of > nodes, which (the connections) may have a direction and/or weight"), > not the meaning "plot of a value over time" or "graphical > representation of a function value's dependence on the function > argument". > > To give an example, say you have a table of products and you want to > store, for each product, which other products it is compatible with. > When querying, you would like to retrieve a table where you have > products in the row headings _and_ in the column headings, with > either 1's or 0's (for YES or NO) in the cells. So for example, you > would have printers, toner cartridges, mainboards and processors as > products. There would be two distinct compatibility matrices: > printers against toners, and mainboards against processors. Oh. I thought you had a more complex problem. Because you don't seem interested in more than one node and it's connections at a time, it is simply a many-to-many problem. [...] > For storing, there seem to be about three possibilities: > 1) A table with as many columns as there are rows. I don't know what > is the limit on the number of columns, but as there might be _lots_ > of rows, this doesn't seem like a very good idea. > 2) A table with a SET column (i.e. "node int, connections set") - this > might work for lots of rows but not lots of connections from one > node; also, it doesn't store the "weights" of the connections. > 3) A table with the columns: (node1 int, node2 int, connection int). > This is probably the most general way, but also the most space- > inefficient, I would guess. The answer from database theory is 3). You describe a many-to-many relationship and this is represented this way usually. 2) is a replacement for 3), which contradicts theory, and is only reasonable, if the set is small and doesn't change (which wouldn't be true in your case), in which case it simplifies the relationships and the queries. I think I would never use 1). For any new product you would have to change the table layout! I would start with 3), i.e. a many-to-many table like this (from head, not tested): CREATE TABLE compatible item INT NOT NULL, with INT NOT NULL, PRIMARY KEY (item1,item2) ); This presumes, there is a row only, when you know the items are compatible. Also, all items are expected to be listed in "item", i.e. you will have (A,B) and (B,A) in the table if A and B are compatible. That is partly redundant and brings a risk for inconsitency, but also is the more flexible approach (having only (A,B) makes sense in some cases) and makes the queries simpler. > For retrieving, AFAIK, SQL doesn't provide any way to transform rows > into columns, right? Not that I know of. > Which means that in case 3), I would have to do many queries and > transform the results in a procedural language, right? Why many queries? You are right, you have to reformat this in the application side, but this is the usual thing to do anyhow. To retrieve the whole matrix you would do SELECT i.id, i.name, w.name FROM item i, compatible c, item w WHERE i.id = c.item AND w.id = c.with ORDER BY i.name and then loop thorugh the result. A new row of your matrix starts when i.id changes (you could do without i.id and use i.name, but the way above also handles the case where two items have the same name for some reason). > I would like to pick any product and fetch the whole compatibility > matrix that contains it. If you want which products are compatible to an PIII you would do: SELECT w.name FROM item i, compatible c, item w WHERE i.name = 'Pentium III' AND c.item = i.id AND w.id = c.with; If you want the matrix for printers and toners this would be SELECT i.id, i.name, w.name FROM item i, compatible c, item w WHERE i.type = 'printer' AND w.type = 'toner' AND i.id = c.item AND w.id = c.with ORDER BY i.name, w.name Of course, it would be reasonable to have an index on (type, id) of item. :) > So, that's it. Any ideas, comments, thoughts, questions? Am I trying > to solve something that is already solved? Does MySQL have "SELECT > MATRIX"? :-))) Well, I don't say that this will be the most reasonable approach to your problem (which you stated a bit general), but a reaonsable start and the usual approach. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lis
Re: Compressed MASTER-SLAVE replication
Hi Jeremy, you wrote: > If you send the traffic thru an ecnrypted tunnel, such as with SSH, > you'll get decent compression and security to boot! :-) Yep, that's a bright idea in fact... I wanted to make sure that we didn't already have that feature built-in. Now I am just curious to know if the SSL-options for replication (added in mysql 4) will also take care of compression. I would guess that yes, they should -- but I am not sure at all :) Thanks Davide G. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Multiple Sum Queries
At 14:24 -0500 4/27/02, [EMAIL PROTECTED] wrote: >I have a table full of cell phone data with three important columns: >ServiceNumber, ratingPeriod (1,2 or 3) and minutes. I want to run a >query grouped by serviceNumber that gives me the sum of minutes per >rating period for each of the numbers in the other cloumns. So the >query result will look like this: > >serviceNumber | SUM(of minutes where ratingPeriod =1) | SUM(of >minutes where ratingPeriod =2) | SUM(of minutes where ratingPeriod >=3) > >I was able to do this with other tools using "Transform" and "Pivot" >commands which are not present in MySQL. > >Is this particular query possible in MySQL? If it is not possible >with a query perhaps it would be possible to build a table in memory >and add the three columns to it individually? SELECT serviceNumber, SUM(IF(ratingPeriod=1,minutes,0)), SUM(IF(ratingPeriod=2,minutes,0)),SUM(IF(ratingPeriod=3,minutes,0)) FROM tbl_name GROUP BY serviceNumber; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multiple Sum Queries
I have a table full of cell phone data with three important columns: ServiceNumber, ratingPeriod (1,2 or 3) and minutes. I want to run a query grouped by serviceNumber that gives me the sum of minutes per rating period for each of the numbers in the other cloumns. So the query result will look like this: serviceNumber | SUM(of minutes where ratingPeriod =1) | SUM(of minutes where ratingPeriod =2) | SUM(of minutes where ratingPeriod =3) I was able to do this with other tools using "Transform" and "Pivot" commands which are not present in MySQL. Is this particular query possible in MySQL? If it is not possible with a query perhaps it would be possible to build a table in memory and add the three columns to it individually? TIA, Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How does DISTINCT really work ?
Hi! > "Fournier" == Fournier Jocelyn <[Presence-PC]" <[EMAIL PROTECTED]>> writes: Fournier> Hi, Fournier> So I assume in this case the only way to have distinct results is to use Fournier> "GROUP BY topic" clause ? If you do this, you can't still use 'ORDER BY date' as 'date' is not part of the GROUP BY columns. >> > mysql> SELECT DISTINCT topic FROM >> > forum.searchmainpresencepc4,forum.searchjoinpresencepc4 WHERE >> > searchmainpresencepc4.numreponse=searchjoinpresencepc4.numreponse AND >> > (mot='les') ORDER BY date DESC LIMIT 0,40; The main problem is what you try to do is not legal in SQL. What you probably want to do is the following: SELECT DISTINCT topic,max(date) as d FROM forum.searchmainpresencepc4,forum.searchjoinpresencepc4 WHERE searchmainpresencepc4.numreponse=searchjoinpresencepc4.numreponse AND (mot='les') group by topic ORDER BY d DESC LIMIT 0,40; And just ignore the d column from the result Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL & PervasiveSQL
Hello. On Thu, Apr 25, 2002 at 04:09:57PM -0400, [EMAIL PROTECTED] wrote: > Hi, > Looking for advice on integrating database applications. > > We are building a web app in php. Generally I use MySQL for everything. > But this app needs to integrate with, or at least exchange data with an > accounting package running PervasiveSQL db. > > I think I can access Pervasive with php's built-in ODBC functionality. > > Questions: > > 1. Does anyone know if both ODBC connections and mysql connections can > be maintained in the same script? (Be polite if you feel this is > strictly a PHP question!) Why? You even acknoledged that you know that it off-topic. It cannot be answered without PHP knowledge. While one needs no MySQL knowledge to answer it (only knowledge of PHP's MySQL API). That said, from what I know of PHP, I see no reason why it shouldn't be possible to use both in the same script. > 2. I am currently torn between two approaches: > a) build the web app with mysql and have the headache of > synchronization with Pervasive(any good docs on that would be > appreciated!) > > OR > > b) build the app with Pervasive and have the security issues of > a web app directly accessing the internal db The answer depends on what the web application is supposed to do. If you just want to accept order and write them to the internal db, you do not need any db on the server. Write the order in some file, which you let copy once per day or so from your internal server and update the db there. No security issues involved. If you want something like e.g. Amazon's order system, there is IMHO no reasonable way to not connect to the database with the main data, i.e. your internal db. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with mysql installation
Should be able to find the right rpm's from your Linux distribution's website. Just a guess here but www.redhat.com. Mike sqp,mysql Balteo wrote: > Hello, > > I am trying to install mysql from RPMs. My installation guide requires: > > -Mysql server > -Mysql shared > -Mysql client > > However those files are not present on mysql's website. Where do I find > them?? > > Is the file available at Mysql.com sufficient for a server + client > installation? > > Thanks in advance, > > Balteo. > > > - > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > -- Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Which would be a faster query:
Hi. Unfortunately you posted only one of the queries, so one cannot weight in their complexity. First, normally forums get at least about 10 times more read requests than new posts. In my experience posting speed is not a limiting factor. That said, I am going to answer your original question. I am not sure, why you need this GROUP BY query at all (and only for posts)? Anyhow, the GROUP BY normally will be significantly slower than running a simple select and update. Regardless, you should always benchmark this yourself, as those statements always depend on table sizes, indexes and so on. Bye, Benjamin. On Thu, Apr 25, 2002 at 12:41:02PM -0400, [EMAIL PROTECTED] wrote: > I am building a forum as part of a site, and the forum is broken into three > tables, a forum table, a thread table, and a post table. > All tables have indexes for common SELECT statements, to increase speed. > > Example- > A user posts a new thread: One method would be 3 queries to insert/update > (insert into post, insert into thread, update forum) and 1 simpler query to > read (SELECT <>), and in another method, 2 queries to > insert/update (insert into thread, insert into post) and one slightly more > complex query to read, using a query similar to this: SELECT COUNT(*) AS > totalreplies, postid, threadid, userid, username, posteddate FROM post GROUP > BY posteddate ORDER BY posteddate DESC > > At smaller volumes the query times are all very similar and not very > substantial. I'm curious as to what the fastest method would be, at higher > volumes. > > Thanks in advance for any thoughts, ideas or info. > > Mike Grabski -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Sql SELECT statement to get all dates between 7:00am yesterday an d 7:00am today
Hello. You have given to few information to give a specific answer. Please include the output of "DESC your_table" in your mail next time. Most people probably solve that by determining the date in the application part and just inserting it in the SQL query. The following is an SQL-only solution (not necessarily "pretty"). Presuming you have some timestamp column (called "ts" afterwards) containing the time. SELECT whatever FROM your_table WHERE ts BETWEEN DATE_FORMAT( NOW(), "%Y%m%d07" ) AND DATE_FORMAT( NOW() - INTERVAL 1 DAY, "%Y%m%d0700" ) (this needs a recent 3.23 version, but you get the idea) Bye, Philemon. On Thu, Apr 25, 2002 at 08:22:48AM -0500, [EMAIL PROTECTED] wrote: > Hi Everyone, > > I am very new to database programming, and am working on an application > where I need to select all records between yesterday at 7:00am and today at > 7:00am. Obviously this can be run at any time or day. This has to be a very > common sql SELECT statement, but for the life of me I can't find anything. > I'm either not doing the right google search or looking in the right places. > > I apologize if this is the wrong place to post this message. Any direction > either towards the SELECT statement answer or where to look would be much > appreciated. Thanks in advance. > > Don Whitlow > Quad/Graphics, Inc. > Manager - Enterprise Computing > [EMAIL PROTECTED] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to optimize this sql ?
Hello. On Thu, Apr 25, 2002 at 01:49:39PM +0200, [EMAIL PROTECTED] wrote: [...] > mysql> show index from cpu; > >+---++--+--+-+---+-+--++-+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | >Cardinality | Sub_part | Packed | Comment | > >+---++--+--+-+---+-+--++-+ > | cpu | 1 | timecode |1 | timecode| A | >4199933 | NULL | NULL | | > | cpu | 1 | timecode |2 | systemid| A | >4199933 | NULL | NULL | | > >+---++--+--+-+---+-+--++-+ > 2 rows in set (0.13 sec) Are the pairs (timecode,systemid) unique? If so, tell MySQL so. > ...and I execute a query like this (here with explain first) : > > mysql> explain SELECT substring_index(elisa.timecode,':',1), > avg(elisa.usertime + elisa.systemtime) AS elisa, > avg(asuncion10.usertime + asuncion10.systemtime) AS asuncion10, > FROM cpu AS elisa, cpu AS asuncion10, cpu AS asuncion20 > WHERE elisa.systemid='44' > AND asuncion10.systemid='47' > AND substring_index(elisa.timecode,':',1) = >substring_index(asuncion10.timecode,':',1) > AND elisa.timecode > now() - INTERVAL 7 DAY > AND asuncion10.timecode > now() - INTERVAL 7 DAY > GROUP BY substring_index(elisa.timecode,':',1) > ORDER BY substring_index(elisa.timecode,':',1); > >++---+---+--+-+--++-+ > | table | type | possible_keys | key | key_len | ref | rows | Extra > | > >++---+---+--+-+--++-+ > | elisa | range | timecode | timecode | 8 | NULL | 612892 | where >used; Using temporary | > | asuncion10 | range | timecode | timecode | 8 | NULL | 612892 | where >used | > >++---+---+--+-+--++-+ > 2 rows in set (0.02 sec) Sorry, but the query and the explain don't fit together. Where is "cpu AS asuncion20"? I presume that the part in the SELECT simply is left by accident. In this case, is MySQL's guess that it has to inspect about 612892 correct? It seems a bit high (more than 1/10th of the rows of the table?) If not, try running ANALYZE TABLE. If it is correct, the query will run forever, because you are going to inspect 612892*612892 rows, including calling substring_index twice that much. Another thing: If you need that substring stuff regularly, your table is not normalized enough and you should split timecode. This would would enable MySQL to use indexes to solve the substring_index(elisa.timecode,':',1) = substring_index(asuncion10.timecode,':',1) part (which wouldn't call substring_index anymore) > This query takes quite a long time to execute, and I'm sure it can > be done more elegant and fasterjust don't know how. > > Anyone ? > > It's MySQL 3.23.49 with InnoDB tables. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re[2]: Cross querying databases
Hello On Thu, Apr 25, 2002 at 03:29:34AM +0100, [EMAIL PROTECTED] wrote: > > Guten Tag Donna Robinson, > Hi 2u2 sir! > and god bless your cotton socks. > > > i hope this will help you.. > ... > it didn't but it gave me enough of a clue so that this did: > select dancedetails.coupletypes.* > from dancedetails.coupletypes > left join dancedata2.couples > on dancedetails.coupletypes.name=dancedata2.couples.name > where dancedata2.couples.name is null; > > So tell me - why does a left join work where an inner join refused to? > (have only been sql-ing for about 2 wks) First, you might be served better by reading a good tutorial or book (see the manual for suggestions), than to ask for basic principles of SQL on the mailing list. It may become more obvious, if you try the query without WHERE clause at all: SELECT ddetails.name, ddata.name FROM dancedetails.coupletypes AS ddetails, dancedata2.couples AS ddata This will result in a Cartesian product (cross product) of the two specified tables, i.e. every field of the "left" table is paired with every field of the "right" table. Now, if you add something like "WHERE ddetails.name = ddata.name" you get only the rows from that set, which fullfil the condition, that their names are equal. If you try "WHERE ddetails.name != ddata.name", you get all rows for which the names in the pair are different. So you got exactly what you asked for, you see? But from your earlier explanations you didn't want to ask "in which rows are the names different", but "which name exists only in one of the tables". A LEFT JOIN will give all rows like a normal join (the ","), but also all rows of the left table, which have no equivalent in the right one, with the values for the right one filled with NULL values. Therefore "WHERE dancedata2.couples.name IS NULL" will give you only those latter mentioned rows of the result set, which have no pair equivalent value in the right table. This is probably still not what you want: It won't give you values, which are only in the right table, but not in the left one. So a LEFT JOIN is only the correct solution for your question, if you know beforehand, that only the left table has "orphaned" values. What you need is a "FULL OUTER JOIN". Unfortunately this is not supported yet by MySQL. So you would need to run two queries, a LEFT JOIN and a RIGHT JOIN (or a LEFT JOIN with tables swapped) to get all results. Regards, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Determining character set from table data
Hello, I am wondering if there is any way to determine the character set used for data in a table. For example, a single database has 10 tables, six with 'latin1' characters, two with 'cp1251' characters, and two with 'greek' characters. The character_set variable is set to 'latin1' on this database because most of the data is in the latin1 character set. However, the client programs will want to display the data from the non-latin1 tables correctly when they do a SQL query. Is there a way to determine the type of character used for data in a table apart from the character_set variable? Thanks, Matt Culbreth - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Why does MAX(this_col) when this_col has index?
On Sat, Apr 27, 2002 at 08:25:26AM -0400, Richard F. Rebel wrote: > > Hello, > > Perhaps I don't understand indexes but I was wondering how to improve teh > performance of a query on an indexed column that uses MAX(). > > mysql> explain select max(master_mtime) from redirects where uri_scheme = > 'http'; > >+---+--+-++-+---+-++| > table | type | possible_keys | key| key_len > | ref | rows| Extra | > >+---+--+-++-+---+-++| > redirects | ref | full_uri_idx,uri_scheme_idx | uri_scheme_idx | 16 > | const | 7694050 | where used | > >+---+--+-++-+---+-++1 > row in set (0.01 sec) > > There is an index on master_mtime but it's not listed by explain??? Indexes typically help out in the WHERE clause. You'll notice that MySQL selected the uri_scheme_idx index because that's the column on which you've asked it to filter. You might get better performance out of a compund index on (uri_scheme, master_time), but you'll have to be the judge of that. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 79 days, processed 2,065,294,319 queries (302/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Limits of mySQL
On Sat, Apr 27, 2002 at 05:15:10PM +1200, Sam Minnee wrote:
>
> I've been asked to put together a very large (well, it's large to
> me) database, and while mySQL is great for my current uses, I
> haven't had experience with stuff of this scale.
>
> The database will have about 88 tables, with up to 100 fields per
> table. There is a _lot_ of interlinking among the tables, and each
> "transaction" will have about 10k of data. By the end of the first
> year, almost 500,000 transactions will be in the database.
> Unfortunately, I can't be more specific, as another party is
> designing the database specification, which I don't have a copy of
> yet.
No red flags so far.
> Now, if I were to use mySQL I would want to use the transactional
> version. I haven't had any experience with this, how does its
> performance and reliability compare (obviously the transactions are
> a + to its reliability).
It's still as fast and reliable as non-transactional MySQL.
> My question is: Will mySQL be able to handle this amount /
> complexity of data well, and how much better would, say, Oracle or
> even MS SQL Server 2000 be?
MySQL will cope just fine. It'll probably be faster than the
alternatives.
> What about PostgreSQL? PostgreSQLs relationships, constraints,
> views, and stored procedures would be beneficial, but not at the
> cost of of suitable performance.
InnoDB provides referential integrity constraints ("relationships"),
so that's a non-issue. As for views and stored procedures, it's up to
you. If you need 'em, try PostgreSQL. MySQL won't have them for a
while yet.
All the databases you mentioned will work for you app. It comes down
to finding the one that has all the features you need at the lowest
price.
Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3.23.47-max: up 79 days, processed 2,065,226,324 queries (302/sec. avg)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Keeping track of db structure changes
On Sat, Apr 27, 2002 at 10:42:47AM +0200, Jens Vonderheide wrote: > Hi, > > is there any way to have MySQL automatically keep track of any changes > I do with ALTER TABLE and later spit out the changes again? Not really, no. > I use one database (well, actually several, but that's not important > here) for development and one database for the production > system. Now, whenever I change the db structure, I have to jot it > down in a text file and later copy-paste it to the production db. Of > course, as anything that is done manually, this can (and already > has) lead to problems if the text file is not kept in sync with the > changes in the db. It's really not all that different that source code. For each application I build, I tend to put the source code in our CVS repository long with the $foo.sql file(s) to reconstruct the tables. Just as there's nothing preventing me from deploying a new release of an application before checking all my changes into CVS, the same is true of your database structures. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 79 days, processed 2,065,187,269 queries (302/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie: Help with count
you can use: select messagestate, count(*) from table where uid=1 group by messagestate; for uid=1, if you want to do the same for all users with one query you can use: select uid, messagestate, count(*) from table group by uid, messagestate; Regards, Ben Holness wrote: > Hi all, > > I have a table that has data that looks something like this (fixed font on): > > UserIDMessageID MessageState > 1 1 PENDING > 1 2 PENDING > 2 3 FAILED > 3 4 DELIVERED > 2 5 ACCEPTED > 2 6 DELIVERED > 1 7 DELIVERED > > and so on. > > I am trying to work out a query that for a given UserID will tell me how > many messages are in which state, without having knowledge of the possible > states. > > Using the above data, a query on UserID 1 would return: > PENDING 2 > DELIVERED 1 > > a query on UserID 2 would return: > FAILED1 > ACCEPTED 1 > DELIVERED 1 > > Is this possible and can anyone point me in the right direction to do it > please? I know how to get a count for all the pending messages for a > particular user ID (SELECT count(MessageID) WHERE UserID='1' AND > MessageState='PENDING'), but I am not sure how to get a new row for each > message type. > > Cheers, > > Ben -- George Chelidze - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Compressed MASTER-SLAVE replication
On Sat, Apr 27, 2002 at 04:28:27PM -, [EMAIL PROTECTED] wrote: > Hi there, > > I am wondering if there's a way of estabilishing a compressed link > between a master and slave mysql servers. I know that a compressed > client-server protocol exists. Do we have anything similar for > replication? When replicated data is mostly text, such an option > would save a lot of time, bandwidth and $$$ over not-so-fast links > :) If you send the traffic thru an ecnrypted tunnel, such as with SSH, you'll get decent compression and security to boot! :-) Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 79 days, processed 2,064,751,710 queries (302/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with mysql installation
> Hello, > > I am trying to install mysql from RPMs. My installation guide requires: > > -Mysql server > -Mysql shared > -Mysql client > > However those files are not present on mysql's website. Where do I find > them?? Where do you want to install .. on 1) Unix like OS or 2) windows or 3) any other? For 1 goto http://www.mysql.com/downloads/mysql-3.23.html and all RPM's are present under heading Linux Downloads For 2 server/client programs are all present in the .zip file on SAME site (under Windows Downloads) > > Is the file available at Mysql.com sufficient for a server + client > installation? > > Thanks in advance, > > Balteo. > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Compressed MASTER-SLAVE replication
Hi there, I am wondering if there's a way of estabilishing a compressed link between a master and slave mysql servers. I know that a compressed client-server protocol exists. Do we have anything similar for replication? When replicated data is mostly text, such an option would save a lot of time, bandwidth and $$$ over not-so-fast links :) Thanks Davide G. ASA srl - Italy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with User Account - access denied
I created and user, like below, using mysql on my Linux machine (Linux-Mandrake 8.1) to access a database called college: GRANT ALL ON college.* TO someuser BY "somepassword"; But I'm denied access even from localhost. I tried the following too: GRANT ALL ON college.* TO someuser@% BY "somepassword"; But MySQL gives me an error message. I need to be able to access the database, from anywhere, including localhost. Anyone can help me out? Thanks __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
read only
sql,query How to enable the table for read write again ? Thanks. -- Best regards, Ridwan Goldbase Technology http://www.pembukuan.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help with mysql installation
Hello, I am trying to install mysql from RPMs. My installation guide requires: -Mysql server -Mysql shared -Mysql client However those files are not present on mysql's website. Where do I find them?? Is the file available at Mysql.com sufficient for a server + client installation? Thanks in advance, Balteo. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Dynamic $table in SELECT?
Here is another question;-) Is there any ability to put tables dynamic in a query? I have to do a select * for many tables and it would be cool if I just do something like this select * from (select t from mytables). Thanks a lot, Jan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: WHERE ignored
> I think you better show us the script. This might help to figure out the
> problem.
Okay, here it is:
-- Start
#!/usr/local/bin/php -q
write('Mailer is locked - terminating');
exit;
}
// Create a lock file
$log->write('Locking mailer');
@touch($lock);
@chmod($lock, 0666);
// Internal mailer class
include ("$coredir/classes/mailer.class.php");
// Grab a database connections
$db = opendb();
$db2 = opendb();
// Query unsent email
$db->query("SELECT MailID, MailFrom, MailTo, Subject, Body, Headers FROM
Mail WHERE Sent = 0");
while ($db->next_record()) {
// Create a new email
$mail = new Mailer($db->field('MailFrom'), $db->field('MailTo'),
$db->field('Subject'), $db->field('Body'), $db->field('Headers'));
// Log event
$log->write('Sending mail # ' . $db->field('MailID'));
// Try to send
if ($mail->send()) {
// Mark as sent
$db2->query('UPDATE Mail SET Sent = 1 WHERE MailID = ' .
$db->field('MailID'));
$log->write('Mail # ' . $db->field('MailID') . ' sent ok');
} else {
// Something went wrong
$log->write('Mail # ' . $db->field('MailID') . ' FAILED');
}
}
// Remove the lock file
$log->write('Unlocking mailer');
@unlink($lock);
?>
-- End --
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: WHERE ignored
> -Oprindelig meddelelse- > Fra: Mike Hall [mailto:[EMAIL PROTECTED]] > Sendt: 27. april 2002 00:04 > No other scripts perform UPDATEs on that table, only INSERTs. But MySQL > returned the correct data at 8:55 and again at 9:05. It was only > when it ran > at 9am that it appeared to ignore the WHERE. According to my > binary log, the > Mail table wasn't changed at all during this time - so the results of all > three queries should have been identical. I think you better show us the script. This might help to figure out the problem. - Carsten - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Install problem!
Hello there. Some time ago I have installed MySQL and after a short period of time I uninstalled it. Now when I try to install it again the installshield makes a sound (like a warning) show a message box to quickly (it disappears right after) and dont install MySQL. Im using windowsxp professional. I Have tried to install version 3.23.49 and 4.0 alpha. ANyone can help me? Thanks in advance, Eduardo David - Email Enviado utilizando o serviço MegaMail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie: Help with count
Hi all, I have a table that has data that looks something like this (fixed font on): UserID MessageID MessageState 1 1 PENDING 1 2 PENDING 2 3 FAILED 3 4 DELIVERED 2 5 ACCEPTED 2 6 DELIVERED 1 7 DELIVERED and so on. I am trying to work out a query that for a given UserID will tell me how many messages are in which state, without having knowledge of the possible states. Using the above data, a query on UserID 1 would return: PENDING 2 DELIVERED 1 a query on UserID 2 would return: FAILED 1 ACCEPTED1 DELIVERED 1 Is this possible and can anyone point me in the right direction to do it please? I know how to get a count for all the pending messages for a particular user ID (SELECT count(MessageID) WHERE UserID='1' AND MessageState='PENDING'), but I am not sure how to get a new row for each message type. Cheers, Ben - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Uppercase first letter of each word
If you are using PHP, just use the ucwords function.
>From the PHP manual
ucwords(PHP 3>= 3.0.3, PHP 4 >= 4.0.0)
Uppercase the first character of each word in a string
string ucwords (string str)
Returns a string with the first character of each word in str capitalized,
if that character is alphabetic.
Example 1. ucwords() example
$foo = 'hello world!';
$foo = ucwords($foo); // Hello World!
$bar = 'HELLO WORLD!';
$bar = ucwords($bar); // HELLO WORLD!
$bar = ucwords(strtolower($bar)); // Hello World!
Note: The definition of a word is any string of characters that is
immediately after a whitespace
(These are: space, form-feed, newline, carriage return, horizontal tab, and
vertical tab).
I hope this helps.
Pat...
- Original Message -
From: "Peter Lovatt" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, April 26, 2002 8:24 AM
Subject: RE: Uppercase first letter of each word
> this is for php, if you are not using php then try something similar
>
> //use explode to create an array with one word in each item
> $sql_array = explode(" ", $sql_text)
>
> //loop thro the array
>
> while(list($val) = each($sql_array))
> {
> $val = ucfirst ($val);
> $new_sql_text .= $val ;
> }
>
> hth
>
> Peter
>
>
>
> ---
> Excellence in internet and open source software
> ---
> Sunmaia
> www.sunmaia.net
> [EMAIL PROTECTED]
> tel. 0121-242-1473
> ---
>
> > -Original Message-
> > From: Doug Bishop [mailto:[EMAIL PROTECTED]]
> > Sent: 26 April 2002 05:10
> > To: [EMAIL PROTECTED]
> > Subject: RE: Uppercase first letter of each word
> >
> >
> > I have no problem with figuring out how to punctuate a field that
> > contains a
> > single word, but how would I code a SQL query to proper-case each
> > word in a
> > field when the field contains, for instance, a title? I want to convert,
> > "HOW NOW BROWN COW" to, "How Now Brown Cow"
> >
> > Thanks,
> > Doug
> >
> >
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php (the manual)
> >http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Why does MAX(this_col) when this_col has index?
Hello, Perhaps I don't understand indexes but I was wondering how to improve teh performance of a query on an indexed column that uses MAX(). mysql> explain select max(master_mtime) from redirects where uri_scheme = 'http'; +---+--+-++-+---+-++| + table | type | possible_keys | key| key_len | ref | rows| Extra | +---+--+-++-+---+-++| + redirects | ref | full_uri_idx,uri_scheme_idx | uri_scheme_idx | 16 | const | 7694050 | where used | +---+--+-++-+---+-++1 + row in set (0.01 sec) There is an index on master_mtime but it's not listed by explain??? Perhaps MAX() uses the sort buffer? If so, how can I work out what to increase the sort buffer to, aside from guessing. Thanks for any help Richard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Limits of mySQL
On Sat, 27 Apr 2002, Sam Minnee wrote: > I've been asked to put together a very large (well, it's large to me) > database, and while mySQL is great for my current uses, I haven't had > experience with stuff of this scale. > > The database will have about 88 tables, with up to 100 fields per table. > There is a _lot_ of interlinking among the tables, and each "transaction" > will have about 10k of data. By the end of the first year, almost 500,000 > transactions will be in the database. Unfortunately, I can't be more > specific, as another party is designing the database specification, which I > don't have a copy of yet. > > Now, if I were to use mySQL I would want to use the transactional version. > I haven't had any experience with this, how does its performance and > reliability compare (obviously the transactions are a + to its reliability). > > My question is: Will mySQL be able to handle this amount / complexity of > data well, and how much better would, say, Oracle or even MS SQL Server 2000 > be? What about PostgreSQL? PostgreSQLs relationships, constraints, views, > and stored procedures would be beneficial, but not at the cost of of > suitable performance. > > It would be much appreciated if someone with more experience developing > databases of this scale could give me some advice on the pros and cons of > each platform. I'm not a suitable person to compare MySQL against other databases but MySQL will cope with this size of database if the tables are properly indexed and your queries optomised. Here our main database has over 90 tables and several of our tablse hold about the amount of data your'll acrue in year one (with many more rows). None of our tables have that many fields the most complex table has 50 fields but I don't think 100 rows will effect MySQL performance significantly. Several of our tables have more that 10k per row and work just fine. It sounds as though your tables will be skirting close to the 4GB file limit on most standard Unixes so be sure to enable large file support (or use a raw partition for innodb). We're only using innodb on small projects so far but it seems to perform well although we have had some issues with it's interaction with the PHP scripting languages persitent connections. HTH Nigel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Uppercase first letter of each word
Hi,
A function like initcap in other databases and programming languages in
mysql would be
much desired in such situations.
You may try this ugly code.
select ltrim(replace(replace(replace...(concat(' ',lower(strcolumn)),' a','
A'),' b',' B' ),' c',' C'), ...,' z',' Z')) from tablename;
Anvar.
At 11:10 PM 25/04/2002 -0500, you wrote:
>I have no problem with figuring out how to punctuate a field that contains a
>single word, but how would I code a SQL query to proper-case each word in a
>field when the field contains, for instance, a title? I want to convert,
>"HOW NOW BROWN COW" to, "How Now Brown Cow"
>
>Thanks,
>Doug
>
>
>-
>Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Stuck Some More
Ian, Saturday, April 27, 2002, 2:31:28 AM, you wrote: IP> Mysql has not been used on my server before, and I am the only user who has IP> ssh access to the system, (any mysqld processes are mine) and have been IP> trying to get mysql set up and working properly. IP> Currenlty I have two mysqld processes idle on my system IP> 5404 p2- I 0:00.00 (mysqld) IP> 15488 p4- I 0:00.00 (mysqld) IP> I have tried to end these (kill -s HUP 5404 / kill -s HUP 15488) - but this IP> does not have any affect. IP> I have tried to start the mysql server (currently it's not running | ERROR IP> 2002: Can't connect to local MySQL server ), using the safe_mysqld& IP> command - with this result... IP> [1] 19882 IP> user_obscured:/# Starting mysqld demon with databases from /usr/local/var IP> nohup IP> /nfs/usr/libexec/mysqld --basedir=/usr/local --datadir=/usr/local/var --port IP> =30100 & IP> mysqld demon ended IP> (enter) IP> [1]+ Donesafe_mysqld IP> and mysql does not restart. IP> Can anyone explain what may be going on here? IP> How can I clear the mysqld processes, and restart mysql so that I can use IP> it? Ian, show me the contents of your .err file (the last 20-40 rows) IP> Ian -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: What next
Ian, Saturday, April 27, 2002, 1:42:46 AM, you wrote: IP> I was able to start the mysql server using this suugested by Egor Egor >> safe_mysqld& IP> [1] 15476 IP> user_obscured:/# Starting mysqld demon with databases from /usr/local/var IP> nohup IP> /nfs/usr/libexec/mysqld --basedir=/usr/local --datadir=/usr/local/var --port IP> =30100 & IP> but after this response, the ssh session 'just sits there' - seems as if IP> it's waiting for further input - is ther a way to exit ther session - or is IP> this what I should / shouldn't be doing at this point? press "Enter" :) Execute: ps ax | grep mysql Is there MySQL in the process list? IP> Ian -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL, mm.MySQL, and SQL Problems
So I'm jumping back into database stuff after a long absence. (I guess you could say that I was never really *in* databases to begin with. Anyway...) I'm working with various databases and have settled on MySQL but am encountering a variety of problems that I hope the MySQL community can help me solve. First, a comment. In dealing with a variety of databases, I was surprised to find the apparent lack of direct support for international characters. So I've basically resolved to insert all of my international text as UTF-8 encoded BLOBs. Is this the general approach people use for this problem? Second, I'm programming in Java and want to use JDBC. So I downloaded mm.MySQL but was dismayed to find that it doesn't support prepared statements. Is there any particular reason why? I wanted to modify the source for mm.MySQL to convert to and from UTF-8 streams when calling PreparedStatement#setString and ResultSet#getString so that I can write my program as if the underlying database *did* support international chars. But without support for prepared statements, I don't know if I want to go through the trouble. Next, I was wondering why multiple selects don't work in MySQL. For example: "SELECT * FROM a WHERE value IN (SELECT column FROM b);". Last, I have a particular problem related to SQL that I want to solve. This is not particularly related to MySQL but I am using MySQL as the database so any solution would have to be within the limits of what MySQL supports. Here's the idea: I have a table for text content that may be translated into various languages. Then I have a table for user preferences that specify which languages the user wants (and in what order). Lastly I have a mapping table from language codes to their default language code. Make sense so far? If not, here is the description of the three tables: mysql> describe content; +---++--+-+-++ | Field | Type | Null | Key | Default | Extra | +---++--+-+-++ | id| int(11)| | PRI | NULL| auto_increment | | resid | int(11)| | | 0 || | lang | varchar(5) | | | || | text | blob | | | || +---++--+-+-++ mysql> describe prefs; +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | user | varchar(50) | | | | | | lang | varchar(5) | | | | | | priority | tinyint(4) | | | 1 | | +--+-+--+-+-+---+ mysql> describe langs; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | code| char(5) | | PRI | | | | defcode | char(5) | | | | | +-+-+--+-+-+---+ The last table, "langs", is used so that if a user's pref is to read US English (en-us) and there is no content in that language *but* there is one in Default English (en), then they are returned that resource. Here's a sampling of the data in these three tables: mysql> select * from content; ++---+---+--+ | id | resid | lang | text | ++---+---+--+ | 1 | 1 | en-us | Color| | 2 | 1 | en-gb | Colour | | 3 | 1 | ja| IRO | | 4 | 2 | en| Mountain | | 5 | 2 | ja| YAMA | ++---+---+--+ mysql> select * from prefs; +---+---+--+ | user | lang | priority | +---+---+--+ | andyc | en-us |1 | | andyc | ja|2 | +---+---+--+ mysql> select * from langs; +---+-+ | code | defcode | +---+-+ | en| en-us | | en-us | en-us | | en-gb | en-us | | ja| ja | +---+-+ As can be seen from the "prefs" table, I (being "andyc") prefer to retrieve content in US English but can also read Japanese. Now if I only wanted the content that matched my number one priority, then it would be easy. However... I want to retrieve the content from my number one preference *and* also know that the resource is available in my other language preference(s). The following select statement returns the information that I want but there are a few problems. First, it returns all of the information which is wasteful since I only want to display the content from the first hit. Second, I'm sure that crossing three tables is *not* a good idea in general. But like I said, I'm kinda new to SQL so I don't know too much about JOINs, etc. Anyway, here's the query and the result: mysql> select c.resid,c.lang,c.text,p.priority -> f
RE: Scheduling with MySQL
> However, I would suggest, if you have control of the search > tools, that you simply put in an extra timestamp field called > "hideuntil" into your database, and add conditions into your > searches that drop rows for which hideuntil is not null and is > after the current time. This is a great idea and I do have control over the search tool. The search tool is a static SQL query, so I think (without testing), I will need something like: SELECT * from Messages WHERE hideuntil >= (NOW()+0) hideuntil is a timestamp(14) and is set so that it will never be null. Because it is a timestamp, if it is left blank, it will default to the current time/date. I will have a play on the system on Monday, but that seems about right to me. Cheers, Ben - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Keeping track of db structure changes
Hi, is there any way to have MySQL automatically keep track of any changes I do with ALTER TABLE and later spit out the changes again? I use one database (well, actually several, but that's not important here) for development and one database for the production system. Now, whenever I change the db structure, I have to jot it down in a text file and later copy-paste it to the production db. Of course, as anything that is done manually, this can (and already has) lead to problems if the text file is not kept in sync with the changes in the db. So what I'm looking for is something like this: ALTER TABLE tab1 ADD COLUMN acol int; ALTER TABLE tab2 ADD INDEX (anothercol); DISPLAY STRUCTURE CHANGES; RESET CHANGELOG; where of course the last two commands don't exist in MySQL. Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
