feature request: statement SELECT...(INSERT|UPDATE) :)

[Dmitry E. Oboukhov]

Hi!

I use mysql on amateurish level mainly for personal needs and so please
forgive me if this feature request is impossible to fulfil or if it is
sent to the wrong mail-list, or if this functionality has been already
realized in other ways :)

Note: I've read the discription of C-function mysql_insert_id() and the
discription of SELECT LAST_INSERT_ID() in new versions, but as far as
I've understood it concerns only AUTOINCREMENT columns, and very often it
isn't enough (some columns may be filled by mysql functions (for example
data/time/mathematical functions etc) and very often the resulting
values are needed at once for report representation or for using in
the next statements INSERT)

So: The INSERT(UPDATE) statement returns the quantity of
strings inserted(updated) into a table.

(*) However as far as I understand at the stage of fulfilling this
operator mysql operates with these very strings.

Is it possible to add to the syntax of the INSERT operator appoximately
in such way:

INSERT [IGNORE] INTO ... - a general syntax

SELECT list INSERT [IGNORE] INTO ... - an added one.

UPDATE ... - a general syntax

SELECT list UPDATE  - an added one.

Where can it be needed?

Example 1
~

We have a data base:

table (id, name, value1, value2, value3)

Now let's imagine CGI-script which makes an insert(update) and shows
the results to a user.

In the current case we need to:
1. INSERT INTO table (name, value1, value2, value3) VALUES
(?,?,?,?), (?,?,?,?),(?,?,?,?);
2. SELECT * FROM table WHERE ...

while the server has all the data needed for the step 2 already on the
step 1 :)

When selecting a few inserted strings at once we have either a complex
expression in the statement WHERE, or we need to split
the INSERT call into few single ones and replace 1 and 2 by the sequence
INSERT - SELECT - INSERT - SELECT.

It would be excellent to write:
SELECT * INSERT table (name, value1, value2, value3)
VALUES (?,?,?,?), (?,?,?,?), (?,?,?,?);
and, having on the entry the data for the insert on the exit, to get the
result of insert at once (and to display it if necessary)

similar UPDATE statement:

SELECT column1, column2
UPDATE table SET column3=value, column4=value
WHERE ...;

Example 2:
~~

For example we have a data base:

table1 (id, name) -- id - AUTOINCREMENT
table2 (id, table1_id, value1)
table3 (id, table1_id, value2)

Now let's imagine CGI-script working with such data base.
In case if it makes a data insert into this data base we need to:
1. INSERT [IGNORE] INTO table1 SET name=?

2. SELECT id FROM table1 WHERE name=?
 or SELECT LAST_INSERT_ID() as id;

3. INSERT INTO table2 SET value1=?, table1_id=? -- value, id_fromt1
   INSERT INTO table3 SET value2=?, table1_id=? -- value, id_fromt1


In the current situation all the data necessary for fulfillment of the
point 3 are actually available on mysql server when fulfilling point 1
however unfortunately it is impossible to extract them by making an
additional request :(

But if we had an additional syntax then we could unite points 1 and 2:

SELECT id INSERT INTO table1 SET name=?

And in some cases even points 1,2,3 altogether:

We insert in all the tables at once:
INSERT INTO table3 (table1_id, value2)
SELECT table1_id, ? INSERT INTO table2 (table1_id, value1)
SELECT id, ? INSERT INTO table1 SET name=?;
-- value2, value1, name

That is by adding the mirror statement SELECT...INSERT to the existing
statement INSERT...SELECT
we would gain a very interesting functionality, allowing sometimes to
get rid of using transactions and (or) to refuse from storage procedures
and to replace the mass colls by the single ones etc.

PS: I understand that adding the changes into a language is a very
serious question that needs a great discussion but one never can tell,
may be mysql developers will be interested in my proposal ;)

I think that taking into account (*) it will be relatively simply to
realise such an operator (even not embedded for a start). Or am I not
right?


signature.asc
Description: Digital signature

Re: feature request: statement SELECT...(INSERT|UPDATE) :)

[Jochem van Dieten]

On Jan 30, 2008 12:50 PM, Dmitry E. Oboukhov wrote:
 Is it possible to add to the syntax of the INSERT operator appoximately
 in such way:
 SELECT list INSERT [IGNORE] INTO ... - an added one.
 SELECT list UPDATE  - an added one.

 PS: I understand that adding the changes into a language is a very
 serious question that needs a great discussion but one never can tell,
 may be mysql developers will be interested in my proposal ;)

I think it would be a good idea to look at the way other databases can
do this and see if there is some common syntax that could also be
implemented by MySQL. For instance PostgreSQL implements a INSERT /
UPDATE / DELETE ... RETURNING ... statement. ISTM that is exactly the
functionality you want, but with a different syntax. If there are no
other (better) competing implementation syntaxes (I don't know any,
but maybe other list members do), I would like MySQL to adopt the
PostgreSQL example.

Jochem

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: feature request: statement SELECT...(INSERT|UPDATE) :)

[Martijn Tonies]

 On Jan 30, 2008 12:50 PM, Dmitry E. Oboukhov wrote:
  Is it possible to add to the syntax of the INSERT operator appoximately
  in such way:
  SELECT list INSERT [IGNORE] INTO ... - an added one.
  SELECT list UPDATE  - an added one.
 
  PS: I understand that adding the changes into a language is a very
  serious question that needs a great discussion but one never can tell,
  may be mysql developers will be interested in my proposal ;)
 
 I think it would be a good idea to look at the way other databases can
 do this and see if there is some common syntax that could also be
 implemented by MySQL. For instance PostgreSQL implements a INSERT /
 UPDATE / DELETE ... RETURNING ... statement. ISTM that is exactly the
 functionality you want, but with a different syntax. If there are no
 other (better) competing implementation syntaxes (I don't know any,
 but maybe other list members do), I would like MySQL to adopt the
 PostgreSQL example.

Firebird uses this syntax as well, I believe it's the SQL standard syntax
for this feature.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development 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/[EMAIL PROTECTED]

Re: How to get the table.column in mysql client tab completion? Or feature request to add this.

[Baron Schwartz]

Hi,

Daevid Vincent wrote:
[snip]

Also, it would be great if mysql client was smart enough to limit my
tab completion choices to possibilities based upon the current SQL query
I'm crafting. So If I have:
 
select t[TAB] from ResolveTable join Tickets;
 
It should only show me columns that start with a 't' that are in the

ResolveTable or Tickets tables only.


This would be nice, but then the mysql client would have to be able to parse SQL, which 
is the server's job.  How about this as a compromise: if you type ResolveTable.t[TAB], 
only show columns from that table.


Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: How to get the table.column in mysql client tab completion? Or feature request to add this.

[Daevid Vincent]

 Daevid Vincent wrote:
 [snip]
  Also, it would be great if mysql client was smart enough 
 to limit my
  tab completion choices to possibilities based upon the 
 current SQL query
  I'm crafting. So If I have:
   
  select t[TAB] from ResolveTable join Tickets;
   
  It should only show me columns that start with a 't' that are in the
  ResolveTable or Tickets tables only.
 
 How about this as a compromise: if you type ResolveTable.t[TAB], 
 only show columns from that table.

I think it already does that. But it sort of defeats the purpose, then I
have to still type out the entire table name each time.

 This would be nice, but then the mysql client would have to 
 be able to parse SQL, which is the server's job.  

