Re: multiple return values from SP or workaround ?

[Wolfram Kraus]

Wim Verhaert wrote:
Is there any way to return multiple result variables from a stored 
procedure? And I guess the answer is NO.

Or does anyone know how I can Concatenate (using the CONCAT function) to 
glue together 
my multiple results into one string that then can  be post processed in 
perl.

EG:
query on table names returns multiple names:
+-+
| Name|
+-+
| name1   |
| name2   |
| name3   |
+-+
now the result returned should look something like
name1::name2::name3
Thanks
Wim
If you can write your query with "group by", GROUP_CONCAT may help you:
http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html
HTH
Wolfram
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Slave I/O thread dies, fatal error 1236

[matt ryan]

040901 18:36:21  Error reading packet from server: binlog truncated in 
the middle of event (server_errno=1236)
040901 18:36:21  Got fatal error 1236: 'binlog truncated in the middle 
of event' from master when reading data from binary log
040901 18:36:21  Slave I/O thread exiting, read up to log 
'FINANCE-bin.186', position 7517914

2 slaves on one box
both masters have identical config
one slave will not stay running for more than an hour, it seems that it 
will run until it catches up with the master, then it dies.

Any sugestions?  All servers have plenty of free drive space
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

moving data

[J S]

Hi,
I want to copy data from one table to another. However I need to do this 
carefully because I haven't got much of space left. I was thinking of maybe 
selecting data from every 10 rows of the old table, inserting it into 
the new table, then deleting those rows from the old table.

Could someone help me out with the SQL for this please? Or tell me if 
there's a better way of doing this?

Thanks,
JS.
_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!  
http://toolbar.msn.co.uk/

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

Re: bad "too many connections" error (os x)

[Egor Egorov]

Michael Winston <[EMAIL PROTECTED]> wrote:

Okay, so the first thing to try is obviously enlarge the max_connections. 
Have you tried this? 

Is it a webserver backend database? 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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]

Re: Replication bug?

[Egor Egorov]

"Logan, David (SST - Adelaide)" <[EMAIL PROTECTED]> wrote:

> We are trying to put a monitoring solution in place at a client and have
> come up against something during testing. If the replication user
> disappears off the master and the slave cannot log in, the
> Slave_IO_Thread still shows running and no error in the last error
> number field. Does anybody know if this is intentional? I can't find any
> references to this in the doco.

We will check. 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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]

Re: Server hanging

[Egor Egorov]

"Ronan Lucio" <[EMAIL PROTECTED]> wrote:

> I had a problem with our MySQL server-4.0.18 where
> it suddenly stoped working.
> Even a KILL command didn´t killed the mysqld process.

Use brute force - kill -9. 

> Does anyone knows what could make MySQL hangs?

Improper build, wrong hardware, misconfigured OS, etc. 

> And where can I find error messages that could tell me
> what happend?

In the .err file. 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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]

Re: 4.1.4 still reports itself as 4.1.3b-beta

[Egor Egorov]

Terry Riley <[EMAIL PROTECTED]> wrote:
> I have tried several instals (Windows XP), using binaries from several of 
> the mirrors, over a 4.1.3b-beta-log version. 
> 
> No errors reported during any install, but it still reports itself as the 
> 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12) and at 
> the command line opening of mysql.
> 
> Have I done something stupid, or are others seeing this, too?

First thing to check: have you stopped mysql service and started it again? 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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]

Re: Database connectivity

[Egor Egorov]

Seena Blace <[EMAIL PROTECTED]> wrote:

> I wanted to setup connectivity between mysql database and oracle database.I have one 
>  database in mysql  from which all tables data I want to insert real time data into 
> oracle database.How to connect mysql database with oracle database?

>From what I understood you want something like replication, no?  You may try to
use mysql binary logs for that task. Learn
http://dev.mysql.com/doc/mysql/en/Log_Files.html for details. 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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]

Re: moving data

[gerald_clark]

J S wrote:
Hi,
I want to copy data from one table to another. However I need to do 
this carefully because I haven't got much of space left. I was 
thinking of maybe selecting data from every 10 rows of the old 
table, inserting it into the new table, then deleting those rows from 
the old table. 
This will not free up the disk space used by those 10 rows.

Could someone help me out with the SQL for this please? Or tell me if 
there's a better way of doing this?

Thanks,
JS.
_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!  
http://toolbar.msn.co.uk/



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

Re: Slave I/O thread dies, fatal error 1236

[gerald_clark]

We have no idea what you are running, or what you are running it on.
matt ryan wrote:
040901 18:36:21  Error reading packet from server: binlog truncated in 
the middle of event (server_errno=1236)
040901 18:36:21  Got fatal error 1236: 'binlog truncated in the middle 
of event' from master when reading data from binary log
040901 18:36:21  Slave I/O thread exiting, read up to log 
'FINANCE-bin.186', position 7517914

2 slaves on one box
both masters have identical config
one slave will not stay running for more than an hour, it seems that 
it will run until it catches up with the master, then it dies.

Any sugestions?  All servers have plenty of free drive space

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

Re: mysql Architecture

[SGreen]

What _exactly_ would you like to know? The term "architecture" is rather 
all-encompassing and, to me anyway, too vague to answer . What specific 
element(s) of MySQL do you want detailed information about?

Of course, you could RTFM -> http://dev.mysql.com/doc/mysql/en/index.html

It's on-line, searchable, indexed, and quite detailed about many things. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Rajashik Kar <[EMAIL PROTECTED]> wrote on 09/01/2004 11:33:20 PM:

> 
> Hi All,
> 
> I would like to know some information about the MySql architecture.
> All the other databases have some info about their architecture in the 
> documentation.
> But I havent found any documentation on the mysql architecture.
> 
> Please refer me some websites or download liks where I can get some info 

> on mysql architecture.
> I would appreciate, if you could send me some documentation on the mysql 

> architecture.
> 
> thanks and regards,
> Rajashik
> 
> 
> 
> 
> 
> Confidentiality Notice 
> 
> The information contained in this electronic message and any 
> attachments to this message are intended
> for the exclusive use of the addressee(s) and may contain 
> confidential or privileged information. If
> you are not the intended recipient, please notify the sender at 
> Wipro or [EMAIL PROTECTED] immediately
> and destroy all copies of this message and any attachments.
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: PHP MySQL (Intel Compiler) Problem

[Egor Egorov]

"Santhanam" <[EMAIL PROTECTED]> wrote:

> Dear Friends,
>I am trying to compile PHP with MySQL support in an HP DL380
> server (Xeon Processors). The MySQL is build with Intel Compiler.
> I am getting the following problem / error :

You have to link the client applications with some intel C compiler libs I
guess.  Try to compile PHP with Intel C Compiler as well or upgrade the mysql
client libraries to the ones compiled by gcc. There is a good reason to have
MySQL server compiled with Intel C compiler but I'd rather prefere having
libmysqlclient compiled by gcc. 





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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]

RE: moving data

[SciBit MySQL Team]

A) 
0. optionally: start transaction;
1. create temporary table mytemptable select * from mytable order by mytableprimarykey 
limit 10;
2. insert into destinationtable select * from mytemptable;
3. delete * from mytable order by mytableprimarykey limit 10;
4. drop mytemptable;
5. optionally: commit;
6. Goto step 0.

if you don't want to "play it save", then you can skip step 1 and insert your data 
straight into the destination table.

or
B)
Study the MySQL Manual for the mysqldump's commandline parameters, to dump all your 
data to a file first. Then recreate your table on the database/table you wish.

or
C)
You can use many of the GUI tools around most of which can backup your data to scripts 
or other databases/tables. Ex. http://www.scibit.com/products/mycon
0. Double click your table, sort and limit your data as you wish
1. Click Backup
2. Use the resulting script against your destination table.
3. Hit Ctrl+A in your table's grid and then Del to delete the select records and then 
hit Refresh to start again (if you have selected only a 100,000 batch for example).
4. Goto step 1.

By default your resulting script will contain records batched 100 per insert statement 
and thus for a 100,000 records you will only have a 1000 insert statements.  This will 
of course execute much faster than 100,000 separate insert statements.

It would be highly recommended to use an intermediate local "dump" script file (if 
your space problem is not a concern for your local computer), that way you have all 
your data backed up for "just in case".

Kind Regards
SciBit MySQL Team
http://www.scibit.com

> 
> -Original Message-
> From: "J S" <[EMAIL PROTECTED]>
> To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
> CC: 
> Subject: moving data
> Sent: Thu, 02 Sep 2004 12:38:30 GMT
> Received: Thu, 02 Sep 2004 12:40:48 GMT
> Read: Thu, 02 Sep 2004 13:23:07 GMT
> Hi,
> 
> I want to copy data from one table to another. However I need to do this 
> carefully because I haven't got much of space left. I was thinking of maybe 
> selecting data from every 10 rows of the old table, inserting it into 
> the new table, then deleting those rows from the old table.
> 
> Could someone help me out with the SQL for this please? Or tell me if 
> there's a better way of doing this?
> 
> Thanks,
> 
> JS.
> 
> _
> Want to block unwanted pop-ups? Download the free MSN Toolbar now!  
> http://toolbar.msn.co.uk/
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 
> 


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

Re: Replication bug?

[Egor Egorov]

Yes, I confirm, it's a bug.





-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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]

Re: hosted application data design

[SGreen]

No, you aren't asking too much of MySQL in most of your questions but in 
another question, you are. Let me explain.

PRIMARY KEYs are nothing more than UNIQUE INDEXES on a table. How many 
columns it takes to uniquely identify any row of information depends on 
your data structure. Many people use an auto_incrementing INT (or BIGINT) 
column to artificially generate uniqueness. That means that their PRIMARY 
KEYS only require one column. Others have the requirements that certain 
combinations of columns cannot appear more than once in a table. They 
designate all of those columns as their PRIMARY KEY.  Here's an example 
that combines elements of both (for the sake of this sample, this is NOT a 
normalized data structure but it will be highly indexed)

CREATE TABLE us_buildings (
ID int not null auto_increment.
State  varchar(30) not null,
City varchar(30) not null,
County varchar(30) not null,
Road varchar(45) not null,
AddressNum int not null,
AddressSuffix varchar(15)
PRIMARY KEY (AddressNum, Road, City, State, AddressSuffix, County)
UNIQUE (ID),
Key(Road, City, State, AddressNum),
Key(State, County, City) 
Key(County, State, City)
)

This table is structured to ensure that any single structure in the US can 
be uniquely identified by it's position (AddressNum + AddressSuffix) along 
a road. The multiple-column PRIMARY KEY is used to prevent duplication and 
also serves as an index. The UNIQUE index on the ID column prevents anyone 
from changing the column into a duplicate value. That makes this column a 
good column to reference from another table (maybe a table like "resident" 
or "surveylog") without needing to duplicate all of the textual 
information it takes to identify the structure. The additional indexes are 
defined so that common searches on combinations of certain values will 
execute quickly. In a properly designed data structure, each element of 
that table would have their own table and this "fact table" would be using 
only the ID values of the other tables. That change (normalization) will 
make a HUGE difference in both storage requirements and index performance 
of this example as the database engine will retrieve numeric values 
directly from the index without the need for an additional seek (or two) 
to read the value from the table data and you will have nothing but 
numeric indexes on this table.

In your case, you can still use an auto_incrementing ID value as a 
reference value and your PRIMARY KEY can be composed of a combination of 
(client_ID, client_msg_num) but you will have to mange your per-client 
message numbers manually. It would be rather difficult to build into any 
database a management system that automates the process of creating an 
auto_incrementing value based on the unique combination of values in one 
or several columns.

Just before you insert a new message, you would need to check for the 
MAX(client_msg_num) for the client_id creating the message and assign the 
new message the next larger number. You would use transactions (InnoDB) or 
table locking (all table types)  to prevent a second instance of your 
application from doing the exact same thing at the exact same time (if you 
don't serialize your access to your message numbers, you could 
accidentally issue duplicate numbers). Some DBAs would design a second 
table that stores the most recently used message number for each client_ID 
(I have used both techniques). Which method works best for you will depend 
on your benchmark testing.

I hope you see that MySQL does most of the work for what you want to do 
but not quite all of it.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



Justin French <[EMAIL PROTECTED]> wrote on 09/02/2004 12:31:57 
AM:

> Hi all,
> 
> I'm designing a hosted application that many clients will access.  I've 
> got all sites running off one PHP code base quite nicely, and now I'm 
> hoping to reduce the multiple databases down to just one shared DB.
> 
> (Note: at this stage, all accounts are fictional, so there's no legacy 
> data to worry about).
> 
> So instead of each account have it's own database and autonomous 
> tables, I'd like all accounts to share one database.  This will:
> 
> - make updates easy
> - allow each account to be "group aware"
> - allow a parent website easy access to all account data
> 
> I've added client_id columns to all tables, and things are working 
> fine, with the following exception...
> 
> I'm worried about the primary keys (IDs) for each table growing to an 
> unmanageable size pretty quick... 50 accounts * 500 forum posts = 25000 
> IDs pretty quickly.  50 sites * 5000 members = 250,000 members.
> 
> I'm sure this isn't much of a technical/performance issue (with proper 
> indexing), but it's just strikes me as a little strange that the first 
> news post for an account could have an ID of '502846', rather than '1'.
> 
> 

Re: 4.1.4 still reports itself as 4.1.3b-beta

[Terry Riley]

Oh, yes, certainly, Egor. I stopped it before doing the upgrade, and 
restarted afterwards. And have restarted several times since, because I 
couldn't believe it!

The majority of the files now in the c:\mysql directory have creation 
dates on or about 27 August, the documentation is for 4.1.4gamma, so it 
does look as though the upgrade went through, as far as copying files is 
concerned.

Just restarted again with no change (just in case). Any other clues?

Terry

- Original Message -

> Terry Riley <[EMAIL PROTECTED]> wrote:
> > I have tried several instals (Windows XP), using binaries from 
> > several of the mirrors, over a 4.1.3b-beta-log version. > > No errors 
> > > > reported during any install, but it still reports itself as the 
> > 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12) and 
> > at the command line opening of mysql.
> > 
> > Have I done something stupid, or are others seeing this, too?
> 
> First thing to check: have you stopped mysql service and started it 
> again? 
> 
> 



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

RE: 4.1.4 still reports itself as 4.1.3b-beta

[Victor Pendleton]

In the registry and/or the windows service utility see which executable is
actually in the path. 

-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 9/2/04 9:19 AM
Subject: Re: 4.1.4 still reports itself as 4.1.3b-beta

Oh, yes, certainly, Egor. I stopped it before doing the upgrade, and 
restarted afterwards. And have restarted several times since, because I 
couldn't believe it!

The majority of the files now in the c:\mysql directory have creation 
dates on or about 27 August, the documentation is for 4.1.4gamma, so it 
does look as though the upgrade went through, as far as copying files is

concerned.

Just restarted again with no change (just in case). Any other clues?

Terry

- Original Message -

> Terry Riley <[EMAIL PROTECTED]> wrote:
> > I have tried several instals (Windows XP), using binaries from 
> > several of the mirrors, over a 4.1.3b-beta-log version. > > No
errors 
> > > > reported during any install, but it still reports itself as the 
> > 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12) and

> > at the command line opening of mysql.
> > 
> > Have I done something stupid, or are others seeing this, too?
> 
> First thing to check: have you stopped mysql service and started it 
> again? 
> 
> 



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

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

RE: tuning suggestion for large query

[mos]

At 04:13 PM 9/1/2004, Sun, Jennifer wrote:
Thanks Mike.
Seems like even with handler, the big query process is still consuming all 
my RAM and swap and being killed with error
'VM: killing process mysql
__alloc_pages: 0-order allocation failed (gfp=0x1d2/0)'

I would like to find a startup parameter either for client or server to 
limit per thread memory usage.
Can't help you there, sorry. I don't think that would work because MySQL is 
putting the retrieved rows into memory to get a snapshot of the table, so 
memory has to decrease. If you decrease the memory for the thread, then it 
will hit a wall.

