Script run how???
Hi, I want to run following command thru script . analyze table t1; optimize table t1; analyze table t2; optimize table t2; Can I do it on linux mysql ? I also want to run script like during analysing if we notice any error related with table then run repair table t1 l repair table t2; Thanks, - How low will we go? Check out Yahoo! Messengers low PC-to-Phone call rates.
Conditional Insert
Dear Members,
I wish to insert and update conditionally into a table using a single SQL
statement. That is if a record already exists in a table, the statement
should update it, else it should insert it. Probably something like this:
if (select * from UserPoints where username = 'ravi') then
(update UserPoints set points = points + 5 where username = 'ravi')
else
(insert into UserPoints (username, points) values ('ravi', 0))
end if
I can not do it pragmatically using PHP, because the environment is highly
concurrent and it may result in a 'race condition' often.
The version of MySQL we are using is 4.1.14-standard-log.
Please reply soon, it is urgent.
Have a nice day!
Thanks and regards,
Ravi.
RE: Conditional Insert
Hi Douglas,
Thanks for your prompt reply. I read through the description of REPLACE and
tested it as well. It seems just fine, but the idea that it would DELETE and
then insert if a match is found, seems like a warning to me.
I need to know this: can it happen that in between this delete and insert,
another thread inserts a matching record?
Do we have any alternative?
Thanks,
Ravi.
-Original Message-
From: Douglas Sims [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 29 August, 2006 10:48 AM
To: Ravi Kumar.
Cc: mysql@lists.mysql.com
Subject: Re: Conditional Insert
Hi Ravi
Standard SQL syntax doesn't provide for that. You could wrap the whole
thing in a transaction, possibly in a stored procedure, but this would be
rather awkward.
However, MySQL has a command called "REPLACE" which I think will do exactly
what you want:
http://dev.mysql.com/doc/refman/5.0/en/replace.html
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 12:08 AM, Ravi Kumar. wrote:
> Dear Members,
>
> I wish to insert and update conditionally into a table using a single
> SQL statement. That is if a record already exists in a table, the
> statement should update it, else it should insert it. Probably
> something like
> this:
>
> if (select * from UserPoints where username = 'ravi') then (update
> UserPoints set points = points + 5 where username = 'ravi') else
> (insert into UserPoints (username, points) values ('ravi', 0)) end if
>
> I can not do it pragmatically using PHP, because the environment is
> highly concurrent and it may result in a 'race condition' often.
>
> The version of MySQL we are using is 4.1.14-standard-log.
>
> Please reply soon, it is urgent.
>
> Have a nice day!
>
> Thanks and regards,
>
> Ravi.
--
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: Conditional Insert
Dear All,
Thanks for the prompt replies. The version of MySQL we are using
(4.1.14-standard-log) supports INSERT ON DUPLICATE, hence I am using this.
Have a fantastic day!
Regards,
Ravi.
_
From: Douglas Sims [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 29 August, 2006 12:02 PM
To: Johan Höök
Cc: Ravi Kumar.; mysql@lists.mysql.com
Subject: Re: Conditional Insert
Much better. Good job.
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 1:26 AM, Johan Höök wrote:
Hi Ravi,
you can take a look at:
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
which might take care of your problem.
/Johan
Douglas Sims skrev:
Hi Ravi
You are correct. I was just sitting here thinking about this after I sent
that and realized that what I said was incorrect; the "REPLACE" command will
not do what you want, because it delete a record instead of updating it, it
has no way to know what the previous value is. You probably should define a
stored procedure and call that. Here is an example which seems to work:
mysql> show create table ravitest;
+--+
---+ | Table| Create Table
|
+--+
---+ | ravitest | CREATE TABLE `ravitest` (
`username` varchar(16) NOT NULL,
`points` int(10) unsigned default NULL,
PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--+
---+ 1 row in set (0.00 sec)
mysql> show create procedure r1;
+---+--+
--+ | Procedure | sql_mode | Create
Procedure
|
+---+--+
--+ | r1| | CREATE
PROCEDURE `r1`(n VARCHAR(16), p INT)
BEGIN
DECLARE RowsFound INT DEFAULT 0;
SELECT COUNT(*) INTO RowsFound FROM ravitest WHERE username=n;
IF RowsFound=0 THEN
INSERT INTO ravitest VALUES (n, p);
ELSE
UPDATE ravitest SET ravitest.points=ravitest.points+p where
ravitest.username=n;
END IF;
END |
+---+--+
--+ 1 row in set (0.00 sec)
mysql> select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 3 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
+--++
7 rows in set (0.00 sec)
mysql> call r1('ravi', 15);
Query OK, 1 row affected (0.00 sec)
mysql> select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 18 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
+--++
7 rows in set (0.00 sec)
mysql> call r1('Elaine', 5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from ravitest;
+--++
| username | points |
+--++
| Ravi | 18 |
| Joe | 7 |
| Amy | 9 |
| Doug | 22 |
| Susan| 4 |
| Tom | 2 |
| Jim | 8 |
| Elaine | 5 |
+--++
8 rows in set (0.00 sec)
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 1:01 AM, Ravi Kumar. wrote:
Hi Douglas,
Thanks for your prompt reply. I read through the description of REPLACE and
tested it as well. It seems just fine, but the idea that it would DELETE and
then insert if a match is found, seems like a warning to me.
I need to know this: can it happen that in between this delete and insert,
another thread inserts a matching record?
Do we have any alternative?
Thanks,
Ravi.
--
Data dictionary for MySQL
Hi All, I am looking for a data dictionary for MySQL. It should be free and ideally, developed in php. And such that it stores the data dictionary in MySQL itself. Any suggestions / pointers? Regards, Ravi.
Like inside in clause
Dear Friends,
Is there a way to specify wildcard inside values of an in clause. I want to
be able to do this:
select id, name from tableName where name in ('a%', 'b%', 'c%');
instead of doing
select id, name from tableName where name like 'a%' or name like 'b%' or
name like 'c%';
Thanks,
Ravi.
Conditional copy of values
Dear All, I have 2 tables Table1 and Table2. In Table1 rowid is primary key and in Table2 rowid is primary key as well as foreign key (referring to rowid in Table1). There are several other columns in Table1, but Table2 has only one col - rowid. I want to insert rowid into Table2 conditionally: insert into Table2 (rowid) select T1.rowid from Table1 T1, Table2 T2 where T1.somecolumn = 'some condition' But I want to insert only those rowid values which do not exist in T2. Please help. I am using MySQL version 4.1.14-standard-log. Hence subqueries are not a solution. Thanks, Ravi.
RE: Conditional copy of values
Dear Gerald, Thanks for the reply. Here is what I was looking for (Google helped me find this): insert into Table2 (rowid) select T1.rowid from Table1 T1 left join Table2 T2 on T1.rowid = T2.rowid where T1.rowid is null and T1.somecolumn = 'somevalue' The 'T1.rowid = null' section prevents duplicate rowid values T2. Thanks again. Ravi. -Original Message- From: Gerald L. Clark [mailto:[EMAIL PROTECTED] Sent: Thursday, 21 September, 2006 18:30 PM To: Ravi Kumar. Cc: mysql@lists.mysql.com Subject: Re: Conditional copy of values Ravi Kumar. wrote: > Dear All, > > I have 2 tables Table1 and Table2. In Table1 rowid is primary key and > in > Table2 rowid is primary key as well as foreign key (referring to rowid > in Table1). > > There are several other columns in Table1, but Table2 has only one col > - rowid. Then what is the reason for table2 ? I see no value for a single element table unless it is used to hold valid entries for another table. You are populating it from Table1, so it now has no reason to exist. Use Table1 instead. > > I want to insert rowid into Table2 conditionally: > > insert into Table2 (rowid) select T1.rowid from Table1 T1, Table2 > T2 where T1.somecolumn = 'some condition' What is T2 doing in this select ? This is not a join, but a Cartesian product. > > But I want to insert only those rowid values which do not exist in T2. Use insert ignore. You already have a primary key on rowid, so you cannot add a rowid that already exists. > > Please help. > > I am using MySQL version 4.1.14-standard-log. Hence subqueries are not > a solution. > > Thanks, > Ravi. > -- Gerald L. Clark Supplier Systems Corporation -- 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: Conditional copy of values
True, but they are very slow in 4.1.14. -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, 26 September, 2006 00:12 AM To: Ravi Kumar. Cc: mysql@lists.mysql.com Subject: Re: Conditional copy of values Ravi Kumar. wrote: > Dear All, > > I have 2 tables Table1 and Table2. In Table1 rowid is primary key and > in > Table2 rowid is primary key as well as foreign key (referring to rowid > in Table1). > > There are several other columns in Table1, but Table2 has only one col > - rowid. > > I want to insert rowid into Table2 conditionally: > > insert into Table2 (rowid) select T1.rowid from Table1 T1, Table2 > T2 where T1.somecolumn = 'some condition' > > But I want to insert only those rowid values which do not exist in T2. > > Please help. > > I am using MySQL version 4.1.14-standard-log. Hence subqueries are not > a solution. You've already got your solution, but, for the record, subqueries ARE supported in mysql 4.1.14. They aren't supported in version prior to 4.1. Michael -- 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]
Regular Exp help
Dear All, I have a table with a varchar column that stores data in this pattern: The pattern may be repeated upto 5 times in same cell, but the string will be different in each case. Hence there will not be a value like this: 1 BW - 2.5 BW - 1 WT - 1 BW - 1 ME - 1 BW - 1 ME - 1.5 SY - 1 BW - 1 WT - 1 OT - 2.5 WE - 1 OT - 1 SY - 1 WT - 1 IT - 1 OT - I need a regular exp or any other query to fetch sum of numbers before a string. For example the result should be 2.5 if I am searching for SY and 3 if I am searching for WT. I tried a bit, but could not succeed. I am using 4.1.14-standard-log. The table is huge, hence the query should be streamlined enough. Please help. Thanks, Ravi.
Installation Issue
I have been noticing following message during mysql startup.the installation was fine. 060213 16:40:58 mysqld started 060213 16:40:59 InnoDB: Started; log sequence number 0 43655 060213 16:40:59 [ERROR] Can't start server : Bind on unix socket: Permission denied 060213 16:40:59 [ERROR] Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? 060213 16:40:59 [ERROR] Aborting 060213 16:40:59 InnoDB: Starting shutdown... 060213 16:41:01 InnoDB: Shutdown completed; log sequence number 0 43655 060213 16:41:01 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 060213 16:41:01 mysqld ended Here is my my.cnf file details [client] # socket = /var/run/mysqld/mysqld.sock socket = /usr/local/mysql/mysql.sock [mysqld] datadir=/usr/local/mysql/data/ port =3306 socket=/var/lib/mysql/mysql.sock user=mysql [mysql.server] #user=mysql basedir=/usr/local/mysql [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Please sugegst . thx +ravi - Brings words and photos together (easily) with PhotoMail - it's free and works with Yahoo! Mail.
Re: Installation Issue
Starting mysql with root.I tried withn mysql user account also but still same error. thanks "Peter M. Groen" <[EMAIL PROTECTED]> wrote: On Tuesday 14 February 2006 00:28, Ravi Kumar wrote: > Permission denied 060213 I assume you are starting MySQL NOT as root.. Has the user write permissions on the directory and the socketfile? -- Peter M. Groen Open Systems Development Klipperwerf 12 2317 DZ Leiden T : +31-(0)71-5216317 M : +31-(0)6-29563390 E : [EMAIL PROTECTED] Skype : peter_m_groen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Mail Use Photomail to share photos without annoying attachments.
Re: Installation Issue
Imran, I noticed couple of permissions were not correct.I changed mysql.mysql. Still Ihave been getting following errors. 060214 15:53:05 mysqld started 060214 15:53:05 InnoDB: Started; log sequence number 0 43655 060214 15:53:06 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 060214 15:53:06 mysqld ended Please advice. thanks Imran Chaudhry <[EMAIL PROTECTED]> wrote: On 2/14/06, Ravi Kumar wrote: > Starting mysql with root.I tried withn mysql user account also but still same > error. > thanks Ravi, Assuming you are starting MySQL with mysqld_safe, then it will invoke the MySQL server as the mysql user. I suspect the cause is that /var/lib/mysql is not owned by mysql If so, as superuser: chown -R mysql:mysql /var/lib/mysql Regards, Imran Chaudhry -- http://www.ImranChaudhry.info MySQL Database Management & Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Yahoo! Mail Use Photomail to share photos without annoying attachments.
Table Size
Hi, What command used to check table size and database size? thanks - Blab-away for as little as 1¢/min. Make PC-to-Phone Calls using Yahoo! Messenger with Voice.
Re: Record Counting
Hi Neil, Try this: SELECT LEFT(DATE_FIELD_NAME, 10), COLOR_FIELD_NAME, COUNT(T.*) AS CNT FROM TABLE_NAME GROUP BY LEFT(DATE_FIELD_NAME, 10), COLOR_FIELD_NAME. Post the table structure if this does not help. Regards, Ravi On 10/27/07, Neil Tompkins <[EMAIL PROTECTED]> wrote: > > Hi, > > I have a table of records all of which have a timestamp against them like > 2007-10-25 10:10:19, and category like red, blue etc and a unique key. > > Using a SELECT statement is it possible to retrieve the count and number > of records for each day e.g 2007-10-25 for all red, and all blue etc > > Thanks. > Neil > _ > Feel like a local wherever you go. > http://www.backofmyhand.com
Re: Record Counting
Sorry a correction SELECT DISTINCT LEFT(DATE_FIELD_NAME, 10), COLOR_FIELD_NAME, COUNT(T.*) AS CNT FROM TABLE_NAME T GROUP BY LEFT(DATE_FIELD_NAME, 10), COLOR_FIELD_NAME Ravi On 10/28/07, Peter Brawley <[EMAIL PROTECTED]> wrote: > > Neil, > > Do you mean ... > > SELECT DATE(datetimecol) AS date,colourcol,COUNT(*) > FROM tbl > GROUP BY date,colourcol; > > PB > > -- > > Neil Tompkins wrote: > > Hi, > > > > I have a table of records all of which have a timestamp against them > like 2007-10-25 10:10:19, and category like red, blue etc and a unique key. > > > > Using a SELECT statement is it possible to retrieve the count and number > of records for each day e.g 2007-10-25 for all red, and all blue etc > > > > Thanks. > > Neil > > _ > > Feel like a local wherever you go. > > http://www.backofmyhand.com > > > > > > > > No virus found in this incoming message. > > Checked by AVG Free Edition. > > Version: 7.5.503 / Virus Database: 269.15.11/1094 - Release Date: > 10/26/2007 8:50 AM > > >
Re: Not showing repeating fields.
select distinct vehicle from On 11/6/07, Jamessketch <[EMAIL PROTECTED]> wrote: > > > Hello, > > I have a table that looks like this: > > vehicle | type| color > car mercedes blue > boat croater silver > plane airbus red > car volvogreen > car BMW white > > and when I you the SELECT statement to write down just vehicles is shows > car > boat plane car car. > How can I have it in a way where repeating ones will NOT be shown? So I > would have written just car boat plane. > > Thank you in advance. > James > -- > View this message in context: > http://www.nabble.com/Not-showing-repeating-fields.-tf4755269.html#a13598356 > Sent from the MySQL - General mailing list archive at Nabble.com. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > >
Re: Use select within delete
Try this (I do not have 4.0.24 to try): delete table1.* from table1, table2 where table1.id = table2.id and date wrote: > > Dario Hernan wrote: > > Hi all I need to delete some fields from a table but in the where > > clause I need to put a select command. > > For instance, delete from table1 where id=(select id from table2 where > > date > Is it possible in mysql 4.0.24?? > > > > Thanks in advance > > Dario > > > > > Not until 4.1. What you can do instead is run the select into a temp > table and then run the delete as a join with that temp table. > > mark > -- > > > > > > > MARK ADDISON > WEB DEVELOPER > > 200 GRAY'S INN ROAD > LONDON > WC1X 8XZ > UNITED KINGDOM > T +44 (0)20 7430 4678 > F > E [EMAIL PROTECTED] > WWW.ITN.CO.UK > > P Please consider the environment. Do you really need to print this > email? > Please Note: > > > > Any views or opinions are solely those of the author and do not > necessarily represent > those of Independent Television News Limited unless specifically stated. > This email and any files attached are confidential and intended solely for > the use of the individual > or entity to which they are addressed. > If you have received this email in error, please notify > [EMAIL PROTECTED] > > Please note that to ensure regulatory compliance and for the protection of > our clients and business, > we may monitor and read messages sent to and from our systems. > > Thank You. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > >
Re: SQL query problem
Dear Mat,
Your mail is not very clear. But I have a feeling that using '%' wildcard in
the like operand should help you
Regards,
Ravi.
On 11/14/07, Matthew Stuart <[EMAIL PROTECTED]> wrote:
>
> Hi, I have built a site with Dreamweaver and I have a problem with a
> query.
>
> I am trying to pass a parameter from one page to another to drill
> down. Basically, I have one product entry that is in multiple
> categories on my website. So, say it's a dress, it is therefore
> related to category 1 which is 'Girls', but it is also more
> specifically related to category 2 which is 'Girls Dresses'.
>
> The way I have set this up is to have a column called MultiCategoryID
> that holds both the number 1 and 2 like this: /1/2/
>
> When a user clicks a link to look at dresses, the parameter 2 is
> passed, but my query on the result page is wrong in some way because
> no records are displaying even though there is content to display.
> This is what I have so far:
>
> SELECT *
> FROM Products
> WHERE MultiCategoryID LIKE '/catdrill/'
> ORDER BY ProductID DESC
>
> The parameter settings are:
> Name: catdrill
> Type: Numeric
> Value: Request("MCID") MCID is the url parameter being passed
> Default value: 2
>
> Only when I test the Default value with an exact match of /1/2/ does
> any product display. What have I done wrong here? Is there a way to
> get it to recognise that I want it to pick specific numbers between
> the slashes rather than the whole lot? I have tried to change the
> slashes to full stops just in case they are causing problems, but
> it's still giving the same problem.
>
> Thanks.
>
> Mat
>
>
Update query help
Dear Friends, I have two tables: T1, T2. T1 has 3 columns: playerid, gameid, score T2 has 2 columns: playerid, totalscore. I wish to update table T2 such that sum of T1.score of each player, gets updated in T2.totalscore. It may be something like this: update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid = T2.playerid OR update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid = T2.playerid group by playerid However none of the above is working. Where am I wrong? Please help. The version of MySQL I am using is 4.1.14-standard-log. Thanks, Ravi.
Thread safety
Dear All: I have a table T1 with these columns: location - varchar odd_even - varchar I have a php page that is called from VC applications installed in 5 different locations (L1, L2, L3, L4 and L5). The page returns 0 or 1 depending on even call or odd call from each location. (1st, 3rd, 5th, 7th... page calls are odd and 2, 4, 6, 8 are even.) The page has this logic: select odd_even from T1 where location = '$location' if (odd_even == 'odd') update T1 set odd_even = 'even' where location = '$location' and odd_even = 'odd'; return '0'; else update T1 set odd_even = 'odd' where location = '$location' and odd_even = 'even'; return '1'; But since each location calls the page in very quick succession (a location have have several parallel calls running - its a multi threaded app), there is a race condition. And at times several continuous calls return same result (odd or even). How do I achieve thread safety? Please help. Its urgent. Thanks. Ravi.
Replication among disconnected Servers
Hi All, We have a MySQL 5.0 server (master) running on Windows. For backup, I wish to setup another system with same version of MySQL to act as backup / slave. But this slave can not have direct access to the master. Is there a way I can achive this? I do not wish to take a full dump in master. Would it work, if I take the binary replication logs manually from master and "provide" it to slave? Any other way to achieve desired results. Thanks, Ravi. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication among disconnected Servers
Can any one help me please? Ravi Kumar. wrote: Hi All, We have a MySQL 5.0 server (master) running on Windows. For backup, I wish to setup another system with same version of MySQL to act as backup / slave. But this slave can not have direct access to the master. Is there a way I can achive this? I do not wish to take a full dump in master. Would it work, if I take the binary replication logs manually from master and "provide" it to slave? Any other way to achieve desired results. Thanks, Ravi. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join & update help
Dear All, I have two tables T1 and T2. T1 has these columns: userid, password (userid is primary key) T2 has these columns: sessionid, userid, logintime, logofftime (sessionid is primary key) I wish to add one column in T1 called last_login_time. I want to populate this new column with max(logintime) from T2 for each user. How do I do this? I tried variants of this: update T1, T2 set last_login_time = max(logintime) where T1.userid = T2.userid. Thanks Ravi. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data Migration between 2 different schemas
Hi !! I am new to My SQL. I have got a requirement where data existing in one schema has to be migrated to another. Both schemas look alike except some minor changes in the table definitions here and there. Does MySQL provide some kind of tools by default for these sort of things or we need to go for third party tools? Please let me know. Thankx in Advance, Mahanti. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
