Script need for dropping databases
Hi, I have a linux box running mysql plus phpmyadmin which has tables getting montly data and when a new month starts a new table is created. I want to store only 2 years of data so when a new month starts i need to drop the table which became the data container of 2 years previous data. So to make this job auto, i need a linux/mysql script which will run on cron etc . Can you please help me for a correct script ? (i cant say im a database guy :-) Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Script need for dropping databases
On Fri, Nov 18, 2011 at 1:32 PM, a bv vbavbal...@gmail.com wrote: Hi, I have a linux box running mysql plus phpmyadmin which has tables getting montly data and when a new month starts a new table is created. I want to store only 2 years of data so when a new month starts i need to drop the table which became the data container of 2 years previous data. So to make this job auto, i need a linux/mysql script which will run on cron etc . Can you please help me for a correct script ? (i cant say im a database guy :-) It may help : http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/ and you can also use something like : #!/bin/bash mysql dbnaneEOFMYSQL DROP table table_name; CREATE TABLE table_name; EOFMYSQL Thanks Rg Mohan L
Re: how do I make utf8 the default encoding for my MySQL installation
Am 18.11.2011 04:31, schrieb Martin Mueller: [mysqld] init_connect=’SET collation_connection = utf8_general_ci’ init_connect=’SET NAMES utf8′ default-character-set=utf8 character-set-server=utf8 collation-server=utf8_general_ci skip-character-set-client-handshake On a Mac with MySQL 5.5.17 this produces the error [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'default-character-set=utf8' character-set-server = utf8 collation-server = utf8_general_ci and done default-character-set is an older option, was long time deprecated and removed - you should NEVER paste options in your configs only because some random guy say they do what you want before you read the manuals what they are doing signature.asc Description: OpenPGP digital signature
Restoring failed due to Unexpected Error
Dear all, Today i need to restore the backup of a 40GB table in a new system. I create a script that takes the backup in compressed format ( 2.3 GB ).The existing hard disk crashes I need to restore my data. But when I restore the data below error exists :- [root@test1 hdd2-1]# gunzip source.sql.gz | mysql test gunzip: stdin: unexpected end of file ERROR 1064 (42000) at line 30309: 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 ''!DOCTYPE html PUBLIC \-//W3C//DTD XHTML 1.0 Transitional//EN\\n\http://www.' at line 1 [root@test1 hdd2-1]# Any suggestion to solve this issue. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Script need for dropping databases
Hi unknown, Have a look at database information_schema.TABLES: SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA='database'; As long as your MySQL version is = 5.1, you don't need a cron script, you can use the MySQL scheduler, create a stored procedure that will run each month. You'll need to use prepared statements, something like this: http://gpshumano.blogs.dri.pt/2009/09/06/automatically-cleaning-up-spam-wordpress-comments/ -NT Em 18-11-2011 08:02, a bv escreveu: Hi, I have a linux box running mysql plus phpmyadmin which has tables getting montly data and when a new month starts a new table is created. I want to store only 2 years of data so when a new month starts i need to drop the table which became the data container of 2 years previous data. So to make this job auto, i need a linux/mysql script which will run on cron etc . Can you please help me for a correct script ? (i cant say im a database guy :-) Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Restoring failed due to Unexpected Error
Am 18.11.2011 12:41, schrieb Adarsh Sharma: But when I restore the data below error exists :- [root@test1 hdd2-1]# gunzip source.sql.gz | mysql test gunzip: stdin: unexpected end of file ERROR 1064 (42000) at line 30309: 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 ''!DOCTYPE html PUBLIC \-//W3C//DTD XHTML 1.0 Transitional//EN\\n\http://www.' at line 1 [root@test1 hdd2-1]# sounds bad have you tried to unpck the backup manually and considered to use mysqlimport but anyways, the message sounds like a damaged compressed file signature.asc Description: OpenPGP digital signature
Re: Restoring failed due to Unexpected Error
- Original Message - From: Reindl Harald h.rei...@thelounge.net but anyways, the message sounds like a damaged compressed file Looks more like he's got an HTML file instead of an SQL file, to me. Have a look inside the file, maybe the HTML is just a wrapper or something. How was the backup taken ? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Restoring failed due to Unexpected Error
Am 18.11.2011 14:18, schrieb Johan De Meersman: - Original Message - From: Reindl Harald h.rei...@thelounge.net but anyways, the message sounds like a damaged compressed file Looks more like he's got an HTML file instead of an SQL file, to me. Have a look inside the file, maybe the HTML is just a wrapper or something. How was the backup taken ? yes the tml is strange but gunzip: stdin: unexpected end of file sounds like the bigger problem to me, backups without verify integrity are a dangerous game because you need them not often but if you need them it is fatal if they are corrupt signature.asc Description: OpenPGP digital signature
MySQL Community Server 5.1.60 has been released
Dear MySQL users, MySQL Server 5.1.60, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.60 is recommended for use on production systems. For an overview of what's new in MySQL 5.1, please see http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html For information on installing MySQL 5.1.60 on new servers or upgrading to MySQL 5.1.60 from previous MySQL releases, please see http://dev.mysql.com/doc/refman/5.1/en/installing.html MySQL Server is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ Not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc: http://forge.mysql.com/wiki/Contributing For information on open issues in MySQL 5.1, please see the errata list at http://dev.mysql.com/doc/refman/5.1/en/open-bugs.html The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.1. It may also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-60.html Enjoy! === D.1.1. Changes in MySQL 5.1.60 (16 November, 2011) Functionality Added or Changed * Upgrading from an Advanced GPL RPM package to an Advanced RPM package did not work. Now on Linux it is possible to use rpm -U to replace any installed MySQL product by any other of the same release family. It is not necessary to remove the old produce with rpm -e first. (Bug #11886309) * MEMORY table creation time is now available in the CREATE_TIME column of the INFORMATION_SCHEMA.TABLES table and the Create_time column of SHOW TABLE STATUS output. (Bug #51655, Bug #11759349) Bugs Fixed * InnoDB Storage Engine: This fix improves the performance of instrumentation code for InnoDB buffer pool operations. (Bug #12950803, Bug #62294) * InnoDB Storage Engine: Data from BLOB columns could be lost if the server crashed at a precise moment when other columns were being updated in an InnoDB table. (Bug #12704861) * InnoDB Storage Engine: Lookups using secondary indexes could give incorrect matches under a specific set of conditions. The conditions involve an index defined on a column prefix, for a BLOB or other long column stored outside the index page, with a table using the Barracuda file format. (Bug #12601439) * InnoDB Storage Engine: This fix corrects cases where the MySQL server could hang or abort with a long semaphore wait message. (This is a different issue than when these symptoms occurred during a CHECK TABLE statement.) (Bug #11766591, Bug #59733) * Replication: Issuing the following statements, in the order shown, could cause a deadlock between the user thread and I/O thread: START SLAVE; STOP SLAVE SQL_THREAD; START SLAVE; (Bug #11878104) See also Bug #44312, Bug #11752963, Bug #38715, Bug #38716. * Internal conversion of zero to binary and back could yield a result with incorrect precision. (Bug #12911710) * Valgrind warnings generated by filesort operations were fixed. (Bug #12856915) * Several improvements were made to the libedit library bundled with MySQL distributions, and that is available for all platforms that MySQL supports except Windows. + Navigation keys did not work for UTF-8 input. + Word navigation and delete operations did not work for UTF-8 input with Cyrillic characters. + Nonlatin characters were corrupted in overwrite mode for UTF-8 input. + Long queries caused the statement history file to become corrupted. + The Alt key caused history operations to fail. (Bug #12605400, Bug #12613725, Bug #12618092, Bug #12624155, Bug #12617651, Bug #12605388) * The help message for mysql_install_db did not indicate that it supports the --defaults-file, --defaults-extra-file and --no-defaults options. (Bug #58898, Bug #11765888) * An assertion designed to detect zero-length sort keys also was raised when the entire key set fit in memory. (Bug #58200, Bug #11765254) * myisampack could create corrupt FULLTEXT indexes when compressing tables. (Bug #53646, Bug #11761180) * OPTIMIZE TABLE could corrupt MyISAM tables if myisam_use_mmap was enabled. (Bug #49030, Bug #11757032) * If MySQL was configured with --without-plugin-innobase and --with-plugin-innodb_plugin, to suppress building the built-in InnoDB storage engine and build the InnoDB Plugin
RE: ibdata1 and undo log
Thanks for the response, Johan. It would really help if I could determine when the ballooning is occurring. Do you know of any way to do that? -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Thursday, November 17, 2011 1:28 AM To: Rozeboom, Kay [DAS] Cc: mysql@lists.mysql.com Subject: Re: ibdata1 and undo log - Original Message - From: Kay Rozeboom [DAS] kay.rozeb...@iowa.gov 1) Can anyone verify that the additional (presently unused) space was allocated for the undo log? 2) Are the many 1-page segments a leftover from a large undo log? I'm not too hot on the InnoDB internals, but yes, the undo log is one possibility for the ballooning of your tablespace. If you have huge transactions (or huge amounts of concurrent ones), that's the likely culprit. Cutting down transaction size (if possible) will help. Another possibility is automated maintenance jobs from your application. I found that Cacti, for instance, has the rather annoying tendency to optimize it's tables every night, which is a bit of a bugger if you have a few multi-gigabyte tables. You might benefit from innodb-file-per-table; that way your actual tablespace gets separated from the metadata and undo logs, giving you a much clearer view of what exactly is ballooning. Obviously, as discussed many times before, that's going to require a full export/import to be useful, though. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: MySQL Community Server 5.1.60 has been released
thanks On 11/18/11, Karen Langford karen.langf...@oracle.com wrote: Dear MySQL users, MySQL Server 5.1.60, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.60 is recommended for use on production systems. For an overview of what's new in MySQL 5.1, please see http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html For information on installing MySQL 5.1.60 on new servers or upgrading to MySQL 5.1.60 from previous MySQL releases, please see http://dev.mysql.com/doc/refman/5.1/en/installing.html MySQL Server is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ Not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc: http://forge.mysql.com/wiki/Contributing For information on open issues in MySQL 5.1, please see the errata list at http://dev.mysql.com/doc/refman/5.1/en/open-bugs.html The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.1. It may also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-60.html Enjoy! === D.1.1. Changes in MySQL 5.1.60 (16 November, 2011) Functionality Added or Changed * Upgrading from an Advanced GPL RPM package to an Advanced RPM package did not work. Now on Linux it is possible to use rpm -U to replace any installed MySQL product by any other of the same release family. It is not necessary to remove the old produce with rpm -e first. (Bug #11886309) * MEMORY table creation time is now available in the CREATE_TIME column of the INFORMATION_SCHEMA.TABLES table and the Create_time column of SHOW TABLE STATUS output. (Bug #51655, Bug #11759349) Bugs Fixed * InnoDB Storage Engine: This fix improves the performance of instrumentation code for InnoDB buffer pool operations. (Bug #12950803, Bug #62294) * InnoDB Storage Engine: Data from BLOB columns could be lost if the server crashed at a precise moment when other columns were being updated in an InnoDB table. (Bug #12704861) * InnoDB Storage Engine: Lookups using secondary indexes could give incorrect matches under a specific set of conditions. The conditions involve an index defined on a column prefix, for a BLOB or other long column stored outside the index page, with a table using the Barracuda file format. (Bug #12601439) * InnoDB Storage Engine: This fix corrects cases where the MySQL server could hang or abort with a long semaphore wait message. (This is a different issue than when these symptoms occurred during a CHECK TABLE statement.) (Bug #11766591, Bug #59733) * Replication: Issuing the following statements, in the order shown, could cause a deadlock between the user thread and I/O thread: START SLAVE; STOP SLAVE SQL_THREAD; START SLAVE; (Bug #11878104) See also Bug #44312, Bug #11752963, Bug #38715, Bug #38716. * Internal conversion of zero to binary and back could yield a result with incorrect precision. (Bug #12911710) * Valgrind warnings generated by filesort operations were fixed. (Bug #12856915) * Several improvements were made to the libedit library bundled with MySQL distributions, and that is available for all platforms that MySQL supports except Windows. + Navigation keys did not work for UTF-8 input. + Word navigation and delete operations did not work for UTF-8 input with Cyrillic characters. + Nonlatin characters were corrupted in overwrite mode for UTF-8 input. + Long queries caused the statement history file to become corrupted. + The Alt key caused history operations to fail. (Bug #12605400, Bug #12613725, Bug #12618092, Bug #12624155, Bug #12617651, Bug #12605388) * The help message for mysql_install_db did not indicate that it supports the --defaults-file, --defaults-extra-file and --no-defaults options. (Bug #58898, Bug #11765888) * An assertion designed to detect zero-length sort keys also was raised when the entire key set fit in memory. (Bug #58200, Bug #11765254) * myisampack could create corrupt FULLTEXT indexes when compressing tables. (Bug #53646, Bug #11761180) * OPTIMIZE TABLE could corrupt MyISAM tables if myisam_use_mmap was enabled. (Bug
Aggregate Query
Hi all, Could do with some help please. I have a query that grabs details of items that have been ordered from an ecommerce site. Order details are in tracking and ordered items in trackitem. The query works fine and generates a row for each item, including bits of info retrieved from other tables. So the next thing I need to do is aggregate rows that are for the same item, at the same price. I've looked at GROUP BY but can't figure out how to get it to group rows with same product name and price, and generate a column with the total of the number of aggregated rows in each row. Can someone please point me in the right direction? My current query is below. Thanks in advance. Nigel -- SELECT `tracking`.`tracking_epoch`, `trackitem`.`trackitem_itemnumber`, `trackitem`.`trackitem_itemname`, `trackitem`.`trackitem_prodtotal`, `product`.`track_duration`, `country`.`country_prs_report_code`, `Songs`.`song_composer`, `Songs`.`song_publisher` FROM `tracking` INNER JOIN `trackitem` ON `trackitem`.`trackitem_tracknum` = `tracking`.`tracking_id` INNER JOIN `product` ON `product`.`product_number` = `trackitem`.`trackitem_itemnumber` INNER JOIN `country` ON `country`.`country_id` = `tracking`.`tracking_country` INNER JOIN `Songs` ON `Songs`.`song_id` = `product`.`song_id` WHERE `tracking`.`tracking_status` = 'C' AND `tracking`.`tracking_epoch` 1287492451 AND `tracking`.`tracking_epoch` 1304876408 AND ( `country`.`country_id` = '822' OR `country`.`country_id` = '214' ) ORDER BY `trackitem`.`trackitem_itemname` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How often should we upgrade MySQL version
We are running MySQL 5.1.46 with master to master replication with 3 other servers for 3 different websites in 3 different parts of the world. My question is how often should we be looking to upgrade our MySQL version considering we can't really afford any downtime. Thanks Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: How often should we upgrade MySQL version
Ever heard the old saying, If it ain't broke, don't fix it. ;-) I'd say that as a general rule: 1. if you aren't experiencing problems then don't upgrade. 2. if you aren't subject to any vulnerabilities that may be found, then don't upgrade 3. if you don't need a new feature introduced, then don't upgrade 4. if you need to be up 100%, then don't upgrade Having said that, it is possible to upgrade with minimal downtime if you're smart about it. Get another server (or two or three) that is a clone of the existing ones. Upgrade those. Test those. Swap over. Then for the next release, do the same thing with the servers you now have as the spares. If you're in such a mission critical situation, you should have spare servers and live hot-swapable backups anyways right. -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Friday, November 18, 2011 11:54 AM To: MySQL ML Subject: How often should we upgrade MySQL version We are running MySQL 5.1.46 with master to master replication with 3 other servers for 3 different websites in 3 different parts of the world. My question is how often should we be looking to upgrade our MySQL version considering we can't really afford any downtime. Thanks Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How often should we upgrade MySQL version
I love the answer On Fri, Nov 18, 2011 at 10:59 PM, Daevid Vincent dae...@daevid.com wrote: Ever heard the old saying, If it ain't broke, don't fix it. ;-) I'd say that as a general rule: 1. if you aren't experiencing problems then don't upgrade. 2. if you aren't subject to any vulnerabilities that may be found, then don't upgrade 3. if you don't need a new feature introduced, then don't upgrade 4. if you need to be up 100%, then don't upgrade Having said that, it is possible to upgrade with minimal downtime if you're smart about it. Get another server (or two or three) that is a clone of the existing ones. Upgrade those. Test those. Swap over. Then for the next release, do the same thing with the servers you now have as the spares. If you're in such a mission critical situation, you should have spare servers and live hot-swapable backups anyways right. -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Friday, November 18, 2011 11:54 AM To: MySQL ML Subject: How often should we upgrade MySQL version We are running MySQL 5.1.46 with master to master replication with 3 other servers for 3 different websites in 3 different parts of the world. My question is how often should we be looking to upgrade our MySQL version considering we can't really afford any downtime. Thanks Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=muadshib...@gmail.com -- *___* * * السجل .. كل الأخبار من كل مكان www.alsjl.com صفحة السجل على فيسبوك http://www.facebook.com/alsjl *Muad Shibani* * * Aden Yemen Mobile: 00967 733045678 www.muadshibani.com
Can't run MySQL under Cygwin : connect to server at 'localhost' failed (only when using password)
Hello mysql-list, I try to install MySQL under Cygwin : I can build (mysql-5.5.17 on windows 7 using Cygwin), I can start and stop the server (only using mysqld.server - mysqladmin fails to connect). I can not connect to the server when I want to use a password (if I don't use any password the connection to the server succeeds). I need client AND server. I followed the on line mysql doc. Some comments about the installation / running process that I would underline :I do NOT use --without-server option in configure (I need the server)I use readline (ccmake configuration) and not libedit : could this be a problem ?I used mysql_install_db with --basedir, --datadir and --skip-name-resolve optionsI use a user dummy (that belongs to the mysql group) : I can not create the mysql user (Windows prevent me from creating a mysql user for a reason I can't figure out : I stopped fighting with Windows. As mentionned in the on-line doc, MySQL should work with any user : dummy is my user - dummy belongs to the group mysql - and the password is dummy)the root user doesn't exist in Cygwin (as far as I understand) : I can't use any mysqlamdin -u root ... as described in the on-line mysql doc When I use mysql or mysqladmin triggering a connection using a password, the connection fails (but succeeds if no password is used). May be someone could find a clue to solve this problem !... Could someone help me ? Thanks Franck Here after is a detailed description of the problem : $ mkgroup -l /etc/group (update groups Windows - Cygwin) $ mkpasswd -l /etc/passwd (update passwords Windows - Cygwin) $ more etc/group | grep mysql (check OK) mysql:S-1-5-21-4028741454-3406211479-1246761672-1004:1004: $ more passwd | grep dummy (check OK) dummy:unused:1000:513:dummy,U-dummy-PC\dummy,S-1-5-21-4028741454-3406211479-1246761672-1000:/home/dummy:/bin/bash $ chgrp -R None /tmp (give read / write access to all users) $ chgrp -R None /var (give read / write access to all users) $ ll (check OK) total 229 drwxrwxrwt+ 1 dummy None 0 Nov 14 11:57 tmp drwxr-xr-x+ 1 dummy None 0 Aug 17 20:58 var $ chgrp -R mysql /usr/local/mysql (give read / write access to users of mysql group) $ chown -R dummy /usr/local/mysql (give read / write access to users of mysql group) $ ps (check : no server) PIDPPIDPGID WINPID TTY UIDSTIME COMMAND 3400 13400 3400 con 1000 11:10:39 /usr/bin/bash 675634006756 5400 con 1000 12:14:46 /usr/bin/ps $ ll /tmp (check : no mysql.sock) total 12 drwxrwxrwt+ 1 dummy None 0 Nov 14 11:57 . drwxr-xr-x+ 1 dummy Administrateurs 0 Nov 5 00:37 .. drwxrwxrwt+ 1 dummy None 0 Nov 9 18:40 .X11-unix drwxr-xr-x+ 1 dummy None 0 Aug 18 00:00 hsperfdata_dummy -rw-r--r-- 1 dummy None316 Oct 26 09:13 xkb_4Di75h -rw-r--r-- 1 dummy None316 Oct 27 00:08 xkb_4NrKCL -rw-r--r-- 1 dummy None316 Oct 27 00:09 xkb_ThcsMy -rw-r--r-- 1 dummy None316 Oct 27 00:10 xkb_shbOiY $ mysqld --user=dummy (launch server : OK) 14 12:15:54 InnoDB: The InnoDB memory heap is disabled 14 12:15:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins 14 12:15:54 InnoDB: Compressed tables use zlib 1.2.5 14 12:15:54 InnoDB: Initializing buffer pool, size = 128.0M 14 12:15:54 InnoDB: Completed initialization of buffer pool 14 12:15:54 InnoDB: highest supported file format is Barracuda. 14 12:15:54 InnoDB: Waiting for the background threads to start 14 12:15:55 InnoDB: 1.1.8 started; log sequence number 1595675 14 12:15:55 [Note] Event Scheduler: Loaded 0 events 14 12:15:55 [Note] mysqld: ready for connections. Version: '5.5.17' socket: '/tmp/mysql.sock' port: 3306 Source distribution $ ll /tmp (check: mysql.sock is created and can be accessed - read / write permissions) total 13 drwxrwxrwt+ 1 dummy None 0 Nov 14 12:15 . drwxr-xr-x+ 1 dummy Administrateurs 0 Nov 5 00:37 .. srwxrwxrwx 1 dummy None 0 Nov 14 12:15 mysql.sock $ ps (check: server launched OK) PIDPPIDPGID WINPID TTY UIDSTIME COMMAND 472034004720 6576 con 1000 12:15:53 /usr/local/mysql/bin/mysqld $ mysql -u dummy -p (when I hit return as a password = connection OK) Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. mysql show tables; ERROR 1046 (3D000): No database selected mysql quit Bye $ mysql -u dummy -p (when I type a real password : dummy, YES... or anything else) ERROR 1045 (28000): Access denied for user 'dummy'@'localhost' (using password: YES) $ mysqladmin -u dummy version (try without password = KO) mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (4)' Check that mysqld is running and that the
Re: Can't run MySQL under Cygwin : connect to server at 'localhost' failed (only when using password)
Am 18.11.2011 23:16, schrieb Franck Houssen: $ mysql -u dummy -p (when I type a real password : dummy, YES... or anything else) ERROR 1045 (28000): Access denied for user 'dummy'@'localhost' (using password: YES) and if you submit the same post every day you will get the same anser as some days before - try to understand how mysql-privileges are working signature.asc Description: OpenPGP digital signature
Re: Can't run MySQL under Cygwin : connect to server at 'localhost' failed (only when using password)
Maybe this is the wrong place to ask, but why would you want to do this? Mysql has binaries for Windows, just use one of them? On Fri, Nov 18, 2011 at 4:16 PM, Franck Houssen f...@hotmail.com wrote: Hello mysql-list, I try to install MySQL under Cygwin : I can build (mysql-5.5.17 on windows 7 using Cygwin), I can start and stop the server (only using mysqld.server - mysqladmin fails to connect). I can not connect to the server when I want to use a password (if I don't use any password the connection to the server succeeds). I need client AND server. I followed the on line mysql doc. Some comments about the installation / running process that I would underline :I do NOT use --without-server option in configure (I need the server)I use readline (ccmake configuration) and not libedit : could this be a problem ?I used mysql_install_db with --basedir, --datadir and --skip-name-resolve optionsI use a user dummy (that belongs to the mysql group) : I can not create the mysql user (Windows prevent me from creating a mysql user for a reason I can't figure out : I stopped fighting with Windows. As mentionned in the on-line doc, MySQL should work with any user : dummy is my user - dummy belongs to the group mysql - and the password is dummy)the root user doesn't exist in Cygwin (as far as I understand) : I can't use any mysqlamdin -u root ... as described in the on-line mysql doc When I use mysql or mysqladmin triggering a connection using a password, the connection fails (but succeeds if no password is used). May be someone could find a clue to solve this problem !... Could someone help me ? Thanks Franck Here after is a detailed description of the problem : $ mkgroup -l /etc/group (update groups Windows - Cygwin) $ mkpasswd -l /etc/passwd (update passwords Windows - Cygwin) $ more etc/group | grep mysql (check OK) mysql:S-1-5-21-4028741454-3406211479-1246761672-1004:1004: $ more passwd | grep dummy (check OK) dummy:unused:1000:513:dummy,U-dummy-PC\dummy,S-1-5-21-4028741454-3406211479-1246761672-1000:/home/dummy:/bin/bash $ chgrp -R None /tmp (give read / write access to all users) $ chgrp -R None /var (give read / write access to all users) $ ll (check OK) total 229 drwxrwxrwt+ 1 dummy None 0 Nov 14 11:57 tmp drwxr-xr-x+ 1 dummy None 0 Aug 17 20:58 var $ chgrp -R mysql /usr/local/mysql (give read / write access to users of mysql group) $ chown -R dummy /usr/local/mysql (give read / write access to users of mysql group) $ ps (check : no server) PIDPPIDPGID WINPID TTY UIDSTIME COMMAND 3400 13400 3400 con 1000 11:10:39 /usr/bin/bash 675634006756 5400 con 1000 12:14:46 /usr/bin/ps $ ll /tmp (check : no mysql.sock) total 12 drwxrwxrwt+ 1 dummy None 0 Nov 14 11:57 . drwxr-xr-x+ 1 dummy Administrateurs 0 Nov 5 00:37 .. drwxrwxrwt+ 1 dummy None 0 Nov 9 18:40 .X11-unix drwxr-xr-x+ 1 dummy None 0 Aug 18 00:00 hsperfdata_dummy -rw-r--r-- 1 dummy None316 Oct 26 09:13 xkb_4Di75h -rw-r--r-- 1 dummy None316 Oct 27 00:08 xkb_4NrKCL -rw-r--r-- 1 dummy None316 Oct 27 00:09 xkb_ThcsMy -rw-r--r-- 1 dummy None316 Oct 27 00:10 xkb_shbOiY $ mysqld --user=dummy (launch server : OK) 14 12:15:54 InnoDB: The InnoDB memory heap is disabled 14 12:15:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins 14 12:15:54 InnoDB: Compressed tables use zlib 1.2.5 14 12:15:54 InnoDB: Initializing buffer pool, size = 128.0M 14 12:15:54 InnoDB: Completed initialization of buffer pool 14 12:15:54 InnoDB: highest supported file format is Barracuda. 14 12:15:54 InnoDB: Waiting for the background threads to start 14 12:15:55 InnoDB: 1.1.8 started; log sequence number 1595675 14 12:15:55 [Note] Event Scheduler: Loaded 0 events 14 12:15:55 [Note] mysqld: ready for connections. Version: '5.5.17' socket: '/tmp/mysql.sock' port: 3306 Source distribution $ ll /tmp (check: mysql.sock is created and can be accessed - read / write permissions) total 13 drwxrwxrwt+ 1 dummy None 0 Nov 14 12:15 . drwxr-xr-x+ 1 dummy Administrateurs 0 Nov 5 00:37 .. srwxrwxrwx 1 dummy None 0 Nov 14 12:15 mysql.sock $ ps (check: server launched OK) PIDPPIDPGID WINPID TTY UIDSTIME COMMAND 472034004720 6576 con 1000 12:15:53 /usr/local/mysql/bin/mysqld $ mysql -u dummy -p (when I hit return as a password = connection OK) Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. mysql show tables; ERROR 1046 (3D000): No database selected mysql quit Bye $ mysql -u dummy -p (when I type a real password : dummy, YES... or anything else) ERROR 1045 (28000): Access denied for user 'dummy'@'localhost' (using password:
MySQL Community Server 5.5.18 has been released
Dear MySQL users, MySQL 5.5.18 is a new version of the 5.5 production release of the world's most popular open source database. MySQL 5.5.18 is recommended for use on production systems. MySQL 5.5 includes several high-impact enhancements to improve the performance and scalability of the MySQL Database, taking advantage of the latest multi-CPU and multi-core hardware and operating systems. In addition, with release 5.5, InnoDB is now the default storage engine for the MySQL Database, delivering ACID transactions, referential integrity and crash recovery by default. MySQL 5.5 also provides a number of additional enhancements including: - Significantly improved performance on Windows, with various Windows specific features and improvements - Higher availability, with new semi-synchronous replication and Replication Heart Beat - Improved usability, with Improved index and table partitioning, SIGNAL/RESIGNAL support and enhanced diagnostics, including a new Performance Schema monitoring capability. For a more complete look at what's new in MySQL 5.5, please see the following resources: MySQL 5.5 is GA, Interview with Tomas Ulin: http://dev.mysql.com/tech-resources/interviews/thomas-ulin-mysql-55.html Documentation: http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html Whitepaper: What's New in MySQL 5.5: http://dev.mysql.com/why-mysql/white-papers/mysql-wp-whatsnew-mysql-55.php If you are running a MySQL production level system, we would like to direct your attention to MySQL Enterprise Edition, which includes the most comprehensive set of MySQL production, backup, monitoring, modeling, development, and administration tools so businesses can achieve the highest levels of MySQL performance, security and uptime. http://mysql.com/products/enterprise/ For information on installing MySQL 5.5.18 on new servers, please see the MySQL installation documentation at http://dev.mysql.com/doc/refman/5.5/en/installing.html For upgrading from previous MySQL releases, please see the important upgrade considerations at: http://dev.mysql.com/doc/refman/5.5/en/upgrading.html MySQL Database 5.5.18 is available in source and binary form for a number of platforms from our download pages at: http://dev.mysql.com/downloads/mysql/ Not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://forge.mysql.com/wiki/Contributing The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.5. It may also be viewed online at: http://dev.mysql.com/doc/refman/5.5/en/news-5-5-18.html Enjoy! Changes in MySQL 5.5.18 (16 November 2011) Functionality Added or Changed * Upgrading from an Advanced GPL RPM package to an Advanced RPM package did not work. Now on Linux it is possible to use rpm -U to replace any installed MySQL product by any other of the same release family. It is not necessary to remove the old produce with rpm -e first. (Bug #11886309) Bugs Fixed * Incompatible Change: Replication: The statements in the following list are now marked as unsafe for statement-based replication. This is due to the fact that each of these statements depends on the results of a SELECT statement whose order cannot always be determined. When using STATEMENT logging mode, a warning is issued in the binary log for any of these statements; when using MIXED logging mode, the statement is logged using the row-based format. + INSERT ... SELECT ... ON DUPLICATE KEY UPDATE + REPLACE ... SELECT + CREATE TABLE ... IGNORE SELECT + CREATE TABLE ... REPLACE SELECT + INSERT IGNORE ... SELECT + UPDATE IGNORE When upgrading, you should note the use of these statements in your applications, keeping in mind that a statement that inserts or replaces rows obtained from a SELECT can take up many times as much space in the binary log when logged using row-based format than when only the statement itself is logged. Depending on the number and size of the rows selected and inserted (or replaced) by any such statements, the difference in size of the binary log after the logging of these statements is switched from statement-based to row-based can potentially be several orders of magnitude. See Section 15.1.2.1, Advantages and Disadvantages of Statement-Based and Row-Based Replication. (Bug #11758262, Bug #50439) * Replication: A replication master could send damaged events to slaves after the binary log disk on the master became full. To correct this issue, only complete events are now pushed by the master dump thread to the slave I/O thread. In addition, the error text that the
Error on 'flush tables' command
I'm getting an occasional error from one of my slaves running community mysql 5.5.16: 'Got an error writing communication packets' on query. Query: 'flush tables' Which halts replication on this slave until I issue a 'SET GLOBAL sql_slave_skip_counter=1' command on the slave. There are a few FEDERATED tables on the slave.. is that what would cause a communication packet error? If not, what else could cause this on a flush tables command? Thanks. -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can't run MySQL under Cygwin : connect to server at 'localhost' failed (only when using password)
Bravo Basil! I've been looking at this all day and wondering myself. Now that I've said that.. And now for something terribly evil, is there an strace for cygwin? On 11/18/2011 03:30 PM, Basil Daoust wrote: Maybe this is the wrong place to ask, but why would you want to do this? Mysql has binaries for Windows, just use one of them? On Fri, Nov 18, 2011 at 4:16 PM, Franck Houssen f...@hotmail.com wrote: Hello mysql-list, I try to install MySQL under Cygwin : I can build (mysql-5.5.17 on windows 7 using Cygwin), I can start and stop the server (only using mysqld.server - mysqladmin fails to connect). I can not connect to the server when I want to use a password (if I don't use any password the connection to the server succeeds). I need client AND server. I followed the on line mysql doc. Some comments about the installation / running process that I would underline :I do NOT use --without-server option in configure (I need the server)I use readline (ccmake configuration) and not libedit : could this be a problem ?I used mysql_install_db with --basedir, --datadir and --skip-name-resolve optionsI use a user dummy (that belongs to the mysql group) : I can not create the mysql user (Windows prevent me from creating a mysql user for a reason I can't figure out : I stopped fighting with Windows. As mentionned in the on-line doc, MySQL should work with any user : dummy is my user - dummy belongs to the group mysql - and the password is dummy)the root user doesn't exist in Cygwin (as far as I understand) : I can't use any mysqlamdin -u root ... as described in the on-line mysql doc When I use mysql or mysqladmin triggering a connection using a password, the connection fails (but succeeds if no password is used). May be someone could find a clue to solve this problem !... Could someone help me ? Thanks Franck Here after is a detailed description of the problem : $ mkgroup -l /etc/group (update groups Windows - Cygwin) $ mkpasswd -l /etc/passwd (update passwords Windows - Cygwin) $ more etc/group | grep mysql (check OK) mysql:S-1-5-21-4028741454-3406211479-1246761672-1004:1004: $ more passwd | grep dummy (check OK) dummy:unused:1000:513:dummy,U-dummy-PC\dummy,S-1-5-21-4028741454-3406211479-1246761672-1000:/home/dummy:/bin/bash $ chgrp -R None /tmp (give read / write access to all users) $ chgrp -R None /var (give read / write access to all users) $ ll (check OK) total 229 drwxrwxrwt+ 1 dummy None 0 Nov 14 11:57 tmp drwxr-xr-x+ 1 dummy None 0 Aug 17 20:58 var $ chgrp -R mysql /usr/local/mysql (give read / write access to users of mysql group) $ chown -R dummy /usr/local/mysql (give read / write access to users of mysql group) $ ps (check : no server) PIDPPIDPGID WINPID TTY UIDSTIME COMMAND 3400 13400 3400 con 1000 11:10:39 /usr/bin/bash 675634006756 5400 con 1000 12:14:46 /usr/bin/ps $ ll /tmp (check : no mysql.sock) total 12 drwxrwxrwt+ 1 dummy None 0 Nov 14 11:57 . drwxr-xr-x+ 1 dummy Administrateurs 0 Nov 5 00:37 .. drwxrwxrwt+ 1 dummy None 0 Nov 9 18:40 .X11-unix drwxr-xr-x+ 1 dummy None 0 Aug 18 00:00 hsperfdata_dummy -rw-r--r-- 1 dummy None316 Oct 26 09:13 xkb_4Di75h -rw-r--r-- 1 dummy None316 Oct 27 00:08 xkb_4NrKCL -rw-r--r-- 1 dummy None316 Oct 27 00:09 xkb_ThcsMy -rw-r--r-- 1 dummy None316 Oct 27 00:10 xkb_shbOiY $ mysqld --user=dummy (launch server : OK) 14 12:15:54 InnoDB: The InnoDB memory heap is disabled 14 12:15:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins 14 12:15:54 InnoDB: Compressed tables use zlib 1.2.5 14 12:15:54 InnoDB: Initializing buffer pool, size = 128.0M 14 12:15:54 InnoDB: Completed initialization of buffer pool 14 12:15:54 InnoDB: highest supported file format is Barracuda. 14 12:15:54 InnoDB: Waiting for the background threads to start 14 12:15:55 InnoDB: 1.1.8 started; log sequence number 1595675 14 12:15:55 [Note] Event Scheduler: Loaded 0 events 14 12:15:55 [Note] mysqld: ready for connections. Version: '5.5.17' socket: '/tmp/mysql.sock' port: 3306 Source distribution $ ll /tmp (check: mysql.sock is created and can be accessed - read / write permissions) total 13 drwxrwxrwt+ 1 dummy None 0 Nov 14 12:15 . drwxr-xr-x+ 1 dummy Administrateurs 0 Nov 5 00:37 .. srwxrwxrwx 1 dummy None 0 Nov 14 12:15 mysql.sock $ ps (check: server launched OK) PIDPPIDPGID WINPID TTY UIDSTIME COMMAND 472034004720 6576 con 1000 12:15:53 /usr/local/mysql/bin/mysqld $ mysql -u dummy -p (when I hit return as a password = connection OK) Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. mysql show tables; ERROR 1046
Uninstall MySql Service
Hello I want to completely reinstall my sql server due to prior install issues. How can you uninstall the MySql service? I am running under window 7. Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org