I thought Handler would work because it is using a cursor into the table 
and shouldn't be consuming much memory. You fetch 10,000 rows at a time, 
then fetch another 10,000 rows etc.. It should only be consuming enough 
memory to hold 10,000 rows. Can you post the code showing how you're using 
Handler?

The only other thing I can suggest is to put a select statement inside of a 
loop and select using either a primary key, say rcd_id 1 to , export 
them, then select 1 to 1 and export it etc until you reach eof. 
This should be quite fast and consumes low amount of memory. I would 
recommend using the primary key instead of say "select ... LIMIT 
," because the offset will need to read over the first 'n' 
offset rows and this can be quite slow. Because you're doing only part of 
the table at a time, you won't get an exact snapshot if people are updating 
the table as you are exporting it, but it will be very low on memory.

Mike

-Original Message-
From: mos [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 01, 2004 4:39 PM
To: [EMAIL PROTECTED]
Subject: Re: tuning suggestion for large query
At 10:40 AM 9/1/2004, you wrote:
>Hi,
>
>We have a job that do 'select * from big-table' on a staging mysql
>database, then dump to data warehouse, it is scheduled to run once a day,
>but may be run manually. Also we have several other small OLTP database on
>the same server.
>When the big job run, it would use all the physical mem and swap, all
>other process slow down because of this.
>
>I would like to limit the resource usage for each mysql client  so that
>they can use only certain max amount of RAM, and don't select everything
>into memory before display it to users. However, I couldn't find any
>parameter would let me implement it.
>Anybody ever encounter the same issue before? Please share your experience.
>
>Thanks.
>
>-
Try the Handler command with an Index if you need the results sorted. It
may do the trick. :)
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To 
unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

Re: Suppression of result in SELECT @temp := column?

[Diana Soares]

I didn't understand the problem very well, but maybe disabling the pager
before that query where you set @temp, do the query, setting the pager
to its default value and then do the  "next statement to produce the
actual result."

\P cat /dev/null
SELECT @temp := columnID FROM table WHERE column = whatever LIMIT 0,1
\n
SELECT 

-- 
Diana Soares


On Tue, 2004-08-31 at 23:00, Eamon Daly wrote:
> Did anyone ever follow up on this question? I'm looking for
> the answer, too.
> 
> Eamon Daly

> - Original Message - 
> From: <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Wednesday, August 25, 2004 9:16 AM
> Subject: Suppression of result in SELECT @temp := column?

> > Is there any way to not sending the result of a user variable assignment
> > to the client?
> > I.e. Suppress the result of;
> >
> > SELECT @temp := columnID FROM table WHERE column = whatever LIMIT 0,1
> >
> > ...since I only use @temp in my next statement to produce the actual
> result.



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

Re: UPDATE string segment?

[Michael Stassen]

That's a little trickier.  You can use SUBSTRING_INDEX() to split a string 
on the Nth '/', but N will vary with path length.  With N = -1, though, 
we'll get the piece after the last '/' (the filename).  Then we could 
replace that with nothing in the original string.  That is, something like 
this (I'm assuming your column is named file_path):

  SELECT REPLACE(file_path, SUBSTRING_INDEX(file_path,'/',-1),'') FROM ...
There may be other combinations of string functions which would do the same 
thing.  See the manual for details 
.

I should point out that feeding a column through a function (or functions) 
like that is fine for the SELECT part, but undesirable in a WHERE clause, 
because once you feed the column to a function, you render any index on that 
column useless (for that query).  If you will be choosing rows based on 
their paths, this will be a problem.

In my experience, it's best to keep path and filename separate.  That is, 
break your current file_path column into two columns, path and filename. 
Then your three example rows would become

path  filename
===   
/Volumes/External HD/aFolder/ aFile.pdf
/Volumes/External HD/aFolder/ anotherFile.pdf
/Volumes/External HD/aFolder/aChildFolder/aThirdFile.pdf
That would have made both of your questions today much simpler to answer.
1) Change the path:
  UPDATE your_table SET path = '/Volumes/External HD/aFolder/aChildFolder/'
  WHERE path = '/Volumes/External HD/aFolder/' ...
2) Get just the path
  SELECT path FROM ...
3) Get the full file_path
  SELECT CONCAT(path,filename) FROM ...
Changing your table should be easy, now that we've already figured out how 
to separate path from filename:

  ALTER TABLE your_table
  ADD COLUMN path CHAR(N),
  ADD COLUMN filename CHAR(M);
Choose CHAR or VARCHAR and suitable values of M and N.  Then,
  UPDATE yourtable
  SET path = REPLACE(file_path, SUBSTRING_INDEX(file_path,'/',-1),''),
  filename = SUBSTRING_INDEX(file_path,'/',-1);
Inspect to make sure it worked, then
  ALTER TABLE yourtable DROP COLUMN file_path;
Of course, you no doubt have code already in place which expects your 
current table definition, so you'll have to weigh the work of changing your 
code against the benefits of separating filename from path to make your own 
determination of whether or not it's worth it.

Michael
John Mistler wrote:
Wow!  That works.  Thanks a bunch!  While we're at it, I have one more
hang-up:
Is there a (DISTINCT?) statement that will select the distinct folder
path(s) of every entry in the table?
For instance, if I have three files:
"/Volumes/External HD/aFolder/aFile.pdf"
"/Volumes/External HD/aFolder/anotherFile.pdf"
"/Volumes/External HD/aFolder/aChildFolder/aThirdFile.pdf"
the query would return:
"/Volumes/External HD/aFolder/"
"/Volumes/External HD/aFolder/aChildFolder/"
Any ideas?
Thanks again,
John
on 9/1/04 8:03 PM, Michael Kruckenberg at [EMAIL PROTECTED]
wrote:

You can use the replace string function:
update table set 
file_path=replace(file_path,'aFolder','aFolder/aChildFolder');

In each update aFolder will be replaced by the new path.
John Mistler wrote:
I have a column that holds hard disk file location info such as:
"/Volumes/External HD/aFolder/aFile.pdf"
"/Volumes/External HD/aFolder/anotherFile.pdf"
etc. . . (many files located in the same folder)
Can anyone suggest a single statement (or multiple) that would update every
file location located in this same folder to another location, say:
"/Volumes/External HD/aFolder/aChildFolder/aFile.pdf"
"/Volumes/External HD/aFolder/aChildFolder/anotherFile.pdf"
etc . . . ?
Thanks,
John


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

Query hangs mysql 4.1

[Mauricio Pellegrini]

Hi, 
I've experienced a hang after running a query wich is run usually 2 to 3
times a day without a problem till now. 

This is what the error log reports

nnoDB: Thread 4784139 stopped in file btr0pcur.c line 205
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbe3fddb8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8108af7
0x4014c895
0x8280a88
0x82630ba
0x82694eb
0x82697a1
0x82818f0
0x8282495
0x828267a
0x826b7bc
0x817abf3
0x814edf1
0x814ef81
0x81401e2
0x8137049
0x8137585
0x814e061
0x8118164
0x811b121
0x811552f
0x8114ec2
0x8114637
0x40146c60
0x402e9b77
New value of fp=(nil) failed sanity check, terminating stack trace!

Also have part of the query in the log. and some other warnings about
the memory bee used acording to the defined variables.

I've used resolve_stack_dump -s /usr/lib/mysql/mysqld-max.sym -n
mysqld.stack
as instructed in the manual, with the following result

0x8108af7 handle_segfault + 423
0x4014c895 _end + 934924901
0x8280a88 row_upd_build_sec_rec_difference_binary + 408
0x82630ba row_ins_sec_index_entry_by_modify + 90
0x82694eb row_ins_index_entry_low + 2859
0x82697a1 row_ins_index_entry + 65
0x82818f0 row_upd_sec_index_entry + 848
0x8282495 row_upd + 197
0x828267a row_upd_step + 282
0x826b7bc row_update_for_mysql + 700
0x817abf3 update_row__11ha_innobasePCcPc + 291
0x814edf1 do_updates__12multi_updateb + 465
0x814ef81 send_eof__12multi_update + 49
0x81401e2 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 530
0x8137049 exec__4JOIN + 4185
0x8137585
mysql_select__FP3THDPPP4ItemP13st_table_listUiRt4List1Z4ItemP4ItemUiP8st_orderT7T5T7UlP13select_resultP18st_select_lex_unitP13s
 + 837
0x814e061
mysql_multi_update__FP3THDP13st_table_listPt4List1Z4ItemT2P4ItemUl15enum_duplicatesP18st_select_lex_unitP13st_select_lex
 + 369
0x8118164 mysql_execute_command__FP3THD + 7780
0x811b121 mysql_parse__FP3THDPcUi + 177
0x811552f dispatch_command__F19enum_server_commandP3THDPcUi + 1631
0x8114ec2 do_command__FP3THD + 162
0x8114637 handle_one_connection + 551
0x40146c60 _end + 934901296
0x402e9b77 _end + 936617287

Watching those entries in the error log I cant figure why is this
happening. Can anybody give me a hint?

Thank you 
Mauricio



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

Before I go searching (shameless)

[Stuart Felenstein]

I'm tring to extract all records belonging to one ID
in a table. 
BUT! I want to do it in piece meal.  Meaning a record
at a time.  
I am assuming I need some kind of count mechanism.

Example: 
I want to see the first 3 purchases this member made.
Instead of grabbing them all in one shot, I want to
break  those 3 out in 3 seperate results set

Results Set1: Purchase 1
Results Set2: Purchase 2
Results Set3: Purchase 3

This is probably easy.  

Thank you,
Stuart

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

Re: Before I go searching (shameless)

[Stuart Felenstein]

Wait!! Don't answer...it's that LIMIT
thing.


:)
Stuart


--- Stuart Felenstein <[EMAIL PROTECTED]> wrote:

> I'm tring to extract all records belonging to one ID
> in a table. 
> BUT! I want to do it in piece meal.  Meaning a
> record
> at a time.  
> I am assuming I need some kind of count mechanism.
> 
> Example: 
> I want to see the first 3 purchases this member
> made.
> Instead of grabbing them all in one shot, I want to
> break  those 3 out in 3 seperate results set
> 
> Results Set1: Purchase 1
> Results Set2: Purchase 2
> Results Set3: Purchase 3
> 
> This is probably easy.  
> 
> Thank you,
> Stuart
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


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

RE: 4.1.4 still reports itself as 4.1.3b-beta

[Terry Riley]

Victor

C:\MYSQL\BIN\mysqld-opt --defaults-file="C:\WINDOWS\my.ini" MySql

is what the path-to-executable states. If I remember rightly, the new 
version should be mysqld, period. Is that what's wrong?

Will I have to tweak the registry to change that?

Cheers
Terry

- Original Message -

> In the registry and/or the windows service utility see which executable 
> is
> actually in the path. 
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]
> Sent: 9/2/04 9:19 AM
> Subject: Re: 4.1.4 still reports itself as 4.1.3b-beta
> 
> Oh, yes, certainly, Egor. I stopped it before doing the upgrade, and 
> restarted afterwards. And have restarted several times since, because I 
> couldn't believe it!
> 
> The majority of the files now in the c:\mysql directory have creation 
> dates on or about 27 August, the documentation is for 4.1.4gamma, so it 
> does look as though the upgrade went through, as far as copying files is
> 
> concerned.
> 
> Just restarted again with no change (just in case). Any other clues?
> 
> Terry
> 
> - Original Message -
> 
> > Terry Riley <[EMAIL PROTECTED]> wrote:
> > > I have tried several instals (Windows XP), using binaries from 
> > > several of the mirrors, over a 4.1.3b-beta-log version. > > No
> errors 
> > > > > reported during any install, but it still reports itself as the 
> > > 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12) and
> 
> > > at the command line opening of mysql.
> > > 
> > > Have I done something stupid, or are others seeing this, too?
> > 
> > First thing to check: have you stopped mysql service and started it 
> > again? 
> > 
> > 



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

Re: hosted application data design

[SGreen]

