Re: odd behaviour with auto_increment
On 9/26/2006 4:02 PM, Dan Buettner wrote: Jorrit, it's a known behavior, not a bug. Recent versions of MySQL will, when given a zero (0) as a value for an auto incrementing identity column, simply fill in the next auto incrementing value ... unless you flip a switch to specifically tell it not to do that ... see: http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html and look for NO_AUTO_VALUE_ON_ZERO HTH, Dan Dan, That explains it! I see the NO_AUTO_VALUE_ON_ZERO in the dump as well. Thanks! Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
odd behaviour with auto_increment
Dear list, I discovered something that seems to be odd behaviour. I have a basic table with one column set to auto_increment: mysql DESCRIBE basic_table; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | Id| int(11) | | PRI | NULL| auto_increment | | simple_column | varchar(11) | | | || +---+-+--+-+-++ 2 rows in set (0.00 sec) One of the values of `Id' was later changed from 1 to 0, resulting in the following output. mysql SELECT * FROM basic_table; ++---+ | Id | simple_column | ++---+ | 0 | foo | | 2 | bar | ++---+ 2 rows in set (0.00 sec) Now if I create a dump of the table, I get the following queries (and a few more uninteresting locking queries, which I won't paste here): CREATE TABLE `basic_table` ( `Id` int(11) NOT NULL auto_increment, `simple_column` varchar(11) NOT NULL default '', PRIMARY KEY (`Id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `basic_table` VALUES (0,'foo'),(2,'bar'); If I run this dump from the MySQL CLI with '\. dump.sql' I get the same table as with the previous SELECT. However, if I run these two commands from the dump directly on the mysql CLI, the table looks as follows: mysql SELECT * FROM basic_table; ++---+ | Id | simple_column | ++---+ | 1 | foo | | 2 | bar | ++---+ 2 rows in set (0.00 sec) Apparently MySQL thought I didn't know a Id-value for the row with 'foo' (because it was zero), so it made one up. This is kind of odd, because a dump query should always generate the same dataset. Am I doing anything wrong? Is this a known bug? - Jorrit P.S. For this test, I've used MySQL version 4.1.21-log. -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copying table fo other database
[EMAIL PROTECTED] wrote: Hi to all! I have to transfer copy of the one table from one to other database. I was working with phpMyAdmin. I exported table in sql file but, when tried to Import it to other database I got Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 806882 bytes) Any idea how to do this? Thanks for any help -afan Afan, The error you see is coming from PHP, which allows to grow one instance only this big. Apparently your SQL dump is quite big and apparently phpmyadmin is buffering it all in memory before executing. The way I see it, there are are three solutions: 1.) You set the memory limits high enough in php.ini (You don't want to do that.) 2.) You split the SQL dump in enough pieces to stay within the memory constraints of PHP. You then have to import every part seperately. 3.) Instead of using phpmyadmin, you connect with a mysql client and import the sql dump with the client. If you ask me, option 3 seems like the most reasonable and is by far the easiest. I hope that helps. -Jorrit -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
On 4/10/2006 8:50 PM, Yves Goergen wrote: On 10.04.2006 18:32 (+0100), Jorrit Kronjee wrote: I'm not entirely sure what you mean. Are `mysql4.mydomain' and `mysql5.mydomain' hostnames? Yes, as I have explained earlier in this thread. Hostnames resolve into IP adresses, which are used to connect to the MySQL server. MySQL doesn't care if you connect via a hostname or via an IP address. It's not virtual hosting like Apache does. - Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
Yves Goergen wrote: On 09.04.2006 23:40 (+0100), Jorrit Kronjee wrote: You seem to be best off with a setup where you've got the MySQL5 UNIX socket disabled, MySQL5 bound to one specific IP address, MySQL4 listening on 127.0.0.1 and a simple port forwarding rule to MySQL4. I'm missing the part to connect to MySQL 4.0 via mysql4.mydomain and to MySQL 5.0 via mysql5.mydomain... But I'll try to do it by restricting access to the primary hostname/IP for now. Yves, I'm not entirely sure what you mean. Are `mysql4.mydomain' and `mysql5.mydomain' hostnames? - Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restrict MySQL server 4/5 to single IP
Yves Goergen wrote: On 09.04.2006 01:03 (+0100), Eric Braswell wrote: Does that make sense? Did I misunderstand? That's exactly what I'm doing right now. In my test network: MySQL 4.0 - 192.168.0.32 (mysql4.myhost) MySQL 5.0 - 192.168.0.33 (mysql5.myhost) But what I wanted to do is: MySQL 4.0 - 192.168.0.32 and 127.0.0.1 MySQL 5.0 - 192.168.0.33 Yves, You seem to be best off with a setup where you've got the MySQL5 UNIX socket disabled, MySQL5 bound to one specific IP address, MySQL4 listening on 127.0.0.1 and a simple port forwarding rule to MySQL4. Then your clients won't have to change anything and they can migrate to MySQL5 when they're ready. Or am I mistaken? - Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
On 3/29/2006 2:10 PM, Jorrit Kronjee wrote: It seems you are running in to Bug #7209: http://bugs.mysql.com/bug.php?id=7209 This is fixed in 5.0.19 now. Best regards Mark Mark, Apparently so, thanks for the hint! We'll try to upgrade as soon as possible. I'll supply the mailing list with the results of the upgrade. Mark, We've been testing it over the weekend and it seems that the bugs we ran into before completely disappeared. Thanks for your support! Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to suppress NULL values in SELECT ... INTO OUTFILE ?
Cor, You can set the enclosing character with the ENCLOSED BY parameter. So something like ENCLOSED BY '' will remove those quotes. - Jorrit C.R.Vegelin wrote: Thanks Paul, Yes, I've tried IFNULL() to map NULL values to empty strings. But then I get 1;2;;4;;2;9 in stead of 1;2;;4;;2;9 So building CSV files with 1;2;;4;;2;9 output is not possible ? Maybe an idea to extend the FIELDS options to enable this ... Regards, Cor - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Saturday, April 01, 2006 6:11 PM Subject: Re: how to suppress NULL values in SELECT ... INTO OUTFILE ? At 13:29 +0100 4/1/06, C.R.Vegelin wrote: Hi everyone, I am struggling to make a CSV file, with rows like: 1;2;;4;;2;9 where NULL values are suppressed in the CSV file. I tried the following alternatives: a) Select ... Into Outfile ... Fields Terminated By ';' Escaped By '' Lines Terminated By '\r\n' ... but this generates output like: 1;2;NULL;4;NULL;2;9 b) Select ... Into Outfile ... Fields Terminated By ';' Lines Terminated By '\r\n' ... but this generates output like: 1;2;\N;4;\N;2;9 Any idea how to get CSV rows like: 1;2;;4;;2;9 ? Thanks for your time and effort. Regards, Cor You could use IFNULL() to map NULL values to the empty string: mysql set @x = null, @y = 1; Query OK, 0 rows affected (0.00 sec) mysql select ifnull(@x,''), ifnull(@y,''); +---+---+ | ifnull(@x,'') | ifnull(@y,'') | +---+---+ | | 1 | +---+---+ 1 row in set (0.00 sec) You'll need to apply this to each column that might contain NULL values. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting number days between 2 dates
Mike Blezien wrote:
Hello,
I'm sure this is a simple query but haven't come up with a good
approach. Need to get the number of days between two dates. IE: today's
date: (2006-04-01 - 2006-03-05)
need to calculate the number of days between these dates.. what is the
best query statement to accomplish this?
TIA,
Mike(mickalo)Blezien
===
Thunder Rain Internet Publishing
Providing Internet Solution that Work
===
Mike,
You probably want to use something like this:
SELECT TO_DAYS('2006-03-15') - TO_DAYS('2006-03-01');
Jorrit
--
System Developer
Infopact Network Solutions
Hoogvlietsekerkweg 170
3194 AM Rotterdam Hoogvliet
tel. +31 (0)88 - 4636700
fax. +31 (0)88 - 4636799
mob. +31 (0)6 - 14105968
[EMAIL PROTECTED]
http://www.infopact.nl/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting number days between 2 dates
Mike Blezien wrote:
Jorrit,
- Original Message - From: Jorrit Kronjee [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Saturday, April 01, 2006 7:46 AM
Subject: Re: Getting number days between 2 dates
Mike Blezien wrote:
Hello,
I'm sure this is a simple query but haven't come up with a good
approach. Need to get the number of days between two dates. IE:
today's date: (2006-04-01 - 2006-03-05)
need to calculate the number of days between these dates.. what is
the best query statement to accomplish this?
TIA,
Mike,
You probably want to use something like this:
SELECT TO_DAYS('2006-03-15') - TO_DAYS('2006-03-01');
Thanks, that works, also using the DAYOFYEAR produces the same results
as I just found :)
appreciate the help
Mike
Mike,
DAYOFYEAR works only well if both dates are in the same year.
SELECT DAYOFYEAR('2006-01-01') - DAYOFYEAR('2005-12-31');
results in a negative number.
Jorrit
--
System Developer
Infopact Network Solutions
Hoogvlietsekerkweg 170
3194 AM Rotterdam Hoogvliet
tel. +31 (0)88 - 4636700
fax. +31 (0)88 - 4636799
mob. +31 (0)6 - 14105968
[EMAIL PROTECTED]
http://www.infopact.nl/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
[EMAIL PROTECTED] wrote: is this relevant ? We've been looking at connection graphs, but MySQL doesn't seem to reach that limit. However, these are timely based measurements, so it could've peaked in between, although highly unlikely. I'm not very comfortable tweaking these values in a production environment while I have no indication that it could solve the problem. Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
Mark Leith wrote: Hi Jorrit, Jorrit Kronjee wrote: [EMAIL PROTECTED] wrote: is this relevant ? We've been looking at connection graphs, but MySQL doesn't seem to reach that limit. However, these are timely based measurements, so it could've peaked in between, although highly unlikely. I'm not very comfortable tweaking these values in a production environment while I have no indication that it could solve the problem. Jorrit It seems you are running in to Bug #7209: http://bugs.mysql.com/bug.php?id=7209 This is fixed in 5.0.19 now. Best regards Mark Mark, Apparently so, thanks for the hint! We'll try to upgrade as soon as possible. I'll supply the mailing list with the results of the upgrade. Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Random 'select permission denied' since upgrade to 5.0.18
Hello list, Recently we upgraded from 3.23.3 to 5.0.18 and started noticing some weird behaviour. We have update scripts running on a regular interval and some of these scripts randomily exited with MySQL error: SELECT command denied to user 'user'@'host' for table 'example' However, this error message only appears sporadically. Usually these scripts run without any problems. tcpdump shows nothing more than we already know. I see the error message appear on different queries. We have the server running on Gentoo Linux (mysql-5.0.18-r30) as a master (with one slave on another Gentoo machine). Any ideas to debug this problem any further? Thanks in advance, Jorrit By the way, there's been a previous posting to this list describing similar problems (no solution though): http://marc.theaimsgroup.com/?l=mysqlm=113050497323853w=2 -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
Keith, Thanks for answering me. Yes I did. Nothing in the MySQL log either. Keep in mind that these errors only sometimes show up and usually in the middle of a few queries in a row (while permissions are set for the whole table). Because the script runs fine most of the time, I don't think privilege settings are the key here. Could this message appear when, for instance, a maximum amount of threads has been spawned or MySQL has reached its connection limit? Jorrit Keith Roberts wrote: Check your mysql log and see if it says anything about not being able to use the new password format. Did run mysql_fix_privilege_tables to update your mysql passwords in the mysql privileges database? Regards Keith In theory, theory and practice are the same; in practice they are not. On Tue, 28 Mar 2006, Jorrit Kronjee wrote: To: mysql@lists.mysql.com From: Jorrit Kronjee [EMAIL PROTECTED] Subject: Random 'select permission denied' since upgrade to 5.0.18 Hello list, Recently we upgraded from 3.23.3 to 5.0.18 and started noticing some weird behaviour. We have update scripts running on a regular interval and some of these scripts randomily exited with MySQL error: SELECT command denied to user 'user'@'host' for table 'example' However, this error message only appears sporadically. Usually these scripts run without any problems. tcpdump shows nothing more than we already know. I see the error message appear on different queries. We have the server running on Gentoo Linux (mysql-5.0.18-r30) as a master (with one slave on another Gentoo machine). Any ideas to debug this problem any further? Thanks in advance, Jorrit By the way, there's been a previous posting to this list describing similar problems (no solution though): http://marc.theaimsgroup.com/?l=mysqlm=113050497323853w=2 -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
[EMAIL PROTECTED] wrote: On Tue, 28 Mar 2006, Jorrit Kronjee wrote: To: mysql@lists.mysql.com From: Jorrit Kronjee [EMAIL PROTECTED] Subject: Re: Random 'select permission denied' since upgrade to 5.0.18 snip Could this message appear when, for instance, a maximum amount of threads has been spawned or MySQL has reached its connection limit? Possible - what are your settings for the relevant mysql server variables? You could try something like: show variables like max% \G I'm not really sure what all the server variables do, but they may be relevant to your problem. Keith Keith, Here's the output: mysql show variables like max% \G *** 1. row *** Variable_name: max_allowed_packet Value: 1047552 *** 2. row *** Variable_name: max_binlog_cache_size Value: 4294967295 *** 3. row *** Variable_name: max_binlog_size Value: 1073741824 *** 4. row *** Variable_name: max_connect_errors Value: 10 *** 5. row *** Variable_name: max_connections Value: 300 *** 6. row *** Variable_name: max_delayed_threads Value: 20 *** 7. row *** Variable_name: max_error_count Value: 64 *** 8. row *** Variable_name: max_heap_table_size Value: 16777216 *** 9. row *** Variable_name: max_insert_delayed_threads Value: 20 *** 10. row *** Variable_name: max_join_size Value: 4294967295 *** 11. row *** Variable_name: max_length_for_sort_data Value: 1024 *** 12. row *** Variable_name: max_relay_log_size Value: 0 *** 13. row *** Variable_name: max_seeks_for_key Value: 4294967295 *** 14. row *** Variable_name: max_sort_length Value: 1024 *** 15. row *** Variable_name: max_sp_recursion_depth Value: 0 *** 16. row *** Variable_name: max_tmp_tables Value: 32 *** 17. row *** Variable_name: max_user_connections Value: 0 *** 18. row *** Variable_name: max_write_lock_count Value: 4294967295 18 rows in set (0.00 sec) -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
is your access control by hostname or IPnumber? if hostname you could be having transient DNS issues - where the IPnumber on the client connect can't be resolved into the permitted hostname (fast enough). try using IPnumber in the access control and see if the problem goes away - if it does you'll want to look into your inverse-map DNS issues. I checked it. It's by IP number. However, I wonder, could this be the case even after multiple queries in the same TCP session? Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
