Re: innodb use outside of explicit transactions
Marc, - Original Message - From: Marc Slemko [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, August 23, 2003 7:19 AM Subject: innodb use outside of explicit transactions Suppose I have an innodb table in 4.0.14 and do: LOCK TABLE maggie INSERT INTO maggie values(123, 'simpson'); UNLOCK TABLES As soon as I issue LOCK TABLE, any transaction in progress is automatically committed. By what point is this INSERT guaranteed to be committed to disk (ie. redo log)? Is it: 1. before INSERT returns? because you have AUTOCOMMIT=1, the transaction is committed in MySQL-4.0.14 before the INSERT returns. There was a bug/'feature' and this was only fixed in 4.0.14: MySQL/InnoDB-4.0.14, July 21, 2003 ... Fixed a bug: if AUTOCOMMIT=1 then inside LOCK TABLES MySQL failed to do the commit after an updating SQL statement if binlogging was not on, and for SELECTS did not commit regardless of the binlogging state. 2. before UNLOCK TABLES returns? 3. before it is read by any separate transaction? 4. before any separate transaction that read this data is committed? 5. sometime, no guarantee? This would seem to violate transactional integrity of the new transaction if it had a foreign key reference. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fragmentation
Jeff, - Original Message - From: Jeff Mathis [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, August 23, 2003 12:20 AM Subject: Re: innodb and fragmentation does shutting down the database server and restarting it have the same effect? no. We were talking about table fragmentation in the tablespace, that is, in the ibdata files. But restarting mysqld has one nice side-effect: MySQL will calculate new index cardinality estimates for every InnoDB table it uses after the restart. The effect is the same as running ANALYZE TABLE (in MySQL versions 4.0.13, I think) on all InnoDB tables. That can help in optimization of some joins if your tables have changed a lot during the lifetime of the mysqld process. jeff Regards, Heikki Heikki Tuuri wrote: Per, I think a 'null' alter table operation: ALTER TABLE innodbtable TYPE=INNODB; does the defragmentation with just one build of the table. And I think it also preserves FOREIGN KEY constraints. Please test it! Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, foreign keys, and a hot backup tool for MySQL .. Subject: innodb and fragmentation From: Per Andreas Buer Date: Fri, 22 Aug 2003 09:39:58 +0200 Hi, We have an InnoDB database which is get quite fragmented. We defragment it about once a month, converting the table from innodb to myisam and back. After a defragmentation our database performance is more or less doubled. IO-strain is reduced with 50%. Would it be possible to have alter table foo no-op og alter table foo reindex or similar - so we could do this with only one conversion - not two? Are there any plans to implement index clustering or similar technology to battle this? (Would clustering help?) -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The 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]
Re: innodb use outside of explicit transactions
On Sat, 23 Aug 2003, Heikki Tuuri wrote: Marc, - Original Message - From: Marc Slemko [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, August 23, 2003 7:19 AM Subject: innodb use outside of explicit transactions Suppose I have an innodb table in 4.0.14 and do: LOCK TABLE maggie INSERT INTO maggie values(123, 'simpson'); UNLOCK TABLES As soon as I issue LOCK TABLE, any transaction in progress is automatically committed. By what point is this INSERT guaranteed to be committed to disk (ie. redo log)? Is it: 1. before INSERT returns? because you have AUTOCOMMIT=1, the transaction is committed in MySQL-4.0.14 before the INSERT returns. There was a bug/'feature' and this was only fixed in 4.0.14: Ahh, ok. This is where I was confused. Some of the wording of the documentation made me think that transactions were not allowed when you held a table lock, although I can't find anything specific in the docs that would imply that looking at them now. After looking at it more closely, I am mistaken and transactions work as normal when tables are locked, it is just that any open transaction is automatically committed when you lock or unlock a table. thanks for the clarification. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can't start mysqld
Hello I have installed mysql on my Linux redhat 7.2 System. when I try to lauch the mysqld ,the message is [ok] but when i launch service mysql status, i got the folowwing :mysql is dead but subsys is locked and I can't connect to my database Can you help me please Think you -- Faites un voeu et puis Voila ! www.voila.fr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
On Fri, Aug 22, 2003 at 01:16:48AM -0500, Hans van Harten wrote:
Rajesh Kumar wrote:
Peter Brawley unknowingly asked us:
Interestingly, use of date_add() and date_sub() on 'odd' dates such
as Feb 31 does produce sane results.
Subtract one from 2000 Feb 31, and you'll get 2000-03-01.
This is sane!!??
This is where Unix Timestamps come into action (and perhaps rescue)!
To be sure that we're storing a sane value in our DB, we could use:
SELECT unix_timestamp('2002-02-31');
which provides a correct result.
Some interesting results:
We could then probably eliminate all timestamps stored as 1969-12-31
as we know that they're illegal.
Uh, isn't it an signed integer, thus allowing date before 1970-01-01 too??
Anyway, all this led me towards is:
create database data_test ;
use data_test;
create table test3 (a date);
insert into test3 values (
from_unixtime(unix_timestamp('2002-102-31'),'%Y-%m-%d' ));
insert into test3 values (
from_unixtime(unix_timestamp('1996-02-31'),'%Y-%m-%d' ));
insert into test3 values (
from_unixtime(unix_timestamp('1996-67-31'),'%Y-%m-%d' ));
insert into test3 values ( from_unixtime(-2147483646));
select * from test3;
still resulting in a mash:
1970-01-01
1996-03-01
1970-01-01
1900-01-00
that comes with neither warnings nor errors reported ...
Error reports would have been nice, but why does your application supply
these incorrect dates anyway? Probably because some user typed them. And
what are you going to tell the user when you get a generic MySQL error
for an INSERT with many values? 'Oops, something went wrong'?
Just check the values in your application, like you should do for any
value coming from an external source. Then you can tell the user exactly
what went wrong for each field. It also prevents garbage or badly
formatted data from entering your database.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
Rajesh Kumar wrote:
Hans van Harten unknowingly asked us:
create database data_test ;
use data_test;
create table test3 (a date);
insert into test3 values (
from_unixtime(unix_timestamp('2002-102-31'),'%Y-%m-%d' ));
insert into test3 values (
from_unixtime(unix_timestamp('1996-02-31'),'%Y-%m-%d' ));
insert into test3 values (
from_unixtime(unix_timestamp('1996-67-31'),'%Y-%m-%d' ));
insert into test3 values ( from_unixtime(-2147483646));
select * from test3;
still resulting in a mash:
1970-01-01
1996-03-01
1970-01-01
1900-01-00
that comes with neither warnings nor errors reported ...
I don't like to organise my dates this way ...
Why don't you use some middleware to validate your dates?
MySql is not intented to check the dates. It is up to the programmer
to validate the dates, and make sure legit dates are being inserted.
Why not throw away all types and just store anything as a blob of bytes, no
more fuz about types at all, but in the programmers logic...
Programmer wil have to check everything and anything anyway, as even
create database TEST;
create table TEST (FUN int);
insert TEST (FUN ) value ( 1212121212121212121212.12 );
select * from TEST;
returns garbage (2147483647) with neither warnings nor errors !!!
HansH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: Replication
Sam, On Fri, Aug 22, 2003 at 08:37:33PM -0400, sam [EMAIL PROTECTED] wrote: So, you are saying that the fact that replication is not working when we re-connect is not a bug. Replication is all about passing data from one node (a master) to the next (the slave). I do not know what it is you want. Simply when we re-connect we expect it to replicate. what you did (took the slave off line by pulling the RJ45), what you wanted to happen (replication to occur), and what actually happened (nothing - it did not replicate) I am not look for support on how to set it up. This is a bug. I should be able to turn-off a slave and when it comes back up have it replicate with its master - correct? Sorry, I do not know why we are having a problem. I am simply stating the problem - if you need more, You need to tell what you need. See the section in the manual about replication features and problems: If connection to the master is lost, the slave will retry immediately, and then in case of failure every master-connect-retry (default 60) seconds. Because of this, it is safe to shut down the master, and then restart it after a while. The slave will also be able to deal with network connectivity outages. However, the slave will notice the network outage only after receiving no data from the master for slave_net_timeout seconds. So if your outages are short, you may want to decrease slave_net_timeout ; see section 4.5.7.4 SHOW VARIABLES. http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html#Replication_Features Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
On Sat, Aug 23, 2003 at 10:54:32AM +0200, Hans van Harten wrote: Fred van Engen wrote: On Fri, Aug 22, 2003 at 01:16:48AM -0500, Hans van Harten wrote: that comes with neither warnings nor errors reported ... Error reports would have been nice, but why does your application supply these incorrect dates anyway? Probably because some user typed them. And what are you going to tell the user when you get a generic MySQL error for an INSERT with many values? 'Oops, something went wrong'? Planned to dump it into MySQL and check only if MySQL croaked about it. Just check the values in your application, like you should do for any value coming from an external source. Then you can tell the user exactly what went wrong for each field. It also prevents garbage or badly formatted data from entering your database. My checks might not match those of (the next version of) MySQL and at that time the difference in thoughts will pass unnoticed ! I agree that MySQL should complain but I'm not sure it should fail. The problem is that MySQL has always behaved this way and is in many cases documented to do so. Some programs may expect MySQL to do clipping of large values (your example in another post) and will fail if this changes. Valid dates are clearly defined independently of MySQL. Just don't feed MySQL anything else. If the presentation changes (-MM-DD, DD-MM- or MM-DD-) that would need to be documented anyway. Valid ranges for any field are defined in your table definitions and the absolute limits are defined in the MySQL documentation. If MySQL should fail on a simple INSERT with out-of-bounds values, it should also fail when the out-of-bounds value is generated in a complex query involving expressions with fields (or subqueries). How would you know for which records an UPDATE or INSERT failed? Would you want it to fail the entire query and not just problematic records or even fields? That would hardly be possible with MyISAM tables and would need some kind of subtransactions in any transactioned tables. Partly failing is silly IMHO and failing completely is difficult. So I guess it should just warn you that you did something silly. If you use transactions, you can decide to abort or let MySQL decide that. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL running out of date
Fred van Engen wrote: On Sat, Aug 23, 2003 at 10:54:32AM +0200, Hans van Harten wrote: My checks might not match those of (the next version of) MySQL and at that time the difference in thoughts will pass unnoticed ! I agree that MySQL should complain but I'm not sure it should fail. The problem is that MySQL has always behaved this way and is in many cases documented to do so. Some programs may expect MySQL to do clipping of large values (your example in another post) and will fail if this changes. Preserving the good old installed base, I cannot agree more!! If MySQL should fail on a simple INSERT with out-of-bounds values, it should also fail when the out-of-bounds value is generated in a complex query involving expressions with fields (or subqueries). Right ... 2^66 insert test (FUN ) value ( '25' ); select * from test where fun2147483648*2147483648*16; return 0 records. How would you know for which records an UPDATE or INSERT failed? Would you want it to fail the entire query and not just problematic records or even fields? Ordinary, UPDATE or INSERT would do one record at a time. BTW INSERT -or REPLACE- do croak about misfits while using FKs and then do not process any field -and none of the other records, if you used an record set- That would hardly be possible with MyISAM tables and would need some kind of subtransactions in any transactioned tables. My plan was to use rollback (on Innodb) directly after croak, then check and report. Ultimately you could write your own rollback-ish mechanisme... ... hell, could even write my own RDBMS or re-invent wheels ;-( Anyway, thanks to all responding. HansH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading 3.23-4.0 under WinXP ?
Hi! I'm pretty green at this and I'm a bit clueless here, After reinstalling my system I upgraded from 3.23 to 4.0 of MySQL, then I moved my old dbs in there and most seems to work OK. Now PHPMyAdmin tells me my userbase is out of date and that I need to run mysql_fix_privilege_tables... HOW do I run this on my WinXP system? What else do I need to do? Is it DOs, should it run from within SQl? (Everything seems to work except my guestbooks after the reinstall). Please help and bare with my lack of knowledge! Best Wishes Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spam from this list
Hmm This is not nice.. I posted on this list, and within seconds I got spam from some phone company which was a reply to my post to the list. Aparently a company is subscribed and use posts here to build a list of valid addresses to spam... The offending mailer was: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spam from this list
are you getting spam or an autoresponder. i rarely post to this list so lets see what happens. Joe Baptista - only at www.baptista.god Whenever we attack, they retreat. When we pound them with missiles and heavy artillery, they retreat even deeper. But when we stopped pounding, they pushed to the airport for propaganda purposes.'' ... Muhammed Saeed al-Sahaf former Iraqi Information Minister On Sat, 23 Aug 2003, Thomas Andersson wrote: Hmm This is not nice.. I posted on this list, and within seconds I got spam from some phone company which was a reply to my post to the list. Aparently a company is subscribed and use posts here to build a list of valid addresses to spam... The offending mailer was: [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: Spam from this list
Joe Baptista wrote: are you getting spam or an autoresponder. i rarely post to this list so lets see what happens. Both, when I get a 'requested' salespitch I call it spam, no matter if it was auto or not. Seems I get it for every mail I post here. Best Wishes Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spam from this list
Strange, I never get those!! Marc - Original Message - From: Thomas Andersson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, August 23, 2003 3:41 PM Subject: Re: Spam from this list Joe Baptista wrote: are you getting spam or an autoresponder. i rarely post to this list so lets see what happens. Both, when I get a 'requested' salespitch I call it spam, no matter if it was auto or not. Seems I get it for every mail I post here. Best Wishes Thomas -- 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: Spam from this list
one doesn't even need to be subscribed to a list to get all the mail addresses. since all mails are archived and accessible from the web, a simple mail spider is sufficient. for this reason, i never use my private mail address for public postings of this kind. and as soon as i have my own mail server, i'll put it under a strict spam filter. ok, i fortunately don't get much spam for now, but you never know. -- Yves Goergen [EMAIL PROTECTED] Please don't CC me (causes double mails) -Ursprüngliche Nachricht- Von: Thomas Andersson [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Samstag, 23. August 2003 15:25 Betreff: Spam from this list Hmm This is not nice.. I posted on this list, and within seconds I got spam from some phone company which was a reply to my post to the list. Aparently a company is subscribed and use posts here to build a list of valid addresses to spam... The offending mailer was: [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: Spam from this list
Hi Thomas, I report this stuff always to [EMAIL PROTECTED] and 20 minutes later its gone ;-) Best regards Nils Valentin Tokyo/Japan 2003 8 23 22:25Thomas Andersson : Hmm This is not nice.. I posted on this list, and within seconds I got spam from some phone company which was a reply to my post to the list. Aparently a company is subscribed and use posts here to build a list of valid addresses to spam... The offending mailer was: [EMAIL PROTECTED] -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: My SQL Search
On Fri, 22 Aug 2003 23:28:18 + Nathan Simms [EMAIL PROTECTED] wrote: Hi, I'm currently using the following mysql statement to perform a search. Right now it is only returning the results which are found in the description field. How do I modify this statement so that it is searching the following fields: company_name, product_name, description. SELECT product_id, company_name, product_name, url, description FROM product, company WHERE product.product_id = company.company_id AND description regexp '#FORM.query#' try this one: SELECT product_id, company_name, product_name, url, description FROM product, company WHERE product.product_id = company.company_id AND description LIKE '%FORM.query%' AND company_name LIKE '%FORM.query%' AND product_name LIKE '%FORM.query%'; of course, you should use LIKE only when you're seeking for partial match. in other cases use company_name='Bla'. and don't forget about indexes. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spam from this list
Marc Storck wrote: Strange, I never get those!! I've gotten 3 after 3 posts so far, bet I'll soon get my 4:th... Best Wishes Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tellme Timestamp
On Fri, 22 Aug 2003 17:14:38 -0500 Dan J. Rychlik [EMAIL PROTECTED] wrote: Hello All, I am trying to find out how I can change my timestamp(14) to timestamp(8). use DATE fields instead. or use DATE_FORMAT() with TIMESTAMP to get only date. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ITS NOT SPAM - ITS AN AUTORESPONDER - WAS Re: Re: Spam from this list (fwd)
This is not spam - it's an autoreponder - so lets not get to excited here. Joe Baptista - only at www.baptista.god Who is this dog Franks in Qatar? ... Muhammed Saeed al-Sahaf former Iraqi Information Minister -- Forwarded message -- Date: 23 Aug 2003 09:37:30 EDT From: Telemobile Inc. [EMAIL PROTECTED] Reply-To: Autoresponder [EMAIL PROTECTED] To: Joe Baptista [EMAIL PROTECTED] Subject: Re: Re: Spam from this list Telemobile Inc - Wireless Communication Solutions Thank you for your request. Please go to http://www.telemobile.com/ruraltelephony/ to download your information. Your sales manager has been notified and will contact you shortly to review your requirements. If you need immediate information then please contact us via email, fax or by phone at the numbers below. Thank you again for your interest. Best Regards, Telemobile Inc. ~~ Telemobile Inc - Wireless Communication Solutions an ISO-9001 registered company 19840 Hamilton Ave; Torrance, CA 90502; USA Tel: +1-310-538-5100; Fax:+1-310-532-8526 [EMAIL PROTECTED] http://www.telemobile.com Wireless Voice, Fax and Data. Anywhere! ~~ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spam from this list
you should not bother the list with this nonsense - your concept of spam
is another persons concept of managing information. Just send them an
email to block autoreponder messages to this lists participants. If they
can;'t comply then complain to the list manager.
And this by definition is not spam. Spam is when you get something
unsolicited. You solicited this response by sending a message which went
to their MX. Once the message gets to them it's up to them to decide how
they respond back to you - and that is not spam.
There are better ways to deal with this then bother the list with spam
complaints. contact the moderator and see what they can do.
regards
joe
Joe Baptista - only at www.baptista.god
{:::{::{:{{:::{::{:}::}:::}}:}::}:::}
Why does a slight tax increase cost you two hundred dollars and a
substantial tax cut saves you thirty cents?
... Happy Thought for the day :-)
On Sat, 23 Aug 2003, Thomas Andersson wrote:
Joe Baptista wrote:
are you getting spam or an autoresponder. i rarely post to this list
so
lets see what happens.
Both, when I get a 'requested' salespitch I call it spam, no matter if it
was auto or not. Seems I get it for every mail I post here.
Best Wishes
Thomas
--
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: Spam from this list
On Sat, 23 Aug 2003, Thomas Andersson wrote: Marc Storck wrote: Strange, I never get those!! I've gotten 3 after 3 posts so far, bet I'll soon get my 4:th... Best Wishes Thomas Now you have good reason to complain - because thats not right. An autoresponder should only send out email once per email address. Repeated sends is the sign of some twit not knowing how to operate an autoresponder. So this is good reason for the list moderator to take them off the list. regards joe Joe Baptista - only at www.baptista.god RONALD REAGAN on WHY DID THE CHICKEN CROSS THE ROAD? .. What chicken? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR: 1130 Need help changing privileges
Hi all, I need help changing host privileges in mySQL server. I am trying to access mySQL server from a remote computer that doesn't have access rights. The online doc sucks; I can't get anything useful out of it. A step-by-step instruction on how to change it would be greatly appreciated. Thanks, Marvin
help installing DBI modules
I'm trying to install DBI perl modules on a SUN 8 box. I can't seem to find a make utilitity for building the components. Is it called something else or will I have to go out and install it?
Re: MySQL running out of date
On Sat, Aug 23, 2003 at 02:07:36PM +0200, Hans van Harten wrote: Fred van Engen wrote: On Sat, Aug 23, 2003 at 10:54:32AM +0200, Hans van Harten wrote: My checks might not match those of (the next version of) MySQL and at that time the difference in thoughts will pass unnoticed ! I agree that MySQL should complain but I'm not sure it should fail. The problem is that MySQL has always behaved this way and is in many cases documented to do so. Some programs may expect MySQL to do clipping of large values (your example in another post) and will fail if this changes. Preserving the good old installed base, I cannot agree more!! Well, the behaviour could change in a major release (5.0 ?) but not in a minor one (3.23.xx or 4.0.xx). If MySQL should fail on a simple INSERT with out-of-bounds values, it should also fail when the out-of-bounds value is generated in a complex query involving expressions with fields (or subqueries). Right ... 2^66 insert test (FUN ) value ( '25' ); select * from test where fun2147483648*2147483648*16; return 0 records. SELECT 2147483648*2147483648*4 returns 0. SELECT 2147483648*2147483648*2 returns -9223372036854775808. SELECT 2147483648*2147483648*2-1 returns 9223372036854775807. The same problem in most programming languages. What do other DBMS do and what do the SQL standards say? I really don't know. How would you know for which records an UPDATE or INSERT failed? Would you want it to fail the entire query and not just problematic records or even fields? Ordinary, UPDATE or INSERT would do one record at a time. INSERT INTO test(id, myint) VALUES (1,2147483647), (2,2147483648); The second is out-of-bounds. Should the first be revoked? INSERT INTO test(id, myint) VALUES (1,2147483646), (2,2147483647); UPDATE test SET myint = myint+1; INSERT INTO test(id, myint) VALUES (1,2147483646), (2,2147483647); INSERT into test2 SELECT id, myint+1 FROM test; The UPDATE and last INSERT are out-of-bounds for the second record. Should the update and insert of the first record be revoked? That's just too much work for current MyISAM tables. BTW INSERT -or REPLACE- do croak about misfits while using FKs and then do not process any field -and none of the other records, if you used an record set- Great. That's InnoDB, which could do the same for each of the earlier examples. People might expect that from transactioned tables. For other table types I guess it would be unrealistic. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[OT] Autoresponders (is Re: Spam from this list)
Joe Baptista wrote: Now you have good reason to complain - because thats not right. An autoresponder should only send out email once per email address. Repeated sends is the sign of some twit not knowing how to operate an autoresponder. Yea - and very well set up autoresponders should maintain a pool of addresses *not* to respond to such as mailing lists. BTW, did anyone around here get mail from CServe containing important email addresses such as support? I got from them these mails twice to three times or so. Just wondering... Jakob ^-- To Unix or not to Unix. That is the question whether 'tis nobler in the mind to suffer slings and arrows of vast documentation or to take arms against a sea of buggy OS and by raping the support lines end then? ; Contact: \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ \/Jakob Dölling \/EMail: mailto:[EMAIL PROTECTED]/ Treuerzipfel 13 ICQ #: 47326203 /\D-38678 Clausthal /\SMS #: +49-82668-8918663/\ /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\ /\Webmaster of http://www.bank-ic.de/ /\ \/\/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Named pipe vs TCP/IP speed
Hi: This surprised me! I have been connecting with named pipes (and telling others to do the same) on Win2k since I discovered them, because my queries (and time to connect) are most definitely *faster*. The client/server communication is about 25-30% slower using TCP/IP. Example: a simple SELECT COUNT(*) FROM table takes 0.3ms with TCP/IP compared to only 0.16ms with named pipes. Or, on a more complicated query, 2ms vs 1.5. Just being curios: How is possible to change the connection type? Do I need to compile MySQL? Thanks, Jakob ^-- To Unix or not to Unix. That is the question whether 'tis nobler in the mind to suffer slings and arrows of vast documentation or to take arms against a sea of buggy OS and by raping the support lines end then? ; Contact: \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ \/Jakob Dölling \/EMail: mailto:[EMAIL PROTECTED]/ Treuerzipfel 13 ICQ #: 47326203 /\D-38678 Clausthal /\SMS #: +49-82668-8918663/\ /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\ /\Webmaster of http://www.bank-ic.de/ /\ \/\/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE based on value in another table
How do I update a table to remove orphaned references to a second table? I've deleted rows in the second table, which has a unique auto_increment key. The first table now has references to keys that no longer exist. I need to update the first table, setting the value to NULL where the referenced key no longer exists. Something like: UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN table2.ID; The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go there. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help installing DBI modules
On Sat, Aug 23, 2003 at 10:42:45AM -0400, March, Kristopher (Contractor) (DSCR) wrote: I'm trying to install DBI perl modules on a SUN 8 box. I can't seem to find a make utilitity for building the components. Is it called something else or will I have to go out and install it? Is /usr/ucb/bin in your path? -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] Autoresponders (is Re: Spam from this list)
On Sat, 23 Aug 2003, Jakob [iso-8859-1] Dölling wrote: BTW, did anyone around here get mail from CServe containing important email addresses such as support? I got from them these mails twice to three times or so. Just wondering... i've been getting email from something called the oracle. regards joe baptista -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: differance between InnoDB and MyISAM tables ?
For example, does 'delayed' work with 'insert' and 'replace' like it does for ISAM and MyISAM tables? Creigh X-MindSpring-Loop: [EMAIL PROTECTED] Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] Delivered-To: mailing list [EMAIL PROTECTED] X-VirusChecked: Checked X-Env-Sender: [EMAIL PROTECTED] X-Msg-Ref: server-6.tower-37.messagelabs.com!1061653250!109767 X-StarScan-Version: 5.0.7; banners=.,-,- From: sanjay gupta [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: differance between InnoDB and MyISAM tables ? Date: Fri, 22 Aug 2003 04:45:22 -0500 X-Mailer: Internet Mail Service (5.5.2653.19) X-Spam-Check-By: lists.mysql.com X-Spam: False ; -3.0 / 5.0 Hi all , can anybody tell me the differance between the innidb and myisam tables types . sanjay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] Autoresponders (is Re: Spam from this list)
I've been getting autoresponders from [EMAIL PROTECTED] [EMAIL PROTECTED] (The Oracle) [EMAIL PROTECTED] On Saturday, August 23, 2003, at 12:41 PM, Joe Baptista wrote: BTW, did anyone around here get mail from CServe containing important email addresses such as support? I got from them these mails twice to three times or so. Just wondering... i've been getting email from something called the oracle.
Re: Mast-Master Replication
Hi, I wish I could use that more, but load data from master locks everything all at once, then you have to wait for the transfer, making it not a great idea to use on a busy live/big database. I can do a back up localy and then transfer the data with a lot less locked time. Still, the times I have used load data from master, it is pretty cool how fast it goes. If the database isn't live and large :) it is defiantly a cool way to do your setup. Thanks, Eric At 01:33 PM 8/20/03 -0500, Hans van Harten wrote: Jeremy Zawodny wrote: On Tue, Aug 19, 2003 at 01:52:26PM -0700, Sanya Shaik wrote: I am unable to find any information about master-master replication. I need to replicate 1 mysql server over to other as a standby master server. It's named circular master-slave ... http://www.mysql.com/doc/en/Replication_Features.html If the second server is merely standby, you probably want master/slave rather than master/master. Having a master available while rebuilding the primairy server allows 'load data from master' to rebuild the db. HansH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb doc in mysql.com
In mysql.com site on section 7.5.4.1 it says and I qoute If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all datafiles, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding `.frm' files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. If I do this, will it break my database ? I am still new with InnoDB concept Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: My SQL Search
I am searching for a specific company name that gets passed to this page. I tried adding the mysql statement: SELECT product_id, company_name, product_name, url, description FROM product, company WHERE product.product_id = company.company_id AND description LIKE '%FORM.query%' AND company_name LIKE '%FORM.query%' AND product_name LIKE '%FORM.query%'; It didn't work so I changed out the '%' to '#' and remove the ';' because this statement is going into a .cfm file so it now looks like: SELECT product_id, company_name, product_name, url, description FROM product, company WHERE product.product_id = company.company_id AND description LIKE '#FORM.query#' OR company_name LIKE '#FORM.query#' OR product_name LIKE '#FORM.query#' Didn't work either, I don't get an error just no result. But... when I use the following statement it works: SELECT product_id, company_name, product_name, url, description FROM product, company WHERE product.product_id = company.company_id AND company_name regexp '#FORM.query#' The problem is that I need my statement to search the product_name and description fields as well. However when I add them to my statement, it doesn't work??? The statment below does not work: SELECT product_id, company_name, product_name, url, description FROM product, company WHERE product.product_id = company.company_id AND company_name regexp '#FORM.query#' AND product_name regexp '#FORM.query#' AND description regexp '#FORM.query#' Also what about indexes? Thanks in advance for your help. Nathan On Fri, 22 Aug 2003 23:28:18 + Nathan Simms [EMAIL PROTECTED] wrote: Hi, I'm currently using the following mysql statement to perform a search. Right now it is only returning the results which are found in the description field. How do I modify this statement so that it is searching the following fields: company_name, product_name, description. SELECT product_id, company_name, product_name, url, description FROM product, company WHERE product.product_id = company.company_id AND description regexp '#FORM.query#' try this one: SELECT product_id, company_name, product_name, url, description FROM product, company WHERE product.product_id = company.company_id AND description LIKE '%FORM.query%' AND company_name LIKE '%FORM.query%' AND product_name LIKE '%FORM.query%'; of course, you should use LIKE only when you're seeking for partial match. in other cases use company_name='Bla'. and don't forget about indexes. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyODBC 3.51 + Windows Server 2000 = Memory Leak
We have a very high traffic mysql app running in ASP on Windows 2000. It connects to a Linux mysql server (4.0.12) via ADODB and MyODBC. It's leaking memory like crazy. We've implemented connection pooling and long timeouts on connections so as to minimize the number of connects closes, but that is only a band-aid on the problem. Does anyone know how to get MyODBC to run on Windows without leaking memory on open/close? Thanks for any help. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyODBC 3.51 + Windows Server 2000 = Memory Leak
We're running MyODBC 3.51 on Windows 2000 Ssrver with IIS5 and ColdFusion 5 applications and don't see any memory leaks. In a very hight traffic application, you'll want to keep connections open as long as possible anyway. There's also a MyODBC list that you might try. - Original Message - From: Jon Drukman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, August 23, 2003 1:26 PM Subject: MyODBC 3.51 + Windows Server 2000 = Memory Leak We have a very high traffic mysql app running in ASP on Windows 2000. It connects to a Linux mysql server (4.0.12) via ADODB and MyODBC. It's leaking memory like crazy. We've implemented connection pooling and long timeouts on connections so as to minimize the number of connects closes, but that is only a band-aid on the problem. Does anyone know how to get MyODBC to run on Windows without leaking memory on open/close? Thanks for any help. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Named pipe vs TCP/IP speed
- Original Message - From: Jakob Dölling Hi: This surprised me! I have been connecting with named pipes (and telling others to do the same) on Win2k since I discovered them, because my queries (and time to connect) are most definitely *faster*. The client/server communication is about 25-30% slower using TCP/IP. Example: a simple SELECT COUNT(*) FROM table takes 0.3ms with TCP/IP compared to only 0.16ms with named pipes. Or, on a more complicated query, 2ms vs 1.5. Just being curios: How is possible to change the connection type? Do I need to compile MySQL? First, you need to enable named pipes with enable-named-pipe in the [mysqld] section of my.ini. Then, just don't use 'localhost' (which uses TCP/IP on Windows) as the hostname in mysql_connect(). :-) Using '.' (just a period) specifies named pipes. But by using a blank/empty string for hostname, named pipes will be used if available, else TCP/IP. Note that 'localhost' on *nix is special and doesn't use TCP/IP, but the faster Unix sockets. But again, by just leaving the hostname blank, this will be used anyway. So I just leave the hostname blank when MySQL is on the local system to get the best connection available. It seems that an address is only needed to connect to a separate MySQL box. See also the first 2 paragraphs here in the manual: http://www.mysql.com/doc/en/Can_not_connect_to_server.html Hope that helps. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
linked db
Hi, is it possible to link databases within mysql? I have a access database that I need to import into mysql this db consists of 4 other dbs linked together sharing a common switchboard. Can I link the tables in one db to tables in another db? thanks Bob
Re: how to show all locks on a table?
At 10:33 AM 8/21/2003 -0700, Bennett Haselton wrote: [already posted to mailing.database.mysql newsgroup but not to list; sorry for cross-post] I found a way to do this before, but I didn't write down how I did it, so I don't remember it now. And I've searched http://www.mysql.com/doc/ in vain. What's the command to show all current locks on a database table? Did I commit some unforgivable faux pas in asking this question? :) I didn't think it was very obscure (even *I* figured it out at one point :) but I didn't write down how I did it, and now I can't figure out how to do it again). Not complaining, since it's free, it's just that usually I've seen much harder questions get answered pretty fast... Did somebody send a reply, and I didn't get it (my Web server, and possibly my email, was down for part of that day)? Or is it not possible to show current locks on a table, and maybe I'm mis-remembering what I saw, when I thought I'd found a way to do it? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search?
Pieter, If you use LIKE '%abc%' then this will match 'abc' 'abcs' 'ABC' 'DABCE' 'DABC' and 'ABCD'. It will not match 'AB'. In your case it may work better if you validate the words before you run your SELECT statement or make your fields more specific. For example, instead of a field called descr which can contain '3 bedroom' '3 bedrooms' '3 beds' or even '3beds' or 'butcher shop' (all of which are different), have a field called bedrooms and store 3 in it. Obviously this field is no good for a shop database, but if you are going to mix commercial and non-commercial property you could either have 2 tables or just keep redundant fields. Spelling mistakes: ORACLE has a SOUNDEX() function which returns the phonetic pronunciation of a word. This can help in fuzzy searches. I don't know of similar functions in mysql, but it may be easier to use codes instead: For example: Use drop down lists of valid options - then your searches will suceed. If this is your first database then you may want to avoid a full-blown search engine. Regards, Andy. Extraordinaire wrote: Hi I builded a little propert database. I added a search function to the site but have the following problem: I used SELECT * FROM table1 WHERE area ='province' and type like'%type_1%' and town like'%town_1%' and descr like '%descr_1%' and suburb like '%sub_1%' But it do not do the like thing. If I search for 3 bed and the actual data is 3 bedroom it gets the data, but if I search for 3 bedrooms (extra s) it do not get it or if I make spelling mistake I do not get a result either. So it seems that it rather search for the exact term instead of the like term. How can I get it to really search for only a phrase, part of a word or a spelling error? Regards Pieter Building extraordinary webs, www.extraordinaire.co.za -- 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]
utf8 support
MySql 4.1 should have utf8 charset support, I downloaded the alpha version but don't seem to have the utf8.xml file comes with it. Any one know how to get the needed charset support files? Thanks John