Thank you. Michael. I do remember reading that (now that you got me to 
re-read that part of the manual. "The manual is your friend. The manual is 
your friend. The...") but I have never used that feature so it didn't come 
to mind as I was writing. Apologies to all for not pre-checking myself 
(not enough caffeine yet today, I guess.) 

Again, thanks! 8-D

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Michael Stassen <[EMAIL PROTECTED]> wrote on 09/02/2004 10:54:32 
AM:

> Shawn,
> 
> I'm not certain you are saying otherwise (or simply recommending against 

> it), but I want to point out that mysql can handle separate 
auto_increment 
> series for each client_id with MyISAM or BDB tables.  You set up the 
table 
> like this:
> 
>CREATE TABLE messages (client_id UNSIGNED INT NOT NULL,
>   msg_id UNSIGNED BIGINT NOT NULL 
AUTO_INCREMENT,
>   PRIMARY KEY (client_id, msg_id),
>   other columns ...
> 
> MySQL will keep a separate count for each client_id.  Of course, this 
has 
> the disadvantage you pointed out of requiring 2 columns to reference 
rows.
> 
> See  
> for details.
> 
> Michael
> 
> 
> [EMAIL PROTECTED] wrote:
> 
> > No, you aren't asking too much of MySQL in most of your questions but 
in 
> > another question, you are. Let me explain.
> > 
> > PRIMARY KEYs are nothing more than UNIQUE INDEXES on a table. How many 

> > columns it takes to uniquely identify any row of information depends 
on 
> > your data structure. Many people use an auto_incrementing INT (or 
BIGINT) 
> > column to artificially generate uniqueness. That means that their 
PRIMARY 
> > KEYS only require one column. Others have the requirements that 
certain 
> > combinations of columns cannot appear more than once in a table. They 
> > designate all of those columns as their PRIMARY KEY.  Here's an 
example 
> > that combines elements of both (for the sake of this sample, this is 
NOT a 
> > normalized data structure but it will be highly indexed)
> > 
> > CREATE TABLE us_buildings (
> > ID int not null auto_increment.
> > State  varchar(30) not null,
> > City varchar(30) not null,
> > County varchar(30) not null,
> > Road varchar(45) not null,
> > AddressNum int not null,
> > AddressSuffix varchar(15)
> > PRIMARY KEY (AddressNum, Road, City, State, AddressSuffix, 
County)
> > UNIQUE (ID),
> > Key(Road, City, State, AddressNum),
> > Key(State, County, City) 
> > Key(County, State, City)
> > )
> > 
> > This table is structured to ensure that any single structure in the US 
can 
> > be uniquely identified by it's position (AddressNum + AddressSuffix) 
along 
> > a road. The multiple-column PRIMARY KEY is used to prevent duplication 
and 
> > also serves as an index. The UNIQUE index on the ID column prevents 
anyone 
> > from changing the column into a duplicate value. That makes this 
column a 
> > good column to reference from another table (maybe a table like 
"resident" 
> > or "surveylog") without needing to duplicate all of the textual 
> > information it takes to identify the structure. The additional indexes 
are 
> > defined so that common searches on combinations of certain values will 

> > execute quickly. In a properly designed data structure, each element 
of 
> > that table would have their own table and this "fact table" would be 
using 
> > only the ID values of the other tables. That change (normalization) 
will 
> > make a HUGE difference in both storage requirements and index 
performance 
> > of this example as the database engine will retrieve numeric values 
> > directly from the index without the need for an additional seek (or 
two) 
> > to read the value from the table data and you will have nothing but 
> > numeric indexes on this table.
> > 
> > In your case, you can still use an auto_incrementing ID value as a 
> > reference value and your PRIMARY KEY can be composed of a combination 
of 
> > (client_ID, client_msg_num) but you will have to mange your per-client 

> > message numbers manually. It would be rather difficult to build into 
any 
> > database a management system that automates the process of creating an 

> > auto_incrementing value based on the unique combination of values in 
one 
> > or several columns.
> > 
> > Just before you insert a new message, you would need to check for the 
> > MAX(client_msg_num) for the client_id creating the message and assign 
the 
> > new message the next larger number. You would use transactions 
(InnoDB) or 
> > table locking (all table types)  to prevent a second instance of your 
> > application from doing the exact same thing at the exact same time (if 
you 
> > don't serialize your access to your message numbers, you could 
> > accidentally issue duplicate numbers). Some DBAs would design a secon

Re: hosted application data design

[Michael Stassen]

Shawn,
I'm not certain you are saying otherwise (or simply recommending against 
it), but I want to point out that mysql can handle separate auto_increment 
series for each client_id with MyISAM or BDB tables.  You set up the table 
like this:

  CREATE TABLE messages (client_id UNSIGNED INT NOT NULL,
 msg_id UNSIGNED BIGINT NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (client_id, msg_id),
 other columns ...
MySQL will keep a separate count for each client_id.  Of course, this has 
the disadvantage you pointed out of requiring 2 columns to reference rows.

See  for details.
Michael
[EMAIL PROTECTED] wrote:
No, you aren't asking too much of MySQL in most of your questions but in 
another question, you are. Let me explain.

PRIMARY KEYs are nothing more than UNIQUE INDEXES on a table. How many 
columns it takes to uniquely identify any row of information depends on 
your data structure. Many people use an auto_incrementing INT (or BIGINT) 
column to artificially generate uniqueness. That means that their PRIMARY 
KEYS only require one column. Others have the requirements that certain 
combinations of columns cannot appear more than once in a table. They 
designate all of those columns as their PRIMARY KEY.  Here's an example 
that combines elements of both (for the sake of this sample, this is NOT a 
normalized data structure but it will be highly indexed)

CREATE TABLE us_buildings (
ID int not null auto_increment.
State  varchar(30) not null,
City varchar(30) not null,
County varchar(30) not null,
Road varchar(45) not null,
AddressNum int not null,
AddressSuffix varchar(15)
PRIMARY KEY (AddressNum, Road, City, State, AddressSuffix, County)
UNIQUE (ID),
Key(Road, City, State, AddressNum),
Key(State, County, City) 
Key(County, State, City)
)

This table is structured to ensure that any single structure in the US can 
be uniquely identified by it's position (AddressNum + AddressSuffix) along 
a road. The multiple-column PRIMARY KEY is used to prevent duplication and 
also serves as an index. The UNIQUE index on the ID column prevents anyone 
from changing the column into a duplicate value. That makes this column a 
good column to reference from another table (maybe a table like "resident" 
or "surveylog") without needing to duplicate all of the textual 
information it takes to identify the structure. The additional indexes are 
defined so that common searches on combinations of certain values will 
execute quickly. In a properly designed data structure, each element of 
that table would have their own table and this "fact table" would be using 
only the ID values of the other tables. That change (normalization) will 
make a HUGE difference in both storage requirements and index performance 
of this example as the database engine will retrieve numeric values 
directly from the index without the need for an additional seek (or two) 
to read the value from the table data and you will have nothing but 
numeric indexes on this table.

In your case, you can still use an auto_incrementing ID value as a 
reference value and your PRIMARY KEY can be composed of a combination of 
(client_ID, client_msg_num) but you will have to mange your per-client 
message numbers manually. It would be rather difficult to build into any 
database a management system that automates the process of creating an 
auto_incrementing value based on the unique combination of values in one 
or several columns.

Just before you insert a new message, you would need to check for the 
MAX(client_msg_num) for the client_id creating the message and assign the 
new message the next larger number. You would use transactions (InnoDB) or 
table locking (all table types)  to prevent a second instance of your 
application from doing the exact same thing at the exact same time (if you 
don't serialize your access to your message numbers, you could 
accidentally issue duplicate numbers). Some DBAs would design a second 
table that stores the most recently used message number for each client_ID 
(I have used both techniques). Which method works best for you will depend 
on your benchmark testing.

I hope you see that MySQL does most of the work for what you want to do 
but not quite all of it.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Justin French <[EMAIL PROTECTED]> wrote on 09/02/2004 12:31:57 
AM:


Hi all,
I'm designing a hosted application that many clients will access.  I've 
got all sites running off one PHP code base quite nicely, and now I'm 
hoping to reduce the multiple databases down to just one shared DB.

(Note: at this stage, all accounts are fictional, so there's no legacy 
data to worry about).

So instead of each account have it's own database and autonomous 
tables, I'd like all accounts to s

Re: Before I go searching (shameless)

[SGreen]

Say it with me:

"The manual is your friend. The manual is your friend. The manual..." - 
(it just bit me, too,  on another thread) LOL

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Stuart Felenstein <[EMAIL PROTECTED]> wrote on 09/02/2004 11:02:06 AM:

> Wait!! Don't answer...it's that LIMIT
> thing.
> 
> 
> :)
> Stuart
> 
> 
> --- Stuart Felenstein <[EMAIL PROTECTED]> wrote:
> 
> > I'm tring to extract all records belonging to one ID
> > in a table. 
> > BUT! I want to do it in piece meal.  Meaning a
> > record
> > at a time. 
> > I am assuming I need some kind of count mechanism.
> > 
> > Example: 
> > I want to see the first 3 purchases this member
> > made.
> > Instead of grabbing them all in one shot, I want to
> > break  those 3 out in 3 seperate results set
> > 
> > Results Set1: Purchase 1
> > Results Set2: Purchase 2
> > Results Set3: Purchase 3
> > 
> > This is probably easy. 
> > 
> > Thank you,
> > Stuart
> > 
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: 
> >
> http://lists.mysql.com/[EMAIL PROTECTED]
> > 
> > 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: Before I go searching (shameless)

[V. M. Brasseur]

Here is the manual.  I suggest you try a search for "LIMIT" in the 
context of a SELECT.

http://dev.mysql.com/doc/mysql/en/index.html
However what it sounds like you really ought to do is give a walk 
through the tutorial.

http://dev.mysql.com/doc/mysql/en/Tutorial.html
If, after exploring these two links, you still have questions feel free 
to post them and people will be glad to be of assistance.

RTFM,
--V
Stuart Felenstein wrote:
I'm tring to extract all records belonging to one ID
in a table. 
BUT! I want to do it in piece meal.  Meaning a record
at a time.  
I am assuming I need some kind of count mechanism.

Example: 
I want to see the first 3 purchases this member made.
Instead of grabbing them all in one shot, I want to
break  those 3 out in 3 seperate results set

Results Set1: Purchase 1
Results Set2: Purchase 2
Results Set3: Purchase 3
This is probably easy.  

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

RE: 4.1.4 still reports itself as 4.1.3b-beta

[Victor Pendleton]

You can test if this is correct by manually starting the service from a dos
window and logging and seeing what version this executable actually is.

-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 9/2/04 10:01 AM
Subject: RE: 4.1.4 still reports itself as 4.1.3b-beta

Victor

C:\MYSQL\BIN\mysqld-opt --defaults-file="C:\WINDOWS\my.ini" MySql

is what the path-to-executable states. If I remember rightly, the new 
version should be mysqld, period. Is that what's wrong?

Will I have to tweak the registry to change that?

Cheers
Terry

- Original Message -

> In the registry and/or the windows service utility see which
executable 
> is
> actually in the path. 
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]
> Sent: 9/2/04 9:19 AM
> Subject: Re: 4.1.4 still reports itself as 4.1.3b-beta
> 
> Oh, yes, certainly, Egor. I stopped it before doing the upgrade, and 
> restarted afterwards. And have restarted several times since, because
I 
> couldn't believe it!
> 
> The majority of the files now in the c:\mysql directory have creation 
> dates on or about 27 August, the documentation is for 4.1.4gamma, so
it 
> does look as though the upgrade went through, as far as copying files
is
> 
> concerned.
> 
> Just restarted again with no change (just in case). Any other clues?
> 
> Terry
> 
> - Original Message -
> 
> > Terry Riley <[EMAIL PROTECTED]> wrote:
> > > I have tried several instals (Windows XP), using binaries from 
> > > several of the mirrors, over a 4.1.3b-beta-log version. > > No
> errors 
> > > > > reported during any install, but it still reports itself as
the 
> > > 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12)
and
> 
> > > at the command line opening of mysql.
> > > 
> > > Have I done something stupid, or are others seeing this, too?
> > 
> > First thing to check: have you stopped mysql service and started it 
> > again? 
> > 
> > 



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

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

RE: 4.1.4 still reports itself as 4.1.3b-beta

[Terry Riley]

Further to last message, I've found that string in three different places 
in the registry:

HKEY_LOCAL_MACHINE\system\controlset001\services\MySQL\ImagePath\

HKEY_LOCAL_MACHINE\system\controlset003\services\MySQL\ImagePath\

HKEY_LOCAL_MACHINE\system\currentcontrolset\services\MySQL\ImagePath\


Never having had to fiddle with the registry much in the past, can I 
presume that if I ignore the first two and change the third one to read 
'mysqld' instead of 'mysqld-opt', save and reboot, I should get the right 
one started? As you can tell, I'm a little wary of this.

Cheers
Terry

- Original Message -

> In the registry and/or the windows service utility see which executable 
> is
> actually in the path. 
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]
> Sent: 9/2/04 9:19 AM
> Subject: Re: 4.1.4 still reports itself as 4.1.3b-beta
> 
> Oh, yes, certainly, Egor. I stopped it before doing the upgrade, and 
> restarted afterwards. And have restarted several times since, because I 
> couldn't believe it!
> 
> The majority of the files now in the c:\mysql directory have creation 
> dates on or about 27 August, the documentation is for 4.1.4gamma, so it 
> does look as though the upgrade went through, as far as copying files is
> 
> concerned.
> 
> Just restarted again with no change (just in case). Any other clues?
> 
> Terry
> 
> - Original Message -
> 
> > Terry Riley <[EMAIL PROTECTED]> wrote:
> > > I have tried several instals (Windows XP), using binaries from 
> > > several of the mirrors, over a 4.1.3b-beta-log version. > > No
> errors 
> > > > > reported during any install, but it still reports itself as the 
> > > 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12) and
> 
> > > at the command line opening of mysql.
> > > 
> > > Have I done something stupid, or are others seeing this, too?
> > 
> > First thing to check: have you stopped mysql service and started it 
> > again? 
> > 
> > 



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

Re: Before I go searching (shameless)

[Stuart Felenstein]

Well I try to avoid the manual , but refer to some
books that I've collected.  Essentially the same, but
more palatable for my tastes.  Not that I wouldnl't
check the manual.

What I needed was "order by Desc limit (x, x) .  In my
case 3 queries, with (0,1)(1,1)(2,1) .
The first 3 records , 1 at a time.

Stuart
--- "V. M. Brasseur" <[EMAIL PROTECTED]> wrote:

> Here is the manual.  I suggest you try a search for
> "LIMIT" in the 
> context of a SELECT.
> 
> http://dev.mysql.com/doc/mysql/en/index.html
> 
> However what it sounds like you really ought to do
> is give a walk 
> through the tutorial.
> 
> http://dev.mysql.com/doc/mysql/en/Tutorial.html
> 
> If, after exploring these two links, you still have
> questions feel free 
> to post them and people will be glad to be of
> assistance.
> 
> RTFM,
> 
> --V
> 
> 
> Stuart Felenstein wrote:
> > I'm tring to extract all records belonging to one
> ID
> > in a table. 
> > BUT! I want to do it in piece meal.  Meaning a
> record
> > at a time.  
> > I am assuming I need some kind of count mechanism.
> > 
> > Example: 
> > I want to see the first 3 purchases this member
> made.
> > Instead of grabbing them all in one shot, I want
> to
> > break  those 3 out in 3 seperate results set
> > 
> > Results Set1: Purchase 1
> > Results Set2: Purchase 2
> > Results Set3: Purchase 3
> > 
> > This is probably easy.  
> > 
> > Thank you,
> > Stuart
> > 
> 


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

Re: Slave I/O thread dies, fatal error 1236

[matt ryan]

gerald_clark wrote:
We have no idea what you are running, or what you are running it on.
matt ryan wrote:
040901 18:36:21  Error reading packet from server: binlog truncated 
in the middle of event (server_errno=1236)
040901 18:36:21  Got fatal error 1236: 'binlog truncated in the 
middle of event' from master when reading data from binary log
040901 18:36:21  Slave I/O thread exiting, read up to log 
'FINANCE-bin.186', position 7517914

2 slaves on one box
both masters have identical config
one slave will not stay running for more than an hour, it seems that 
it will run until it catches up with the master, then it dies.

Any sugestions?  All servers have plenty of free drive space
Oops sorry
windows 2000, mysql 4.0.20d
here's the config on the affected server, it is identical to the server 
that works fine, except for the port number and base/data dir's

it's interesting to note, that if I wait a few minutes after the thread 
dies, and issue "start slave" it'll usually start right up and run until 
it's caught up to the master server again.

Matt
skip-locking
set-variable= key_buffer_size=1500M
set-variable=join_buffer_size=512M
set-variable= max_allowed_packet=300M
set-variable= table_cache=512
set-variable=delay_key_write=ALL
set-variable= sort_buffer_size=256M
set-variable= record_buffer=300M
set-variable= thread_cache=8
set-variable=myisam_sort_buffer_size=256M
myisam-recover=BACKUP,FORCE
set-variable=read_buffer_size=300M
set-variable=interactive_timeout=7200
set-variable=wait_timeout=7200
set-variable=thread_concurrency=4
server-id=5
#log-bin
master-host=192.168.1.168
master-port=3306
master-user=repl
master-password=Daredevil22
master-connect-retry=60

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

RE: 4.1.4 still reports itself as 4.1.3b-beta

[Paul DuBois]

At 10:13 -0500 9/2/04, Victor Pendleton wrote:
You can test if this is correct by manually starting the service from a dos
window and logging and seeing what version this executable actually is.
Or check from the command line without even starting it:
mysqld --version

-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 9/2/04 10:01 AM
Subject: RE: 4.1.4 still reports itself as 4.1.3b-beta
Victor
C:\MYSQL\BIN\mysqld-opt --defaults-file="C:\WINDOWS\my.ini" MySql
is what the path-to-executable states. If I remember rightly, the new
version should be mysqld, period. Is that what's wrong?
Will I have to tweak the registry to change that?
Cheers
Terry
- Original Message -
 In the registry and/or the windows service utility see which
executable
 is
 actually in the path.
 -Original Message-
 From: [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: 9/2/04 9:19 AM
 Subject: Re: 4.1.4 still reports itself as 4.1.3b-beta
 Oh, yes, certainly, Egor. I stopped it before doing the upgrade, and
 restarted afterwards. And have restarted several times since, because
I
 couldn't believe it!
 The majority of the files now in the c:\mysql directory have creation
 dates on or about 27 August, the documentation is for 4.1.4gamma, so
it
 does look as though the upgrade went through, as far as copying files
is
 concerned.
 Just restarted again with no change (just in case). Any other clues?
 Terry
 - Original Message -
 > Terry Riley <[EMAIL PROTECTED]> wrote:
 > > I have tried several instals (Windows XP), using binaries from
 > > several of the mirrors, over a 4.1.3b-beta-log version. > > No
 errors
 > > > > reported during any install, but it still reports itself as
the
 > > 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12)
and
 > > at the command line opening of mysql.
 > >
 > > Have I done something stupid, or are others seeing this, too?
 >
 > First thing to check: have you stopped mysql service and started it
 > again?
 >
 >

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

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: 4.1.4 still reports itself as 4.1.3b-beta

[Victor Pendleton]

DuBois just trumped me. Just check the version from the command line.

-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 9/2/04 10:13 AM
Subject: RE: 4.1.4 still reports itself as 4.1.3b-beta

Further to last message, I've found that string in three different
places 
in the registry:

HKEY_LOCAL_MACHINE\system\controlset001\services\MySQL\ImagePath\

HKEY_LOCAL_MACHINE\system\controlset003\services\MySQL\ImagePath\