Which brings up a good point. I feel that mySQL should extract the
parser portion out into a library/module so that other things (such as
the client) could use it. Top of my list would be a SQL lint checker. We
have 25+ developers, and if someone checks in a SQL file that is faulty
(maybe forgot a ' mark or a ; or something), then it breaks the build!
We have lint checkers for PHP (php -l foo.php) and a ruby one. But I
digress...

d


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How to get the table.column in mysql client tab completion? Or feature request to add this.

[Daevid Vincent]

Is there a way to get the full table.column always in mysql client when
using the auto-tab completion feature? I'm currently using 5.0.36.

The way it works now is a bit confusing. Notice I have TWO different
DateOnly columns (for example) in two different tables. [middle
column]
 
mysql select Scan
ScanData.Data   [ ScanData.DateOnly ]   ScanData.device_id
ScanData.IP_Addr  ScanData.JQID ScanData.Name
ScanData.NoteMD5  ScanData.Port ScanData.Proto
ScanData.ScanDate ScanData.ScanID   ScanData.Service
ScanData.Summary  ScanData.VulnID   ScanDate
ScanInfo[ ScanInfo.DateOnly ]   ScanInfo.DeviceID
ScanInfo.IP_Addr  ScanInfo.JobQID   ScanInfo.NewVulns
ScanInfo.ScanDate ScanInfo.ScanID   ScanInfo.ScannerID
ScanInfo.StaleDataScanInfo.Trend_Vulns  ScanInfo.Trend_Warns
ScanInfo.WarnsScannerID ScanData

But when I go to select DateOnly, I don't know which table it's in. 

mysql select Dat[TAB]
Data  DateOnly  
 
We have nearly 300 tables across 3 databases for our product. I can't
even begin to remember all the columns in each one...
 
Also, it would be great if mysql client was smart enough to limit my
tab completion choices to possibilities based upon the current SQL query
I'm crafting. So If I have:
 
select t[TAB] from ResolveTable join Tickets;
 
It should only show me columns that start with a 't' that are in the
ResolveTable or Tickets tables only.
 

ÐÆ5ÏÐ 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

feature request, optimize myisam with concurrent read only

[matt_lists]

Would like to see this, there is a TMD temp table created during an optimize

is there any reason read only access cant take place during the optimize 
process?   Update/delete/insert shouldn't, but read access should be 
allowed on myisam


any thoughts on this?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

problems/feature request ideas

[sheeri kritzer]

2 weeks ago we had a server crashing, and while I was checking it out
(before, during and after crashes) I noticed that after a crash, the
server was slow.  Threads would run for 200 seconds or more, and yet
when they finished, nothing was written to the slow query log.

Why would that be?  Slow query logging was on all the time, and other
slow queries were written after that (anything greater than 4 seconds
would be)

Also, do other folks find that a deadlock log would be useful?  InnoDB
obviously stores it in RAM because SHOW ENGINE INNODB STATUS will show
you the last deadlock information.  But I feel like a deadlock log
would be useful, to see how many deadlocks we get in a certain period
of time (but not an averageI'm sure there are peak times, etc).

Any ideas/comments?

-Sheeri

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: problems/feature request ideas

[Heikki Tuuri]

Sheeri,

- Original Message - 
From: sheeri kritzer [EMAIL PROTECTED]

Newsgroups: mailing.database.myodbc
Sent: Tuesday, March 28, 2006 9:29 PM
Subject: problems/feature request ideas



2 weeks ago we had a server crashing, and while I was checking it out
(before, during and after crashes) I noticed that after a crash, the
server was slow.  Threads would run for 200 seconds or more, and yet
when they finished, nothing was written to the slow query log.

Why would that be?  Slow query logging was on all the time, and other
slow queries were written after that (anything greater than 4 seconds
would be)

Also, do other folks find that a deadlock log would be useful?  InnoDB
obviously stores it in RAM because SHOW ENGINE INNODB STATUS will show
you the last deadlock information.  But I feel like a deadlock log
would be useful, to see how many deadlocks we get in a certain period
of time (but not an averageI'm sure there are peak times, etc).


SHOW DEADLOCKS is in our TODO. It would definitely be useful for users.


Any ideas/comments?

-Sheeri


Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables

http://www.innodb.com/order.php


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysqldump feature request

[Amit M Bhosle]

Hi:

 I am using mysql 4.1.10 for a while now, and i was wondering how
painful it would be to tweak the mysqldump to support an additional
option, --insert-ignore , which instructs it to create the dump files
with

INSERT IGNORE blah blah
instead of just 
INSERT blah blah

i have actually modified a 4.1.10 nightly build src i had downloaded a
while back, and the changes are very minimal.. few lines at the most..
pretty much the same as that required for the insert delayed option.

as of now, everytime i want to migrate to a new mysql version, i need
to get hold of the source, and make these minor changes. hence my
request for the same.

would be great if anyone could point out the possibility of this being
supported in the near future.

thx a ton..
AB



-- 
A great idea need not be complicated.
http://www.i-5.blogspot.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysqldump feature request

[Chris Elsworth]

Hello,

I have a feature request for mysqldump that would greatly ease a
current task I have; in my replicated setup my master is currently
MyISAM (as are most slaves), but I have one slave that is InnoDB for
testing.

Somehow or other, it's gotten itself out of sync, so I'm in the
position of needing to resync it.

If I were to do the usual mysqldump --add-drop-table $db | mysql
then everything will be recreated as MyISAM.

If mysqldump had a couple of extra options;
   --truncate-table
   --create-if-not-exists

Which, respectively, truncate a table before inserting any rows to it,
and only create a table if it doesn't already exist (merely by placing
the relevant already-implemented commands in 4.1 in the sql dump) I
would have a one-step process for resyncing my MyISAM master to a
slave of differing table types, by keeping the already-created slave
tables.

I'm sure these could probably come in useful for other scenarios too. 
Would this be possible/feasible/useful to anyone else?

Thanks,
-- 
Chris

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqldump feature request

[Gleb Paharenko]

Hello.



You can solve your problem using --all command line option 

(--create-options after 4.1.2) which is on by default as of 

MySQL 4.1.







Chris Elsworth [EMAIL PROTECTED] wrote:

 Hello,

 

 I have a feature request for mysqldump that would greatly ease a

 current task I have; in my replicated setup my master is currently

 MyISAM (as are most slaves), but I have one slave that is InnoDB for

 testing.

 

 Somehow or other, it's gotten itself out of sync, so I'm in the

 position of needing to resync it.

 

 If I were to do the usual mysqldump --add-drop-table $db | mysql

 then everything will be recreated as MyISAM.

 

 If mysqldump had a couple of extra options;

   --truncate-table

   --create-if-not-exists

 

 Which, respectively, truncate a table before inserting any rows to it,

 and only create a table if it doesn't already exist (merely by placing

 the relevant already-implemented commands in 4.1 in the sql dump) I

 would have a one-step process for resyncing my MyISAM master to a

 slave of differing table types, by keeping the already-created slave

 tables.

 

 I'm sure these could probably come in useful for other scenarios too. 

 Would this be possible/feasible/useful to anyone else?

 

 Thanks,



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysqlbindump feature request...

[Bruce Dembecki]

I'm not sure of the right way to submit these things, so I'll do it here...

I want to dump some data form the binlogs and process it back into the
servers. However I just want to process the data from one specific server.
In mysqlbindump I can optionally specifiy a specific database for the
information to dump... I want to specify a server id...

Eg I want to dump from binary-log.23 all the information that has a server
id value of 12.

I was thinking something like:

mysqlbinlogdump --serverid=12 binary-log.23

Best Regards, Bruce


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more accurate

[Sasha Pachev]

Daevid Vincent wrote:
Well, my hack (which is sort of like what you suggest) is to change my
primary key from just an auto_increment 'id' field to a combination of two
other fields (mac/scanner_id) that I know must be unique. Then I rely upon
the fact that mySQL will not allow a duplicate PK. (I did say it was a
hack). A co-worker assures me that a SELECT is cheap, however a version I
tried (without my hack) still allowed duplicates to slip through because I
wasn't locking the tables. I have multiple scanners hitting the same table
and locking seems to me a bad idea.
Also, I guess my TIMESTAMP brainstorm won't work b/c the resolution of that
field is 1 second and these queries happen faster than that. *Neuman!*  :-/
REPLACE INTO won't work, as I need the previous record (hence the update). I
store the first and last time I saw a node, amongst other info. REPLACE
would delete that data.
http://daevid.com
Daevid:
I believe what Steve suggests is the cleanest solution under the assumption you 
are willing to move to 4.1. If not, your college has a very good point - any 
one-row operation on MySQL is very fast - on modern hardware (AMD XP 2200+ or 
faster) you are looking at the order of magnitude of 10,000 per seconds.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more accurate

[Robert A. Rosenberg]

At 12:38 -0700 on 05/27/2004, Daevid Vincent wrote about Re: Feature 
Request: UPDATE 'error codes' or mysql_affected:

REPLACE INTO won't work, as I need the previous record (hence the update). I
store the first and last time I saw a node, amongst other info. REPLACE
would delete that data.
REPLACE would (might?) also fail if the Primary Key is some other 
table's Foreign Key (although this may be a permissible deletion 
since the record is not actually getting deleted but just the fields 
getting updated).

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more accurate

