Re: RPM files
Hi Andrew, all ! AndrewMcHorney wrote: Hello I downloaded some of the document files but the file extension is rpm. How does one extract this file? If you are on an RPM-based Linux system (like SuSE, RedHat, etc), you need not extract, you can install - using the rpm program. See man rpm. If you are on any other system (Linux not using RPM, like Debian or Ubuntu; or any non-Linux), or if you don't want to install the package but just extract some files, you can get the whole contents in cpio format. The tool to do this is called rpm2cpio, see its man page. All binaries generated by MySQL (and non-binary files as well) are also available in tar.gz packages. On some few platforms, the native tar may be somewhat limited and fail on long path names, use the GNU version of tar then. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL crash on FreeBSD 7
Hi all, I woke up this morning to find that our main database had stopped responding so I jumped on to see what was going on. MySQL was no longer running and the error log contained lots of... --- InnoDB: Warning: a long semaphore wait: --Thread 39439068816 has waited at trx0trx.c line 715 for 639.00 seconds the semaphore: Mutex at 0x809c002a8 created file srv0srv.c line 872, lock var 1 waiters flag 1 --- ...and ended with the following... --- InnoDB: ## Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 InnoDB: ## Diagnostic info printed to the standard error stream InnoDB: Error: semaphore wait has lasted 600 seconds InnoDB: We intentionally crash the server, because it appears to be hung. 080710 4:34:17InnoDB: Assertion failure in thread 34382814000 in file srv0srv.c line 2093 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. 080710 4:34:17 - mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=33554432 read_buffer_size=2093056 max_used_connections=502 max_connections=501 threads_connected=501 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 5161000 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 080710 04:34:18 mysqld ended --- I don't really have any idea what it was doing at that time, but it could be related to the mysqldump that runs at 12:45pm but that's usually done within an hour. Upon restart it reported that InnoDB was not shut down normally and did it's crash recovery thing without any issues. The server is running FreeBSD 7.0-RELEASE #0 amd64 with an unchanged kernel and mysql-server-5.0.45_1 installed from ports. If anyone has any idea what caused this or steps I should take to prevent it happening again I'd be eternally grateful. Thanks. -Stut -- http://stut.net/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rename a table
Hi All, I was to rename a table as below set @t_name=now(); rename table amc to concat('amf_',t_name); but i am getting below error. How do i fix this. 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 'concat('amf_',t_name)' at line 1 I can do the same using unix script, but just wanted to check if i can do this in mysql itself. regards anandkl
Re: rename a table
rename table amc to concat('amf_',@t_name); 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 'concat('amf_',@t_name)' at line 1 On 7/10/08, Octavian Rasnita [EMAIL PROTECTED] wrote: Have you tried: rename table amc to concat('amf_', @t_name); Octavian - Original Message - From: Ananda Kumar [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Thursday, July 10, 2008 1:54 PM Subject: rename a table Hi All, I was to rename a table as below set @t_name=now(); rename table amc to concat('amf_',t_name); but i am getting below error. How do i fix this. 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 'concat('amf_',t_name)' at line 1 I can do the same using unix script, but just wanted to check if i can do this in mysql itself. regards anandkl
Re: rename a table
Hi ! Ananda Kumar wrote: Hi All, I was to rename a table as below set @t_name=now(); rename table amc to concat('amf_',t_name); but i am getting below error. How do i fix this. 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 'concat('amf_',t_name)' at line 1 I can do the same using unix script, but just wanted to check if i can do this in mysql itself. SQL (standard) AFAIK demands that all identifiers (names of database, table, view, column, ...) be constant strings in the statements, it does not allow expressions for them. If there are exceptions in MySQL, I am not aware of them. To do such things as you want, you have to use dynamic SQL: Construct the statement as a string (which does not use expressions for identifiers), and then send it to the server. A Unix script generating a command file which you pass to the command line client is one way of doing dynamic SQL. Any other client application (Perl, PHP, ...) could do the same. The command line client can do it in SQL, using prepared statements: Construct a string with your statement into a variable, then prepare a statement from the variable and execute it. Several tests in the test suite do it, this is how to find them: cd mysql-test/t ; grep -i 'prepare.*@' *.test Use them as a model for your needs. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rename a table
Hi Joerg, Can you please let me the correct path to find mysql-test/t grep -i 'prepare.*@' *.test regards anandkl On 7/10/08, Joerg Bruehe [EMAIL PROTECTED] wrote: Hi ! Ananda Kumar wrote: Hi All, I was to rename a table as below set @t_name=now(); rename table amc to concat('amf_',t_name); but i am getting below error. How do i fix this. 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 'concat('amf_',t_name)' at line 1 I can do the same using unix script, but just wanted to check if i can do this in mysql itself. SQL (standard) AFAIK demands that all identifiers (names of database, table, view, column, ...) be constant strings in the statements, it does not allow expressions for them. If there are exceptions in MySQL, I am not aware of them. To do such things as you want, you have to use dynamic SQL: Construct the statement as a string (which does not use expressions for identifiers), and then send it to the server. A Unix script generating a command file which you pass to the command line client is one way of doing dynamic SQL. Any other client application (Perl, PHP, ...) could do the same. The command line client can do it in SQL, using prepared statements: Construct a string with your statement into a variable, then prepare a statement from the variable and execute it. Several tests in the test suite do it, this is how to find them: cd mysql-test/t ; grep -i 'prepare.*@' *.test Use them as a model for your needs. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
Forcing import
Hi, Is there a way to force mysql to import a dump which contains a mysql reserved word as a field name? Regards Warren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Forcing import
Warren Windvogel wrote: Hi, Is there a way to force mysql to import a dump which contains a mysql reserved word as a field name? Regards Warren You can use sed to replace column names with other. for example: sed 's/timestamp (timestamp) not null/datetime (timestamp) not null/g' which replaces column name timestamp with datetime, and in the same way replace column names in INSERT statements -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rename a table
Hi Ananda, Ananda Kumar wrote: Hi Joerg, Can you please let me the correct path to find mysql-test/t grep -i 'prepare.*@' *.test no, I cannot - I do not know where you installed MySQL, and which package you used. Just do find / -type d -name mysql-test and the system will tell you. Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How Can I Auto Delete my Binary Files?
I'm running a RHEL 4.1 and MySQL 4.0.26 so a lot or the more recent commands available in 5.0.x aren't available to me. Does anyone know of any scripts or anything I can use to delete files that arent being used or run by my slave servers? It's pretty safe to say that I can delete log files older than 7 days so that can eliminate the need to check for open files.
Re: Schema Design
On Mon, Jul 7, 2008 at 4:20 AM, Ananda Kumar [EMAIL PROTECTED] wrote: Yes, i definitly agree on the third table if APPLICATION FUNCTIONALITY increase and need to add more columns into the current tables. On 7/7/08, metastable [EMAIL PROTECTED] wrote: Ananda Kumar wrote: I feel creating the third table would just be duplicating the data and occupying space. Creating index on existing table and quering would be better. But you got a good point of FOREIGN KEY CONSTRAINT. Can we use this constraint on current SETTINGS table, rather than creating the third table. [snip] On the contrary, using the third table would eliminate all data duplication, thus safeguarding data integrity and making maintenance easier (think 'I want to change this setting description or make it a boolean in stead of a text setting'). It will also use less space because of the previous. Ofcourse, I don't see any settings description or setting types limitations in your schema as it is now. If that isn't an objection, you could create the foreign key constraint on the existing table as such: ALTER TABLE Settings ADD UNIQUE KEY (applicationID) ALTER TABLE Settings ADD FOREIGN KEY (applicationID) REFERENCES Applications(applicationID) ON UDPATE CASCADE ON DELETE SET NULL (from the top of my head, something like this) This implies that you are using InnoDB tables ! I would however still create the third table. It will make your life much easier when at some time in the future you decide to expand the functionality and do indeed include limitations on the settings ('must be boolean', 'must be any of green,red,blue', ...), to allow for validation and such. HTH Stijn Well, the other thing I'm somewhat concerned about is that the column type is TEXT. For some of the things I'm indexing, it's an INT, but I had to create SettingValue as TEXT so that we could store text in it as well. It seems like a waste to index on text when the underlying value is really a number. Waynn
mysql slave got duplicate error (1062) frequently
I set a master and a slave, master server is a live server, slave server does't online. but I got 1062 error frenquently, especially when I stop slave a while and start slave again. following is from my general log: 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',9788534,UNIX_TIMESTAMP(),110252138) 19 Query insert into user_stat (column_name,user_id,column_value,time,stat_id) values('visits',8403043,348,NOW(),110252140) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',11839858,UNIX_TIMESTAMP(),110252161) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('comments',11882675,3,110252168) 19 Query delete from user_stat where 1 AND column_name='last_visit' AND user_id=11839858 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',11839858,UNIX_TIMESTAMP(),110252170) 19 Query delete from user_stat where 1 AND column_name='last_visit' AND user_id=10731230 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',10731230,UNIX_TIMESTAMP(),110252176) 19 Query insert into user_stat (column_name,user_id,column_value,time,stat_id) values('visits',9779846,169,NOW(),110252179) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',8499860,UNIX_TIMESTAMP(),110252181) 19 Query insert into user_stat (column_name,user_id,column_value,time,stat_id) values('visits',8314927,19,NOW(),110252185) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',11636653,UNIX_TIMESTAMP(),110252187) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',11865811,UNIX_TIMESTAMP(),110252189) 19 Query delete from user_stat where 1 AND column_name='last_visit' AND user_id=8499860 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',8499860,UNIX_TIMESTAMP(),110252192) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',10713080,UNIX_TIMESTAMP(),110252193) 19 Query insert into user_stat (column_name,user_id,column_value,time,stat_id) values('visits',9477379,490,NOW(),110252194) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',8050753,UNIX_TIMESTAMP(),110252196) 19 Query delete from user_stat where 1 AND column_name='last_visit' AND user_id=8050753 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',8050753,UNIX_TIMESTAMP(),110252208) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',10944715,UNIX_TIMESTAMP(),110252209) in the first line, the slave insert one record with user_id 9788534, the in last line insert a new record again. the user_stat's structure is: CREATE TABLE `user_stat` ( `stat_id` bigint(20) unsigned NOT NULL, `user_id` mediumint(8) unsigned NOT NULL default '0', `column_name` enum('last_visit','profile_photos','albums','photos','journals','friends','comments','visits','mp3s') NOT NULL default 'last_visit', `column_value` int(10) unsigned NOT NULL default '0', `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`stat_id`), UNIQUE KEY `user_column` (`user_id`,`column_name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 so I got dup error at the last line. I think something is wrong in the process of binlog replication, like, the 'delete' sql is discarded. and there are many errors in the error log like this: 080710 22:41:21 [Warning] Aborted connection 31778 to db: 'data5' user: 'user' host: '56-1.my.com' (Got an error reading communication packets) so I want to know: what is happen ? how to resolve it ? -- Thanks Regards Changying Li -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql slave got duplicate error (1062) frequently
my sql version is mysql select version(); +--+ | version()| +--+ | 5.0.24a-standard-log | +--+ 1 row in set (0.00 sec) linux kernel 2.6.11.6 Changying Li [EMAIL PROTECTED] writes: I set a master and a slave, master server is a live server, slave server does't online. but I got 1062 error frenquently, especially when I stop slave a while and start slave again. following is from my general log: 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',9788534,UNIX_TIMESTAMP(),110252138) 19 Query insert into user_stat (column_name,user_id,column_value,time,stat_id) values('visits',8403043,348,NOW(),110252140) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',11839858,UNIX_TIMESTAMP(),110252161) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('comments',11882675,3,110252168) 19 Query delete from user_stat where 1 AND column_name='last_visit' AND user_id=11839858 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',11839858,UNIX_TIMESTAMP(),110252170) 19 Query delete from user_stat where 1 AND column_name='last_visit' AND user_id=10731230 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',10731230,UNIX_TIMESTAMP(),110252176) 19 Query insert into user_stat (column_name,user_id,column_value,time,stat_id) values('visits',9779846,169,NOW(),110252179) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',8499860,UNIX_TIMESTAMP(),110252181) 19 Query insert into user_stat (column_name,user_id,column_value,time,stat_id) values('visits',8314927,19,NOW(),110252185) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',11636653,UNIX_TIMESTAMP(),110252187) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',11865811,UNIX_TIMESTAMP(),110252189) 19 Query delete from user_stat where 1 AND column_name='last_visit' AND user_id=8499860 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',8499860,UNIX_TIMESTAMP(),110252192) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',10713080,UNIX_TIMESTAMP(),110252193) 19 Query insert into user_stat (column_name,user_id,column_value,time,stat_id) values('visits',9477379,490,NOW(),110252194) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',8050753,UNIX_TIMESTAMP(),110252196) 19 Query delete from user_stat where 1 AND column_name='last_visit' AND user_id=8050753 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',8050753,UNIX_TIMESTAMP(),110252208) 19 Query insert into user_stat (column_name,user_id,column_value,stat_id) values('last_visit',10944715,UNIX_TIMESTAMP(),110252209) in the first line, the slave insert one record with user_id 9788534, the in last line insert a new record again. the user_stat's structure is: CREATE TABLE `user_stat` ( `stat_id` bigint(20) unsigned NOT NULL, `user_id` mediumint(8) unsigned NOT NULL default '0', `column_name` enum('last_visit','profile_photos','albums','photos','journals','friends','comments','visits','mp3s') NOT NULL default 'last_visit', `column_value` int(10) unsigned NOT NULL default '0', `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`stat_id`), UNIQUE KEY `user_column` (`user_id`,`column_name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 so I got dup error at the last line. I think something is wrong in the process of binlog replication, like, the 'delete' sql is discarded. and there are many errors in the error log like this: 080710 22:41:21 [Warning] Aborted connection 31778 to db: 'data5' user: 'user' host: '56-1.my.com' (Got an error reading communication packets) so I want to know: what is happen ? how to resolve it ? -- Thanks Regards Changying Li -- Thanks Regards Changying Li -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rename a table
I could not find it, when i tried the above find / -type d -name mysql-test But i looked at the prepared statements in mysql, i try that and get to you all. regards anandkl On 7/10/08, Joerg Bruehe [EMAIL PROTECTED] wrote: Hi Ananda, Ananda Kumar wrote: Hi Joerg, Can you please let me the correct path to find mysql-test/t grep -i 'prepare.*@' *.test no, I cannot - I do not know where you installed MySQL, and which package you used. Just do find / -type d -name mysql-test and the system will tell you. Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
Re: How Can I Auto Delete my Binary Files?
Shaun, Purge master logs is available in 4.0 it just doesn't have the before key word. It should be trivial to write a script that decides which log file to purge based on the mtime. -Eric On Thu, Jul 10, 2008 at 12:15 PM, Shaun Adams [EMAIL PROTECTED] wrote: I'm running a RHEL 4.1 and MySQL 4.0.26 so a lot or the more recent commands available in 5.0.x aren't available to me. Does anyone know of any scripts or anything I can use to delete files that arent being used or run by my slave servers? It's pretty safe to say that I can delete log files older than 7 days so that can eliminate the need to check for open files. -- high performance mysql consulting. http://provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]