HKEY_LOCAL_MACHINE\system\currentcontrolset\services\MySQL\ImagePath\


Never having had to fiddle with the registry much in the past, can I 
presume that if I ignore the first two and change the third one to read 
'mysqld' instead of 'mysqld-opt', save and reboot, I should get the
right 
one started? As you can tell, I'm a little wary of this.

Cheers
Terry

- Original Message -

> In the registry and/or the windows service utility see which
executable 
> is
> actually in the path. 
> 
> -Original Message-
> From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]
> Sent: 9/2/04 9:19 AM
> Subject: Re: 4.1.4 still reports itself as 4.1.3b-beta
> 
> Oh, yes, certainly, Egor. I stopped it before doing the upgrade, and 
> restarted afterwards. And have restarted several times since, because
I 
> couldn't believe it!
> 
> The majority of the files now in the c:\mysql directory have creation 
> dates on or about 27 August, the documentation is for 4.1.4gamma, so
it 
> does look as though the upgrade went through, as far as copying files
is
> 
> concerned.
> 
> Just restarted again with no change (just in case). Any other clues?
> 
> Terry
> 
> - Original Message -
> 
> > Terry Riley <[EMAIL PROTECTED]> wrote:
> > > I have tried several instals (Windows XP), using binaries from 
> > > several of the mirrors, over a 4.1.3b-beta-log version. > > No
> errors 
> > > > > reported during any install, but it still reports itself as
the 
> > > 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12)
and
> 
> > > at the command line opening of mysql.
> > > 
> > > Have I done something stupid, or are others seeing this, too?
> > 
> > First thing to check: have you stopped mysql service and started it 
> > again? 
> > 
> > 



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

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

RE: 4.1.4 still reports itself as 4.1.3b-beta

[Ian Gibbons]

On 2 Sep 2004 at 16:01, Terry Riley wrote:

> Victor
> 
> C:\MYSQL\BIN\mysqld-opt --defaults-file="C:\WINDOWS\my.ini" MySql
> 
> is what the path-to-executable states. If I remember rightly, the new 
> version should be mysqld, period. Is that what's wrong?
> 
> Will I have to tweak the registry to change that?

You can uninstall the service my running this from a prompt:

 C:\MYSQL\BIN\mysqld-opt -remove

Then install the new binary:

 C:\MYSQL\BIN\mysqld -install

For a full list of command line options run this:

 C:\MYSQL\BIN\mysqld --help --verbose > help.txt

then read help.txt


Hope this helps

If not try this (joking):

http://foldoc.doc.ic.ac.uk/foldoc/foldoc.cgi?RTFM

Ian
-- 

Re: 4.1.4 still reports itself as 4.1.3b-beta

[Terry Riley]

Problem solved!

Changing the CurrentControlSet MySQL ImagePath from 'mysqld-opt' to 
'mysqld' now brings up the correct (or rather, the expected) version.

On reflection, perhaps I didn't shut down MySQL before running the setup 
for the gamma version, and that is why it continued to use the older 
beta, though I thought I had. (FX: talks to self - 'must read the 
instructions *first* next time').

Thanks for your help, guys. Running the 'mysqld --version ' on the command 
line convinced me that I had actually installed it.

Cheers
Terry

- Original Message -

> I have tried several instals (Windows XP), using binaries from several 
> of the mirrors, over a 4.1.3b-beta-log version. 
> 
> No errors reported during any install, but it still reports itself as 
> the 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12) 
> and at the command line opening of mysql.
> 
> Have I done something stupid, or are others seeing this, too?
> 
> Terry Riley
> 



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

DELETE FROM mysql.user?

[Mark C. Stafford]

Should I care that USAGE still shows up after all rights have been
revoked? It feels like a potential security hole...



-- working around the system to ensure a clean environment
-- i feel like i shouldn't have to do this...but want to start with a
clean slate
DELETE FROM mysql.user WHERE user = 'jdoe';
DELETE FROM mysql.columns_priv WHERE user = 'jdoe';
DELETE FROM mysql.tables_priv WHERE user = 'jdoe';
DELETE FROM mysql.db WHERE user = 'jdoe';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'jdoe'@'192.168.%';
/*
[localhost -- root] ERROR 1141: There is no such grant defined for
user 'jdoe' on host '192.168.%'
*/

-- simulating new user
GRANT SELECT ON test.* TO 'jdoe'@'192.168.%' IDENTIFIED BY 'still-got-usage';
GRANT INSERT ON test.* TO 'jdoe'@'192.168.%';
GRANT UPDATE ON test.* TO 'jdoe'@'192.168.%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'jdoe'@'192.168.%';
/*
++
| Grants for [EMAIL PROTECTED]   
  |
++
| GRANT USAGE ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD
'4a8930bb6abf3967' |
| GRANT SELECT, INSERT ON `test`.* TO 'jdoe'@'192.168.%'  
  |
++
*/

-- simulating user's departure from organization
REVOKE SELECT ON test.* FROM 'jdoe'@'192.168.%';
REVOKE INSERT ON test.* FROM 'jdoe'@'192.168.%';
REVOKE UPDATE ON test.* FROM 'jdoe'@'192.168.%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'jdoe'@'192.168.%';
/*
++
| Grants for [EMAIL PROTECTED]   
  |
++
| GRANT USAGE ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD
'4a8930bb6abf3967' |
++
*/

--try it again, sam
REVOKE USAGE ON *.* FROM 'jdoe'@'192.168.%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'jdoe'@'192.168.%';
/*
++
| Grants for [EMAIL PROTECTED]   
  |
++
| GRANT USAGE ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD
'4a8930bb6abf3967' |
++
*/

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

"Duplicate entry" breaks replication

[Alex Greg]

Hi,
We recently moved to new servers, and also upgraded from MySQL 3.23 to 
4.0.20. We have one master database server and a second identical 
machine configured to replicate all databases from the master. 
Previously this has worked without a hitch, but we seem to be having 
some problems.

The way we did the transition was as follows:
1) On old database server, issued "FLUSH TABLES WITH READ LOCK;" command
2) Rsynced the data directory onto the new server (db1)
3) Locked the new database server
4) Rsynced the data directory onto the new server (db2)
5) Configured replication and started it up
This works, but we keep getting errors like "Error 'Duplicate entry 
'53751556' for key 1' on query. Default database: 'archive'." I know 
what this means, but how come we are getting them if the two servers 
started off with an identical snapshot of data.

Also, should issuing an insert that fails in this nature break replication?
Regards,
-- Alex
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Backing up directly to tape.

[Tucker, Gabriel]

Hello All

I have been searching the archives and was unable to find an answer.

I need the ability to backup MySQL instances directly to a tape device.

Currently, I run a mysqldump to disk and have legato pick up the file.  As I get to 
some larger databases, hundred's of gigs, and higher transaction rates, I will need an 
online solution that goes directly to a tape device.  Currently we are using MySQL 
table types, though this may change.

Thanks - Gabe

<><><><><><><><><><><><><><><><><><><><><>
"There are no problems, only solutions."

Gabe Tucker
Bloomberg LP
(609) 750 6668 - P
(646) 268 5681 - F

<><><><><><><><><><><><><><><><><><><><><>


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

RE: "Duplicate entry" breaks replication

[Victor Pendleton]

Did you actually stop all writes to the master data while you were copying
the data over to the slave database? Is it possible that the connection you
issued the read lock with  timed out before the transfer was completed?

-Original Message-
From: Alex Greg
To: [EMAIL PROTECTED]
Sent: 9/2/04 10:56 AM
Subject: "Duplicate entry" breaks replication

Hi,


We recently moved to new servers, and also upgraded from MySQL 3.23 to 
4.0.20. We have one master database server and a second identical 
machine configured to replicate all databases from the master. 
Previously this has worked without a hitch, but we seem to be having 
some problems.

The way we did the transition was as follows:

1) On old database server, issued "FLUSH TABLES WITH READ LOCK;" command
2) Rsynced the data directory onto the new server (db1)
3) Locked the new database server
4) Rsynced the data directory onto the new server (db2)
5) Configured replication and started it up

This works, but we keep getting errors like "Error 'Duplicate entry 
'53751556' for key 1' on query. Default database: 'archive'." I know 
what this means, but how come we are getting them if the two servers 
started off with an identical snapshot of data.

Also, should issuing an insert that fails in this nature break
replication?


Regards,


-- Alex

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

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

Re: After I went searching

[Stuart Felenstein]

The query works fine on it's own, but generates a SQL
error in my page:

SELECT *
FROM MemberRez
WHERE LurkID=colname (I've tried it without this line)
ORDER BY andnext asc limit 1,1

andnext is the pk auto inc.

Page error:
You have an error in your SQL syntax. Check the manual
that corresponds to your MySQL server version for the
right syntax to use near 'LIMIT -1' at line 1

Maybe formatting ?
Stuart




--- Stuart Felenstein <[EMAIL PROTECTED]> wrote:

> Wait!! Don't answer...it's that LIMIT
> thing.
> 
> 
> :)
> Stuart
> 
> 
> --- Stuart Felenstein <[EMAIL PROTECTED]> wrote:
> 
> > I'm tring to extract all records belonging to one
> ID
> > in a table. 
> > BUT! I want to do it in piece meal.  Meaning a
> > record
> > at a time.  
> > I am assuming I need some kind of count mechanism.
> > 
> > Example: 
> > I want to see the first 3 purchases this member
> > made.
> > Instead of grabbing them all in one shot, I want
> to
> > break  those 3 out in 3 seperate results set
> > 
> > Results Set1: Purchase 1
> > Results Set2: Purchase 2
> > Results Set3: Purchase 3
> > 
> > This is probably easy.  
> > 
> > Thank you,
> > Stuart
> > 
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:   
> >
>
http://lists.mysql.com/[EMAIL PROTECTED]
> > 
> > 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


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

Re: bad "too many connections" error (os x)

[Michael Winston]

On Sep 2, 2004, at 6:04 AM, Egor Egorov wrote:
Michael Winston <[EMAIL PROTECTED]> wrote:
Okay, so the first thing to try is obviously enlarge the 
max_connections.
Have you tried this?
Yes.  It's set to 400 (a number we will never reach unless there's some 
sort of logjam).  max_connect_errors is set to 200.

Is it a webserver backend database?
Yes.  PHP-generated pages.
Thanks,
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: DELETE FROM mysql.user?

[V. M. Brasseur]

"USAGE" is a synonym for "no privileges."  Yes, it's kind of a confusing 
term which implies "this user can use the system" when in fact the 
situation is exactly the opposite.

http://dev.mysql.com/doc/mysql/en/User_resources.html
Cheers,
--V
Mark C. Stafford wrote:
Should I care that USAGE still shows up after all rights have been
revoked? It feels like a potential security hole...

-- working around the system to ensure a clean environment
-- i feel like i shouldn't have to do this...but want to start with a
clean slate
DELETE FROM mysql.user WHERE user = 'jdoe';
DELETE FROM mysql.columns_priv WHERE user = 'jdoe';
DELETE FROM mysql.tables_priv WHERE user = 'jdoe';
DELETE FROM mysql.db WHERE user = 'jdoe';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'jdoe'@'192.168.%';
/*
[localhost -- root] ERROR 1141: There is no such grant defined for
user 'jdoe' on host '192.168.%'
*/
-- simulating new user
GRANT SELECT ON test.* TO 'jdoe'@'192.168.%' IDENTIFIED BY 'still-got-usage';
GRANT INSERT ON test.* TO 'jdoe'@'192.168.%';
GRANT UPDATE ON test.* TO 'jdoe'@'192.168.%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'jdoe'@'192.168.%';
/*
++
| Grants for [EMAIL PROTECTED]   
  |
++
| GRANT USAGE ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD
'4a8930bb6abf3967' |
| GRANT SELECT, INSERT ON `test`.* TO 'jdoe'@'192.168.%'  
  |
++
*/

-- simulating user's departure from organization
REVOKE SELECT ON test.* FROM 'jdoe'@'192.168.%';
REVOKE INSERT ON test.* FROM 'jdoe'@'192.168.%';
REVOKE UPDATE ON test.* FROM 'jdoe'@'192.168.%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'jdoe'@'192.168.%';
/*
++
| Grants for [EMAIL PROTECTED]   
  |
++
| GRANT USAGE ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD
'4a8930bb6abf3967' |
++
*/

--try it again, sam
REVOKE USAGE ON *.* FROM 'jdoe'@'192.168.%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'jdoe'@'192.168.%';
/*
++
| Grants for [EMAIL PROTECTED]   
  |
++
| GRANT USAGE ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD
'4a8930bb6abf3967' |
++
*/

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

Re: DELETE FROM mysql.user?

[Paul DuBois]

At 9:54 -0600 9/2/04, Mark C. Stafford wrote:
Should I care that USAGE still shows up after all rights have been
revoked? It feels like a potential security hole...
USAGE *means* "no privileges", that's why it shows up when all privs
have been revoked.
It does mean that the account can be used to connect to the server.
If you don't want that, either delete the record in the user table
and FLUSH PRIVILEGES, or if you have MySQL 4.1.1 or later, you can
use DROP USER.

-- working around the system to ensure a clean environment
-- i feel like i shouldn't have to do this...but want to start with a
clean slate
DELETE FROM mysql.user WHERE user = 'jdoe';
DELETE FROM mysql.columns_priv WHERE user = 'jdoe';
DELETE FROM mysql.tables_priv WHERE user = 'jdoe';
DELETE FROM mysql.db WHERE user = 'jdoe';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'jdoe'@'192.168.%';
/*
[localhost -- root] ERROR 1141: There is no such grant defined for
user 'jdoe' on host '192.168.%'
*/
-- simulating new user
GRANT SELECT ON test.* TO 'jdoe'@'192.168.%' IDENTIFIED BY 'still-got-usage';
GRANT INSERT ON test.* TO 'jdoe'@'192.168.%';
GRANT UPDATE ON test.* TO 'jdoe'@'192.168.%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'jdoe'@'192.168.%';
/*
++
| Grants for [EMAIL PROTECTED]  
  |
++
| GRANT USAGE ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD
'4a8930bb6abf3967' |
| GRANT SELECT, INSERT ON `test`.* TO 'jdoe'@'192.168.%' 
  |
++
*/

-- simulating user's departure from organization
REVOKE SELECT ON test.* FROM 'jdoe'@'192.168.%';
REVOKE INSERT ON test.* FROM 'jdoe'@'192.168.%';
REVOKE UPDATE ON test.* FROM 'jdoe'@'192.168.%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'jdoe'@'192.168.%';
/*
++
| Grants for [EMAIL PROTECTED]  
  |
++
| GRANT USAGE ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD
'4a8930bb6abf3967' |
++
*/

--try it again, sam
REVOKE USAGE ON *.* FROM 'jdoe'@'192.168.%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'jdoe'@'192.168.%';
/*
++
| Grants for [EMAIL PROTECTED]  
  |
++
| GRANT USAGE ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD
'4a8930bb6abf3967' |
++
*/

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: DELETE FROM mysql.user?

[Mark C. Stafford]

Thank you both.

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

Re: After I went searching

[Rhino]

Formatting shouldn't be an issue if you entered the query exactly as you
said you did. However, I'm a little dubious that you did; the error message
seems to be saying that you have a negative sign in front of the first 1 in
the Limit clause.

Check your typing and try the query again. Also, remember that the row
numbers used by the Limit clause start with 0, not with 1. In other words,
you may want to try "Limit 0,1", not "Limit 1,1".

Rhino

- Original Message - 
From: "Stuart Felenstein" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, September 02, 2004 12:18 PM
Subject: Re: After I went searching


