auto_increment and the value 0
I'm migrating a database from 4.0.12 on Solaris to 4.0.18-0 on Red Hat Linux. A few of the tables have a 0 (zero) in the auto_increment primary key column. However, when importing, the 0 in the insert is translated to the next available auto_increment value thus causing a duplicate key situation on the next value in the import. I've tried removing the 0 row from the export and adding it in manually afterwards, but that also translates the 0 to the next available auto_increment value. I've also tried creating the table with the table option AUTO_INCREMENT=0 and inserting the 0 row first. That also translated it to a value of 1 and caused duplicate keys. Is there a way to maintain the 0 value in an auto_increment column? Brian Stanton DBA, Belo 214-977-4087
RE: Host denied errors
IP should work just fine. However, if the source webserver is behind a firewall or otherwise NAT'd, your mysql server may see them coming from an in between IP instead of the actual webserver. The quickest way to figure it out is to have the webserver host try to connect and send you the error message. Typically it will read something like: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES). That host in the error message should match with your settings in the database. Brian Stanton DBA, Belo 214-977-4087 -Original Message- From: Scott Haneda [mailto:[EMAIL PROTECTED] Sent: Friday, March 24, 2006 2:34 PM To: mysql@lists.mysql.com Subject: Host denied errors Little confused, can not seem to locate the docs on this. Trying to set up mysql to allow a remote webserver to talk to it, using a user/pass/host setting with host set to allow anything, of course works. If the IP I am connecting from has a valid PTR, I can use the hostname as well, and that works. In this one case, I do not have the ability to get the client to get a PTR set up on the IP I want to connect from. I tried putting in the IP address, and it still blocks it, can someone tell me how mysql authenticates this data? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- 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]
client/server differences
I'm looking to use a RHEL4 server with standard RHEL4 packages to connect to a RHEL4 MySQL 5.0 server. I was curious if anyone knows of any known problems with a 4.1 client (the one provided with RHEL4) communicating to a 5.0 database? It connects fine, but wanted to be sure there were not any known issues. Thanks, Brian Stanton DBA, Belo 214-977-4087
host info
Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton
RE: host info
Unfortunately, that gives me the host of the machine I'm connecting from, not the server I'm connecting to. Thanks, Brian Stanton -Original Message- From: Berman, Mikhail [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 10:28 AM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: host info Brian, In UNIX from mysql prompt do: mysql system /bin/hostname Mikhail -Original Message- From: Stanton, Brian [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 11:03 AM To: 'mysql@lists.mysql.com' Subject: host info Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: host info
Using 4.0.18... connected from one unix box to another using mysql -h server -u user -p database Thanks, Brian Stanton -Original Message- From: Berman, Mikhail [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 12:30 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: host info Brian, Have you initiated mysql command with -h host-name option or just mysql with other options but -h? Mikhail Berman -Original Message- From: Stanton, Brian [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 11:33 AM To: 'mysql@lists.mysql.com' Subject: RE: host info Unfortunately, that gives me the host of the machine I'm connecting from, not the server I'm connecting to. Thanks, Brian Stanton -Original Message- From: Berman, Mikhail [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 10:28 AM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: host info Brian, In UNIX from mysql prompt do: mysql system /bin/hostname Mikhail -Original Message- From: Stanton, Brian [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 11:03 AM To: 'mysql@lists.mysql.com' Subject: host info Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton -- 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: host info
The 'Connection' output from the 'status' command is actually what I was looking for. However, most likely it will be a jdbc connection to mysql, not the mysql client, so I'll have to see if it works that way or not. Thanks, Brian Stanton -Original Message- From: Eamon Daly [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 2:04 PM To: Stanton, Brian; mysql@lists.mysql.com Subject: Re: host info I don't know if it's possible in MySQL. That said, in the mysql client, you can type '\s' for 'status'. Look for 'Current user' in the output. Eamon Daly - Original Message - From: Stanton, Brian [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 27, 2005 10:03 AM Subject: host info Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: host info
Along those lines, you could use show variables like 'pid_file' if the user needing to know the hostname has privileges for this. Thanks for the idea! Thanks, Brian Stanton -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 3:42 PM To: Stanton, Brian Cc: 'mysql@lists.mysql.com' Subject: RE: host info Hi all, Mysql server knows the OS server as localhost. the hostname you see in status is the OS server from which you connect (the client one), since it's defined in the grant. The only method i can see is : ls datadir_path/*.pid its hostname.pid You can do it also with *.err Mathias Selon Stanton, Brian [EMAIL PROTECTED]: The 'Connection' output from the 'status' command is actually what I was looking for. However, most likely it will be a jdbc connection to mysql, not the mysql client, so I'll have to see if it works that way or not. Thanks, Brian Stanton -Original Message- From: Eamon Daly [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 2:04 PM To: Stanton, Brian; mysql@lists.mysql.com Subject: Re: host info I don't know if it's possible in MySQL. That said, in the mysql client, you can type '\s' for 'status'. Look for 'Current user' in the output. Eamon Daly - Original Message - From: Stanton, Brian [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 27, 2005 10:03 AM Subject: host info Does anyone know a function that will return the hostname of the mysql server you are connecting to? Just as: mysql select database(); returns the database you're connected to, I need to display the host I'm connected to. Similar to the oracle statement: select host_name from v$instance; Thanks, Brian Stanton -- 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: something is pegging mysql
Use the general query log. http://dev.mysql.com/doc/mysql/en/query-log.html Thanks, Brian Stanton Systems Administrator, Belo Interactive -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 2:11 PM To: mysql@lists.mysql.com Subject: something is pegging mysql It has been a rough day today. I am using mysql with coldfusion. Something somehwere on my site is causing mysql to take up 100% of the cpu. This causes coldfusion to lock. Is there any sort of query log I can look at to see what queries have run in, say, the last hour? If I see what queries are running I could track down the page and either fix a defective query or change it so I am not getting killed. --ja -- -- 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]
4.0.12 to 4.0.18
I upgraded from MySQL 4.0.12 to MySQL 4.0.18 last night and found some oddities this morning. Apparently some of the varchar data being input into certain tables had carriage returns: \n in the data. This was an accidental thing, but on 4.0.12, the \n was ignored when matching on that record. However, now in 4.0.18 it seems to include that when determining matches. For example: select * from table where record='abc'; used to match both 'abc' and 'abc\n' but now only matches 'abc'. Did I miss something in the change history that said this should now happen or is this an undocumented difference? Thanks, Brian Stanton Systems Administrator, Belo Interactive
RE: Replication errors...
Thanks! I was hoping it was something that had already been found. I'll upgrade as soon as I can. Thanks, Brian -Original Message- From: Sasha Pachev [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 24, 2004 10:47 PM To: Stanton, Brian Cc: '[EMAIL PROTECTED]' Subject: Re: Replication errors... Stanton, Brian wrote: Shortly after the MySQL 4.0 line went to production, I upgraded to mysql 4.0.12. Since then my slave has been getting corrupted tables 2 to 3 times every month. I've also seen this problem in mysql 4.0.13. When I run a check table on the table in question it gives the following results: +-+---+--+-- ---+ | Table | Op| Msg_type | Msg_text | +-+---+--+-- ---+ | database.table | check | warning | Table is marked as crashed | | database.table | check | warning | 2 clients is using or hasn't closed the table properly | | database.table | check | warning | Not used space is supposed to be: 526688 but is: 522768 | | database.table | check | error| record delete-link-chain corrupted | | database.table | check | error| Corrupt | +-+---+--+-- ---+ 5 rows in set (0.01 sec) It repairs just fine and then replication continues, but I never ran into this issue in the 3.23.xx line. Has anyone else been seeing this problem? Has it been fixed in a later 4.0.x version? ERROR: 1030 Got error 127 from table handler 040207 3:44:03 Slave: error 'Got error 127 from table handler' on query ... 040207 3:44:03 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log ... I've seen it on my systems. The problem is a bug in DELETE in 4.0 that was fixed in 4.0.18. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication errors...
Shortly after the MySQL 4.0 line went to production, I upgraded to mysql 4.0.12. Since then my slave has been getting corrupted tables 2 to 3 times every month. I've also seen this problem in mysql 4.0.13. When I run a check table on the table in question it gives the following results: +-+---+--+-- ---+ | Table | Op| Msg_type | Msg_text | +-+---+--+-- ---+ | database.table | check | warning | Table is marked as crashed | | database.table | check | warning | 2 clients is using or hasn't closed the table properly | | database.table | check | warning | Not used space is supposed to be: 526688 but is: 522768 | | database.table | check | error| record delete-link-chain corrupted | | database.table | check | error| Corrupt | +-+---+--+-- ---+ 5 rows in set (0.01 sec) It repairs just fine and then replication continues, but I never ran into this issue in the 3.23.xx line. Has anyone else been seeing this problem? Has it been fixed in a later 4.0.x version? ERROR: 1030 Got error 127 from table handler 040207 3:44:03 Slave: error 'Got error 127 from table handler' on query ... 040207 3:44:03 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log ... Thanks, Brian I don't need any of that SQL stuff -- I just want a database!
RE: SQL_BIG_TABLES and replication
I raised tmp_table_size to 1000M and restarted mysql on the slave and still got the same error. Any other variables I should be looking at? Thanks, Brian I don't need any of that SQL stuff -- I just want a database! -Original Message- From: Sasha Pachev [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 25, 2004 6:09 PM To: Stanton, Brian Cc: mysql (E-mail) Subject: Re: SQL_BIG_TABLES and replication Stanton, Brian wrote: I'm currently running mysql 4.0.13 on red hat 7.2. The following create table query currently requires the user to use SET SQL_BIG_TABLES=1 for the query to go through on the master successfully. However, that doesn't seem to get set when the slave tries to replicate the create table statement. I've tried restarting the slave with the --big-tables option, but that doesn't seem to help either. The resulting table files on the master are relatively small... 8.4k Feb 16 22:37 60dayREGusers_sep_oct2003.frm 5.0M Feb 16 22:37 60dayREGusers_sep_oct2003.MYD 9.5M Feb 16 22:37 60dayREGusers_sep_oct2003.MYI however the ProfileIDValue_REGID table is rather large. 8.4k Sep 15 09:44 ProfileIDValue_REGID.frm 499M Feb 16 22:17 ProfileIDValue_REGID.MYD 443M Feb 16 22:18 ProfileIDValue_REGID.MYI 8.4k Nov 13 11:47 UniqueID_oct2003.frm 32M Nov 13 11:53 UniqueID_oct2003.MYD 51M Nov 13 11:53 UniqueID_oct2003.MYI 8.4k Jan 9 10:50 UniqueID_sep2003.frm 34M Jan 9 10:58 UniqueID_sep2003.MYD 55M Jan 9 10:58 UniqueID_sep2003.MYI Anyone have any thoughts? ERROR: 1114 The table '#sql_931_0' is full 040225 15:34:25 Slave: error 'The table '#sql_931_0' is full' on query 'create table 60dayREGusers_sep_oct2003 (primary key(UniqueID)) select distinct UniqueID_sep2003.UniqueID from UniqueID_sep2003,ProfileIDValue_REGID where UniqueID_sep2003.UniqueID=ProfileIDValue_REGID.UniqueID union select distinct UniqueID_oct2003.UniqueID from UniqueID_oct2003,ProfileIDValue_REGID where UniqueID_oct2003.UniqueID=ProfileIDValue_REGID.UniqueID', error_code=1114 Looks like a bug to me. MySQL should be able to figure out it needs to use the disk when an in-memory temp table exceeds tmp_table_size without SQL_BIG_TABLES. It would be nice if you could create a test case for it and submit it to MySQL developers. For now, try increasing tmp_table_size ( make sure you have enough RAM + swap space to deal with it, though). If re-writing the query is an option, I would also try it without UNION, which is a fairly new feature and could still have a few quirks. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL_BIG_TABLES and replication
I'm currently running mysql 4.0.13 on red hat 7.2. The following create table query currently requires the user to use SET SQL_BIG_TABLES=1 for the query to go through on the master successfully. However, that doesn't seem to get set when the slave tries to replicate the create table statement. I've tried restarting the slave with the --big-tables option, but that doesn't seem to help either. The resulting table files on the master are relatively small... 8.4k Feb 16 22:37 60dayREGusers_sep_oct2003.frm 5.0M Feb 16 22:37 60dayREGusers_sep_oct2003.MYD 9.5M Feb 16 22:37 60dayREGusers_sep_oct2003.MYI however the ProfileIDValue_REGID table is rather large. 8.4k Sep 15 09:44 ProfileIDValue_REGID.frm 499M Feb 16 22:17 ProfileIDValue_REGID.MYD 443M Feb 16 22:18 ProfileIDValue_REGID.MYI 8.4k Nov 13 11:47 UniqueID_oct2003.frm 32M Nov 13 11:53 UniqueID_oct2003.MYD 51M Nov 13 11:53 UniqueID_oct2003.MYI 8.4k Jan 9 10:50 UniqueID_sep2003.frm 34M Jan 9 10:58 UniqueID_sep2003.MYD 55M Jan 9 10:58 UniqueID_sep2003.MYI Anyone have any thoughts? ERROR: 1114 The table '#sql_931_0' is full 040225 15:34:25 Slave: error 'The table '#sql_931_0' is full' on query 'create table 60dayREGusers_sep_oct2003 (primary key(UniqueID)) select distinct UniqueID_sep2003.UniqueID from UniqueID_sep2003,ProfileIDValue_REGID where UniqueID_sep2003.UniqueID=ProfileIDValue_REGID.UniqueID union select distinct UniqueID_oct2003.UniqueID from UniqueID_oct2003,ProfileIDValue_REGID where UniqueID_oct2003.UniqueID=ProfileIDValue_REGID.UniqueID', error_code=1114 Thanks, Brian I don't need any of that SQL stuff -- I just want a database!
