BLOB Source
Can someone please post some asp or php working with BLOB Files, and how to Get files into a Mysql Database,, in working woth PDF Files thanks
Re: Named Pipe for General Query Log
Hey, I sent this a while ago, but never received a response. This still seems to exist under 5.0.15-standard (at least under mysql-standard-5.0.15-linux-i686-glibc23) Can anyone from MySQL comment on this or should I open it as a bug? Thanks, JP On 6/11/05, Jake Peavy [EMAIL PROTECTED] wrote: Has anyone been able to use a named pipe for their general query log (or any of the other logfiles for that matter)? I tried the following as user mysql: rm /var/lib/mysql/myhost.log mkfifo -m 0660 /var/lib/mysql/myhost.log but the mysql server would not start. I think it would be very useful to be able to use a FIFO for this so I can use the log for debugging/info without having to create a log rotation script. I am running 5.0.2-alpha-standard on linux on i386. Thanks. F
Let mysql calculate levenshtein-distance
Hi, I have a nice little code snippet in pseudocode, which calculates the levenshtein-distance between 2 words. Is it possible to implement that code in mysql like in select word1,word2 from myTable order by dist(word1,word2 ) where dist is the function mentionend above which takes 2 strings and returns an int? Weird question??? :) Thanks Horst -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sub Query
Hi Could anyone help perhaps tell me why the following simple query containing a sub-query gives a syntax error. select 1 from messages where not exists ( select 1 from message_push_notifications where message_id = messages.id) Thanx Herman __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database IDs
Hello. In the manual we have: Returns the first automatically generated value that was set for an AUTO_INCREMENT column by the last INSERT or UPDATE query to affect such a column. So in you query both last_insert_id() should return the same value, which equals to the value that was set for an AUTO_INCREMENT column by the last INSERT or UPDATE query. Note that you have one query, which just inserts several rows, so during it is processed the returned value of last_insert_id() is constant, even if your bulk insert is changing an AUTO_INCREMENT field. Björn Persson wrote: Speaking of LAST_INSERT_ID(), does anyone know how MySQL is supposed to=20 process a statement like this:? insert into some_table (ref_field, other_field) values (last_insert_ID(), 1), (last_insert_ID(), 2); I've looked in the manual for details on which order that statement is=20 processed in, but I haven't found an answer. Bj=C3=B6rn Persson -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PHP installation problem
I am having a hard time compiling php 5.0.5 on solaris 10. I have already
installed mysql 5.0.15 (Binary) 64-bit.
./configure --prefix=/PATH/php505 --with-apxs2=/PATH/apache/bin/apxs
--with-mysql=/PATH/mysql/ --enable-dbase --with-zlib-dir=/path/php505/
--with-pear
This ends with - error: mysql config failed
I have attached part of the config.log file:
ld: warning: file /apps/mysql//lib/libmysqlclient.a(client.o): wrong ELF
class: ELFCLASS64
Undefined first referenced
symbol in file
mysql_error /var/tmp//cciQfQiO.o
ld: fatal: Symbol referencing errors. No output written to conftest
collect2: ld returned 1 exit status
configure: failed program was:
#line 61645 configure
#include confdefs.h
/* Override any gcc2 internal prototype to avoid an error. */
/* We use char because int might match the return type of a gcc2
builtin and then its argument prototype would still apply. */
char mysql_error();
int main() {
mysql_error()
; return 0; }
*
I am installing php 5.0.5 on a solaris 10 server 64-bit. I have already
installed mysql 5.0.15 (Binary) 64-Bit.
I already have Apache 2.0.54 running on this server. All the applications
are installed in a non-global zone in Solaris 10. I am not sure if the
problem installing php is because of 64-Bit Mysql. I would really
appreciate some help.
Thank You
UR.
Re: mysql_init(m) changes values of variables
At 11:33 -0500 11/13/05, Bruce Martin wrote: Ok I have an update. I fixed my problem by passing a NULL parameter to mysql_init() to do this I did this: MYSQL *m; m=mysql_init(NULL); connection=mysql_real_connect(m,cStrdbServer,cStrdbUser,cStrdbUserPass,cStrdbName,0,NULL,0); Which solves the mystery. When you passed m, it was not initialized and pointed to random memory. mysql_init() was the non-NULL value and took it to be the address of a struct to initialize, and initialized it. That stomped all over whatever m happened to be pointing to - in this case your int. On Nov 12, 2005, at 7:44 PM, Bruce Martin wrote: Hello all, I found this odd thing when writing a client in C to connect to a mysql database server. I want to use mysql_real_query so I need the strlen() of the sqlStatement. So prior to calling mysql_init(m) I get the length of the sqlStatement. I check that length and it is correct after I first set it up the length is 23 which is correct. I then call mysql_init(m), then I check the length again, well the value of the int I set earlier, and it is 0. What's the deal? why would mysql_init() change the value of my int? Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIKE problem?
Hello. Really, seems a bit weird for me. I suggest you to check your 'character_set_xxx' variables to be sure that there're no unnecessary translations from one encoding to another. If you're able to make a small repeatable test case, install on your Debian server second instance of MySQL (use official binaries) and check out if the problem remains with a new copy. [EMAIL PROTECTED] wrote: Recently I ran into a problem with 'LIKE' in mysql on Debian Sarge: mysql select VERSION(); +---+ | VERSION() | +---+ | 4.1.11-Debian_4sarge2-log | +---+ 1 row in set (0.00 sec) with the following table: CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL auto_increment, `username` varchar(64) collate latin2_hungarian_ci default NULL, ... ) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci I get the following outputs: mysql select count(*) from user where username like 'o%'; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.00 sec) mysql select count(*) from user where username like 'or%'; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.00 sec) mysql select count(*) from user where username like 'ors%'; +--+ | count(*) | +--+ | 89 | +--+ 1 row in set (0.00 sec) So the number of usernames which match like 'o%' is zero, while the number of matching lines for like 'ors%' is 89. Moreover, the sum of the results of these two queries select count(*) from user where username like 'a%'; select count(*) from user where username not like 'a%' or username is null; is not the same for all letters of the alphabet: letter like not-like sum n 2304 59317 61621 o 0 60797 60797 p 3048 58573 61621 Any ideas? Zoltan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BLOB Source
You are going to need some sort of Upload software.
For this example, we are going to use Persits.Upload for the upload manager.
!-- BEGIN SNIPPET --
%
Set Upload = Server.CreateObject(Persits.Upload) 'need to
download ASPUpload to use this.
Upload.IgnoreNoPost = true
'ASPUpload Specific
Upload.Save
'ASPUpload Specific
Set File = Upload.Files(fld_2)
'ASPUpload Specific
'
if Upload.form(button)=Upload! then
'
If Upload.form(fld_2) then
If Not File Is Nothing Then
'
SQL = INSERT INTO table_name
(fld_0,fld_1,fld_2,File_Type,File_Size) VALUES ('
SQL = SQL value_0 ',' value_1 ',?,'
right(File.FileName,3) ',' File.Size ')
'Here the ? Is the uploaded file from your web page.
File.ToDatabase DSN=yourDSN, SQL'The Connection to
the Database.
Response.Write File saved.
Else
Response.Write File not selected. 'If INPUT
TYPE=FILE IS Empty then bail
End If
else
response.write There is an Error... Check this out'Other type
of error message
end if
Set Upload = nothing'Clean Up
Set File = nothing 'Clean up
end if
%
form method=post action=your_page_goes_here.asp
enctype=multipart/form-data
input type=file name=fld_1 size=20
input type=submit value=submit name=button
/form
!-- END SNIPPET --
What you will notice is that if the form is OK (not missing any req.
fields), then it sends the file to the database. The ? is the value of the
uploaded file. It is recommended that you send the file type in case you
want to pull them later without having to worry about putting in specific
Response.ContentType for every page. You can use the File_Type in the db to
use the right ContentType.
The File.ToDatabase object pushes the SQL string (including the Uploaded
BLOB string) to the database.
That's it. Of course, you will have to tailor this to your upload software
and to your database.
J.R.
-Original Message-
From: Brian E Boothe [mailto:[EMAIL PROTECTED]
Sent: Sunday, November 13, 2005 10:50 PM
To: mysql@lists.mysql.com
Subject: BLOB Source
Can someone please post some asp or php working with BLOB Files, and how to
Get files into a Mysql Database,, in working woth PDF Files thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub Query
Herman Scheepers [EMAIL PROTECTED] wrote on 11/13/2005 11:53:23 AM: Hi Could anyone help perhaps tell me why the following simple query containing a sub-query gives a syntax error. select 1 from messages where not exists ( select 1 from message_push_notifications where message_id = messages.id) Thanx Herman One simple explanation is that you are trying to execute that statement on a version of MySQL that doesn't have subqueries. You must be on 4.1 or greater in order to use subqueries. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Updating a LONGTEXT field
I've looked at concat and other string functions as well as searched on 'update' and 'longtext', but the results haven't been useful. Am I missing something easy, or will this be more involved than a simple one-line SQL statement? You obviously didn't look at CONCAT that hard... UPDATE mytable SET myfield = CONCAT( myfield, more text ) WHERE condition LIMIT 1 Evidently not. :) That works nicely. Thanks! Whil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compilation of mysql-5.0.15 on Solaris 5.9
I am trying to compile mysql-5.0.15 on Solaris 5.9 with the following options: CC=cc;export CC CFLAGS=-Xa -fast -native -xstrconst -mt -xarch=v9;export CFLAGS CXX=CC;export CXX CXXFLAGS=-noex -mt -xarch=v9 ASFLAGS=-xarch=v9;export CXXFLAGS ./configure --prefix=/usr/local/mysql --enable-assembler using Sun C 5.6 2004/07/15. I am getting the following errors: cc -O -DDBUG_OFF -Xa -fast -native -xstrconst -xarch=v9 -D_FILE_OFFSET_BITS=64 - DHAVE_RWLOCK_T -o factorial my_main.o factorial.o -mt libdbug.a ../mysys/libmys ys.a ../strings/libmystrings.a -lpthread -lthread -lposix4 -lcrypt -lgen -lsocke t -lnsl -lm -lpthread -lthread Undefined first referenced symbol in file strmov libdbug.a(dbug.o) ld: fatal: Symbol referencing errors. No output written to factorial make[2]: *** [factorial] Error 1 make[2]: Leaving directory `/export/home/src/net/db/mysql/mysql-5.0.15/dbug' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/export/home/src/net/db/mysql/mysql-5.0.15' make: *** [all] Error 2 Any suggestions? I am new to the Sun environment. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[OT]Re: character sets.....(missing info)
For information for other people who would have the same problem: Warning Do not issue the query 'set names' with Connector/J, as the driver will not detect that the character set has changed, and will continue to use the character set detected during the initial connection setup. To allow multiple character sets to be sent from the client, the UTF-8 encoding should be used, either by configuring utf8 as the default server character set, or by configuring the JDBC driver to use UTF-8 through the characterEncoding property. (from http://dev.mysql.com/doc/refman/5.0/en/cj-character-sets.html) I changed the test db in utf8 and reinserted the data, but still the xml export is not ok, the accentuated characters are still wrong,even using the characterEncoding property of the driver. So I think the problem is probably not with mysql encoding but may be with the xml export from java, and I found that on the web to ensure you export in the right encoding: //Open an Output Stream Writer to set encoding OutputStream fout = new FileOutputStream(fileName); OutputStream bout = new BufferedOutputStream(fout); OutputStreamWriter out = new OutputStreamWriter(bout,UTF-8); Just wanted to give some info who may help somebody who is not a java developper (like me!). If somebody around knows if this has any chance to work or if there is an other problem please let me know! Thanks to everybody for your help. melanie _ MSN Messenger 7.5 is now out. Download it for FREE here. http://messenger.msn.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Duplicate Insert Crashes Client
Hello again, Ok new problem. If for some reason, my client tries to INSERT something to the database that is identical to a record already there, my client crashes. Is there an error I can trap for this? The DB does insert the new record. Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
With Rollup wrong results
I am running the query below: SELECT products.product, products.price, count( log.product ) AS 'Count', ROUND(price*count(log.product), 2) AS 'Total' FROM products LEFT JOIN log ON products.product= log.product GROUP BY product with rollup Here are the results: +--+---+---+---+ | product | price | Count | Total | +--+---+---+---+ | ATM Card | 3.00 | 2 | 6.00 | | Audio Response | 3.00 | 0 | 0.00 | | Check Card | 5.00 | 1 | 5.00 | | Courtesy Pay | 5.00 | 2 | 10.00 | | Draft with Direct Deposit| 5.00 | 0 | 0.00 | | Draft without Direct Deposit | 3.00 | 0 | 0.00 | | E-statement | 5.00 | 2 | 10.00 | | Gap | 20.00 | 0 | 0.00 | | MBI | 10.00 | 0 | 0.00 | | Membersonline| 5.00 | 0 | 0.00 | | New Account | 5.00 | 1 | 5.00 | | New Loan | 5.00 | 0 | 0.00 | | New MemBear Account| 5.00 | 0 | 0.00 | | NULL | 5.00 | 8 | 40.00 | +--+---+---+---+ 14 rows in set (0.00 sec) As you can see the summary total in the 'Total' column is incorrect. The summary total should be 36 but is spitting out 40. I am not even sure where it is getting 40. The only way to get 40 is if it is multiplying 5X8. I am running MySQL 4.1.15. Also the 'price' column has the wrong total. I am not really concerned with that total. I just want the summary total in the 'Total' column to be correct. Any suggestions? Thank You, Noel Stratton Computer Specialist Members 1st Credit Union -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Let mysql calculate levenshtein-distance
yes, create a UDF. look at the mysql source for examples... On Mon, 14 Nov 2005, Horst [iso-8859-1] J?ger wrote: Hi, I have a nice little code snippet in pseudocode, which calculates the levenshtein-distance between 2 words. Is it possible to implement that code in mysql like in select word1,word2 from myTable order by dist(word1,word2 ) where dist is the function mentionend above which takes 2 strings and returns an int? Weird question??? :) Thanks Horst -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 269-1555 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General logging vs. bin logging
Hello, I'm trying to get a handle on who is making what changes in my database, but cannot seem to find user/account info in the bin-logs besides the actual update that was made. Is there a way in which MySQL logs not only the changes, but also the user account that updated a record in the database and how is this enabled? Any help would be greatly appreciated. Thanks in advance. CL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Will SELECT COUNT(*) FROM TABLE ever be cheap on INNODB?
Hi all, I know I'm a bit late in coming to this discussion. Glad to see that this problem is on the InnoDB to-do list. I will put out that one thing you can do is utilize triggers. Make a separate table with one field, and put a trigger on the table you want counted so that every time there is an insert to the table, you increment the field in the 2nd table. Add a second trigger to decrement the 'counter table' field every time a delete statement is issued. This should work. It's a hack, but if you need that implementation quickly, there ya go. Granted, you need MySQL 5.0. -Sheeri On 11/1/05, Kevin Burton [EMAIL PROTECTED] wrote: MyISAM has a cool feature where it keeps track of the internal row count so that SELECT COUNT(*) FROM FOO executes in constant time. Usually 1ms or so. The same query on INNODB is O(logN) since it uses the btree to satisfy the query. I believe that MyISAM just increments an internal count so that every insert/delete changes the count. Are there plans to add this to INNODB? I was relying on this for my database monitoring app and noticed that it was killing my performance (I forgot about this problem...) Would be really nice to have. Kevin Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://www.feedblog.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 -- 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]
How to reload my.cnf?
Hi All, I will need to change my.cnf in master machine, but I can't afford to restart MySQL server as the server has been used for heavy traffic databases. Can anyone tell me how to do it? Cheers, *Tedy Aulia*
Re: Let mysql calculate levenshtein-distance
If you are asking whether MySQL already has a function that will calculate a levenshtein-distance, I'm pretty sure that the answer is no. You can check the MySQL manual yourself to be sure. The (English language manuals are at http://dev.mysql.com/doc/ and other translations of the manuals can also be accessed from that same page. You'll need to choose the appropriate manual based on your version of MySQL; then look in the Functions and Operators chapter.) If you are asking whether MySQL supports user-defined functions (UDFs), which allow you to add your own functions to MySQL, the answer is yes, provided you are using Version 5 of MySQL. That means that if you can write the logic needed to calculate a levenshtein-distance in C or C++, you can create your own UDF and add it to MySQL, then invoke it via the function name in your SQL. Possible problem: I see from your example that you want to put the function in your ORDER BY clause; I'm not sure if you can do *that* in MySQL; I think functions have to be invoked from the SELECT clause or HAVING clause or maybe a few other places; I've never seen a function invoked in an ORDER BY in either MySQL or DB2, which I know considerably better than MySQL. You may want to try writing a trivial function and then seeing if you can invoke it from an ORDER BY before you spend much time on the levenshtein-distance function Of course, you could always rewrite your query so that the function is invoked from the SELECT, like this: select word1,word2, dist(word1, word2) from myTable order by 3 where the '3' in the ORDER BY clause indicates the third column of the SELECT clause. That should be just as good as your original query. Rhino - Original Message - From: Horst Jäger [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, November 14, 2005 11:02 AM Subject: Let mysql calculate levenshtein-distance Hi, I have a nice little code snippet in pseudocode, which calculates the levenshtein-distance between 2 words. Is it possible to implement that code in mysql like in select word1,word2 from myTable order by dist(word1,word2 ) where dist is the function mentionend above which takes 2 strings and returns an int? Weird question??? :) Thanks Horst -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
net_printf
Some months ago, I wrote a MySQL user-defined procedure (based on sql_analyse.cc). It works well (has survived a test of 200M queries): however, at the moment, I'm reviewing the code and trying to make it more robust. As part of this, I'm trying to send error messages back to the mysql client, and am having considerable difficulty figuring out how to do this. The obvious candidate is net_printf, but this requires a THD object as the first argument, and I'm not clear on how to obtain this, if it is called from within a function which has not received a THD object as its own first argument. I've tried using the current_thd macro: this apparently returns a valid THD object, but using it as the first argument in a call to net_printf seems to cause the connection with the client to be terminated, and the thread itself to die. Apologies if I'm missing something blatantly obvious (almost certainly the case): I'd be really grateful if someone could point me in the right direction. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.18 Performance Question
Hello, We are running a MySQL 4.18 database as a read-only snapshot of an internal Sybase database. We have seen an increase in core tables where now we have several million records of data in the MySQL tables. Suddenly the SELECT queries we ran before which took few seconds are now taking much longer to run [10-12]. My questio is to know what performance issues are with MySQL 4.18 which we need to watch out for dealing with very large tables. Also as far as indexes is there an UPDATE STATISTICS command to make sure the indexes are performing properly? Thanks in advance. I am a Sybase/SQL Server guy who has been handed this task. So if I ask dumb question please bear with me. Yours, Shahryar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.18 Performance Question
Hi Shahryar, These pages may help : http://dev.mysql.com/doc/refman/5.0/en/optimization.html The other command you may find useful is the OPTIMIZE table command documented here : http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Shahryar G. Hashemi [mailto:[EMAIL PROTECTED] Sent: Tuesday, 15 November 2005 8:52 AM To: mysql@lists.mysql.com Subject: MySQL 4.18 Performance Question Hello, We are running a MySQL 4.18 database as a read-only snapshot of an internal Sybase database. We have seen an increase in core tables where now we have several million records of data in the MySQL tables. Suddenly the SELECT queries we ran before which took few seconds are now taking much longer to run [10-12]. My questio is to know what performance issues are with MySQL 4.18 which we need to watch out for dealing with very large tables. Also as far as indexes is there an UPDATE STATISTICS command to make sure the indexes are performing properly? Thanks in advance. I am a Sybase/SQL Server guy who has been handed this task. So if I ask dumb question please bear with me. Yours, Shahryar -- 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: Will SELECT COUNT(*) FROM TABLE ever be cheap on INNODB?
Just to contribute a different point of view, let me tell you what happens in DB2. DB2 has positions in its catalog, also known as meta data, to keep all kinds of statistics about the data, including the number of rows in each table. However, the designers made an executive decision in the early days of DB2 that those statistics would NOT be dynamically maintained. Apparently, they feared that they would add a great deal of overhead to DB2 if it constantly updated statistics like the number of rows in each table, the range of values in each column of an index, the number of pages of data in the tablespace, etc. etc. etc. Instead, the provided a utility called RUNSTATS that could be run whenever the user liked. When executed, RUNSTATS refreshes all of the desired statistics for a particular table or index and brings them right up to date. The DB2 Optimizer, the component that chooses access paths for the data, then uses those statistics. (Of course, if you don't run RUNSTATS, the Optimizer uses the existing statistics, whatever they may be. Those statistics may be wildly inaccurate and can skew the access path selection process.) Now, with respect to getting decent performance from a COUNT(*) query, DB2 is quite smart: if there is an index on any column of the table, DB2 can simply go to the index, which is organized as a b-tree, and count the number of index entries. That tends to involve minimum I/O and is therefore frequently very fast. If there are multiple indexes on the table, it may even be able to reason out which index has the fewest number of entries to minimize the amount of counting it has to do but I'm not sure about that. Maybe the MySQL developers will want to think about using techniques like those I've just mentioned for getting a COUNT(*) result faster? Rhino - Original Message - From: sheeri kritzer [EMAIL PROTECTED] To: Kevin Burton [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, November 14, 2005 5:10 PM Subject: Re: Will SELECT COUNT(*) FROM TABLE ever be cheap on INNODB? Hi all, I know I'm a bit late in coming to this discussion. Glad to see that this problem is on the InnoDB to-do list. I will put out that one thing you can do is utilize triggers. Make a separate table with one field, and put a trigger on the table you want counted so that every time there is an insert to the table, you increment the field in the 2nd table. Add a second trigger to decrement the 'counter table' field every time a delete statement is issued. This should work. It's a hack, but if you need that implementation quickly, there ya go. Granted, you need MySQL 5.0. -Sheeri On 11/1/05, Kevin Burton [EMAIL PROTECTED] wrote: MyISAM has a cool feature where it keeps track of the internal row count so that SELECT COUNT(*) FROM FOO executes in constant time. Usually 1ms or so. The same query on INNODB is O(logN) since it uses the btree to satisfy the query. I believe that MyISAM just increments an internal count so that every insert/delete changes the count. Are there plans to add this to INNODB? I was relying on this for my database monitoring app and noticed that it was killing my performance (I forgot about this problem...) Would be really nice to have. Kevin Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://www.feedblog.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 -- 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] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.0.15 Compile error on Redhat EL4.0
Sent this before but didn't get a response ... So sending again ... Compiling from source: Platform is RHEL 4.0 # gcc -v Reading specs from /usr/lib/gcc/i386-redhat-linux/3.4.4/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-java-awt=gtk --host=i386-redhat-linux Thread model: posix gcc version 3.4.4 20050721 (Red Hat 3.4.4-2) configure- CC=gcc ./configure \ --prefix=/usr/local/mysql-5.0.15-i686-linux-rhel4 --with-libwrap=/usr/lib \ --with-ndbcluster --with-ndb-shm \ --with-ndb-sci The above configure runs fine. make ends with: then mv -f .deps/sql_map.Tpo .deps/sql_map.Po; else rm -f .deps/sql_map.Tpo; exit 1; fi if g++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local/mysql-5.0.15-i686-linux-rhel4\ -DDATADIR=\/usr/local/mysql-5.0.15-i686-linux-rhel4/var\ -DSHAREDIR=\/usr/local/mysql-5.0.15-i686-linux-rhel4/share/mysql\ -DHAVE_CONFIG_H -I. -I. -I.. -I../innobase/include -I../ndb/include -I../ndb/include/ndbapi -I../ndb/include/mgmapi -I../include -I../include -I../regex -I. -I/usr/lib/include -O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -MT mysqld.o -MD -MP -MF .deps/mysqld.Tpo -c -o mysqld.o mysqld.cc; \ then mv -f .deps/mysqld.Tpo .deps/mysqld.Po; else rm -f .deps/mysqld.Tpo; exit 1; fi mysqld.cc: In function `int main(int, char**)': mysqld.cc:3279: error: invalid conversion from `const char*' to `char*' make[4]: *** [mysqld.o] Error 1 make[4]: Leaving directory `/usr/local/src/mysql-5.0.15/sql' make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory `/usr/local/src/mysql-5.0.15/sql' make[2]: *** [all] Error 2 make[2]: Leaving directory `/usr/local/src/mysql-5.0.15/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/local/src/mysql-5.0.15' make: *** [all] Error 2 Any clues? Eric Stewart - Network Admin, USF Tampa Library - [EMAIL PROTECTED] Managing sysadmins is like leading a neighborhood gang of neurotic pumas on jet-powered hoverbikes with nasty smack habits and opposable thumbs. - Feen, Benjy: Pumas on Hoverbikes: Sysadmin Management, http://www.monkeybagel.com/pumas.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Let mysql calculate levenshtein-distance
On Mon, 14 Nov 2005, Rhino wrote: If you are asking whether MySQL already has a function that will calculate a levenshtein-distance, I'm pretty sure that the answer is no. You can check the MySQL manual yourself to be sure. The (English language manuals are at http://dev.mysql.com/doc/ and other translations of the manuals can also be accessed from that same page. You'll need to choose the appropriate manual based on your version of MySQL; then look in the Functions and Operators chapter.) If you are asking whether MySQL supports user-defined functions (UDFs), which allow you to add your own functions to MySQL, the answer is yes, provided you are using Version 5 of MySQL. That means that if you can write no v3 and v4 can too the logic needed to calculate a levenshtein-distance in C or C++, you can create your own UDF and add it to MySQL, then invoke it via the function name in your SQL. Possible problem: I see from your example that you want to put the function in your ORDER BY clause; I'm not sure if you can do *that* in MySQL; I think yes they can, but not efficient ... order by rand() ... functions have to be invoked from the SELECT clause or HAVING clause or maybe a few other places; I've never seen a function invoked in an ORDER BY in either MySQL or DB2, which I know considerably better than MySQL. You may want to try writing a trivial function and then seeing if you can invoke it from an ORDER BY before you spend much time on the levenshtein-distance function Of course, you could always rewrite your query so that the function is invoked from the SELECT, like this: select word1,word2, dist(word1, word2) from myTable order by 3 where the '3' in the ORDER BY clause indicates the third column of the SELECT clause. That should be just as good as your original query. Rhino - Original Message - From: Horst Jäger [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, November 14, 2005 11:02 AM Subject: Let mysql calculate levenshtein-distance Hi, I have a nice little code snippet in pseudocode, which calculates the levenshtein-distance between 2 words. Is it possible to implement that code in mysql like in select word1,word2 from myTable order by dist(word1,word2 ) where dist is the function mentionend above which takes 2 strings and returns an int? Weird question??? :) Thanks Horst -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 269-1555 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can this SQL query be done with MySql?
Hi! I have a MySQL table with perid (person id), CaseId (the latter two fields are varchar). The persons (perid) can be on more than one case. They can get married and change caseids, or they can come of age and get their own caseid. So a given perid can be associated with multiple caseids. I'd like (using just sql) to create a list of those people (perids) who have changed cases. So the list would show a perid and then a bunch of cases that they have been on. My second question is, in MySql can we query a field just created in the query? So can you do something like this? sele count(*) as cnt, lastname from tablename where cnt1 Here we are using a created field name in the query itself. Is this possible? Thanks heaps! -Warren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Duplicate Insert Crashes Client
What type client are you using? With the C API you would test for the return value (0 or 1) and process accordingly. You could use 'INSERT IGNORE' syntax, but then you would not know what records failed (you could test for how many were inserted with mysql_info() using the C API). See Chap 6 around page 421 of the MySQL Reference Manual. I hope this helps... Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP 825 SE 47th Terrace Cape Coral, FL 33904 (239) 540-2626 Voice - Original Message - From: Bruce Martin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, November 13, 2005 3:58 PM Subject: Duplicate Insert Crashes Client Hello again, Ok new problem. If for some reason, my client tries to INSERT something to the database that is identical to a record already there, my client crashes. Is there an error I can trap for this? The DB does insert the new record. Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub Query
That's pretty hard to do; you haven't said what the query is trying to do and you haven't supplied definitions of the tables you are using or provided a few rows of sample data so that we could form a mental picture of what you are trying to accomplish. Also, you haven't stated what version of MySQL you are using; if you are using a version prior to 4.1.x, subqueries aren't possible at all. Nevertheless, assuming that subqueries *are* possible in your version of MySQL, I can make an educated GUESS. The WHERE clause of your subquery looks wrong, particularly messages.id. I would expect the WHERE to be constructed with one of the following: - with a constant, e.g. WHERE message_id = 10 - in the case of a correlated subquery, with another column name that refers back to the outer query, e.g. WHERE message_id = x.message_id (in this case, the correlation name, 'x' in this case, would also need to appear in the FROM clause of the OUTER query.) - in the case of a join, with another column name, e.g. WHERE a.message_id = b.message_id Your WHERE clause doesn't follow any of those patterns. I assume that's what MySQL doesn't like. Rhino - Original Message - From: Herman Scheepers [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, November 13, 2005 11:53 AM Subject: Sub Query Hi Could anyone help perhaps tell me why the following simple query containing a sub-query gives a syntax error. select 1 from messages where not exists ( select 1 from message_push_notifications where message_id = messages.id) Thanx Herman __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database IDs
Gleb Paharenko: So in you query both last_insert_id() should return the same value, which equals to the value that was set for an AUTO_INCREMENT column by the last INSERT or UPDATE query. Note that you have one query, which just inserts several rows, so during it is processed the returned value of last_insert_id() is constant, even if your bulk insert is changing an AUTO_INCREMENT field. That's what I thought, and on one server I have (4.0.21) it seems to work reliably that way. On another server (4.1.10a) that kind of insertions failed on a foreign key constraint. (I use InnoDB.) On a third box (4.1.14) it seems to work sometimes and fail sometimes. I think what happens when it fails is that the second last_insert_ID() gets the ID of the first row in the same query. I suppose I should file a bug report then? Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can this SQL query be done with MySql?
See remarks interspersed below Rhino - Original Message - From: WARVIN BARKER [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, November 14, 2005 10:44 AM Subject: Can this SQL query be done with MySql? Hi! I have a MySQL table with perid (person id), CaseId (the latter two fields are varchar). The persons (perid) can be on more than one case. They can get married and change caseids, or they can come of age and get their own caseid. So a given perid can be associated with multiple caseids. I'd like (using just sql) to create a list of those people (perids) who have changed cases. So the list would show a perid and then a bunch of cases that they have been on. What you're describing is called a many-to-many relationship by database designers. The way that many-to-many relationships are normally implemented are as follows. (I'm guessing you are dealing with some sort of welfare administration system so that will be what I show in my examples). Person Table (primary key = PerID) PerIDFirstNameLastNameBirthdate... (other columns about the person) P001 MarySmith 1960-01-01 P002 FredSmith 1980-01-01 P003 Elaine Jones 1982-01-01 Case Table (primary key = CaseID) CaseID... (other columns about the case) C001 C002 Person_Case Table (primary key = PerID *and* CaseID) PerIDCaseID... (other columns about this particular person and case) P001 C001 P001 C002 P002 C001 P003 C002 If I were in your situation, I'd probably store the start date and end date for the person's association with the case (and maybe the reasons for starting and ending the association) in the Person_Case table. For example: PerIDCaseIDStartDateStartReason EndDateEndReason P001 C001 1978-01-01 Quit school, no work - P002 C001 1980-01-01 Child born 1998-01-01 legally adult P002 C002 1998-01-01No work -- P003 C002 2000-01-01Married, no work available 2003-01-01 got job In this example, we have 4 rows in the Person_Case table: 1. Mary Smith went on welfare when she turned 18, quit school, and couldn't find work. She has never found work and remain on welfare now (the '-' is a common notation for 'null', meaning 'unknown or not applicable'). 2. In 1980, when she was 20, Mary had a son, Fred. He was associated with her case until he turned 18, then he was detached from the case because he was now too old to be on his mother's case. 3. Fred Smith is now 18 and gets his own case number. (Let's assume he can't find work either). 4. Fred Smith marries Elaine Jones in 2000. She is added to case C002 but finds a job in 2003, ending her association with the case. Now, when you want to know what cases a given person has been associated with, you simply query the Person_Case table. If you need to determine more information about the person or case, just join those tables to Person_Case. My second question is, in MySql can we query a field just created in the query? So can you do something like this? sele count(*) as cnt, lastname from tablename where cnt1 Here we are using a created field name in the query itself. Is this possible? Certainly! You wouldn't do it quite that way though; you are not allowed to have that formulate the query the way your example shows (at least not in DB2, the database I use most; I'm pretty sure the same rule applies to MySQL). To accomplish what you want, you would do something like this: select PerID, count(*) from Person_Case group by PerID having count(*) 1 Explanation: For each different person in the Person_Case table, determine the number of rows for that case, which is the number of cases that are (or have ever been) associated with; only display that person's ID if he/she is associated with more than one case. [If you only want to show cases that the person is currently associated with, add WHERE conditions to ensure that the current date is between the start date and end date for that association. Something like this: select PerID, count(*) from Person_Case where current_date = StartDate and current_date = EndDate group by PerID having count(*) 1] -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Anyone use Snort and Acid?
I am posting to this list in hopes that someone might have a clue as to why I might be experiencing a problem since upgrading to 5.0.15. Setup is as follows: Snort v2.4.3 MySql v5.0.15 ACID v0.9.6b23 RedHat 7.2 Since upgrading MySql from 4.1.15 to 5.0.15, Acid does not display the signature properly. All it shows is a number - no description/link. I have tried re-creating the database in both MyISAM and InnoDB but no luck. The only thing I have not tried is to back-rev MySql as I am not sure what affect this will have on other db's. Any suggestions/ideas are greatly appreciated... Ed . . . . . . . . . . . . . . . . . . Randomly Generated Quote (125 of 1011): It must be a peace without victory Only a peace between equals can last. --Woodroe Wilson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: With Rollup wrong results
Noel Stratton: As you can see the summary total in the 'Total' column is incorrect. The summary total should be 36 but is spitting out 40. I am not even sure where it is getting 40. The only way to get 40 is if it is multiplying 5X8. Isn't that what you told it to do? ROUND(price*count(log.product), 2) AS 'Total' For the summary line that means Pick a price field at random (as there are several rows to choose from) and multiply it with the count of all the log.product fields. Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
Jeffrey G. Ubalde wrote: Good day list! I would just like to ask a somewhat not so intelligent question. What is the downside of indexing almost all of the fields in a table? Is it advisable? Indexes have to be built... so if you did that, for every query that alters the data many indexes will have to be written. A lot of extra overhead if they will never be used. Look to see where indexes are needed by the types of queries you are writing. Add a slow query log to my.cnf. This will give you a very good idea of where you might need some indexes. Then trace the queries to make sure the indexes you've created are being used. Cheers, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question
I would just like to ask a somewhat not so intelligent question. What is the downside of indexing almost all of the fields in a table? Is it advisable? Indexes have to be built They also consume disk space, so if you're tight on hard drives, they might put you over the top. -- Scott Noyes [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OT: Training Materials
Good Day Again. Anybody here who have conducted MySQL training before? I was wondering if somebody here have training materials/presentation they could share that we can use for internal training within our group. Any presentation related to SQL or RDBMS for that matter would greatly be appriciated. Im am planning to collate all information I can get hold of specially those coming from DB experts in the open source community. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OT: Training Materials
MySQL AB offers great training! http://www.mysql.com/training/ In the long run, getting training right from the true experts is going to be your best bang for your buck. Trust me, I know from experience on this one. Cory. Jeffrey G. Ubalde wrote: Anybody here who have conducted MySQL training before? I was wondering if somebody here have training materials/presentation they could share that we can use for internal training within our group. Any presentation related to SQL or RDBMS for that matter would greatly be appriciated. Im am planning to collate all information I can get hold of specially those coming from DB experts in the open source community. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OT: Training Materials
Cory, We would love to take that training. But unfortunately, management cant afford that. (even if we think they can :) there are no budgets alloted for that.) The only thing we have is bandwidth, our peers and forums (I have been lurking here for a while now and have learned a lot already). Anyways, I dont wanna sound so miserable.. hehehe I know someday I can attend to the official MySQL training, but for now.. I think we would settle for open resources. Regards, Cory @ SkyVantage wrote: MySQL AB offers great training! http://www.mysql.com/training/ In the long run, getting training right from the true experts is going to be your best bang for your buck. Trust me, I know from experience on this one. Cory. Jeffrey G. Ubalde wrote: Anybody here who have conducted MySQL training before? I was wondering if somebody here have training materials/presentation they could share that we can use for internal training within our group. Any presentation related to SQL or RDBMS for that matter would greatly be appriciated. Im am planning to collate all information I can get hold of specially those coming from DB experts in the open source community. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone use Snort and Acid?
On Sun, Nov 13, 2005 at 08:56:12PM -0800, Ed Kasky wrote: Snort v2.4.3 ACID v0.9.6b23 I seem to recall hearing that ACID is deprecated; you might want to look around if there is a newer equivalent. SQUIL (http://sguil.sourceforge.net/) is supposed to be really good. -Jason Martin -- I have a watch cat! Just break in and she'll watch. This message is PGP/MIME signed. pgpTCfY0X9o7C.pgp Description: PGP signature
Re: Newbie Question
Just to amplify what Michael has said If you had a table with 50 columns and created a separate index for each of those columns, then you'd probably get some performance benefit from having those indexes. But you'd also have to absorb the following overhead: 1. For each new row that you created, you'd have to add an entry to each of the 50 indexes on the table. 2. For each row that you deleted, you'd have to remove an entry from each of the 50 indexes on the table. 3. For each row that you changed, you'd have to change the index entries on all columns that changed. 4. For each index you build, you'd have to pay for building that index and you'd have to pay for whatever storage that index used. That adds up to a lot of overhead, likely far more than the benefit you got by indexing the columns in the first place. Rather than putting indexes on every column database designers tend to put them in the following places: 1. A unique index on the primary key (which is required on every foreign key to enable Referential Integrity). 2. A unique or non-unique index (as appropriate) on each foreign key to help performance of Referential Integrity and joins, which are frequently on foreign keys. 3. A unique or non-unique index (as appropriate) on any other columns where query performance is critical. 4. A non-unique index on the column (or combination of columns) that you want as your clustering key. (The clustering key governs the physical sequence in which data is stored.) [NOTE: I mostly use DB2 and clustering is a big deal there but I'm not sure if it works the same way in MySQL.] Rhino - Original Message - From: Michael J. Pawlowsky [EMAIL PROTECTED] To: Jeffrey G. Ubalde [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, November 14, 2005 8:19 PM Subject: Re: Newbie Question Jeffrey G. Ubalde wrote: Good day list! I would just like to ask a somewhat not so intelligent question. What is the downside of indexing almost all of the fields in a table? Is it advisable? Indexes have to be built... so if you did that, for every query that alters the data many indexes will have to be written. A lot of extra overhead if they will never be used. Look to see where indexes are needed by the types of queries you are writing. Add a slow query log to my.cnf. This will give you a very good idea of where you might need some indexes. Then trace the queries to make sure the indexes you've created are being used. Cheers, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone use Snort and Acid?
. . . . . . . . . . . . . . . . . . Randomly Generated Quote (125 of 1011): It must be a peace without victory Only a peace between equals can last. --Woodroe Wilson Whoever generated this quote needs to use a spellchecker; the correct spelling is WOODROW Wilson. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
where is the mistake in this SQL statement?
{ this is a resend, forgot subject in original posting }
Hello, I am using MySQL 4.0.24 on OpenBSD 3.8.
I am having difficulty creating a SQL schema. My attempts contain
lines like:
CREATE TABLE users (
id PRIMARY KEY,
priority integer NOT NULL DEFAULT '7',
policy_id integer unsigned NOT NULL DEFAULT '1',
email varchar(255) NOT NULL,
fullname varchar(255) DEFAULT NULL,
local char(1),
KEY email (email)
);
Resulting in this error:
ERROR 1064 at line 1: You have an error in your SQL syntax. Check
the manual that corresponds to your MySQL server version for the
right syntax to use near 'PRIMARY KEY,
priority integer NOT NULL DEFAULT '7',
I think there is an incompatibility between my server version and
this statement but I can't figure out what it is. What should be
changed near PRIMARY KEY? This is for Spamassassin and amavisd-new.
__
Find your next car at http://autos.yahoo.ca
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: where is the mistake in this SQL statement?
You havent specified the datatype for the column 'id'.
sujay
-Original Message-
From: Peter Matulis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 15, 2005 10:57 AM
To: mysql@lists.mysql.com
Subject: where is the mistake in this SQL statement?
{ this is a resend, forgot subject in original posting }
Hello, I am using MySQL 4.0.24 on OpenBSD 3.8.
I am having difficulty creating a SQL schema. My attempts contain lines
like:
CREATE TABLE users (
id PRIMARY KEY,
priority integer NOT NULL DEFAULT '7',
policy_id integer unsigned NOT NULL DEFAULT '1',
email varchar(255) NOT NULL,
fullname varchar(255) DEFAULT NULL,
local char(1),
KEY email (email)
);
Resulting in this error:
ERROR 1064 at line 1: You have an error in your SQL syntax. Check the
manual that corresponds to your MySQL server version for the right syntax to
use near 'PRIMARY KEY,
priority integer NOT NULL DEFAULT '7',
I think there is an incompatibility between my server version and this
statement but I can't figure out what it is. What should be changed near
PRIMARY KEY? This is for Spamassassin and amavisd-new.
__
Find your next car at http://autos.yahoo.ca
--
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: Anyone use Snort and Acid?
I just discovered that BASE is a newer version of ACID based on some of the same code. It too returns single digit numbers where the signature description should be on the results screen. I will check out SQUIL. Thanks for the tip At 08:22 PM Monday, 11/14/2005, you wrote -= On Sun, Nov 13, 2005 at 08:56:12PM -0800, Ed Kasky wrote: Snort v2.4.3 ACID v0.9.6b23 I seem to recall hearing that ACID is deprecated; you might want to look around if there is a newer equivalent. SQUIL (http://sguil.sourceforge.net/) is supposed to be really good. -Jason Martin -- I have a watch cat! Just break in and she'll watch. This message is PGP/MIME signed. . . . . . . . . . . . . . . . . . . Randomly Generated Quote (127 of 1011): I've noticed that the press tends to be quite accurate, except when they're writing on a subject I know something about. (Keith F. Lynch) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: With Rollup wrong results
Noel Stratton wrote: I am running the query below: SELECT products.product, products.price, count( log.product ) AS 'Count', ROUND(price*count(log.product), 2) AS 'Total' FROM products LEFT JOIN log ON products.product= log.product GROUP BY product with rollup Here are the results: +--+---+---+---+ | product | price | Count | Total | +--+---+---+---+ | ATM Card | 3.00 | 2 | 6.00 | | Audio Response | 3.00 | 0 | 0.00 | | Check Card | 5.00 | 1 | 5.00 | | Courtesy Pay | 5.00 | 2 | 10.00 | | Draft with Direct Deposit| 5.00 | 0 | 0.00 | | Draft without Direct Deposit | 3.00 | 0 | 0.00 | | E-statement | 5.00 | 2 | 10.00 | | Gap | 20.00 | 0 | 0.00 | | MBI | 10.00 | 0 | 0.00 | | Membersonline| 5.00 | 0 | 0.00 | | New Account | 5.00 | 1 | 5.00 | | New Loan | 5.00 | 0 | 0.00 | | New MemBear Account| 5.00 | 0 | 0.00 | | NULL | 5.00 | 8 | 40.00 | +--+---+---+---+ 14 rows in set (0.00 sec) As you can see the summary total in the 'Total' column is incorrect. The summary total should be 36 but is spitting out 40. I am not even sure where it is getting 40. The only way to get 40 is if it is multiplying 5X8. I am running MySQL 4.1.15. Also the 'price' column has the wrong total. I am not really concerned with that total. I just want the summary total in the 'Total' column to be correct. Any suggestions? I think there are two issues here. First, there's the issue of how WITH ROLLUP behaves. It doesn't simply add values in columns. Instead, it gives a super-aggregate value, one in keeping with the kind of values in the column. Consider the following: CREATE TABLE rt (cat INT, val INT); INSERT INTO rt VALUES (1, 1), (1, 3), (1, 5), (1, 7), (2, 2), (2, 4), (2, 6), (2, 8), (3, 3), (3, 9), (3, 27); SELECT cat, MIN(val), MAX(val), COUNT(val), SUM(val), AVG(val) FROM rt GROUP BY cat WITH ROLLUP; +--+--+--++--+--+ | cat | MIN(val) | MAX(val) | COUNT(val) | SUM(val) | AVG(val) | +--+--+--++--+--+ |1 |1 |7 | 4 | 16 | 4. | |2 |2 |8 | 4 | 20 | 5. | |3 |3 | 27 | 3 | 39 | 13. | | NULL |1 | 27 | 11 | 75 | 6.8182 | +--+--+--++--+--+ 4 rows in set (0.01 sec) Notice the last line. You get the smallest of the MIN() values, the largest of the MAX() values, the average of the AVG() values, and the total of the COUNT() and SUM() values. (And yes, the manual completely fails to explain this.) This brings us to the second issue. How should price be interpreted in the ROLLUP row? It's not an aggregate function, so what should we do with the values in the price column. The answer to that isn't really defined, so I think mysql is free to pick any value. In practice, it appears to simply use the value from the immediately preceding row. In fact, technically, price shouldn't even be in the query, because it is neither a grouped column nor an aggregate function. Mysql allows this as a convenience when you know what you are doing http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html, but you have to be careful. I expect there is a single price per product in your products table, so adding price to the query would have been safe with a simple GROUP BY, but adding WITH ROLLUP complicates matters. In particular, I think your price*COUNT(log.product) is handled like this in the ROLLUP row: COUNT(log.product) is summed, producing 8, which is then multiplied by the last value of price found, 5, producing 40. Not what you had in mind. Instead of multiplying price by the number of rows found, how about adding price every time we find a row, like this: SELECT p.product, p.price, count(l.product) AS 'Count', ROUND(SUM(p.price), 2) AS 'Total' FROM products p JOIN log l ON p.product= l.product GROUP BY p.product WITH ROLLUP; +--+---+---+---+ | product | price | Count | Total | +--+---+---+---+ | ATM Card | 3.00 | 2 | 6.00 | | Check Card | 5.00 | 1 | 5.00 | | Courtesy Pay | 5.00 | 2 | 10.00 | | E-statement | 5.00 | 2 | 10.00 | | New Account | 5.00 | 1 | 5.00 | | NULL | 5.00 | 8 | 36.00 | +--+---+---+---+ 6 rows in set (0.00 sec) Notice that the ROLLUP row for Total is
Re: With Rollup wrong results
Hello. In my opinion, you have something wrong with you query in general. product.price field is in SELECT part and not in GROUP BY, so the results not-predictable. See: http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html Noel Stratton wrote: I am running the query below: SELECT products.product, products.price, count( log.product ) AS 'Count', ROUND(price*count(log.product), 2) AS 'Total' FROM products LEFT JOIN log ON products.product= log.product GROUP BY product with rollup Here are the results: +--+---+---+---+ | product | price | Count | Total | +--+---+---+---+ | ATM Card | 3.00 | 2 | 6.00 | | Audio Response | 3.00 | 0 | 0.00 | | Check Card | 5.00 | 1 | 5.00 | | Courtesy Pay | 5.00 | 2 | 10.00 | | Draft with Direct Deposit| 5.00 | 0 | 0.00 | | Draft without Direct Deposit | 3.00 | 0 | 0.00 | | E-statement | 5.00 | 2 | 10.00 | | Gap | 20.00 | 0 | 0.00 | | MBI | 10.00 | 0 | 0.00 | | Membersonline| 5.00 | 0 | 0.00 | | New Account | 5.00 | 1 | 5.00 | | New Loan | 5.00 | 0 | 0.00 | | New MemBear Account| 5.00 | 0 | 0.00 | | NULL | 5.00 | 8 | 40.00 | +--+---+---+---+ 14 rows in set (0.00 sec) As you can see the summary total in the 'Total' column is incorrect. The summary total should be 36 but is spitting out 40. I am not even sure where it is getting 40. The only way to get 40 is if it is multiplying 5X8. I am running MySQL 4.1.15. Also the 'price' column has the wrong total. I am not really concerned with that total. I just want the summary total in the 'Total' column to be correct. Any suggestions? Thank You, Noel Stratton Computer Specialist Members 1st Credit Union -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Let mysql calculate levenshtein-distance
Hello. Probably, yes. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html Horst Jäger wrote: Hi, I have a nice little code snippet in pseudocode, which calculates the levenshtein-distance between 2 words. Is it possible to implement that code in mysql like in select word1,word2 from myTable order by dist(word1,word2 ) where dist is the function mentionend above which takes 2 strings and returns an int? Weird question??? :) Thanks Horst -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Duplicate Insert Crashes Client
Hello. Ok new problem. If for some reason, my client tries to INSERT something Please could you answer, what kind of client do you mean. Is it your own client application or mysql command line client or something else? If it is yours and it is small, you can send the code to the list, include the 'CREATE' statement for you table and sample data. Provide information about operating system and MySQL version. Bruce Martin wrote: Hello again, Ok new problem. If for some reason, my client tries to INSERT something to the database that is identical to a record already there, my client crashes. Is there an error I can trap for this? The DB does insert the new record. Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General logging vs. bin logging
Hello. I'm a bit confused with the subject. Have you tried the General query log or not? See: http://dev.mysql.com/doc/refman/5.0/en/Query_log.html Clyde Lewis wrote: Hello, I'm trying to get a handle on who is making what changes in my database, but cannot seem to find user/account info in the bin-logs besides the actual update that was made. Is there a way in which MySQL logs not only the changes, but also the user account that updated a record in the database and how is this enabled? Any help would be greatly appreciated. Thanks in advance. CL -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub Query
Hello. Not enough information to make a conclusion. At least for me this query didn't return any error: mysql create table messages(id int); Query OK, 0 rows affected (0.02 sec) mysql create table message_push_notifications(message_id int); Query OK, 0 rows affected (0.01 sec) mysql select 1 from messages where not exists ( select 1 from message_push_notifications where message_id = messages.id); Empty set (0.00 sec) Please send 'CREATE' statements for you tables, MySQL version and exact error message. Herman Scheepers wrote: Hi Could anyone help perhaps tell me why the following simple query containing a sub-query gives a syntax error. select 1 from messages where not exists ( select 1 from message_push_notifications where message_id = messages.id) Thanx Herman __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: client/server differences
Hello. There should be only one issue, because the MySQL 5.0 server has a new implementation of the DECIMAL data type. See C API Changes at: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html I'm looking to use a RHEL4 server with standard RHEL4 packages to connect to a RHEL4 MySQL 5.0 server. I was curious if anyone knows of any known problems with a 4.1 client (the one provided with RHEL4) communicating to a 5.0 database? It connects fine, but wanted to be sure there were not any known issues.Stanton, Brian wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to reload my.cnf?
Hello. You can change some variables without restarting the server. See: http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html Tedy Aulia wrote: Hi All, I will need to change my.cnf in master machine, but I can't afford to restart MySQL server as the server has been used for heavy traffic databases. Can anyone tell me how to do it? Cheers, *Tedy Aulia* -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB Source
Hello. There is a good artice: http://www.onlamp.com/pub/a/php/2000/09/15/php_mysql.html Archives at lists.mysql.com are your best friends. See, say: http://lists.mysql.com/mysql/165229 Can someone please post some asp or php working with BLOB Files, and how to Get files into a Mysql Database,, in working woth PDF Files thanks Brian E Boothe wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