[Robert J Taylor]

Does REPLACE INTO not work in your case?
|
|
   |REPLACE| works exactly like |INSERT|, except that if an old record
   in the table has the same value as a new record for a |PRIMARY KEY|
   or a |UNIQUE| index, the old record is deleted before the new record
   is inserted. See section 14.1.4 |INSERT| Syntax
   http://dev.mysql.com/doc/mysql/en/INSERT.html.
http://dev.mysql.com/doc/mysql/en/REPLACE.html
HTH,
Robert J  Taylor
[EMAIL PROTECTED]
Daevid Vincent wrote:
I'm developing a program where I try an UPDATE ... LIMIT 1 and if
mysql_affected_rows == 0, then I know nothing was updated and so I do an
INSERT. I find this is much cleaner and the majority of the time, I'm going
to do UPDATES, so I didn't want to waste a SELECT (even though I hear
they're cheap). I'm doing these queries several times per second.
however... Of course UPDATE doesn't 'ERROR if the record doesn't exist, it
just didn't do anything (therefore that's why I use the mysql_num_rows() to
check). The problem is that if I am actually doing an UPDATE to a record
where nothing actually changed in the existing record, I still get
mysql_affected_rows() equal to 0. *grrr*.
It would be extremely useful to somehow get a result of maybe -1 if I tried
to update a record that didn't exist, versus a result of -2 if I tried to
update a record that did exist, but mySQL didn't change anything.
I don't know exactly what I'm asking for other than a way to know the
difference...
At the very least, it seems to me that if I update a record that exists
already (even if no data changed), I should still get mysql_affected_rows()
 

