Embedded MySQL
Hi All, Can some kind soul tell me from where I can download the "Embedded Library" version of MySQL? -- TIA, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Embedded MySQL
Hi Chris and Melvin, Thanks a lot for the quick responses. I have found it. Actually, it comes bundled with the main distribution. The documentation is in the main reference manual and the header can be found in the include directory. -- Thanks again. Asif I don't see a binary version avaliable, but I know the following configure option exists: --with-embedded-server Build the embedded server (libmysqld). <-- I'm assuming this is what you want .. I haven't tried this one, but I think this might help http://mysql-je.sourceforge.net Can some kind soul tell me from where I can download the "Embedded Library" version of MySQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Version Numbers - Precedence
Hi, Mathematically speaking, the 5.0.22 I am using came _before_ 5.0.5. Is that correct? Or 5.0.5 came first and then came 5.0.22? -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Version Numbers - Precedence
Hi, I am replying to my own post to clarify my query. Actually, I have 5.0.22 installed and I want to use the BIT data-type on InnoDB Engine. Can I do it or do I have to install 5.0.5 version (which is not stable) ? -- TIA Asif On 6/24/06, Asif Lodhi <[EMAIL PROTECTED]> wrote: Hi, Mathematically speaking, the 5.0.22 I am using came _before_ 5.0.5. Is that correct? Or 5.0.5 came first and then came 5.0.22? -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SSL on MySQL 5.0.22
Hi, I am using 5.0.22 on Windows. As I read in the reference manual, almost "ALL" SSL values/options are specific to 5.1.x versions. Do I stand _ANY_ chance to use SSL anyway? Specifically, I want to know exactly HOW I can enable SSL support for MySQL on Windows using which options, variables, etc. Thanks in advance, -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SSL on MySQL 5.0.22
Hi, How can I connect from a Windows client (VB/ODBC Connector) to a MySQL Server 5.0.22, also running on Windows (Max version installed as a service using Local-Service Account) with all tables using InnoDB storage engine USING SSL? Perhaps using OpenSSL on a Linux-based installation would be easier. But mine is a Windows-based MySQL Server. Any hints, comments, help? -- Best regards, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB obeyance of PRIMARY KEY constraint - confirmation
Hi, I did read in the manual that the field level REFERENCES constraints on InnoDB tables do not work as expected and one has to first define a table level index and then create a table-level FOREIGN KEY constraint for the field to make it work. I just would like to know if that's the case with the field-level PRIMARY KEY constraints as well. Do I have to define table-level PRIMARY KEY constraints as well? I also did read that InnoDB is very good at long PRIMARY KEYs. Does "long primary keys" means keys having more than one field? or keys having a greater character length? In case more fields per primary key are a problem, I am using unique indexes to link-up my tables to insure my db's referential integrity. I am not using any MyISAM tables. Any comments/hints? -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CREATE TABLE Failure
Hi All, I have been repeatedly trying to create the following table - without __ANY__ success. Looks like there is REALLY something wrong with the MySQL engine or something! Here is the script: CREATE TABLE Order ( DID int not null, DeskNo int not null, OrderDate datetimenot null, /* KOT Date */ OrderNo int not null, /* KOT No */ CRMID int not null, /* Customer Relationship Management ID B2PID */ CustomerID int not null, /* PersonID of Customer, Member, etc. */ TableNo int not null, WaiterIDint not null, /* PersonID of Waiter */ OrderClerkIDint not null, /* PersonID of Order-Clerk */ Status int not null, /* Order Status - Open;1;In-Process;2; Cooked/Ready;3;Delivered;4; Cancelled;-1, -2, -3, .. */ CONSTRAINT Order_UQ UNIQUE INDEX (DID, DeskNo, OrderDate, OrderNo), INDEX CRMID_NDX (CRMID), FOREIGN KEY CRM_FK_NDX (CRMID) REFERENCES RelationB2P (ID), INDEX CustomerID_NDX (CustomerID), FOREIGN KEY Customer_FK_NDX (PersonID) REFERENCES Person (PersonID), INDEX DID_NDX (DID), FOREIGN KEY Department_FK_NDX (DID) REFERENCES EntityBranchDept (DepartmentID), IDNEX WaiterID_NDX (WaiterID), FOREIGN KEY WaiterID_FK_NDX (WaiterID) REFERENCES Person (PersonID), INDEX OrderClerkID (OrderClerkID), FOREIGN KEY OrderClerk_FK_NDX (OrderClerkID) REFERENCES Person (PersonID) ) ENGINE=InnoDB; You guys can delete all the index/references and other clauses! Even then this table doesn't get created! I'd appreciate your help. -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUERY
Hi John, On 6/24/06, John Hicks <[EMAIL PROTECTED]> wrote: Karl Larsen wrote: > ..I was glancing through the mamouth > MySQL reference manual ... I worked on an Oracle-9i development project around two years back. If I recall correctly, there were at least 30 manuals to read !!! -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multi-column indexes on InnoDB tables
Hi, I have created 4, 5 and 8 column unique indexes ( I had to - the application requirements dictated me to do so) on some tables in an InnoDB only database on a 5.0.22 MySQL server running on XP-SP2 machine. The 4-col indexes are on order, invoice and receipt tables; the 5-col indexes are on detail tables corresponding to these master transaction tables; the 8-col indexes are on link-up tables such as OrderMenuItems, InvoiceOrders (used to record orders stacking up against each invoice), InvoiceReceipts (multiple receipts against one invoice), etc. The many-col-index requirement is due to the fact that we have several departments and each department has several order/invoice/receipt desks. There is a great likelihood of power failures on a daily basis. We are not using any UPSes. We have generators but they start after 15-20 minutes after a power failure. The departments are scattered over 50-60 acres (perhaps more) public area and even network failures can occur because of weather-specific corrosion, mice-digging-into-the-ground-and-eating-cable and other such problems. So I have to store departmetn-desk specific data in local tables. In addition, communication with the main server is minimal. Transaction load is a few thousands transactions a day. ALL I am asking is how strong you think MySQL stands up in such a business scenario. I have even created the manual business procedures for power-failure scenarios. I DO need to know HOW gracefully MySQL will recover after each power failure or pull-the-plug situations. I have to decide as to which database server I am going to use. I have so far only designed the db and written table-creation scripts only. I would genuinely appreciate your help and advice. -- Best regards, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is there a way to optimize like '%..%' searches ?
What about partitioning the database? On 7/1/06, Dan Buettner <[EMAIL PROTECTED]> wrote: Wow, that is a tough one. My question would be, how often is older data really accessed? Could you start incorporating a default date range like "past 3 months" or "past 1 year" into all searches, but allow people to override it if needed? Then if you add an index on the timestamp column it would help any searches with a date clause. Dan On 6/30/06, Martin Jespersen <[EMAIL PROTECTED]> wrote: > It's basically a log that people needs to be able to search with > wildcards in... the log grows many thousand records per day and never > gets smaller, so searches just gets slower and slower. There is a sort > field, the timestamp which is used in the searches, but it only makes > the searches lsower yet instead of helping in the query, since all that > does is sort by timestamp desc > > > basically the query works like this: > > some searches for "foo bar baz" and i create an sql that looks like: > > select * from table where logline like '%foo%bar%baz%' order by > timestamp desc. I have wrekced my brian plenty but have not come up with > any otehr way of doing it that gives the needed flexibility in the > searces. Since what is searched for is not words as such - most loglines > are actually a single "word" on the form > "somethingsomethingsomethingsomethingsomething" > and so on - the logline is varibale length and variable number of > "entities" between the sepcial chars (even the special chars are very > varied) and of no specific format, thus the needed flexibility in the > searches. > > If i coud i would changes the log format, but that is not possible since > this database has loglines going all the way back to the 1980's (with > more "old" lines being added as well as new ones) and the format has > changed many times since then... > > Basically i am stuck with a very crappy heap of data i need to be able > to search in a smart manner. > > Fulltext seaching would have been ideal if i was able to do boolean > macthes with leading wildcard, but without it is useless :/ > > btw the result doesn't need scoring for relevance at all - what is > searched for is always the newest matches to the searchterm, regardless > of relevance (relevance could become handy at a later stage tho, but i > dare not even think about it atm) > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Returning Resultsets from Stored Routines
Hi All, I want to restrict all direct access to tables and allow users access only through the stored procedures. However, I haven't seen any explicit mention in the docs that one can return a resultset/recordset from a stored routine - that's what I can do using MS-SQL Server. If this cannot be done then the security benefit of limiting access only to the stored routines cannot be achieved. I will very much appreciate your help, comments and advice. -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Monitoring Slow Queries
Hi, Though I understand very well that it has been discussed lots of time before but I don't have time to browse through the previous archives and dig out the stuff I need. So, guys, I would be thankful if you could give me your valuable advice that I need right now. I just need to know 1) What parameters I need to set in my.cnf to log slow queries so that they stick out conspicuously and get noticed, and 2) How I can find out from the log that MySQL creates as a result of 1) as to which queries are running slow. -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database Return Errors
Hi, I am developing a VB6 app with a MySQL-5.0.22/WinXP backend. I have skimmed the Stored Procedures/Triggers docs and it looks like I can define custom error-names or number - though I have also seen the "Handlers" in the same doc. The question is: Can I get the error-codes or error-names that MySQL returns in VB6',s ADO.Erross collection? Will I get one if MySQL throws an error - such as when a duplicate constraint is violated? -> so that I can display meaningful error messages to the user. -- TIA, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Monitoring Slow Queries
Thanks, Philip. On 8/2/06, Philip Hallstrom <[EMAIL PROTECTED]> wrote: .. http://hackmysql.com/mysqlsla . That's definitely of immense help. -- Thanks a zillion, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Return Errors
Thanks for replying, John. On 8/2/06, John Meyer <[EMAIL PROTECTED]> wrote: Have you checked out MyConnector/NET and the MySqlException class? From: Asif Lodhi [mailto:[EMAIL PROTECTED] Subject: Database Return Errors The question is: Can I get the error-codes or error-names that MySQL returns in VB6',s ADO.Erross collection? Will I get one if MySQL throws an error - such as when a duplicate constraint is violated? -> so that I can display meaningful error messages to the user. However, I am developing the app using VB6 and MS.NET driver, if I am right, cannot help me in my scenario. And I cannot enjoy the benefit of using MySQLException class either. Do you know of any such class for VB6? -- Thanks Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
delimiter in mysql client
Hi, When I create scripts, I change the delimiter at the beginning of the create_table script from ";" (without quotes) to "//". At the end of the script, I change the delimiter back to the original ";". This is according to what is described in doc at www.mysql.com. However, when I run these scripts from mysql client, I don't get the original delimiter back - all I get is a prompt "->" no matter how many times I press Enter - like this: -> -> -> -> The mysql client doesn't show the above behavior when I change the delimiter manually by actually typing it and then copying and pasting the script contents (without the delimiter change statements lines) and then again change back to the normal delimiter ";" manually by typing the "DELimiter ;" in the mysql client. Is there any way I can hope to run my scripts - without having to copy and paste them into mysql client? -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ROLLBACK/COMMIT in Stored Procedures
Hi, In my stored procedures, i want to ROLLBACK when I encounter any invalid values. However, as it happens, I cannot because MySQL does not support COMMIT/ROLLBACK functionality right now (as of ver. 5.0.22 on WinXP Pro). I am setting session variables (Set @XX="Error Message') according ot each anomally I find in the IN args of the procedures and inserting duplicate values in a temporary table to make MySQL throw me a duplicate-key error that I can then check from VB6 using another procedure giving me the @XX value. Is this the correct way? Is there any other better way of doing the same thing? -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't get v5.0.22 to work;alternatives?
On 8/1/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I filed bug #20941 (mysqld seg faults during instance configuration on XP pro, http://bugs.mysql.com/?id=20941) some time ago and have seen no real movement on fixing it. I'm dead in the water. I can't get MySQL v5.0 to work on my system. I'd like to try installing an older build as a stop gap (even if it has other, non-critical bugs). Is there somewhere I can find an older build? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: delimiter in mysql client
Hi, I am replying back to my own post. Sorry, I forgot to mention that I am using mysql client on WinXP Pro SP2 and MySQL-5.0.22 is installed on the same machine. Any hints as to why mysql client is showing this behavior? Thanks in advance, -- Asif On 8/4/06, Asif Lodhi <[EMAIL PROTECTED]> wrote: Hi, When I create scripts, I change the delimiter at the beginning of the create_table script from ";" (without quotes) to "//". At the end of the script, I change the delimiter back to the original ";". This is according to what is described in doc at www.mysql.com. However, when I run these scripts from mysql client, I don't get the original delimiter back - all I get is a prompt "->" no matter how many times I press Enter - like this: -> -> -> -> The mysql client doesn't show the above behavior when I change the delimiter manually by actually typing it and then copying and pasting the script contents (without the delimiter change statements lines) and then again change back to the normal delimiter ";" manually by typing the "DELimiter ;" in the mysql client. Is there any way I can hope to run my scripts - without having to copy and paste them into mysql client? -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL+OpenVPN
Hi, Can anyone on this list tell me _how_ i can connect to my MySQL-5.0.22 server running on Windows-XP-SP2 using OpenVPN on Windows? It looks like a good VPN/SSL implementation. -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't get v5.0.22 to work;alternatives?
Hi, Earlier, I forgot to CCed the reply to the list and, accidentally, posted a blank reply instead. So, here is the copy of the reply that I sent to the original poster - in case, someone else has the same problem. Additionally, I have found that the MySQL ODBC driver installs best when you do it manually - that is, hand-copy files into the Windows System32 directory. In addition, as I have found, it complains that some MSVCR7.dll is missing. When I searched for the file on Google, the search engine led me to some DllFiles.com (or some similar webpage) where I got the file, downloaded the same to my computer and everything worked like a cinch! -- Good luck, Asif On 8/1/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I can't get MySQL v5.0 to work on my system. I got MySQL 5.0.22 running successfully on Win-XP-SP2 as follows: 1) Download the no-install zip package of MySQL-5.0.22 from the website. 2) Unpack it in the directory of your choice (C:\mysql5 - for example) 3) Create a top-level folder - C:\mysql5Data for the data directory. 4) Cut the contents of C:\mysql5\data directory and paste the same into C:\mysql5Data. 5) Delete C:\mysql5\data directory. 6) Create a copy of an appropriate mysql-xxx.ini file and rename it to MY.INI. 7) Change the value of the data-dir variable to C:/mysql5Data - NOTE FORWARD INSTEAD OF BACK-SLASHES. 8) Create another top-level C:\InnoDBData folder. 9) Change appropriate InnoDB data-directory variables in C:\my.ini (with forward slashes!) 10) Right-click MY COMPUTER icon on the desktop and select properties from the shortcut menu. Goto the Advanced tab and select Environment [Variables]. Select PATH in the \ system variables and add C:\mysql5\bin; at the beginning of it. Apply and OK. 11) Select RUN from the start menu, type CMD and press ENTER. 12) Right click the C:\InnoDBData folder, select Properties from the shortcut menu and add "LOCAL SERVICE" user-account in the security tab and give it "Full Control" access. 13) Do the same as in 12) with the C:/mysql5Data folder. 12) type mysqld-nt --install YourServiceName --defaults-file=C:\mysql5\my.ini --local-service and press ENTER. 13) type NET START YourServiceName AND PRESS ENTER. Now you have MYSQL-5.0.22 installed on WinXP-Pro-SP2. Forget about the automatic installer. I got similar errors like yours. -- HTH, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Code snippet to run at database service startup
Hi, What if I want certain variables initialized and certain code to run at the database service startup? I am running 5.0.22 on Windows XP Service Pack 2. Are there any tirggers that can tell me that the database is starting up? -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to set "User" variables (not system/session ones) at database startup
Hi, I want to set certain _user_ variables, which I will create myself, at the database startup time so that all clients can use the values stored [only one time at the beginning] in those variables instead of computing the same each time in triggers or other code. It's just like the package-level "host" variables of Oracle. How can I do the same in MySQL? I am using 5.0.22 and will switch to 5.0.24 soon. -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Expressions, SQL Aggregate functions & SELECT Performance - index usage
Hi, I am having to write queries that contain operators such as MOD and DIV in addition to other normal arithmetic operators to add, multiply, divide, etc. I am also using MAX(), MIN() in the same queries. Actually, I am using INSERT ... SELECT MAX() * X ... FROM TABLE idiom to calculate the max key value to be inserted in the table instead of using the auto-increment (because it's rollback-unfriendly). However, I wonder if using such expressions (I not using any other functions except SQL Aggregate functions) in the select column-list would slow down the INSERTs and what the performance implications are if there are around 75 users. -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SSH Server for MySQL-5.0.22 Server on WinXP-SP2
Hi Alan and others, On 8/7/06, Alan Vinh <[EMAIL PROTECTED]> wrote: I would like to know how they got MySql to work with SSL... :) Sorry fellows. I should have gone through the docs first. As it happens, MySQL-5.0.22 comes bundled with SSL support - albeit with yaSSL - not OpenSSL. The SSL files are supposed to be in PEM format. In docs, it's mentioned that the connection to an SSL-enabled server can be established via the "C" API. As far as I am concerned, that means writing a static DLL in "C" and call the functions written to connect securely (via SSL, that is) to MySQL in VB6. Am I right? Please comment. I did register on CACert.org a couple of days back. Would any of you guys tell me __how__ MySQL is going to work with the public key, private key and CA file of the CACert.org? Any help? -- TIA, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert ... Select Max() .. transactional Query optimization on an InnoDB table
Hi, Using MySQL-5.0.22/Win-XP-SP2 and storing data in InnoDB tables. Clients connect from VB6. Enclosing all transactions in "START TRANSACTION ... COMMIT" statements from VB6 clients. I have the following query for a table tmp2 with a column x of data-type INT. Insert into tmp2(x,y,x) Select ((Max(x))-((max(x) div 100) * 100)+1 as pid, 'text1','text2' from tmp2 where ((x div 100)=2147 having (((max(x))-((max(x) div 100) * 100)) < 483647; I have the following questions: 1) Will the above INSERT statement run if I use constant values for y and z? without a GROUP BY clause? 2) How I can make the above select statement return a 0 (ZERO) value for pid in case it returns an empty resultset? Though I am also thinking about it but it'd be great if you guys could give me some hints. I want to keep it fast so I haven't used any functions, etc. -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Get a Numeric Zero instead of NULL in SELECT
Hi, I have a query: insert into tmp2 (x) select ((t3.m * 100)+b.id ) as x2 fromtmp3 t3 LEFT JOIN (select ((max(x)) - ((max(x) div 100) * 100))+1 as pid from tmp2 where ((x div 100)=2147) having (((max(x)) - ((max(x) div 100) * 100)) < 483648)) b ON t3.m=b.pid The table t3 has a single INT field "m" with a single record and value: 2147. The table tmp2 has a single INT field "x" with no records. The above code returns NULL in the 2nd column of the SELECT that I can't add to or multiply with another number to get the final value to insert into the table tmp2. I am using INNODB tables on 5.0.22 running on a WINDOWS-XP-SP2. Around 75 Clients connect from VB6/Windows. I am STARTing TRANSACTIONs and COMMITing them from VB6 client-code. Since I am also using SQL STRICT mode with more stricter parameters in the MY.INI. I don't want to use functions as that will impair the query speed. Do you know of any way that I could use to get a numeric ZERO instead of a null in the 2ND column of the SELECT? Any suggestions/comments? Thanks in advance, -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get a Numeric Zero instead of NULL in SELECT
Hi,
I am replying to my own post to tell you that now I am using
CASE WHEN {condition} THEN END
construct to check for NULL and returning numeric ZERO. On the face
of it, CASE doesn't
seem to be function - it's an operator - isn't?
However, I would now like to ask you whether I can use some kind of an
Oracle-like USE_INDEX optimization hint in MySQL to get it to use a
specific index in the SELECT? I used explain on it and it tells me
that it's using index on all except on the the query I am using as the
2nd table. Is there any way I can speed it up?
Any suggestions?
--
Asif
On 8/10/06, Asif Lodhi <[EMAIL PROTECTED]> wrote:
I have a query:
insert into tmp2 (x)
select ((t3.m * 100)+b.id ) as x2 /* I AM USING CASE WHEN cond .. here */
fromtmp3 t3 LEFT JOIN
(select ((max(x)) - ((max(x) div 100) * 100))+1
as pid
from tmp2
where ((x div 100)=2147)
having (((max(x)) - ((max(x) div 100) * 100)) <
483648)) b
ON t3.m=b.pid
The table t3 has a single INT field "m" with a single record and
value: 2147. The table tmp2 has a single INT field "x" with no
records. The above code returns NULL in the 2nd column of the SELECT
that I can't add to or multiply with another number to get the final
value to insert into the table tmp2. I am using INNODB tables on
5.0.22 running on a WINDOWS-XP-SP2. Around 75 Clients connect from
VB6/Windows. I am STARTing TRANSACTIONs and COMMITing them from VB6
client-code. Since I am also using SQL STRICT mode with more stricter
parameters in the MY.INI.
I don't want to use functions as that will impair the query speed.
Do you know of any way that I could use to get a numeric ZERO instead
of a null in the 2ND column of the SELECT?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL - HHHHangs when a stored procedure is called - 5.0.22/InnoDB/WinXP-SP2
Hi,
I have the following database objects in a purely InnoDB database:
--
CREATE TABLE Person (
PersonIDint not null PRIMARY KEY,
Ttl char(15),
FllNm varchar(50),
frstNm varchar(15) not null,
midNm varchar(15),
lstNm varchar(15) not null,
Gender char(1) not null DEFAULT 'M' CHECK Gender in
('M','F'),
dob dateCHECK DOB > '19000101',
nicNo varchar(13),
mrtlSttschar(1) not null DEFAULT 'M' CHECK mrtlStts in
('S','M','D','W'),
cellNo varchar(15),
website varchar(80),
Sttsint not null DEFAULT 1, /* 1=Active,
2=Inactive */
index PersonDob_ndx (dob),
index PersonNIC_ndx (nicno),
index PersonCell_ndx (cellNo),
index PersonFllNm_ndx (FllNm),
index PersonNm_ndx (frstNm, midNm, lstNm)
) ENGINE=InnoDB;
CREATE FUNCTION CharValIsNumeric (v VARCHAR(15))
RETURNS boolean
BEGIN
declare i, l int(2);
set l=char_length(v);
set i=1;
while (i <= l) and (substring(v,i,1) in
('1','2','3','4','5','6','7','8','9','0')) do
set i=i+1;
end while;
IF i > l THEN
return 1;
else
return 0;
end if;
END;
create procedure PersonAdd (Ttl_char(15),
frstNm_ varchar(15),
midNm_ varchar(15),
lstNm_ varchar(15),
Gender_ char(1),
dob_char(8),/* mmdd */
nicNo_ varchar(13),
mrtlStts_ char(1),
cellNo_ varchar(15),
website_varchar(80),
machine_no_ int)
begin
insert into Person (PersonID, Ttl, frstNm, midNm, lstNm, Gender, dob,
nicNo, mrtlStts,
cellNo, website)
select (machine_no_ * 100) + (CASE WHEN ((max(PersonID)) -
((max(PersonID) div 100) * 100)) is null Then
0
ELSE
((max(PersonID)) - ((max(PersonID) div 100) *
100))
END) + 1 as PersonID, Ttl_, frstNm_, midNm_, lstNm_, Gender_,
dob_, nicNo_,
mrtlStts_, cellNo_, website_
from Person;
end;
create trigger Person_Check_bi
before insert on Person
for each row
begin
if new.gender <> 'M' and new.gender <> 'F' then
set @errmsg = 'Gender value not equal to either M or F';
insert into tmp1 (checkCol) values (1);
end if;
if new.dob < '19000101' then
set @errmsg = 'Date of birth set far back in time! Less than
1900';
insert into tmp1 (checkCol) values (1);
end if;
if new.mrtlStts <> 'S' and
new.mrtlStts <> 'M' and
new.mrtlStts <> 'D' and
new.mrtlStts <> 'W' then
set @errmsg = 'Marital Status not equal to one of S,M,D,W -
Single,
Married, Divorced and Widowed';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.nicno)))=0 then
set @errmsg = 'NIC No contains non-numeric characters';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.cellno)))=0 then
set @errmsg = 'Cell/Mobile No contains non-numeric characters';
insert into tmp1 (checkCol) values (1);
end if;
end;
create trigger Person_Check_bu
before update on Person
for each row
begin
if new.gender <> 'M' and new.gender <> 'F' then
set @errmsg = 'Gender value not equal to either M or F';
insert into tmp1 (checkCol) values (1);
end if;
if new.dob < '19000101' then
set @errmsg = 'Date of birth set far back in time! Less than
1900';
insert into tmp1 (checkCol) values (1);
end if;
if new.mrtlStts <> 'S' and
new.mrtlStts <> 'M' and
new.mrtlStts <> 'D' and
new.mrtlStts <> 'W' then
set @errmsg = 'Marital Status not equal to one of S,M,D,W -
Re: MySQL - HHHHangs when a stored procedure is called - 5.0.22/InnoDB/WinXP-SP2
Hi,
I am replying to my own post - only to add that in response to the
MySQL misbehaving, I just kill it and restart it. More than once, I
also restarted the service, drop the database and re-created the
database and all the tables, procedures, triggers, grants, etc. But
still when I call the stored procedure, MySQL hangs.
Any help?
--
Thanks in advance,
Asif
On 8/16/06, Asif Lodhi <[EMAIL PROTECTED]> wrote:
Hi,
I have the following database objects in a purely InnoDB database:
--
CREATE TABLE Person (
PersonIDint not null PRIMARY KEY,
Ttl char(15),
FllNm varchar(50),
frstNm varchar(15) not null,
midNm varchar(15),
lstNm varchar(15) not null,
Gender char(1) not null DEFAULT 'M' CHECK Gender in
('M','F'),
dob dateCHECK DOB > '19000101',
nicNo varchar(13),
mrtlSttschar(1) not null DEFAULT 'M' CHECK mrtlStts in
('S','M','D','W'),
cellNo varchar(15),
website varchar(80),
Sttsint not null DEFAULT 1, /* 1=Active,
2=Inactive */
index PersonDob_ndx (dob),
index PersonNIC_ndx (nicno),
index PersonCell_ndx (cellNo),
index PersonFllNm_ndx (FllNm),
index PersonNm_ndx (frstNm, midNm, lstNm)
) ENGINE=InnoDB;
CREATE FUNCTION CharValIsNumeric (v VARCHAR(15))
RETURNS boolean
BEGIN
declare i, l int(2);
set l=char_length(v);
set i=1;
while (i <= l) and (substring(v,i,1) in
('1','2','3','4','5','6','7','8','9','0')) do
set i=i+1;
end while;
IF i > l THEN
return 1;
else
return 0;
end if;
END;
create procedure PersonAdd (Ttl_char(15),
frstNm_ varchar(15),
midNm_ varchar(15),
lstNm_ varchar(15),
Gender_ char(1),
dob_char(8),/* mmdd */
nicNo_ varchar(13),
mrtlStts_ char(1),
cellNo_ varchar(15),
website_varchar(80),
machine_no_ int)
begin
insert into Person (PersonID, Ttl, frstNm, midNm, lstNm, Gender, dob,
nicNo, mrtlStts,
cellNo, website)
select (machine_no_ * 100) + (CASE WHEN ((max(PersonID)) -
((max(PersonID) div 100) * 100)) is null Then
0
ELSE
((max(PersonID)) - ((max(PersonID) div 100) *
100))
END) + 1 as PersonID, Ttl_, frstNm_, midNm_, lstNm_, Gender_,
dob_,
nicNo_,
mrtlStts_, cellNo_, website_
from Person;
end;
create trigger Person_Check_bi
before insert on Person
for each row
begin
if new.gender <> 'M' and new.gender <> 'F' then
set @errmsg = 'Gender value not equal to either M or F';
insert into tmp1 (checkCol) values (1);
end if;
if new.dob < '19000101' then
set @errmsg = 'Date of birth set far back in time! Less than
1900';
insert into tmp1 (checkCol) values (1);
end if;
if new.mrtlStts <> 'S' and
new.mrtlStts <> 'M' and
new.mrtlStts <> 'D' and
new.mrtlStts <> 'W' then
set @errmsg = 'Marital Status not equal to one of S,M,D,W -
Single,
Married, Divorced and Widowed';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.nicno)))=0 then
set @errmsg = 'NIC No contains non-numeric characters';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.cellno)))=0 then
set @errmsg = 'Cell/Mobile No contains non-numeric characters';
insert into tmp1 (chec
ddmmyyyy-format date hangs MySQL - 5.0.22/InnoDB/WinXP-SP2
Hi,
In my earlier post, I was making a mistake (though I didn't do so in
the posted text!) - I was passing the dob (the date field) in the
ddmm format. When I passed the date field in mmdd format, the
stored procedure ran fine and the record got inserted. The problem is
MySQL hangs/goes in limbo quitely instead of throwing back an error.
Certainly smells like a bug. ???
I have psted the text of my original post at the end of this message.
--
Asif
I have the following database objects in a purely InnoDB database:
--
CREATE TABLE Person (
PersonIDint not null PRIMARY KEY,
Ttl char(15),
FllNm varchar(50),
frstNm varchar(15) not null,
midNm varchar(15),
lstNm varchar(15) not null,
Gender char(1) not null DEFAULT 'M' CHECK
Gender in ('M','F'),
dob dateCHECK DOB > '19000101',
nicNo varchar(13),
mrtlSttschar(1) not null DEFAULT 'M' CHECK
mrtlStts in ('S','M','D','W'),
cellNo varchar(15),
website varchar(80),
Sttsint not null DEFAULT 1, /*
1=Active, 2=Inactive */
index PersonDob_ndx (dob),
index PersonNIC_ndx (nicno),
index PersonCell_ndx (cellNo),
index PersonFllNm_ndx (FllNm),
index PersonNm_ndx (frstNm, midNm, lstNm)
) ENGINE=InnoDB;
CREATE FUNCTION CharValIsNumeric (v VARCHAR(15))
RETURNS boolean
BEGIN
declare i, l int(2);
set l=char_length(v);
set i=1;
while (i <= l) and (substring(v,i,1) in
('1','2','3','4','5','6','7','8','9','0')) do
set i=i+1;
end while;
IF i > l THEN
return 1;
else
return 0;
end if;
END;
create procedure PersonAdd (Ttl_char(15),
frstNm_ varchar(15),
midNm_ varchar(15),
lstNm_ varchar(15),
Gender_ char(1),
dob_char(8),/* mmdd */
nicNo_ varchar(13),
mrtlStts_ char(1),
cellNo_ varchar(15),
website_varchar(80),
machine_no_ int)
begin
insert into Person (PersonID, Ttl, frstNm, midNm, lstNm, Gender, dob,
nicNo, mrtlStts,
cellNo, website)
select (machine_no_ * 100) + (CASE WHEN ((max(PersonID)) -
((max(PersonID) div 100) * 100)) is null Then
0
ELSE
((max(PersonID)) - ((max(PersonID) div 100)
* 100))
END) + 1 as PersonID, Ttl_, frstNm_, midNm_, lstNm_,
Gender_, dob_, nicNo_,
mrtlStts_, cellNo_, website_
from Person;
end;
create trigger Person_Check_bi
before insert on Person
for each row
begin
if new.gender <> 'M' and new.gender <> 'F' then
set @errmsg = 'Gender value not equal to either M or F';
insert into tmp1 (checkCol) values (1);
end if;
if new.dob < '19000101' then
set @errmsg = 'Date of birth set far back in time!
Less than 1900';
insert into tmp1 (checkCol) values (1);
end if;
if new.mrtlStts <> 'S' and
new.mrtlStts <> 'M' and
new.mrtlStts <> 'D' and
new.mrtlStts <> 'W' then
set @errmsg = 'Marital Status not equal to one of
S,M,D,W - Single,
Married, Divorced and Widowed';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.nicno)))=0 then
set @errmsg = 'NIC No contains non-numeric characters';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.cellno)))=0 then
set @errmsg = 'Cell/Mobile No contains non-numeric characters';
insert into tmp1 (checkCol) values (1);
end if;
end;
create trigger Person_Check_bu
before update on Person
for each row
begin
if new.gender <> 'M' and new.gender <> 'F' then
set @errmsg = 'Gender value not equal to either M or F';
insert into tmp1 (checkCol) values (1);
end if;
if new.dob < '19000101' then
set @errmsg = 'Date of birth set far back in time!
Less than 1900';
insert into tmp1 (c
Strange MySQL behavior - 5.0.22/InnoDB Tables/WinXP-SP2
Hi, I posted the following code in one or two of my earlier posts and _then_ it WAS working! I know there is no GROUP BY clause but IT WAS WORKING somehow - the procedure ran fine and inserted quite a good few records. However, NOW, after I have dropped and re-created the database/tables/all the stuff, MySQL is complaining that I should put a GROUP BY in the following procedure: create procedure PersonAdd (Ttl_char(15), frstNm_ varchar(15), midNm_ varchar(15), lstNm_ varchar(15), Gender_ char(1), dob_char(8),/* mmdd */ nicNo_ varchar(13), mrtlStts_ char(1), cellNo_ varchar(15), website_varchar(80), machine_no_ int) begin insert into Person (PersonID, Ttl, frstNm, midNm, lstNm, Gender, dob, nicNo, mrtlStts, cellNo, website) select (machine_no_ * 100) + (CASE WHEN ((max(PersonID)) - ((max(PersonID) div 100) * 100)) is null Then 0 ELSE ((max(PersonID)) - ((max(PersonID) div 100) * 100)) END) + 1 as PersonID, Ttl_, frstNm_, midNm_, lstNm_, Gender_, dob_, nicNo_, mrtlStts_, cellNo_, website_ from Person; end; When I put all of the fields (except the first expression containing the aggregage functions) in the group by clause, MySQL complains that I didn't put PersonID in the GroupBY clause and when I do put PersonID in the GROUP BY clause MySQL still complains. I didn't change anything in the code or anything - just re-created the database, tables, etc. Any help as to what is causing this strange behavior and why it did work previously? -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange MySQL behavior - 5.0.22/InnoDB Tables/WinXP-SP2
Hi, Guys! Stange!!! I am replying to my own post just to tell you that after posting the previously message, I restarted my system and ran my VB6 program. To my surprise, MySQL__IS__ executing the same stored procedure, that it was previously complaining about, again and not complaining about the absence of the Group By clause any more. This is __very__ strange behavior. Can anyone tell what's wrong? This is driving me nuts now! -- Thanks in advance, Asif On 8/18/06, Asif Lodhi <[EMAIL PROTECTED]> wrote: I posted the following code in one or two of my earlier posts and _then_ it WAS working! I know there is no GROUP BY clause but IT WAS WORKING somehow - the procedure ran fine and inserted quite a good few records. However, NOW, after I have dropped and re-created the database/tables/all the stuff, MySQL is complaining that I should put a GROUP BY in the following procedure: create procedure PersonAdd (Ttl_char(15), frstNm_ varchar(15), midNm_ varchar(15), lstNm_ varchar(15), Gender_ char(1), dob_char(8),/* mmdd */ nicNo_ varchar(13), mrtlStts_ char(1), cellNo_ varchar(15), website_varchar(80), machine_no_ int) begin insert into Person (PersonID, Ttl, frstNm, midNm, lstNm, Gender, dob, nicNo, mrtlStts, cellNo, website) select (machine_no_ * 100) + (CASE WHEN ((max(PersonID)) - ((max(PersonID) div 100) * 100)) is null Then 0 ELSE ((max(PersonID)) - ((max(PersonID) div 100) * 100)) END) + 1 as PersonID, Ttl_, frstNm_, midNm_, lstNm_, Gender_, dob_, nicNo_, mrtlStts_, cellNo_, website_ from Person; end; When I put all of the fields (except the first expression containing the aggregage functions) in the group by clause, MySQL complains that I didn't put PersonID in the GroupBY clause and when I do put PersonID in the GROUP BY clause MySQL still complains. I didn't change anything in the code or anything - just re-created the database, tables, etc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Parameterized stored procedures via ADO Command Object
Hi, I have been trying very hard to get this working but I have NOT been able to call the stored procedures in my MySQL database using the ADO (**NOT** Ado.NET) Command object. I want to use stored procedures 'cos I want to restrict access to stored procedures and views only. However, it seems like support of ADO Command object has not been implemented - at least, that's what posters on the relevant MySQL forum say. Is there __any__ way I can do this? -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security settings won't take during installation
Hi Adrian, On 8/25/06, Adrian Greeman <[EMAIL PROTECTED]> wrote: "The security settings could not be applied to the database because the .. I am pasting here the text of one of my earlier posts to this list: I got MySQL 5.0.22 running successfully on Win-XP-SP2 as follows: 1) Download the no-install zip package of MySQL-5.0.22 from the website. 2) Unpack it in the directory of your choice (C:\mysql5 - for example) 3) Create a top-level folder - C:\mysql5Data for the data directory. 4) Cut the contents of C:\mysql5\data directory and paste the same into C:\mysql5Data. 5) Delete C:\mysql5\data directory. 6) Create a copy of an appropriate mysql-xxx.ini file and rename it to MY.INI. 7) Change the value of the data-dir variable to C:/mysql5Data - NOTE FORWARD INSTEAD OF BACK-SLASHES. 8) Create another top-level C:\InnoDBData folder. 9) Change appropriate InnoDB data-directory variables in C:\my.ini (with forward slashes!) 10) Right-click MY COMPUTER icon on the desktop and select properties from the shortcut menu. Goto the Advanced tab and select Environment [Variables]. Select PATH in the \ system variables and add C:\mysql5\bin; at the beginning of it. Apply and OK. 11) Select RUN from the start menu, type CMD and press ENTER. 12) Right click the C:\InnoDBData folder, select Properties from the shortcut menu and add "LOCAL SERVICE" user-account in the security tab and give it "Full Control" access. 13) Do the same as in 12) with the C:/mysql5Data folder. 12) type mysqld-nt --install YourServiceName --defaults-file=C:\mysql5\my.ini --local-service and press ENTER. 13) type NET START YourServiceName AND PRESS ENTER. Now you have MYSQL-5.0.22 installed on WinXP-Pro-SP2. Forget about the automatic installer. I got similar errors like yours. Additionally, I have found that the MySQL ODBC driver installs best when you do it manually - that is, hand-copy files into the Windows System32 directory. In addition, as I have found, it complains that some MSVCR7.dll is missing. When I searched for the file on Google, the search engine led me to some DllFiles.com (or some similar webpage) where I got the file, downloaded the same to my computer and everything worked like a cinch! -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Avoiding multi-col indexes & increasing speed inspite of fully-enforced constraints on a fully-normalized db
Hi, I have a multi-column index (TrnsxType, TrnsxDate, TrnsxID, DepartID). This index along with a multi-column index of some child tables results in 8-column indexes (TrnsxType, TrnsxDate, TrnsxID, DepartID, OrderType, OrderDate, OrderNo, DepartmentID), etc. I cannot eliminate Department ID because the software has to go with the manual procedures where each department has its own order/invoice/bill books - each with its own number series. In case the software goes down because of a power failure or something, things will continue on the manual system - using the manual system's document numbers, etc. When the power becomes available, transactions manually recorded will be fed into the software-based system. To cope with the very likely possibility of duplication of manual order/invoice numbers etc. with those of software generated invoice/order numbers, etc., I am storing invoice/order numbers recorded on manual invoices/orders as Negative numbers in the database - so that even if there is a duplication, the two numbers stay separate - yet to the physical paper world they stay the same - well, almost - differing only in the signs! However, even in that case, I have a problem - since there can be power/network failures, I am storing document (invoice/order, etc.) numbers with unique machine numbers embedded in them (as the left-most 3-4 digits, for example) so even if the transactions are fed into a network-disconnected computer the transaction numbers stay unique because of the left-most machine-number digits in the transaction-numbers. However, the manual system has a separate document number series for each department - so even if I store manual document numbers in -ve and use the left most 3-4 digits of the transaction-number column as the embedded machine-numbers (to make the transaction number unique, that is - in case connection to the server is dropped), I am going to have duplication errors - unless I get the department-no in the unique index (can't use a primary key as I am using InnoDB). I am storing all types of transactions in a single master/detail table combination with each transaction distinguished by its transaction type (order, invoice, purchase requisition, cash sale, etc.) However, that puts one more field into the index - in addition to increasing the data load on the tables. I decided on a single two-table design because the total number of transactions per year is not very big - last year the total number of transaction was under 100,000 - i.e. under hundred thousand. I reckon that it can go as high as 500,000 to 1000,000 but not much in the near future. If I create separate tables for each transaction type - invoice, order, cash sale, credit sale, etc. then I fear the system will be having to deal with too many tables (there are at least 10 transaction types). Since keeping different types of transactions in different tables will only decrease the index key length by 1 and there will still be three-columns in the indexs and there will be "many" tables with three-column indexes, do you guys think that splitting up the tables like this will increase performance? Lastly, my database is fully normalized and I have tried to enformce data-integrity at the database level with all constraints enforced. Since, on innoDB tables, there is a requirement of building indexes for foreign key constraints, I fear I'll have performance degradation problems on multi-column indexes. Because of that I have designed the database so that there will be very few updates or deletes - because of the stuff that I read about InnoDB issues. Does MySQL performs well with so many constraints enforced? I have STRICT SQL and all other restricted clauses enabled in my.ini file. Cutting it short: can you recommend a solution that I can use to reduce the number of columns in indexes? can you give me an advice to increase the MySQL performance in the face of fully-enforced constraints? I am posting this query on this list because I have seen some very good responses to similar problems on this list. Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
USE_INDEX
Hi, I remember USE_INDEX optimization hint in Oracle and I have seen something like this on this list as well but I can't seem to look it up in the online documentation or the mysql manual sitting on my hard disk. Can anybody give me pointers as to where I can get documentation on this function/hint/feature ? documentation URLs, etc.? Thanks in advance, -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get into mysql command line?
Hi Cornelia, On 10/16/06, Cornelia Menzel <[EMAIL PROTECTED]> wrote: Anyhow, if somebody has a glue of what was or could have been the problem, I am interested to know what it was. What really annoys me, is that I have not found what it was. Though I have used only 5.x versions but I got similar errors when I mucked with the filesystem permissions. The solution in my case was to make sure that the MySQL user account (that you use to run MySQL server with) has full permissions on MySQL and other related folders such a the data dir, innodb dir, etc. -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inline View
Hi Melissa, What's wrong with phrasing your query as : select distinct i.reportid, ivalue as IPAddress, ivalue as Computer From item where reportid=1; /* LIMIT 3 */ -- or something like this if you do want 3 rows anway. Am I putting LIMIT right? -- Asif On 10/19/06, Melissa Dougherty <[EMAIL PROTECTED]> wrote: I'm trying to take table data and display the data horizontal I need to take the column and show the results in one row. I have tried several different inline view (queries) and get multiple rows. Here is an example It brings back three rows with each column in a different row. select distinct i.reportid, (select ivalue from item where ifield like '%IP Address%' and ifield = i.ifield and ivalue = i.ivalue) AS IPAddress, (select ivalue from item where ifield = 'Computer Name' and ifield = i.ifield and ivalue = i.ivalue) AS Computer from item i where i.reportid = 1 order by 1 Any suggestions? Thanks, Melissa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deadlock
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]
Re: float numbers
Hi Ahmad, On 11/18/06, Ahmad Al-Twaijiry <[EMAIL PROTECTED]> wrote: 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 ? You'll get a slight slow-down in performance - a trade-off you will make if you choose DECIMAL instead of FLOAT. Even if you choose DOUBLE, there is a risk of rounding errors. Try converting/casting the integer '1234567890' to float/double and assign the resulting float to an integer variable - then see what difference it makes. With DECIMAL you get accurate monetary calculations but slightly slower speed. -- Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What to do if a table is supposed to get > 3GB data per day?
Hi, Would you like to express your opinion as to what design strategy to take if a table (used for read operations only) is supposed to get more than 3GB of data per day? With 1000 simultaneous users ? -- Thanks in advance, Asif
