How to view locks in MySQL

2006-11-01 Thread Ow Mun Heng
Under MSSQL there's a stored procedure called sp_lock which can be used. Is there an equivalent one in mySQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

help trying to add an autoincrement col to an exisiting table

2006-11-01 Thread Randy Paries
Hello, currently i have the following table structure CREATE TABLE users ( uname varchar(20) NOT NULL default '', passwd varchar(15) NOT NULL default '', fname varchar(25) NOT NULL default '', lname varchar(40) NOT NULL default '', dir varchar(28) NOT NULL default '', pict varchar(50) NOT N

Re: MyISAM vs InnoDB

2006-11-01 Thread Jochem van Dieten
On 11/1/06, mos wrote: At 02:27 PM 11/1/2006, Jochem van Dieten wrote: What is the big deal of a TB? Now, if you get past 20 TB you might want to team up with one of the commercial PostgreSQL supporters (Fujitsu, EnterpriseDB, Greenplum etc.), but Sun even sells appliances for 100 TB PostgreSQL

RE: Update field concactenation

2006-11-01 Thread Edwin Cruz
Try: Update myTable set myField = concat(myField,' new data to add') where myField = 'some restrictions'; Regards! > -Mensaje original- > De: Rich [mailto:[EMAIL PROTECTED] > Enviado el: Miércoles, 01 de Noviembre de 2006 06:35 p.m. > Para: Submit MySQL > Asunto: Update field concact

Update field concactenation

2006-11-01 Thread Rich
Hi folks. Boy do I have an interesting one for you today. This list is great. Can I update a record using a typical update query, and have a field be updated to: previous value of the field + (something I want added to the end) so field 'flavours' starts out as: "vanilla chocolate" ...t

Re: Manually Inserted Data

2006-11-01 Thread Gerald L. Clark
Keith Spiller wrote: Hi Rolando, Thanks for your help. I have reduced the query to the bare essensials to try to test the concept, but it still fails... ( SELECT ID, Start, End ( SELECT ID, Start, End as z FROM site_calendar_v2 as c WHERE Global='1' ) UNION ( SELECT '9', '2006-11-01',

Re: MyISAM vs InnoDB

2006-11-01 Thread mos
At 02:27 PM 11/1/2006, Jochem van Dieten wrote: On 11/1/06, mos wrote: Sure, I've thought of those too. But has anyone gotten Firebird to store 700-800gb tables? Can you split Firebird's .gdb file across drives? The main problem with tables of that size is maintaining the index. My upp

Re: Manually Inserted Data

2006-11-01 Thread Keith Spiller
Hi Rolando, Thanks for your help. I have reduced the query to the bare essensials to try to test the concept, but it still fails... ( SELECT ID, Start, End FROM site_calendar_v2 as c WHERE Global='1' ) UNION ( SELECT '9', '2006-11-01', '-00-00' as z ) ORDER BY z, Start ASC, Status DES

Re: Manually Inserted Data

2006-11-01 Thread Rolando Edwards
The first UNION part has 19 columns the other UNION part has 18 columns - Original Message - From: Keith Spiller <[EMAIL PROTECTED]> To: [MySQL] Sent: Wednesday, November 1, 2006 3:31:24 PM GMT-0500 US/Eastern Subject: Manually Inserted Data Hi, I'm trying to manually insert data into a

Manually Inserted Data

2006-11-01 Thread Keith Spiller
Hi, I'm trying to manually insert data into a mysql query so that the data will be sorted by the query but not inserted into the table. Here's what I have so far: ( SELECT ID, Title, Label, Location, Start, End, Time, Description, Organization, Department, Contact, Phone, Email, Global, Board

Re: MyISAM vs InnoDB

2006-11-01 Thread Jochem van Dieten
On 11/1/06, mos wrote: Sure, I've thought of those too. But has anyone gotten Firebird to store 700-800gb tables? Can you split Firebird's .gdb file across drives? The main problem with tables of that size is maintaining the index. My upper limit for MySQL is 100 million rows. After tha

Re: RE: Max of Count

2006-11-01 Thread Dan Buettner
This is how I'd do it, though ISTR there being a problem with LIMITs in subqueries at one point (works for me on 5.0.24). (I changed to user and user_entitlements because I have those tables I can use in my database) SELECT u.id, COUNT(*) AS ct FROM users u, user_entitlements ue WHERE u.id = ue.

RE: Max of Count

2006-11-01 Thread Jerry Schwartz
Thanks, Dan, that does indeed work; but as you said it only gives one record. Removing the limit gives them all to me, which for visual inspection works fine. This started out as a "Gee, I wonder..." task, so as a learning experience I'd like to figure out how to get all of (and only) the records

Re: returning unique value

2006-11-01 Thread Dan Buettner
Ross - In your query you can add a "LIMIT" clause to get just one row in the result, a la: SELECT * FROM thumbnails where gallery=$id LIMIT 1 If you want the "first" image, say the one with lowest ID number, you could do this: SELECT * FROM thumbnails where gallery=$id ORDER BY id LIMIT 1 Dan

Fw: returning unique value

2006-11-01 Thread Ross Hulford
I have atable which contains the number and name of the photo galleries and 'thumnails' the images that are conenected to the galleries. I am trying to create a 'pick a gallery' screen where it selects all the galleries and then output the first thumbnail image associated with that gallery.

returning unique value

2006-11-01 Thread Ross Hulford
I have two tables galleries which contains the number and name of the photo galleries and 'thumnails' the images that are conenected to the galleries. I am trying to create a 'pick a gallery' screen where it selects all the galleries and then output the first thumbnail image associated with t

jdbc question regarding enum

2006-11-01 Thread Jon Drukman
disclaimer: i know nothing about java or jdbc. a co-worker is trying to access a database i've set up using jdbc. he says that my enum column is always returning an integer value instead of the string. obviously this is less than desirable. does anybody have any advice i could give him on w

Re: MyISAM vs InnoDB

2006-11-01 Thread mos
At 09:35 AM 11/1/2006, Martijn Tonies wrote: >> > MyISAM vs InnoDB ? What is the best to use >> >> Always use a DBMS, and MySQL is no (proper) DBMS without a >> transactional >>backend. There are InnoDB, which is not completely free (needs a proprietary >>backup tool); BDB, which is depr

Re: Automatic Removal

2006-11-01 Thread Martijn Tonies
> got a Really Simple question, which is probably really easy, but it's > just not starign me in the face. > > Is there any "query" in MYSQL (or function in PHP that works with MYSQL) > to remove a value from a field when a new week comes across, or to > remove something from a field on a defi

Automatic Removal

2006-11-01 Thread Richard Harris
Hi, got a Really Simple question, which is probably really easy, but it's just not starign me in the face. Is there any "query" in MYSQL (or function in PHP that works with MYSQL) to remove a value from a field when a new week comes across, or to remove something from a field on a defined da

Re: storing images in the database

2006-11-01 Thread Richard Harris
Hi, In my honest opinion, uploading the image to a "preset" directory in the code, and setting the code to submit the image name, extension, and if needs be (it shouldnt, because you can set it in the HTML), the directory. Google has a lot of useful resources on this. But, if you are really

RE: MyISAM to InnoDB conversion help

2006-11-01 Thread Mikhail Berman
Great, Thank you for your help Rolando, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:41 AM Cc: mysql@lists.mysql.com; Mikhail Berman Subject: Re: MyISAM to InnoDB conversion help I just noticed your innodb_da

Re: MyISAM to InnoDB conversion help

2006-11-01 Thread Rolando Edwards
I just noticed your innodb_data_file_path You have a shared InnoDB tablespace That can be murder on a MySQL Server You may want to separate each InnoDB into a separate file Here are the steps needed to separate InnoDB tables. 1) Do a mysqldump on your database to mydata.sql. 2) Shutdown MySQL 3)

Re: MyISAM to InnoDB conversion help

2006-11-01 Thread Rolando Edwards
Check these variable bulk_insert_buffer_size (Default usually 8M) innodb_buffer_pool_size (Default usually 8M) - Original Message - From: Mikhail Berman <[EMAIL PROTECTED]> To: Rolando Edwards <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:13:44 AM GMT

Re: MyISAM vs InnoDB

2006-11-01 Thread Edward Macnaghten
Francis wrote: Question about MyISAM vs InnoDB ? What is the best to use, I have a large table contain around 10 millons of records. What is the best for me ? Use MyISAM or InnoDB ? Depends VERY much on your application. If any concurrency and/or durability is required then I would

Re: MyISAM vs InnoDB

2006-11-01 Thread Edward Macnaghten
Francis wrote: Question about MyISAM vs InnoDB ? What is the best to use, I have a large table contain around 10 millons of records. What is the best for me ? Use MyISAM or InnoDB ? Depends VERY much on your application. If any concurrency and/or durability is required then I would

RE: MyISAM to InnoDB conversion help

2006-11-01 Thread Mikhail Berman
Hi Rolando, Thank you for your help. I am on MySQL 5, and I have tried to do the conversion using ALTER TABLE command. With the same very slow result. Do you by any chance have specific suggestions how to tweak variables related to this? Here is what I got: +-

Re: MyISAM to InnoDB conversion help

2006-11-01 Thread Rolando Edwards
If you are do this in MySQL 5, try this: ALTER TABLE ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikha

Re: MyISAM vs InnoDB

2006-11-01 Thread Martijn Tonies
>> > MyISAM vs InnoDB ? What is the best to use >> >> Always use a DBMS, and MySQL is no (proper) DBMS without a >> transactional >>backend. There are InnoDB, which is not completely free (needs a proprietary >>backup tool); BDB, which is deprecated until further notices; and SolidDB, >>wh

Re: MyISAM vs InnoDB

2006-11-01 Thread mos
At 05:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote: Em Tue, 31 Oct 2006 15:24:44 -0500, Francis escreveu: > MyISAM vs InnoDB ? What is the best to use Always use a DBMS, and MySQL is no (proper) DBMS without a transactional backend. There are InnoDB, which is not com

MyISAM to InnoDB conversion help

2006-11-01 Thread Mikhail Berman
Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting i

Re: Pushing mysql to the limits

2006-11-01 Thread Mathieu Bruneau
Hi, What been said below is true, I also think you should probably use Merge table on subsequent table. While 2G won't be enough for holding the full index for sure, splitting the table could allow you to have much smaller index and be able to hold that one in memory. Also if you use dynamic size

Re: Pushing mysql to the limits

2006-11-01 Thread Dan Buettner
Cabbar - That much data is possible in MySQL; I've seen references to terabyte databases in the past. 700-800 GB in a single table appears to be possible but I wouldn't really recommend it. A couple of suggestions, based on my own reading (I've not worked with such large dbs in MySQL myself, on

Re: MyISAM vs InnoDB

2006-11-01 Thread Paul McCullagh
On Nov 1, 2006, at 12:56 PM, Leandro Guimarães Faria Corcete DUTRA wrote: Em Tue, 31 Oct 2006 15:24:44 -0500, Francis escreveu: MyISAM vs InnoDB ? What is the best to use Always use a DBMS, and MySQL is no (proper) DBMS without a transactional backend. There are InnoDB, which is not co

Re: MyISAM vs InnoDB

2006-11-01 Thread Jon Ribbens
Miles Thompson <[EMAIL PROTECTED]> wrote: > At 07:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote: > > .. further notices; and SolidDB, which > >is still β. > > Help this poor English-speaker - what's the symbol you use to describe > SolidDB? I assume it is a "beta" character, sin

Re: MyISAM vs InnoDB

2006-11-01 Thread Miles Thompson
At 07:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote: .. further notices; and SolidDB, which is still β. Choose your evil. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://b

Re: MyISAM vs InnoDB

2006-11-01 Thread Leandro Guimarães Faria Corcete DUTRA
Em Tue, 31 Oct 2006 15:24:44 -0500, Francis escreveu: > MyISAM vs InnoDB ? What is the best to use Always use a DBMS, and MySQL is no (proper) DBMS without a transactional backend. There are InnoDB, which is not completely free (needs a proprietary backup tool); BDB, which is deprecated

Pushing mysql to the limits

2006-11-01 Thread Cabbar Duzayak
Hi, We have huge amount of data, and we are planning to use logical partitioning to divide it over multiple machines/mysql instances. We are planning to use Intel based machines and will prefer ISAM since there is not much updates but mostly selects. The main table that constitutes this much of d