Supported for IPv6?
Hi,MySQL, Does MySQL support for IPv6?
75% discount during the Database Workbench pro Secret Super Sale!
Upscene Productions has a Secret Super Sale on Database Workbench Pro, the database developer IDE. This is a 75% discount on the normal price, check for details: http://www.upscene.com/dbw_secretsupersale.php Valid for one week only, March the 23rd to 28th. With regards, Martijn Tonies Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Does MySQL 5.1 store queries?
Until recently I have been using 4.0.25 and have just upgraded to 5.1 and just wondered if MySQL now enabled me to store queries in the database rather than have to put them all on my pages. Basically, I want to be able to write some select statements and save them in the database. Thanks. Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does MySQL 5.1 store queries?
Mat, On Tue, Mar 17, 2009 at 2:04 PM, Matthew Stuart m...@btinternet.com wrote: Until recently I have been using 4.0.25 and have just upgraded to 5.1 and just wondered if MySQL now enabled me to store queries in the database rather than have to put them all on my pages. Basically, I want to be able to write some select statements and save them in the database. Yes, http://dev.mysql.com/doc/refman/5.1/en/stored-routines.html Ewen Thanks. Mat -- 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
Re: Does MySQL 5.1 store queries?
Until recently I have been using 4.0.25 and have just upgraded to 5.1 and just wondered if MySQL now enabled me to store queries in the database rather than have to put them all on my pages. Basically, I want to be able to write some select statements and save them in the database. That depends how you look at it... Do you want VIEWs? If so, then yes. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Is there any solution
Hi list, THE IDEA IS TO HAVE A COMMON LOGIN I have two mysql servers with different databases on each of them. I want to search each databases(few tables) on both the server using a single login(mysql connection) Procedure is working fine.but then i have to use two logins(mysql connection) (Federated is not working efficiently) Is there any solution apart from scripting. -- Krishna
Re: Is there any solution
Kishhna, On Tue, Mar 17, 2009 at 4:13 PM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi list, THE IDEA IS TO HAVE A COMMON LOGIN I have two mysql servers with different databases on each of them. I want to search each databases(few tables) on both the server using a single login(mysql connection) Procedure is working fine.but then i have to use two logins(mysql connection) (Federated is not working efficiently) Is there any solution apart from scripting. Maybe this is too much overhead, but you could have something like sphinx search index both servers and search there instead. Cheers, Ewen -- Krishna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does MySQL 5.1 store queries?
Matthew, wondered if MySQL now enabled me to store queries in the database In three ways---stored procedures; views; and prepared statements built from query strings that come from anywhere including your own tables. PB http://www.artfulsoftware.com - Matthew Stuart wrote: Until recently I have been using 4.0.25 and have just upgraded to 5.1 and just wondered if MySQL now enabled me to store queries in the database rather than have to put them all on my pages. Basically, I want to be able to write some select statements and save them in the database. Thanks. Mat No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.0.237 / Virus Database: 270.11.17/2007 - Release Date: 03/17/09 10:18:00
Re: JOB: linux sysadmin with good mysql skills [think really mysql dba] - LOCATION: reading, berkshire, england, uk - SALARY: £40k-£55k at least!
On Tue, 2009-03-17 at 17:10 +0100, Jon Stephens wrote: Please don't post off-topic mails to the MySQL Cluster list. Jon hi, I couldn't see anything that read such posts were not permitted. That said, I have tried to present the post as clearly and informative as possible. Hope people agree. If you or anyone else could let me know if such posts are permitted to the general MySQL list alone (the announcement/ general one) that would be smashing. I don't want to stir up an OT debate regarding this posting or to upset the list etc. thanks, James :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how can I make a stored procedure executable by public?
I am writing a tracking procedure that will be inserted into every procedure (regardless of who writes the procedure) that will insert a record into an audit table. This means the procedure, regardless of who writes it, must have the permission to insert into the table. I am going to modify the code of the procedures once they're stored in the database and the authors of the procedures will probably not know that I will be doing it (although it's not really a secret) and the way they code will not be altered in any way. I would like to write a grant command like: grant insert on mydb.audit_table to public but I don't see anything in the manual Is there any way that I can do this. I know I can grant ALL privileges to a user, but I want to grant one privilege to all users, without having to loop through the mysql.user table and explicitly granting the insert privilege. I guess I could put it in test, but then everyone could do anything with it, which would not be particularly desirable. The table should be insert only, not readable or updateable by anyone but the owner of mydb. Is there any way I can do this? Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Negated SELECT query
3 tables are related by one-many links. Employees Assets Maintenance Employees can be assigned = 0 Assets Assets can have = 0 occurances of Maintenance. Assets table contains EmployeeIDs and MaintenanceIDs, but no Foreign Key contraints. Queries ... 1) which Employees do not have any Assets ? 2) which Assets have not had any Maintenance ? These have been written successfully with Sub-Queries, I would like to know how they can be done with only JOINs ? (that also means without the EXCEPT statement) Is that too much of a challenge ? (MySQL 5.0.67) -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12747 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
Re: JOB: linux sysadmin with good mysql skills [think really'mysql dba] - LOCATION: reading , berkshire, england, uk - SALARY:£40k-£55k at l east!
James, I'm guessing this is the same position you sent me direct, sorry for not getting back to you, it slipped my mind. Sadly the location is not suitable for me. Can I suggest you post this on the MySQL website in the Jobs Forum? You should get a good response from there as its a good oportunity with what appears to be a good package attached. Regards John On Tue, 2009-03-17 at 17:10 +0100, Jon Stephens wrote: Please don't post off-topic mails to the MySQL Cluster list. Jon hi, I couldn't see anything that read such posts were not permitted. That said, I have tried to present the post as clearly and informative as possible. Hope people agree. If you or anyone else could let me know if such posts are permitted to the general MySQL list alone (the announcement/ general one) that would be smashing. I don't want to stir up an OT debate regarding this posting or to upset the list etc. thanks, James :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: JOB: linux sysadmin with good mysql skills [thin k really mysql dba] - LOCATION: reading, berkshire, england , uk - SALARY: £40k-£55k at least!
On Tue, Mar 17, 2009 at 12:25 PM, j...@camalyn.org j...@camalyn.org wrote: On Tue, 2009-03-17 at 17:10 +0100, Jon Stephens wrote: Please don't post off-topic mails to the MySQL Cluster list. Jon hi, I couldn't see anything that read such posts were not permitted. That said, I have tried to present the post as clearly and informative as possible. Hope people agree. If you or anyone else could let me know if such posts are permitted to the general MySQL list alone (the announcement/ general one) that would be smashing. I don't want to stir up an OT debate regarding this posting or to upset the list etc. thanks, James :) James, My suggestion is just to ignore any objections and post jobs anyway; unless you find that there is an explicit mailing list or bulletin board for MySQL jobs. Here are my thoughts on that: a)First, employment is important to most individuals. If you happen to pair up someone needing a job with someone needing an employee ... it raises the quality of life for the individual who gets a job they like ... and I'm willing waste 30 seconds of my time a week reading job postings. b)Second, if it ends up that there isn't a better place to post MySQL jobs, you and others posting jobs (and the minor controversey it creates) will bring this oversight out in the open. Then, if it doesn't already exist, they might create a more formal mechanism for posting jobs. In other words, your job posting may be revealing an oversight in the system, and I don't see any harm in bringing attention to this oversight. My suggestion is to continue unless you find a better MySQL-specific forum to post these jobs ... Dave A.
Re: Negated SELECT query
On Tue, Mar 17, 2009 at 12:42 PM, BobSharp bobsh...@ntlworld.com wrote: These have been written successfully with Sub-Queries, I would like to know how they can be done with only JOINs ? http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: JOB: linux sysadmin with good mysql skills [think really mysql dba] - LOCATION: reading, berkshire, england, uk - SALARY: £40k-£55k at least!
On Tue, 2009-03-17 at 12:56 -0400, Jujitsu Lizard wrote: On Tue, Mar 17, 2009 at 12:25 PM, j...@camalyn.org j...@camalyn.org wrote: On Tue, 2009-03-17 at 17:10 +0100, Jon Stephens wrote: Please don't post off-topic mails to the MySQL Cluster list. Jon hi, I couldn't see anything that read such posts were not permitted. That said, I have tried to present the post as clearly and informative as possible. Hope people agree. If you or anyone else could let me know if such posts are permitted to the general MySQL list alone (the announcement/ general one) that would be smashing. I don't want to stir up an OT debate regarding this posting or to upset the list etc. thanks, James :) James, My suggestion is just to ignore any objections and post jobs anyway; unless you find that there is an explicit mailing list or bulletin board for MySQL jobs. Here are my thoughts on that: a)First, employment is important to most individuals. If you happen to pair up someone needing a job with someone needing an employee ... it raises the quality of life for the individual who gets a job they like ... and I'm willing waste 30 seconds of my time a week reading job postings. b)Second, if it ends up that there isn't a better place to post MySQL jobs, you and others posting jobs (and the minor controversey it creates) will bring this oversight out in the open. Then, if it doesn't already exist, they might create a more formal mechanism for posting jobs. In other words, your job posting may be revealing an oversight in the system, and I don't see any harm in bringing attention to this oversight. My suggestion is to continue unless you find a better MySQL-specific forum to post these jobs ... Dave A. __ This email has been scanned by Netintelligence http://www.netintelligence.com/email With you on that one Dave, job postings are a good thing! What could be better than having opportunities delivered right to your inbox so you don't have to go looking for them. Maybe one day the right job for me will come along and I will escape the world of Cognos BI :p James - As a specialist open source recruiter does your company have a mailing list of its own for sending out opportunities to prospective candidates? Regards John
Re: JOB: linux sysadmin with good mysql skills [think really mysql dba] - LOCATION: reading, berkshire, england, uk - SALARY: £40k-£55k at least!
Massimo, First, I don't see this is spam, every reply I have seen on the list, with the exception of those from yourself and another Sun employee, agree that James post was of benefit to list the members, particularly in the current economic climate. Surely if list members think the message is beneficial then its not spam! I also do not see that messages I have sent are spam, they were replies to messages on the list! Regards John Daisley Email: john.dais...@butterflysystems.co.uk Mobile: 07812 451238 MySQL Certified Database Administrator (CMDBA) MySQL Certified Developer (CMDEV) MySQL Certified Associate (CMA) Comptia A+ Certified Professional IT Technician Cognos BI Developer On Tue, 2009-03-17 at 21:48 +0100, Massimo Brignoli wrote: Can you please stop spamming all the mailing lists??? If you want to ask something to James, please do it privately. Thank you Massimo Il giorno 17/mar/09, alle ore 19:15, John Daisley ha scritto: On Tue, 2009-03-17 at 12:56 -0400, Jujitsu Lizard wrote: On Tue, Mar 17, 2009 at 12:25 PM, j...@camalyn.org j...@camalyn.org wrote: On Tue, 2009-03-17 at 17:10 +0100, Jon Stephens wrote: Please don't post off-topic mails to the MySQL Cluster list. Jon hi, I couldn't see anything that read such posts were not permitted. That said, I have tried to present the post as clearly and informative as possible. Hope people agree. If you or anyone else could let me know if such posts are permitted to the general MySQL list alone (the announcement/ general one) that would be smashing. I don't want to stir up an OT debate regarding this posting or to upset the list etc. thanks, James :) James, My suggestion is just to ignore any objections and post jobs anyway; unless you find that there is an explicit mailing list or bulletin board for MySQL jobs. Here are my thoughts on that: a)First, employment is important to most individuals. If you happen to pair up someone needing a job with someone needing an employee ... it raises the quality of life for the individual who gets a job they like ... and I'm willing waste 30 seconds of my time a week reading job postings. b)Second, if it ends up that there isn't a better place to post MySQL jobs, you and others posting jobs (and the minor controversey it creates) will bring this oversight out in the open. Then, if it doesn't already exist, they might create a more formal mechanism for posting jobs. In other words, your job posting may be revealing an oversight in the system, and I don't see any harm in bringing attention to this oversight. My suggestion is to continue unless you find a better MySQL- specific forum to post these jobs ... Dave A. __ This email has been scanned by Netintelligence http://www.netintelligence.com/email With you on that one Dave, job postings are a good thing! What could be better than having opportunities delivered right to your inbox so you don't have to go looking for them. Maybe one day the right job for me will come along and I will escape the world of Cognos BI :p James - As a specialist open source recruiter does your company have a mailing list of its own for sending out opportunities to prospective candidates? Regards John __ This email has been scanned by Netintelligence http://www.netintelligence.com/email
RE: Negated SELECT query
SELECT Employees.* FROM Employees LEFT JOIN Assets ON Employess.EmployeeID = Assets.EmployeeID WHERE Assets.EmployeeID IS NULL The one for assets with no maintenance is similar. The point is the left join above produces in its output all rows from the Employees table regardless of whether anything matches in the assets table. By selecting only rows where the foreign key field in the assets table is null gives you the employees having no assets. John Bonnett -Original Message- From: BobSharp [mailto:bobsh...@ntlworld.com] Sent: Wednesday, 18 March 2009 3:13 AM To: mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Subject: Negated SELECT query 3 tables are related by one-many links. Employees Assets Maintenance Employees can be assigned = 0 Assets Assets can have = 0 occurances of Maintenance. Assets table contains EmployeeIDs and MaintenanceIDs, but no Foreign Key contraints. Queries ... 1) which Employees do not have any Assets ? 2) which Assets have not had any Maintenance ? These have been written successfully with Sub-Queries, I would like to know how they can be done with only JOINs ? (that also means without the EXCEPT statement) Is that too much of a challenge ? (MySQL 5.0.67) -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12747 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Negated SELECT query
Thanks for that,worked through and found that this gives the correct result ... --- Employee No Assets --- SELECT DISTINCT e.employeeID AS eID, concat(e.firstname, , e.lastname) AS eName FROM employees e LEFT JOIN assets a ON e.employeeID = a.employeeID WHERE e.employeeID IS NULL ORDER BY e.employeeID --- Employee No History --- SELECT DISTINCT a.assetID AS aCode, LEFT(a.assetdescription,60) AS aTitle, c.assetcategory AS cCategory FROM assets a LEFT JOIN maintenance m ON m.assetID = a.assetID LEFT JOIN assetcategories c ON a.assetcategoryID = c.assetcategoryID WHERE m.assetID IS NULL ORDER BY a.assetID Cheers - Original Message - From: Bonnett, John john.bonn...@vision.zeiss.com To: bobsh...@ntlworld.com; mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Sent: Tuesday, March 17, 2009 10:59 PM Subject: RE: Negated SELECT query SELECT Employees.* FROM Employees LEFT JOIN Assets ON Employess.EmployeeID = Assets.EmployeeID WHERE Assets.EmployeeID IS NULL The one for assets with no maintenance is similar. The point is the left join above produces in its output all rows from the Employees table regardless of whether anything matches in the assets table. By selecting only rows where the foreign key field in the assets table is null gives you the employees having no assets. John Bonnett -Original Message- From: BobSharp [mailto:bobsh...@ntlworld.com] Sent: Wednesday, 18 March 2009 3:13 AM To: mysql@lists.mysql.com Cc: wi...@lists.mysql.com; mysql-h...@lists.mysql.com Subject: Negated SELECT query 3 tables are related by one-many links. Employees Assets Maintenance Employees can be assigned = 0 Assets Assets can have = 0 occurances of Maintenance. Assets table contains EmployeeIDs and MaintenanceIDs, but no Foreign Key contraints. Queries ... 1) which Employees do not have any Assets ? 2) which Assets have not had any Maintenance ? These have been written successfully with Sub-Queries, I would like to know how they can be done with only JOINs ? (that also means without the EXCEPT statement) Is that too much of a challenge ? (MySQL 5.0.67) -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12747 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=bobsh...@ntlworld.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.0.237 / Virus Database: 270.11.18/2008 - Release Date: 03/17/09 16:25:00 -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12747 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Separate customer databases vs all in one
I'm writing a report tool wherein we have many customers who subscribe to this SaaS. There are millions of rows of data per customer. All customers are islands from each other (of course). Are there any major issues or benefits between storing each customer in their own database (with their own tables), or all lumped into a single database? At first thought, it seems that by separating them, queries should be faster no (as there is less data to sift though per customer)? It of course makes upgrading table schema a wee bit more cumbersome, but a simple loop and script can handle that easily enough. And since you can query across databases, we can still make internal aggregate reports for our own usage. For example: SELECT * FROM customerA.foo.bar JOIN customerB.foo.bar; or we can use UNIONS etc. too. Consolidating them into one would seem to bloat the tables and slow things down (or is the fact that mySQL uses B-Trees invalidate that theory)? It also makes us have to have a customer_id entry in every table basically (or some FK to distinguish who's data is who's). It also feels like it could leak data if a malformed query were to get through, although I'm not terribly worried about this as we do some heavy UAT before pushing from DEV to TEST to PROD. Performance is a major factor concern here given our huge data sets involved. Does joining across databases impose any speed/performance hits vs. just joining across tables within a single database? http://daevid.com
Re: Separate customer databases vs all in one
Are these databases identical or merely similar? If they are structurally identical, I'd go for one database per customer. Then you have isolation, easy structure updates and above all, consistent front-end code, in whatever language that occurs. Just obtain the customer ID and then use the appropriate database. Everything else can remain the same. The only fly in the ointment concerns whether you'd ever have the customer need to cross databases. I would imagine that sort of thing is for internal use, not the customers. In that case, the performance hit if any won't impact upon the customer, just you. hth, Arthur On Tue, Mar 17, 2009 at 8:21 PM, Daevid Vincent dae...@daevid.com wrote: I'm writing a report tool wherein we have many customers who subscribe to this SaaS. There are millions of rows of data per customer. All customers are islands from each other (of course). Are there any major issues or benefits between storing each customer in their own database (with their own tables), or all lumped into a single database? At first thought, it seems that by separating them, queries should be faster no (as there is less data to sift though per customer)? It of course makes upgrading table schema a wee bit more cumbersome, but a simple loop and script can handle that easily enough. And since you can query across databases, we can still make internal aggregate reports for our own usage. For example: SELECT * FROM customerA.foo.bar JOIN customerB.foo.bar; or we can use UNIONS etc. too. Consolidating them into one would seem to bloat the tables and slow things down (or is the fact that mySQL uses B-Trees invalidate that theory)? It also makes us have to have a customer_id entry in every table basically (or some FK to distinguish who's data is who's). It also feels like it could leak data if a malformed query were to get through, although I'm not terribly worried about this as we do some heavy UAT before pushing from DEV to TEST to PROD. Performance is a major factor concern here given our huge data sets involved. Does joining across databases impose any speed/performance hits vs. just joining across tables within a single database? http://daevid.com