> The query works fine on it's own, but generates a SQL
> error in my page:
>
> SELECT *
> FROM MemberRez
> WHERE LurkID=colname (I've tried it without this line)
> ORDER BY andnext asc limit 1,1
>
> andnext is the pk auto inc.
>
> Page error:
> You have an error in your SQL syntax. Check the manual
> that corresponds to your MySQL server version for the
> right syntax to use near 'LIMIT -1' at line 1
>
> Maybe formatting ?
> Stuart
>
>
>
>
> --- Stuart Felenstein <[EMAIL PROTECTED]> wrote:
>
> > Wait!! Don't answer...it's that LIMIT
> > thing.
> >
> >
> > :)
> > Stuart
> >
> >
> > --- Stuart Felenstein <[EMAIL PROTECTED]> wrote:
> >
> > > I'm tring to extract all records belonging to one
> > ID
> > > in a table.
> > > BUT! I want to do it in piece meal.  Meaning a
> > > record
> > > at a time.
> > > I am assuming I need some kind of count mechanism.
> > >
> > > Example:
> > > I want to see the first 3 purchases this member
> > > made.
> > > Instead of grabbing them all in one shot, I want
> > to
> > > break  those 3 out in 3 seperate results set
> > >
> > > Results Set1: Purchase 1
> > > Results Set2: Purchase 2
> > > Results Set3: Purchase 3
> > >
> > > This is probably easy.
> > >
> > > Thank you,
> > > Stuart
> > >
> > > -- 
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > >
> >
> http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> > >
> >
> >
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> >
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


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

Re: After I went searching

[Stuart Felenstein]

Yep, understand about the 0 and checked the code no
-1's.  All is weird, but here is a situation that in
the back of my mind I keep thinking maybe I need a
many to many.  The problem is I "again" can't quite
conceptualize it.  If I'm making a rehash again , I
really apologize.
Here is the situation:

I have a table that hold resumes , each member can
store 3 resumes.
the table: 
ResTable(myisam)
AndNextID - (auto inc) just really a counter and PK
MemberID - 
Resume Title - 
Resume - 

So if they hit their three a trigger which does a
count on their records by their ID returns an error
message.
The problem I'm running into now is on updating.  I'm
really trying not to confuse app logic and mechanics
with the database. So I'll cut to the quick.

ResTable1
...(same fields as above)
ResTable2
(yadda yadda)

Member_ResTable
Member (well actually not sure what would be in here)

Shawn, if your out there - I'm not ignoring your
advice to stave off the coding for now , concentrate
on the db design. I just haven't come to grips with it
yet! LOL

Stuart

Would there be a benefit in a:

--- Rhino <[EMAIL PROTECTED]> wrote:

> Formatting shouldn't be an issue if you entered the
> query exactly as you
> said you did. However, I'm a little dubious that you
> did; the error message
> seems to be saying that you have a negative sign in
> front of the first 1 in
> the Limit clause.
> 
> Check your typing and try the query again. Also,
> remember that the row
> numbers used by the Limit clause start with 0, not
> with 1. In other words,
> you may want to try "Limit 0,1", not "Limit 1,1".
> 
> Rhino
> 
> - Original Message - 
> From: "Stuart Felenstein" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Thursday, September 02, 2004 12:18 PM
> Subject: Re: After I went searching
> 
> 
> > The query works fine on it's own, but generates a
> SQL
> > error in my page:
> >
> > SELECT *
> > FROM MemberRez
> > WHERE LurkID=colname (I've tried it without this
> line)
> > ORDER BY andnext asc limit 1,1
> >
> > andnext is the pk auto inc.
> >
> > Page error:
> > You have an error in your SQL syntax. Check the
> manual
> > that corresponds to your MySQL server version for
> the
> > right syntax to use near 'LIMIT -1' at line 1
> >
> > Maybe formatting ?
> > Stuart
> >
> >
> >
> >
> > --- Stuart Felenstein <[EMAIL PROTECTED]> wrote:
> >
> > > Wait!! Don't answer...it's that
> LIMIT
> > > thing.
> > >
> > >
> > > :)
> > > Stuart
> > >
> > >
> > > --- Stuart Felenstein <[EMAIL PROTECTED]>
> wrote:
> > >
> > > > I'm tring to extract all records belonging to
> one
> > > ID
> > > > in a table.
> > > > BUT! I want to do it in piece meal.  Meaning a
> > > > record
> > > > at a time.
> > > > I am assuming I need some kind of count
> mechanism.
> > > >
> > > > Example:
> > > > I want to see the first 3 purchases this
> member
> > > > made.
> > > > Instead of grabbing them all in one shot, I
> want
> > > to
> > > > break  those 3 out in 3 seperate results set
> > > >
> > > > Results Set1: Purchase 1
> > > > Results Set2: Purchase 2
> > > > Results Set3: Purchase 3
> > > >
> > > > This is probably easy.
> > > >
> > > > Thank you,
> > > > Stuart
> > > >
> > > > -- 
> > > > MySQL General Mailing List
> > > > For list archives:
> http://lists.mysql.com/mysql
> > > > To unsubscribe:
> > > >
> > >
> >
>
http://lists.mysql.com/[EMAIL PROTECTED]
> > > >
> > > >
> > >
> > >
> > > -- 
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > >
> >
>
http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> > >
> >
> >
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> >
> 
> 


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

RE: 4.1.4 still reports itself as 4.1.3b-beta

[Terry Riley]

All sorted now, Ian.

- Original Message -
> Hope this helps
> 
> If not try this (joking):
> 
> http://foldoc.doc.ic.ac.uk/foldoc/foldoc.cgi?RTFM
> 
> Ian
> -- 


That's what was missing. LOL

> 



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

upgrading from 4.0.20 to 4.1.4 on windows

[Massimo Petrini]

A little question on my slave upgrade.
In the log is see as an error whne my slave is connecting to my master. See
below
Is all normal ?
Meanwhile I run the fix suggested for password .

Thanks
Massimo

---
InnoDB: Resetting space id's in the doublewrite buffer
040902 17:50:08  InnoDB: Started; log sequence number 14 660630550
InnoDB: You are upgrading to an InnoDB version which allows multiple
InnoDB: tablespaces. Wait that purge and insert buffer merge run to
InnoDB: completion...
InnoDB: Full purge and insert buffer merge completed.
InnoDB: You have now successfully upgraded to the multiple tablespaces
InnoDB: format. You should NOT DOWNGRADE again to an earlier version of
InnoDB: InnoDB! But if you absolutely need to downgrade, see section 4.6 of
InnoDB: http://www.innodb.com/ibman.php for instructions.
040902 17:50:10  [ERROR] mysql.user table is not updated to new password
format; Disabling new password usage until mysql_fix_privilege_tables is run
040902 17:50:10  [ERROR] Warning: Can't open and lock time zone table: Table
'mysql.time_zone_leap_second' doesn't exist trying to live without them
MySql: ready for connections.
Version: '4.1.4-gamma-nt-max'  socket: ''  port: 3306  Source distribution
040902 17:50:10  [ERROR] Slave SQL thread initialized, starting replication
in log 'seurat-bin.155' at position 291988391, relay log
'.\dirsta-relay-bin.040' position: 169022558
040902 17:50:10  [ERROR] Slave I/O thread: connected to master
'[EMAIL PROTECTED]:3306',  replication started in log 'seurat-bin.155' at position
291988391
040902 18:11:43  [ERROR] MySql: Normal shutdown

040902 18:11:43  [ERROR] Error reading packet from server: Lost connection
to MySQL server during query (server_errno=2013)
040902 18:11:43  [ERROR] Slave I/O thread killed while reading event
040902 18:11:43  [ERROR] Slave I/O thread exiting, read up to log
'seurat-bin.155', position 292423012
040902 18:11:43  [ERROR] Error reading relay log event: slave SQL thread was
killed
040902 18:11:44  [ERROR] MySql: Forcing close of thread 13  user: 'root'

040902 18:11:44  InnoDB: Starting shutdown...
040902 18:11:46  InnoDB: Shutdown completed; log sequence number 14
660659733
040902 18:11:46  [ERROR] MySql: Shutdown complete

040902 18:11:52  InnoDB: Started; log sequence number 14 660659733
MySql: ready for connections.
Version: '4.1.4-gamma-nt-max'  socket: ''  port: 3306  Source distribution
040902 18:11:52  [ERROR] Slave SQL thread initialized, starting replication
in log 'seurat-bin.155' at position 292423012, relay log
'.\dirsta-relay-bin.41' position: 434666
040902 18:11:52  [ERROR] Slave I/O thread: connected to master
'[EMAIL PROTECTED]:3306',  replication started in log 'seurat-bin.155' at position
292423012

-
Massimo Petrini
c/o Omt spa
Via Ferrero 67/a
10090 Cascine Vica (TO)
Tel.+39 011 9505334
Fax +39 011 9575474
E-mail  [EMAIL PROTECTED]


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

Re: After I went searching

[SGreen]

 -- yep, I'm hanging around ;-)

You don't have a many-to-many relationship between resumes and members. 
It's a one-to-many (each member can have multiple resumes but each resume 
belongs specifically to only 1 member)

That's done with a single table like this (not 3 different resume tables):

CREATE TABLE resume (
ID int auto_increment not null,
member_ID int,
...more fields to hold the resume content and metadata ...
)

The member_ID can appear multiple times in the resume table but each 
resume is limited to only 1 member_id (one-to-many).

Before you even allow a user to attempt an insert a new resume into your 
system, check to see if they have already met their limit:

SELECT member_ID, count(1) as rescount
FROM resume
WHERE member_ID = ###  <-- put a real number/string there
GROUP BY member_ID;

And compare the results of the 'rescount' column to your business rule 
(some day you may allow 6 resumes, who knows?). This comparison happens in 
your application code, not the database, as you need to decide whether to 
give the user the screen to enter another resume or to send the user a 
screen that says "So sorry but you already have your limit of  resumes on 
file".

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--8< prior responses mangled for space ---8<--

Stuart Felenstein <[EMAIL PROTECTED]> wrote on 09/02/2004 01:05:31 PM:

> I have a table that hold resumes , each member can
> store 3 resumes.
--8<--
> ResTable1
> ...(same fields as above)
> ResTable2
> (yadda yadda)
> 
> Member_ResTable
> Member (well actually not sure what would be in here)
> 
> Shawn, if your out there - I'm not ignoring your
> advice to stave off the coding for now , concentrate
> on the db design. I just haven't come to grips with it
> yet! LOL
> 
> Stuart
--8<--

Re: Query hangs mysql 4.1

[Heikki Tuuri]

Mauricio,

please send the FULL .err log to me. Do not cut anything off.

[EMAIL PROTECTED]

Best regards,

Heikki
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

Order MySQL support from http://www.mysql.com/support/index.html


Hi,
I've experienced a hang after running a query wich is run usually 2 to 3
times a day without a problem till now.

This is what the error log reports

nnoDB: Thread 4784139 stopped in file btr0pcur.c line 205
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbe3fddb8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8108af7
0x4014c895
0x8280a88
0x82630ba
0x82694eb
0x82697a1
0x82818f0
0x8282495
0x828267a
0x826b7bc
0x817abf3
0x814edf1
0x814ef81
0x81401e2
0x8137049
0x8137585
0x814e061
0x8118164
0x811b121
0x811552f
0x8114ec2
0x8114637
0x40146c60
0x402e9b77
New value of fp=(nil) failed sanity check, terminating stack trace!

Also have part of the query in the log. and some other warnings about
the memory bee used acording to the defined variables.

I've used resolve_stack_dump -s /usr/lib/mysql/mysqld-max.sym -n
mysqld.stack
as instructed in the manual, with the following result

0x8108af7 handle_segfault + 423
0x4014c895 _end + 934924901
0x8280a88 row_upd_build_sec_rec_difference_binary + 408
0x82630ba row_ins_sec_index_entry_by_modify + 90
0x82694eb row_ins_index_entry_low + 2859
0x82697a1 row_ins_index_entry + 65
0x82818f0 row_upd_sec_index_entry + 848
0x8282495 row_upd + 197
0x828267a row_upd_step + 282
0x826b7bc row_update_for_mysql + 700
0x817abf3 update_row__11ha_innobasePCcPc + 291
0x814edf1 do_updates__12multi_updateb + 465
0x814ef81 send_eof__12multi_update + 49
0x81401e2 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 530
0x8137049 exec__4JOIN + 4185
0x8137585
mysql_select__FP3THDPPP4ItemP13st_table_listUiRt4List1Z4ItemP4ItemUiP8st_ord
erT7T5T7Ul \
P13select_resultP18st_select_lex_unitP13s + 837 0x814e061
mysql_multi_update__FP3THDP13st_table_listPt4List1Z4ItemT2P4ItemUl15enum_dup
licatesP18 \
st_select_lex_unitP13st_select_lex + 369 0x8118164
mysql_execute_command__FP3THD + \
7780 0x811b121 mysql_parse__FP3THDPcUi + 177
0x811552f dispatch_command__F19enum_server_commandP3THDPcUi + 1631
0x8114ec2 do_command__FP3THD + 162
0x8114637 handle_one_connection + 551
0x40146c60 _end + 934901296
0x402e9b77 _end + 936617287

Watching those entries in the error log I cant figure why is this
happening. Can anybody give me a hint?

Thank you
Mauricio


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

Compatibility of data files across platforms

[Alex]

Hi list,
  In general, is there any guarantee that the .frm and innodb files
(including logs) will be exactly the same cross-platform?  More
specifically, would the database data files created on Windows work when 
placed on an OSX installation of the same version?

 Assume whatever version of mysql you are familiar with, although if it
matters I'm using 4.0.20. The same configuration for each platforms'
installation.
  My intent is to be able to distribute a pre-populated database on
multiple platforms. It is desirable to conserve disk space by sharing
the data files amongst distributions if possible.  I've tested it and it
appears to work.  This is preferable to using mysqldump and importing 
when the typical end-user would have difficulty accomplishing that.

Thanks.



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

Re: Problem on InnoDB - Tablespace enough but engine said table full

[Heikki Tuuri]

Ady,

InnoDB thinks that the tablespace size is 10 706 MB.

