Can't send messages to the mailing list
Hello, I was subscribed for a long time to MySQL general mailing list but now I see that I can't send messages to the list because they are rejected, and in the error mail message that returns, I saw that it says something about SPAM. I didn't send spam mails to the list or to somewhere else but I have also seen that I couldn't even subscribe with another email address to the list because the subscription email is also rejected. If somebody knows, please tell me what can I do to be able to subscribe to the mailing list and send messages to it again. I was subscribed with the email orasn...@gmail.com. Thank you and sorry for offtopicness. -- Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting single results per (left) record with INNER JOIN
Hi Nigel, all ! Nigel Peck wrote: [[...]] My query is: SELECT `People`.`person_id`, `People`.`name` FROM `People` INNER JOIN `Person_postal_addresses` ON `Person_postal_addresses`.`person_id` = `People`.`person_id` WHERE `People`.`name` REGEXP 'example' OR `Person_postal_addresses`.`address` REGEXP 'example' ; How do I alter that to get one result per matching row in the People table? When there are multiple matches, for one row in People, for: `Person_postal_addresses`.`address` REGEXP '1' Thanks in advance, Nigel I see two possibilities: a) SELECT DISTINCT ... http://dev.mysql.com/doc/refman/5.0/en/select.html b) Use a subquery and an EXISTS predicate: http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html I expect the subquery approach to be performing worse than the distict. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com 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/mysql?unsub=arch...@jab.org
Re: InnoDB - CREATE INDEX - Locks table for too long
Hi, I am using your procedure on MyISAM tables now and works but RENAME does not work with locked tables, (anyway it is already an atomic operation) =BARON Try something like this: create table new_table like old_table; alter table new_table add key(.); insert into new_table select * from old_table where primary_key between ? and ?; Repeat the last statement until nearly finished, then: lock tables new_table write, old_table write; insert into new_table select * from old_table where primary_key ?; rename table old_table backup, new_table old_table; unlock tables; so the above procedure fails unless I swap like this: - ... ... unlock tables; rename table old_table backup, new_table old_table; - The problem is of course that it is no more atomic. I also thought of SELECT for UPDATE that locks the records but does not prevent from inserting new ones, any suggestions? Thanks Clauido
Re: what will happen if the disk is full when mysql flush binlog to it
Hi Cui, On Sat, Mar 14, 2009 at 4:33 PM, Cui Shijun rancp...@gmail.com wrote: hi, What will happen if the disk is full when mysql flush binlog to it? If I use innodb engine(mysql-5.1.22) and turn on the binlog, is there any chance that mysql has data updated without binlog flushed? Will mysql return with fail when it fail on binlog? From the docs. Even with sync_binlog set to 1, there is still the chance of an inconsistency between the table content and binary log content in case of a crash. To resolve this, you should set --innodb_support_xa to 1. Although this option is related to the support of XA transactions in InnoDB, it also ensures that the binary log and InnoDB data files are synchronized. Having innodb_support_xa enabled on a replication master — or on any MySQL server where binary logging is in use — ensures that the binary log does not get out of sync compared to the table data. This is covered here http://dev.mysql.com/doc/refman/5.1/en/full-disk.html and here http://dev.mysql.com/doc/refman/5.1/en/binary-log.html Cheers, Ewen Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ewen.fort...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL 5.1 Rolling back a transaction containing Create Alter Drop table statements
Hi, Is there any way to rollback a transaction in MySQL 5.1 that contains Create, Alter, Drop, table statements. i.e. is there any way to turn off the feature: Statements That Cause an Implicit Commit or even remove some of the default statements that cause a commit. I tried using the TEMPORARY KEYWORD. It is not exactly what I want. Moreover, the ALTER syntax does not accept a TEMPORARY Keyword, and ALTER a temporary table causes an explicit commit. Any thoughts on this issue, -- Hatem Nassrat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL 5.1 Rolling back a transaction containing Create Alter Drop table statements
On Mon, Mar 16, 2009 at 10:25 AM, Hatem Nassrat hatem.nass...@gmail.com wrote: Hi, Is there any way to rollback a transaction in MySQL 5.1 that contains Create, Alter, Drop, table statements. No. Sorry. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Migrating all MySQL from Netware to Suse
Hi! Trying to migrate from Netware to Suse (SLES 10 sp 1 NOWS SBE 2). I already changed the my.cnf file: 1.added a datadir=/MySQL/data line 2.removed the comments from all INNOdb lines The tables on the Netware files are not big On the SLES server I already see a mysql folder containing tables and an ibdata1 file In the old Netware DB there are also ibdata and ib_logfile. I did a mysqldump --single-transaction --quick -uroot -p --all-databases destination_file_name command on the Netware server. Does it include the ibdata* and ib_logfile*? What is the import command for the above mysqldump? TIA Nanu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Ever-shrinking disk space after repeated DB restores
OK, I found the answer. The binary log files were filling up (/usr/ local/mysql/data on OS X/mysql-bin.x). Easy fix: mysql reset master; And all the disk space is recovered On 15-Mar-09, at 10:47 AM, René Fournier wrote: It seems MySQL isn't releasing disk space on my laptop after I copy a database from one machine to it. From the server, I'm running something like this: gzip -dc /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz | mysql -h 192.168.0.224 -u root -p alba2 ...every night. And the remaining disk space on my laptop is reduced daily by the size of the database tables I'm dropping and then adding. Is there something I can do to reclaim that space? I've restarted MySQL and my computer (OS X 10.5.6) to no avail. Thanks. ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
W2008 Server Issues?
1. Any issues running MySQL Ver. X on Windows 2008 Server? 2. Running a query to list all records of about 2600 records is very slow and gets an error of Extremely High Work Load, Each record does contain 1 LONGBLOB containing small voice recording. We are using the SQL Query Browser and running the same query and same database on a 2000 server 1 gig less memory runs the query in about 15 seconds with no issues. Is this an issue with MySQL or the 2008 server? Kurt Duncan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: W2008 Server Issues?
Duncan, Kurt (MCUSA) wrote: 1. Any issues running MySQL Ver. X on Windows 2008 Server? 2. Running a query to list all records of about 2600 records is very slow and gets an error of Extremely High Work Load, Each record does contain 1 LONGBLOB containing small voice recording. We are using the SQL Query Browser and running the same query and same database on a 2000 server 1 gig less memory runs the query in about 15 seconds with no issues. Is this an issue with MySQL or the 2008 server? I'm no expert, so I'm not going to comment on the environmental issues. You'd likely get much more predictable performance if you re-engineer your tables so that the BLOB is in a separate table from the main entity, with a FK from there. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL University session on March 19: How to Use Charsets and Collations Properly
How to Use Charsets and Collations Properly http://forge.mysql.com/wiki/How_to_Use_Charsets_and_Collations_Properly This Thursday (March 19th, 14:00 UTC), Susanne Ebrecht will give a MySQL University session on How to Use Charsets and Collations Properly. Susanne works at the MySQL Support team and is an expert in character set issues. For MySQL University sessions, point your browser to this page: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.) MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone, not just Sun employees. Sessions are recorded (slides and audio), so if you can't attend the live session you can look at the recording anytime after the session. Here's the schedule for the upcoming weeks: * March 19, 2009: How to Use Charsets and Collations Properly (Susanne Ebrecht) * March 26, 2009: No session scheduled * April 2, 2009: How to Create a Test Case (updated session from 2007) (Patrick Crews) * April 9, 2009: No MySQL University session scheduled (due to upcoming Easter vacation in Europe) * April 16, 2009 (tentative): Going Nuts - How to write MySQL test cases in Perl (Luis Soares, Alfranio Correia, Serge Kozlov, Lars Thalmann) * April 23, 2009: No MySQL University session during MySQL Conference Expo 2009 * April 30, 2009: Testing Multiple Servers With MySQL Sandbox (Giuseppe Maxia) * May 7, 2009: MySQL Replication: Walk-through of the new 5.1 and 6.0 features (Lars Thalmann) * May 14, 2009: Programming Towards Multi-Core CPUs (Mikael Ronström) * May 21, 2009: To be scheduled * May 28, 2009: Architecture of MySQL Backup (Lars Thalmann) * June 4, 2009: Boosting Performance With MySQL 5.1 Partitioning (Giuseppe Maxia) * June 11, 2009: Building MySQL Releases on Unix (Jörg Brühe) The schedule is not engraved in stone at this point. Please visit http://forge.mysql.com/wiki/MySQL_University#Upcoming_Sessions for the up-to-date list. On that page, you can also find the starting time for many time zones. Cheers, Stefan -- *** Sun Microsystems GmbHStefan Hinz Sonnenallee 1Manager Documentation, Database Group 85551 Kirchheim-Heimstetten Phone: +49-30-82702940 Germany Fax: +49-30-82702941 http://www.sun.de/mysql mailto: stefan.h...@sun.com Amtsgericht Muenchen: HRB161028 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Can't create table
Hello, I've tried to create these 4 test tables, but when I try to create the last one, MySQL gives the following error: ERROR 1005 (HY000) at line 20: Can't create table '.\z\favorite_link.frm' (errno : 150) It seems that the foreign keys are not created well in the fourth table, and I don't know why. Does anyone have any idea? Here are the SQL statements for creating these tables: create table if not exists user( id int unsigned not null auto_increment primary key ) engine=InnoDB; create table if not exists favorite( id bigint unsigned not null auto_increment primary key, user int unsigned not null, foreign key(user) references user(id) on update cascade on delete cascade ) engine=InnoDB; create table if not exists link( id bigint unsigned not null auto_increment primary key, user int unsigned not null, foreign key(user) references user(id) on update cascade on delete cascade ) engine=InnoDB; create table if not exists favorite_link( favorite bigint unsigned not null, link int unsigned not null, primary key pri(favorite, link), foreign key(favorite) references favorite(id) on update cascade on delete cascade, foreign key(link) references link(id) on update cascade on delete cascade ) engine=InnoDB; Thank you very much. -- Octavian -- 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 create table
Octavian Râºniþã wrote: Hello, I've tried to create these 4 test tables, but when I try to create the last one, MySQL gives the following error: ERROR 1005 (HY000) at line 20: Can't create table '.\z\favorite_link.frm' (errno : 150) It seems that the foreign keys are not created well in the fourth table, and I don't know why. Does anyone have any idea? Here are the SQL statements for creating these tables: create table if not exists user( id int unsigned not null auto_increment primary key ) engine=InnoDB; create table if not exists favorite( id bigint unsigned not null auto_increment primary key, user int unsigned not null, foreign key(user) references user(id) on update cascade on delete cascade ) engine=InnoDB; create table if not exists link( id bigint unsigned not null auto_increment primary key, user int unsigned not null, foreign key(user) references user(id) on update cascade on delete cascade ) engine=InnoDB; create table if not exists favorite_link( favorite bigint unsigned not null, link int unsigned not null, primary key pri(favorite, link), foreign key(favorite) references favorite(id) on update cascade on delete cascade, foreign key(link) references link(id) on update cascade on delete cascade ) engine=InnoDB; Thank you very much. link int unsigned not null, id bigint unsigned not null auto_increment primary key These are not the same type. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: best mysql optimization tutorial and/or quick start guide?
- Original Message - From: Darryle Steplight dstepli...@gmail.com To: Stephen Edberg sbedb...@ucdavis.edu Cc: John G. Heim jh...@math.wisc.edu; mysql@lists.mysql.com Sent: Thursday, March 05, 2009 12:53 PM Subject: Re: best mysql optimization tutorial and/or quick start guide? High Performance MySql Optimization, Backups, Replication, and more 2nd Edition . Got a problem, pick a chapter and read the solution. This book is awesome, I'm confident you will find what you are looking for :) . Excellent tip. It's exactly what I'm looking for. Well, I guess I wouldn't exactly call it a quick start guide. But by chance, it happened to be on bookshare.org. This is a web site which, if you're blind, you can subscribe to to download e-books. I'm already subscribed so I didn't have to pay any additional price for this particular book. Must be my lucky day. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: best mysql optimization tutorial and/or quick start guide?
John G. Heim wrote: - Original Message - From: Darryle Steplight dstepli...@gmail.com To: Stephen Edberg sbedb...@ucdavis.edu Cc: John G. Heim jh...@math.wisc.edu; mysql@lists.mysql.com Sent: Thursday, March 05, 2009 12:53 PM Subject: Re: best mysql optimization tutorial and/or quick start guide? High Performance MySql Optimization, Backups, Replication, and more 2nd Edition . Got a problem, pick a chapter and read the solution. This book is awesome, I'm confident you will find what you are looking for :) . Excellent tip. It's exactly what I'm looking for. Well, I guess I wouldn't exactly call it a quick start guide. But by chance, it happened to be on bookshare.org. This is a web site which, if you're blind, you can subscribe to to download e-books. I'm already subscribed so I didn't have to pay any additional price for this particular book. Must be my lucky day. This book is also available on the Safari edition associated with ACM memberships. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org