Re: Prevent execution of queries without a WHERE clause
Hi Darren, Once I knew the name of the command I could Google it and find all the relevant documentation, so I've put additional directives in place to override the limits on SELECTS. Thanks for the thought, though. On Tue, 2009-09-08 at 15:32 +0100, Darren Cassar wrote: > Hi, > > You can check this feature out on section 2.9 on the mysql 5.0 > certification guide (page 44). > > this feature has some side effects other than requesting a where with > deletes and updates i.e. > Quoting for whoever doesn't have the MySQL certification study guide > 1.UPDATE and DELETE statments are allowed only if then include a WHERE > clause that spedifically identifies which records to update or delete > by means of a key,value or if they include a LIMIT clause. > 2. Output from single-table SELECT statments is restricted to no more > than 1,000 rows unless the statment include a LIMIT clause > 3. Multiple-table SELECT statments are allowed only if MySQL will > examine no more than 1,000,000 rows to process the query. > > The --i-am-a-dummy option is a synonym for --safe-updates. :) > > Gluck > > Darren > > On Tue, Sep 8, 2009 at 10:25 AM, Ian Simpson > wrote: > Thanks John, that's done the trick > > > On Mon, 2009-09-07 at 18:24 +0100, John Daisley wrote: > > Add the option 'safe-updates' to the mysql section of your > 'my.cnf' / 'my.ini' file and restart the mysqld service. > > > > [mysql] > > Safe-updates > > > > Regards > > > > John Daisley > > Mobile +44(0)7812 451238 > > Email j...@butterflysystems.co.uk > > > > Certified MySQL 5 Database Administrator (CMDBA) > > Certified MySQL 5 Developer > > Cognos BI Developer > > > > --- > > Sent from HP IPAQ mobile device. > > > > > > > > -Original Message- > > From: Ian Simpson > > Sent: Monday, September 07, 2009 5:14 PM > > To: mysql@lists.mysql.com > > Subject: Prevent execution of queries without a WHERE clause > > > > Hi all, > > > > I vaguely recall finding mention a MySQL command or start-up > option that > > blocked any update or delete query that didn't have a WHERE > component, > > to prevent statements accidentally affecting too many rows > (like those > > of a certain absent-minded web developer who might work for > the same > > company as me...). I now can't find any reference to it, > other than a > > vague mention of using safe mode in the comments in the > mysql docs; it > > doesn't explain if that is starting with --safe-mode, or > using the > > mysqld_safe script. Both of these modes seem remarkably > poorly > > documented, making me unwilling to experiment with them > without advice, > > in case one of them disables networking or something > similar. > > > > Hoping someone can help with this. > > > > Thanks > > -- > > Ian Simpson > > System Administrator > > MyJobGroup > > > > > -- > Ian Simpson > System Administrator > MyJobGroup > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/mysql?unsub=i...@mysqlpreacher.com > > -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Prevent execution of queries without a WHERE clause
Hi, You can check this feature out on section 2.9 on the mysql 5.0 certification guide (page 44). this feature has some side effects other than requesting a where with deletes and updates i.e. Quoting for whoever doesn't have the MySQL certification study guide 1.UPDATE and DELETE statments are allowed only if then include a WHERE clause that spedifically identifies which records to update or delete by means of a key,value or if they include a LIMIT clause. 2. Output from single-table SELECT statments is restricted to no more than 1,000 rows unless the statment include a LIMIT clause 3. Multiple-table SELECT statments are allowed only if MySQL will examine no more than 1,000,000 rows to process the query. The --i-am-a-dummy option is a synonym for --safe-updates. :) Gluck Darren On Tue, Sep 8, 2009 at 10:25 AM, Ian Simpson wrote: > Thanks John, that's done the trick > > On Mon, 2009-09-07 at 18:24 +0100, John Daisley wrote: > > Add the option 'safe-updates' to the mysql section of your 'my.cnf' / > 'my.ini' file and restart the mysqld service. > > > > [mysql] > > Safe-updates > > > > Regards > > > > John Daisley > > Mobile +44(0)7812 451238 > > Email j...@butterflysystems.co.uk > > > > Certified MySQL 5 Database Administrator (CMDBA) > > Certified MySQL 5 Developer > > Cognos BI Developer > > > > --- > > Sent from HP IPAQ mobile device. > > > > > > > > -Original Message- > > From: Ian Simpson > > Sent: Monday, September 07, 2009 5:14 PM > > To: mysql@lists.mysql.com > > Subject: Prevent execution of queries without a WHERE clause > > > > Hi all, > > > > I vaguely recall finding mention a MySQL command or start-up option that > > blocked any update or delete query that didn't have a WHERE component, > > to prevent statements accidentally affecting too many rows (like those > > of a certain absent-minded web developer who might work for the same > > company as me...). I now can't find any reference to it, other than a > > vague mention of using safe mode in the comments in the mysql docs; it > > doesn't explain if that is starting with --safe-mode, or using the > > mysqld_safe script. Both of these modes seem remarkably poorly > > documented, making me unwilling to experiment with them without advice, > > in case one of them disables networking or something similar. > > > > Hoping someone can help with this. > > > > Thanks > > -- > > Ian Simpson > > System Administrator > > MyJobGroup > > > > > -- > Ian Simpson > System Administrator > MyJobGroup > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=i...@mysqlpreacher.com > >
RE: Prevent execution of queries without a WHERE clause
Thanks John, that's done the trick On Mon, 2009-09-07 at 18:24 +0100, John Daisley wrote: > Add the option 'safe-updates' to the mysql section of your 'my.cnf' / > 'my.ini' file and restart the mysqld service. > > [mysql] > Safe-updates > > Regards > > John Daisley > Mobile +44(0)7812 451238 > Email j...@butterflysystems.co.uk > > Certified MySQL 5 Database Administrator (CMDBA) > Certified MySQL 5 Developer > Cognos BI Developer > > --- > Sent from HP IPAQ mobile device. > > > > -Original Message- > From: Ian Simpson > Sent: Monday, September 07, 2009 5:14 PM > To: mysql@lists.mysql.com > Subject: Prevent execution of queries without a WHERE clause > > Hi all, > > I vaguely recall finding mention a MySQL command or start-up option that > blocked any update or delete query that didn't have a WHERE component, > to prevent statements accidentally affecting too many rows (like those > of a certain absent-minded web developer who might work for the same > company as me...). I now can't find any reference to it, other than a > vague mention of using safe mode in the comments in the mysql docs; it > doesn't explain if that is starting with --safe-mode, or using the > mysqld_safe script. Both of these modes seem remarkably poorly > documented, making me unwilling to experiment with them without advice, > in case one of them disables networking or something similar. > > Hoping someone can help with this. > > Thanks > -- > Ian Simpson > System Administrator > MyJobGroup > > -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Prevent execution of queries without a WHERE clause
Add the option 'safe-updates' to the mysql section of your 'my.cnf' / 'my.ini' file and restart the mysqld service. [mysql] Safe-updates Regards John Daisley Mobile +44(0)7812 451238 Email j...@butterflysystems.co.uk Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer Cognos BI Developer --- Sent from HP IPAQ mobile device. -Original Message- From: Ian Simpson Sent: Monday, September 07, 2009 5:14 PM To: mysql@lists.mysql.com Subject: Prevent execution of queries without a WHERE clause Hi all, I vaguely recall finding mention a MySQL command or start-up option that blocked any update or delete query that didn't have a WHERE component, to prevent statements accidentally affecting too many rows (like those of a certain absent-minded web developer who might work for the same company as me...). I now can't find any reference to it, other than a vague mention of using safe mode in the comments in the mysql docs; it doesn't explain if that is starting with --safe-mode, or using the mysqld_safe script. Both of these modes seem remarkably poorly documented, making me unwilling to experiment with them without advice, in case one of them disables networking or something similar. Hoping someone can help with this. Thanks -- Ian Simpson System Administrator MyJobGroup -- 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 [The entire original message is not included] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Prevent execution of queries without a WHERE clause
Hi all, I vaguely recall finding mention a MySQL command or start-up option that blocked any update or delete query that didn't have a WHERE component, to prevent statements accidentally affecting too many rows (like those of a certain absent-minded web developer who might work for the same company as me...). I now can't find any reference to it, other than a vague mention of using safe mode in the comments in the mysql docs; it doesn't explain if that is starting with --safe-mode, or using the mysqld_safe script. Both of these modes seem remarkably poorly documented, making me unwilling to experiment with them without advice, in case one of them disables networking or something similar. Hoping someone can help with this. Thanks -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
