Re: SQL To Change Last Approvers
You could use a temporary table for that: CREATE TEMPORARY TABLE tmp ( deptID INT NOT NULL, appSeq INT NOT NULL ) AS SELECT deptID, max(appSeq) AS appSeq FROM approvers GROUP BY deptID; UPDATE approvers, tmp SET approvers.appName = 'Sting Ray' WHERE approvers.appName = 'Joe Bass' AND approvers.deptID = tmp.deptID AND approvers.appSeq = tmp.appSeq ; Thanks! It's funny sometimes how these things seem so obvious --- AFTER somebody's shown the way! Randolph Randy L. Chrismon [EMAIL PROTECTED] Ph. 732-452-7610 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL To Change Last Approvers
I have some tables for placing orders. Depending on the department, an order must be approved by 1, or more, specific approvers. create table department ( deptID int not null, deptName varchar(32), ... ) create table approvers ( deptID int not null, appSeq int not null, appName varchar(32) ) Some data: Department deptID DeptName 1 Hooks 2 Lines 3 Sinkers 4 Rods 5 Reels ... ... Approver deptID appSeq appName 1 1 Joe Bass 1 2 Suzy Shad 2 1 Tom Trout 2 2 Suzy Shad 2 3 Joe Bass 3 1 Mike Mako 3 2 Don Dolphin 3 3 Tom Tuna 4 1 Suzy Shad 4 2 Joe Bass ... ... Joe Bass made the mistake of insulting the boss's dog. So, the boss decides to remove Joe's Last Approver status (the appSeq number is the order in which an order must be approved). But, since the boss doesn't like the dog, either, he didn't fire Joe and doesn't want to take away his other approver responsibilities. So, how do I go about changing the last approver -- i.e. those departments in which Joe Bass has the highest appSEQ number from Joe Bass to Sting Ray? The final result should look like Approver deptID appSeq appName 1 1 Joe Bass 1 2 Suzy Shad 2 1 Tom Trout 2 2 Suzy Shad 2 3 Sting Ray 3 1 Mike Mako 3 2 Don Dolphin 3 3 Tom Tuna 4 1 Suzy Shad 4 2 Sting Ray ... ... What's got me stumped is that different departments have different numbers of approvers and that Joe Bass isn't always the last approver. Thanks. Randolph Randy L. Chrismon [EMAIL PROTECTED]
Need an XRef Table or Something
Here are some tables: Create Table FN ( FNunid varchar(32) not null, ..., PRIMARY KEY (FNunid) ); Create Table Dependent ( DEPunid varchar(32) not null, FNunid varchar(32) not null, ..., PRIMARY KEY (DEPunid), CONSTRAINT `0_69` FOREIGN KEY (FNunid) REFERENCES FN(FNunid) ); Create Table Action( ACTunid varchar(32) not null, FNunid varchar(32) not null, ... PRIMARY KEY (ACTunid), CONSTRAINT `0_67` FOREIGN KEY (FNunid) REFERENCES FN (FNunid) ); The tables were designed to hold information being transferred from a lot of Lotus Notes databases (that's why the UNID primary keys are 32 characters). Obviously, the assumption was that Dependent and Action records are children of FN records. This worked fine for a million plus records -- and then... Some bright people in another office constructed their Lotus Notes databases so that some Dependent records are children of other Dependent records, which, through a chain, ultimately connect to an FN record. Likewise Action records can be children of Dependent or Dependent-to-a-Dependent records. In these cases, the FNunid field actually contains the DEPunid of the next higher Dependent record until you finally get to a record with FNunid = to an FN.FNunid. Do I need to say kaboom? I think the way out of this is to create some sort of lookup table that relates the various levels of dependent records to the root FN record. I can't seem to get my head around the design of such a table, however. By definition (and the structure of Lotus Notes databases), every dependent and action record is, in fact, a child to another dependent or root FN record. Not every FN record, however, has children of either Dependent or Action type. In addition, no FN record is a child of any other record. I thought of something like: Create Table XRef( ThisRecordID varchar(32) not null, ParentRecordID varchar(32) default null ) But I can't figure out how to write a SQL query that will trace an Action or Dependent record, at an unknown level in the hierarchy, back to the root FN. Is there a better way to do this or can you help me with the SQL statement? Thanks Randy [EMAIL PROTECTED] Confidentiality Note: This message and any accompanying attachments contain information from the law firm Fragomen, Del Rey, Bernsen Loewy, P.C. which is confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this e-mail in error, please notify our offices immediately, by telephone at 212-688-8555, or by e-mail at [EMAIL PROTECTED]
Table Size in Bytes
How do I figure out the physical disk storage size of a table and of the containing database? I'm using InnoDB. When I look at the directory through the OS, I see the Ibdata1 file is about 1GB and a MyServer-bin.06 file that is about 900MB. There are also other -bin and various log files that are much smaller. However, I have two databases on this server and I'd like to know the physical size of each of them separately. I'd also like to know the physical size of the individual tables. Thanks. Randolph Randy L. Chrismon [EMAIL PROTECTED] Ph. 732-452-7610 Confidentiality Note: This message and any accompanying attachments contain information from the law firm Fragomen, Del Rey, Bernsen Loewy, P.C. which is confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this e-mail in error, please notify our offices immediately, by telephone at 212-688-8555, or by e-mail at [EMAIL PROTECTED]
Re: 4.1.1 --old-paswords 4.0.X clients
It would be nice to dump the confidentiality statement. Unfortunately, our mail server adds it to the message as it's sent to the internet -- I have no control over it. It's alright though since my message is intended to be for the use of the entity named above. In this case, the MySQL list community! As for the --old-password, it's looking to me like it's not that easy. I think but don't know yet, that any new password created on the 4.1.1 server -- even with --old-passwords set -- uses the new algorithm. My reading of the documentation, until somebody tells me, or I can prove otherwise, is that --old-passwords only allows previously existing passwords to be used -- i.e. only when an existing 4.0 server is upgraded to 4.1. Any new passwords created on the new server must use the new algorithm -- apparently even if you are using a 4.0 client log in as root to create the password. At least, that's what is happening to me. Thanks. Randolph Randy L. Chrismon [EMAIL PROTECTED] Ph. 732-452-7610 Victor Medina [EMAIL PROTECTED] 01/08/2004 15:14 To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: 4.1.1 --old-paswords 4.0.X clients It did change, but you can still use the old protocol by adding --old-passwords option to the mysqld or adding old-passwords option to the [mysqld] group in my.cnf, using this, old clients will be able to connect to 4.1 series servers without probs :) By the way, i am not quite sure, but you should remove the down legal note, i think the mysql list is public and free to every one to use and quote, so while posting here it is better to remove the note, maybe someone at mysql.com could confirm this? Best Regards On Thu, 2004-01-08 at 15:45, [EMAIL PROTECTED] wrote: I have a workstation that I set up as a 4.0.16 server to prove to myself that I could use MySQL for an application we are developing. Now, for beta testing, I set up a new 4.1.1 server on a real server box. I did not upgrade the workstation with the 4.0.16 server. In setting up the new server, I ran the mysql_fix_privilege_tables script and restarted the server with the --old-passwords option. I then logged in locally as root and created a new user account for me from my OTHER workstation with the 4.0 client. Unfortunately, as long as my account on the new server has a password, I can't log in. I get the old standby, Client does not support authentication protocol requested by server; consider upgrading MySQL client. I went back to the documentation and it slowly dawned on me that it was speaking strictly of an UPGRADE to an existing installation when it talked about old passwords and the like. Consequently, I am beginning to suspect that I cannot install a new 4.1.1 server and still use 4.0 clients. Is this correct or am I missing something (wish I could say that was unlikely ;-)? Thanks Randolph Randy L. Chrismon [EMAIL PROTECTED] Confidentiality Note: This message and any accompanying attachments contain information from the law firm Fragomen, Del Rey, Bernsen Loewy, P.C. which is confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this e-mail in error, please notify our offices immediately, by telephone at 212-688-8555, or by e-mail at [EMAIL PROTECTED] -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 | ||geek by nature - linux by choice | |...|
Re: 4.1.1 --old-paswords 4.0.X clients
Is that equivalent to NOT running mysql_fix_privilege_tables? Or, do I need to run the script -- or do I even need to run the script on a new 4.1 install; just go back and alter the table to change the size? Didn't think of Password hashing as a search term. Thanks for the tip. Randy Randolph Randy L. Chrismon [EMAIL PROTECTED] Ph. 732-452-7610 jamie murray [EMAIL PROTECTED] 01/08/2004 15:44 To: [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject:Re: 4.1.1 --old-paswords 4.0.X clients If you resize the password column to what it was pre 4.1 it will store a hash the same length as the old algo used to produce and users will be able to log in just fine. Michael Stassen pointed me to this link http://www.mysql.com/doc/en/Password_hashing.html. and it has a number of work arounds.
