Re: select datetime older than X weeks
From: Frank Bax I have a table with datetime field and I would like to select all data older than X weeks, where X is a variable in my php script. SELECT (.) WHERE `datetime_field` NOW() - INTERVAL (7*X) DAY This way you compare the datetime field with a constant (the expression results in a constant), so MySQL can use an index if there is one for the datetime field. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: big table corruption
matt_lists wrote: every few days I get one of these MySql: Can't open file: 'mrf.MYI' (errno: 145) For more information, see Help and Support Center at http://www.mysql.com. Checking table: './db/mrf' For more information, see Help and Support Center at http://www.mysql.com. Recovering table: './db/mrf' For more information, see Help and Support Center at http://www.mysql.com. always recovers fine I guess, nothing gets wrote to the .err file 3 more of the same table crashing last night Have a look at your /var/log/messages for messages around the time the table crashes . It really should not crash this much. Is there a quota system on your box ? I once had a table marked corrupt and though I tried hard to find where the problem is, I was not able to. Eventually, it turned out that the user had exceeded the quota set for her. -- Raj Shekhar, System Administrator Media Web India http://www.netphotograph.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Trouble w/ mysqldump (images attached)
Hi there, [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote on Thursday, January 06, 2005 7:19 PM: Thanks for the reply! I show the following information for my DB, and shows the same for both the 3.23 DB And the 4.18a DB Field Type Collation avatardata mediumtext latin1_swedish_ci It'd be better to use a BLOB, I think, because this would avoid the translation issues. Other alternatives worth pursuing: * Add the BINARY flag to the field - avatardata MEDIUMTEXT BINARY, for example. * Use a different collation, preferably one that's not case insensitive (the 'ci' part of the collation) * Use latin1_bin on the new DB (and you might need to use _latin1_bindata instead of just data when doing inserts) http://dev.mysql.com/doc/mysql/en/Charset-literal.html * Force the collation type, using the COLLATION keyword, as in http://dev.mysql.com/doc/mysql/en/Charset-collate.html I pasted a data table from the bad avatar and the good avatar to a file differential program, there was no differential at all that the system found.. If you want to send me (directly, rather than via the list) one of the records, as taken from both the bad and good versions, I could take a look for you. The binary files differ, so I'm not too sure what data is being used when you did the paste-and-compare. I'm not too sure where or what to do to change this information? Do you mean that I recompile MySQL using different ./configure commands? Shouldn't need to recompile, as long as you have the right charactersets available (which should be the default, I think). cheers, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Displaying Data for Each Day of the Year/Week
I think mine is a fairly simple question. In fact, I
think my script may just need a simple tweak. It's
also possible that my problem relates to displaying
the data with PHP, but I want to make sure I've got my
MySQL table set up correctly.
Anyway, I've collected historic events for every day
of the year - including multiple events for some days.
Now I want to create a web page that will display
events that occurred on January 1 (various years) when
it's January 1. When the clock hits midnight, it will
display events that occurred on January 2.
So I created a database table named gzcalendar with
three fields. The first field will simply be numbered
1-366, for every day of the year (including leap
years). As you can see, Cal_Date lists dates, while
events are listed under Cal_Event.
Cal_ID | Cal_Date | Cal_Event
1 | 1972-01-01 | A new species of whale was
discovered.
7 | 1898-01-07 | The dodo was declared extinct.
8 | 1972-01-08 | The first Earth Day was observed.
Then I used this script to try to display a sample:
?php
$date = date(Y-m-d);
$sql = SELECT Cal_Event FROM gzcalendar WHERE
Cal_Date = '$date';
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res))
{
print($row['Cal_Event'] . br /);
}
?
It doesn't work, and I'm guessing it's because of the
first line - $date = date(Y-m-d);
If someone visits my website on January 7, 2005
(2005-01-07), then they're not going to connect with
an event that occurred on January 7, 1888, right?
It seems to me I want something like this:
$date = date(m-d);
So if someone visits my website on January 7, they'll
see an event that occurred on January 7, regardless of
the year.
But I haven't yet figured out exactly how to do that?
If I can at least get my table set up correctly, then
I can get some advice for displaying it from a PHP
forum. I also want to learn how to display data for
each day of the week - Sunday, Monday, etc.
Thanks!
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble w/ mysqldump (images attached)
Hi, [EMAIL PROTECTED] schrieb: Hi Tom, Thanks for the reply! I show the following information for my DB, and shows the same for both the 3.23 DB And the 4.18a DB Field Type Collation avatardata mediumtext latin1_swedish_ci I pasted a data table from the bad avatar and the good avatar to a file differential program, there was no differential at all that the system found.. that seems to point towards a client issue. Which client do you use to look at the atachments (I think I have heard about problems with php and 4.1.x on this list recently)? As a further test I would suggest that you take the data table (.myd file?) from the 4.1.8 db and copy it into a __test__ 3.23 db replacing the data table there (it's myisam isn't it?). See if the avatars are ok when you read them from the 3.23 db. Regards, Frank. I'm not too sure where or what to do to change this information? Do you mean that I recompile MySQL using different ./configure commands? Thanks Tom! [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote on Thursday, January 06, 2005 4:57 PM: Sorry, forgot the attachments. These are the same exact two avatars from the same user, using my 3.23 backup, for the good avatar, then the 4.18 bad avatar Looks like a character set issue - what's the column type, BLOB or TEXT or something in between? This could be due to the server converting UTF-8 into a different character set. Characters such as 0x8F (143 decimal) and 0x8D are being converted into 0x3F, which is ? and often indicates that the character does not exist in the target collation. Basically, MySQL is treating the content as text, and replacing characters which it doesn't understand with ?. Try using a different collation or character set, and importing again? Unfortunately, the conversion is not reversible - a set of characters have been replaced with a single character, so although the image is the same binary size, some of the data has been permanently lost unless you can restore from the backup. cheers, Tom In a message dated 1/6/2005 12:48:28 PM Eastern Standard Time, Tom Molesworth [EMAIL PROTECTED] writes: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote on Thursday, January 06, 2005 4:57 PM: Sorry, forgot the attachments. These are the same exact two avatars from the same user, using my 3.23 backup, for the good avatar, then the 4.18 bad avatar Looks like a character set issue - what's the column type, BLOB or TEXT or something in between? This could be due to the server converting UTF-8 into a different character set. Characters such as 0x8F (143 decimal) and 0x8D are being converted into 0x3F, which is ? and often indicates that the character does not exist in the target collation. Basically, MySQL is treating the content as text, and replacing characters which it doesn't understand with ?. Try using a different collation or character set, and importing again? Unfortunately, the conversion is not reversible - a set of characters have been replaced with a single character, so although the image is the same binary size, some of the data has been permanently lost unless you can restore from the backup. cheers, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slave integrity.
Folks, I'm doing replication (one master, two slaves) with MySQL 4.0.23. I'm really happy with it. Although, every once in a while, the slaves get desync'ed, one at a time (the DB is 6GB large, and has 140 tables). I can't explain why this desync happens, but this wouldn't be a problem if I had a way (smart and trustable) to verify the integrity of the slave. Does anyone know how I can accomplish this? Today I'm doing a thousand queries on both servers (master/slave) to compare the results, but this keeps my servers unavailable temporarily (have to flush tables with read lock). Any thoughts on this? Thanks a lot! Best regards, RV Tec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow InnoDB Delete Transaction
Hi, Since we upgraded from MySql 3.20 to 4.1 innodb seems to have become slower. I'd like to understand the innodb status a bit better. Here is a the slow innodb transaction ---TRANSACTION 0 2599083795, ACTIVE 72659 sec, process no 12324, OS thread id 1138606116 estimating records in index range mysql tables in use 1, locked 1 1527743 lock struct(s), heap size 127790400, undo log entries 127362260 MySQL thread id 8885936, query id 78269607 10.168.80.166 web init delete from negotiated_classes_cache where id=110495925522553001 Trx read view will not see trx with id = 0 2599083796, sees 0 2599083791 What exactly does this mean ? And why would this query take so long ? In this table Id is the primary key. The machine that this is running on is a Dell quad Xeon and the data is stored on a SAN. The machine is not showing high load at all. I'd be grateful for your advice. Please let me know if I should provide any more information. Regards, Marvin Wright Flights Developer Lastminute.com [EMAIL PROTECTED] +44 (0) 207 802 4543 ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
RE: Displaying Data for Each Day of the Year/Week
[snip]
Cal_ID | Cal_Date | Cal_Event
1 | 1972-01-01 | A new species of whale was
discovered.
7 | 1898-01-07 | The dodo was declared extinct.
8 | 1972-01-08 | The first Earth Day was observed.
Then I used this script to try to display a sample:
?php
$date = date(Y-m-d);
$sql = SELECT Cal_Event FROM gzcalendar WHERE
Cal_Date = '$date';
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res))
{
print($row['Cal_Event'] . br /);
}
?
$monthDay = date(m-d);
$sql = SELECT Cal_Event FROM gzcalendar WHERE SUBSTRING(Cal_Date, 6, 5)
= '$monthDay';
CAUTION! MySQL SUBSTRINGs start counting strings at '1', PHP and othe
langauges usually count from '0'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Does it matter?
Please remove me from your email distribution. Best Regards, Steven Grosch [EMAIL PROTECTED] TEK LLC 610 260 9899 -Original Message- From: mysql@lists.mysql.com [mailto:[EMAIL PROTECTED] Sent: Friday, January 07, 2005 12:05 AM To: [EMAIL PROTECTED] Subject: Does it matter? You have written a very good text, excellent, good work! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Does it matter?
{snip]
Please remove me from your email distribution.
[/snip]
Do you not see an unsubscribe link at the bottom of this e-amil?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: creating first table
[snip] How can I list the rows and columns. [/snip] http://dev.mysql.com/doc/mysql/en/SELECT.html --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MS Access OLEDB/object type to MySQL Blob
I have searched the whole wide world (www) for months and have not found a converter that properly convert a MS Access image (gif, jpeg etc) to MySQL blob. How do I solve the problem, please? I am now frustrated. I am using Java with MySQL. Ant - Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more.
Re: creating first table
Gerald Preston [EMAIL PROTECTED] wrote on 01/07/2005 01:02:04 AM: Hi! Running mysql on a XP command line. Doing dhow databases list the following; Database Club Mysql Test I did a grant all user id; How can I list the rows and columns. One of these may help: SHOW CREATE TABLE ... or SHOW COLUMNS ... http://dev.mysql.com/doc/mysql/en/SHOW_CREATE_TABLE.html http://dev.mysql.com/doc/mysql/en/SHOW_COLUMNS.html I tried alter table club add ME varchar( 3)); and get : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right sysntax to use near 'gjwpp88 club add (ME varchar( 3 )) an line 1; http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html What have I missed? Perhaps you don't have access to the rather excellent indexed and searchable on-line manual? Here's a link for future reference: http://dev.mysql.com/doc/mysql/en/index.html If you prefer off-line reading, they also have it in downloadable formats (PDF, HTML tarball, etc). Thanks, Jerry NP! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Slow queries, need advice on how to improve; key_buffer?
Hi, I'm having a problem with slow query and parse times with a MySQL - PHP ecommerce application I am testing out. Also getting an error message with the key_buffer variable. I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I read in my Widenius - MySQL book that I should use the command set_variable=key_buffer=64M (or some other memory limit - I have 1.2 gig of ram on server). I also tried this instead of adding the above to my.cnf. However upon logging on as root in mySQL and running the command, I'm, getting a command not found error. Can anyone tell me what I'm doing wrong with the above two attempts? Or give any advice as to how to improve speed of accessing the mySQL database for my web application? I'm running MySQL 3.23.56 on a linux box. Thanks anyone, BD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow queries, need advice on how to improve; key_buffer?
[snip] I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. [/snip] You can set it without stopping mysql using the following: 'SET GLOBAL key_buffer_size = [size] * 1024 * 1024' where [size] is the size in Mb you want the key_buffer_size to be. You'll need to add it to your my.cnf if you want this setting to hold on a restart. Try adding 'set-variable = key_buffer = 64M' to your my.cnf. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: BD Sent: Friday, January 07, 2005 9:07 AM To: mysql@lists.mysql.com Subject: Slow queries, need advice on how to improve; key_buffer? Hi, I'm having a problem with slow query and parse times with a MySQL - PHP ecommerce application I am testing out. Also getting an error message with the key_buffer variable. I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I read in my Widenius - MySQL book that I should use the command set_variable=key_buffer=64M (or some other memory limit - I have 1.2 gig of ram on server). I also tried this instead of adding the above to my.cnf. However upon logging on as root in mySQL and running the command, I'm, getting a command not found error. Can anyone tell me what I'm doing wrong with the above two attempts? Or give any advice as to how to improve speed of accessing the mySQL database for my web application? I'm running MySQL 3.23.56 on a linux box. Thanks anyone, BD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow queries, need advice on how to improve; key_buffer?
Thanks Tom, OK I tried this, I must be doing something wrong, I entered in: SET GLOBAL key_buffer_size = 64 * 1024 *1024; and got the error: You have an error in your SQL syntax near 'key_buffer_size = 64 * 1024 *1024' at line 1 I must be doing something wrong. I tried both 64M and just 64. Should I also enter tick marks? I tried that too. BD -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Friday, January 07, 2005 11:20 AM To: BD Cc: mysql@lists.mysql.com Subject: RE: Slow queries, need advice on how to improve; key_buffer? [snip] I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. [/snip] You can set it without stopping mysql using the following: 'SET GLOBAL key_buffer_size = [size] * 1024 * 1024' where [size] is the size in Mb you want the key_buffer_size to be. You'll need to add it to your my.cnf if you want this setting to hold on a restart. Try adding 'set-variable = key_buffer = 64M' to your my.cnf. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: BD Sent: Friday, January 07, 2005 9:07 AM To: mysql@lists.mysql.com Subject: Slow queries, need advice on how to improve; key_buffer? Hi, I'm having a problem with slow query and parse times with a MySQL - PHP ecommerce application I am testing out. Also getting an error message with the key_buffer variable. I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I read in my Widenius - MySQL book that I should use the command set_variable=key_buffer=64M (or some other memory limit - I have 1.2 gig of ram on server). I also tried this instead of adding the above to my.cnf. However upon logging on as root in mySQL and running the command, I'm, getting a command not found error. Can anyone tell me what I'm doing wrong with the above two attempts? Or give any advice as to how to improve speed of accessing the mySQL database for my web application? I'm running MySQL 3.23.56 on a linux box. Thanks anyone, BD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full text Searching on C#, C++
I'm having a lot of trouble trying to get this to work. I've set the minimum word length to 2, restarted MySQL and rebuilt the indexes, but can't seem to get this to work. Is it because MySQL is not indexing # and ++? I can't seem to find a setting to alter this behavior. The only other thing I can think of is that I need to escape # and ++, but any of the normal escape character I've tried don't seem to do anything. Thanks -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
_blank
http://lists.mysql.com/[EMAIL PROTECTED] --- iBestMail, agora com POP3/SMTP e 120MB de espaço! Experimente: http://www.ibestmail.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow queries, need advice on how to improve; key_buffer?
[snip] I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I'm running MySQL 3.23.56 on a linux box. [/snip] Sorry, I didn't explain that you were probably not able to connect because prior to version 4.1 setting variable in my.cnf require that you add set-variable = before the setting ie. 'set-variable = key_buffer = 64M'. When you restarted mysql it probably exited on error. [snip] OK I tried this, I must be doing something wrong, I entered in: SET GLOBAL key_buffer_size = 64 * 1024 *1024; and got the error: You have an error in your SQL syntax near 'key_buffer_size = 64 * 1024 *1024' at line 1 [/snip] Sorry about this too, in 3.23 leave out the word global. If you can restart without a problem though, I would jest add the 'set-variable = key_buffer = 64M' line to your my.cnf file and restart mysql. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: BD Sent: Friday, January 07, 2005 9:07 AM To: mysql@lists.mysql.com Subject: Slow queries, need advice on how to improve; key_buffer? Hi, I'm having a problem with slow query and parse times with a MySQL - PHP ecommerce application I am testing out. Also getting an error message with the key_buffer variable. I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I read in my Widenius - MySQL book that I should use the command set_variable=key_buffer=64M (or some other memory limit - I have 1.2 gig of ram on server). I also tried this instead of adding the above to my.cnf. However upon logging on as root in mySQL and running the command, I'm, getting a command not found error. Can anyone tell me what I'm doing wrong with the above two attempts? Or give any advice as to how to improve speed of accessing the mySQL database for my web application? I'm running MySQL 3.23.56 on a linux box. Thanks anyone, BD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text Searching on C#, C++
Brent Baisley [EMAIL PROTECTED] wrote on 07/01/2005 17:03:36: I'm having a lot of trouble trying to get this to work. I've set the minimum word length to 2, restarted MySQL and rebuilt the indexes, but can't seem to get this to work. Is it because MySQL is not indexing # and ++? I can't seem to find a setting to alter this behavior. The only other thing I can think of is that I need to escape # and ++, but any of the normal escape character I've tried don't seem to do anything. I think Fulltext indexes only words which consist of A-Z, a-z, 0-9 and _ . I don't think is has any escape behaviour at all, and no control other than the word length. A feature that I would like would be to be able to define the set of characters which constitute a word. In my case, I would like to consider video timecodes (form hh:mm:ss:ff) to be words, so that I would like : to be regarded as a letter. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow queries, need advice on how to improve; key_buffer?
Hi Tom, OK thanks I just added the set-variable = key_buffer = 64M line to my my.cnf file and at least I got no errors and the MySQL server restarted OK and I got my test site running. The problem now is, this did not do anything to improve the query and parse times. I'm testing out an on line store which has about 12,000 product entries in it and about 300 product categories. Both the parse and query times are running over 12 seconds - that's just to bring up the front page of the store with the category - sub cat list. I'm sure there are lots of other switches in MySQl, do you have any other suggestions as to what I could do to reduce query times down to a reasonable 1-3 seconds? I just bought the book http://www.amazon.com/exec/obidos/tg/detail/-/0596003064/102-0076963-3409775 ?%5Fencoding=UTF8v=glance but it has not arrived yet. Any suggestions in the mean time? BD -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Friday, January 07, 2005 12:08 PM To: BD Cc: mysql@lists.mysql.com Subject: RE: Slow queries, need advice on how to improve; key_buffer? [snip] I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I'm running MySQL 3.23.56 on a linux box. [/snip] Sorry, I didn't explain that you were probably not able to connect because prior to version 4.1 setting variable in my.cnf require that you add set-variable = before the setting ie. 'set-variable = key_buffer = 64M'. When you restarted mysql it probably exited on error. [snip] OK I tried this, I must be doing something wrong, I entered in: SET GLOBAL key_buffer_size = 64 * 1024 *1024; and got the error: You have an error in your SQL syntax near 'key_buffer_size = 64 * 1024 *1024' at line 1 [/snip] Sorry about this too, in 3.23 leave out the word global. If you can restart without a problem though, I would jest add the 'set-variable = key_buffer = 64M' line to your my.cnf file and restart mysql. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: BD Sent: Friday, January 07, 2005 9:07 AM To: mysql@lists.mysql.com Subject: Slow queries, need advice on how to improve; key_buffer? Hi, I'm having a problem with slow query and parse times with a MySQL - PHP ecommerce application I am testing out. Also getting an error message with the key_buffer variable. I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I read in my Widenius - MySQL book that I should use the command set_variable=key_buffer=64M (or some other memory limit - I have 1.2 gig of ram on server). I also tried this instead of adding the above to my.cnf. However upon logging on as root in mySQL and running the command, I'm, getting a command not found error. Can anyone tell me what I'm doing wrong with the above two attempts? Or give any advice as to how to improve speed of accessing the mySQL database for my web application? I'm running MySQL 3.23.56 on a linux box. Thanks anyone, BD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow queries, need advice on how to improve; key_buffer?
[snip] The problem now is, this did not do anything to improve the query and parse times. I'm testing out an on line store which has about 12,000 product entries in it and about 300 product categories. Both the parse and query times are running over 12 seconds [/snip] This is probably related to not having proper indexes on your tables. If you post the query, and a 'SHOW CREATE TABLE [tablename]' for each table involved, someone maybe able to help you speed it up. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: BD Sent: Friday, January 07, 2005 11:21 AM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: RE: Slow queries, need advice on how to improve; key_buffer? Hi Tom, OK thanks I just added the set-variable = key_buffer = 64M line to my my.cnf file and at least I got no errors and the MySQL server restarted OK and I got my test site running. The problem now is, this did not do anything to improve the query and parse times. I'm testing out an on line store which has about 12,000 product entries in it and about 300 product categories. Both the parse and query times are running over 12 seconds - that's just to bring up the front page of the store with the category - sub cat list. I'm sure there are lots of other switches in MySQl, do you have any other suggestions as to what I could do to reduce query times down to a reasonable 1-3 seconds? I just bought the book http://www.amazon.com/exec/obidos/tg/detail/-/0596003064/102-0076963-3409775 ?%5Fencoding=UTF8v=glance but it has not arrived yet. Any suggestions in the mean time? BD -Original Message- From: Tom Crimmins Sent: Friday, January 07, 2005 12:08 PM To: BD Cc: mysql@lists.mysql.com Subject: RE: Slow queries, need advice on how to improve; key_buffer? [snip] I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I'm running MySQL 3.23.56 on a linux box. [/snip] Sorry, I didn't explain that you were probably not able to connect because prior to version 4.1 setting variable in my.cnf require that you add set-variable = before the setting ie. 'set-variable = key_buffer = 64M'. When you restarted mysql it probably exited on error. [snip] OK I tried this, I must be doing something wrong, I entered in: SET GLOBAL key_buffer_size = 64 * 1024 *1024; and got the error: You have an error in your SQL syntax near 'key_buffer_size = 64 * 1024 *1024' at line 1 [/snip] Sorry about this too, in 3.23 leave out the word global. If you can restart without a problem though, I would jest add the 'set-variable = key_buffer = 64M' line to your my.cnf file and restart mysql. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: BD Sent: Friday, January 07, 2005 9:07 AM To: mysql@lists.mysql.com Subject: Slow queries, need advice on how to improve; key_buffer? Hi, I'm having a problem with slow query and parse times with a MySQL - PHP ecommerce application I am testing out. Also getting an error message with the key_buffer variable. I tried to improve MySQL speed/performance by adding key_buffer=50M to my my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting my php-MySQL web application via http. When I removed above and then restarted MySQL the error goes away. I read in my Widenius - MySQL book that I should use the command set_variable=key_buffer=64M (or some other memory limit - I have 1.2 gig of ram on server). I also tried this instead of adding the above to my.cnf. However upon logging on as root in mySQL and running the command, I'm, getting a command not found error. Can anyone tell me what I'm doing wrong with the above two attempts? Or give any advice as to how to improve speed of accessing the mySQL database for my web application? I'm running MySQL 3.23.56 on a linux box. Thanks anyone, BD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave integrity.
Hey, I have some code that does a bunch of tests. I keep saying I'm going to release it, but I don't get around to it. My code does the following: - checks that the schema matches - check that keys match - check that foreign keys match - check that row counts match - checksums the whole table in 5000 row increments to verify the data matches (a lot faster than comparing row by row, but my code requires that each table has a numeric, single primary key, like int(11)) If you're interested, you could be an early beta tester :) On Fri, 7 Jan 2005 05:16:49 -0500 (EST), RV Tec [EMAIL PROTECTED] wrote: Folks, I'm doing replication (one master, two slaves) with MySQL 4.0.23. I'm really happy with it. Although, every once in a while, the slaves get desync'ed, one at a time (the DB is 6GB large, and has 140 tables). I can't explain why this desync happens, but this wouldn't be a problem if I had a way (smart and trustable) to verify the integrity of the slave. Does anyone know how I can accomplish this? Today I'm doing a thousand queries on both servers (master/slave) to compare the results, but this keeps my servers unavailable temporarily (have to flush tables with read lock). Any thoughts on this? Thanks a lot! Best regards, RV Tec -- 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: Slow queries, need advice on how to improve; key_buffer - zen-cart?
OK thanks Tom, The application I am using for the site is www.zen-cart.com so I'm not sure I can do anything about changing the table indexes because it is a pre written php-MySQL open source freeware script. I'm getting the query times directly from the application and not MySQL. However I could post a query I generate directly from MySQL, how could I do that? What would be the command I should use to generate query times from MySQL? For the SHOW CREATE TABLE, I have posted below the five tables that (I believe) are directly involved in generating a product and category list on the front page of my test store. I did not post all tables in the database because there are 97 tables total in the database, but I think these are the pertinent tables involved in slow query times; any suggestions that I get here I will definitely pass along to the zen cart developers. If there is something that I can do without changing the PHP code of the application and fix just with MySQL settings that would be great... zen_products |CREATE TABLE `zen_products` ( `products_id` int(11) NOT NULL auto_increment, `products_type` int(11) NOT NULL default '1', `products_quantity` float NOT NULL default '0', `products_model` varchar(32) default NULL, `products_image` varchar(64) default NULL, `products_price` decimal(15,4) NOT NULL default '0.', `products_virtual` tinyint(1) NOT NULL default '0', `products_date_added` datetime NOT NULL default '0001-01-01 00:00:00', `products_last_modified` datetime default NULL, `products_date_available` datetime default NULL, `products_weight` decimal(5,2) NOT NULL default '0.00', `products_status` tinyint(1) NOT NULL default '0', `products_tax_class_id` int(11) NOT NULL default '0', `manufacturers_id` int(11) default NULL, `products_ordered` float NOT NULL default '0', `products_quantity_order_min` float NOT NULL default '1', `products_quantity_order_units` float NOT NULL default '1', `products_priced_by_attribute` tinyint(1) NOT NULL default '0', `product_is_free` tinyint(1) NOT NULL default '0', `product_is_call` tinyint(1) NOT NULL default '0', `products_quantity_mixed` tinyint(1) NOT NULL default '0', `product_is_always_free_shipping` tinyint(1) NOT NULL default '0', `products_qty_box_status` tinyint(1) NOT NULL default '1', `products_quantity_order_max` float NOT NULL default '0', `products_sort_order` int(11) NOT NULL default '0', `products_discount_type` tinyint(1) NOT NULL default '0', `products_discount_type_from` tinyint(1) NOT NULL default '0', `products_price_sorter` decimal(15,4) NOT NULL default '0.', `master_categories_id` int(11) NOT NULL default '0', `products_mixed_discount_quantity` tinyint(1) NOT NULL default '1', PRIMARY KEY (`products_id`), KEY `idx_products_date_added` (`products_date_added`) ) TYPE=MyISAM | | zen_categories_description | CREATE TABLE `zen_categories_description` ( `categories_id` int(11) NOT NULL default '0', `language_id` int(11) NOT NULL default '1', `categories_name` varchar(32) NOT NULL default '', `categories_description` text NOT NULL, PRIMARY KEY (`categories_id`,`language_id`), KEY `idx_categories_name` (`categories_name`) ) TYPE=MyISAM | zen_categories | CREATE TABLE `zen_categories` ( `categories_id` int(11) NOT NULL auto_increment, `categories_image` varchar(64) default NULL, `parent_id` int(11) NOT NULL default '0', `sort_order` int(3) default NULL, `date_added` datetime default NULL, `last_modified` datetime default NULL, `categories_status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`categories_id`), KEY `idx_categories_parent_id` (`parent_id`), KEY `idx_sort_order` (`sort_order`) ) TYPE=MyISAM | | zen_products_description | CREATE TABLE `zen_products_description` ( `products_id` int(11) NOT NULL auto_increment, `language_id` int(11) NOT NULL default '1', `products_name` varchar(64) NOT NULL default '', `products_description` text, `products_url` varchar(255) default NULL, `products_viewed` int(5) default '0', PRIMARY KEY (`products_id`,`language_id`), KEY `products_name` (`products_name`) ) TYPE=MyISAM | | zen_products_to_categories | CREATE TABLE `zen_products_to_categories` ( `products_id` int(11) NOT NULL default '0', `categories_id` int(11) NOT NULL default '0', PRIMARY KEY (`products_id`,`categories_id`) ) TYPE=MyISAM | +- -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Friday, January 07, 2005 12:23 PM To: BD Cc: mysql@lists.mysql.com Subject: RE: Slow queries, need advice on how to improve; key_buffer? [snip] The problem now is, this did not do anything to improve the query and parse times. I'm testing out an on line store which has about 12,000 product entries in it and about 300 product categories. Both the parse and query times are running over 12 seconds [/snip] This is probably related to not having proper indexes on your tables. If
RE: Does it matter?
NO.
Best Regards,
Steven Grosch
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: Friday, January 07, 2005 8:41 AM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: RE: Does it matter?
{snip]
Please remove me from your email distribution.
[/snip]
Do you not see an unsubscribe link at the bottom of this e-amil?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does it matter?
click this
http://lists.mysql.com/[EMAIL PROTECTED]
Steven Grosch schrieb:
NO.
Best Regards,
Steven Grosch
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: Friday, January 07, 2005 8:41 AM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: RE: Does it matter?
{snip]
Please remove me from your email distribution.
[/snip]
Do you not see an unsubscribe link at the bottom of this e-amil?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Displaying Data for Each Day of the Year/Week
Thanks - that works great!
--- Jay Blanchard
[EMAIL PROTECTED] wrote:
[snip]
Cal_ID | Cal_Date | Cal_Event
1 | 1972-01-01 | A new species of whale was
discovered.
7 | 1898-01-07 | The dodo was declared extinct.
8 | 1972-01-08 | The first Earth Day was observed.
Then I used this script to try to display a sample:
?php
$date = date(Y-m-d);
$sql = SELECT Cal_Event FROM gzcalendar WHERE
Cal_Date = '$date';
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res))
{
print($row['Cal_Event'] . br /);
}
?
$monthDay = date(m-d);
$sql = SELECT Cal_Event FROM gzcalendar WHERE
SUBSTRING(Cal_Date, 6, 5)
= '$monthDay';
CAUTION! MySQL SUBSTRINGs start counting strings at
'1', PHP and othe
langauges usually count from '0'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
grrting MySQL to play nice with selinux
I got MySQL on RH Fedora Core 3 ... Used the MySQL on the default install and recently updated via yum to 3.23.58-14... I found it odd that off the install I could connect to mysql via command line client, but PHP and perl and other ways did not work.. I figured out that it was selinux doing this... I currently have it set to disabled, I would like to know if anyone knows how to get selinux and mysql to play nice? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with MySQL 4.1.8 Not starting under Fedora Core3
OK I am running Fedora Core3, Kernel kernel-2.6.9-1.724_FC3 along with MySQL-server 4.1.8-0.i386. No matter what I do MySQL the process will not start. This happens to me on a couple of boxes. One of the boxes which was upgraded from Fedora Core2 to Core3 does work with the exact same versions of everything. Here are the versions I installed via RPM: -rw-r--r-- 1 root root 3.1M Dec 23 10:03 MySQL-bench-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 5.3M Dec 23 10:05 MySQL-client-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 2.7M Dec 16 03:43 MySQL-devel-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 2.7M Dec 16 03:43 MySQL-embedded-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 15M Dec 23 10:23 MySQL-server-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 1.1M Dec 16 03:43 MySQL-shared-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 1.3M Dec 16 14:41 MySQL-shared-compat-4.1.8-0.i386.rpm When I start mysql with /etc/rc.d/init.d/mysql start I don't get the typical OK, and the mysql log shows this: /usr/sbin/mysqld, Version: 4.1.8-standard-log. started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id CommandArgument Making it look like everything is OK. Nothing is in /var/log/messages. I have read several posts on the web and found similar issues with no resolve. I have seen people try to run mysqld but of course get a security issue because it's not supposed to run as root, and that seems to be a popular problem which is not really the heart of the issue. When I su mysql and run mysqld I get this: 050107 14:09:12 [Warning] Asked for 196608 thread stack, but got 126976 mysqld: Can't create/write to file '/tmp/ib5ArcLz' (Errcode: 13) 050107 14:09:12 InnoDB: Error: unable to create temporary file; errno: 13 050107 14:09:12 [ERROR] Can't init databases 050107 14:09:12 [ERROR] Aborting 050107 14:09:12 [Note] mysqld: Shutdown complete I hope this helps as I have already created a flat spot on my head from banging it against the wall, and look forward to a more relaxing weekend. Any help appreciated... Joey
Re: Help with MySQL 4.1.8 Not starting under Fedora Core3
Permission problem: [EMAIL PROTECTED] santino]$ perror 13 Error code 13: Permission denied Check /tmp permission and check /usr/local/mysql/data/ user and group of this directory must be mysql:mysql if not: chown -R mysql:mysql /usr/local/mysql/data/ Santino PS: /usr/local/mysql is a symbolic link to mysql version you use: [EMAIL PROTECTED] santino]# file /usr/local/mysql /usr/local/mysql: symbolic link to `mysql-max-4.1.8-pc-linux-i686' At 14:15 -0500 7-01-2005, Joey wrote: OK I am running Fedora Core3, Kernel kernel-2.6.9-1.724_FC3 along with MySQL-server 4.1.8-0.i386. No matter what I do MySQL the process will not start. This happens to me on a couple of boxes. One of the boxes which was upgraded from Fedora Core2 to Core3 does work with the exact same versions of everything. Here are the versions I installed via RPM: -rw-r--r-- 1 root root 3.1M Dec 23 10:03 MySQL-bench-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 5.3M Dec 23 10:05 MySQL-client-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 2.7M Dec 16 03:43 MySQL-devel-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 2.7M Dec 16 03:43 MySQL-embedded-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 15M Dec 23 10:23 MySQL-server-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 1.1M Dec 16 03:43 MySQL-shared-4.1.8-0.i386.rpm -rw-r--r-- 1 root root 1.3M Dec 16 14:41 MySQL-shared-compat-4.1.8-0.i386.rpm When I start mysql with /etc/rc.d/init.d/mysql start I don't get the typical OK, and the mysql log shows this: /usr/sbin/mysqld, Version: 4.1.8-standard-log. started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id CommandArgument Making it look like everything is OK. Nothing is in /var/log/messages. I have read several posts on the web and found similar issues with no resolve. I have seen people try to run mysqld but of course get a security issue because it's not supposed to run as root, and that seems to be a popular problem which is not really the heart of the issue. When I su mysql and run mysqld I get this: 050107 14:09:12 [Warning] Asked for 196608 thread stack, but got 126976 mysqld: Can't create/write to file '/tmp/ib5ArcLz' (Errcode: 13) 050107 14:09:12 InnoDB: Error: unable to create temporary file; errno: 13 050107 14:09:12 [ERROR] Can't init databases 050107 14:09:12 [ERROR] Aborting 050107 14:09:12 [Note] mysqld: Shutdown complete I hope this helps as I have already created a flat spot on my head from banging it against the wall, and look forward to a more relaxing weekend. Any help appreciated... Joey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
weird syntax error
When I use 4.0 on windows I don't get this error but now that i'm using 4.1.x I'm getting error 1064 , is BLOB depricated or something ??? Please help ! thanks in advance === Your MySQL connection id is 97 to server version: 4.1.8-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE TABLE demoCart ( - ProductID int primary key, - CategoryID int, - ModelNumber varChar(75), - ModelName varChar(250), - ProductImageThumb varchar(250), - ProductImage varchar(250), - UnitCost decimal(9,2), - Description BLOB, - ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 10 mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: weird syntax error
How about removing the comma before the close parenthesis at the end of the statement - or adding a Primary Key clause after the comma but before the close parenthesis? Rhino - Original Message - From: B Wiley Snyder [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, January 07, 2005 3:28 PM Subject: weird syntax error When I use 4.0 on windows I don't get this error but now that i'm using 4.1.x I'm getting error 1064 , is BLOB depricated or something ??? Please help ! thanks in advance === Your MySQL connection id is 97 to server version: 4.1.8-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE TABLE demoCart ( - ProductID int primary key, - CategoryID int, - ModelNumber varChar(75), - ModelName varChar(250), - ProductImageThumb varchar(250), - ProductImage varchar(250), - UnitCost decimal(9,2), - Description BLOB, - ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 10 mysql -- 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: Unique IDs
When I saw this message a few weeks ago, I *knew* MySQL had something for this, but I couldn't remember where I saw it, and I couldn't find it. Today I found it. Take a look at: http://dev.mysql.com/doc/mysql/en/Miscellaneous_functions.html and scroll down to UUID() Returns a Universal Unique Identifier (UUID) generated according to ``DCE 1.1: Remote Procedure Call'' (Appendix A) CAE (Common Applications Environment) Specifications published by The Open Group in October 1997 (Document Number C706). A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other. This was added in MySQL 4.1.2. You didn't mention the version you were using, but 4.1 is production now. Hope that helps!! j- k- On Monday 20 December 2004 05:33, Andrew Mull said something like: I'm working on a rather large database with many cross-linked tables currently using auto increment IDs. The system is primarily a web based system, however, there will be times that the system will be run as a stand alone server...meaning no internet connection is available. The question arises that if someone enters information to the database on the website, while others are entering information on the local database, what is the best way to merge the data? I would imagine that we would run into many duplicate auto increment IDs. I'm sure that for one table insert, this would not be a problem as I could store the SQL statement in a text file without the ID specified, and run it as a batch process on the live server when we get connectivity. But I don't have a handle on how to update the sub tables that have a FK pointer. Any ideas? Thanks! -Andy -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
logical OR query problem
I am having trouble with a query that gives me activities that have not been written up but if these activities are a party they whould only appear in the query if they have been held, hence date will not='-00-00' If I write the query as so, no non-party activities will show up because the foreign key P_ID will be null and not match the p.ID. SELECT a.description FROM activity a, party p WHERE a.write_up IS NULL AND a.P_ID=p.ID AND p.date!=-00-00; IF I allow for a.P_ID null with logical OR (like so )it's a mess SELECT a.description FROM activity a, party p WHERE a.write_up IS NULL AND ((a.P_ID=p.ID AND p.date!=-00-00) OR a.P_ID IS NULL)); Any help on how I can get this to work would be greatly appreciated. Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow queries, need advice on how to improve; key_buffer - zen -cart?
[snip] The application I am using for the site is www.zen-cart.com so I'm not sure I can do anything about changing the table indexes because it is a pre written php-MySQL open source freeware script. I'm getting the query times directly from the application and not MySQL. [/snip] You could turn on logging in mysql to see what the query is that is taking so long, then make sure the tables are properly indexed based on this. I would hope that this software has properly indexed the tables, but you can verify this on your own. Add the following to your my.cnf to enable the logging of slow queries. [mysqld] set-variable = long_query_time=2 log-long-format log-slow-queries = /var/log/mysqld.slow.log (or whatever file you want, just make sure the user mysqld is running as has write permissions to it.) --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: BD Sent: Friday, January 07, 2005 11:58 AM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: RE: Slow queries, need advice on how to improve; key_buffer - zen-cart? OK thanks Tom, The application I am using for the site is www.zen-cart.com so I'm not sure I can do anything about changing the table indexes because it is a pre written php-MySQL open source freeware script. I'm getting the query times directly from the application and not MySQL. However I could post a query I generate directly from MySQL, how could I do that? What would be the command I should use to generate query times from MySQL? For the SHOW CREATE TABLE, I have posted below the five tables that (I believe) are directly involved in generating a product and category list on the front page of my test store. I did not post all tables in the database because there are 97 tables total in the database, but I think these are the pertinent tables involved in slow query times; any suggestions that I get here I will definitely pass along to the zen cart developers. If there is something that I can do without changing the PHP code of the application and fix just with MySQL settings that would be great... zen_products |CREATE TABLE `zen_products` ( `products_id` int(11) NOT NULL auto_increment, `products_type` int(11) NOT NULL default '1', `products_quantity` float NOT NULL default '0', `products_model` varchar(32) default NULL, `products_image` varchar(64) default NULL, `products_price` decimal(15,4) NOT NULL default '0.', `products_virtual` tinyint(1) NOT NULL default '0', `products_date_added` datetime NOT NULL default '0001-01-01 00:00:00', `products_last_modified` datetime default NULL, `products_date_available` datetime default NULL, `products_weight` decimal(5,2) NOT NULL default '0.00', `products_status` tinyint(1) NOT NULL default '0', `products_tax_class_id` int(11) NOT NULL default '0', `manufacturers_id` int(11) default NULL, `products_ordered` float NOT NULL default '0', `products_quantity_order_min` float NOT NULL default '1', `products_quantity_order_units` float NOT NULL default '1', `products_priced_by_attribute` tinyint(1) NOT NULL default '0', `product_is_free` tinyint(1) NOT NULL default '0', `product_is_call` tinyint(1) NOT NULL default '0', `products_quantity_mixed` tinyint(1) NOT NULL default '0', `product_is_always_free_shipping` tinyint(1) NOT NULL default '0', `products_qty_box_status` tinyint(1) NOT NULL default '1', `products_quantity_order_max` float NOT NULL default '0', `products_sort_order` int(11) NOT NULL default '0', `products_discount_type` tinyint(1) NOT NULL default '0', `products_discount_type_from` tinyint(1) NOT NULL default '0', `products_price_sorter` decimal(15,4) NOT NULL default '0.', `master_categories_id` int(11) NOT NULL default '0', `products_mixed_discount_quantity` tinyint(1) NOT NULL default '1', PRIMARY KEY (`products_id`), KEY `idx_products_date_added` (`products_date_added`) ) TYPE=MyISAM | | zen_categories_description | CREATE TABLE `zen_categories_description` | ( `categories_id` int(11) NOT NULL default '0', `language_id` int(11) NOT NULL default '1', `categories_name` varchar(32) NOT NULL default '', `categories_description` text NOT NULL, PRIMARY KEY (`categories_id`,`language_id`), KEY `idx_categories_name` (`categories_name`) ) TYPE=MyISAM | zen_categories | CREATE TABLE `zen_categories` ( `categories_id` int(11) NOT NULL auto_increment, `categories_image` varchar(64) default NULL, `parent_id` int(11) NOT NULL default '0', `sort_order` int(3) default NULL, `date_added` datetime default NULL, `last_modified` datetime default NULL, `categories_status` tinyint(1) NOT NULL default '1', PRIMARY KEY (`categories_id`), KEY `idx_categories_parent_id` (`parent_id`), KEY `idx_sort_order` (`sort_order`) ) TYPE=MyISAM | | zen_products_description | CREATE TABLE `zen_products_description` ( `products_id` int(11) NOT NULL auto_increment, `language_id` int(11) NOT NULL default '1',
Re: Fixing the worst InnoDB corruption bug in 3 years - when
Bruce Dembecki wrote: At the 2004 Users Conference in Orlando in April there were two sessions on optimizing MySQL hosted by a MySQL staffer who's name eludes me for the moment. Peter Zaitsev I believe. He did some InnoDB performance tuning sessions. He told the assembled masses that in benchmarks he ran that innodb_file_per_table was somewhat faster than using the large innodb table space. I didn't get the impression it was like 50% faster or anything, but once finished optimizing indexes and so on any gains are likely to be in small pieces, but they all add up. In most environments you are correct, the performance improvement you might get from innodb_file_per_table is not going to be of a large magnitude. Naturally, the best way for you to determine any performance benefits of this or any other parameter tweaking is to benchmark your performance before and after adjusting the parameter. Cheers, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Slow queries, need advice on how to improve; key_buffer - zen-cart?
Hi Tom, Here's the query log - mysqld.slow.log... /usr/libexec/mysqld, Version: 3.23.58-log, started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id CommandArgument # Time: 050107 17:40:27 # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 446 Rows_examined: 446 use zencarttest; select configuration_key as cfgkey, configuration_value as cfgvalue from zen_configuration; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 76 Rows_examined: 76 select configuration_key as cfgkey, configuration_value as cfgvalue from zen_product_type_layout; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 2 Rows_examined: 2 select code, title, symbol_left, symbol_right, decimal_point, thousands_point, decimal_places, value from zen_currencies; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 3 Rows_examined: 3 select * from zen_get_terms_to_filter; # Time: 050107 17:40:28 # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 114 select layout_box_name from zen_layout_boxes where (layout_box_status=1 or layout_box_status_single=1) and layout_template ='classic' and layout_box_name='search_header.php'; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 1 select categories_id from zen_categories where categories_status=1 limit 1; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 21 select product_type_id from zen_product_types_to_category where category_id='4' and product_type_id='3'; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 21 select product_type_id from zen_product_types_to_category where category_id='421' and product_type_id='3'; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 21 select product_type_id from zen_product_types_to_category where category_id='426' and product_type_id='3'; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 21 select product_type_id from zen_product_types_to_category where category_id='26' and product_type_id='3'; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 21 select product_type_id from zen_product_types_to_category where category_id='29' and product_type_id='3'; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 21 select product_type_id from zen_product_types_to_category where category_id='30' and product_type_id='3'; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 21 select product_type_id from zen_product_types_to_category where category_id='31' and product_type_id='3'; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 21 select product_type_id from zen_product_types_to_category where category_id='34' and product_type_id='3'; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 21 select product_type_id from zen_product_types_to_category where category_id='204' and product_type_id='3'; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 21 select product_type_id from zen_product_types_to_category where category_id='43' and product_type_id='3'; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 21 select product_type_id from zen_product_types_to_category where category_id='46' and product_type_id='3'; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 21 select product_type_id from zen_product_types_to_category where category_id='52' and product_type_id='3'; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 21 select product_type_id from zen_product_types_to_category where category_id='346' and product_type_id='3'; # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 21 select
Re: logical OR query problem
Richard Reina [EMAIL PROTECTED] wrote on 01/07/2005 03:31:26 PM: I am having trouble with a query that gives me activities that have not been written up but if these activities are a party they whould only appear in the query if they have been held, hence date will not='-00-00' If I write the query as so, no non-party activities will show up because the foreign key P_ID will be null and not match the p.ID. SELECT a.description FROM activity a, party p WHERE a.write_up IS NULL AND a.P_ID=p.ID AND p.date!=-00-00; IF I allow for a.P_ID null with logical OR (like so )it's a mess SELECT a.description FROM activity a, party p WHERE a.write_up IS NULL AND ((a.P_ID=p.ID AND p.date!=-00-00) OR a.P_ID IS NULL)); Any help on how I can get this to work would be greatly appreciated. Richard You need an OUTER JOIN not the implicit INNER JOIN you form by using the comma to separate the table names SELECT a.description FROM activity a LEFT JOIN party p ON AND a.P_ID=p.ID WHERE a.write_up IS NULL AND (p.date!=-00-00 OR a.P_ID IS NULL) That will give you all records from activity that meets these conditions: a) it wasn't a party or b) it was a party and the party's date is not -00-00 Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Slow queries, need advice on how to improve; key_buffer - zen -cart?
[snip] # Time: 050107 17:40:41 # [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx] # Query_time: 13 Lock_time: 0 Rows_sent: 148 Rows_examined: 1567270 use zencarttest; select distinct m.manufacturers_id, m.manufacturers_name from zen_manufacturers m left join zen_products p on m.manufacturers_id = p.manufacturers_id where m.manufacturers_id = p.manufacturers_id and p.products_status= '1' order by manufacturers_name; [/snip] This appears to be the problem query. Looks like zen_products could use an index on (manufacturers_id, products_status), and zen_manufacturers could use an index on (manufacturers_id,manufacturers_name). You can try to add these indexes and run the query to see if it helps. You may want to do an EXPLAIN after adding the indexes to make see if it is using them. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
insert
hi all... i need some help. can somebody explain why is this insert/select not working? insert into the_db (place,address,number) values(stuff,stuff2,select this from that where one=two); i tried ()s around the 'select' too but it didn't work. thanks. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert
insert/select has a different syntax than what you are trying to use. try insert into table_name (place, address, number) select stuff, stuff2, this from that where one=two i may yet have the syntax wrong, but its close. kalin mintchev wrote: hi all... i need some help. can somebody explain why is this insert/select not working? insert into the_db (place,address,number) values(stuff,stuff2,select this from that where one=two); i tried ()s around the 'select' too but it didn't work. thanks. -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert
insert/select has a different syntax than what you are trying to use. try insert into table_name (place, address, number) select stuff, stuff2, this from that where one=two aha... this is it. thank you... and you can sellect more then one things from 'that': insert into table_name (place, address, number,number_too) select stuff,stuff2, this, this_too from that where one=two; thanks... i may yet have the syntax wrong, but its close. kalin mintchev wrote: hi all... i need some help. can somebody explain why is this insert/select not working? insert into the_db (place,address,number) values(stuff,stuff2,select this from that where one=two); i tried ()s around the 'select' too but it didn't work. thanks. -- Jeff Mathis, Ph.D.505-955-1434 Prediction Company[EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 -- 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]
''Lost connection to MySQL server during query'' on Stored Procedure
Hello, I just installed MySQL 5.0.2 (on Debian Sarge) and started to do some experimets with Stored Procedures. I very often get the error Lost connection to MySQL server during query when executing such a self created Procedure. Sometimes the same Precedure works fine, sometimes it results in that error without having modified the procedure in any way. The Procedure just does some normal Selects and inserts which themselfs should not lead to servercrash. Is it just that the new feature is under development or is there a chance to get it working better? Do I have to wait for coming MySQL-Releases? ;] Greetz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANT can't grant with a password?
I've read the sections on GRANT's and permissions, and done some googling, and still haven't found what I'm looking for. I have a user that has USAGE and GRANT global privs and all privs and GRANT on database rubric. However, when they try to run this query: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON rubric.* TO 'user'@'localhost' IDENTIFIED BY 'password'; They get the error ERROR 1044: Access denied for user 'user'@'host' to database 'mysql' They can log in just fine, so it is not a matter of host name. I found a post that seemed to allude to the fact that a user with GRANT could only create a new user via GRANT if there was not IDENTIFIED BY clause. (However, a user with write permissions to the mysql database could). I verified this to be the case when this query, GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON rubric.* TO 'user'@'localhost' run as the user in question, worked and created the user, albeit with no password. Is there a way for a user with GRANT privs to create a user *with* a password? j- k- -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Trouble w/ mysqldump (images attached)
I have reinstalled the v3 RPM again to my system, and used one of my original backups from that same version. In 3.23 there is no 'collation' field.. I wonder if that is the problem?? I am going to change it to latein1_bin to see what happens next.. Thanks! I'll let you know! In a message dated 1/7/2005 3:44:25 AM Eastern Standard Time, Tom Molesworth [EMAIL PROTECTED] writes: Hi there, [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote on Thursday, January 06, 2005 7:19 PM: Thanks for the reply! I show the following information for my DB, and shows the same for both the 3.23 DB And the 4.18a DB Field Type Collation avatardata mediumtext latin1_swedish_ci It'd be better to use a BLOB, I think, because this would avoid the translation issues. Other alternatives worth pursuing: * Add the BINARY flag to the field - avatardata MEDIUMTEXT BINARY, for example. * Use a different collation, preferably one that's not case insensitive (the 'ci' part of the collation) * Use latin1_bin on the new DB (and you might need to use _latin1_bindata instead of just data when doing inserts) http://dev.mysql.com/doc/mysql/en/Charset-literal.html * Force the collation type, using the COLLATION keyword, as in http://dev.mysql.com/doc/mysql/en/Charset-collate.html I pasted a data table from the bad avatar and the good avatar to a file differential program, there was no differential at all that the system found.. If you want to send me (directly, rather than via the list) one of the records, as taken from both the bad and good versions, I could take a look for you. The binary files differ, so I'm not too sure what data is being used when you did the paste-and-compare. I'm not too sure where or what to do to change this information? Do you mean that I recompile MySQL using different ./configure commands? Shouldn't need to recompile, as long as you have the right charactersets available (which should be the default, I think). cheers, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble w/ mysqldump (images attached)
Hi Dr. The avatars still show fine on 4.18a -- but the problem occurs when I actually do a dump and reimport the dump file. That's when something goes array.. Kinda weird if you ask me.. I wish that vBulletin wouldn't actually hard code the binary in a table, lol.. It's got me totally baffled! :) In a message dated 1/7/2005 4:02:04 AM Eastern Standard Time, Dr. Frank Ullrich [EMAIL PROTECTED] writes: Hi, [EMAIL PROTECTED] schrieb: Hi Tom, Thanks for the reply! I show the following information for my DB, and shows the same for both the 3.23 DB And the 4.18a DB Field Type Collation avatardata mediumtext latin1_swedish_ci I pasted a data table from the bad avatar and the good avatar to a file differential program, there was no differential at all that the system found.. that seems to point towards a client issue. Which client do you use to look at the atachments (I think I have heard about problems with php and 4.1.x on this list recently)? As a further test I would suggest that you take the data table (.myd file?) from the 4.1.8 db and copy it into a __test__ 3.23 db replacing the data table there (it's myisam isn't it?). See if the avatars are ok when you read them from the 3.23 db. Regards, Frank. I'm not too sure where or what to do to change this information? Do you mean that I recompile MySQL using different ./configure commands? Thanks Tom! [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote on Thursday, January 06, 2005 4:57 PM: Sorry, forgot the attachments. These are the same exact two avatars from the same user, using my 3.23 backup, for the good avatar, then the 4.18 bad avatar Looks like a character set issue - what's the column type, BLOB or TEXT or something in between? This could be due to the server converting UTF-8 into a different character set. Characters such as 0x8F (143 decimal) and 0x8D are being converted into 0x3F, which is ? and often indicates that the character does not exist in the target collation. Basically, MySQL is treating the content as text, and replacing characters which it doesn't understand with ?. Try using a different collation or character set, and importing again? Unfortunately, the conversion is not reversible - a set of characters have been replaced with a single character, so although the image is the same binary size, some of the data has been permanently lost unless you can restore from the backup. cheers, Tom In a message dated 1/6/2005 12:48:28 PM Eastern Standard Time, Tom Molesworth [EMAIL PROTECTED] writes: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote on Thursday, January 06, 2005 4:57 PM: Sorry, forgot the attachments. These are the same exact two avatars from the same user, using my 3.23 backup, for the good avatar, then the 4.18 bad avatar Looks like a character set issue - what's the column type, BLOB or TEXT or something in between? This could be due to the server converting UTF-8 into a different character set. Characters such as 0x8F (143 decimal) and 0x8D are being converted into 0x3F, which is ? and often indicates that the character does not exist in the target collation. Basically, MySQL is treating the content as text, and replacing characters which it doesn't understand with ?. Try using a different collation or character set, and importing again? Unfortunately, the conversion is not reversible - a set of characters have been replaced with a single character, so although the image is the same binary size, some of the data has been permanently lost unless you can restore from the backup. cheers, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BIG INNODB Problems solved.. I think
A few days ago I did the following: Turned off MySQL. Installed 4.1.8a, then I went into the directory where all of the database files are located. I moved everything having to do with INNODB tables and logs into another location for safekeeping. Then I started up the new version of MySQL. I had our developer basically start from scratch (using her stored text files)... so far, there have been no problems... We're keeping our fingers crossed! :-) J. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: adding automatic alert
--- Roger Baklund [EMAIL PROTECTED] wrote:
Aji Andri wrote:
Hi all,
I'm make an inventory goods database, in one of my
table I need to make an automatic alert when my
stock
reach it's limit (say for tires it's limit is 4),
can
I make an automatic alert for it so when it reach
it
limit I can have an alert may be a beep or a
dialog box ?
This is a typical task for the programming
language/tool you are using
to create your application. What you ask may look a
bit like a task for
a trigger, but triggers work serverside, and I
assume you want the beep
on the client. I really think you would be better of
just using a simple
script, running every 30 minutes or so. Something
like this (meta code):
cnt = select stock from goods where
goodstype='tires'
if (cnt 5):
beep()
send_email('[EMAIL PROTECTED]','Out of tires!')
(This script will of course keep beeping/sending
emails every 30 minutes
until you stop the script or increase the registered
stock above the
limit... )
--
Roger
(sorry Roger for mis-sending, I think it was send to
list, :-) )
__
Do you Yahoo!?
Yahoo! Mail - 250MB free storage. Do more. Manage less.
http://info.mail.yahoo.com/mail_250
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