0 (since in theory I matched something, even if mySQL behind the scenes
   

didn't change the data).  

Out of curiosity, if I have a TIMESTAMP column, would that solve my problem,
since mySQL should be forced to update that TIMESTAMP right?? [btw, I know I
could try this idea, but I'm home and my code is at work right now and I
just had the idea! ;-]
http://daevid.com
 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more accurate

[Steve Meyers]

http://dev.mysql.com/doc/mysql/en/INSERT.html
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 
4.1.0), and a row is inserted that would cause a duplicate value
in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is
performed.
Daevid Vincent wrote:
I'm developing a program where I try an UPDATE ... LIMIT 1 and if
mysql_affected_rows == 0, then I know nothing was updated and so I do an
INSERT. I find this is much cleaner and the majority of the time, I'm going
to do UPDATES, so I didn't want to waste a SELECT (even though I hear
they're cheap). I'm doing these queries several times per second.
however... Of course UPDATE doesn't 'ERROR if the record doesn't exist, it
just didn't do anything (therefore that's why I use the mysql_num_rows() to
check). The problem is that if I am actually doing an UPDATE to a record
where nothing actually changed in the existing record, I still get
mysql_affected_rows() equal to 0. *grrr*.
It would be extremely useful to somehow get a result of maybe -1 if I tried
to update a record that didn't exist, versus a result of -2 if I tried to
update a record that did exist, but mySQL didn't change anything.
I don't know exactly what I'm asking for other than a way to know the
difference...
At the very least, it seems to me that if I update a record that exists
already (even if no data changed), I should still get mysql_affected_rows()
0 (since in theory I matched something, even if mySQL behind the scenes
didn't change the data).  

Out of curiosity, if I have a TIMESTAMP column, would that solve my problem,
since mySQL should be forced to update that TIMESTAMP right?? [btw, I know I
could try this idea, but I'm home and my code is at work right now and I
just had the idea! ;-]
http://daevid.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more accurate

[Daevid Vincent]

Well, my hack (which is sort of like what you suggest) is to change my
primary key from just an auto_increment 'id' field to a combination of two
other fields (mac/scanner_id) that I know must be unique. Then I rely upon
the fact that mySQL will not allow a duplicate PK. (I did say it was a
hack). A co-worker assures me that a SELECT is cheap, however a version I
tried (without my hack) still allowed duplicates to slip through because I
wasn't locking the tables. I have multiple scanners hitting the same table
and locking seems to me a bad idea.

Also, I guess my TIMESTAMP brainstorm won't work b/c the resolution of that
field is 1 second and these queries happen faster than that. *Neuman!*  :-/

REPLACE INTO won't work, as I need the previous record (hence the update). I
store the first and last time I saw a node, amongst other info. REPLACE
would delete that data.

http://daevid.com

 -Original Message-
 From: Steve Meyers [mailto:[EMAIL PROTECTED] 
 Sent: Thursday, May 27, 2004 7:42 AM
 To: Daevid Vincent
 Cc: [EMAIL PROTECTED]
 Subject: Re: Feature Request: UPDATE 'error codes' or 
 mysql_affected_rows() to be more accurate
 
 http://dev.mysql.com/doc/mysql/en/INSERT.html
 
   INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
   [INTO] tbl_name [(col_name,...)]
   VALUES ({expr | DEFAULT},...),(...),...
   [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
   
   If you specify the ON DUPLICATE KEY UPDATE clause (new 
 in MySQL  
   4.1.0), and a row is inserted that would cause a duplicate value
   in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is
   performed.
 
 
 Daevid Vincent wrote:
  I'm developing a program where I try an UPDATE ... LIMIT 1 and if
  mysql_affected_rows == 0, then I know nothing was updated 
 and so I do an
  INSERT. I find this is much cleaner and the majority of the 
 time, I'm going
  to do UPDATES, so I didn't want to waste a SELECT (even 
 though I hear
  they're cheap). I'm doing these queries several times per second.
  
  however... Of course UPDATE doesn't 'ERROR if the record 
 doesn't exist, it
  just didn't do anything (therefore that's why I use the 
 mysql_num_rows() to
  check). The problem is that if I am actually doing an 
 UPDATE to a record
  where nothing actually changed in the existing record, I still get
  mysql_affected_rows() equal to 0. *grrr*.
  
  It would be extremely useful to somehow get a result of 
 maybe -1 if I tried
  to update a record that didn't exist, versus a result of -2 
 if I tried to
  update a record that did exist, but mySQL didn't change anything.
  
  I don't know exactly what I'm asking for other than a way 
 to know the
  difference...
  
  At the very least, it seems to me that if I update a record 
 that exists
  already (even if no data changed), I should still get 
 mysql_affected_rows()
  
 0 (since in theory I matched something, even if mySQL 
 behind the scenes
  
  didn't change the data).  
  
  Out of curiosity, if I have a TIMESTAMP column, would that 
 solve my problem,
  since mySQL should be forced to update that TIMESTAMP 
 right?? [btw, I know I
  could try this idea, but I'm home and my code is at work 
 right now and I
  just had the idea! ;-]
  
  http://daevid.com
  
  
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more accurate

[Daevid Vincent]

I'm developing a program where I try an UPDATE ... LIMIT 1 and if
mysql_affected_rows == 0, then I know nothing was updated and so I do an
INSERT. I find this is much cleaner and the majority of the time, I'm going
to do UPDATES, so I didn't want to waste a SELECT (even though I hear
they're cheap). I'm doing these queries several times per second.

however... Of course UPDATE doesn't 'ERROR if the record doesn't exist, it
just didn't do anything (therefore that's why I use the mysql_num_rows() to
check). The problem is that if I am actually doing an UPDATE to a record
where nothing actually changed in the existing record, I still get
mysql_affected_rows() equal to 0. *grrr*.

It would be extremely useful to somehow get a result of maybe -1 if I tried
to update a record that didn't exist, versus a result of -2 if I tried to
update a record that did exist, but mySQL didn't change anything.

I don't know exactly what I'm asking for other than a way to know the
difference...

At the very least, it seems to me that if I update a record that exists
already (even if no data changed), I should still get mysql_affected_rows()
 0 (since in theory I matched something, even if mySQL behind the scenes
didn't change the data).  

Out of curiosity, if I have a TIMESTAMP column, would that solve my problem,
since mySQL should be forced to update that TIMESTAMP right?? [btw, I know I
could try this idea, but I'm home and my code is at work right now and I
just had the idea! ;-]

http://daevid.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Feature request related to COMPRESS and UNCOMPRESS functions

[Steven Roussey]

 Here is the background:  Anyone that is running a huge system like MARC
 that has millions of uncompressed blob records in huge tables, needs to be
 able to migrate, in real-time and without down-time, to compressed blobs.
 Therefore, we need a way to know if a given field is compressed or not.

I hear you on that! We did the compression on the application end. When we
started compressing all of the blobs in the table were uncompressed except
newly added ones. We took advantage of the fact that zlib fails on
decompression. So we wrote a function my_decompress() that takes the blob
and decompresses it and if it fails just returns the original (assumed to be
already decompressed). Works great and decompression gets divided among the
webservers which scales better than having MySQL do it. 

However, you should develop a way to take tables offline. Lack of proper
table maintenance can slow things down by a factor of 10 or more (and one of
the reasons we can not use InnodDB).

-steve--



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Feature request related to COMPRESS and UNCOMPRESS functions

[Sergei Golubchik]

Hi!

On Feb 27, Lester Hightower wrote:
 To whom it may concern at Mysql AB:
 
 I see from the Mysql 4.1.1 CHANGELOG that new COMPRESS(), UNCOMPRESS(),
 and UNCOMPRESSED_LENGTH() functions were added.  That is great news, and
 something I have been very interested in for a long time, as evidenced by
 this mysql mailing list thread, dating back to 12/18/2001:
 
   http://marc.10east.com/?t=10086980305r=1w=2
 
 The MARC system (marc.10east.com) was one of the primary reasons for me
 requesting that this feature be added to Mysql.  There is one short-coming
 in the new COMPRESS()/UNCOMPRESS() functionality that I would like to
 point out, and request that you address.
 
 Here is the background:  Anyone that is running a huge system like MARC
 that has millions of uncompressed blob records in huge tables, needs to be
 able to migrate, in real-time and without down-time, to compressed blobs.
 Therefore, we need a way to know if a given field is compressed or not.
 
 Running alter table on our tables, to add an am_i_compressed boolean, not
 only takes an excruciating amount of time and resources, but adds bits to
 each record that, from a disk-space perspective, we cannot afford.
 
 Instead, I would like to be able to run a query like:
 
   select IFCOMPRESSED(msg_body, UNCOMPRESS(msg_body), msg_body)
   from msg_bodies_200402
   where clause
 
 Note that the IFCOMPRESSED() function is the key, and what I am requesting
 be added to future versions of Mysql.  That function has to be possible.
 
 Hopefully you are storing a header with your compressed data, and if so,
 then the IFCOMPRESSED() is trivial to implement.  If you are not storing a
 header with your compressed data, then this might be more complicated.
 Adding a header might be a possibility -- which is why I am trying to
 point this out _EARLY_ in the process before lots of people start using
 COMPRESS()/UNCOMPRESS(), or maybe zlib, lzo, or whatever library you are
 using can let you know if the data is compressed -- maybe they store a
 small header themselves.
 
 Anyway, that is the issue that I want to point out and ask for assistance
 on.  Thank you very much for listening to the user community and adding
 COMPRESS()/UNCOMPRESS(), and please seriously consider this request.

There is a header - but it only stores the length of the uncompressed
data. And technically, no header can guarantee that the data are
compressed. The check you can use is something like

 1. test that UNCOMPRESSED_LENGTH returns something realistic, as you
know how large a blob in your table can be - it should catch most of
the uncompressed rows.
 2. try to uncompress the rest - zlib puts crc in the compressed stream,
and UNCOMPRESS returns NULL if uncompression fails.

so the query could be something like

 select IF(UNCOMPRESSED_LENGTH(msg_body)  102400, msg_body,
IFNULL(UNCOMPRESS(msg_body), msg_body)) ...

Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik [EMAIL PROTECTED]
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   ___/  www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Feature request related to COMPRESS and UNCOMPRESS functions

[Lester Hightower]

To whom it may concern at Mysql AB:

I see from the Mysql 4.1.1 CHANGELOG that new COMPRESS(), UNCOMPRESS(),
and UNCOMPRESSED_LENGTH() functions were added.  That is great news, and
something I have been very interested in for a long time, as evidenced by
this mysql mailing list thread, dating back to 12/18/2001:

http://marc.10east.com/?t=10086980305r=1w=2

The MARC system (marc.10east.com) was one of the primary reasons for me
requesting that this feature be added to Mysql.  There is one short-coming
in the new COMPRESS()/UNCOMPRESS() functionality that I would like to
point out, and request that you address.

Here is the background:  Anyone that is running a huge system like MARC
that has millions of uncompressed blob records in huge tables, needs to be
able to migrate, in real-time and without down-time, to compressed blobs.
Therefore, we need a way to know if a given field is compressed or not.

Running alter table on our tables, to add an am_i_compressed boolean, not
only takes an excruciating amount of time and resources, but adds bits to
each record that, from a disk-space perspective, we cannot afford.

Instead, I would like to be able to run a query like:

  select IFCOMPRESSED(msg_body, UNCOMPRESS(msg_body), msg_body)
from msg_bodies_200402
where clause

Note that the IFCOMPRESSED() function is the key, and what I am requesting
be added to future versions of Mysql.  That function has to be possible.

Hopefully you are storing a header with your compressed data, and if so,
then the IFCOMPRESSED() is trivial to implement.  If you are not storing a
header with your compressed data, then this might be more complicated.
Adding a header might be a possibility -- which is why I am trying to
point this out _EARLY_ in the process before lots of people start using
COMPRESS()/UNCOMPRESS(), or maybe zlib, lzo, or whatever library you are
using can let you know if the data is compressed -- maybe they store a
small header themselves.

Anyway, that is the issue that I want to point out and ask for assistance
on.  Thank you very much for listening to the user community and adding
COMPRESS()/UNCOMPRESS(), and please seriously consider this request.

Sincerely,

--
Lester H. Hightower [EMAIL PROTECTED]
Chief Technology Officer, 10 East Corp.


p.s. Could someone at Mysql AB update the URL at the bottom of this page,
http://lists.mysql.com/, to point to http://marc.10east.com/ instead of
http://marc.theaimsgroup.com/?  That is our old company name, and we are
always trying to reduce usage on that domain name.  Thanks.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Feature request Relating To max_questions

[Gary Huntress]

Hi,

I rely on max_questions to balance the load on my servers.   I'd like to
tune them more effectively than just a ballpark guess.

During the course of an hour (the question count resets each hour) is it
possible to retrieve the question count for a particular user?   Obviously
it must be stored somehwere in order to implement this feature.

If it is not currently possible, I would find it very useful.
Something like

SELECT current_question_count(theusername)




Regards,
Gary SuperID Huntress
===
FreeSQL.org offering free database hosting to developers
Visit http://www.freesql.org



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

innodb feature request

[Joe Shear]

I'd like to be able to look at show innodb status, notice that a query
is waiting on a lock to be released, and then determine which tx is
holding that lock. 

I know you can use the innodb_lock_monitor table, but this would make
things a lot faster especially when there are a lot of active
transactions.

-- 
Joe Shear [EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Correct way of doing an isnumeric() test? ( Feature Request ? )

[Paul DuBois]

At 12:20 +1000 7/23/03, Daniel Kasak wrote:
Paul DuBois wrote:

At 10:46 +1000 7/23/03, Daniel Kasak wrote:

Hi all.

I need to test whether the first bit of a field is numeric.
For example, the field might contain:
154 boxes


Define this more precisely.  Is an acceptable match one or more
digits followed by a space?  If so, field REGEXP '^[0-9]+ ' should
work.
Yeah that will do it nicely.
It would still be good to have an isnumeric() function aliased to 
something like this, but anyway it solves my problem.
Thanks!
In your original message, you also said that such a function would add
to compatibility with other DB servers.
I'm afraid I don't understand this.  What you appear to want is not a
general function that characterizes values as numeric or non-numeric,
but a special-purpose function that looks specifically for values with
a numeric prefix followed by a space.  isnumeric() would be the wrong
name for this, and I don't really see how this aids compatibility.  Do
other DB servers really have such a thing?
It seems to me that REGEXP is still a better solution. It can be adapted
to a broad class of patterns, whereas the proposed isnumeric() handles
a limited special case.  No?
--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Correct way of doing an isnumeric() test? ( Feature Request ? )

[Daniel Kasak]

Hi all.

I need to test whether the first bit of a field is numeric.
For example, the field might contain:
154 boxes

I'm currently testing it by doing:

if(abs(left(field,locate(' ', field))0),'Numeric bit at front', 'Not 
Numeric bit at front')

It seems to work.
Well it kinda works. If 'field' has '3a5 boxes' in it, the above 
function would return true, as the abs('3a5') would equate to 3. But 
it's good enough for now.

Is there a simpler / better way of doing it? I can't find any reference 
to a function like isnumeric().
Feature request? It would be handy. It would also add to compatibility 
with other DB servers...

--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Correct way of doing an isnumeric() test? ( Feature Request ? )

[Daniel Kasak]

Paul DuBois wrote:

At 10:46 +1000 7/23/03, Daniel Kasak wrote:

Hi all.

I need to test whether the first bit of a field is numeric.
For example, the field might contain:
154 boxes


Define this more precisely.  Is an acceptable match one or more
digits followed by a space?  If so, field REGEXP '^[0-9]+ ' should
work.
Yeah that will do it nicely.
It would still be good to have an isnumeric() function aliased to 
something like this, but anyway it solves my problem.
Thanks!

--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Correct way of doing an isnumeric() test? ( Feature Request ? )

[Paul DuBois]

At 10:46 +1000 7/23/03, Daniel Kasak wrote:
Hi all.

I need to test whether the first bit of a field is numeric.
For example, the field might contain:
154 boxes
Define this more precisely.  Is an acceptable match one or more
digits followed by a space?  If so, field REGEXP '^[0-9]+ ' should
work.
I'm currently testing it by doing:

if(abs(left(field,locate(' ', field))0),'Numeric bit at front', 
'Not Numeric bit at front')

It seems to work.
Well it kinda works. If 'field' has '3a5 boxes' in it, the above 
function would return true, as the abs('3a5') would equate to 3. But 
it's good enough for now.

Is there a simpler / better way of doing it? I can't find any 
reference to a function like isnumeric().
Feature request? It would be handy. It would also add to 
compatibility with other DB servers...

--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Feature request: using CURRENT_DATE as DEFAULT value

[gerald_clark]

Perhaps TIMESTAMP will provide what you need.

Daevid Vincent wrote:

http://www.mysql.com/doc/en/CREATE_TABLE.html states Default values must be
constants. This means, for example, that you cannot set the default for a
date column to be the value of a function such as NOW() or CURRENT_DATE.
Is this ever going to be fixed? I often find myself using this when
creating new records, and it'd be just one less thing to worry about putting
in the INSERT statement?
 



-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Feature request: using CURRENT_DATE as DEFAULT value

[Daevid Vincent]

http://www.mysql.com/doc/en/CREATE_TABLE.html states Default values must be
constants. This means, for example, that you cannot set the default for a
date column to be the value of a function such as NOW() or CURRENT_DATE.

Is this ever going to be fixed? I often find myself using this when
creating new records, and it'd be just one less thing to worry about putting
in the INSERT statement?


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Feature request: using CURRENT_DATE as DEFAULT value

[Paul DuBois]

At 17:56 -0800 3/5/03, Daevid Vincent wrote:
http://www.mysql.com/doc/en/CREATE_TABLE.html states Default values must be
constants. This means, for example, that you cannot set the default for a
date column to be the value of a function such as NOW() or CURRENT_DATE.
Is this ever going to be fixed?
Yes.  When?  Dunno.

 I often find myself using this when
creating new records, and it'd be just one less thing to worry about putting
in the INSERT statement?


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Feature Request: Return AUTO_INCREMENT on INSERT

[Shane Allen]

How about this:

mysql CREATE TABLE tablename (value1 int(10) AUTO_INCREMENT, value2 timestamp(14), 
PRIMARY KEY (value1));
Query OK, 0 rows affected (0.00 sec)

mysql INSERT SQL_RETURN_INSERT_ID INTO tablename (value1, value2) VALUES (0, NOW());
+-+
| INSERT_ID() |
+-+
|   1 |
+-+
1 row in set (0.01 sec)

mysql INSERT SQL_RETURN_INSERT_ID INTO tablename (value1, value2) VALUES (0, NOW()), 
(0, NOW());
+-+
| INSERT_ID() |
+-+
|   2 |
|   3 |
+-+
2 rows in set (0.01 sec)

I'd just *love* it if inserting and retrieving the auto_increment value was atomic.
Thoughts?

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Feature request: array-variables

[Christian Kohlschtter]

Hello!

Is it feasible to enable storing multiple values into one variable?
(that would then be an array)

For example:
@a := (SELECT idref FROM tableB);
(... some commands ...)
SELECT id FROM tableA WHERE id IN @a;

Or will mysql support something like cursors?
-- 
Christian Kohlschütter
[EMAIL PROTECTED]

http://www.newsclub.de - Der Meta-Nachrichten-Dienst


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: Mysql versions, disks, speed, FTS, and EXPLAIN feature request (SELECT [CALC_RESOURCES])

[Victoria Reznichenko]

On Saturday 21 December 2002 00:06, Steven Roussey wrote:

 I wanted to thank the MySQL team for making such a great product! We
 moved from 3.23 to 4.0.x a couple of months ago and everything works
 great. Just upgraded to 4.0.6 and glad to see it work out of the box
 without a rev 4.0.6a. Those glib issues were such a pain!

 4.0.5a and 4.0.6 have been as solid as any 3.23.x in our experience.

 Just a note to users of Full Text Search: put it on some other machine.
 FTS was basically clearing the MySQL and Linux caches with all its read
 data. Putting it on a separate machine let all the other stuff run just
 fine.

 Short example:
   Main server: 3000 q/s   Disk read:  540 KB/sLoad:  1
   FTS server: 2 q/s   Disk read: 7600 KB/sLoad: 12

 Our queries are not representative of anyone else's! YMMV! Just a note
 on how FTS can really read a lot of data and how moving it can really
 clear things up. Which brings me to:

 EXPLAIN feature request: to have EXPLAIN RESOURCES SELECT or something
 similar to be able to show resource usage instead of query/index plan.
 Resources like CPU, disk read, and disk write. Likely it should not
 actually be EXPLAIN, since it would do the operation (where explain does
 not). So maybe SELECT [CALC_RESOURCES] ... followed by a SHOW
 RESOURCES_USAGE or something.

I've consulted about your question with Sinisa Milivojevic. Here is what he 
said:

EXPLAIN can not do that, because explain does not know: 

* how much code will be traversed 
* how fragmented are tables
* how many functions will have to be evaluated
* how loaded is a system (is relevant on some OS's)
* how big temp tables or temp files will be
* how well ANALYZE'd are indices
* etc



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Mysql versions, disks, speed, FTS, and EXPLAIN feature request (SELECT [CALC_RESOURCES])

[Steven Roussey]

Hi all!

I wanted to thank the MySQL team for making such a great product! We
moved from 3.23 to 4.0.x a couple of months ago and everything works
great. Just upgraded to 4.0.6 and glad to see it work out of the box
without a rev 4.0.6a. Those glib issues were such a pain!

4.0.5a and 4.0.6 have been as solid as any 3.23.x in our experience.

Just a note to users of Full Text Search: put it on some other machine.
FTS was basically clearing the MySQL and Linux caches with all its read
data. Putting it on a separate machine let all the other stuff run just
fine.

Short example:
Main server: 3000 q/s   Disk read:  540 KB/sLoad:  1
FTS server: 2 q/s   Disk read: 7600 KB/sLoad: 12

Our queries are not representative of anyone else's! YMMV! Just a note
on how FTS can really read a lot of data and how moving it can really
clear things up. Which brings me to:

EXPLAIN feature request: to have EXPLAIN RESOURCES SELECT or something
similar to be able to show resource usage instead of query/index plan.
Resources like CPU, disk read, and disk write. Likely it should not
actually be EXPLAIN, since it would do the operation (where explain does
not). So maybe SELECT [CALC_RESOURCES] ... followed by a SHOW
RESOURCES_USAGE or something.

-steve-



Main server:
Load is 1.0

Server version  4.0.6-gamma-standard-log
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 14 hours 18 min 27 sec

Threads: 228  Questions: 152864656  Slow queries: 2877  Opens: 89075
Flush tables: 1  Open tables: 2825  Queries per second avg: 2967.842

# iostat -k 10

avg-cpu:  %user   %nice%sys   %idle
  23.050.00   19.30   57.65

Device:tpskB_read/skB_wrtn/skB_readkB_wrtn
dev3-00.60 0.0011.20  0112
dev8-0   31.6097.6078.80976788
dev8-1   27.5080.8073.60808736
dev8-2   30.2092.0068.80920688
dev8-3   29.0091.2070.00912700
dev8-4   26.9075.6069.60756696
dev8-5   30.7098.4074.40984744


FTS server:
Load is 12

Server version  4.0.6-gamma-standard-log
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 15 hours 50 min 12 sec

Threads: 36  Questions: 132274  Slow queries: 2527  Opens: 332  Flush
tables: 1  Open tables: 64  Queries per second avg: 2.320
# iostat -k 10
Device:tpskB_read/skB_wrtn/skB_readkB_wrtn
dev3-0  268.40  7623.20 3.60  76232 36



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: SecurityFocus HOME Mailing List: BugTraq; Possibly Feature Request

[Benjamin Pflugmann]

Hello.

(This is more an additional answer to the original mail than to the
one I replied to).

On Sat 2002-08-24 at 19:21:56 -0400, [EMAIL PROTECTED] wrote:
 On Sat, 2002-08-24 at 18:38, Van wrote:

  Just thought I'd pass it along, since I haven't seen Monty and
  crew address it.

I think that it is a non-issue and that this already has been convered
in the BugTraq thread.

[...]

  This BDA individual appears to have a point on a configuration
  option that allows an exclude to prevent localhost from getting
  denied due to the eleven bad connection (ex. Bad Handshake)
  problem.
 
 I don't agree that he has a valid point.  More specifically, It's
 not a bug, it's a feature.

Seconded.

  Since I'm not familiar with the error-handling code that deals
  with this, I'd offer a suggestion that rather than just ignoring
  excessive bad connections from localhost an admin notification
  (via e-mail or console message, perhaps) be sent when localhost is
  exhibiting this behavior so someone can intervene before it
  becomes a problem and DoS-es itself.

This is the task of an monitoring system, not of MySQL. It is standard
procedure to have a monitoring system on a production system (at
least, if outages cause some form of damages). I have mon running
and should an important IP get blocked (for whatever reason), I will
get a mail two minutes later.

The advantage of this method is that you get notified for any reason
the access does not work, not only blocked IPs. Additionally, a
monitoring system if flexible, in when, how often, how and whom to
notify. You do not want to build all this into MySQL, do you? (And
without, the feature could trigger thausands of mails in an DDoS
attack). As I said, this is the task of a monitoring system.

 If you can code something like this (that can be turned off by default
 or by config, because I _want_ the behavior you want to remove) that is
 portable (i.e. works on Windows), then feel free.

As should be clear from above, I am rather against it, even if it
could be done easily.

If at all, I would suggest to implement a trigger system, into which
some other program can hook in, if it wants to know about special
events. But then, the problem can be handled by existing means, so why
bother?

Regards,

Benjamin.

-- 
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: SecurityFocus HOME Mailing List: BugTraq; Possibly Feature Request

[Van]

Benjamin Pflugmann wrote:

 On Sat 2002-08-24 at 19:21:56 -0400, [EMAIL PROTECTED] wrote:
  On Sat, 2002-08-24 at 18:38, Van wrote:
 
   Just thought I'd pass it along, since I haven't seen Monty and
   crew address it.
 
 I think that it is a non-issue and that this already has been convered
 in the BugTraq thread.
 

But, not covered in a post from a MySQL AB representive.  The BugTraq thread
usually doesn't end unless / until such an event occurs...

   Since I'm not familiar with the error-handling code that deals
   with this, I'd offer a suggestion that rather than just ignoring
   excessive bad connections from localhost an admin notification
   (via e-mail or console message, perhaps) be sent when localhost is
   exhibiting this behavior so someone can intervene before it
   becomes a problem and DoS-es itself.
 
 This is the task of an monitoring system, not of MySQL. It is standard
 procedure to have a monitoring system on a production system (at
 least, if outages cause some form of damages). I have mon running
 and should an important IP get blocked (for whatever reason), I will
 get a mail two minutes later.
 
 The advantage of this method is that you get notified for any reason
 the access does not work, not only blocked IPs. Additionally, a
 monitoring system if flexible, in when, how often, how and whom to
 notify. You do not want to build all this into MySQL, do you? (And
 without, the feature could trigger thausands of mails in an DDoS
 attack). As I said, this is the task of a monitoring system.
 

Perhaps in your world monitoring systems lie in constant function all over the
place, and I assure you they do in my own without the nuisance of thousands of
e-mails in my inbox (ever), but the vast majority of other peoples' systems lie
exposed on the Internet at large, absolutely unsupervised.  My clients' systems,
and probably your client systems probably have no entries in wtmp except your
own.  And, since MySQL is a viable deployment solution on Windows, my guess is
you don't even have those entries in their systems' logging facilities since
Windoze logging is not a priority in the OSes design.

I don't use TCP/IP for connections in most of my MySQL-based applications and
never from localhost to localhost, but I've been doing this stuff for quite a
few years.  Not everyone has the luxury of experience (the real kind; not XP),
and best I can judge not many people actually do stuff in *n*x either, even at
this point in time,  nor on this list.  So, what that gives you is many non-*n*x
users using non-*n*x machines to connect to their MySQL servers to do stuff
(sometimes ODBC stuff), and those servers mostly run some kind of *n*x (pro'ly
Linux).  This all falls into the realm of people running complex queries over
TCP/IP.  This is the kind of thing you'd probably encounter tons of bad
connection type stuff if there were connectivity issues, like those you'd find
on a DSL connection behind a Linux firewall running NAT, or masquerading, which
is something most, (if not all) newbie Linuxers do...

If your queries run on localhost via the local web-server and you've educated
your users to run queries using UNIX sockets, the likelihood you'll ever get a
bad connection from localhost to your server is about zero (0).  If I get such
an error, I'd want my inbox to fill up and I'll definitely notice.  If someone
who doesn't know the first thing about the bad connection problem starts
getting messages about them, perhaps an education will commence.

No one uses a monitoring system out there, except for you apparently, and
thanks for doing it; and, clearly myself as I've admitted to it.  

Some people out there (and, they probably have good reasons for it) isolate
their web machines from their MySQL machines, and this would mandate a TCP/IP
connection, which is fine, but I'm sure that's a more rare configuration than
most people are deploying.

  If you can code something like this (that can be turned off by default
  or by config, because I _want_ the behavior you want to remove) that is
  portable (i.e. works on Windows), then feel free.
 
 As should be clear from above, I am rather against it, even if it
 could be done easily.
 
 If at all, I would suggest to implement a trigger system, into which
 some other program can hook in, if it wants to know about special
 events. But then, the problem can be handled by existing means, so why
 bother?

Windoze can have services disabled by default, but many times we encounter
problems due to people running them without their knowledge or need.  People
have to take responsibility for their systems, even if it means a couple
inconvenient e-Mails.  Otherwise, the Gov't will take over what is currently our
responsibility.  I'm certain I'd be against that.  

 
 Regards,
 
 Benjamin.

I'm glad you monitor your systems and apparently take pride in those
responsiblities.  Please allow for the possiblity that you are the exception
rather than the rule and 

Feature Request: --log-update to add 'drop table' after 'create temporarytable'

[Michael Widenius]

Hi!

 Daniel == Daniel Kasak [EMAIL PROTECTED] writes:

cut

Daniel Would it be possible to add a 'drop table' command when a connection 
Daniel which has made a temporary table has been dropped, to make the logs 
Daniel match up with what actually happens? Thanks!

The later MySQL 3.23 and 4.0.3 version already logs drop table into
the MySQL binary log.


Regards,
Monty

-- 
For technical support contracts, goto https://order.mysql.com/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED]
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, CTO
/_/  /_/\_, /___/\___\_\___/   Helsinki, Finland
   ___/   www.mysql.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Feature Request: --log-update to add 'drop table' after 'create temporarytable'

[Daniel Kasak]

Hi all.
Hello spam filter! sql. query.

I had to restore from a backup and run through part of the day's 
transactions from the --log-update option ... I had accidentally deleted 
2500 records, and wanted to replay the logs minus that one delete 
command :-)
One problem which I encountered was that 'create temporary table' 
commands were in the log, and when I ran them through mysql, I hit a 
case where 2 different people had created a temporary table of the same 
name on the same day. The problem is since that since the logs were 
being re-run (mysql databasename  hostname.xxx -ppassword) with the 
same connection, the temporary table wasn't being dropped and the second 
create temporary table command was giving a 'table exists' type error.
I got around the problem by 'grep'ing out the create temporary table 
commands from the log and re-running (they are only used in our case to 
provide info to clients, not for using in further update / insert commands).
I suppose I should add delete table commands when I do a create 
temporary table command, but since the instructions said that the 
temporary table would be deleted when the connection was dropped, I left 
this out.
Would it be possible to add a 'drop table' command when a connection 
which has made a temporary table has been dropped, to make the logs 
match up with what actually happens? Thanks!

Dan

-- 
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Feature request; Field for comments

[LauxDieter]

Hi mysql users, mysql developers!

I have just started using mysql for a database project and I wonder that one
function is missing. I am using myphpadmin as utility. It is possible to
write a comment to each table, but it is not possible to write a comment to each
field. Why not? I think this would be a usefull feature which should  be
implemented, especially if you work with several members.

Example (these are all fields in a table):

nick [... mysql stuff] - here the nickname of each user is stored.
email [...] - the email of the user
emailcmp [...] - the email of the company the user is working for

as you see, the feature is beginning to get usefull when the fields get more
complicated and unique naming is getting a problem.

favouritekey - a binary code to store the users interests as documented
  in keyrules.rtf


I think this would be a great enhanchement, especially for bigger groups of
developers. What do you think?

Thanks in advance

Dieter

-- 
GMX - Die Kommunikationsplattform im Internet.
http://www.gmx.net


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Feature request; Field for comments

[Robin Johnson]

On Sun, 11 Aug 2002 [EMAIL PROTECTED] wrote:
 I have just started using mysql for a database project and I wonder that one
 function is missing. I am using myphpadmin as utility. It is possible to
 write a comment to each table, but it is not possible to write a comment to each
 field. Why not? I think this would be a usefull feature which should  be
 implemented, especially if you work with several members.

 I think this would be a great enhanchement, especially for bigger groups of
 developers. What do you think?
Hi,

Actually, do yourself a big favour and upgrade to the latest release of
phpMyAdmin that I released earlier this evening. We support comment fields
for columns ourselves now after we saw a feature request for it.

-- 
Robin Hugh Johnson
E-Mail : [EMAIL PROTECTED]
Home Page  : http://www.orbis-terrarum.net/?l=people.robbat2
ICQ#   : 30269588 or 41961639


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Feature request 4.0.x MAX_SLOW_QUERY_PER_HOURS

[Andrew Sitnikov]

Hello,

  I think that MAX_SLOW_QUERY_PER_HOURS is useful options for

  GRANT ... WITH MAX_QUERIES_PER_HOUR = N1
 MAX_UPDATES_PER_HOUR = N2
 MAX_CONNECTIONS_PER_HOUR = N3;
  
  statment.


  Thank.

Best regards,
 Andrew Sitnikov 
 e-mail : [EMAIL PROTECTED]
 GSM: (+372) 56491109


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Feature request 4.0.x MAX_SLOW_QUERY_PER_HOURS

[Andrew Sitnikov]

Hello,

  I think that MAX_SLOW_QUERY_PER_HOURS is useful options for

  GRANT ... WITH MAX_QUERIES_PER_HOUR = N1
 MAX_UPDATES_PER_HOUR = N2
 MAX_CONNECTIONS_PER_HOUR = N3;
  
  statment.


  Thank.

Best regards,
 Andrew Sitnikov 
 e-mail : [EMAIL PROTECTED]
 GSM: (+372) 56491109


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

EXPLAIN feature request

[Ken Menzel]

Hi Guys,
   I would like to request an additional feature with EXPLAIN.
EXPLAIN does not run the query guesses on how the query would be run.
This is not always accurate. The slow-log however logs the actual
information the query was run with (like explain only the truth!).
Could we have an option with EXPLAIN to actually run the query and
provide the true results?
Maybe REXPLAIN,  or EXPLAIN RUN query?

Thanks Ken
-
Ken Menzel  ICQ# 9325188
www.icarz.com  [EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: EXPLAIN feature request

[Sinisa Milivojevic]

Ken Menzel writes:
 Hi Guys,
I would like to request an additional feature with EXPLAIN.
 EXPLAIN does not run the query guesses on how the query would be run.
 This is not always accurate. The slow-log however logs the actual
 information the query was run with (like explain only the truth!).
 Could we have an option with EXPLAIN to actually run the query and
 provide the true results?
 Maybe REXPLAIN,  or EXPLAIN RUN query?
 
 Thanks Ken
 -
 Ken Menzel  ICQ# 9325188
 www.icarz.com  [EMAIL PROTECTED]
 

Hi!

This is of course doable, but EXPLAIN will be more accurate if all
tables involved were ANALYZE'd ...

-- 
Regards,
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED]
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Fulltime Developer
/_/  /_/\_, /___/\___\_\___/   Larnaca, Cyprus
   ___/   www.mysql.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mysqldump feature request

[Franklin Schmidt]

It would be nice to have an option for mysqldump to put the building of
indexes after the insert statements to speed up loading.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

feature request: privileged connection quotas

[Martin Waite]

Hi,

I'd like to be able to reserve some connections to the MySQL 
server for emergencies - eg. someone issued a slow query and locked out 
several hundred updates from several hundred other connections.

Currently in this sort of situation, it is possible to completely
run out of connections and then the only solution is to restart mysqld
or just wait for the slow query to end.

Would it be possible to add a feature to MySQL so that a configurable
number of connections are reserved for a configurable list of users ?
Then, an administrator would always be able to connect and clear any
blockages.

regards,
Martin


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Feature request

[Colin Faber]

Hi, 

I've got a feature request which I don't think should be all that hard
to implement (if not already)

With in the mysql fulltext search system if your search words exist in
more than 50% of the records; it is ignored.

How about having mysql return either an error or set something that can
be retrieved which will notify if this event happens.

A feature like this would allow people that use the FTS as the bases of
a search engine to return nifty little `google like' results.  The
following words were not included in the search because of their
commonality with in our database .. 'words'

etc.


-- 
Colin Faber
(303) 859-1491
fpsn.net, Inc.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Feature request

[Sergei Golubchik]

Hi!

On Dec 12, Colin Faber wrote:
 Hi, 
 
 I've got a feature request which I don't think should be all that hard
 to implement (if not already)
 
 With in the mysql fulltext search system if your search words exist in
 more than 50% of the records; it is ignored.
 
 How about having mysql return either an error or set something that can
 be retrieved which will notify if this event happens.
 
 A feature like this would allow people that use the FTS as the bases of
 a search engine to return nifty little `google like' results.  The
 following words were not included in the search because of their
 commonality with in our database .. 'words'
 
 etc.
 

Colin, unfortunately this feature would be difficult to add.
The word that exists more than in 50% rows is not, in fact, ignored -
it does not recieve any special treatment at all.

What happens, is that word weight uses

log((total_number_of_rows-rows_matched)/rows_matched)

You see, that if rows_matched  total_number_of_rows/2, this gives
negative number and, as weight cannot be negative, is replaced by zero.

This formula was chosen from a number of different weighting schemes
in a series of tests aimed at best search effectivity.

By modifying ftdefs.h it can be replaced by a formula
that always produces positive weights, thus removing 50% limit.

Still, all this can be changed in MySQL 4.1 or 4.2

Regards,
Sergei

-- 
MySQL Development Team
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik [EMAIL PROTECTED]
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   ___/

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Format of doubles in queries(feature request)

[Roland]

Hello,
I don't know if this is the right mailing list for a simple feature 
request, but please read on anyway.
SKIP TO THE END IF YOU JUST WANT TO READ THE FEATURE REQUEST.

As a Java programmer, I often access a MySQL database trough a driver, and 
notice a little problem:
The MySQL format for DOUBLEs with exponent is like  3.41E+7
The '+' is required for positive exponents. If you wrote that same number 
as 3.41E7 it would generate a
syntax error.
The problem arises, when you convert a java double to string, the plus sign 
is omitted for positive exponents.
That means that java would output the above number as 3.41E7. When I pass 
this number on to a MySQL query I
get a syntax error. To avoid it I have two options:
1. (Easy) Enclose the double with single quotes. Works fine!
select * from  table where number='3.41E7';  instead of
select * from  table where number=3.41E7;
2. (Hard)  Change the format of the number to make Java convert it to 3.41E+7.

To make it all simpler why not:

FEATURE REQUEST:
Allow an alternate syntax for double types. When the exponent is positive 
the plus sign may be omitted. That means
3.41E+7 == 3.41E7.
Would that be too hard to implement? I don't think so. But it would save 
other programmers a lot of work. The way it is now,
each application programmer has to program his own conversion, or use the 
single-quotes trick.

Thanks for your attention,
Roland


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MYSQLDUMP feature request :)

[Peter Zaitsev]

Hello mysql,

  Resently  MYSQL have introduced transaction support, which at least
  in INNODB allows to make consistent backup/recovery.  The bad thing
  about this is - MYSQLDUMP does not support anything about this, so I
  had to done the same things buy hands.

  The Idea is quite simple - to add  transaction start in the begin of
  backup and in the begining of recovery. This would take a consistent
  backup of all of the tables, and as well will allow consistent
  recovery, at least  if not using drop table.
  

-- 
Best regards,
 Peter  mailto:[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Can mysql_close (conn) Be Modified to Close the Handle Immediately? (Possible Feature Request)

[Van]

Greetings:

I haven't seen any discussions on reducing the duration of a network connection
on port 3306 after issuing a mysql_close, so it makes sense to inquire whether
anyone has addressed this.

The code follows:
// main.c
#include mysql.h
if ((conn = do_connect (BTIME_HOST, USER, BTIME_PW, BTIME_DB, 0, NULL, 0)) ==
0);
// trivial processing of a quick insert, or update; no output
mysql_close (conn);

do_connect does the usual, such as mysql_init(), and some error checking
(trivial), then, passes back:
return(conn) on success.

My question (probably best addressed by someone with intimate knowledge of the
mysql_close code) is that while testing, I found the connection stays present
for about a minute:

vanboers@sedona:~$ while true; do date; netstat -tn | grep 3306; sleep 1; done
Mon Jun  4 23:55:52 MST 2001
tcp0  0 192.168.1.12:38012  192.168.1.14:3306   TIME_WAIT
snipped
Mon Jun  4 23:56:52 MST 2001
tcp0  0 192.168.1.12:38012  192.168.1.14:3306   TIME_WAIT
Mon Jun  4 23:56:53 MST 2001
Mon Jun  4 23:56:54 MST 2001

It's not a big deal, but, the question remains that, if the connection has been
closed (meaning processing is complete), then, there should be no reason for the
network connection to remain in TIME_WAIT state.

The application in question has potential to create a large volume of such
connections on an ongoing basis, and, particularly at times when there is high
volume of logging in and logging out of the network at large (i.e. 9 am, 12pm,
1pm, 5pm), which could become an issue on available connections depending on
scale and installation base.

Perhaps this is more a function of the 2.4.5 linux kernel TCP code, than
mysql_close, but, I'm not aware of any such 60 second implementation in the
kernel code.

If this is a MySQL issue, please advise.  I've no problem with disseminating the
full source to any interested party.

Best Regards,
Van
-- 
=
Linux rocks!!!   http://www.dedserius.com
=

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Feature Request: replicate-ignore-table for Master *before* killing updatelog!

[Peter Holm]

Hi,

it would be nice to have a replicate-ignore-table for the Master also! 

It works fine, but if one has to replicate only certain tables of a db
it would be good to have this. 

It is not enough to have this on slave only, because we do not want to
send the data of whole databases across the network for security
reasons!

If you follow this thought you might get the idea that it would be in
general useful to set up as many binlogs as one needs!

We are doing "manual" replication using update-log this way:
Read update-log - extract only needed data - send data to second
server. Of course this would not work with binlog, because we do not
know how to extract the lines we want from binlog.

Is there a closer documentation of the binlog-format?

What do you think about?

Please ask if it something was not clear.


Thanks for your attention.




Have a nice thread,
Peter

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Feature Request:

[Aaron's Shell Login]

I am BEGGING you to please make the describe command OPTIONALLY display the Privileges 
column. Before 3.23, I was able to see the describes properly. Now the command is 
almost totally useless to me!


Aaron

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php