external mysqldump
Hi List, i wrote this alrready in mysql-forum a few days ago, but did not get any answer. :( i try to do backup with mysqldump from external host with routines. mysqldump -R -h my.host.name -u myuser -p'mypass' mydb filename.sql I got error: myuser has insufficent privileges to SHOW CREATE FUNCTION `countuser`! It works if i do the same mysqldumLine directly on the server where db is installed. In mysql-table myuser has same privileges for % and locklhost. Are there some further restrictions for external connects? I dont have an idea what to change now. Thank you Andre -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: external mysqldump
Try: mysql -u root -p mysql GRANT SUPER ON *.* TO myuser@'%'; mysql GRANT SUPER ON *.* TO myuser@'localhost'; Andre Hübner wrote: Hi List, i wrote this alrready in mysql-forum a few days ago, but did not get any answer. :( i try to do backup with mysqldump from external host with routines. mysqldump -R -h my.host.name -u myuser -p'mypass' mydb filename.sql I got error: myuser has insufficent privileges to SHOW CREATE FUNCTION `countuser`! It works if i do the same mysqldumLine directly on the server where db is installed. In mysql-table myuser has same privileges for % and locklhost. Are there some further restrictions for external connects? I dont have an idea what to change now. Thank you Andre -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: Column level replication q?
Hi Gary, Did you try with a view with only the public fields of your table? Aloha! Claudio -Messaggio originale- Da: Gary W. Smith [mailto:[EMAIL PROTECTED] Inviato: mercoledì 20 febbraio 2008 1.18 A: mysql@lists.mysql.com Oggetto: Column level replication q? We need to replicate a table to a third part. The information in the table is pretty much public, with the exception of one column. Is it possible to replicate all of the tables with the exception of one column? What I was thinking was to replication it to a 2nd machine that will limit it to the tables we care about and then expose that slave machine as a secondary master. We are looking to do something like this MasterA - SlaveA/MasterB - SlaveC MasterA tableA (our machine) field1 field2 field3 field4 SlaveA/MasterB (our machine) field1 field2 field4 SlaveC (their machine) field1 field2 field4 We know we can limit the tables which they can use (which is why we have introducted SlaveA/MasterB so they will only be able to pull the tables we make avaiable to them. Our current method is database dumps but this is become impracticle due to size. Replication in testing works for what we want to do, we just have a problem with a single field. Any ideas on how to make this work? Gary Wayne Smith Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto * This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: external mysqldump
Thank you for answering. is there a way to do without granting super-privileg? for security reasons i cannot grant too high privileges for normal db-users. rights should be limited to own db. Thanks Andre - Original Message - From: Ben Clewett [EMAIL PROTECTED] To: Andre Hübner [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, February 20, 2008 9:46 AM Subject: Re: external mysqldump Try: mysql -u root -p mysql GRANT SUPER ON *.* TO myuser@'%'; mysql GRANT SUPER ON *.* TO myuser@'localhost'; Andre Hübner wrote: Hi List, i wrote this alrready in mysql-forum a few days ago, but did not get any answer. :( i try to do backup with mysqldump from external host with routines. mysqldump -R -h my.host.name -u myuser -p'mypass' mydb filename.sql I got error: myuser has insufficent privileges to SHOW CREATE FUNCTION `countuser`! It works if i do the same mysqldumLine directly on the server where db is installed. In mysql-table myuser has same privileges for % and locklhost. Are there some further restrictions for external connects? I dont have an idea what to change now. Thank you Andre -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: Column level replication q?
Of course I am talking about a materialized view, did you try? Here some useful links: http://forums.mysql.com/read.php?100,21746,21746#msg-21746 and from the reply in the same forum topic by Waheed Noor 09/10/2007 01:39PM you get this link: http://www.shinguz.ch/MySQL/mysql_mv.html Aloha! Claudio -Messaggio originale- Da: Gary W. Smith [mailto:[EMAIL PROTECTED] Inviato: mercoledì 20 febbraio 2008 1.18 A: mysql@lists.mysql.com Oggetto: Column level replication q? We need to replicate a table to a third part. The information in the table is pretty much public, with the exception of one column. Is it possible to replicate all of the tables with the exception of one column? What I was thinking was to replication it to a 2nd machine that will limit it to the tables we care about and then expose that slave machine as a secondary master. We are looking to do something like this MasterA - SlaveA/MasterB - SlaveC MasterA tableA (our machine) field1 field2 field3 field4 SlaveA/MasterB (our machine) field1 field2 field4 SlaveC (their machine) field1 field2 field4 We know we can limit the tables which they can use (which is why we have introducted SlaveA/MasterB so they will only be able to pull the tables we make avaiable to them. Our current method is database dumps but this is become impracticle due to size. Replication in testing works for what we want to do, we just have a problem with a single field. Any ideas on how to make this work? Gary Wayne Smith Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto * This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: external mysqldump
I use a specific user (backup) for my backups, therefore user privileges are not effected. Is there something like this which you could use? Andre Hübner wrote: Thank you for answering. is there a way to do without granting super-privileg? for security reasons i cannot grant too high privileges for normal db-users. rights should be limited to own db. Thanks Andre - Original Message - From: Ben Clewett [EMAIL PROTECTED] To: Andre Hübner [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, February 20, 2008 9:46 AM Subject: Re: external mysqldump Try: mysql -u root -p mysql GRANT SUPER ON *.* TO myuser@'%'; mysql GRANT SUPER ON *.* TO myuser@'localhost'; Andre Hübner wrote: Hi List, i wrote this alrready in mysql-forum a few days ago, but did not get any answer. :( i try to do backup with mysqldump from external host with routines. mysqldump -R -h my.host.name -u myuser -p'mypass' mydb filename.sql I got error: myuser has insufficent privileges to SHOW CREATE FUNCTION `countuser`! It works if i do the same mysqldumLine directly on the server where db is installed. In mysql-table myuser has same privileges for % and locklhost. Are there some further restrictions for external connects? I dont have an idea what to change now. Thank you Andre -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group a select * and a select COUNT from 2 different [... ] (solved thankyou !)
Hello, thankyou to everyone who has helped me out on this one as I did not think it was actuallay possible ! :) This is what worked best for me : SELECT a.username, a.first_name, a.last_name,COALESCE(COUNT(b.username), 0) AS count FROM user_list a LEFT JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; I'm sorry if I was not clear with my first email yesterday making it sound like I wanted a 0 or a 1 and nothing else... I made a mistake and thought that I was getting nothing or 1 whereas it was actually counting corectly. I was also suggested a LEFT OUTER JOIN but have read that it is a synonym to LEFT JOIN, is this the case or is there a difference between the two? Thanks again, Richard David Schneider-Joseph a écrit : Try this one: SELECT a.username, a.first_name, a.last_name,COALESCE(COUNT(b.username), 0) AS count FROM user_list a LEFT JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; The LEFT JOIN will ensure you still get a result row even if there are no matching rows in `login_table`. And the COALESCE will give you a value of 0 instead of NULL for the count, in that case. On Feb 19, 2008, at 5:29 PM, Richard wrote: Sorry it's me again, I made a mistake, it counts the number of logins correctly, but does not show members with 0 logins ! Any idea how to do this? Thanks :) Peter Brawley a écrit : Richard, Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a Try ... SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count FROM user_list a JOIN login_table b ON a.username = b.username GROUP BY a.username,a.first_name,a.lastname; PB - Richard wrote: Hello, This time I'm rearly not sure if this is possible to do. I've got two queries that I would like to bring together to make only one query ... I've got a list of users And also a login table I would like to list all users and show the number of times they have logged in. So to get the list of users I would do : SELECT username, first_name, last_name FROM user_list And to count the number of connections I would do SELECT COUNT(*) AS count FROM login_table WHERE username = $result['username'] Can I do something like this : SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count FROM login_table b WHERE a.username = b.username) FROM user_list a I know that the above query can not work but It's just to give a better idea about what I'm trying to do . :) If I do a join, I will the username repeated for each login. Thanks in advance, Richard -- 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: external mysqldump
Unfortunately, not. For internal uses i can use root or other special user. But if my users want to do backup on there own with external mysqldump they get this error. if i do login with userdata from console i can do mysqldump, its only the external connect which makes this problem. - Original Message - From: Ben Clewett [EMAIL PROTECTED] To: Andre Hübner [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, February 20, 2008 10:26 AM Subject: Re: external mysqldump I use a specific user (backup) for my backups, therefore user privileges are not effected. Is there something like this which you could use? Andre Hübner wrote: Thank you for answering. is there a way to do without granting super-privileg? for security reasons i cannot grant too high privileges for normal db-users. rights should be limited to own db. Thanks Andre - Original Message - From: Ben Clewett [EMAIL PROTECTED] To: Andre Hübner [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, February 20, 2008 9:46 AM Subject: Re: external mysqldump Try: mysql -u root -p mysql GRANT SUPER ON *.* TO myuser@'%'; mysql GRANT SUPER ON *.* TO myuser@'localhost'; Andre Hübner wrote: Hi List, i wrote this alrready in mysql-forum a few days ago, but did not get any answer. :( i try to do backup with mysqldump from external host with routines. mysqldump -R -h my.host.name -u myuser -p'mypass' mydb filename.sql I got error: myuser has insufficent privileges to SHOW CREATE FUNCTION `countuser`! It works if i do the same mysqldumLine directly on the server where db is installed. In mysql-table myuser has same privileges for % and locklhost. Are there some further restrictions for external connects? I dont have an idea what to change now. Thank you Andre -- 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]
Certified MySQL Associate (CMA) certification value
I'm considering studying for: Certified MySQL Associate (CMA) http://www.mysql.com/certification/candguide#t21 At first glance, it seems to follow: http://www.mysql.com/training/courses/introduction_to_databases.html I can hit the bookstore to find out more, of course. But, what I was wondering was: what's the value of this certification for me? What doors will it open? thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: external mysqldump
Ah! What you actually need is for the users to have SELECT access to mysql.procs. GRANT SELECT ON mysql.procs TO user@'%' identified by Of course this is something of a security risk as well, in that any use could see any other user's functions and stored procedures, but it will achieve the permissions you need without giving away all the data as well. Simon Simon Elliston Ball [EMAIL PROTECTED] On 20 Feb 2008, at 12:03, Andre Hübner wrote: i tried, but always got error: ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES i followed this and did found a former discussion. seems to be not possible to give this privileg by this line. http://lists.mysql.com/mysql/198421 hmm, bad case, isnt it? Andre - Original Message - From: Simon Elliston Ball [EMAIL PROTECTED] To: Andre Hübner [EMAIL PROTECTED] Sent: Wednesday, February 20, 2008 12:08 PM Subject: Re: external mysqldump GRANT SUPER ON userdatabase.* to user@'%' identified by 'password'; That way each external user can only do super things to their own db. simon Simon Elliston Ball [EMAIL PROTECTED] On 20 Feb 2008, at 11:03, Andre Hübner wrote: Unfortunately, not. For internal uses i can use root or other special user. But if my users want to do backup on there own with external mysqldump they get this error. if i do login with userdata from console i can do mysqldump, its only the external connect which makes this problem. - Original Message - From: Ben Clewett [EMAIL PROTECTED] To: Andre Hübner [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, February 20, 2008 10:26 AM Subject: Re: external mysqldump I use a specific user (backup) for my backups, therefore user privileges are not effected. Is there something like this which you could use? Andre Hübner wrote: Thank you for answering. is there a way to do without granting super-privileg? for security reasons i cannot grant too high privileges for normal db- users. rights should be limited to own db. Thanks Andre - Original Message - From: Ben Clewett [EMAIL PROTECTED] To: Andre Hübner [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, February 20, 2008 9:46 AM Subject: Re: external mysqldump Try: mysql -u root -p mysql GRANT SUPER ON *.* TO myuser@'%'; mysql GRANT SUPER ON *.* TO myuser@'localhost'; Andre Hübner wrote: Hi List, i wrote this alrready in mysql-forum a few days ago, but did not get any answer. :( i try to do backup with mysqldump from external host with routines. mysqldump -R -h my.host.name -u myuser -p'mypass' mydb filename.sql I got error: myuser has insufficent privileges to SHOW CREATE FUNCTION `countuser`! It works if i do the same mysqldumLine directly on the server where db is installed. In mysql-table myuser has same privileges for % and locklhost. Are there some further restrictions for external connects? I dont have an idea what to change now. Thank you Andre -- 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] -- 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: external mysqldump
i tried, but always got error: ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES i followed this and did found a former discussion. seems to be not possible to give this privileg by this line. http://lists.mysql.com/mysql/198421 hmm, bad case, isnt it? Andre - Original Message - From: Simon Elliston Ball [EMAIL PROTECTED] To: Andre Hübner [EMAIL PROTECTED] Sent: Wednesday, February 20, 2008 12:08 PM Subject: Re: external mysqldump GRANT SUPER ON userdatabase.* to user@'%' identified by 'password'; That way each external user can only do super things to their own db. simon Simon Elliston Ball [EMAIL PROTECTED] On 20 Feb 2008, at 11:03, Andre Hübner wrote: Unfortunately, not. For internal uses i can use root or other special user. But if my users want to do backup on there own with external mysqldump they get this error. if i do login with userdata from console i can do mysqldump, its only the external connect which makes this problem. - Original Message - From: Ben Clewett [EMAIL PROTECTED] To: Andre Hübner [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, February 20, 2008 10:26 AM Subject: Re: external mysqldump I use a specific user (backup) for my backups, therefore user privileges are not effected. Is there something like this which you could use? Andre Hübner wrote: Thank you for answering. is there a way to do without granting super-privileg? for security reasons i cannot grant too high privileges for normal db- users. rights should be limited to own db. Thanks Andre - Original Message - From: Ben Clewett [EMAIL PROTECTED] To: Andre Hübner [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, February 20, 2008 9:46 AM Subject: Re: external mysqldump Try: mysql -u root -p mysql GRANT SUPER ON *.* TO myuser@'%'; mysql GRANT SUPER ON *.* TO myuser@'localhost'; Andre Hübner wrote: Hi List, i wrote this alrready in mysql-forum a few days ago, but did not get any answer. :( i try to do backup with mysqldump from external host with routines. mysqldump -R -h my.host.name -u myuser -p'mypass' mydb filename.sql I got error: myuser has insufficent privileges to SHOW CREATE FUNCTION `countuser`! It works if i do the same mysqldumLine directly on the server where db is installed. In mysql-table myuser has same privileges for % and locklhost. Are there some further restrictions for external connects? I dont have an idea what to change now. Thank you Andre -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql help: delete row where only related to one other row
Say I have two tables: table_a -- a_id (primary key) b_id table_b -- b_id (primary key) name there is a one to many mapping between rows in table b and rows in table a. Say I had an Id of a row in table a an (a_id, say 5). Now, what I want to do is delete the row in table_a (easy enough), but I also want to delete the related row in table_b, if it is ONLY related to the a_id of 5. In other words, I want to delete the row from table b, but I don't want to delete a row from table b that is in use by another row in table a. I'm thinking some type of subquery could do this, but I'm not sure. Can some one tell me how to do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL University session on February 21
Hi, this Thursday, Stewart Smith will give a MySQL University session: http://forge.mysql.com/wiki/Checking_Memory_With_Valgrind Please register for this session by filling in your name on the session Wiki page. Registering is not required but appreciated. That Wiki page also contains a section to post questions. Please use it! Those planning to attend a MySQL University session for the very first time should probably read the instructions for attendees, http://forge.mysql.com/wiki/Instructions_for_Attendees. Next MySQL University sessions: February 28: New Optimizer Features in MySQL 5.2 (Sergey Petrunia) March 6: How to Add a Collation (Alexander Barkov) March 13: Checking Threading and Locing With Helgrind (Stewart Smith) March 20: Building MySQL Client Applications (Hartmut Holzgraefe) -- Regards, Stefan Hinz [EMAIL PROTECTED], MySQL AB Documentation Manager Berlin, Germany (UTC +1:00/winter, +2:00/summer) Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Certified MySQL Associate (CMA) certification value
Hi, On Feb 20, 2008 6:13 AM, Thufir [EMAIL PROTECTED] wrote: I'm considering studying for: Certified MySQL Associate (CMA) http://www.mysql.com/certification/candguide#t21 At first glance, it seems to follow: http://www.mysql.com/training/courses/introduction_to_databases.html I can hit the bookstore to find out more, of course. But, what I was wondering was: what's the value of this certification for me? What doors will it open? In my opinion there is a tremendous shortage of qualified DBAs for MySQL. The certification may establish that you have at least a baseline of knowledge. If you're looking for a job, you could hardly go wrong with it, IMO. Take a look at one person's perspective: http://mysqldatabaseadministration.blogspot.com/2008/01/mysql-dumped-oracle-loaded-whose-fault.html And then, http://mysqldatabaseadministration.blogspot.com/2008/02/fotolog-seeks-mysql-dba.html I think those are the doors you can expect to open. Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Column level replication q?
Claudio, I didn't think about that approach either. The triggers will be much simpler to implement on the primary servers, then pull it over with a table rewrite to the intermediate server, then allow that to replicate out just fine. I'll play around with it a little. The table has millions of rows, but the primary data in the table that I really care about is relationship keys, which should be small if we put just that data into a intermediate table. Thanks, Gary Wayne Smith From: Nanni Claudio [mailto:[EMAIL PROTECTED] Sent: Wed 2/20/2008 1:19 AM To: Gary W. Smith; mysql@lists.mysql.com Subject: R: Column level replication q? Of course I am talking about a materialized view, did you try? Here some useful links: http://forums.mysql.com/read.php?100,21746,21746#msg-21746 and from the reply in the same forum topic by Waheed Noor 09/10/2007 01:39PM you get this link: http://www.shinguz.ch/MySQL/mysql_mv.html Aloha! Claudio -Messaggio originale- Da: Gary W. Smith [mailto:[EMAIL PROTECTED] Inviato: mercoledì 20 febbraio 2008 1.18 A: mysql@lists.mysql.com Oggetto: Column level replication q? We need to replicate a table to a third part. The information in the table is pretty much public, with the exception of one column. Is it possible to replicate all of the tables with the exception of one column? What I was thinking was to replication it to a 2nd machine that will limit it to the tables we care about and then expose that slave machine as a secondary master. We are looking to do something like this MasterA - SlaveA/MasterB - SlaveC MasterA tableA (our machine) field1 field2 field3 field4 SlaveA/MasterB (our machine) field1 field2 field4 SlaveC (their machine) field1 field2 field4 We know we can limit the tables which they can use (which is why we have introducted SlaveA/MasterB so they will only be able to pull the tables we make avaiable to them. Our current method is database dumps but this is become impracticle due to size. Replication in testing works for what we want to do, we just have a problem with a single field. Any ideas on how to make this work? Gary Wayne Smith Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto * This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person.
RE: Column level replication q?
Well, I created the lookup table, created my two triggers (as nothing is ever updated, just added or removed) and did a bulk one time load and it seems to work. Now I just need to replicate that to the other server. I'll have to find my easy button and press it a few times. In fact, on the other end, we don't even need to have the same table names. So we can replicate it just like this with no problem. Thanks for the links, Gary From: Nanni Claudio [mailto:[EMAIL PROTECTED] Sent: Wed 2/20/2008 1:19 AM To: Gary W. Smith; mysql@lists.mysql.com Subject: R: Column level replication q? Of course I am talking about a materialized view, did you try? Here some useful links: http://forums.mysql.com/read.php?100,21746,21746#msg-21746 and from the reply in the same forum topic by Waheed Noor 09/10/2007 01:39PM you get this link: http://www.shinguz.ch/MySQL/mysql_mv.html Aloha! Claudio -Messaggio originale- Da: Gary W. Smith [mailto:[EMAIL PROTECTED] Inviato: mercoledì 20 febbraio 2008 1.18 A: mysql@lists.mysql.com Oggetto: Column level replication q? We need to replicate a table to a third part. The information in the table is pretty much public, with the exception of one column. Is it possible to replicate all of the tables with the exception of one column? What I was thinking was to replication it to a 2nd machine that will limit it to the tables we care about and then expose that slave machine as a secondary master. We are looking to do something like this MasterA - SlaveA/MasterB - SlaveC MasterA tableA (our machine) field1 field2 field3 field4 SlaveA/MasterB (our machine) field1 field2 field4 SlaveC (their machine) field1 field2 field4 We know we can limit the tables which they can use (which is why we have introducted SlaveA/MasterB so they will only be able to pull the tables we make avaiable to them. Our current method is database dumps but this is become impracticle due to size. Replication in testing works for what we want to do, we just have a problem with a single field. Any ideas on how to make this work? Gary Wayne Smith Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto * This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person.
MySql, PostgreSQL, Java
Hi, We are developing a Java EE 5 based system in which we run our system using MySQL on our local test workstation machines. Each night our project is built and deployed on our test server, which is using PostgreSQL, it's the database we will be using in our production environment as well (for reasons other than MySQL being a bad technical choice). We are wondering if the collator 'utf8_swedish_ci' is some kind of international standard. So that you can make MySQL, PostgreSQL and Java match a string using the same criteria while still considering, for example, that Ä is the capital version of the Swedish letter ä (for case in-sensitive matching). Best regards, Kent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Clarification on SSL options needed
Hello, I'm trying to set up SSL for my MySQL server. As my understanding of SSL is still a bit shaky and the MySQL documentation on the subject a bit terse, I thought I'd ask a few questions to make sure I haven't misunderstood anything: 1. Is the --ssl-ca option to mysqld the public key used by the server to verify the identity of clients? In that case, is --ssl-ca unnecessary if the server doesn't need to verify client identifies? If it is always necessary, then what is it used for in case the server doesn't verify client identities? 2. Is the argument of the client's --ssl-ca option the public key used to verify the certificate received from the server? If the server is registered with a CA, Is it meant to be the public key of the CA the server is registered with? 3. What is the minimal number of certificates/keys that need to be generated/distributed in case the server doesn't need to verify client identities? If I've understood SSL correctly, it should just be a private key and a certificate (that contains the public key and is signed with the CA's private key) for the server, as well as the CA's public key for the client. Any help/clarification appreciated, Ulf Magnusson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How bad is adding BLOB column and index at the same time?
I am feeling more motivated to interrupt this operation now. What would be an effective way to do that? As a reminder, I submitted this operation using the GUI administration tool --- specifically the table editor. The whole tool is unresponsive while this operation is running. I can run and use another instance of the GUI administration tool. If I succeed in interrupting this operation, presumably the rollback will take quite a while. How can I tell whether I have successfully interrupted the operation and the rollback is what's nailing my machine? Thanks, Mike Spreitzer SMTP: [EMAIL PROTECTED], Lotus Notes: Mike Spreitzer/Watson/IBM Office phone: +1-914-784-6424 (IBM T/L 863-) AOL Instant Messaging: M1k3Sprtzr Mike Spreitzer/Watson/[EMAIL PROTECTED] 02/19/08 08:34 PM To Baron Schwartz [EMAIL PROTECTED] cc mysql@lists.mysql.com Subject Re: How bad is adding BLOB column and index at the same time? Thanks, Baron. Yes, the table is bigger than memory. It took about 2.5 days to create the table, inserting about 7,000 rows at a time; this column and index addition has been running for about a day now. I notice you did not say it was terribly stupid to create this index before putting the final data in the new column. So I infer there is no big motivation to interrupt the operation I have going. Thanks, Mike Baron Schwartz [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/19/08 08:13 PM To Mike Spreitzer/Watson/[EMAIL PROTECTED] cc mysql@lists.mysql.com Subject Re: How bad is adding BLOB column and index at the same time? Hi, On Feb 19, 2008 5:20 PM, Mike Spreitzer [EMAIL PROTECTED] wrote: I am new to MySQL, and wonder if I have done something terribly stupid. I have an InnoDB table with 27 million rows. Without thinking very much, I issued the following command through the GUI administration tool: ALTER TABLE `wyky`.`externallinks` ADD COLUMN `el_p2` BLOB NOT NULL AFTER `el_index`, ADD INDEX `el_p2`(`el_p2`(150)) Of course, all the values in this column will be the same. The index will, if this ever completes, indicate that one value is associated with all 27E6 rows. My primary question is, will this take O(27E6) time or something worse (e.g., O(27E6 squared) time)? Of course my plan is to eventually put some interesting data in that new column. I do not really need the index until the data is there. This database is not being used on-line, this is just for study, so I do not mind large batch operations. I just don't want to be grossly stupid in my choice of batch operations. My second question is: if I have indeed done something grossly stupid, what is the best (if there is any at all!) way to interrupt it and proceed. If the table is bigger than memory, building the index will be very slow. If you interrupt it, it's just going to roll back everything it's done so far. So you have the choice of either letting it finish and then dropping the index, or killing it and letting it roll back. (You can't prevent the roll back, even if you restart).
Re: How bad is adding BLOB column and index at the same time?
The only way you can do it is with KILL. You will be able to see some of the progress on rollback in SHOW INNODB STATUS. I recommend innotop for watching this if you wish. If you see the process's status as Killed in SHOW PROCESSLIST, you have killed it. Have a copy of War and Peace at the ready :-) On Wed, Feb 20, 2008 at 1:37 PM, Mike Spreitzer [EMAIL PROTECTED] wrote: I am feeling more motivated to interrupt this operation now. What would be an effective way to do that? As a reminder, I submitted this operation using the GUI administration tool --- specifically the table editor. The whole tool is unresponsive while this operation is running. I can run and use another instance of the GUI administration tool. If I succeed in interrupting this operation, presumably the rollback will take quite a while. How can I tell whether I have successfully interrupted the operation and the rollback is what's nailing my machine? Thanks, Mike Spreitzer SMTP: [EMAIL PROTECTED], Lotus Notes: Mike Spreitzer/Watson/IBM Office phone: +1-914-784-6424 (IBM T/L 863-) AOL Instant Messaging: M1k3Sprtzr Mike Spreitzer/Watson/[EMAIL PROTECTED] 02/19/08 08:34 PM To Baron Schwartz [EMAIL PROTECTED] cc mysql@lists.mysql.com Subject Re: How bad is adding BLOB column and index at the same time? Thanks, Baron. Yes, the table is bigger than memory. It took about 2.5 days to create the table, inserting about 7,000 rows at a time; this column and index addition has been running for about a day now. I notice you did not say it was terribly stupid to create this index before putting the final data in the new column. So I infer there is no big motivation to interrupt the operation I have going. Thanks, Mike Baron Schwartz [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/19/08 08:13 PM To Mike Spreitzer/Watson/[EMAIL PROTECTED] cc mysql@lists.mysql.com Subject Re: How bad is adding BLOB column and index at the same time? Hi, On Feb 19, 2008 5:20 PM, Mike Spreitzer [EMAIL PROTECTED] wrote: I am new to MySQL, and wonder if I have done something terribly stupid. I have an InnoDB table with 27 million rows. Without thinking very much, I issued the following command through the GUI administration tool: ALTER TABLE `wyky`.`externallinks` ADD COLUMN `el_p2` BLOB NOT NULL AFTER `el_index`, ADD INDEX `el_p2`(`el_p2`(150)) Of course, all the values in this column will be the same. The index will, if this ever completes, indicate that one value is associated with all 27E6 rows. My primary question is, will this take O(27E6) time or something worse (e.g., O(27E6 squared) time)? Of course my plan is to eventually put some interesting data in that new column. I do not really need the index until the data is there. This database is not being used on-line, this is just for study, so I do not mind large batch operations. I just don't want to be grossly stupid in my choice of batch operations. My second question is: if I have indeed done something grossly stupid, what is the best (if there is any at all!) way to interrupt it and proceed. If the table is bigger than memory, building the index will be very slow. If you interrupt it, it's just going to roll back everything it's done so far. So you have the choice of either letting it finish and then dropping the index, or killing it and letting it roll back. (You can't prevent the roll back, even if you restart). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I've been looking for a way to fake replication from mysql to a local BDB database. I'm not finding anything. Anybody ever come across this? It seems like it wouldn't be too terribly difficult to read from the relay-log and keep track of where you are. But not having written anything like this before, I'm far from authoritative. Any comments or suggestions? (For reference, I'm implementing Bind-DLZ. I'm going to replicate tables from a multi-master server to a couple of slaves running on the nameservers. Then I want a daemon to read that relay-log and replicate those commands into a local BDB database. The reason is for maximum speed.) - -- Regards... Todd we're off on the usual strange tangents. next will be whether it is ethical to walk in your neighbor's open house if they're running ipv6:-). --Randy Bush Linux kernel 2.6.22-14-generic 4 users, load average: 0.18, 0.06, 0.02 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHvKwfY2VBGxIDMLwRAuYJAJ9vwyx/iZ2iNjR6hk5vTT57pmViJgCeJ6sx QbkxNY3AbnTtRU7z2YqyWuU= =ryok -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizer problem?
I have the following table:
--
-- Table structure for table 'media'
--
CREATE TABLE media (
id int(10) unsigned NOT NULL auto_increment,
user_id int(10) unsigned default NULL,
title varchar(255) NOT NULL,
description text NOT NULL,
`hash` varchar(255) NOT NULL,
length float(9,2) NOT NULL,
created timestamp NOT NULL default CURRENT_TIMESTAMP,
`type` enum('video','image') default NULL,
`status`
enum('new','processing','suspended','active','deleted','failed','pending')
NOT NULL default 'new',
flags int(20) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY `hash` (`hash`),
KEY `type` (`type`),
KEY user_id (user_id),
KEY created (created),
KEY `status` (`status`),
KEY flags (flags)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
the table has about 200,000 rows.
the following query takes about .56 seconds on a completely empty system:
SELECT *
FROM
media
WHERE
media.status = 'active' AND
user_id = '190' AND
id != '13660' AND
media.flags 3 = 0
and media.type = 'video'
ORDER BY
media.id DESC LIMIT 0, 6
When I do explain, I can see it shows PRIMARY as a viable index to use, but
instead its using and index merge with user_id,status,type.
when I add use index (PRIMARY), the query drops to 0.02.
Any ideas why the optimizer isn't using the primary? since i'm ordering by
that, it seems it would make sense to use that.
Re: Optimizer problem?
Hi,
On Wed, Feb 20, 2008 at 7:23 PM, Tanner Postert
[EMAIL PROTECTED] wrote:
I have the following table:
--
-- Table structure for table 'media'
--
CREATE TABLE media (
id int(10) unsigned NOT NULL auto_increment,
user_id int(10) unsigned default NULL,
title varchar(255) NOT NULL,
description text NOT NULL,
`hash` varchar(255) NOT NULL,
length float(9,2) NOT NULL,
created timestamp NOT NULL default CURRENT_TIMESTAMP,
`type` enum('video','image') default NULL,
`status`
enum('new','processing','suspended','active','deleted','failed','pending')
NOT NULL default 'new',
flags int(20) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY `hash` (`hash`),
KEY `type` (`type`),
KEY user_id (user_id),
KEY created (created),
KEY `status` (`status`),
KEY flags (flags)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
the table has about 200,000 rows.
the following query takes about .56 seconds on a completely empty system:
SELECT *
FROM
media
WHERE
media.status = 'active' AND
user_id = '190' AND
id != '13660' AND
media.flags 3 = 0
and media.type = 'video'
ORDER BY
media.id DESC LIMIT 0, 6
When I do explain, I can see it shows PRIMARY as a viable index to use, but
instead its using and index merge with user_id,status,type.
when I add use index (PRIMARY), the query drops to 0.02.
Any ideas why the optimizer isn't using the primary? since i'm ordering by
that, it seems it would make sense to use that.
As far as I know, the optimizer's cost metric doesn't account for the
extra work caused by merging the index scans, so it probably thinks
it'll be cheaper to do so. I think this is one of the cases where the
human is smarter than the optimizer.
I always try to avoid manually adding hints, but sometimes you have
to. It may be a good idea to check when you upgrade MySQL and
determine if it has gotten smart enough to execute the query faster
than your forced execution plan. Otherwise you might be locking it
into a worse plan than it might be able to use in future versions.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Certified MySQL Associate (CMA) certification value
On Wed, Feb 20, 2008 at 1:51 PM, Baron Schwartz [EMAIL PROTECTED] wrote: [...] In my opinion there is a tremendous shortage of qualified DBAs for MySQL. The certification may establish that you have at least a baseline of knowledge. If you're looking for a job, you could hardly go wrong with it, IMO. [...] Yeah, I think that I'm going for the certification, although I can't imagine what kind of job I could land -- junior DB admin? I don't have a college degree :( -Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Moving to another hard drive
I am trying to relocate MySQL and a database to another hard drive on the same system. According to this page, http://dev.mysql.com/doc/refman/5.0/en/upgrading-to-arch.html, I get the impression that I should be able to do this by copying the data files to the new MySQL installation. However, that doesn't seem to be sufficient as MySQL does not see the database that was copied over. - Looking for last minute shopping deals? Find them fast with Yahoo! Search.
