Re: Impact of making a stored program transactional while running under transaction manager?
David Karr wrote: it's supposedly good advice in a multi-step stored procedure to have an explicit start transaction and commit wrapping the work. What is the impact of doing this if the stored procedure is called from code managed by a transaction manager. For instance, in a JEE appserver and a transaction manager? Impact should be NONE. If AUTOCOMMIT is already OFF then the BEGIN TRANSACION should have 0 effect, now as to the COMMIT - you need to darn sure what the STATE of things are vis-avis your TRANSACTION Manger, as i could possibly be setting AUTOCOMMIT off and the COMMIT: wil turn it ON! I think you are , at some point, likely to chop your wang off mixing 2 different Transaction control mechanisms. For example - Which one handles ROLLBACKS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to get Error Number and Error Message 2nd Try
Paul DuBois wrote: On Feb 10, 2009, at 11:26 PM, Al wrote: I know that all of the prgramming interfaces have the ability to issue a Function Call to get error number for the most recently invoked MySQL function and the error message for the most recently invoked MySQL function such as in C using *mysql_errno() etc. BUT * Surely there is a way to get the SAME info via a SQL statment or function I know one can do a ** select @@error_count as errorcount; to get the COUNT of errors, Surely there is SOMETHING similar to get the latest ERROR NUMBER and latest ERROR MESSAGE, SHOW WARNINGS might help. http://dev.mysql.com/doc/refman/5.1/en/show-warnings.html Indeed it will, bu if you need via a SQL QUERY to return that info, HOW can one get to it. I mean since I can use Straight SQL to get to the COUNT of errors, it just seems there would HAVE to be a FUNCTION or USER_VARIABLE that would contain this info Has Anybody found such a way THX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to get Error Number and Error Message
I know that all of the prgramming interfaces have the ability to issue a Function Call to get error number for the most recently invoked MySQL function and the error message for the most recently invoked MySQL function such as in C using *mysql_errno() etc. BUT * Surely there is a way to get the SAME info via a SQL statment or function I know one can do a ** select @@error_count as errorcount; to get the COUNT of errors, Surely there is SOMETHING similar to get the latest ERROR NUMBER and latest ERROR MESSAGE, but I have looked everywhere. Has anyone seen a way to do this THANKS a LOT for any and all help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to get Error Number and Error Message 2nd Try
I know that all of the prgramming interfaces have the ability to issue a Function Call to get error number for the most recently invoked MySQL function and the error message for the most recently invoked MySQL function such as in C using *mysql_errno() etc. BUT * Surely there is a way to get the SAME info via a SQL statment or function I know one can do a ** select @@error_count as errorcount; to get the COUNT of errors, Surely there is SOMETHING similar to get the latest ERROR NUMBER and latest ERROR MESSAGE, but I have looked everywhere. Has anyone seen a way to do this THANKS a LOT for any and all help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Database for a library
Hello, I'm building a web application for a library but I faced a problem ... NOTICE: This web app includes a feature that you can read the books online ... I have to save the books either in the database (mysql for sure) or on the hard disks ... but I preferred DB choice because of many befits (search, speed, time and many others) ... The problem is how to save the book, till now I have three choices: 1- save the whole book in one record of type long text (downside is the bandwidth ,,, it requires a lot of bandwidth to transfer the whole book to the user) 2- save each chapter of each book in a record 3- save each page of each book in one record (the downside is the huge amount of records but the benefits are saving bandwidth and easier pagination) I prefer the third choice but is mysql capable of handling this huge amount of records (it maybe be millions because I have over 10,000 book) thanks in advanced
Newbie Q- Mac OSX install - login to mysql not working...
(Sorry if this is a duplicate post, email issues w/multiple accts) I have a MacBook Leopard OS 10.5.1 2 GB RAM. I've got a good amount of knowledge of computer systems, but haven't setup or worked with mySQL before. (usually use it preinstalled on my hosting service). I downloaded the package and installed it via the automated setup that comes for the MacOS. It started fine. The MySQL Sys prefs tool shows the status of the server as running. However, I want to now setup Wordpress on my localhost. I need to create a database on mysql. I opened the shell and navigate to the location of mysql. I have been using the instructions as shown below. $ mysql -u adminusername -p Enter password: This is where I'm stuck. No matter what account I use, I am unable to login to the database. My personal account is the only account on the machine, while it's not root, it has full admin privs, and I've tried everything to login, including no password, my account's password, etc. Since I've not activated root on this machine (I understand that Apple, by default disables it), perhaps there's something I'm missing in my lack of knowledge of the way the Mac OS uses root? I'm sure this is a stupid newbie issue, but I'm a bit wrapped around the axle here. Do I need to use sudo? (I've even attempted this with no success). I've searched on a few lists to see if anyone has had this problem, but not seen it yet. Should I just reinstall? Am I just missing something that passed by in install (I don't remember adding a password other than entering the password that I use to admin the machine. Thoughts? Thanks in advance for any help.
Revoking Privileges
The version I'm using is: Your MySQL connection id is 6 to server version: 4.1.22 I log on as root. I then: mysql show grants for 'WP_INT_BASEBALL'@'localhost'; ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT USAGE ON *.* TO 'WP_INT_BASEBALL'@'localhost' IDENTIFIED BY PASSWORD '*37638FAC6D9ED84696E9504AC8EA4495EA97A908' | | GRANT ALL PRIVILEGES ON `WP_INT_BASEBALL`.* TO 'WP_INT_BASEBALL'@'localhost' | ++ 2 rows in set (0.00 sec) mysql REVOKE USAGE ON *.* FROM 'WP_INT_BASEBALL'@'localhost' IDENTIFIED BY PASSWORD '*37638FAC6D9ED84696E9504AC8EA4495EA97A908'; Query OK, 0 rows affected (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) When I do another SHOW GRANTS: mysql show grants for 'WP_INT_BASEBALL'@'localhost'; ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT USAGE ON *.* TO 'WP_INT_BASEBALL'@'localhost' IDENTIFIED BY PASSWORD '*37638FAC6D9ED84696E9504AC8EA4495EA97A908' | | GRANT ALL PRIVILEGES ON `WP_INT_BASEBALL`.* TO 'WP_INT_BASEBALL'@'localhost' | ++ 2 rows in set (0.00 sec) So what am I doing wrong? Why isn't that gone? === Al -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating a Table With a Default
I'm trying to install ProBIND, and I'm running mysql 4.1.20. One of the ProBIND install scripts calls for tables to be created in MySQL, and I've culled it down to this: mysql CREATE TABLE zones ( - id INT(11) DEFAULT '1' NOT NULL AUTO_INCREMENT, - PRIMARY KEY (id) - ); ERROR 1067 (42000): Invalid default value for 'id' I've tried double quotes, changing it to INT(1), and still I get that error. Any hints? === Al -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating a Table With a Default
It makes no sense to use a default value with an auto_increment attribute, which means, the default value is the auto-incremented value. Carlos Hmmm... I see your point. I sort of figured that the default was an initial value, and from there, it incremented when accessed. Sort of weird that ProBIND didn't change the script. === Al -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sum with update
Hi Everyone I have a question regarding SUM and Update. is it possible to use SUM with Update ? for example I have 3 tables table_1: idT1 Price 120 230 350 420 table_2: idCust Total idT1 2 3011 2 2 221 1 4 2 2 3 31 1 2 302 4 2 30 1 table_3: idCust Account 2 200 3 19 399 I want to update table_3.Account**IF** (table_3.Account + SUM(table_2.Total* table_1.Price) ) small than 1000 for each customer (idCust) is this possible using only SQL or I have to do it in programming ? Thanks -- echo Hello World :)
old-password issue with mysqldump
Hi everyone
I'm using mysqld with option old-passwords in my server (let's call
it serverA)
and in my application I use UPDATE . SET
USERPWD=PASSWORD('ABCD') to set the users password
now I want to move my database to another server (serverB) and the
mysqld in this new server doesn't run with old-passwords
so I dumped the databases from the first server (serverA) to a file
using mysqldump command and I imported it to the new server (serverB)
with command mysql source database.sql
now no one from my users can access to his account and I think it's
because the old-passwords is not enabled in the new server (serverB)
I can enable old-passwords in the new server but I don't want to do
that (the new password format is more secure and better, right ?).
so is there anyway to convert all the password in my database to the
new password format ?
Thanks
--
echo Hello World :)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: old-password issue with mysqldump
Hi
Thanks, this is easy and simple if I just have few users :) I have
more than 30,000 users :)
I think there is no way to do this so I have to use old-passwords
option in the new servers :)
Thanks
On 12/11/06, Daniel da Veiga [EMAIL PROTECTED] wrote:
On 12/11/06, Ahmad Al-Twaijiry [EMAIL PROTECTED] wrote:
Hi everyone
I'm using mysqld with option old-passwords in my server (let's call
it serverA)
and in my application I use UPDATE . SET
USERPWD=PASSWORD('ABCD') to set the users password
now I want to move my database to another server (serverB) and the
mysqld in this new server doesn't run with old-passwords
so I dumped the databases from the first server (serverA) to a file
using mysqldump command and I imported it to the new server (serverB)
with command mysql source database.sql
now no one from my users can access to his account and I think it's
because the old-passwords is not enabled in the new server (serverB)
I can enable old-passwords in the new server but I don't want to do
that (the new password format is more secure and better, right ?).
so is there anyway to convert all the password in my database to the
new password format ?
Start your new server with the --old-passwords option, login with your
superuser (root or whatever you call it) and change its password to
the new format:
SET PASSWORD FORv 'some_user'@'some_host' = PASSWORD('newpwd');
Then you can logout and restart your server without the
--old-passwords option, and still login with the superuser, and with
this account, you can set all your users passwords to the new format
with a command just like the one above, check:
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html
And you may find all information about passwords there.
--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
echo Hello World :)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deadlock
YES :) after removing CustomerID and OrderID from Primary index to unique index, every thing is working fine and I don't get any deadlock anymore :) Thanks Everyone :) On 11/16/06, Asif Lodhi [EMAIL PROTECTED] wrote: Hi Ahmad, On 11/13/06, Ahmad Al-Twaijiry [EMAIL PROTECTED] wrote: also I want to mention that I have 3 primary key in my table: ShopID CustomerID OrderID Could this be the problem ? Yes, may be. Cause I am sure I have read somewhere in the docs that having multiple cols in the primary key of a single table slows down your updates. You should consider replacing the primary key with a unique index. Primary keys are good for single columns. -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- echo Hello World :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
float numbers
Ho everyone I have a column in a table defined as float mynumber float(20,2) if we say mynumber column in a row is 100 , when I run this SQL : UPDATE Table SET mynumber=mynumber-100.15 the mynumber column will be 00 not 999899.85 what is the problem ? -- echo Hello World :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: float numbers
Thanks Dan Mos I have many tables that use float (in production database), if I convert all of the float column to DOUBLE or DECIMAL (using alter ), is there any impact or anything I should know that could happen to me ? is there any page that describe the differences between FLOAT, DECIMAL and DOUBLE in MySQL 5.0.* ? Thanks On 11/18/06, mos [EMAIL PROTECTED] wrote: At 03:29 PM 11/17/2006, Ahmad Al-Twaijiry wrote: Ho everyone I have a column in a table defined as float mynumber float(20,2) if we say mynumber column in a row is 100 , when I run this SQL : UPDATE Table SET mynumber=mynumber-100.15 the mynumber column will be 00 not 999899.85 what is the problem ? Ho Ho Ho, Float only uses 4 bytes and does not have much precision so you'll get rounding. Try DOUBLE instead. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- echo Hello World :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deadlock
Hi everyone,
Everyday I got around 10 Deadlock errors in my database :
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when
trying to get lock; try restarting transaction
SQL=UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105
I'm using Innodb engine type for my tables and I already checked
http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html
I know this is not dangerous but is there anyway to prevent it ? I
recheck my script and I couldn't find any problem on it
my question is this deadlock because there is another transaction that
lock the row or the table ? I want to know if this error because row
locking or table locking
my code is something like this (PHP5) :
?php
try{
$dblink-beginTransaction();
$sql=UPDATE Shop SET Total=Total-$q WHERE
CustomerID= . $CustomerID. AND OrderID= . $OrderID;
$dblink-exec($sql);
}catch (PDOException $e) {
// deadlock , report it
$dblink-rollBack();
}
?
I also have this code in another script (with another mysql username)
that will go over all record in Shop table and do some update in the
records.
Here is what I got when I execute SHOW ENGINE INNODB STATUS
061104 13:53:02
*** (1) TRANSACTION:
TRANSACTION 0 3020198, ACTIVE 0 sec, process no 12031, OS thread id
2584136624 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 42 lock struct(s), heap size 5504
MySQL thread id 110727, query id 3714030 localhost user2 Updating
UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 35335 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020198 lock_mode X waiting
Record lock, heap no 292 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0
0: len 4; hex 3471; asc 4q;; 1: len 4; hex 06cc; asc
;; 2: len 4; hex 0060; asc`;; 3: len 6; hex 002e1597; asc
. ;; 4: len 7; hex 00801026f1; asc ;; 5: len 4; hex
0c61; asca;; 6: len 4; hex d723a142; asc # B;; 7: len 8; hex
8000123ed6edf88a; asc;;
*** (2) TRANSACTION:
TRANSACTION 0 3020183, ACTIVE 1 sec, process no 12031, OS thread id
2588175280 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
30 lock struct(s), heap size 2496, undo log entries 26
MySQL thread id 110721, query id 3714111 localhost user2 Updating
UPDATE Shop SET Total=Total+1,Price=27.67 WHERE ShopID=9268
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 35335 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020183 lock_mode X
Record lock, heap no 292 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0
0: len 4; hex 3471; asc 4q;; 1: len 4; hex 06cc; asc
;; 2: len 4; hex 0060; asc`;; 3: len 6; hex 002e1597; asc
. ;; 4: len 7; hex 00801026f1; asc ;; 5: len 4; hex
0c61; asca;; 6: len 4; hex d723a142; asc # B;; 7: len 8; hex
8000123ed6edf88a; asc;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 13428 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020183 lock_mode X waiting
Record lock, heap no 59 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0 0: len 4; hex 2434; asc $4;; 1: len 4; hex 029c;
asc ;; 2: len 4; hex 005d; asc];; 3: len 6; hex
002e0aa8; asc. ;; 4: len 7; hex 00800f27f5; asc ' ;;
5: len 4; hex 160d; asc ;; 6: len 4; hex 295cdd41; asc )\ A;;
7: len 8; hex 8000123ed6cf3331; asc 31;;
--
echo Hello World :)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deadlock
Sorry
I have $dblink-commit(); right after $dblink-exec($sql); but I
forgot to write it here (my mistake, sorry )
also I want to mention that I have 3 primary key in my table:
ShopID
CustomerID
OrderID
Could this be the problem ?
On 11/13/06, Rolando Edwards [EMAIL PROTECTED] wrote:
You should play it safe and add $dblink-commit(); right after
$dblink-exec($sql);
The reason for this is from Page 419 of the
MySQL 5.0 Certification Study Guide bullet point #3:
During the course of a transaction, InnoDB may acquire row locks
AS IT DISCOVERS THEM TO BE NECESSARY.
I don't like the sound of that statament, because it is assumed
that PDO will attempt an autocommit with each SQL statment.
That is not so with a transaction in PDO. Note the explanation
for PDO::commit (http://us3.php.net/manual/en/function.pdo-commit.php):
bool PDO::commit ( void )
Commits a transaction, returning the database connection to autocommit mode
until the next call to PDO::beginTransaction() starts a new transaction.
That means that autocommit is disabled when you issue
$dblink-beginTransaction();
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Monday, November 13, 2006 9:34:37 AM GMT-0500 US/Eastern
Subject: Deadlock
Hi everyone,
Everyday I got around 10 Deadlock errors in my database :
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when
trying to get lock; try restarting transaction
SQL=UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105
I'm using Innodb engine type for my tables and I already checked
http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html
I know this is not dangerous but is there anyway to prevent it ? I
recheck my script and I couldn't find any problem on it
my question is this deadlock because there is another transaction that
lock the row or the table ? I want to know if this error because row
locking or table locking
my code is something like this (PHP5) :
?php
try{
$dblink-beginTransaction();
$sql=UPDATE Shop SET Total=Total-$q WHERE
CustomerID= . $CustomerID. AND OrderID= . $OrderID;
$dblink-exec($sql);
}catch (PDOException $e) {
// deadlock , report it
$dblink-rollBack();
}
?
I also have this code in another script (with another mysql username)
that will go over all record in Shop table and do some update in the
records.
Here is what I got when I execute SHOW ENGINE INNODB STATUS
061104 13:53:02
*** (1) TRANSACTION:
TRANSACTION 0 3020198, ACTIVE 0 sec, process no 12031, OS thread id
2584136624 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 42 lock struct(s), heap size 5504
MySQL thread id 110727, query id 3714030 localhost user2 Updating
UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 35335 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020198 lock_mode X waiting
Record lock, heap no 292 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0
0: len 4; hex 3471; asc 4q;; 1: len 4; hex 06cc; asc
;; 2: len 4; hex 0060; asc`;; 3: len 6; hex 002e1597; asc
. ;; 4: len 7; hex 00801026f1; asc ;; 5: len 4; hex
0c61; asca;; 6: len 4; hex d723a142; asc # B;; 7: len 8; hex
8000123ed6edf88a; asc;;
*** (2) TRANSACTION:
TRANSACTION 0 3020183, ACTIVE 1 sec, process no 12031, OS thread id
2588175280 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
30 lock struct(s), heap size 2496, undo log entries 26
MySQL thread id 110721, query id 3714111 localhost user2 Updating
UPDATE Shop SET Total=Total+1,Price=27.67 WHERE ShopID=9268
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 35335 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020183 lock_mode X
Record lock, heap no 292 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0
0: len 4; hex 3471; asc 4q;; 1: len 4; hex 06cc; asc
;; 2: len 4; hex 0060; asc`;; 3: len 6; hex 002e1597; asc
. ;; 4: len 7; hex 00801026f1; asc ;; 5: len 4; hex
0c61; asca;; 6: len 4; hex d723a142; asc # B;; 7: len 8; hex
8000123ed6edf88a; asc;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 13428 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020183 lock_mode X waiting
Record lock, heap no 59 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0 0: len 4; hex 2434; asc $4;; 1: len 4; hex 029c;
asc ;; 2: len 4; hex 005d; asc];; 3: len 6; hex
002e0aa8; asc. ;; 4: len 7; hex 00800f27f5; asc ' ;;
5: len 4; hex 160d; asc ;; 6: len 4; hex 295cdd41; asc )\ A;;
7: len 8; hex 8000123ed6cf3331; asc 31;;
--
echo Hello World :)
--
MySQL General Mailing List
For list archives: http
Re: MAX + SUM in one query
but what if I want also to include another table
for example, can I do this :
SELECT R.Stock_StockID,S.StockName, MAX(R.RequestPrice),
SUM(R.RequestTotal) FROM
Request R, StocksT S WHERE S.StockID=R.Stock_StockID AND
R.RequestType='Offer' AND
R.RequestENDDate=Date(now()) GROUP BY R.Stock_StockID
Also remember I want to get the SUM of Total of the records that
RequestPrice=MAX(RequestPrice)
for example, if the MAX(RequestPrice)=200 then I just need the Total
SUM of the records that there RequestPrice=200
I don't need the SUM for all the records
Thanks
On 11/5/06, Visolve DB Team [EMAIL PROTECTED] wrote:
Hi
That's fine.
But for the query, I have created a simple table which simulates as that of
yours. I have used simple domain names.
I typed the StockID as RequestID. Nothing morethan that. But it gives
solution for your query.
SELECT R.Stock_StockID,MAX(R.RequestPrice), SUM(R.RequestTotal) FROM
Request R WHERE R.RequestType='Offer' AND
R.RequestENDDate=Date(now()) GROUP BY R.Stock_StockID
solution:
select StockID, sum(RequestTotal),max(RequestPrice) from test where
RequestType='offer' group by StockID;
Pls have a look into the table and the output for the query.
Thanks
ViSolve DB Team.
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: Visolve DB Team [EMAIL PROTECTED]
Cc: MySQL List mysql@lists.mysql.com
Sent: Monday, November 06, 2006 12:10 PM
Subject: Re: MAX + SUM in one query
Hi
no R.RequestENDDate=Date(now()) will work fine (I use it in other sql
queries)
also as you can see in my sql, I want to group using Stock_StockID ,
so your solution will not work with me
On 11/5/06, Visolve DB Team [EMAIL PROTECTED] wrote:
Hi,
The query what you tried will return empty set only, since you have
compared
the RequestENDDate with now(), which always returns false[due to
seconds].
Try extracting the date part alone from RequestENDDate for the Where
cond.
otherwise the query do well:
select RequestID, sum(RequestTotal),max(RequestPrice) from test where
RequestType='offer' group by RequestID;
Test table:
mysql select * from t;
+--++--+---+-+
| id | idtype | tot | price | d |
+--++--+---+-+
| 10 | off| 200 | 14| 2006-11-06 10:49:36 |
| 10 | off| 100 | 22| 2006-11-06 10:49:36 |
| 10 | off| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | off| 200 | 14| 2006-11-06 10:49:36 |
| 11 | off| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | bi| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | bi| 120 | 4 | 2006-11-06 10:49:36 |
+--++--+---+-+
output:
+--+--++
| id | sum(tot) | max(price) |
+--+--++
| 10 | 420 | 22 |
| 11 | 320 | 14 |
+--+--++
2 rows in set (0.01 sec)
Thanks,
ViSolve DB Team.
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Monday, November 06, 2006 8:58 AM
Subject: MAX + SUM in one query
Hi everyone
I have the following the table :
CREATE TABLE `Request` (
`RequestID` int(10) unsigned NOT NULL auto_increment,
`Stock_StockID` int(10) unsigned NOT NULL default '0',
`RequestType` enum('Bid','Offer') NOT NULL default 'Bid',
`RequestTotal` int(10) unsigned NOT NULL default '0',
`RequestPrice` float(10,2) NOT NULL default '1.00',
`RequestENDDate` datetime NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`RequestID`,`Customer_CustID`,`Stock_StockID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
The data in the table :
RequestID Stock_StockID RequestType RequestTotal RequestPrice
RequestENDDate
__
1 10Offer 2000 300
now()
2 10Offer 100 300
now()
3 10Offer 3010
now()
4 10Bid 210 100
now()
5 11Offer 3010
now()
6 10Offer 3010
now()
7 10Offer 5030
now()
Now my question is how can I get the MAX(RequestPrice) and the
SUM(RequestTotal) (WHERE MAX(RequestPrice) ) and where
RequestType=Offer for each Stock_StockID
I tried this
SELECT R.Stock_StockID,MAX(R.RequestPrice), SUM(R.RequestTotal) FROM
Request R WHERE R.RequestType='Offer' AND
R.RequestENDDate=Date(now()) GROUP BY R.Stock_StockID
but it doesn't work.
Anyone know how to do it ?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com
Re: MAX + SUM in one query
This didn't work.
To explain in easy way, let say I have this table
ID, OrderID, Price, Total, Type
1 200 100 20 Offer
2 200 700 40 Offer
3 200 700 30 Offer
4 300 100 60 Offer
5 300 500 80 Offer
The result should be like this (when GROUP BY OrderID, I don't care
about the ID):
result record #1:
OrderID=200MAX(Price)=700 SUM(Total)=70 --- 70 from 40+30
result record #2
OrderID=300MAX(Price)=500 SUM(Total)=80 -- we have only one row
with the MAX(Price)=500 and OrderID=300 and the total or if is 80
Thanks
On 11/6/06, Visolve DB Team [EMAIL PROTECTED] wrote:
Hi
The query will work and should.
As of our understanding,
you want to retrieve the sum of the total column for a particular id, with
its max price where the type='offer' and id same for both the tables.
Is it so?
Thanks
ViSolve DB Team.
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: Visolve DB Team [EMAIL PROTECTED]
Cc: MySQL List mysql@lists.mysql.com
Sent: Monday, November 06, 2006 2:33 PM
Subject: Re: MAX + SUM in one query
but what if I want also to include another table
for example, can I do this :
SELECT R.Stock_StockID,S.StockName, MAX(R.RequestPrice),
SUM(R.RequestTotal) FROM
Request R, StocksT S WHERE S.StockID=R.Stock_StockID AND
R.RequestType='Offer' AND
R.RequestENDDate=Date(now()) GROUP BY R.Stock_StockID
Also remember I want to get the SUM of Total of the records that
RequestPrice=MAX(RequestPrice)
for example, if the MAX(RequestPrice)=200 then I just need the Total
SUM of the records that there RequestPrice=200
I don't need the SUM for all the records
Thanks
On 11/5/06, Visolve DB Team [EMAIL PROTECTED] wrote:
Hi
That's fine.
But for the query, I have created a simple table which simulates as that
of
yours. I have used simple domain names.
I typed the StockID as RequestID. Nothing morethan that. But it gives
solution for your query.
SELECT R.Stock_StockID,MAX(R.RequestPrice), SUM(R.RequestTotal) FROM
Request R WHERE R.RequestType='Offer' AND
R.RequestENDDate=Date(now()) GROUP BY R.Stock_StockID
solution:
select StockID, sum(RequestTotal),max(RequestPrice) from test where
RequestType='offer' group by StockID;
Pls have a look into the table and the output for the query.
Thanks
ViSolve DB Team.
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: Visolve DB Team [EMAIL PROTECTED]
Cc: MySQL List mysql@lists.mysql.com
Sent: Monday, November 06, 2006 12:10 PM
Subject: Re: MAX + SUM in one query
Hi
no R.RequestENDDate=Date(now()) will work fine (I use it in other sql
queries)
also as you can see in my sql, I want to group using Stock_StockID ,
so your solution will not work with me
On 11/5/06, Visolve DB Team [EMAIL PROTECTED] wrote:
Hi,
The query what you tried will return empty set only, since you have
compared
the RequestENDDate with now(), which always returns false[due to
seconds].
Try extracting the date part alone from RequestENDDate for the Where
cond.
otherwise the query do well:
select RequestID, sum(RequestTotal),max(RequestPrice) from test where
RequestType='offer' group by RequestID;
Test table:
mysql select * from t;
+--++--+---+-+
| id | idtype | tot | price | d |
+--++--+---+-+
| 10 | off| 200 | 14| 2006-11-06 10:49:36 |
| 10 | off| 100 | 22| 2006-11-06 10:49:36 |
| 10 | off| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | off| 200 | 14| 2006-11-06 10:49:36 |
| 11 | off| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | bi| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | bi| 120 | 4 | 2006-11-06 10:49:36 |
+--++--+---+-+
output:
+--+--++
| id | sum(tot) | max(price) |
+--+--++
| 10 | 420 | 22 |
| 11 | 320 | 14 |
+--+--++
2 rows in set (0.01 sec)
Thanks,
ViSolve DB Team.
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Monday, November 06, 2006 8:58 AM
Subject: MAX + SUM in one query
Hi everyone
I have the following the table :
CREATE TABLE `Request` (
`RequestID` int(10) unsigned NOT NULL auto_increment,
`Stock_StockID` int(10) unsigned NOT NULL default '0',
`RequestType` enum('Bid','Offer') NOT NULL default 'Bid',
`RequestTotal` int(10) unsigned NOT NULL default '0',
`RequestPrice` float(10,2) NOT NULL default '1.00',
`RequestENDDate` datetime NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`RequestID`,`Customer_CustID`,`Stock_StockID`),
) ENGINE=InnoDB
MAX + SUM in one query
Hi everyone
I have the following the table :
CREATE TABLE `Request` (
`RequestID` int(10) unsigned NOT NULL auto_increment,
`Stock_StockID` int(10) unsigned NOT NULL default '0',
`RequestType` enum('Bid','Offer') NOT NULL default 'Bid',
`RequestTotal` int(10) unsigned NOT NULL default '0',
`RequestPrice` float(10,2) NOT NULL default '1.00',
`RequestENDDate` datetime NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`RequestID`,`Customer_CustID`,`Stock_StockID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
The data in the table :
RequestID Stock_StockID RequestType RequestTotal RequestPrice
RequestENDDate
__
1 10Offer 2000 300 now()
2 10Offer 100 300 now()
3 10Offer 3010 now()
4 10Bid 210 100 now()
5 11Offer 3010 now()
6 10Offer 3010 now()
7 10Offer 5030 now()
Now my question is how can I get the MAX(RequestPrice) and the
SUM(RequestTotal) (WHERE MAX(RequestPrice) ) and where
RequestType=Offer for each Stock_StockID
I tried this
SELECT R.Stock_StockID,MAX(R.RequestPrice), SUM(R.RequestTotal) FROM
Request R WHERE R.RequestType='Offer' AND
R.RequestENDDate=Date(now()) GROUP BY R.Stock_StockID
but it doesn't work.
Anyone know how to do it ?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAX + SUM in one query
Hi
no R.RequestENDDate=Date(now()) will work fine (I use it in other sql queries)
also as you can see in my sql, I want to group using Stock_StockID ,
so your solution will not work with me
On 11/5/06, Visolve DB Team [EMAIL PROTECTED] wrote:
Hi,
The query what you tried will return empty set only, since you have compared
the RequestENDDate with now(), which always returns false[due to seconds].
Try extracting the date part alone from RequestENDDate for the Where cond.
otherwise the query do well:
select RequestID, sum(RequestTotal),max(RequestPrice) from test where
RequestType='offer' group by RequestID;
Test table:
mysql select * from t;
+--++--+---+-+
| id | idtype | tot | price | d |
+--++--+---+-+
| 10 | off| 200 | 14| 2006-11-06 10:49:36 |
| 10 | off| 100 | 22| 2006-11-06 10:49:36 |
| 10 | off| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | off| 200 | 14| 2006-11-06 10:49:36 |
| 11 | off| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | bi| 120 | 4 | 2006-11-06 10:49:36 |
| 11 | bi| 120 | 4 | 2006-11-06 10:49:36 |
+--++--+---+-+
output:
+--+--++
| id | sum(tot) | max(price) |
+--+--++
| 10 | 420 | 22 |
| 11 | 320 | 14 |
+--+--++
2 rows in set (0.01 sec)
Thanks,
ViSolve DB Team.
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Monday, November 06, 2006 8:58 AM
Subject: MAX + SUM in one query
Hi everyone
I have the following the table :
CREATE TABLE `Request` (
`RequestID` int(10) unsigned NOT NULL auto_increment,
`Stock_StockID` int(10) unsigned NOT NULL default '0',
`RequestType` enum('Bid','Offer') NOT NULL default 'Bid',
`RequestTotal` int(10) unsigned NOT NULL default '0',
`RequestPrice` float(10,2) NOT NULL default '1.00',
`RequestENDDate` datetime NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`RequestID`,`Customer_CustID`,`Stock_StockID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
The data in the table :
RequestID Stock_StockID RequestType RequestTotal RequestPrice
RequestENDDate
__
1 10Offer 2000 300
now()
2 10Offer 100 300
now()
3 10Offer 3010
now()
4 10Bid 210 100
now()
5 11Offer 3010
now()
6 10Offer 3010
now()
7 10Offer 5030
now()
Now my question is how can I get the MAX(RequestPrice) and the
SUM(RequestTotal) (WHERE MAX(RequestPrice) ) and where
RequestType=Offer for each Stock_StockID
I tried this
SELECT R.Stock_StockID,MAX(R.RequestPrice), SUM(R.RequestTotal) FROM
Request R WHERE R.RequestType='Offer' AND
R.RequestENDDate=Date(now()) GROUP BY R.Stock_StockID
but it doesn't work.
Anyone know how to do it ?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Ahmad
http://www.v-tadawul.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
transaction in mysql 5
Hi everyone I have a PHP script that will run every minute and do a lot of SELECT and UPDATE statments All my tables are InnoDB and I'm using PHP 5 and POD class ( http://php.net/pod ) to connect to mysql, in my script I start the transaction (using method beginTransaction() ) in the beginning of the script and commit in the end of the script ( also rollback if there is any problem) As I said , this script run every one minute, my question is : what will happen if I run the script more than one in the same time (like if I run it as a thread) ? is it possible that one of my script process will select the same data that the other process is selecting or updating ? or if I run a second process of my script it will not be able to read/write anything until the first script commit to the database or rollback ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Timezone
Hi everyone is it possible in Mysql 5.0.1 to set the timezone for a user ? PS: I don't have root access to mysql, so I'm looking for away to do it as a normal user. --- Ahmad http://www.v-tadawul.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reports
Hi everyone do you know any good (free) application that make it easy to generate (with charts) a very nice reports from mysql ? Thanks --- Ahmad http://www.v-tadawul.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
list of cols that I need to index
Hi is there anyway or command to run it against a production table to see if there is any column that I should think about indexing it remember this is a production database, so I can't run it in debug mode and I don't have a root access to the database (I'm just a developer). Thanks -- Ahmad Fahad AlTwaijiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FreeBSD 6.1 + Libthr + MySQL 5.0.24a max connection issue or bug?
Heya! This fixed it for me, I didn't set kern.threads.max_groups_per_proc: earlier. What is the max threads can FreeBSD go for? Thanks, -A - Original Message From: Ken Menzel [EMAIL PROTECTED] To: Abdullah Ibn Hamad Al-Marri [EMAIL PROTECTED]; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Sent: Wednesday, October 4, 2006 11:59:27 PM Subject: Re: FreeBSD 6.1 + Libthr + MySQL 5.0.24a max connection issue or bug? Have a look at kern.threads.max_threads_per_proc: 1500 and kern.threads.max_groups_per_proc: 1500 You are probably hitting one of these sysctl's. Here is a link to some other info http://unix.derkeiler.com/Mailing-Lists/FreeBSD/performance/2006-01/msg2.html There is also a wiki on Freebsd and MySQL at http://wikitest.freebsd.org/MySQL Hope this helps, Ken - Original Message - From: Abdullah Ibn Hamad Al-Marri [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 4:13 PM Subject: FreeBSD 6.1 + Libthr + MySQL 5.0.24a max connection issue or bug? Hello folks, I hope Greg is reading this list I use MySQL 5.0.24a from the FreeBSD ports, with libthr threading. I have a huge app makes alot of connections to MySQL server. The max I could reach is 1500 threads, even I made the max connection in my.cf 5k and I used kern.threads.max_threads_per_proc: to increase it to 5K. here is the my top in the box. 45101 mysql 1500 4 0 477M 166M sbwait 0 11:36 0.00% mysqld when it reachs 1500 I get this msg below Could not connect to db My server has dual xeon and 2 gig of ram. Any help would be appreciated. Thank you, -Abdullah
FreeBSD 6.1 + Libthr + MySQL 5.0.24a max connection issue or bug?
Hello folks, I hope Greg is reading this list I use MySQL 5.0.24a from the FreeBSD ports, with libthr threading. I have a huge app makes alot of connections to MySQL server. The max I could reach is 1500 threads, even I made the max connection in my.cf 5k and I used kern.threads.max_threads_per_proc: to increase it to 5K. here is the my top in the box. 45101 mysql 1500 4 0 477M 166M sbwait 0 11:36 0.00% mysqld when it reachs 1500 I get this msg below Could not connect to db My server has dual xeon and 2 gig of ram. Any help would be appreciated. Thank you, -Abdullah
FreeBSD 6.1 + Libthr + MySQL 5.0.24a max connection issue or bug?
Hello folks, I hope Greg is reading this list I use MySQL 5.0.24a from the FreeBSD ports, with libthr threading. I have a huge app makes alot of connections to MySQL server. The max I could reach is 1500 threads, even I made the max connection in my.cf 5k and I used kern.threads.max_threads_per_proc: to increase it to 5K. here is the my top in the box. 45101 mysql 1500 4 0 477M 166M sbwait 0 11:36 0.00% mysqld when it reachs 1500 I get this msg below Could not connect to db My server has dual xeon and 2 gig of ram. Any help would be appreciated. Thank you, -Abdullah
Re: SUM in WHERE
Thanks Douglas, I know my question is very hard if my data was 22, 41, 10, 40, 30, 20, 201 ,22 ,20, 100 , 60 ,70 I want a (very smart) sql query that wil check the data and see how it's possible to get SUM=100 for example in the data above the **result** **can be** : 10,40,30,20 100 and no I don't try to get the row id=7 (this is very simple man, shame on me if I ask question like this). On 9/25/06, Douglas Sims [EMAIL PROTECTED] wrote: Ahh... I think I understand what you are trying to do now. The query will keep the cumulative total of the Total column... so, your data is 22, 41, 10, 40, 30, 20... After the 22 the cumulative total is 22 After the 41 the cumulative total is 22+41 or 63 After the 10 the cumulative total is 63+10 or 73 After the 40 the cumulative total is 73+40 or 113 After the 30 the cumulative total is 113+30 or 143 ... So, you see, it is never exactly equal to 100 so the query doesn't return any rows. I think you are looking for the first row (ordered by the ID column) where the value is exactly 100. That would be the row with id=7. Here is a query which will give you that: SELECT * FROM tbl_name WHERE total=100 ORDER BY id LIMIT 1,1 Douglas Sims [EMAIL PROTECTED] On Sep 24, 2006, at 3:27 PM, Ahmad Al-Twaijiry wrote: Hi I need the result to be 100 not to more or less than 100 here is my query : mysql select version() ; ++ | version() | ++ | 4.1.21-log | ++ 1 row in set (0.00 sec) my table : CREATE TABLE `tbl_name` ( `ID` int(11) NOT NULL auto_increment, `Total` int(11) NOT NULL default '0', PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 mysql select * from tbl_name; ++---+ | ID | Total | ++---+ | 1 |22 | | 2 |41 | | 3 |10 | | 4 |40 | | 5 |30 | | 6 |20 | | 7 | 100 | | 8 | 100 | | 9 |50 | | 10 |50 | ++---+ 10 rows in set (0.31 sec) mysql set @total=0; mysql select Total as amount1, tot as tot1 from (select Total,@total:[EMAIL PROTECTED] as tot from tbl_name order by ID) as Tx where Tot100; I will get : +-+--+ | amount1 | tot1 | +-+--+ | 40 | 113 | | 30 | 143 | | 20 | 163 | | 100 | 263 | | 100 | 363 | | 50 | 413 | | 50 | 463 | +-+--+ 7 rows in set (0.00 sec) but for =100 I will get mysql set @total=0; Query OK, 0 rows affected (0.00 sec) mysql select Total as amount1, tot as tot1 from (select Total,@total:[EMAIL PROTECTED] as tot from tbl_name order by ID) as Tx where Tot=100; Empty set (0.00 sec) Thanks On 9/24/06, Douglas Sims [EMAIL PROTECTED] wrote: Hi Ahmad I tested that example query with version 5.0.19. According to the manual, (http://dev.mysql.com/doc/refman/5.0/en/ansi-diff- subqueries.html) derived tables (subqueries in the from clause) should work in versions 4.1.x and up, so I'm not sure why it didn't work for you. In most cases you can rewrite queries which use derived tables as queries with joins, but I think that would be very hard to do in this case. The key bit of logic in this doesn't actually require there to be a derived table. The inner query: SELECT amount, @total:[EMAIL PROTECTED] AS tot FROM t ORDER BY TransactionDate will give you a result set with a running total, and then you can use whatever logic you need to give you the first one or more rows where @total exceeds the threshold (e.g. 100) (Be sure to initialize that @total variable before the SELECT) The easiest way to do this, of course, is as a subselect of another query but you could also do it in the perl/python/php/whatever layer which is sending this query to the database. Can you send a transcript of what you tried, including the SHOW CREATE TABLE statement? Douglas Sims [EMAIL PROTECTED] On Sep 24, 2006, at 10:09 AM, Ahmad Al-Twaijiry wrote: doesn't work :( , tested with 4.1.21 On 9/20/06, Douglas Sims [EMAIL PROTECTED] wrote: Following is one way of doing what you want. mysql show create table t; +--- + --- -+ | Table | Create Table | +--- + --- -+ | t | CREATE TABLE `t` ( `TransactionDate` datetime default NULL, `amount` float default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +--- + --- -+ 1 row in set (0.00 sec) mysql select * from t; +-++ | TransactionDate | amount
Rapid application development
Hi Everyone, I know this isn't the right mail list, but I need your feedback as mysql users What is the best RAD (Rapid application development) do you use with MySQL to develop software or a web-based program ? and in which language is it ? (PHP, Perl, Java, ...etc). I like programming but one this that I hate is the first stage of programming when you start creating the basic code (db connection, interface, insert,update,etc), this is why I'm looking for a good RAD tool that can speed up my programming. Thanks -- Ahmad Fahad AlTwaijiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUM in WHERE
doesn't work :( , tested with 4.1.21 On 9/20/06, Douglas Sims [EMAIL PROTECTED] wrote: Following is one way of doing what you want. mysql show create table t; +--- +--- -+ | Table | Create Table | +--- +--- -+ | t | CREATE TABLE `t` ( `TransactionDate` datetime default NULL, `amount` float default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +--- +--- -+ 1 row in set (0.00 sec) mysql select * from t; +-++ | TransactionDate | amount | +-++ | 2006-01-02 00:00:00 | 20 | | 2006-01-04 00:00:00 | 178| | 2006-01-07 00:00:00 | 32.43 | | 2006-01-09 00:00:00 | 3 | | 2006-01-11 00:00:00 | -1000 | | 2006-01-15 00:00:00 | 33.9 | +-++ 6 rows in set (0.00 sec) mysql set @total=0; Query OK, 0 rows affected (0.00 sec) mysql select amount as amount1, tot as tot1 from (select amount, @total:[EMAIL PROTECTED] as tot from t order by TransactionDate) AS Tx where Tot100; +-+--+ | amount1 | tot1 | +-+--+ | 178 | 198 | | 32.43 | 230.43305176 | | 3 | 233.43305176 | +-+--+ 3 rows in set (0.00 sec) Good luck! Douglas Sims [EMAIL PROTECTED] On Sep 19, 2006, at 4:02 PM, Quentin Bennett wrote: No, I don't think it is. I think you want to have a query that will return 'n' rows where the sum of Total is = 100 If your table is ID Total 1 10 2 20 3 30 4 40 5 50 it would return 1 10 2 20 3 30 4 40 (sum total = 100) but if your table was ID Total 1 100 2 20 3 30 4 40 5 50 it would return 1 100 only. Have I got it right. Using only SQL, your best bet would be a stored procedure, otherwise its really application logic to select the rows one at a time and keep a running total. HTH Quentin -Original Message- From: Ahmad Al-Twaijiry [mailto:[EMAIL PROTECTED] Sent: Wednesday, 20 September 2006 2:24 a.m. To: Price, Randall Cc: Edward Macnaghten; mysql@lists.mysql.com Subject: Re: SUM in WHERE Actually is this possible with simple SQL command in Mysql ? On 9/19/06, Price, Randall [EMAIL PROTECTED] wrote: I tried it also with 5.0.24-community-nt and it still didn't work! Randall Price Microsoft Implementation Group Secure Enterprise Computing Initiatives Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: Ahmad Al-Twaijiry [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 19, 2006 10:06 AM To: Edward Macnaghten Cc: mysql@lists.mysql.com Subject: Re: SUM in WHERE I tried it also with 4.1.21-log and still didn't work ! On 9/19/06, Ahmad Al-Twaijiry [EMAIL PROTECTED] wrote: I tried that before and it also doesn't work, is it because I'm using mysql version 4.1.19 ? On 9/19/06, Edward Macnaghten [EMAIL PROTECTED] wrote: Ahmad Al-Twaijiry wrote: Hi everyone snip SELECT * FROM tbl_name WHERE SUM(Total)=100 ORDER BY ID SELECT ID FROM tbl_name GROUP BY ID HAVING SUM(Total)=100 ORDER BY ID -- Ahmad Fahad AlTwaijiry -- Ahmad Fahad AlTwaijiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Ahmad Fahad AlTwaijiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Ahmad Fahad AlTwaijiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUM in WHERE
doesn't work :( , tested with 4.1.21 On 21 Sep 2006 13:20:37 -, Felix Geerinckx [EMAIL PROTECTED] wrote: On 18/09/2006, Ahmad Al-Twaijiry wrote: I want to run SQL query that will return to me the first records that the SUM of Total field = 100 USE test; DROP TABLE IF EXISTS foo; CREATE TABLE foo ( id INT UNSIGNED NOT NULL PRIMARY KEY, total INT NOT NULL); INSERT INTO foo VALUES (1, 20), (2, 30), (3, 40), (4, 10), (5, 20), (6, 20); SELECT f1.id, f1.total FROM foo f1 JOIN foo f2 ON f2.id = f1.id GROUP BY f1.id, f1.total HAVING SUM(f2.total) = 100; -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Ahmad Fahad AlTwaijiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUM in WHERE
Hi I need the result to be 100 not to more or less than 100 here is my query : mysql select version() ; ++ | version() | ++ | 4.1.21-log | ++ 1 row in set (0.00 sec) my table : CREATE TABLE `tbl_name` ( `ID` int(11) NOT NULL auto_increment, `Total` int(11) NOT NULL default '0', PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 mysql select * from tbl_name; ++---+ | ID | Total | ++---+ | 1 |22 | | 2 |41 | | 3 |10 | | 4 |40 | | 5 |30 | | 6 |20 | | 7 | 100 | | 8 | 100 | | 9 |50 | | 10 |50 | ++---+ 10 rows in set (0.31 sec) mysql set @total=0; mysql select Total as amount1, tot as tot1 from (select Total,@total:[EMAIL PROTECTED] as tot from tbl_name order by ID) as Tx where Tot100; I will get : +-+--+ | amount1 | tot1 | +-+--+ | 40 | 113 | | 30 | 143 | | 20 | 163 | | 100 | 263 | | 100 | 363 | | 50 | 413 | | 50 | 463 | +-+--+ 7 rows in set (0.00 sec) but for =100 I will get mysql set @total=0; Query OK, 0 rows affected (0.00 sec) mysql select Total as amount1, tot as tot1 from (select Total,@total:[EMAIL PROTECTED] as tot from tbl_name order by ID) as Tx where Tot=100; Empty set (0.00 sec) Thanks On 9/24/06, Douglas Sims [EMAIL PROTECTED] wrote: Hi Ahmad I tested that example query with version 5.0.19. According to the manual, (http://dev.mysql.com/doc/refman/5.0/en/ansi-diff- subqueries.html) derived tables (subqueries in the from clause) should work in versions 4.1.x and up, so I'm not sure why it didn't work for you. In most cases you can rewrite queries which use derived tables as queries with joins, but I think that would be very hard to do in this case. The key bit of logic in this doesn't actually require there to be a derived table. The inner query: SELECT amount, @total:[EMAIL PROTECTED] AS tot FROM t ORDER BY TransactionDate will give you a result set with a running total, and then you can use whatever logic you need to give you the first one or more rows where @total exceeds the threshold (e.g. 100) (Be sure to initialize that @total variable before the SELECT) The easiest way to do this, of course, is as a subselect of another query but you could also do it in the perl/python/php/whatever layer which is sending this query to the database. Can you send a transcript of what you tried, including the SHOW CREATE TABLE statement? Douglas Sims [EMAIL PROTECTED] On Sep 24, 2006, at 10:09 AM, Ahmad Al-Twaijiry wrote: doesn't work :( , tested with 4.1.21 On 9/20/06, Douglas Sims [EMAIL PROTECTED] wrote: Following is one way of doing what you want. mysql show create table t; +--- + --- -+ | Table | Create Table | +--- + --- -+ | t | CREATE TABLE `t` ( `TransactionDate` datetime default NULL, `amount` float default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +--- + --- -+ 1 row in set (0.00 sec) mysql select * from t; +-++ | TransactionDate | amount | +-++ | 2006-01-02 00:00:00 | 20 | | 2006-01-04 00:00:00 | 178| | 2006-01-07 00:00:00 | 32.43 | | 2006-01-09 00:00:00 | 3 | | 2006-01-11 00:00:00 | -1000 | | 2006-01-15 00:00:00 | 33.9 | +-++ 6 rows in set (0.00 sec) mysql set @total=0; Query OK, 0 rows affected (0.00 sec) mysql select amount as amount1, tot as tot1 from (select amount, @total:[EMAIL PROTECTED] as tot from t order by TransactionDate) AS Tx where Tot100; +-+--+ | amount1 | tot1 | +-+--+ | 178 | 198 | | 32.43 | 230.43305176 | | 3 | 233.43305176 | +-+--+ 3 rows in set (0.00 sec) Good luck! Douglas Sims [EMAIL PROTECTED] On Sep 19, 2006, at 4:02 PM, Quentin Bennett wrote: No, I don't think it is. I think you want to have a query that will return 'n' rows where the sum of Total is = 100 If your table is ID Total 1 10 2 20 3 30 4 40 5 50 it would return 1 10 2 20 3 30 4 40 (sum total = 100) but if your table was ID Total 1 100 2 20 3 30 4 40 5 50 it would return 1 100 only. Have I got it right. Using only SQL, your best bet would be a stored procedure, otherwise its really application logic to select
Re: SUM in WHERE
I tried it also with 4.1.21-log and still didn't work ! On 9/19/06, Ahmad Al-Twaijiry [EMAIL PROTECTED] wrote: I tried that before and it also doesn't work, is it because I'm using mysql version 4.1.19 ? On 9/19/06, Edward Macnaghten [EMAIL PROTECTED] wrote: Ahmad Al-Twaijiry wrote: Hi everyone snip SELECT * FROM tbl_name WHERE SUM(Total)=100 ORDER BY ID SELECT ID FROM tbl_name GROUP BY ID HAVING SUM(Total)=100 ORDER BY ID -- Ahmad Fahad AlTwaijiry -- Ahmad Fahad AlTwaijiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUM in WHERE
Actually is this possible with simple SQL command in Mysql ? On 9/19/06, Price, Randall [EMAIL PROTECTED] wrote: I tried it also with 5.0.24-community-nt and it still didn't work! Randall Price Microsoft Implementation Group Secure Enterprise Computing Initiatives Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: Ahmad Al-Twaijiry [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 19, 2006 10:06 AM To: Edward Macnaghten Cc: mysql@lists.mysql.com Subject: Re: SUM in WHERE I tried it also with 4.1.21-log and still didn't work ! On 9/19/06, Ahmad Al-Twaijiry [EMAIL PROTECTED] wrote: I tried that before and it also doesn't work, is it because I'm using mysql version 4.1.19 ? On 9/19/06, Edward Macnaghten [EMAIL PROTECTED] wrote: Ahmad Al-Twaijiry wrote: Hi everyone snip SELECT * FROM tbl_name WHERE SUM(Total)=100 ORDER BY ID SELECT ID FROM tbl_name GROUP BY ID HAVING SUM(Total)=100 ORDER BY ID -- Ahmad Fahad AlTwaijiry -- Ahmad Fahad AlTwaijiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Ahmad Fahad AlTwaijiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SUM in WHERE
Hi everyone I didn't find any maillist regarding SQL question so I'm posting my question in here. I have a table like this [ ID ][ Total ] [ 1 ][ 20 ] [ 2 ][ 30 ] [ 3 ][ 40 ] [ 4 ][ 10 ] [ 5 ][ 20 ] [ 6 ][ 20 ] I want to run SQL query that will return to me the first records that the SUM of Total field = 100 for example SELECT * FROM tbl_name WHERE SUM(Total)=100 ORDER BY ID I know my SQL statment above will not work, could someone help me ? -- Ahmad Fahad AlTwaijiry
Re: SUM in WHERE
Hi I would expect the following result : [ ID ][ Total ] [ 1 ][ 20 ] [ 2 ][ 30 ] [ 3 ][ 40 ] [ 4 ][ 10 ] because if you SUM(Total) in the result you will see it = 100 On 9/19/06, André Hänsel [EMAIL PROTECTED] wrote: -Ursprüngliche Nachricht- Von: Ahmad Al-Twaijiry [mailto:[EMAIL PROTECTED] Gesendet: Montag, 18. September 2006 23:28 An: mysql@lists.mysql.com Betreff: SUM in WHERE [...] I want to run SQL query that will return to me the first records that the SUM of Total field = 100 for example SELECT * FROM tbl_name WHERE SUM(Total)=100 ORDER BY ID I know my SQL statment above will not work, could someone help me ? So what result do you expect from your query? -- Ahmad Fahad AlTwaijiry
MySQL 5.0.24 hard time with VB 3.5.4 and 3.6
Hello folks,
I'm having hard time with MySQL, I posted this issue t the vb board, and they
claimed it's MySQL issue.
I have this issue since I upgraded to MySQL 5.0.22 and now it's still I going
on since I upgraded to MySQL 5.0.24 too.
Database error in vBulletin 3.6.0:
Invalid SQL:
INSERT IGNORE INTO session
(sessionhash, userid, host, idhash, lastactivity, location,
styleid, languageid, loggedin, inforum, inthread, incalendar, badlocation,
useragent, bypass, profileupdate)
VALUES
('49778b903012cc3dba1481897dcb5749', 0, '62.68.61.2',
'ab1219e33a7fca1808ca880a806b6383', 1155125518,
'/showthread.php?t=5311amp;page=4', 0, 0, 0, 21, 5311, 0, 0, 'Mozilla/4.0
(compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)', 0, 0);
MySQL Error : MySQL server has gone away
Error Number : 2006
Date : Wednesday, August 9th 2006 @ 12:13:16 PM
Script : http://bb.wearab.net/showthread.php?t=5311page=4
--
Database error in vBulletin 3.6.0:
Invalid SQL:
UPDATE session
SET lastactivity = 1155128141, inforum = 0, inthread = 0,
incalendar = 0, badlocation = 0
WHERE sessionhash = '72acdfba168f0c3d33e0728f4eff5326';
MySQL Error : MySQL server has gone away
Error Number : 2006
Date : Wednesday, August 9th 2006 @ 12:56:14 PM
Script :
http://bb.wearab.net/online.php?order=ascsort=usernamepp=200page=1
Referrer :
IP Address : 82.148.97.67
--
Here is my server profile.
Server TypeFreeBSD
Web Server Apache v2.2.2
PHP5.1.4
PHP Max Post Size 8.00 MB
PHP Maximum Upload Size2.00 MB
PHP Memory Limit 16.00 MB
MySQL Version 5.0.24
MySQL Packet Size 32.00 MB
Data Usage 70.57 MB
Index Usage34.93 MB
Attachment Usage 17.01 MB
Avatar Usage 414.5 KB
Profile Picture Usage 429.2 KB
Thank you,
-Arabian
Re: Why release 5.0.23 instead of 5.0.24?
- Original Message From: Greg 'groggy' Lehey To: Abdullah Ibn Hamad Al-Marri Cc: Jim Winstead ; mysql@lists.mysql.com Sent: Monday, July 31, 2006 5:54:31 AM Subject: Re: Why release 5.0.23 instead of 5.0.24? On Saturday, 29 July 2006 at 9:53:29 -0700, Abdullah Ibn Hamad Al-Marri wrote: Jim Winstead wrote: On Sat, Jul 29, 2006 at 08:35:41AM -0700, Abdullah Ibn Hamad Al-Marri wrote: Why MySQL 5.0.23 while it has serious bug? I thought you guys will release 5.0.24 instead. This is still the case. 5.0.23 will not be released, and 5.0.24 will be released as soon as it is ready. I suppose you mean 5.0.24. I can't speak for the build team, but the FreeBSD Ports Collection will have it as soon as the maintainer updates it. He's on vacation until 21 August, so normally it wouldn't be until after that. If you have a good reason to need it earlier, let me know and I may be able to update it for you. Greg -- Greg Lehey, Senior Software Engineer, Online Backup MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Diary http://www.lemis.com/grog/diary.html Are you MySQL certified? http://www.mysql.com/certification/ Hello Greg, Yes I meant 5.0.24 ;) I appreciate your kind efforts to help the FreeBSD Community :) FreeBSD users who runs MySQL 5.0.22 and 5.0.21 suffers from MySQL crash all the time. Check this thread pleaseVB query crashes mysql 5.0.21 http://www.vbulletin.com/forum/bugs35.php?do=viewbugid=2449 quote from http://bugs.mysql.com/bug.php?id=19618 [9 Jun 1:12]Christian Hammers Marco, the confirmation that the bugfix was committed for 5.0.22 but then postponed to 5.0.23 due to the fact that 5.0.22 was a security release can be read in the already made comments to the upcoming 5.0.23 release notes: http://dev.mysql.com/doc/refman/5.0/en/news-5-0-23.html Hope that helps, -christian- So upading the FreeBSD MySQL 5.0.x port would be greatful thing for FreeBSD users as well. I remain thanking you Greg :) Regards, -Abdullah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.22 install problem with NetBSD
I guess MySQL should make binary for NetBSD too. - Original Message From: Clay R White [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, July 30, 2006 2:01:58 AM Subject: MySQL 5.0.22 install problem with NetBSD From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: MySQL 5.0.22 install problems with NetBSD Description: Started the install (new install). Reached the step: scripts/mysql_install_db --user=mysql. Recieved the following errors: scripts/mysql_install_db: line 86: ./bin/my_print_defaults: cannot execute binary file Neither host 'roswell.lazyrockinw.com' nor 'localhost' could be looked up with ./bin/resolveip Please configure the 'hostname' command to return a correct hostname. If you want to solve this at a later stage, restart this script with the --force option I then tried to run ./bin/resolveip localhost received -bash:./bin/resolveip: cannot execute binary file file is currently set as: -rwxr-xr-x 1 root wheel 1144116 May 26 13:24 bin/resolveip The distribution was mysql-standard-5.0.22-freebsd6.0-i386.tar.gz although I've also tried the openbsd distribution. I followed the instructions in the INSTALL_BINARY file, both distributions failed with identical errors on the same script. How-To-Repeat: Repeated whenerver I attempted to run scripts/mysql_install_db Fix: I did notice in the support pages that this was a known bug back in one of the 4.X distributions. I could not find where there was a fix, patch or workaround. Submitter-Id: Originator:Clay White Organization: CJ Enterprises Bertram, TX 78605 MySQL support: none Synopsis: Unable to install MySQL on NetBSD due to unknown installation script errors Severity: serious Priority: medium Release: mysql-5.0.22-standard (MySQL Community Edition - Standard (GPL)) C compiler:gcc (GCC) 3.4.4 [FreeBSD] 20050518 C++ compiler: gcc (GCC) 3.4.4 [FreeBSD] 20050518 Environment: machine, os, target, libraries (multiple lines) System: NetBSD roswell.lazyrockinw.com 3.99.20 NetBSD 3.99.20 (piggy) #2: Mon Jun 12 22:59:37 CDT 2006 [EMAIL PROTECTED]:/stuff/src_current/sys/arch/i386 /compile/obj/piggy i386 Some paths: /usr/pkg/bin/perl /usr/bin/make /usr/pkg/bin/gmake /usr/bin/gcc /us r/bin/cc GCC: Using built-in specs. C compiler:gcc (GCC) 3.4.4 [FreeBSD] 20050518 C++ compiler: gcc (GCC) 3.4.4 [FreeBSD] 20050518 Environment: machine, os, target, libraries (multiple lines) System: NetBSD roswell.lazyrockinw.com 3.99.20 NetBSD 3.99.20 (piggy) #2: Mon Ju n 12 22:59:37 CDT 2006 [EMAIL PROTECTED]:/stuff/src_current/sys/arch/i386 /compile/obj/piggy i386 Some paths: /usr/pkg/bin/perl /usr/bin/make /usr/pkg/bin/gmake /usr/bin/gcc /us r/bin/cc GCC: Using built-in specs. Configured with: /home/nick/work/netbsd/src/tools/gcc/../../gnu/dist/gcc/configu re --enable-long-long --disable-multilib --enable-threads --disable-symvers --bu ild=i386-unknown-netbsdelf2.0. --host=i386--netbsdelf --target=i386--netbsdelf Thread model: posix gcc version 3.3.3 (NetBSD nb3 20040520) Compilation info: CC='ccache gcc' CFLAGS='' CXX='ccache gcc' CXXFLAGS='' LDF LAGS='' ASFLAGS='' LIBC: lrwxr-xr-x 1 root wheel 16 Jun 13 01:15 /lib/libc.so - libc.so.12.128.2 lrwxr-xr-x 1 root wheel 16 Jun 13 01:15 /lib/libc.so.12 - libc.so.12.128.2 -r--r--r-- 1 root wheel 881693 Jun 13 01:15 /lib/libc.so.12.128.2 lrwxr-xr-x 1 root wheel 16 Jun 13 01:15 /lib/libc.so - libc.so.12.128.2 lrwxr-xr-x 1 root wheel 16 Jun 13 01:15 /lib/libc.so.12 - libc.so.12.128.2 -r--r--r-- 1 root wheel 881693 Jun 13 01:15 /lib/libc.so.12.128.2 -r--r--r-- 1 root wheel 1702186 Jun 13 01:15 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 21 Jun 13 01:15 /usr/lib/libc.so - /lib/libc.so.12.1 28.2 lrwxr-xr-x 1 root wheel 21 Jun 13 01:15 /usr/lib/libc.so.12 - /lib/libc.so.1 2.128.2 lrwxr-xr-x 1 root wheel 21 Jun 13 01:15 /usr/lib/libc.so.12.128.2 - /lib/lib c.so.12.128.2 Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr /local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Com munity Edition - Standard (GPL)' '--with-server-suffix=-standard' '--enable-thre ad-safe-client' '--enable-local-infile' '--enable-assembler' '--with-pic' '--dis able-shared' '--with-zlib-dir=bundled' '--with-big-tables' '--with-yassl' '--wit h-readline' '--with-archive-storage-engine' '--with-innodb' '--with-extra-charse ts=complex' 'CC=ccache gcc' 'CXX=ccache gcc'
Why release 5.0.23 instead of 5.0.24?
Hello, Why MySQL 5.0.23 while it has serious bug? I thought you guys will release 5.0.24 instead. Security fix: If a user has access to MyISAM table t, that user can create a MERGE table m that accesses t. However, if the user's privileges on t are subsequently revoked, the user can continue to access t by doing so through m. If this behavior is undesirable, you can start the server with the new --skip-merge option to disable the MERGE storage engine. (Bug#15195) MySQL 5.0.23 contained a fix for Bug#10952 that has been reverted in 5.0.24 because it introduced the risk of unintended data loss. Thank you, -Abdullah
Re: Why release 5.0.23 instead of 5.0.24?
That's why I wondered. So when will we get MySQL 5.0.25 for FreeBSD? -A On Sat, Jul 29, 2006 at 08:35:41AM -0700, Abdullah Ibn Hamad Al-Marri wrote: Why MySQL 5.0.23 while it has serious bug? I thought you guys will release 5.0.24 instead. This is still the case. 5.0.23 will not be released, and 5.0.24 will be released as soon as it is ready. (I see that some 5.0.23 binaries are listed on the 5.0 download page -- this is a mistake.) Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie wants to load a couple of tables and join them
Basically, I'm new to mysql (or to any database for that matter). I have an old version installed on my linux machine. I thought, as a learning exercise I'd take 2 files (tab separated tables) load them into mysql and then merge or join them. So what are the steps? The first thing I tried was to create a database with mysqladmin create MACARP and the error I get is CREATE DATABASE failed; error: 'Access denied for user: '@localhost' to database 'MACARP'' A similar attempt to create a user ended similarly. Can I get some hints? === Al -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error 1064 when trying to create table
When I execute the following query
CREATE TABLE Sponsor (
sNum INTEGER UNSIGNED NOT NULL,
sAdrBlock CHAR(2),
sCivil_ID CHAR(12),
sDateOfBirth DATE,
sDrvLicenseExp DATE,
sDrvLicenseNum CHAR(9),
sGender ENUM('M','F'),
sName VARCHAR(20),
sStatus VARCHAR(20),
sTelephone CHAR(7),
A_areaNum INTEGER UNSIGNED NOT NULL,
G_goverNum INTEGER UNSIGNED NOT NULL,
O_occupNum INTEGER UNSIGNED NOT NULL,
N_nationNum INTEGER UNSIGNED NOT NULL,
PRIMARY KEY (sNum),
FOREIGN KEY (A_areaNum) REFERENCES Area(areaNum),
FOREIGN KEY (G_goverNum) REFERENCES Governate(goverNum),
FOREIGN KEY (O_occupNum) REFERENCES Occupation(occupNum),
FOREIGN KEY (N_nationNum) REFERENCES Nationality(nationNum)
);
I get the following 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
'Area(areaNum),
FOREIGN KEY(G_goverNum) REFERENCES Governate(goverNum),
at line 17
Any ideas?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JBoss queries aren't cached by MySQL
Hi again, The query cache finally works after I've reverted to mm.mysql-2.0.12-bin.jar. The problem was that the query cache somehow wouldn't work within transactions with mysql-connector-java-3.1.10-bin.jar. Anyone care to dig further? Here is one working combination: Jboss 3.2.2 with transactions JDBC: mm.mysql-2.0.12-bin.jar (3.1.10 doesn't work for me) MySQL: 4.1.14 (Haven't tried 5.0) Extract from http://dev.mysql.com/doc/mysql/en/query-cache-how.html ' In MySQL 4.0, the query cache is disabled within transactions (it does not return results). Beginning with MySQL 4.1.1, the query cache also works within transactions when using InnoDB tables (it uses the table version number to detect whether or not its contents are still current).' Regards, Al Send instant messages to your online friends http://asia.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JBoss queries aren't cached by MySQL
Hi All, I didn't see any follow-up on this thread. Hope I can get some hint :) How to get the query cache to work with Jboss? I've got my app running on the following JBoss 3.2.2. (Unable to upgrade right now) MySQL 4.0.20d mysql-connector-java-3.1.10-bin.jar I have switched on the query_cache as follows: query_cache_limit=2M query_cache_size=32M query_cache_type=1 However, after triggering a few queries from my applications, the query cache is not used at all: SHOW STATUS LIKE 'Qcache%'; Qcache_queries_in_cache,0 Qcache_inserts,0 Qcache_hits,0 Qcache_lowmem_prunes,0 Qcache_not_cached,328 Qcache_free_memory,33545632 Qcache_free_blocks,1 Qcache_total_blocks,1 Note: Running queries from MySQL-Front hits the cache - the above counters increase. Regards, Al -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 12:33 AM To: Rafal Kedziorski Cc: mysql@lists.mysql.com Subject: Re: JBoss queries aren't cached by MySQL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rafal Kedziorski wrote: Hi, we have the problem, that queries generated by JBoss or our code which runns under JBoss will be not cached by MySQL. The same query sendet from an external application or MySQLFront will be cached by the same MySQL. I'm using JBoss 3.2.5 with JDBC 3.0.16 and MySQL 4.0.23a. Any idea why? Regards, Rafal Rafal, Define what you mean by not cached by MySQLDo you mean the query cache? If so, what do your query cache settings look like? -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCbm1JtvXNTca6JD8RAtT9AJ9k8HnIkRh+U9UE3ROf+eff5ZudRgCgxNNI KeS6Iiq5ttoKjZsaDlyXV74= =gCL+ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Send instant messages to your online friends http://asia.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JBoss queries aren't cached by MySQL
Hi again, It seems that the problem is solved with MySQL 4.1 (and later?). See a posting from the Jboss forum: http://www.jboss.org/index.html?module=bbop=viewtopicp=3830750#3830750 Cheers, Al -Original Message- From: Al Caponi [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 8:35 PM To: mysql@lists.mysql.com Subject: RE: JBoss queries aren't cached by MySQL Hi All, I didn't see any follow-up on this thread. Hope I can get some hint :) How to get the query cache to work with Jboss? I've got my app running on the following JBoss 3.2.2. (Unable to upgrade right now) MySQL 4.0.20d mysql-connector-java-3.1.10-bin.jar I have switched on the query_cache as follows: query_cache_limit=2M query_cache_size=32M query_cache_type=1 However, after triggering a few queries from my applications, the query cache is not used at all: SHOW STATUS LIKE 'Qcache%'; Qcache_queries_in_cache,0 Qcache_inserts,0 Qcache_hits,0 Qcache_lowmem_prunes,0 Qcache_not_cached,328 Qcache_free_memory,33545632 Qcache_free_blocks,1 Qcache_total_blocks,1 Note: Running queries from MySQL-Front hits the cache - the above counters increase. Regards, Al -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 12:33 AM To: Rafal Kedziorski Cc: mysql@lists.mysql.com Subject: Re: JBoss queries aren't cached by MySQL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rafal Kedziorski wrote: Hi, we have the problem, that queries generated by JBoss or our code which runns under JBoss will be not cached by MySQL. The same query sendet from an external application or MySQLFront will be cached by the same MySQL. I'm using JBoss 3.2.5 with JDBC 3.0.16 and MySQL 4.0.23a. Any idea why? Regards, Rafal Rafal, Define what you mean by not cached by MySQLDo you mean the query cache? If so, what do your query cache settings look like? -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCbm1JtvXNTca6JD8RAtT9AJ9k8HnIkRh+U9UE3ROf+eff5ZudRgCgxNNI KeS6Iiq5ttoKjZsaDlyXV74= =gCL+ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Send instant messages to your online friends http://asia.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Send instant messages to your online friends http://asia.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JBoss queries aren't cached by MySQL
Hi Gleb All, Thanks for replying. I saw a posting on Jboss forum that suggested upgrading MySQL to 4.1 and above (See my previous posting - http://lists.mysql.com/mysql/189988) I tried both MySQL 4.1.14 and 5.0.13-rc but it didn't work for me :( Still diggin... :) Thanks, Alain -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 9:07 PM To: mysql@lists.mysql.com Subject: Re: JBoss queries aren't cached by MySQL Hello. If you can just check your application with the latest release of MySQL (4.1.14 now). Turn on the general log to be sure that MySQL receives unchanged queries and no session variables has been changed. Al Caponi [EMAIL PROTECTED] wrote: Hi All, I didn't see any follow-up on this thread. Hope I can get some hint :) How to get the query cache to work with Jboss? I've got my app running on the following JBoss 3.2.2. (Unable to upgrade right now) MySQL 4.0.20d mysql-connector-java-3.1.10-bin.jar I have switched on the query_cache as follows: query_cache_limit=2M query_cache_size=32M query_cache_type=1 However, after triggering a few queries from my applications, the query cache is not used at all: SHOW STATUS LIKE 'Qcache%'; Qcache_queries_in_cache,0 Qcache_inserts,0 Qcache_hits,0 Qcache_lowmem_prunes,0 Qcache_not_cached,328 Qcache_free_memory,33545632 Qcache_free_blocks,1 Qcache_total_blocks,1 Note: Running queries from MySQL-Front hits the cache - the above counters increase. Regards, Al -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 12:33 AM To: Rafal Kedziorski Cc: mysql@lists.mysql.com Subject: Re: JBoss queries aren't cached by MySQL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rafal Kedziorski wrote: Hi, we have the problem, that queries generated by JBoss or our code which runns under JBoss will be not cached by MySQL. The same query sendet from an external application or MySQLFront will be cached by the same MySQL. I'm using JBoss 3.2.5 with JDBC 3.0.16 and MySQL 4.0.23a. Any idea why? Regards, Rafal Rafal, Define what you mean by not cached by MySQLDo you mean the query cache? If so, what do your query cache settings look like? -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCbm1JtvXNTca6JD8RAtT9AJ9k8HnIkRh+U9UE3ROf+eff5ZudRgCgxNNI KeS6Iiq5ttoKjZsaDlyXV74= =gCL+ -END PGP SIGNATURE- -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Send instant messages to your online friends http://asia.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Remote Connection?
hi, i try to access the MySQL server remotely using Command prompet how can i do so? i mean i want to run all MySQL command on my hosting server ... _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Key Buffer Size
i did: http://www.mysql.com/doc/en/Server_system_variables.html -Original Message- From: Terence [mailto:[EMAIL PROTECTED] Sent: Friday, March 19, 2004 3:11 PM To: [EMAIL PROTECTED] Subject: Key Buffer Size Hi, Can somebody tell me whether this is good or bad: Our mysql server has key_buffer_size = 402,653,184 (without the commas) The MySQL administrator tool indicates a 100% usage most of the time, and 362,324,992 as current usage within 24 hours which builds up very fast. Are there memory leaks for threads on my system? Or is this normal behaviour? Restarting the mysql server clears the above. I can't seem to find much information on the mysql site or documentation as to what the ideals are. Running MySQL 4.1.1 Dual Xeon 2.4GhZ 1 Gig Ram Redhat 9 Thanks -- 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]
Help optimising a query
Hi all. Im looking for some help to speed up some queries I have to run. Ill try to
briefly describe the setup, but mostly try let my code explain itself.
There are three tables described below from a database that describes the execution of
a Java program. class_loads records details of each class. methods records details of
each method. Each method is associated with a record from class_loads via the class_id
field. Finally method_executions contains info about method executions by mapping
method_ids (which can be used to join with methods table) to event_numbers which
represent time.
mysql describe class_loads;
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| event_number | bigint(20) unsigned | | PRI | 0 | |
| class_id | int(10) unsigned| | PRI | 0 | |
| class_name | varchar(255)| | MUL | | |
| source_name | varchar(255)| | | | |
+--+-+--+-+-+---+
5 rows in set (0.08 sec)
mysql describe methods;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| method_id| int(10) unsigned | | PRI | 0 | |
| class_id | int(10) unsigned | | MUL | 0 | |
| method_name | varchar(255) | | | | |
| method_signature | varchar(255) | | | | |
+--+--+--+-+-+---+
4 rows in set (0.00 sec)
mysql describe method_executions;
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| event_number | bigint(20) unsigned | | PRI | 0 | |
| method_id| int(10) unsigned| | | 0 | |
+--+-+--+-+-+---+
3 rows in set (0.00 sec)
The objective is to compute what I would call an execution frequency matrix (time
intervals as rows, methods as columns), i.e. for some given event_number/time interval
[x, y), I need to determine how often each method with a name conforming to a
specified regex is executed within that interval.
Heres what I have done. Being a novice Im sure its painfully ineffecient. The
bottleneck is indicated below. To give an idea of the table sizes, in a fairly small
example we have 199, 2423 and 2434194 rows in class_loads, methods and
method_executions respectively.
CREATE TEMPORARY TABLE temp_class_loads (
class_id INT NOT NULL,
class_name VARCHAR(255) NOT NULL,
source_name VARCHAR(255) NOT NULL)
CREATE TEMPORARY TABLE temp_methods (
method_id INT NOT NULL,
source_name VARCHAR(255) NOT NULL,
class_name VARCHAR(255) NOT NULL,
method_name VARCHAR(255) NOT NULL,
method_signature VARCHAR(255) NOT NULL)
LOCK TABLES class_loads READ, methods READ, method_entries READ
INSERT INTO temp_class_loads (
SELECT class_id, class_name, source_name
FROM class_loads
WHERE class_name LIKE com.%)
INSERT INTO temp_methods (
SELECT method_id, source_name, class_name, method_name, method_signature
FROM temp_class_loads, methods
WHERE temp_class_loads.class_id = methods.class_id
GROUP BY method_id)
{do the following statements for various [x, y) event_number ranges - The third one is
the bottleneck!}
CREATE TEMPORARY TABLE interval_method_freqs (
method_id INT NOT NULL,
count INT NOT NULL)
INSERT INTO interval_method_freqs (
SELECT method_id, COUNT(*)
FROM method_executions
WHERE method_executions.event_number BETWEEN x AND y
GROUP BY method_id)
SELECT SUM(count)
FROM temp_methods LEFT JOIN interval_method_freqs USING(method_id)
GROUP BY source_name, class_name, method_name, method_signature
DROP TABLE interval_method_freqs
{loop}
UNLOCK TABLES
DROP TABLE temp_methods
DROP TABLE temp_class_loads
If you got this far thanks for making the effort! I look forward to reading suggested
improvements.
Regards,
A.
Viruses from the list
I use an email-adress for this list only and since my first posting a few days ago I got viruses, while I didn't before. VIRUS FROM mamo @ hvd.healthnet.lu (W32/[EMAIL PROTECTED]) VIRUS FROM kawamoto @ wave.tky.plala.or.jp ([EMAIL PROTECTED]) VIRUS FROM sales @ vulcanford.ca (W32/[EMAIL PROTECTED])/[EMAIL PROTECTED]) Wake up people, it was time enough to update virus-definitions. Clean your pcs. I will disable my email-adress soon. Al -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting chararacters greater ASCII 122 (no - nao)
I have to convert names with characters greater than ASCII 122 - z to use it in a select statement: Is there a way to do it, like select field, function(field) as converted Examples: não - nao você - voce cabeça - cabeca É Possibile - E Possibile N` Aniré - N Anire Sé Mañana - Se Manana Ärger - Aerger a.s.o Could you give me some hints, please? Al -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump sorted
Is it possible to sort the records, which are created with mysqldump? I didn't find an optioin in man mysqldump. If you cannot do it with mysqldump, what would be the best workaroud for it? into outfile? Al -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Win98 - Work-around to sudden lost connection to database
Hi all, There is a limitation on Win98 when a client app using mm.mysql driver doing extensive queries to MySQL 3.23.x frequently encounters a connection lost error. Error msg is something like: 'java.lang.Exception: Cannot connect to MySQL server on localhost:3306. Is there a MySQL server running on the machine/port you are trying to connect to? (java.net.SocketException)' I've found a very useful piece of info. In brief, you've got to change/create the registry key for max concurrent TCP connections: Pls see: http://www.proxyplus.cz/faq/articles/EN/art10002.htm If the above URL is unavailable, here is the important part: snip Solution: There are following articles in Microsoft Knowledge base: Windows NT/2000: http://support.microsoft.com/support/kb/articles/Q196/2/71.ASP Windows 95: http://support.microsoft.com/support/kb/articles/Q170/7/91.ASP Some users reported to us there is another solution for Windows 95/98/Me which solved the problem at all. Windows 95/98/Me allows up to 100 (default value) of concurrent TCP connections. It looks like when the number of connection reaches the limit 10055 error is reported. The solution is to increase the limit of simultaneous connections. You can do it by changing/creating value of the key: HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\VxD\MSTCP\MaxConnection s If you will create the MaxConnections variable key use STRING type for it (early Windows 95 used DWORD probably). Set the value to 300 for example, restart the Windows and check whether 10055 problem disappeared. There is MS Knowledge Base article which describes the meaning of MSTCP registry entries: http://support.microsoft.com/support/kb/articles/Q158/4/74.ASP /snip My 2 cts worth... I LOVE THIS GUY! HTH, Al. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext search and cumulative relevence?
Hello,
I am interested in utilizing full text search in a way that I have not
seen in my run across the various examples and docs.
What I'd like to do is create full text indexes on individual columns in
order to attain relevancy of the individual search criterion per column.
I'd then like to aggregate the individual scores into a cumulative
relevance score and finally return this score as the final score along
with the rows that match my fixed criterion.
My table follows:
CREATE TABLE products
(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
part_type varchar(10),
ext_color varchar(10),
int_color varchar(10),
lang varchar(20),
);
ALTER TABLE ... ADD FULLTEXT (part_type);
ALTER TABLE ... ADD FULLTEXT (exterior_color);
ALTER TABLE ... ADD FULLTEXT (interior_color);
ALTER TABLE ... ADD FULLTEXT (language);
Some inserts:
INSERT ... (part_type, ext_color, int_color, lang) VALUES
('case','red','blue','english');
INSERT ... (part_type, ext_color, int_color, lang) VALUES
('frame','red','red','spanish');
And so on as to create decent test base of ~500 - 1k records of varying
data...
My first cut at a select statement follows:
select *,
(IF(MATCH (int_color) AGAINST ('red IN BOOLEAN MODE)
1,1,MATCH (int_color) AGAINST ('green' IN BOOLEAN MODE)) +
IF(MATCH (ext_color) AGAINST ('blue' IN BOOLEAN MODE)
1,1,MATCH (ext_color) AGAINST ('grey' IN BOOLEAN MODE)) +
IF(MATCH (lang) AGAINST ('spanish' IN BOOLEAN MODE) 1,1,MATCH
(ethnicity) AGAINST ('spanish' IN BOOLEAN MODE))
)/3 as score
FROM products WHERE
part_type= 'case';
What I am wondering is if this is entirely wrongheaded? I get results
that seem to make sense, but my I am sure that my calculations are off
and I could have flawed (and extremely simplistic test records).
Also is full text search is best suited to scanning for occurrences
across multiple columns where fulltext index would be something like
FULLTEXT(part_type,ext_color,int_color,lang). This suggests that my
search would scan for 1 criterion in all of those columns - not what I
am after.
The typical example seems to be searching for occurrences of
names/titles across columns like authors, titles, snippets, etc... But
I haven't run into anything that addresses capturing relevance given my
needs. If there is another strategy on how to achieve this, I'd be
grateful! Or if this strategy needs tweaking, I'd also be thankful!
I am using MySQL 4.0.13 under RH/Linux 7.4 and Windows 2000.
Thanks,
Alejandro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
reinstall mysql
I would like to remove old mysql ,and install it again. I remove the old directory,Then I tryed to install it again .When I reach step make it give this error. [EMAIL PROTECTED] mysql-standard-4.0.13-pc-linux-i686]# make distclean make: *** No rule to make target `distclean'. Stop. My Question. 1.How can I remove old mysql. 2.I need clear steps to install it again. Regard Saad al-Hajeri _ Get an email address your friends will never forget... FREE! Become [EMAIL PROTECTED] at http://www.emailaccount.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 3.23.39 shuts down by itself on Win2K
Hi all, (BI've got a java application running with MySQL 3.23.39 on a Win2K server. (BNo other application uses this MySQL server. (BAnd yesterday it just shut down without any reason. This is the second time. (BThe first time was a few months back and I didn't pay attention to it. (BDoes anyone have any explanation or similar experience and fix? (B (BHere is the related extract from mysql.err: (B-- (B030331 9:14:13 db\mysql\bin\mysqld-opt: Normal shutdown (B (B030331 9:14:14 db\mysql\bin\mysqld-opt: Forcing close of thread 569 user: (B'testuser' (B (B030331 9:14:14 Aborted connection 569 to db: 'testdb' user: 'testuser' (Bhost: `localhost' (Unknown error) - see (Bhttp://www.mysql.com/doc/C/o/Communication_errors.html (B030331 9:14:14 db\mysql\bin\mysqld-opt: Shutdown Complete (B-- (B (BIt's very unlikely that a query caused MySQL to 'crash' or shut down by (Bitself because there was no query at that time and the last query - 6mins (Bbefore the shut down - was (and has always been) successfully performed (Bwithout any problem. (B (BI've looked around on various web forums for anything similar but I've only (Bgathered that this might be possible if the partition on which resides the (Bdata is full. But that is not the case with the machine. Anyway, there is no (B'disk full' warning in the mysql.err file. (BThe URL for this info is 'intermittently' available though: (Bhttp://www.geocrawler.com/archives/3/108/2002/8/0/9430513/ (B (BNo other place mentions about such problem. Could anyone kindly enlighten me (B:)? (B (BMany thanks, (BAl. (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 3.23.39 shuts down by itself on Win2K
Nope. That is not the case here because the connections to the database are autoReconnect=true. -Original Message- From: Terrance Win [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 7:42 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: MySQL 3.23.39 shuts down by itself on Win2K It seems The client had been sleeping more than wait_timeout or interactive_timeout without doing any requests. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem on installing mysql from mysql-3.23.52-sun-solaris2.8-sparc.tar
Hi, I faced a problem while installing Mysql mysql-3.23.52-sun-solaris2.8-sparc.tar I followd the instructions on INSTALL-BINARY then when I tryed to run mysql it gave my the following error: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) the following what I have got from running mysqlbug: System: SunOS nwpsrv 5.8 Generic_108528-13 sun4u sparc SUNW,Ultra-4 Architecture: sun4 Some paths: /usr/bin/perl /usr/ccs/bin/make /opt/SUNWspro/bin/cc Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -rw-r--r-- 1 root bin 1768408 Mar 13 2002 /lib/libc.a lrwxrwxrwx 1 root root 11 Mar 16 2002 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146296 Mar 13 2002 /lib/libc.so.1 -rw-r--r-- 1 root bin 1768408 Mar 13 2002 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Mar 16 2002 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146296 Mar 13 2002 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-ser ver-suffix= --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared CC=gcc 'CFLAGS=-O3 -fno-omit-frame-poi nter' 'CXXFLAGS=-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' CXX=gcc Perl: This is perl, version 5.005_03 built for sun4-solaris please help Sultan AL-Yahyai Ministry Of Trasportation and Telecomunication Directorate General of Civil Aviation and Meteology Office Phon: (00968) 519611 Fax: : (00968) 519363 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Urgent!!
Hi, I have a certain task to be done, which is a Database Driven website based on MySQL operated on Linux, my Question is: Can I develope such a site using ASP (Active Server Pages)? So, I will avoid spending a lot of time in learning other technology instead of ASP. and also if there is special requirements in order to do it. Best Regards. I.M.A _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help!! Extreme newbie
Being new to MYSQL as well as this list, I'm hoping you'll forgive what I'm sure will be a rather dumb question... I just installed mysql on a new install of SUSE 8.0 and every time I try to connect to the mysql server as a non root use I am denied access..have changed passwords, granted permissions, flushed permissions and yet nothing seems to work... Any suggestions? Thanks, Al Davis AKA extreme mysql/linux newbie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Best SQL Statement
Dear All, I have a table contains airline ticket prices, and I want to get the following data from this table The first 5 records of the lowest price of each airline from each city, and don't want to get a duplicated combination of (airline, city) in this result (even if I didn't see all airlines or all cities, I want to see only five records). I get the required results (in MSAccess) by doing the following: 1- Create a query Q1 with the following source: SELECT distinct [ticket cut rate].[primary airline] FROM [ticket cut rate] 2- Then Create a second Query Q2 with the following source: SELECT Q1.[primary airline], Min([ticket cut rate].[price per adult]) AS [MinOfprice per adult] FROM Q1 INNER JOIN [ticket cut rate] ON Q1.[primary airline] = [ticket cut rate].[primary airline] GROUP BY Q1.[primary airline]; 3- And I get the required results from the following Query: SELECT TOP 5 First([ticket cut rate].origin) AS FirstOforigin, [ticket cut rate].[primary airline], [ticket cut rate].[price per adult] FROM (Q1 INNER JOIN Q2 ON Q1.[primary airline] = Q2.[primary airline]) INNER JOIN [ticket cut rate] ON (Q2.[primary airline] = [ticket cut rate].[primary airline]) AND (Q2.[MinOfprice per adult] = [ticket cut rate].[price per adult]) GROUP BY [ticket cut rate].[primary airline], [ticket cut rate].[price per adult]; How can I get the same result with only one sql statement??? Oh by the way, can I create queries in MySQL just like MSAccess Please help bst rgds Hayan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Advice for dataupload
Dear all, I have two design-identical database, one on my intranet, the other on the internet, is there any procedure that Synchronizes the content of two databases? I'm using PHP applications and MySQL Database, WindowsNT4 IIS Please advice Best Regards Hayan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Quits in Replication
We worked around the problem by using safe_mysqld. (We were just running mysqld directly.) What safe_mysqld does is to launch mysqld and then hang around in the background. If mysqld ever dies unexpectedly, it immediately restarts it. If mysqld is explicitly killed, e.g., with mysqladmin shutdown, safe_mysqld doesn't restart it. We are still seeing the mysqld die a couple of times a day for no apparent reason, but now it gets automatically restarted. This brings up an interesting question. Is MySQL replication so unreliable that it needs a watchdog like safe_mysqld? Is anyone else seeing frequent, unexplained restarts of MySQL in a replication (or even standalone) environment? Is this peculiar to NetBSD? Inquiring minds want to know. Al __ Al Johnson Network Storage Solutions voice 865.675-4070 ext 2 cell 865.604.5869 [EMAIL PROTECTED] -Original Message- From: denonymous [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 14, 2002 3:11 PM To: [EMAIL PROTECTED] Subject: Re: MySQL Quits in Replication From: Al Johnson [EMAIL PROTECTED] Nothing in the MySQL error logs. Don't know what you are refering to as the replication log. There is no binlog for the slave and there are no other logs in the data directory or log directory. Is this something that has to be enabled? I was referring to the slave's replication binlogs, but it appears you don't have them (they'd be on the slave in the MySQL data directory, afaik). I don't know much about replication setup, I'm only going by our setup here. Sorry I can't be of more help! -- denonymous www.coldcircuit.net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Quits in Replication
We are having a serious problem with a simple replication setup using MySQL. One small database with 12 tables is being replicated one way - one master, one slave. Write transaction load is small, about 1 record per second, usually on only one of the tables. Read load is also light. The replication starts out fine. However, after a few hours the mysql daemon on either the master or the slave just quits. No error is logged. No core dump. It just quits. As far as we can tell, nothing special is going on with the system at the time the mysql daemon quits. For example, yesterday, we did a fresh install at about 3:30 pm. At about 7:05 pm the master mysql daemon quit. We restarted it and about 9:30 am this morning the slave mysql daemon quit. ??? Systems using the same versions, but without replication, have never experienced a loss of the mysql daemon. The entire daemon quits? That's not something I've ever run across, and it seems even stranger with the fact that both the master and the slave have done it. Yes, mysqld disappears. Poof! Have you checked both the mysql error logs and the replication logs? The replication logs should be in the MySQL data directory. When a slave stops replication, that's where the last known error is logged, and usually sheds insight into *that* problem. Nothing in the MySQL error logs. Don't know what you are refering to as the replication log. There is no binlog for the slave and there are no other logs in the data directory or log directory. Is this something that has to be enabled? Al __ Al Johnson Network Storage Solutions voice 865.675-4070 ext 2 cell 865.604.5869 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Quits in Replication
Hi, We are having a serious problem with a simple replication setup using MySQL. One small database with 12 tables is being replicated one way - one master, one slave. Write transaction load is small, about 1 record per second, usually on only one of the tables. Read load is also light. The replication starts out fine. However, after a few hours the mysql daemon on either the master or the slave just quits. No error is logged. No core dump. It just quits. As far as we can tell, nothing special is going on with the system at the time the mysql daemon quits. For example, yesterday, we did a fresh install at about 3:30 pm. At about 7:05 pm the master mysql daemon quit. We restarted it and about 9:30 am this morning the slave mysql daemon quit. ??? Systems using the same versions, but without replication, have never experienced a loss of the mysql daemon. Ideas? Suggestions? Environment is NetBSD 1.5Y with MySQL 3.23.47 (the latest supported by NetBSD). Al __ Al Johnson Network Storage Solutions voice 865.675-4070 ext 2 cell 865.604.5869 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Inserting strings containing spaces only fails?
It's in the doco: http://www.mysql.com/doc/C/H/CHAR.html snip Values in VARCHAR columns are variable-length strings. You can declare a VARCHAR column to be any length between 1 and 255, just as for CHAR columns. However, in contrast to CHAR, VARCHAR values are stored using only as many characters as are needed, plus one byte to record the length. Values are not padded; instead, trailing spaces are removed when values are stored. (This space removal differs from the ANSI SQL specification.) /snip Regards Al -Original Message- From: Wout Neirynck [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 13, 2002 3:40 PM To: [EMAIL PROTECTED] Subject: Inserting strings containing spaces only fails? Hello, Appareantly it's impossible to insert a string, consisting entirely out of spaces, in a textual field (I've tried varchar(19) and char(2) types, they both don't work). MySQL doesn't complain about it, but it trims the strings down to the empty string. If I try a select searching for the spaces-only string afterwards, it gives me zero results. The same problem occurs when updating. The problem does not occur, however, when converting the columns to text types. MySQL version: 3.23.47-nt OS: Windows NT 4.0 Problem can be reproduced with MySQLGui and Java using the JDBC-MySQL bridge driver. Is this a bug or do I have to do something special to disable the 'string trimming'? Any help would be greatly appreciated! Wout Neirynck - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: NULL timestamps not possible?
Of course, the page lies somewhat: it says that if you omit the column in
an insert, it should get set to now(), but the following example shows it
doesn't - notice that u is omitted in the insert, but gets set to 0
instead of now().
create table foo (t timestamp, u timestamp);
insert into foo(t) values('');
// inserts in both t and u.
This is in the doco. Only the first TIMESTAMP column is updated
automatically.
Extracted from http://www.mysql.com/doc/D/A/DATETIME.html
The TIMESTAMP column type provides a type that you can use to automatically
mark INSERT or UPDATE operations with the current date and time. If you have
multiple TIMESTAMP columns, only the first one is updated automatically.
Regards
Al
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: encrypt password
http://www.mysql.com/doc/M/i/Miscellaneous_functions.html
Using the PASSWORD function is an irreversible process. Check the above
link.
A work around is you always deal with the encrypted password on the server
side.
E.g. When the user will login you encrypt the submitted password and do a
SELECT with the resulting String.
Regards,
Al
-Original Message-
From: Manish Mehta [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 04, 2002 2:12 PM
To: mysql
Subject: encrypt password
Hi ,
I am working in mysql . with the help of password() function I insert the
data in some table .
for example :-
insert into a values (password('name'));
when I fire select query . It shows me this - 23e6065b74
I wants to decrypt the data again in same format as I insert with Select
query.
please tells me the process .
Thx in advance
Manish Mehta
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: FULLTEXT error?
Hi Doug, What's the MySQL version you're running? Your SQL query worked fine on my PC. MySQL 3.23.47 Win98SE Last time I had some problem (that was 3.23.32 I think) when mixing VARCHAR and TEXT types in the FULLTEXT index. After upgrading to MySQL 3.23.47, I did not encounter anymore problem like these... Regards, Al -Original Message- From: Doug Dalton [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 28, 2002 5:02 AM To: [EMAIL PROTECTED] Subject: FULLTEXT error? Error SQL-query: CREATE TABLE quotes ( id int(9) NOT NULL auto_increment, author varchar(255), content text, PRIMARY KEY (id), UNIQUE KEY id (id), FULLTEXT KEY author (author,content) ) TYPE=MyISAM; MySQL said: You have an error in your SQL syntax near 'KEY author (author,content) ) TYPE=MyISAM;' at line 7 I have also tried to create the table and ALTER TABLE quotes ADD FULLTEXT author (author,contect) I get the same error, is this a function at is only in 3.23 Max or is it in the standard version as well? R/Doug - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL .so missing - help!
Hi I am new to MySQL and PHP, so - please be gentle.. We are running PHP4.0.1 and mysql 9.38 (Distrib 3.22.32) on our development server (Debian Linux). I am trying to setup a database with mysql and PHP. I tried to install phpMyAdmin 2.2.3 and can't access the index page. I get an error about the mysql extension: cannot load MySQL extension, please check PHP Configuration MySQL is there, I can get to it and create databases etc but it won't talk to php. In php.ini, there is no reference to MySQL.so under Dynamic Extensions. Further, I can't locate the file MySQL.so or mysql.so *anywhere* on the server. I used the locate command to try find it. I didn't set up apache, php and mysql and am a little nervous about making changes - any suggestions? Thanks Alka [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL.so missing - help!
Hi I am new to MySQL and PHP, so - please be gentle.. We are running PHP4.0.1 and mysql 9.38 (Distrib 3.22.32) on our development server (Debian Linux). I am trying to setup a database with mysql and PHP. I tried to install phpMyAdmin 2.2.3 and can't access the index page. I get an error about the mysql extension: cannot load MySQL extension, please check PHP Configuration MySQL is there, I can get to it and create databases etc but it won't talk to php. In php.ini, there is no reference to MySQL.so under Dynamic Extensions. Further, I can't locate the file MySQL.so or mysql.so *anywhere* on the server. I used the locate command to try find it. I didn't set up apache, php and mysql and am a little nervous about making changes - any suggestions? Thanks Alka [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table Field Size ..
Hi, I want to ask what difference can be between two MySQL Table CHAR fields the first one's size (In Design) is (10) and the other is (100), but the tow contains the same length of charachters like 3 charachters. Does it effect speed, data size... Please advice Best Regards Hayan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Which is Faster
Dear all, Which of the following SQL queries is faster and better select thefield from thetable group by thetable Or select distinct thefield from thetable? and WHY? Best Regards Hayan Get your own 800 number Voicemail, fax, email, and a lot more http://www.ureach.com/reg/tag - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table Lock
Hi, Is there a way to lock a table so that only one user can change it and then unlockit? I need to do this coz I want visitors to retrieve some values from a database but each value must be retrieved only by one user and then marked up to be expired, so I don't want two users to retrieve the same value Any suggestions Best Regards Hayan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Table Lock
Hi, Is there a way to lock a table so that only one user can change it and then unlockit? I need to do this coz I want visitors to retrieve some values from a database but each value must be retrieved only by one user and then marked up to be expired, so I don't want two users to retrieve the same value Any suggestions Best Regards Hayan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with Load Data InFile
Dear all, I had a problem with load data infile syntax LOAD DATA INFILE '$file' REPLACE INTO TABLE $table FIELDS TERMINATED BY '|' that the original data has already carriage returns was considered as new records in the database, I solved it by using the following syntax: LOAD DATA INFILE '$file' REPLACE INTO TABLE $table FIELDS TERMINATED BY '|' LINES TERMINATED BY '^' well it worked BUT it generates a strange blank charachter at the beginning of the first field of each record is there any soloution that brings me peace of mind for such problems, concerning that I previously suffered from developing in local environment before publishing which forced me to use two different syntaxes of LOAD DATA... the first one with ADDSLASHES() function to the $file argument and the second without this funcion Please advice if you a solution - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with LOAD DATA INFILE
HI ALL: Happy New Year ;) I have a text file generated from MSAccess database, one of the fields (Long Text) have Carriage returns within the text, So the data of some records falls in more than one line in the text file, when I'm using the SQL statement LOAD DATA INFILE... I have only the first line of the data in this field, You know the data in the file looks like : 8|RO|The rates are applicabe for prepaid shipments only. Other charges: Awb fees 230Syp, Handling 0.65Syp per KG, Stamp 16Syp.|\r\n 9|KL|Other charges: Awb fees 230Syp, Handling 0.65Syp per KG, Stamp 16Syp, Security Charge 0.13Usd per Kg based on actual weight of the shipment.|\r\n 10|JU|Other charges: Awb fees 230Syp, Handling 0.65Syp per KG, Stamp 16Syp.|\r\n Please Help Best Regards Hayan Get your own 800 number Voicemail, fax, email, and a lot more http://www.ureach.com/reg/tag - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Limitation of Full-Text indexing in MyISAM table?
Hi all, Is there any limitation in creating full-text indexes on MyISAM tables? Currently, I have a table like this: CREATE TABLE IF NOT EXISTS mytable ( UId BIGINT(20) NOT NULL AUTO_INCREMENT, Title VARCHAR(255) NOT NULL, Field1 TEXT, Field2 TEXT, Field3 VARCHAR(255), Field4 TEXT, Field5 TEXT, Field6 VARCHAR(50), Field7 VARCHAR(50), Field8 VARCHAR(50), Field9 TIMESTAMP NOT NULL, Field10 TIMESTAMP NOT NULL, PRIMARY KEY(UId), INDEX article_idx1(Field6), INDEX article_idx2(Field7), INDEX article_idx3(Field8), FULLTEXT article_idx4(Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8) ) TYPE=MyISAM; But I keep getting the following error when I try to delete some row: java.sql.SQLException: General error: Incorrect key file for table: 'mytable'. Try to repair it So far, I've figured out that the error is due to mixing VARCHAR and TEXT type in the FULLTEXT index but I dunno why is there a problem. Haven't found much in MySQL doc about that yet. Is the problem more on the Java side? Could anyone suggest any solution or direct me to some resources? (Other than converting all my fields to TEXT :)) Many THANKS!!! Al _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Limitation of Full-Text indexing in MyISAM table?
Hi Sergei! Thanks for the reply! That was fast :) -Original Message- From: Sergei Golubchik [mailto:[EMAIL PROTECTED]] Sent: Monday, December 10, 2001 10:48 PM To: Al Caponi Cc: MySQL Mailing List Subject: Re: Limitation of Full-Text indexing in MyISAM table? Hi! On Dec 10, Al Caponi wrote: Hi all, Is there any limitation in creating full-text indexes on MyISAM tables? Currently, I have a table like this: CREATE TABLE IF NOT EXISTS mytable ( UId BIGINT(20) NOT NULL AUTO_INCREMENT, Title VARCHAR(255) NOT NULL, Field1 TEXT, Field2 TEXT, Field3 VARCHAR(255), Field4 TEXT, Field5 TEXT, Field6 VARCHAR(50), Field7 VARCHAR(50), Field8 VARCHAR(50), Field9 TIMESTAMP NOT NULL, Field10 TIMESTAMP NOT NULL, PRIMARY KEY(UId), INDEX article_idx1(Field6), INDEX article_idx2(Field7), INDEX article_idx3(Field8), FULLTEXT article_idx4(Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8) ) TYPE=MyISAM; But I keep getting the following error when I try to delete some row: java.sql.SQLException: General error: Incorrect key file for table: 'mytable'. Try to repair it So far, I've figured out that the error is due to mixing VARCHAR and TEXT type in the FULLTEXT index but I dunno why is there a problem. Haven't found much in MySQL doc about that yet. No, mixing VARCHAR and TEXT is perfectly legal (even fulltext search example in the manual does it). Yup, seen that... which makes me even more frustrated :( What version of MySQL are you using ? I'm using MySQL 3.23.39 on Win98SE with JSDK 1.3 Can you create a testcase for that ? I'm not too sure of how to go about creating that... Do you mean the test code or just a rough idea of the testing? Roughly, I'm just inserting a record and deleting it based on the UId field. Regards, Al _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Limitation of Full-Text indexing in MyISAM table?
Hi, Hi! On Dec 10, Al Caponi wrote: But I keep getting the following error when I try to delete some row: java.sql.SQLException: General error: Incorrect key file for table: 'mytable'. Try to repair it So far, I've figured out that the error is due to mixing VARCHAR and TEXT type in the FULLTEXT index but I dunno why is there a problem. Haven't found much in MySQL doc about that yet. No, mixing VARCHAR and TEXT is perfectly legal (even fulltext search example in the manual does it). Yup, seen that... which makes me even more frustrated :( What version of MySQL are you using ? I'm using MySQL 3.23.39 on Win98SE with JSDK 1.3 This one is old. (June 2001) The bug you've hit was fixed in Jule. Is it the 'UPDATE|DELETE) ...WHERE MATCH bugfix' in MySQL 3.23.45? I'm not performing a delete using a WHERE MATCH but on a BIGINT column type. Anyway, I'll upgrade my MySQL version and check it out. Thanx Al _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Limitation of Full-Text indexing in MyISAM table?
On Dec 10, Al Caponi wrote: But I keep getting the following error when I try to delete some row: java.sql.SQLException: General error: Incorrect key file for table: 'mytable'. Try to repair it So far, I've figured out that the error is due to mixing VARCHAR and TEXT type in the FULLTEXT index but I dunno why is there a problem. Haven't found much in MySQL doc about that yet. No, mixing VARCHAR and TEXT is perfectly legal (even fulltext search example in the manual does it). Yup, seen that... which makes me even more frustrated :( What version of MySQL are you using ? I'm using MySQL 3.23.39 on Win98SE with JSDK 1.3 This one is old. (June 2001) The bug you've hit was fixed in Jule. Is it the 'UPDATE|DELETE) ...WHERE MATCH bugfix' in MySQL 3.23.45? I'm not performing a delete using a WHERE MATCH but on a BIGINT column type. Anyway, I'll upgrade my MySQL version and check it out. Problem solved after upgrading 3.23.46a for Win98. Haven't used UPDATE|DELETE) ...WHERE MATCH yet... Cheers! _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySql as an Embedded Java Database
We are developing an application for commercial distribution using a Java client, JDBC, and an SQL database . It will come in 2 versions - a standalone single-user version that will run on a PC and multi-user version where the database will run on a separate server. For the standalone version we started using an embedded Java database. A number of issues are now causing me to look at MySql for both versions. For MySql to work, we need to --- 1. Install MySql silently along with the Java application - basically a one-click install. 2. Be able to start and shutdown MySql server when the application is started and shut-down. 3. No database maintenance - or able to automate whatever maintenance there is - preferably using a Java interface. Can MySql do the job? And if so, can someone point me in the right direction? For example, embedded MySql appears to require a C interface - could it also be made to work with JDBC? What is required for a silent install? Thanks for your insights. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
row security
Hi, I wanted to know if you can allow certain users to edit certain rows. Please, if anyone knows how to do it, then please tell me, thanks.
Access denied
*** NOTICE: This transmission contains information relating to Schlumberger's business activities in Libya. If you are a United States (U.S.) Person (i.e., U.S. Citizen; Permanent Resident Alien green card holder; employee of a U.S. Company; or physically in the U.S.) you are prohibited by law from disclosing, responding, or otherwise acting upon any of the information contained in this transmission. If you are a Schlumberger employee, please consult the OFS Export Administration web site: http://www.sugar-land.spc.slb.com (under Services) to ensure that any actions which may be taken in connection with this transmission are in full compliance with Schlumberger's Trade Control Policy and procedures. *** Hello mysql, Please can you tell me why i got the following message when i was trying to connect to mysql database from the server. the connection statement was like : - c = DriverManager.getConnection(jdbc:mysql://IT-SNS:3306/slb?user=root=root); i have got the following message :- java.security.AccessControlException: access denied (java.net.SocketPermission IT-SNS resolve) so please help me to solve this problem. -- Best regards, Sami mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MFC
Hi, Im new two this list as well, and I was wondering if anyone know how to connect an MFC application to a remote database? It works fine with my local database but when I come to create a file DSN for the remote one using MyODBC, it wouldnt connect. Any ideas? Thanks.
Build dir vs. execute dir
We need to build MySQL from the source distribution and install into a staging area, and then copy the contents of that staging area over to /usr/local/mysql. During our build process, we cannot modify /usr/local. The configure script lets us assign a prefix for installation into the staging area, but then the MySQL scripts and executables all expect to be running from the the staging area, not from /usr/local/mysql. Is there a way to do this without modifying the configure script (or any of the source)? Or is there some utility that can move a MySQL installation around, modifying the numerous embedded references to where it was originally installed? Thanks - Al - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