You have specified 36 782 MB of data files in the my.cnf line :(.

Now you should figure out what are the data files that InnoDB is using, and
remove the end of the innodb_data_file_path line, as well as the unused
ibdata files. Remember that InnoDB uses data files starting from the first,
in the order that you list them in innodb_data_file_path.

Be very careful! Do not remove your valuable data!

After that, you can grow the tablespace as instructed at
http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html

Best regards,

Heikki
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

Order MySQL support from http://www.mysql.com/support/index.html

.

I have MySQL for heavy duty job .

here is my InnoDB table space definition


innodb_data_file_path = \
/data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15
00M;/data0 \
/ibdata5:1500M;/
data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ \
ibdata9:1500M;/data4/ibdata25:1802M;/data3/ibdata25:1802M \
;/data4/ibdata25.data3:576M;/data1/ibdata10:1500M;/data1/ibdata11:1500M;/dat
a1/ibdata1 \
2:1500M;/data1/ibdata13:1500M;/data2/ibd \
ata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16:1500M;/data2/ibdata17:1500
M;/data2/i \
bdata18:1500M;/data2/ibdata19:1500M;/dat \
a3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ibdata22:1500M;/data3/ibdata2
3:1500M;/d \
ata3/ibdata24:1500M;


What i don't understand is :

after ibdata1 -> ibdata9 full is go to /data4/ibdata25 ... (1802Mbyte)

But it's not continues to write to /data4/ibdata25.data3... /data1/ibdata10
and so on

but said table  is full when i try to write data to table

The question is why ?

Secondly



What is the meaning of

InnoDB: Error: tablespace size stored in header is 685184 pages, but
InnoDB: the sum of data file sizes is 2354048 pages


Thx


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

Re: After I went searching

[Peter Brawley]

Stuart,

If one member may have a max of three resumes, then the relationship is
one-many with cardinality=3, implementable with a foreign key in resumes (eg
resumes.memberID). MySQL doesn't have tools for implementing cardinality
constraints, so you would have to do that in application code, ie grey out
the add-a-resume button for members whose resume count exceeds 3.

PB
  - Original Message -
  From: Stuart Felenstein
  To: Rhino ; [EMAIL PROTECTED]
  Sent: Thursday, September 02, 2004 12:05 PM
  Subject: Re: After I went searching


  Yep, understand about the 0 and checked the code no
  -1's.  All is weird, but here is a situation that in
  the back of my mind I keep thinking maybe I need a
  many to many.  The problem is I "again" can't quite
  conceptualize it.  If I'm making a rehash again , I
  really apologize.
  Here is the situation:

  I have a table that hold resumes , each member can
  store 3 resumes.
  the table:
  ResTable(myisam)
  AndNextID - (auto inc) just really a counter and PK
  MemberID -
  Resume Title -
  Resume -

  So if they hit their three a trigger which does a
  count on their records by their ID returns an error
  message.
  The problem I'm running into now is on updating.  I'm
  really trying not to confuse app logic and mechanics
  with the database. So I'll cut to the quick.

  ResTable1
  ...(same fields as above)
  ResTable2
  (yadda yadda)

  Member_ResTable
  Member (well actually not sure what would be in here)

  Shawn, if your out there - I'm not ignoring your
  advice to stave off the coding for now , concentrate
  on the db design. I just haven't come to grips with it
  yet! LOL

  Stuart

  Would there be a benefit in a:

  --- Rhino <[EMAIL PROTECTED]> wrote:

  > Formatting shouldn't be an issue if you entered the
  > query exactly as you
  > said you did. However, I'm a little dubious that you
  > did; the error message
  > seems to be saying that you have a negative sign in
  > front of the first 1 in
  > the Limit clause.
  >
  > Check your typing and try the query again. Also,
  > remember that the row
  > numbers used by the Limit clause start with 0, not
  > with 1. In other words,
  > you may want to try "Limit 0,1", not "Limit 1,1".
  >
  > Rhino
  >
  > - Original Message -
  > From: "Stuart Felenstein" <[EMAIL PROTECTED]>
  > To: <[EMAIL PROTECTED]>
  > Sent: Thursday, September 02, 2004 12:18 PM
  > Subject: Re: After I went searching
  >
  >
  > > The query works fine on it's own, but generates a
  > SQL
  > > error in my page:
  > >
  > > SELECT *
  > > FROM MemberRez
  > > WHERE LurkID=colname (I've tried it without this
  > line)
  > > ORDER BY andnext asc limit 1,1
  > >
  > > andnext is the pk auto inc.
  > >
  > > Page error:
  > > You have an error in your SQL syntax. Check the
  > manual
  > > that corresponds to your MySQL server version for
  > the
  > > right syntax to use near 'LIMIT -1' at line 1
  > >
  > > Maybe formatting ?
  > > Stuart
  > >
  > >
  > >
  > >
  > > --- Stuart Felenstein <[EMAIL PROTECTED]> wrote:
  > >
  > > > Wait!! Don't answer...it's that
  > LIMIT
  > > > thing.
  > > >
  > > >
  > > > :)
  > > > Stuart
  > > >
  > > >
  > > > --- Stuart Felenstein <[EMAIL PROTECTED]>
  > wrote:
  > > >
  > > > > I'm tring to extract all records belonging to
  > one
  > > > ID
  > > > > in a table.
  > > > > BUT! I want to do it in piece meal.  Meaning a
  > > > > record
  > > > > at a time.
  > > > > I am assuming I need some kind of count
  > mechanism.
  > > > >
  > > > > Example:
  > > > > I want to see the first 3 purchases this
  > member
  > > > > made.
  > > > > Instead of grabbing them all in one shot, I
  > want
  > > > to
  > > > > break  those 3 out in 3 seperate results set
  > > > >
  > > > > Results Set1: Purchase 1
  > > > > Results Set2: Purchase 2
  > > > > Results Set3: Purchase 3
  > > > >
  > > > > This is probably easy.
  > > > >
  > > > > Thank you,
  > > > > Stuart
  > > > >
  > > > > --
  > > > > MySQL General Mailing List
  > > > > For list archives:
  > http://lists.mysql.com/mysql
  > > > > To unsubscribe:
  > > > >
  > > >
  > >
  >
  http://lists.mysql.com/[EMAIL PROTECTED]
  > > > >
  > > > >
  > > >
  > > >
  > > > --
  > > > MySQL General Mailing List
  > > > For list archives: http://lists.mysql.com/mysql
  > > > To unsubscribe:
  > > >
  > >
  >
  http://lists.mysql.com/[EMAIL PROTECTED]
  > > >
  > > >
  > >
  > >
  > > --
  > > MySQL General Mailing List
  > > For list archives: http://lists.mysql.com/mysql
  > > To unsubscribe:
  >
  http://lists.mysql.com/[EMAIL PROTECTED]
  > >
  >
  >


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

Re: Compatibility of data files across platforms

[Heikki Tuuri]

Alex,

since the floating point format in all the common architectures is the same
nowadays, you can freely copy data files and .frm files across platforms.

To avoid problems in table name case, use always lower case table names and
database names:

