Re: mysql 4.0.15 and latest redhat enterprise AS Beta - segmention fault
Thomas, the current mysqld-standard does not work with the Red Hat AS beta (Taroon). We are looking into solving this issue. The problem seems to be connected to statical linking. You can try mysqld-max, as it is dynamically linked. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Paul DuBois [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, October 05, 2003 12:58 AM Subject: Re: mysql 4.0.15 and latest redhat enterprise AS Beta - segmention fault At 12:01 +0200 10/3/03, Thomas Gusenleitner wrote: HI List! mysql 4.0.15 won't start on the latest beta of the redhat enterpise AS. (will be released this month) i used the std. rpms for linux x86. i get the following error: Starting mysqld daemon with databases from /var/lib/mysql /usr/bin/mysqld_safe: line 339: 14874 Speicherzugriffsfehler $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 031003 10:42:17 mysqld ended Did you look at the error log? What did it say? i've compiled the SRPM package - everything ok. bye, thomas -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
change the *user* table OR simply use GRANT query?
Greetings, I'm so curious about this: If a new MySQL database account need to be created, do you insert the account info to the *user* table of the *mysql* database OR do you grant the new account by the GRANT query? What are the differences andn which one do you prefer? (I got some trouble when I tried to use the insert, don't know why, just doesn't work. It seems that the GRANT query is the easiest way to achieve that and works perfectly without issuing the FLUSH PRIVILEGES statement.) cheers, feng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL garbles some UTF-character such as capital swedish oe or cyrillic soft sign
Description: The UTF-8 encoding for capital swedish characters ae aa and oe doesn't work. Some cyrillic characters doesn't work either. A lot of other utf-encoded characters work so the settings are not wrong. It seems like mysql looses some information when it stores these characters. At first I thought it was something wrong with the Java JDBC-driver but when I got the exact same problem using the mysql-terminal client I concluded there must be something wrong with the server. How-To-Repeat: Set the server in UTF-8 mode. Try to insert Orebro (with the correct spelling) in a table and try to retrieve it back. It doesn't work. The second byte in the two byte sequence representing oe changes and makes the whole character unreadable. I'm sending an attachment in a separate e-mail (jpg-screenshots) Fix: Insert orebro with a non-capital oe in the beginning and it will work. It's not quite allright though since some cyrillic characters suffers the same problem. Submitter-Id: submitter ID Originator:[EMAIL PROTECTED] Organization: pi.se AB Karlavagen 8 Stockholm MySQL support: [none | licence | email support | extended email support ] Synopsis: UTF-8 representation problem with certain characters Severity: non-critical Priority: medium Category: mysql Class: sw-bug Release: mysql-4.1.0-alpha-standard (Official MySQL-standard binary) Server: /usr/local/mysql/bin/mysqladmin Ver 8.40 Distrib 4.1.0-alpha, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.1.0-alpha-standard-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 15 hours 58 min 0 sec Threads: 4 Questions: 22230 Slow queries: 0 Opens: 28 Flush tables: 1 Open tables: 22 Queries per second avg: 0.387 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux paros.tourmatch.se 2.4.20 #3 SMP ons maj 7 13:25:44 CEST 2003 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs gcc version 2.95.4 20011002 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Aug 11 21:01 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1153784 Apr 8 15:01 /lib/libc-2.2.5.so -rw-r--r--1 root root 2391002 Apr 8 15:02 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 8 15:02 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Aug 16 17:02 /usr/lib/libc-client.a - c-client.a lrwxrwxrwx1 root root 19 Aug 16 17:02 /usr/lib/libc-client.so - libc-client.so.2001 -rw-r--r--1 root root 716080 Jan 13 2002 /usr/lib/libc-client.so.2001 Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL garbles some UTF-characters such as
Title: Message capital and (cyrillic soft-sign).
Re: slow performance with large or list in where
Your problem is that MySQL don't use indeces in selects with 'or' (See MySQL manual). But i didn't have understand what's your problem executing individual selects... You don't need to open parallel connections. Put the UPDATE commands in a file, and execute them in mysql prompt like this: mysql source fileName; (for help, type 'help' in mysql prompt) Is this your problem? ;) Alexis Quoting Marc Slemko [EMAIL PROTECTED]: If I do a query such as: SELECT * from foo where fooid = 10 or fooid = 20 or fooid = 03 ... with a total of around 1900 or fooid = parts on a given table with 500k rows, it takes about four times longer than doing 1900 separate queries in the form: SELECT * from foo where fooid = 10 fooid is the primary key. My real goal is to do updates in a similar way, they have the same issue. And my real goal is actually doing the same thing where instead of fooid=10 I have a multicolumn primary key and use (keycol1=10 and keycol2=2 and ...) My examples here are the simplest case I can find that exhibit the issue. Explain plan on the select shows: table typepossible_keys key key_len ref rowsExtra adsummary range PRIMARY PRIMARY 31 NULL1915Using where so it is doing a range scan in some manner. Given that the rows I'm updating will normally be cached in memory, is there anything I can do to force it to just do repeated index lookups like the individual queries are doing? The reason I don't want to use individual queries is because my performance is then bottlenecked by latency, and when trying to update thousands of rows a second that doesn't work out. Just opening multiple connections and doing them in parallel is a problem because of where the updates are coming from and the fact that they have to be done inside a transaction so other connections would just end up blocked anyway. Currently running 4.0.15a. I'm trying to avoid going to 4.1 at this point, but it does look like the INSERT ... ON DUPLICATE KEY UPDATE ... syntax may do individual index lookups the way I want... I haven't verified that yet though. Any suggestions are greatly appreciated. -- 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]
SQL select
hello, i spent about 2 hours solve this problem, i have got a 2 mysql tables, eg: data room_id user_id user_typ user_login messages id text from from_type (quest, user) to to_type (quest, user) itime (type datetime, time when message were insert) and my situation: data 1 1 ujoe 1 2 hthomas 1 3 upeter messages 1 bla1 1 u 2 h 2003-10-02 15:00:00 2 bla2 2 h 1 u 2003-10-02 15:00:20 3 bla3 3 u 1 u 2003-10-02 15:00:40 4 bla4 2 h 1 u 2003-10-02 15:01:05 I need in one SQL select, if is possible, get all records (user_id, user_typ, user_login) from table data of users who sent me later a private message (private message = from, from_type, to, to_type is set) then I sent message to him. thanks for all ideas. j. nemec. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow performance with large or list in where
On Sun, 5 Oct 2003, Alexis da Cruz Henriques Guia wrote: Your problem is that MySQL don't use indeces in selects with 'or' (See MySQL manual). If you are referring to: http://www.mysql.com/doc/en/Searching_on_two_keys.html then that page doesn't say mysql doesn't use indexes on selects with or. It is, as far as I can tell, talking about the case where you have two different keys in the OR clause. In my simplified query, I only have one key with multiple OR values and the page says searching on one key with different OR parts is optimised quite well. Granted, it isn't clear if the page is referring to one single column or all the columns involved in one unique index so it isn't clear if the case that they say is optimized well would be of use to me or not. mysql certainly is using an index for the relevant queries, it is just doing a range scan of that index, instead of individual lookups. The range scan may well be faster if it were reading all the data from disk... but it isn't, and never will be for my query. But i didn't have understand what's your problem executing individual selects... You don't need to open parallel connections. Put the UPDATE commands in a file, and execute them in mysql prompt like this: mysql source fileName; (for help, type 'help' in mysql prompt) Is this your problem? no... my problem is that it is still far too slow to do it that way across a network given that you can only send one command to the server at once, then you have to wait for the response. Not only is there the network latency that adds up when you want to do thousands of operations per second, even on 100 mbit or gigabit ethernet, but there is the OS scheduling latency involved. When doing batch updates, passing the update in batches to the server is nearly always going to result in far better performance... if the server supports it properly. Sticking multiple statements in one file and passing it to the mysql command line client doesn't change the problem in any way. In any case, I can't do any of this by writing commands to a file and loading them using the mysql client, I need to do it using JDBC. I have no problem executing all my updates sequentially except for the fact that it is far too slow. But thanks for the response... ;) Alexis Quoting Marc Slemko [EMAIL PROTECTED]: If I do a query such as: SELECT * from foo where fooid = 10 or fooid = 20 or fooid = 03 ... with a total of around 1900 or fooid = parts on a given table with 500k rows, it takes about four times longer than doing 1900 separate queries in the form: SELECT * from foo where fooid = 10 fooid is the primary key. My real goal is to do updates in a similar way, they have the same issue. And my real goal is actually doing the same thing where instead of fooid=10 I have a multicolumn primary key and use (keycol1=10 and keycol2=2 and ...) My examples here are the simplest case I can find that exhibit the issue. Explain plan on the select shows: table typepossible_keys key key_len ref rowsExtra adsummary range PRIMARY PRIMARY 31 NULL1915Using where so it is doing a range scan in some manner. Given that the rows I'm updating will normally be cached in memory, is there anything I can do to force it to just do repeated index lookups like the individual queries are doing? The reason I don't want to use individual queries is because my performance is then bottlenecked by latency, and when trying to update thousands of rows a second that doesn't work out. Just opening multiple connections and doing them in parallel is a problem because of where the updates are coming from and the fact that they have to be done inside a transaction so other connections would just end up blocked anyway. Currently running 4.0.15a. I'm trying to avoid going to 4.1 at this point, but it does look like the INSERT ... ON DUPLICATE KEY UPDATE ... syntax may do individual index lookups the way I want... I haven't verified that yet though. Any suggestions are greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql account
At 14:00 +1000 10/5/03, Wang Feng wrote:
If you deleted the rows with a DELETE statement, you'll need to also
issue a FLUSH PRIVILEGES statement to cause the server to reread the
grant tables.
In order to try the FLUSH PRIVILEGES statement, I inserted a new user
account into the 'user' table of the 'mysql' database:
mysql insert into user (host, user, password) values ('localhost', 'brian',
'brian');
That won't work to set up the account properly. If you use INSERT to
create an account, you must also use PASSWORD() to encrypt the account.
If you use GRANT to set up the account, you specify the password in
the IDENTIFIED BY clause, and you don't have to use PASSWORD(). GRANT
encrypts it for you.
After doing this, I subsequently use FLUSH PRIVILEGES to let the MySQL
server reread the grant tables.
mysql flush privileges
Query OK, 0 rows affected (0.01 sec)
Then, I quit the MySQL and try to login by 'c:\mysql\bin\mysql -h
localhost -u brian -p' followed by the password brian. I got the following
error message:
Error: 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES).
Then, I restart the computer, and repeat the process above again, same
error. It seems I don't have to restart the computer (in WindowsXP) to
make the change valid.
Something cause my attention is that if I do the
mysql select host, user, password from user;
I found that the password of *alan* is something like 51df199bcd85 while
*brian's* new account password is brian. That is, the later one has not
been encrypted.
See above. Also, read this section of the MySQL Reference Manual:
http://www.mysql.com/doc/en/Adding_users.html
I can see the Alan's *actual* password characters('alan') in WinMySQLadmin
or the my.conf file, not the user table; but I DO see Brian's *actual*
password ('brian') from the user table.
BTW, I can't find any thing in the my.conf or WinMySQLadmin related to the
*brian* --- e.g. in the my.conf file, I only can see 'user=alan,
password=alan'.
I'm not sure why you would expect to.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow performance with large or list in where
Have You test in operator? select * from table where id in (10,20,30,50,60,90, ) Santino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change the *user* table OR simply use GRANT query?
At 18:47 +1000 10/5/03, Wang Feng wrote: Greetings, I'm so curious about this: If a new MySQL database account need to be created, do you insert the account info to the *user* table of the *mysql* database OR do you grant the new account by the GRANT query? What are the differences andn which one do you prefer? Generally, it's preferable to use GRANT. It's simpler, and you don't need to figure out just which changes to make to the grant tables in the mysql database. With GRANT, the server figures out the changes and makes them for you. GRANT also causes the server to reread the grant tables, so you don't need to use FLUSH PRIVILEGES. http://www.mysql.com/doc/en/User_Account_Management.html (I got some trouble when I tried to use the insert, don't know why, just doesn't work. It seems that the GRANT query is the easiest way to achieve that and works perfectly without issuing the FLUSH PRIVILEGES statement.) cheers, feng -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Good SQL Cookbook
Does anyone have a recommendation for a good SQL cookbook? I can do some basic selects, updates and deletes but I'm not comfortable with some of the logic involved in joins or the proper use of group by, etc. In addition, though a full exposition on the SQL language might be instructive, right now, I need to figure out some queries. For example, SELECT C_Date,InvoiceNum,AmtPaid FROM cash WHERE Office = Denver; Is very straight forward and returns what I expet. So does: SELECT SUM(AmtPaid) FROM cash WHERE Office=Denver; But, is it possible to combine these two into a single statement so that if I send it all to an outfile, I have a readable report? This is just an example of the kinds of things I'd like to be able to get a handle on through some sort of reference. Thanks, Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow performance with large or list in where
On Sun, 5 Oct 2003, Santino wrote: Have You test in operator? select * from table where id in (10,20,30,50,60,90, ) Yes, IN does perform at the levels I want and works for the simplified example I gave, but doesn't work for the generalized case I need, which is matching individual rows in a table with a multicolumn primary key which is why I can't use it. Well, I could use it but it would require creating an extra column that is a string with all the component columns of the primary key combined or a binary field that I pack myself then have a unique index on that... but I'd really like to avoid that since this table will have hundreds of thousands of rows added a day and has half a dozen columns that form the primary key. Interestingly, the explain output is exactly the same for the in and the fooid=10 or fooid=20 or ... case. thanks for the suggestion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Good SQL Cookbook
Check out safari.oreilly.com, they have a two week trial right now, and they have a sh*tload of books online, including 'MySQL Cookbook' by Paul Dubois, and they also have 'MySQL' and 'MySQL and Perl for the Web' by Paul also, I would say they are probably the best MySQL books I've found, I also like Leon Atkinson, he's more a php man, but he's pretty good on SQL and he has a good book, Core MySQL, which isn't bad either and if you're into php any at all, he has a new book out Core PHP, which covers the upcoming php 5.0, but they do have the 2nd edition on line of this book, and he has a lot of good php/mysql code in it. I hate to keep pumping up Leon Atkinson, but he has an online shopping program called FreeTrade(open source), and if you study the code, you can find some interesting queries in it also... You'll have to google for the freetrade project, it moves around, and I'm not sure where it is now. I like both authors, they are both very good at explaining the material and they do get the point across very well. Kelley Randy Chrismon wrote: Does anyone have a recommendation for a good SQL cookbook? I can do some basic selects, updates and deletes but I'm not comfortable with some of the logic involved in joins or the proper use of group by, etc. In addition, though a full exposition on the SQL language might be instructive, right now, I need to figure out some queries. For example, SELECT C_Date,InvoiceNum,AmtPaid FROM cash WHERE Office = Denver; Is very straight forward and returns what I expet. So does: SELECT SUM(AmtPaid) FROM cash WHERE Office=Denver; But, is it possible to combine these two into a single statement so that if I send it all to an outfile, I have a readable report? This is just an example of the kinds of things I'd like to be able to get a handle on through some sort of reference. Thanks, Randy -- 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: can NOT drop the database
Just got back onto this again now. To quote the docs you pointed me at: If MySQL crashes in the middle of an ALTER TABLE operation, you may end up with an orphaned temporary table inside the InnoDB tablespace. With innodb_table_monitor you see a table whose name is #sql..., but since MySQL does not allow accessing any table with such a name, you cannot dump or drop it. The solution is to use a special mechanism available starting from version 3.23.48 of InnoDB. If you have an orphaned table #sql... inside the tablespace, then by calling CREATE TABLE `rsql..._recover_innodb_tmp_table`(...) type = innodb; where the table definition is similar to the temporary table, you can make InnoDB to rename the orphaned table to `rsql..._recover_innodb_tmp_table`. Then you can dump and drop the renamed table. The backquotes around the table name are needed because a temporary table name contains the character '-'. It was a crash during an ALTER TABLE operation, I remember it, but it was a while back so I am unable to tell what these 2 orphan table definitions are - even what the tables were called unfortunately. According to the blurb above I need the definition to rename the orphan table. So it looks like I'm stuck. Even so I tried a few random guesses at the table definitions without getting anywhere. It seems mysql doesn't like my backquotes - with my en_UK keyboard I have ` which I think should be OK, so how come I get an error? Have you got any more suggestions? Thanks Adam On 10/01/2003 03:30 PM Heikki Tuuri wrote: Adam, you can use the innodb_table_monitor http://www.innodb.com/ibman.html#InnoDB_Monitor and the advice at http://www.innodb.com/ibman.html#InnoDB_troubleshooting_dict to resolve the problem. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html - Alkuperäinen viesti - Lähettäjä: Adam Hardy [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Wednesday, October 01, 2003 3:24 PM Aihe: Re: can NOT drop the database Hi Heikki, a similar problem happened to me and I got the error: ERROR 1051: Unknown table '#sql-ffa_2,#sql-2b2_30' After reading your post, I checked in the data directory and there are two files there, both of file type data: #sql-2b2_30.frm #sql-ffa_2.frm I certainly didn't put them there myself - I think they must have come from mySQL automatically at some point. Are they real tables in the database? I can't see them when I use 'show tables'. Is it a permissions problem? I have this on another database that I was trying to restore from a dump. I had to rename the database in the end. I am using InnoDB tables for some of my data. Regards Adam -- Running mySQL 4.1.0 on Linux 2.4.20 RH9 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow performance with large or list in where
Hi, - Original Message - From: Marc Slemko Sent: Sunday, October 05, 2003 2:27 PM Subject: Re: slow performance with large or list in where On Sun, 5 Oct 2003, Santino wrote: Have You test in operator? select * from table where id in (10,20,30,50,60,90, ) Yes, IN does perform at the levels I want and works for the simplified example I gave Yeah, I was gonna suggest IN too. The reason it's faster with so many values I think is because the query is much shorter bytes-wise and therefore there's less for MySQL to parse. And BTW, 3.23's parser seems to be REALLY slow compared to 4's. :-) but doesn't work for the generalized case I need, which is matching individual rows in a table with a multicolumn primary key which is why I can't use it. Ah, I see. So you're gonna have it like this?: WHERE (col1=123 AND col2='foo') OR (col1=456 AND col2='bar') OR ... Well, I could use it but it would require creating an extra column that is a string with all the component columns of the primary key combined or a binary field that I pack myself then have a unique index on that... but I'd really like to avoid that since this table will have hundreds of thousands of rows added a day and has half a dozen columns that form the primary key. That might actually be something to look at. You have 6 columns to form the PRIMARY KEY?! Remember, the PRI KEY should really be as short as possible (bytes-wise). Maybe you could make the PRIMARY KEY be a value derived from your 6 columns. And then you could make just a unique index on those columns if needed. Interestingly, the explain output is exactly the same for the in and the fooid=10 or fooid=20 or ... case. Right. They're optimized exactly the same way. The speed difference comes from the fact that IN is shorter and easier to parse than so many ORs. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow performance with large or list in where
Might instead want to look at where fooid in (xx, xx, xx, xx) On Sat, 4 Oct 2003, Marc Slemko wrote: If I do a query such as: SELECT * from foo where fooid = 10 or fooid = 20 or fooid = 03 ... with a total of around 1900 or fooid = parts on a given table with 500k rows, it takes about four times longer than doing 1900 separate queries in the form: SELECT * from foo where fooid = 10 fooid is the primary key. My real goal is to do updates in a similar way, they have the same issue. And my real goal is actually doing the same thing where instead of fooid=10 I have a multicolumn primary key and use (keycol1=10 and keycol2=2 and ...) My examples here are the simplest case I can find that exhibit the issue. Explain plan on the select shows: table typepossible_keys key key_len ref rowsExtra adsummary range PRIMARY PRIMARY 31 NULL1915Using where so it is doing a range scan in some manner. Given that the rows I'm updating will normally be cached in memory, is there anything I can do to force it to just do repeated index lookups like the individual queries are doing? The reason I don't want to use individual queries is because my performance is then bottlenecked by latency, and when trying to update thousands of rows a second that doesn't work out. Just opening multiple connections and doing them in parallel is a problem because of where the updates are coming from and the fact that they have to be done inside a transaction so other connections would just end up blocked anyway. Currently running 4.0.15a. I'm trying to avoid going to 4.1 at this point, but it does look like the INSERT ... ON DUPLICATE KEY UPDATE ... syntax may do individual index lookups the way I want... I haven't verified that yet though. Any suggestions are greatly appreciated. -- 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: Blob fields
Any mysql encryption functions would be done server side ofcourse before putting it into the database.. I'd just incorporate an de/encryption scheme into your client app, and insert as standard BLOB string to remote server. On Sat, 4 Oct 2003, sian_choon wrote: Hi, I have the question related to this topic, hopefully you could help me on this. Is that possible that we insert an encrypted image into blob fields where the encryption is done by using mysql existing function (AES_Encrypt) from the client side? If yes, how is the procedure ? Thanks in advance. Jeremy Zawodny [EMAIL PROTECTED] wrote: On Wed, Oct 01, 2003 at 05:51:18PM +0100, Angelo Carmo wrote: I people, Who knows how to insert an image file into blob fileds. Lots of us know how. And we've discussed it on the list about 600 times already. I'm sure you'll find an answer in the list archives. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 17 days, processed 630,933,987 queries (412/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? The New Yahoo! Shopping - with improved product search -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY/ORDER BY Problem
The SQL specification does allow aggregates in the ORDER BY. Does mySQL have any plans to add such functionality (or at least add it to its list of things it doesn't do)? The problem with the solution of ordering by an alias is that I may not necessarily want the thing I'm ordering by to be in the result set. In the example below, I may just want to select the breed, ordered by minimum age, without showing the min. age. Is there a good way in mySQL to make this work? Thanks. Why doesn't the following work: mysql CREATE TABLE dog(id integer, breed char(20), age integer, weight integer) ; mysql SELECT breed, MIN(age) - FROM dog - GROUP BY breed - ORDER BY MIN(age); ERROR : Invalid use of group function I don't believe that aggregate functions are legal in an ORDER BY clause. The solution, as you've found, is to select the value you want to order by, alias it, and refer to the alias in the ORDER BY clause. but this does mysql SELECT breed, MIN(age) AS minage - FROM dog - GROUP BY breed - ORDER BY minage; -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
confused newbie -- Open Office 1.1 as front end
Hi guys n gals OK MySQL is nice and robust, i'm stiil new to it and have an M$ Access mentality when it come to buiding and working with DBs. Can i build an example_client table and an example_appointment table in MySQL and then use M$ Access(odbc) or data sources in Open Office to create the relationships and forms and things to make the DBs usable to the average person? I haven't found a straight answer yet or even a a clear cut guide. All advice is GREATLY APPRECIATED(including what i can't do with this method)!! Thanks folks --- Warren Stanley Information Technology Support Officer Bidgerdii Community Health Rockhampton Q 4700 ---
RE: confused newbie -- Open Office 1.1 as front end
Warren: Yes and no. You can use Access as a front end to MySQL, including creating forms and queries. If you want relational integrity you'll need to handle that directly in MySQL (with raw SQL) or with a dedicated MySQL designer tool (there are several available, both freeware and commercial - google will find them for you or check the archives). Access can only manage relationships in actual Access databases. I'm not familiar with Open Office, but based on things I've read on the list (again, search the archives) I think you can do at least some of what you want with it. It's important to understand that working with MySQL and Access is *not* like working with Access alone. It's much more like working with Access and MS-SQL Server without .ADP files. You'll use either linked tables (low effort, low performance) or pass-through queries (more effort, better performance). Someday soon (hopefully ;-) you'll be able to use stored procedures. Hope this helps, John Hopkins Hopkins IT -Original Message- From: Warren Stanley [mailto:[EMAIL PROTECTED] Sent: Sunday, October 05, 2003 4:48 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: confused newbie -- Open Office 1.1 as front end Hi guys n gals OK MySQL is nice and robust, i'm stiil new to it and have an M$ Access mentality when it come to buiding and working with DBs. Can i build an example_client table and an example_appointment table in MySQL and then use M$ Access(odbc) or data sources in Open Office to create the relationships and forms and things to make the DBs usable to the average person? I haven't found a straight answer yet or even a a clear cut guide. All advice is GREATLY APPRECIATED(including what i can't do with this method)!! Thanks folks --- Warren Stanley Information Technology Support Officer Bidgerdii Community Health Rockhampton Q 4700 --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating Triggers and procedures in MySQL
Hi All, I am looking into putting triggers, procedures and into MySQL. The tools I am using seem to lack this facility, unless MySQL doesnot actually have these important database features. Any help is appreciated. Gregory Hicks Database Analyst Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating Triggers and procedures in MySQL
Information from the manual: 1.7.4.4 Stored Procedures and Triggers Stored procedures are being implemented in our version 5.0 development tree. See section 2.3.4 Installing from the Development Source Tree . This effort is based on SQL-99, which has a basic syntax similar (but not identical) to Oracle PL/SQL. In addition to this, we are implementing the SQL-99 framework to hook in external languages. A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this has been done, clients don't need to keep re-issuing the entire query but can refer to the stored procedure. This provides better overall performance because the query has to be parsed only once, and less information needs to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server. However, stored procedures of course do increase the load on the database server system, as more of the work is done on the server side and less on the client (application) side. Triggers will also be implemented. A trigger is effectively a type of stored procedure, one that is invoked when a particular event occurs. For example, you can install a stored procedure that is triggered each time a record is deleted from a transactional table and that stored procedure automatically deletes the corresponding customer from a customer table when all his transactions are deleted. = Last time this was brought up, there was a long discussion between the I love stored-procedures and the I hate store-procedure groups of people. Hopefully, we won't get that again. If you are interested in the lengthy discussion, you may want to search the list archives. On Mon, 06 Oct 2003 10:54:48 -0850 (CDT), Gregory Hicks spoke thusly about Creating Triggers and procedures in MySQL: Hi All, I am looking into putting triggers, procedures and into MySQL. The tools I am using seem to lack this facility, unless MySQL doesnot actually have these important database features. Any help is appreciated. Gregory Hicks Database Analyst Programmer --- Listserv only address. Jeff Shapiro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql account
Thanks Paul.
Fully understand!!
cheers,
feng
- Original Message -
From: Paul DuBois [EMAIL PROTECTED]
To: Wang Feng [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, October 06, 2003 4:38 AM
Subject: Re: mysql account
At 14:00 +1000 10/5/03, Wang Feng wrote:
If you deleted the rows with a DELETE statement, you'll need to also
issue a FLUSH PRIVILEGES statement to cause the server to reread the
grant tables.
In order to try the FLUSH PRIVILEGES statement, I inserted a new user
account into the 'user' table of the 'mysql' database:
mysql insert into user (host, user, password) values ('localhost',
'brian',
'brian');
That won't work to set up the account properly. If you use INSERT to
create an account, you must also use PASSWORD() to encrypt the account.
If you use GRANT to set up the account, you specify the password in
the IDENTIFIED BY clause, and you don't have to use PASSWORD(). GRANT
encrypts it for you.
After doing this, I subsequently use FLUSH PRIVILEGES to let the MySQL
server reread the grant tables.
mysql flush privileges
Query OK, 0 rows affected (0.01 sec)
Then, I quit the MySQL and try to login by 'c:\mysql\bin\mysql -h
localhost -u brian -p' followed by the password brian. I got the
following
error message:
Error: 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES).
Then, I restart the computer, and repeat the process above again, same
error. It seems I don't have to restart the computer (in WindowsXP)
to
make the change valid.
Something cause my attention is that if I do the
mysql select host, user, password from user;
I found that the password of *alan* is something like 51df199bcd85 while
*brian's* new account password is brian. That is, the later one has
not
been encrypted.
See above. Also, read this section of the MySQL Reference Manual:
http://www.mysql.com/doc/en/Adding_users.html
I can see the Alan's *actual* password characters('alan') in
WinMySQLadmin
or the my.conf file, not the user table; but I DO see Brian's *actual*
password ('brian') from the user table.
BTW, I can't find any thing in the my.conf or WinMySQLadmin related to
the
*brian* --- e.g. in the my.conf file, I only can see 'user=alan,
password=alan'.
I'm not sure why you would expect to.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating Triggers and procedures in MySQL
Jeff, Your name is familiar to me. Are you involved in any way with the Borland C++ Builders list on Topica.com? OK, that explains why I can't find the feature. I consider them to be important, but not mandatory. Their presence can simplfy business rules in data processing. regards greg On Sun, 5 Oct 2003 19:48:36 -0600, Jeff Shapiro wrote: Information from the manual: 1.7.4.4 Stored Procedures and Triggers Stored procedures are being implemented in our version 5.0 development tree. See section 2.3.4 Installing from the Development Source Tree . This effort is based on SQL-99, which has a basic syntax similar (but not identical) to Oracle PL/SQL. In addition to this, we are implementing the SQL-99 framework to hook in external languages. A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this has been done, clients don't need to keep re-issuing the entire query but can refer to the stored procedure. This provides better overall performance because the query has to be parsed only once, and less information needs to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server. However, stored procedures of course do increase the load on the database server system, as more of the work is done on the server side and less on the client (application) side. Triggers will also be implemented. A trigger is effectively a type of stored procedure, one that is invoked when a particular event occurs. For example, you can install a stored procedure that is triggered each time a record is deleted from a transactional table and that stored procedure automatically deletes the corresponding customer from a customer table when all his transactions are deleted. = Last time this was brought up, there was a long discussion between the I love stored-procedures and the I hate store-procedure groups of people. Hopefully, we won't get that again. If you are interested in the lengthy discussion, you may want to search the list archives. On Mon, 06 Oct 2003 10:54:48 -0850 (CDT), Gregory Hicks spoke thusly about Creating Triggers and procedures in MySQL: Hi All, I am looking into putting triggers, procedures and into MySQL. The tools I am using seem to lack this facility, unless MySQL doesnot actually have these important database features. Any help is appreciated. Gregory Hicks Database Analyst Programmer --- Listserv only address. Jeff Shapiro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