http://dev.mysql.com/doc/mysql/en/Moving.html
"
On Windows, InnoDB internally always stores database and table names in
lowercase. To move databases in a binary format from Unix to Windows or from
Windows to Unix, you should have all table and database names in lowercase.
A convenient way to accomplish this on Unix is to add the following line to
the [mysqld] section of your `my.cnf' before you start creating your
databases and tables:

[mysqld]
set-variable = lower_case_table_names=1

On Windows, lower_case_table_names is set to 1 by default.

Like MyISAM data files, InnoDB data and log files are binary-compatible on
all platforms if the floating-point number format on the machines is the
same. You can move an InnoDB database simply by copying all the relevant
files, which were listed in section 16.9 Backing Up and Recovering an InnoDB
Database.
"

Best regards,

Heikki
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

Order MySQL support from http://www.mysql.com/support/index.html


..
Hi list,

   In general, is there any guarantee that the .frm and innodb files
(including logs) will be exactly the same cross-platform?  More
specifically, would the database data files created on Windows work when
placed on an OSX installation of the same version?

  Assume whatever version of mysql you are familiar with, although if it
matters I'm using 4.0.20. The same configuration for each platforms'
installation.

   My intent is to be able to distribute a pre-populated database on
multiple platforms. It is desirable to conserve disk space by sharing
the data files amongst distributions if possible.  I've tested it and it
appears to work.  This is preferable to using mysqldump and importing
when the typical end-user would have difficulty accomplishing that.

Thanks.


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

Re: MySQL server crashed with the following error message

[Heikki Tuuri]

Pandu,

this might be the '4 billion bug' that is fixed in upcoming 4.0.21.

Best regards,

Heikki
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

Order MySQL support from http://www.mysql.com/support/index.html

..
Hi,

Yesterday, our production MySQL server died with the following error
message. Please let us know if there are any patches for this.



040830 19:52:13  InnoDB: Assertion failure in thread 27156583 in file
mem0pool.c line 493

InnoDB: Failing assertion: 0

InnoDB: We intentionally generate a memory trap.

InnoDB: Send a detailed bug report to [EMAIL PROTECTED]

Attempting backtrace. You can use the following information to find out

where mysqld died. If you see no messages after this, something went

terribly wrong...

Cannot determine thread, fp=0x877c76d8, backtrace may not be correct.



Number of processes running now: 1





Thanks and Regards,

Pandu R Pabbisetty

Software Architect



Five9

7901 Stoneridge Drive, Suite 200

Pleasanton, CA 94588 USA

925-201-2023 (direct), 925-469-0172 (fax)

[EMAIL PROTECTED]

www.five9.com



The Leader in Hosted VOIP Contact Center Solutions




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

Re: question about innodb_thread_concurrency

[Heikki Tuuri]

Devanada,

normally you do not need to tune this option at all.

I introduced the option when certain Linux binaries showed 'thread
thrashing' at high concurrency. Setting the option to 1 or 2 could in some
cases alleviate the problem.

You can experiment by setting the option to 500 to disable restrictions on
concurrent operations. I am considering changing the default value to 500,
because we have not seen 'thread thrashing' on any platform for a long
while.

Best regards,

Heikki
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

Order MySQL support from http://www.mysql.com/support/index.html

...
Hi,

This is a question about optimization of mysql's InnoDB performance ...
quoting the doc's

 > "The default value is 8. If you have low performance and |SHOW INNODB
STATUS| reveals many threads waiting for semaphores,
 > you may have thread thrashing and should try setting this parameter
lower or higher. The default value is 8. If you have low
 > performance and |SHOW INNODB STATUS| reveals many threads waiting for
semaphores, you may have thread thrashing and
 > should try setting this parameter lower or higher."

What is a reasonable value for a dual Xeon 2.8Ghz with 6 disks? we are
currently running with innodb_thread_concurrency=16. Each DB has 200-400
active php clients, and there appears to be quite a large backup within
innodb, based on this. Any suggestions? Or experience with setting this
option to 500?

Thanks :)

Devananda
Neopets, Inc


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

Re: After I went searching

[Stuart Felenstein]

K ...so I have it set up correctly.  After I posted,
"then" went back and scoured through your previous
responses.  Realized, it's not a many to many.  

I honestly think if I was only doing the database,
life would be a whole lot easier :).  Sorry , that's
humour.

Thank you
Stuart


--- [EMAIL PROTECTED] wrote:

>  -- yep, I'm hanging around ;-)
> 
> You don't have a many-to-many relationship between
> resumes and members. 
> It's a one-to-many (each member can have multiple
> resumes but each resume 
> belongs specifically to only 1 member)
> 
> That's done with a single table like this (not 3
> different resume tables):
> 
> CREATE TABLE resume (
> ID int auto_increment not null,
> member_ID int,
> ...more fields to hold the resume content
> and metadata ...
> )
> 
> The member_ID can appear multiple times in the
> resume table but each 
> resume is limited to only 1 member_id (one-to-many).
> 
> Before you even allow a user to attempt an insert a
> new resume into your 
> system, check to see if they have already met their
> limit:
> 
> SELECT member_ID, count(1) as rescount
> FROM resume
> WHERE member_ID = ###  <-- put a real number/string
> there
> GROUP BY member_ID;
> 
> And compare the results of the 'rescount' column to
> your business rule 
> (some day you may allow 6 resumes, who knows?). This
> comparison happens in 
> your application code, not the database, as you need
> to decide whether to 
> give the user the screen to enter another resume or
> to send the user a 
> screen that says "So sorry but you already have your
> limit of  resumes on 
> file".
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 
> --8< prior responses mangled for space
> ---8<--
> 
> Stuart Felenstein <[EMAIL PROTECTED]> wrote on
> 09/02/2004 01:05:31 PM:
> 
> > I have a table that hold resumes , each member can
> > store 3 resumes.
> --8<--
> > ResTable1
> > ...(same fields as above)
> > ResTable2
> > (yadda yadda)
> > 
> > Member_ResTable
> > Member (well actually not sure what would be in
> here)
> > 
> > Shawn, if your out there - I'm not ignoring your
> > advice to stave off the coding for now ,
> concentrate
> > on the db design. I just haven't come to grips
> with it
> > yet! LOL
> > 
> > Stuart
> --8<--


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

Re: After I went searching

[Stuart Felenstein]

Peter, 

Thank you ! I like that suggestion of graying out the
button!

Stuart
--- Peter  Brawley <[EMAIL PROTECTED]>
wrote:

> Stuart,
> 
> If one member may have a max of three resumes, then
> the relationship is
> one-many with cardinality=3, implementable with a
> foreign key in resumes (eg
> resumes.memberID). MySQL doesn't have tools for
> implementing cardinality
> constraints, so you would have to do that in
> application code, ie grey out
> the add-a-resume button for members whose resume
> count exceeds 3.
> 
> PB
>   - Original Message -
>   From: Stuart Felenstein
>   To: Rhino ; [EMAIL PROTECTED]
>   Sent: Thursday, September 02, 2004 12:05 PM
>   Subject: Re: After I went searching
> 
> 
>   Yep, understand about the 0 and checked the code
> no
>   -1's.  All is weird, but here is a situation that
> in
>   the back of my mind I keep thinking maybe I need a
>   many to many.  The problem is I "again" can't
> quite
>   conceptualize it.  If I'm making a rehash again ,
> I
>   really apologize.
>   Here is the situation:
> 
>   I have a table that hold resumes , each member can
>   store 3 resumes.
>   the table:
>   ResTable(myisam)
>   AndNextID - (auto inc) just really a counter and
> PK
>   MemberID -
>   Resume Title -
>   Resume -
> 
>   So if they hit their three a trigger which does a
>   count on their records by their ID returns an
> error
>   message.
>   The problem I'm running into now is on updating. 
> I'm
>   really trying not to confuse app logic and
> mechanics
>   with the database. So I'll cut to the quick.
> 
>   ResTable1
>   ...(same fields as above)
>   ResTable2
>   (yadda yadda)
> 
>   Member_ResTable
>   Member (well actually not sure what would be in
> here)
> 
>   Shawn, if your out there - I'm not ignoring your
>   advice to stave off the coding for now ,
> concentrate
>   on the db design. I just haven't come to grips
> with it
>   yet! LOL
> 
>   Stuart
> 
>   Would there be a benefit in a:
> 
>   --- Rhino <[EMAIL PROTECTED]> wrote:
> 
>   > Formatting shouldn't be an issue if you entered
> the
>   > query exactly as you
>   > said you did. However, I'm a little dubious that
> you
>   > did; the error message
>   > seems to be saying that you have a negative sign
> in
>   > front of the first 1 in
>   > the Limit clause.
>   >
>   > Check your typing and try the query again. Also,
>   > remember that the row
>   > numbers used by the Limit clause start with 0,
> not
>   > with 1. In other words,
>   > you may want to try "Limit 0,1", not "Limit
> 1,1".
>   >
>   > Rhino
>   >
>   > - Original Message -
>   > From: "Stuart Felenstein" <[EMAIL PROTECTED]>
>   > To: <[EMAIL PROTECTED]>
>   > Sent: Thursday, September 02, 2004 12:18 PM
>   > Subject: Re: After I went searching
>   >
>   >
>   > > The query works fine on it's own, but
> generates a
>   > SQL
>   > > error in my page:
>   > >
>   > > SELECT *
>   > > FROM MemberRez
>   > > WHERE LurkID=colname (I've tried it without
> this
>   > line)
>   > > ORDER BY andnext asc limit 1,1
>   > >
>   > > andnext is the pk auto inc.
>   > >
>   > > Page error:
>   > > You have an error in your SQL syntax. Check
> the
>   > manual
>   > > that corresponds to your MySQL server version
> for
>   > the
>   > > right syntax to use near 'LIMIT -1' at line 1
>   > >
>   > > Maybe formatting ?
>   > > Stuart
>   > >
>   > >
>   > >
>   > >
>   > > --- Stuart Felenstein <[EMAIL PROTECTED]>
> wrote:
>   > >
>   > > > Wait!! Don't answer...it's that
>   > LIMIT
>   > > > thing.
>   > > >
>   > > >
>   > > > :)
>   > > > Stuart
>   > > >
>   > > >
>   > > > --- Stuart Felenstein <[EMAIL PROTECTED]>
>   > wrote:
>   > > >
>   > > > > I'm tring to extract all records belonging
> to
>   > one
>   > > > ID
>   > > > > in a table.
>   > > > > BUT! I want to do it in piece meal. 
> Meaning a
>   > > > > record
>   > > > > at a time.
>   > > > > I am assuming I need some kind of count
>   > mechanism.
>   > > > >
>   > > > > Example:
>   > > > > I want to see the first 3 purchases this
>   > member
>   > > > > made.
>   > > > > Instead of grabbing them all in one shot,
> I
>   > want
>   > > > to
>   > > > > break  those 3 out in 3 seperate results
> set
>   > > > >
>   > > > > Results Set1: Purchase 1
>   > > > > Results Set2: Purchase 2
>   > > > > Results Set3: Purchase 3
>   > > > >
>   > > > > This is probably easy.
>   > > > >
>   > > > > Thank you,
>   > > > > Stuart
>   > > > >
>   > > > > --
>   > > > > MySQL General Mailing List
>   > > > > For list archives:
>   > http://lists.mysql.com/mysql
>   > > > > To unsubscribe:
>   > > > >
>   > > >
>   > >
>   >
>  
>
http://lists.mysql.com/[EMAIL PROTECTED]
>   > > > >
>   > > > >
>   > > >
>   > > >
>   > > > --
>   > > > MySQL General Mailing List
>   > > > For list archives:
> http://lists.mysql.com/mysql
>   > > > To unsubscribe:
>   > > >
>   > >
> 
=== message truncated ===


-- 
MySQL General Mailing List
For list archives: http://lists

Re: tuning suggestion for large query

[Marc Slemko]

On Wed, 1 Sep 2004 11:40:34 -0400, Sun, Jennifer
<[EMAIL PROTECTED]> wrote:
> Hi,
> 
> We have a job that do 'select * from big-table' on a staging mysql database, then 
> dump to data warehouse, it is scheduled to run once a day, but may be run manually. 
> Also we have several other small OLTP database on the same server.
> When the big job run, it would use all the physical mem and swap, all other process 
> slow down because of this.
> 
> I would like to limit the resource usage for each mysql client  so that they can use 
> only certain max amount of RAM, and don't select everything into memory before 
> display it to users. However, I couldn't find any parameter would let me implement 
> it.
> Anybody ever encounter the same issue before? Please share your experience.

How exactly are you running this "select * from big-table"?  From the
"mysql" command line client?  Is that what is using memory?  It isn't
clear from your post if it is the server or something else using
memory.

If it is the mysql command line client that is the issue, try adding a
"-q" parameter.  If you are using myisam tables, however, keep in mind
that table will be effectively locked for the whole duration... but if
it is the mysql command line client (which defaults to buffering
everything in memory), it may be faster to use -q anyway.

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

RE: tuning suggestion for large query

[Sun, Jennifer]

The command is issued from mysql command line. Is there any parameters or options I 
can use without locking the table?


-Original Message-
From: Marc Slemko [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 02, 2004 2:24 PM
To: Sun, Jennifer
Cc: [EMAIL PROTECTED]
Subject: Re: tuning suggestion for large query


On Wed, 1 Sep 2004 11:40:34 -0400, Sun, Jennifer
<[EMAIL PROTECTED]> wrote:
> Hi,
> 
> We have a job that do 'select * from big-table' on a staging mysql database, then 
> dump to data warehouse, it is scheduled to run once a day, but may be run manually. 
> Also we have several other small OLTP database on the same server.
> When the big job run, it would use all the physical mem and swap, all other process 
> slow down because of this.
> 
> I would like to limit the resource usage for each mysql client  so that they can use 
> only certain max amount of RAM, and don't select everything into memory before 
> display it to users. However, I couldn't find any parameter would let me implement 
> it.
> Anybody ever encounter the same issue before? Please share your experience.

How exactly are you running this "select * from big-table"?  From the
"mysql" command line client?  Is that what is using memory?  It isn't
clear from your post if it is the server or something else using
memory.

If it is the mysql command line client that is the issue, try adding a
"-q" parameter.  If you are using myisam tables, however, keep in mind
that table will be effectively locked for the whole duration... but if
it is the mysql command line client (which defaults to buffering
everything in memory), it may be faster to use -q anyway.

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

RE: tuning suggestion for large query

[Sun, Jennifer]

I did 'handler table_name read limit large_numbers'. Is there a way I can use lower 
number, but automatically loop through the number and display all of the table 
records? Thanks.

-Original Message-
From: mos [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 02, 2004 10:37 AM
To: [EMAIL PROTECTED]
Subject: RE: tuning suggestion for large query


At 04:13 PM 9/1/2004, Sun, Jennifer wrote:
>Thanks Mike.
>Seems like even with handler, the big query process is still consuming all 
>my RAM and swap and being killed with error
>'VM: killing process mysql
>__alloc_pages: 0-order allocation failed (gfp=0x1d2/0)'
>
>I would like to find a startup parameter either for client or server to 
>limit per thread memory usage.

Can't help you there, sorry. I don't think that would work because MySQL is 
putting the retrieved rows into memory to get a snapshot of the table, so 
memory has to decrease. If you decrease the memory for the thread, then it 
will hit a wall.

I thought Handler would work because it is using a cursor into the table 
and shouldn't be consuming much memory. You fetch 10,000 rows at a time, 
then fetch another 10,000 rows etc.. It should only be consuming enough 
memory to hold 10,000 rows. Can you post the code showing how you're using 
Handler?

The only other thing I can suggest is to put a select statement inside of a 
loop and select using either a primary key, say rcd_id 1 to , export 
them, then select 1 to 1 and export it etc until you reach eof. 
This should be quite fast and consumes low amount of memory. I would 
recommend using the primary key instead of say "select ... LIMIT 
," because the offset will need to read over the first 'n' 
offset rows and this can be quite slow. Because you're doing only part of 
the table at a time, you won't get an exact snapshot if people are updating 
the table as you are exporting it, but it will be very low on memory.

Mike


>-Original Message-
>From: mos [mailto:[EMAIL PROTECTED]
>Sent: Wednesday, September 01, 2004 4:39 PM
>To: [EMAIL PROTECTED]
>Subject: Re: tuning suggestion for large query
>
>
>At 10:40 AM 9/1/2004, you wrote:
> >Hi,
> >
> >We have a job that do 'select * from big-table' on a staging mysql
> >database, then dump to data warehouse, it is scheduled to run once a day,
> >but may be run manually. Also we have several other small OLTP database on
> >the same server.
> >When the big job run, it would use all the physical mem and swap, all
> >other process slow down because of this.
> >
> >I would like to limit the resource usage for each mysql client  so that
> >they can use only certain max amount of RAM, and don't select everything
> >into memory before display it to users. However, I couldn't find any
> >parameter would let me implement it.
> >Anybody ever encounter the same issue before? Please share your experience.
> >
> >Thanks.
> >
> >-
>
>
>Try the Handler command with an Index if you need the results sorted. It
>may do the trick. :)
>
>Mike
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To 
>unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


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


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

Re: tuning suggestion for large query

[Marc Slemko]

Due to the nature of myisam tables, when you are doing a query then
the table will be locked for writes.  Reads will still be permitted
until another write request is made, at which time all further reads
and writes will be blocked until the query completes.

This, however, is already happening even without "-q" and adding the
"-q" will likely significantly shorten the time to execute, depending
on exactly how large this table is.

myisam is a very limiting table type as soon as you want to do
anything more than read from or write to a single row at a time using
indexed lookups.  innodb tables do not have this problem, although
they have limitations of their own.

On Thu, 2 Sep 2004 14:30:24 -0400, Sun, Jennifer
<[EMAIL PROTECTED]> wrote:
> The command is issued from mysql command line. Is there any parameters or options I 
> can use without locking the table?
> 
> 
> 
> 
> -Original Message-
> From: Marc Slemko [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 02, 2004 2:24 PM
> To: Sun, Jennifer
> Cc: [EMAIL PROTECTED]
> Subject: Re: tuning suggestion for large query
> 
> On Wed, 1 Sep 2004 11:40:34 -0400, Sun, Jennifer
> <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> > We have a job that do 'select * from big-table' on a staging mysql database, then 
> > dump to data warehouse, it is scheduled to run once a day, but may be run 
> > manually. Also we have several other small OLTP database on the same server.
> > When the big job run, it would use all the physical mem and swap, all other 
> > process slow down because of this.
> >
> > I would like to limit the resource usage for each mysql client  so that they can 
> > use only certain max amount of RAM, and don't select everything into memory before 
> > display it to users. However, I couldn't find any parameter would let me implement 
> > it.
> > Anybody ever encounter the same issue before? Please share your experience.
> 
> How exactly are you running this "select * from big-table"?  From the
> "mysql" command line client?  Is that what is using memory?  It isn't
> clear from your post if it is the server or something else using
> memory.
> 
> If it is the mysql command line client that is the issue, try adding a
> "-q" parameter.  If you are using myisam tables, however, keep in mind
> that table will be effectively locked for the whole duration... but if
> it is the mysql command line client (which defaults to buffering
> everything in memory), it may be faster to use -q anyway.
>

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

RE: tuning suggestion for large query

[Sun, Jennifer]

Thanks Marc,

What version of myisam table you are talking about? We are on 4.0.20, when I ran the 
big table query, I tried to insert to it twice without any issues.
The -q worked good for mysql client. Thanks.


Jennifer 

-Original Message-
From: Marc Slemko [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 02, 2004 2:41 PM
To: Sun, Jennifer
Cc: [EMAIL PROTECTED]
Subject: Re: tuning suggestion for large query


Due to the nature of myisam tables, when you are doing a query then
the table will be locked for writes.  Reads will still be permitted
until another write request is made, at which time all further reads
and writes will be blocked until the query completes.
This, however, is already happening even without "-q" and adding the
"-q" will likely significantly shorten the time to execute, depending
on exactly how large this table is.

myisam is a very limiting table type as soon as you want to do
anything more than read from or write to a single row at a time using
indexed lookups.  innodb tables do not have this problem, although
they have limitations of their own.

On Thu, 2 Sep 2004 14:30:24 -0400, Sun, Jennifer
<[EMAIL PROTECTED]> wrote:
> The command is issued from mysql command line. Is there any parameters or options I 
> can use without locking the table?
> 
> 
> 
> 
> -Original Message-
> From: Marc Slemko [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 02, 2004 2:24 PM
> To: Sun, Jennifer
> Cc: [EMAIL PROTECTED]
> Subject: Re: tuning suggestion for large query
> 
> On Wed, 1 Sep 2004 11:40:34 -0400, Sun, Jennifer
> <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> > We have a job that do 'select * from big-table' on a staging mysql database, then 
> > dump to data warehouse, it is scheduled to run once a day, but may be run 
> > manually. Also we have several other small OLTP database on the same server.
> > When the big job run, it would use all the physical mem and swap, all other 
> > process slow down because of this.
> >
> > I would like to limit the resource usage for each mysql client  so that they can 
> > use only certain max amount of RAM, and don't select everything into memory before 
> > display it to users. However, I couldn't find any parameter would let me implement 
> > it.
> > Anybody ever encounter the same issue before? Please share your experience.
> 
> How exactly are you running this "select * from big-table"?  From the
> "mysql" command line client?  Is that what is using memory?  It isn't
> clear from your post if it is the server or something else using
> memory.
> 
> If it is the mysql command line client that is the issue, try adding a
> "-q" parameter.  If you are using myisam tables, however, keep in mind
> that table will be effectively locked for the whole duration... but if
> it is the mysql command line client (which defaults to buffering
> everything in memory), it may be faster to use -q anyway.
>

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

Re: tuning suggestion for large query

[Marc Slemko]

On Thu, 2 Sep 2004 15:19:44 -0400, Sun, Jennifer
<[EMAIL PROTECTED]> wrote:
> Thanks Marc,
> 
> What version of myisam table you are talking about? We are on 4.0.20, when I ran the 
> big table query, I tried to insert to it twice without any issues.
> The -q worked good for mysql client. Thanks.

There is an optimization that can allow inserts (note: not updates)
and selects to happen at the same time, which may be what you are
seeing.  There are lots of corner cases, etc. so your best bet is to
check out the documentation which does a reasonable job of explaining
them, in particular:

http://dev.mysql.com/doc/mysql/en/Internal_locking.html
http://dev.mysql.com/doc/mysql/en/Table_locking.html

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

Installing MySQL 4.1 from RPM on Fedora2

[Danesh Daroui]

Hi all, 

I have installed Server, Client, Benhcmark, Share, Embeded and Compact-Share RPMs 
version 4.1 on a Fedora2 system. The have been installed successfully but when I type:

Shell> mysql -u root

it says that it can not open socket and I think it is because mysqld is not alive. 
When I type:

Shell> mysqld

it returns a fatal error and refers me to Security notes about installation. Also, I 
thought that an user account which name is mysql should be created automatically when 
RPMs are installed, but there is no user account with name mysql !!! Please help me to 
fix the problem and install mysql on my linux system.

Regards,

Danesh Daroui

Re: PHP MySQL (Intel Compiler) Problem

[Santhanam]

Dear Friends,
I have installed MySQL-devel RPM (Standard - gcc) and used it for
compiling PHP. MySQL Server remains the same intel build. It is working
perfectly although this may not be the desired solution.. please post if u
find any results.
Special Thanks to Mr.Egor Egorov for giving me a good tip.
With Regards
Santhanam


> "Santhanam" <[EMAIL PROTECTED]> wrote:
>
>> Dear Friends,
>>I am trying to compile PHP with MySQL support in an HP DL380
>> server (Xeon Processors). The MySQL is build with Intel Compiler.
>> I am getting the following problem / error :
>
> You have to link the client applications with some intel C compiler libs I
> guess.  Try to compile PHP with Intel C Compiler as well or upgrade the
> mysql
> client libraries to the ones compiled by gcc. There is a good reason to
> have
> MySQL server compiled with Intel C compiler but I'd rather prefere having
> libmysqlclient compiled by gcc.
>
>
>
>
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.net http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Egor Egorov
>  / /|_/ / // /\ \/ /_/ / /__   [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]
>
>


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

column types for intger, float or double field include arrays?

[Raghudev Ramaiah]

  

-
Do you Yahoo!?
Express yourself with Y! Messenger! Free. Download now.

column types for intger, float or double field include arrays?

[Raghudev Ramaiah]

hi
i have used column types such as intger and float and double for fields in the MySQL 
tables. can i use arryas of integers, floats and doubles as column type? i.e., can a 
field ina table be an integer array or float array ?
ex: count[20] integer is it allowed and is it an array?
 
thanks and regards
Raghu


-
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.

Re: column types for intger, float or double field include arrays?

[V. M. Brasseur]

Numeric types are simply that: numbers.  They cannot be arrays.
http://dev.mysql.com/doc/mysql/en/Numeric_types.html
Incidentally, why would you need this?  Maybe the group can come up with 
a viable alternative for you.

Cheers,
--V
Raghudev Ramaiah wrote:
hi
i have used column types such as intger and float and double for fields in the MySQL tables. can i use arryas of integers, floats and doubles as column type? i.e., can a field ina table be an integer array or float array ?
ex: count[20] integer is it allowed and is it an array?
 
thanks and regards
Raghu


-
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: tuning suggestion for large query

[mos]

At 01:33 PM 9/2/2004, Sun, Jennifer wrote:
I did 'handler table_name read limit large_numbers'. Is there a way I can 
use lower number, but automatically loop through the number and display 
all of the table records? Thanks.
If "large_numbers" is the number of rows in the table, then of course it 
will consume a lot of memory and would make it the equivalent to a Select 
statement. To use Handler properly you need 2 loops. The outer loop would 
be executing the Handler statement to get the Next 1 rows. Then the 
inner loop goes through those 10,000 rows and exports them to the other 
table. You exit the loop when you get EOF. So you only have 10,000 rows in 
memory at one time (or however many you want to process at a time). You 
need some sort of programming language to write the code to do the looping. 
You can't do this sort of looping at the MySQL prompt I'm afraid.

Mike

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

Re: column types for intger, float or double field include arrays?

[Raghudev Ramaiah]

Hi,
I need this since I have around 3 sets of 20 fields each are of the same data type 
.
 if i am able to use arrays , i can say
 
integer[20] m1;
integer[20] m2;
integer[20] m3;

if not , i will have to declare 60 fields
integer m1 to integr m60.
 
any solutions please?its quite urgent!
 
Thanks and Regards
Raghu
"V. M. Brasseur" <[EMAIL PROTECTED]> wrote:
Numeric types are simply that: numbers. They cannot be arrays.

http://dev.mysql.com/doc/mysql/en/Numeric_types.html

Incidentally, why would you need this? Maybe the group can come up with 
a viable alternative for you.

Cheers,

--V

Raghudev Ramaiah wrote:
> hi
> i have used column types such as intger and float and double for fields in the MySQL 
> tables. can i use arryas of integers, floats and doubles as column type? i.e., can a 
> field ina table be an integer array or float array ?
> ex: count[20] integer is it allowed and is it an array?
> 
> thanks and regards
> Raghu
> 
> 
> -
> Do you Yahoo!?
> Win 1 of 4,000 free domain names from Yahoo! Enter now.

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



-
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!

RE: Installing MySQL 4.1 from RPM on Fedora2

[Donny Simonton]

Start mysql on fc2 with 
/etc/rc.d/init.d/mysql start

If that fails then look at the mysql error log.

Donny

> -Original Message-
> From: Danesh Daroui [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 02, 2004 3:04 PM
> To: [EMAIL PROTECTED]
> Subject: Installing MySQL 4.1 from RPM on Fedora2
> 
> Hi all,
> 
> I have installed Server, Client, Benhcmark, Share, Embeded and Compact-
> Share RPMs version 4.1 on a Fedora2 system. The have been installed
> successfully but when I type:
> 
> Shell> mysql -u root
> 
> it says that it can not open socket and I think it is because mysqld is
> not alive. When I type:
> 
> Shell> mysqld
> 
> it returns a fatal error and refers me to Security notes about
> installation. Also, I thought that an user account which name is mysql
> should be created automatically when RPMs are installed, but there is no
> user account with name mysql !!! Please help me to fix the problem and
> install mysql on my linux system.
> 
> Regards,
> 
> Danesh Daroui



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

How to use "use" for a specific host

[Brian Menke]

I'm trying to specify a host name and database name to do an update to a
table. I can't quite figure out what the correct syntax is? Is it something
like:

 

USE [EMAIL PROTECTED] I have tried several permutations of that but
can't quite seem to get the syntax just right.

 

Thanks.

 

-Brian

Re: column types for intger, float or double field include arrays?

[Roger Baklund]

* Raghudev Ramaiah 
> I need this since I have around 3 sets of 20 fields each are 
> of the same data type .
>  if i am able to use arrays , i can say
>  
> integer[20] m1;
> integer[20] m2;
> integer[20] m3;
> 
> if not , i will have to declare 60 fields
> integer m1 to integr m60.
>  
> any solutions please?its quite urgent!

A table is a kind of an array...

You can store one integer on each row:

CREATE TABLE m (
  id tinyint unsigned not null,
  value int,
  primary key(id)
);

INSERT INTO m VALUES (1,2),(2,4),(3,6),(60,120);

SELECT value FROM m WHERE id=1;# == m[1]
SELECT value FROM m WHERE id=60;   # == m[60]

...or, using three integers 20 times:

DROP TABLE m;

CREATE TABLE m (
  id tinyint not null,
  m1 int,
  m2 int,
  m3 int,
  primary key(id)
);

INSERT INTO m VALUES (1,2,4,6),(2,4,8,12),(20,8,16,24);

SELECT m1 FROM m WHERE id=1;   # == m1[1]
SELECT m2 FROM m WHERE id=20;   # == m2[20]

-- 
Roger


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

Re: How to use "use" for a specific host

[V. M. Brasseur]

Are you using the mysql client to connect?  If so, you can use the -h 
and -D flags:
  mysql -h hostname [-u username -p -P port] -D databasename

The -D flag isn't even necessary.  The command above is equivalent to 
this one:
  mysql -h hostname [-u username -p -P port] databasename

http://dev.mysql.com/doc/mysql/en/mysql.html
If you're writing your own client, you chould check the API 
documentation for the language you're using.

If you're using a completely different client, check the docs for it.
Be one with your manual.  Or be one with your many manuals.  I leave it 
to you to figure out that existential math.

Cheers,
--V
Brian Menke wrote:
I'm trying to specify a host name and database name to do an update to a
table. I can't quite figure out what the correct syntax is? Is it something
like:
 

USE [EMAIL PROTECTED] I have tried several permutations of that but
can't quite seem to get the syntax just right.
 

Thanks.
 

-Brian

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

Re: How to use "use" for a specific host

[Roger Baklund]

* Brian Menke
> I'm trying to specify a host name and database name to do an update to a
> table. I can't quite figure out what the correct syntax is? Is it
> something
> like:
>
> USE [EMAIL PROTECTED] I have tried several permutations of that but
> can't quite seem to get the syntax just right.

The hostname is used when connecting to the host, not when you change active
database (with the USE statement).

The syntax for USE is simply:

mysql> USE databasename

What server you connect to is defined in the connect statement of whatever
programming language you use, or on the command line if you use the standard
mysql client. Using the standard client it is however possible to change to
a different server, using the 'connect' client command, short '\r'. You can
swap to a different database on a different server using the syntax:

mysql> CONNECT databasename otherhost

A shortcut is defined, as mentioned. This will also work:

mysql> \rdatabasename otherhost

http://dev.mysql.com/doc/mysql/en/USE.html >
http://dev.mysql.com/doc/mysql/en/mysql_Commands.html >

--
Roger


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

Re: question about innodb_thread_concurrency

[Ware Adams]

Heikki Tuuri wrote:
Devanada,
normally you do not need to tune this option at all.
I introduced the option when certain Linux binaries showed 'thread
thrashing' at high concurrency. Setting the option to 1 or 2 could in some
cases alleviate the problem.
You can experiment by setting the option to 500 to disable restrictions on
concurrent operations. I am considering changing the default value to 500,
because we have not seen 'thread thrashing' on any platform for a long
while.
We did see something that looked a lot like this about a year ago on Mac 
OS X 10.3.x running InnoDB 4.0.x.  Queries that normally ran fine all of 
a sudden started running incredibly slowly.  The CPU was pinned, but 
things just weren't finishing in any reasonable time.  Eventually InnoDB 
noticed some long waits and intentionally crashed itself.

Thread concurrency was at 20-30 at the time, I think.  We were running 
on a dual processor machine on a 6 drive RAID, so we followed the 
instructions in the InnoDB manual and set thread concurrency to 8.  The 
problems immediately went away.

Haven't tried increasing it now that we're on a G5, a few revisions of 
the OS and a few revisions of InnoDB newer.  Performance seems fine as is.

--Ware Adams
Best regards,
Heikki
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
Order MySQL support from http://www.mysql.com/support/index.html
...
Hi,
This is a question about optimization of mysql's InnoDB performance ...
quoting the doc's
 > "The default value is 8. If you have low performance and |SHOW INNODB
STATUS| reveals many threads waiting for semaphores,
 > you may have thread thrashing and should try setting this parameter
lower or higher. The default value is 8. If you have low
 > performance and |SHOW INNODB STATUS| reveals many threads waiting for
semaphores, you may have thread thrashing and
 > should try setting this parameter lower or higher."
What is a reasonable value for a dual Xeon 2.8Ghz with 6 disks? we are
currently running with innodb_thread_concurrency=16. Each DB has 200-400
active php clients, and there appears to be quite a large backup within
innodb, based on this. Any suggestions? Or experience with setting this
option to 500?
Thanks :)
Devananda
Neopets, Inc

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

Re: Backing up directly to tape.

[Bruce Ferrell]

How about
mysqldump > /dev/nst0


Tucker, Gabriel wrote:
Hello All
I have been searching the archives and was unable to find an answer.
I need the ability to backup MySQL instances directly to a tape device.
Currently, I run a mysqldump to disk and have legato pick up the file.  As I get to 
some larger databases, hundred's of gigs, and higher transaction rates, I will need an 
online solution that goes directly to a tape device.  Currently we are using MySQL 
table types, though this may change.
Thanks - Gabe
<><><><><><><><><><><><><><><><><><><><><>
"There are no problems, only solutions."
Gabe Tucker
Bloomberg LP
(609) 750 6668 - P
(646) 268 5681 - F
<><><><><><><><><><><><><><><><><><><><><>

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

MySQL compiled with OpenSSL support in XP(not works)

[Osmin Castillo]

Just finish compiled the mysql 4.0.20-0 windows source 4.0.20-0 with
the openssl options, generate certificates and edited the my.cnf file
with this values:

[client]
ssl-ca=c:/certifi/ssl/cacert.pem 
ssl-cert=c:/certifi/ssl/client-cert.pem
ssl-key=c:/certifi/ssl/client-key.pem

[mysqld]
ssl-ca=c:/certifi/ssl/cacert.pem 
ssl-cert=c:/certifi/ssl/server-cert.pem
ssl-key=c:/certifi/ssl/server-key.pem

I'm having some problems connecting to the server under Winx XP with
the openssl certificates. Checking the have_openssl variable says
"YES". When i try to connect with a user with the  REQUIRE SSL grant I
can't loggin and receive this error: "ERROR 1045: Access denied for
user: '[EMAIL PROTECTED]'"

Using another user with out the REQUIRE SSL  grant,  works ok. But the
connection is not ssl secure. So i send this command "show status;"
and see this ssl items with out value

Ssl_version,Ssl_cipher,Ssl_cipher_list (all of them are empty)


Where is the problem? Please help me...

Thanks in advance..


Osmin

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

Re: Suppression of result in SELECT @temp := column?

[Eamon Daly]

Nifty idea, but it doesn't work for me:

$ cat < \P cat /dev/null
> SELECT @id := id FROM users ORDER BY id DESC LIMIT 1;
> \n
> SELECT * FROM users WHERE id = @id;
> EOF
@id := id
3
PAGER set to stdout
id  first   lastemail
3   testy   mctest  [EMAIL PROTECTED]

Ideally, the output would look like:

$ cat < /* MAGIC */
> SELECT @id := id FROM users ORDER BY id DESC LIMIT 1;
> SELECT * FROM users WHERE id = @id;
> EOF
id  first   lastemail
3   testy   mctest  [EMAIL PROTECTED]

I suppose a JOIN would work, instead:

$ cat < CREATE TEMPORARY TABLE last_id SELECT id FROM users ORDER BY id DESC LIMIT
1;
> SELECT users.* FROM users JOIN last_id USING (id);
> EOF
id  first   lastemail
3   testy   mctest  [EMAIL PROTECTED]

but using @id seems less, well, wasteful.


Eamon Daly



- Original Message - 
From: "Diana Soares" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, September 02, 2004 9:38 AM
Subject: Re: Suppression of result in SELECT @temp := column?


> I didn't understand the problem very well, but maybe disabling the pager
> before that query where you set @temp, do the query, setting the pager
> to its default value and then do the  "next statement to produce the
> actual result."
>
> \P cat /dev/null
> SELECT @temp := columnID FROM table WHERE column = whatever LIMIT 0,1
> \n
> SELECT 
>
> -- 
> Diana Soares
>
>
> On Tue, 2004-08-31 at 23:00, Eamon Daly wrote:
> > Did anyone ever follow up on this question? I'm looking for
> > the answer, too.
> > 
> > Eamon Daly
>
> > - Original Message - 
> > From: <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Wednesday, August 25, 2004 9:16 AM
> > Subject: Suppression of result in SELECT @temp := column?
>
> > > Is there any way to not sending the result of a user variable
assignment
> > > to the client?
> > > I.e. Suppress the result of;
> > >
> > > SELECT @temp := columnID FROM table WHERE column = whatever LIMIT 0,1
> > >
> > > ...since I only use @temp in my next statement to produce the actual
> > result.


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

Re: Suppression of result in SELECT @temp := column?

[Zak Greant]

Hi!
On Aug 25, 2004, at 8:16, <[EMAIL PROTECTED]> wrote:
Is there any way to not sending the result of a user variable 
assignment to
the client?

I.e. Suppress the result of;
SELECT @temp := columnID FROM table WHERE column = whatever LIMIT 0,1
...since I only use @temp in my next statement to produce the actual 
result.
Use DO instead of SELECT. DO is like SELECT, except that it doesn't 
return any results.

Also, is there a equivalence to the MSSQL statement;
SET NOCOUNT ON
...in MySQL, which suppresses the "rows affected" messages?
Not AFAIK. You can make the MySQL client less verbose (which includes 
suppressing the rows affected message) by starting the client with the 
--silent/-s option.

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

RE: How to use "use" for a specific host

[Brian Menke]

Thank you for your replies. I should have been more specific. I was just
using the sql editor in MySQL Control Center. I have two databases that I'm
managing that sit on different physical machines, but have the same database
names and table names etc., basically a test environment and a production
environment. When I update one table on one machine, the other table of the
same name on the other machine also gets updated. I swear it happened even
though I intentionally disconnected from the other database. The same thing
happened when I used MySQL Front. It's very bizarre.

-Brian

-Original Message-
From: V. M. Brasseur [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 02, 2004 3:00 PM
To: Brian Menke
Cc: [EMAIL PROTECTED]
Subject: Re: How to use "use" for a specific host

Are you using the mysql client to connect?  If so, you can use the -h 
and -D flags:
   mysql -h hostname [-u username -p -P port] -D databasename

The -D flag isn't even necessary.  The command above is equivalent to 
this one:
   mysql -h hostname [-u username -p -P port] databasename

http://dev.mysql.com/doc/mysql/en/mysql.html

If you're writing your own client, you chould check the API 
documentation for the language you're using.

If you're using a completely different client, check the docs for it.

Be one with your manual.  Or be one with your many manuals.  I leave it 
to you to figure out that existential math.

Cheers,

--V

Brian Menke wrote:
> I'm trying to specify a host name and database name to do an update to a
> table. I can't quite figure out what the correct syntax is? Is it
something
> like:
> 
>  
> 
> USE [EMAIL PROTECTED] I have tried several permutations of that but
> can't quite seem to get the syntax just right.
> 
>  
> 
> Thanks.
> 
>  
> 
> -Brian
> 
> 


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

innodb buffer pool size -- why 70%?

[Sergei Skarupo]

I read in a couple of places that on a dedicated server the innodb buffer pool size 
should not exceed 70% of total physical memory. Where does this number come from?
 
Thanks in advance,
 
Sergei

Re: Problem on InnoDB - Tablespace enough but engine said table full

[Ady Wicaksono]

May i know, how could i know which of the data files that InnoDB MySQL engine is not 
used ?
 
Did i i made a mistake when adding table space ?

Heikki Tuuri <[EMAIL PROTECTED]> wrote: 
Ady,

InnoDB thinks that the tablespace size is 10 706 MB.

You have specified 36 782 MB of data files in the my.cnf line :(.

Now you should figure out what are the data files that InnoDB is using, and
remove the end of the innodb_data_file_path line, as well as the unused
ibdata files. Remember that InnoDB uses data files starting from the first,
in the order that you list them in innodb_data_file_path.

Be very careful! Do not remove your valuable data!

After that, you can grow the tablespace as instructed at
http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html

Best regards,

Heikki
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

Order MySQL support from http://www.mysql.com/support/index.html

.

I have MySQL for heavy duty job .

here is my InnoDB table space definition


innodb_data_file_path = \
/data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15
00M;/data0 \
/ibdata5:1500M;/
data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ \
ibdata9:1500M;/data4/ibdata25:1802M;/data3/ibdata25:1802M \
;/data4/ibdata25.data3:576M;/data1/ibdata10:1500M;/data1/ibdata11:1500M;/dat
a1/ibdata1 \
2:1500M;/data1/ibdata13:1500M;/data2/ibd \
ata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16:1500M;/data2/ibdata17:1500
M;/data2/i \
bdata18:1500M;/data2/ibdata19:1500M;/dat \
a3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ibdata22:1500M;/data3/ibdata2
3:1500M;/d \
ata3/ibdata24:1500M;


What i don't understand is :

after ibdata1 -> ibdata9 full is go to /data4/ibdata25 ... (1802Mbyte)

But it's not continues to write to /data4/ibdata25.data3... /data1/ibdata10
and so on

but said table  is full when i try to write data to table

The question is why ?

Secondly



What is the meaning of

InnoDB: Error: tablespace size stored in header is 685184 pages, but
InnoDB: the sum of data file sizes is 2354048 pages


Thx


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




-
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!