Re: DBD::MySQL
From: Nathan Mealey [EMAIL PROTECTED] Has anyone out there had any trouble installing DBD::MySQL for MySQL 4.1.x on Mac OS X 10.3? I have tried it on several machines in my company's office, but it fails on all that are running 4.1.x, and installs fine on those running 4.0.x Thoughts? Try it at the Perl/MySQL list at http://lists.mysql.com/perl . I think you'll find more specialized knowledge on that list... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get lines matching a select / group by query
- Original Message - From: Wesley Furgiuele [EMAIL PROTECTED] To: Alexander Newald [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, September 21, 2004 1:41 AM Subject: Re: Get lines matching a select / group by query Use COUNT(): SELECT COUNT( id ) FROM test WHERE LEFT( id, 5 ) = '12345' GROUP BY value; Wes Hello, unfortunately that is what I tried (I didn't posted my query correctly as I didn't use cut and past - sorry) It will return the count of lines for each pairs of group by Alexander Newald On Tue, 21 Sep 2004 00:24:33 +0200, Alexander Newald [EMAIL PROTECTED] wrote: Hello, I like to get the number of lines returned by a select ... group by query: Example: SELECT id FROM test WHERE LEFT(id,5) = 12345 GROUP BY value; Perhaps I will get +---+ | id| +---+ | 2 | | 1 | | 5 | +---+ 3 rows in set (0.02 sec) But I like to simply get 3 As I use mySQL 3.x and can't change it for now I'm not able to use FOUND_ROWS() Thanks, Alexander Newald -- 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]
Select with normalized table
Hi, Here are the tables : CREATE TABLE markets ( Id integer NOT NULL auto_increment, ContractCode varchar(20), PRIMARY KEY (Id), ) TYPE=InnoDB; CREATE TABLE strategies ( Id integer NOT NULL auto_increment, StrategyCode char(15) NOT NULL DEFAULT '', PRIMARY KEY(Id) ) TYPE=InnoDB; CREATE TABLE strategies_markets ( StrategyId integer NOT NULL DEFAULT '0', MarketId integer NOT NULL DEFAULT '0', Ratio integer NOT NULL DEFAULT '1', PRIMARY KEY(StrategyId, MarketId), FOREIGN KEY (StrategyId) REFERENCES strategies(Id), FOREIGN KEY (MarketId) REFERENCES markets(Id) ) Type=InnoDB; A particular stategy gather at least one market, and each market can be in several strategies (or none). So strategies_markets represent this 1 to N relationship. Here is my question: I have an incoming array of markets and I need to find if there is a strategy that correspond to that particular set of markets. Is there any particular way of doing that ? I would think of the following for a strategy with 3 legs (strategies can involve up to 8 legs in my case) : SELECT sm1.StrategyId FROM strategies_markets AS sm1, strategies_markets AS sm2, strategies_markets AS sm3 WHERE sm1.MarketId ='the incoming market Id1' AND sm2.MarketId ='the incoming market Id2' AND sm3.MarketId ='the incoming market Id3' AND sm1.StrategyId = sm2.StrategyId AND sm2.StrategyId = sm3.StrategyId Can anyone think of a simpler solution involving maybe less join, a IN(), or anything else ? Thanks. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[ANN]: DBACentral for MySQL v.1.0.5 has been released
Hello All, The new version of DBACentral for MySQL has been released. Since now DBACentral for MySQL works with all MySQL server versions, currently released. Also a lot of new options are added in the new version, and a bunch of bugs are fixed. Download page: http://microolap.com/dba/mysql/dbacentral/download.htm Full list of current changes: [+] Now DBACentral works with all MySQL versions, including 4.1.4-alpha and 5.0.1-alpha. [+] Added a lot of new options for customizing program behavior and appearance, including: application and editor fonts, data grid style sheets, and a number of various editor preferences. [+] The 'Copy Command' function is added to the SQL Log. [+] Now you can execute statements like BEGIN:END through SQL Executor and Query Designer. [-] Several bugs with window scrolling are fixed and the model drawing is optimized in the relationship manager. [-] Fixed Data Grid bug with NULL values in TEXT fields. [-] Fixed bug with creating a table from XML file. [-] Fixed several bugs with using keyboard in Table Designer. [-] Fixed several bugs with copying and pasting text in the Data Grid. [-] Fixed bug with deleting a table field, included into an index. [-] Fixed bug with loading a sample database twice during one DBACentral session. -- Best regards, Edward Smirnov microOLAP Technologies LTD mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: libmysqlclient.so.10 is needed
Tim Johnson wrote: Hello: Installing MySQL-server-4.0.21-0.i386.rpm on Red Hat 9.0 Getting the following: error: Failed dependencies: libmysqlclient.so.10 is needed by (installed) MySQL-python-0.9.1-6 libmysqlclient.so.10 is needed by (installed) perl-DBD-MySQL-2.1021-3 libmysqlclient.so.10 is needed by (installed) php-mysql-4.2.2-17.2 I presume that I need other packages. What packages would that be? thanks tim look for a libmysql10 package. For a complete installation, go for the following package : MySQL, MySQL-common, MySQL-server, MySQL-client and libmysql10 -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with normalized table
Hi Philippe, how about this: SELECT strategyid, COUNT(marketid) cnt FROMstrategies_markets WHERE marketid in(selected markets here) GROUP BY strategyid ORDER BY cnt DESC; The resultset should contain the best matches, in descending order. To find strategies that contains all markets (but that may contain others) you could use: SELECT strategyid, COUNT(marketid) cnt FROMstrategies_markets WHERE marketid in(selected markets here) GROUP BY strategyid HAVING cnt = number of selected markets If you reverse the key-order in the primary key of strategies_markets, this baby should really fly. Regards, Johan 2004-09-21 kl. 11.51 skrev Philippe Poelvoorde: Hi, Here are the tables : CREATE TABLE markets ( Id integer NOT NULL auto_increment, ContractCode varchar(20), PRIMARY KEY (Id), ) TYPE=InnoDB; CREATE TABLE strategies ( Id integer NOT NULL auto_increment, StrategyCode char(15) NOT NULL DEFAULT '', PRIMARY KEY(Id) ) TYPE=InnoDB; CREATE TABLE strategies_markets ( StrategyId integer NOT NULL DEFAULT '0', MarketId integer NOT NULL DEFAULT '0', Ratio integer NOT NULL DEFAULT '1', PRIMARY KEY(StrategyId, MarketId), FOREIGN KEY (StrategyId) REFERENCES strategies(Id), FOREIGN KEY (MarketId) REFERENCES markets(Id) ) Type=InnoDB; A particular stategy gather at least one market, and each market can be in several strategies (or none). So strategies_markets represent this 1 to N relationship. Here is my question: I have an incoming array of markets and I need to find if there is a strategy that correspond to that particular set of markets. Is there any particular way of doing that ? I would think of the following for a strategy with 3 legs (strategies can involve up to 8 legs in my case) : SELECT sm1.StrategyId FROM strategies_markets AS sm1, strategies_markets AS sm2, strategies_markets AS sm3 WHERE sm1.MarketId ='the incoming market Id1' AND sm2.MarketId ='the incoming market Id2' AND sm3.MarketId ='the incoming market Id3' AND sm1.StrategyId = sm2.StrategyId AND sm2.StrategyId = sm3.StrategyId Can anyone think of a simpler solution involving maybe less join, a IN(), or anything else ? Thanks. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Johan Pettersson OnGame e-solutions AB Centralplan 19 SE-111 20 Stockholm Sweden Mobile: +46 (0)704 335 345 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help regarding securing data files
Hello All, First of all I would like to mention that I am newbie using mysql. So I am sorry if I posted it wrong but is there any way by which I can secure my data files such that even if the data files are copied to other mysql server it cannot be accessed. Some form of password protection or similar ? Regards, VijayKumar Dogra
Batch Querying
I'm writing my first MySQL app in VB.net using myODBC. However I think this question applies to all languages using MySQL. From what I understand, I am unable to issue a batch statement of commands separated by commas to mySQL. I receive an error whenever I try to do so from my app. For this reason, I am unable to make use of SQL variables and temporary tables. I must instead use persistant tables. Can anyone tell me if this is in fact the case and if so, any suggestions on how to get over this hurdle? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Batch Querying
What version of MySQL are you using? I believe multiple delimited statements are available in 4.1.x and later. Jeff Burgoon wrote: I'm writing my first MySQL app in VB.net using myODBC. However I think this question applies to all languages using MySQL. From what I understand, I am unable to issue a batch statement of commands separated by commas to mySQL. I receive an error whenever I try to do so from my app. For this reason, I am unable to make use of SQL variables and temporary tables. I must instead use persistant tables. Can anyone tell me if this is in fact the case and if so, any suggestions on how to get over this hurdle? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OUTER JOIN bug in 5.0.1?
Josh Trutwin [EMAIL PROTECTED] wrote on 09/20/2004 10:41:46 PM: On Mon, 20 Sep 2004 10:25:16 -0400 [EMAIL PROTECTED] wrote: I think you missed my point. I think the 5.0.1 behavior was correct and the others are wrong. There is a known bug (or two) about mixing outer joins and inner joins and it looks like it may be fixed. IF you want to see all of the students THAT TABLE (students) needs to be on the LEFT side of a LEFT JOIN or the RIGHT side of a RIGHT JOIN. That's what the directions mean Interesting - do you have a link to more information on this bug? http://bugs.mysql.com/1677 and http://bugs.mysql.com/1591 and http://bugs.mysql.com/3765 SELECT s.tech_id, s.full_name, sa.points_awarded, sa.date_submitted FROM student s INNER JOIN enrollment e ON e.tech_id = s.tech_id AND e.term_id = '3' LEFT JOIN submitted_assignment sa ON sa.tech_id = s.tech_id LEFT JOIN assignment a ON a.a_id = sa.a_id AND a.a_id = '100' ORDER BY s.full_name; I also moved the clause AND a.a_id = '100' into the ON portion of the LEFT JOIN. That way you can see who did and didn't get that assignment. If you describe what you WANT to see, I can help you write the query to get it. What I think I wrote for you will be all students where term_ID=3 and what grades they got on assignment 100. But i think you may get some duplicate rows of blank scores. Does assignment relate to student, perhaps with a tech_id or student_id field? That fixes one dilemma by setting up the following query SELECT s.tech_id, s.full_name, if(isnull(a.a_ID),'no','yes') as assigned_100, sa.points_awarded, sa.date_submitted FROM student s INNER JOIN enrollment e ON e.tech_id = s.tech_id AND e.term_id = '3' LEFT JOIN assignment a ON a.student_ID = s.student_ID AND a.a_id = '100' LEFT JOIN submitted_assignment sa ON sa.tech_id = s.tech_id AND a.a_id = sa.a_id ORDER BY s.full_name; This is what I eventually used: SELECT s.tech_id, s.full_name, sa.points_awarded, sa.date_submitted FROM student s INNER JOIN enrollment e ON e.tech_id = s.tech_id LEFT JOIN submitted_assignment sa ON sa.tech_id = s.tech_id AND sa.a_id = '$a_id' LEFT JOIN assignment a ON a.a_id = sa.a_id WHERE e.term_id = '3' ORDER BY s.full_name; It didn't seem to work with the AND sa.a_id = '$a_id' in the assignment join condition - but this works. I don't understand why that doesn't filter the right rows if it's in the WHERE clause, I usually define my JOIN condition solely on the column(s) being joined together and any additional filtering gets done in the WHERE clause. There are up to three layers of record filtering that happen during any query. First is the JOIN filtering. That is where the ON conditions are used with the table declarations to build a virtual table that consists of all columns from each of the participating tables and each combination of rows that meets the ON conditions. If table A has 5 rows and table B has 50 rows and the ON conditions force a match of at most 2 records from table B to each record in table A, the virtual table will have at most 10 rows (not the 250 that would be generated without the ON conditions). Second to be applied is the rest of the WHERE clause that could not be applied during the ON determinations. This is especially true with queries that contain OUTER JOINS. If it didn't happen in this order, we couldn't do an outer join of two tables and look for a null result in the outer table to determine non-matching rows. The third set of filters to be applied comes from the HAVING clause. HAVING conditions are applied after every other portion of the query has been analyzed except for the LIMIT clause. That is why HAVING works on the results of GROUP BY aggregate functions and can use column aliases declared in the SELECT clause. When you put a condition into the ON clause of a JOIN, it is going to be applied to the formation of the virtual table which gets computed _before_ the entire WHERE clause is applied. Under many conditions, some WHERE conditions can also be applied to table JOINs along with the ON restrictions. Luckily, the query optimizer handles that for us. http://dev.mysql.com/doc/mysql/en/LEFT_JOIN_optimization.html http://dev.mysql.com/doc/mysql/en/Where_optimizations.html I am glad you have it working. Come back if you run into any more trouble. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Man, I thought I had a good handle on OUTER JOINs. Erg. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Batch Querying
4.0.20a-max, just because I thought this was the most stable build. Victor Pendleton [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] What version of MySQL are you using? I believe multiple delimited statements are available in 4.1.x and later. Jeff Burgoon wrote: I'm writing my first MySQL app in VB.net using myODBC. However I think this question applies to all languages using MySQL. From what I understand, I am unable to issue a batch statement of commands separated by commas to mySQL. I receive an error whenever I try to do so from my app. For this reason, I am unable to make use of SQL variables and temporary tables. I must instead use persistant tables. Can anyone tell me if this is in fact the case and if so, any suggestions on how to get over this hurdle? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: libmysqlclient.so.10 is needed
or... for me this rpm was the solution: MySQL-shared-compat-4.0.21-0.i386.rpm from: ftp://gd.tuwien.ac.at/db/mysql/Downloads/MySQL-4.0/MySQL-shared-compat-4.0.21-0.i386.rpm Philippe Poelvoorde wrote: Tim Johnson wrote: Hello: Installing MySQL-server-4.0.21-0.i386.rpm on Red Hat 9.0 Getting the following: error: Failed dependencies: libmysqlclient.so.10 is needed by (installed) MySQL-python-0.9.1-6 libmysqlclient.so.10 is needed by (installed) perl-DBD-MySQL-2.1021-3 libmysqlclient.so.10 is needed by (installed) php-mysql-4.2.2-17.2 I presume that I need other packages. What packages would that be? thanks tim look for a libmysql10 package. For a complete installation, go for the following package : MySQL, MySQL-common, MySQL-server, MySQL-client and libmysql10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Batch Querying
Victor, I think I found what you're referring to: http://dev.mysql.com/doc/mysql/en/C_API_multiple_queries.html ... From version 4.1, MySQL supports the execution of multiple statements specified in a single query string. To use this capability with a given connection, you must specify the CLIENT_MULTI_STATEMENTS option in the flags parameter of mysql_real_connect() when opening the connection. You can also set this for an existing connection by calling mysql_set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_ON) Can anyone tell me if this functionality is implemented in the MyODBC? If so, can you tell me how you turn it on/off? Thanks again! Victor Pendleton [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] What version of MySQL are you using? I believe multiple delimited statements are available in 4.1.x and later. Jeff Burgoon wrote: I'm writing my first MySQL app in VB.net using myODBC. However I think this question applies to all languages using MySQL. From what I understand, I am unable to issue a batch statement of commands separated by commas to mySQL. I receive an error whenever I try to do so from my app. For this reason, I am unable to make use of SQL variables and temporary tables. I must instead use persistant tables. Can anyone tell me if this is in fact the case and if so, any suggestions on how to get over this hurdle? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Batch Querying
For multiple statements in one submission, have you tried using a semicolon ; not a comma , ? (http://dev.mysql.com/doc/mysql/en/Entering_queries.html) Each command may return a recordset of its own. Be prepared to either cycle through the returned recordsets or ignore them as they arrive. When you create a connection with a MySQL database, you establish an environment where variables and temporary tables can exist that is specifically yours. Just issue your commands in multiple statements WITHOUT CLOSING YOUR CONNECTION and you will be able to use those items. Here is some VB/ADO-like pseudocode to illustrate: set oConn = new Connection set oRS = new Recordset set oRSTimer = new Recordset oConn.open connection string, sUser, sPassword oConn.execute SET @Start_time = NOW() oConn.execute CREATE TEMPORARY TABLE tmpResults SELECT oRS.Open SELECT * FROM tmpResults where Col2='bluegills'; SET @End_Time=Now(), oConn, 0,1,1 oRStimer.Open [EMAIL PROTECTED], @end_time, oConn, 0,1,1 oConn.Execute DROP TABLE tmpResults oConn.Close What I did was open a connection, set a variable, create a temp table by populating it with the results of a SELECT query, get some records from that temp table AND set another variable, then finally get another record that contained the values of both temporary variables. After all that, I dropped my temporary table and closed the connection. I am nearly 100% certain that the combined statements in the oRS.Open... line will work for you. Let us know if it does or doesn't, OK?. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Burgoon [EMAIL PROTECTED] wrote on 09/21/2004 08:54:27 AM: I'm writing my first MySQL app in VB.net using myODBC. However I think this question applies to all languages using MySQL. From what I understand, I am unable to issue a batch statement of commands separated by commas to mySQL. I receive an error whenever I try to do so from my app. For this reason, I am unable to make use of SQL variables and temporary tables. I must instead use persistant tables. Can anyone tell me if this is in fact the case and if so, any suggestions on how to get over this hurdle? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OUTER JOIN bug in 5.0.1?
On Tue, 21 Sep 2004 08:57:21 -0400 [EMAIL PROTECTED] wrote: snip There are up to three layers of record filtering that happen during any query. First is the JOIN filtering. That is where the ON conditions are used with the table declarations to build a virtual table that consists of all columns from each of the participating tables and each combination of rows that meets the ON conditions. If table A has 5 rows and table B has 50 rows and the ON conditions force a match of at most 2 records from table B to each record in table A, the virtual table will have at most 10 rows (not the 250 that would be generated without the ON conditions). Second to be applied is the rest of the WHERE clause that could not be applied during the ON determinations. This is especially true with queries that contain OUTER JOINS. If it didn't happen in this order, we couldn't do an outer join of two tables and look for a null result in the outer table to determine non-matching rows. The third set of filters to be applied comes from the HAVING clause. HAVING conditions are applied after every other portion of the query has been analyzed except for the LIMIT clause. That is why HAVING works on the results of GROUP BY aggregate functions and can use column aliases declared in the SELECT clause. Yes, this much I grasp as far as the ordering of filtering. Cartesian Product, then JOIN, Then WHERE. When you put a condition into the ON clause of a JOIN, it is going to be applied to the formation of the virtual table which gets computed _before_ the entire WHERE clause is applied. Under many conditions, some WHERE conditions can also be applied to table JOINs along with the ON restrictions. Luckily, the query optimizer handles that for us. Perhaps another example would help. I've been trying to re-write another join query that's designed to produce an attendance record for each student detailing which classes they've attended and which they've missed. Each occurance of a student (PK tech_id) attending a class (PK c_id) is recorded in an intersection table class_attneded (tech_id, c_id PK). The absense of a record in this table indicates the student missed the class. So if tech_id 123123 was at classes 1 and 3, there would be records in the class_attended table: tech_id - class_id 1231231 1231233 The query that worked pre 5.0.1 is: SELECT * FROM student s INNER JOIN enrollment e ON e.tech_id = s.tech_id INNER JOIN class_attended ca ON ca.tech_id = s.tech_id RIGHT JOIN class c ON c.c_id = ca.c_id WHERE s.tech_id = '253542' AND c.term_id = '4' AND c.class_date NOW() ORDER BY week_no; In my mind I see this as get all the class records (the table on the right side of the RIGHT JOIN) and if you can match up the class_attended, enrollment and student information do so, otherwise set those values to NULL. In the example above this yeilds 3 rows - the for for class id 2 has NULLs in the other table data. I cannot seem to figure out the 5.0.1 equivalent because I seem to have learned this the wrong way. I tried this, starting with the class table since those are the rows that I want to have displayed regardless of matches. SELECT * FROM class c LEFT JOIN class_attended ca ON c.c_id = ca.c_id INNER JOIN student s ON ca.tech_id = s.tech_id AND ca.tech_id = '253542' INNER JOIN enrollment e ON e.tech_id = s.tech_id WHERE c.term_id = '4' AND c.class_date NOW() ORDER BY week_no; This acts like an INNER JOIN though, I can only get two rows. I've tried mucking around with it, but I just cannot get the outer row for class id = 2 to show. This was so much easier with the bug! :) Thanks for your help, Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help regarding securing data files
On Tue, 21 Sep 2004, VijayKumar Dogra wrote: is there any way by which I can secure my data files such that even if the data files are copied to other mysql server it cannot be accessed. Some form of password protection or similar ? The short answer is: 'no'. You have to protect your server. Regards, Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Batch Querying
Hey. Thanks. First off, I meant to say semicolon (;) in my first post but I mistyped and said comma by accident. I am trying to use semicolon as my delimiter. This method works if I issue statements from PHPMyAdmin, but not through my ODBC app. However, your pseudocode made me realize something I didn't know was true. I had just assumed a temporary table would not persist between issued statements but they do if you have not closed the connection, as you stated. I don't know why I never tried this, but it works for me both with temporary tables and variables which is great. However, the syntax of your open statement still does not work for me. I get a SQL syntax error any time I try combining statements with Semicolons. Since I've been programming using SQL Server for a while now I feel like I SHOULD be able to issue commands in this way but somewhere I heard that mySQL prevents batch queries on purpose. [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] For multiple statements in one submission, have you tried using a semicolon ; not a comma , ? (http://dev.mysql.com/doc/mysql/en/Entering_queries.html) Each command may return a recordset of its own. Be prepared to either cycle through the returned recordsets or ignore them as they arrive. When you create a connection with a MySQL database, you establish an environment where variables and temporary tables can exist that is specifically yours. Just issue your commands in multiple statements WITHOUT CLOSING YOUR CONNECTION and you will be able to use those items. Here is some VB/ADO-like pseudocode to illustrate: set oConn = new Connection set oRS = new Recordset set oRSTimer = new Recordset oConn.open connection string, sUser, sPassword oConn.execute SET @Start_time = NOW() oConn.execute CREATE TEMPORARY TABLE tmpResults SELECT oRS.Open SELECT * FROM tmpResults where Col2='bluegills'; SET @End_Time=Now(), oConn, 0,1,1 oRStimer.Open [EMAIL PROTECTED], @end_time, oConn, 0,1,1 oConn.Execute DROP TABLE tmpResults oConn.Close What I did was open a connection, set a variable, create a temp table by populating it with the results of a SELECT query, get some records from that temp table AND set another variable, then finally get another record that contained the values of both temporary variables. After all that, I dropped my temporary table and closed the connection. I am nearly 100% certain that the combined statements in the oRS.Open... line will work for you. Let us know if it does or doesn't, OK?. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Burgoon [EMAIL PROTECTED] wrote on 09/21/2004 08:54:27 AM: I'm writing my first MySQL app in VB.net using myODBC. However I think this question applies to all languages using MySQL. From what I understand, I am unable to issue a batch statement of commands separated by commas to mySQL. I receive an error whenever I try to do so from my app. For this reason, I am unable to make use of SQL variables and temporary tables. I must instead use persistant tables. Can anyone tell me if this is in fact the case and if so, any suggestions on how to get over this hurdle? Thanks, Jeff -- 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]
Duplicate Rows
MySQL 4.0.14 In a scenario: Ref EmailAddr 1[EMAIL PROTECTED] 2[EMAIL PROTECTED] 3[EMAIL PROTECTED] 4[EMAIL PROTECTED] how can I delete duplicate email entries (records 1, 2) leaving 4. regards ___ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Secure logon from VB.net
Hello all,
Ok i know this may be a simple question but i need a little help.
I am writing a program in VB.net that uses MySQL as a backend. My net admin
wants the log on to be encrypted??
This is currently how i am connecting:
Public LocalSYS As New OdbcConnection(DRIVER={MySQL ODBC 3.51
Driver}; _
SERVER=192.168.0.1; _
DATABASE=DB; _
UID=User; _
PASSWORD=Password; _
OPTION=3;)
As you can see its a public variable that i have declared so that i don't
have to continuously add it for every form in the program.. So the question
i have is how do i change this so that it is encrypted??? Any ideas and
suggestions would be greatly appreciated..
Many thanks,
Tom T
Re: Duplicate Rows
- Original Message - From: A Z [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 21, 2004 10:02 AM Subject: Duplicate Rows MySQL 4.0.14 In a scenario: Ref EmailAddr 1[EMAIL PROTECTED] 2[EMAIL PROTECTED] 3[EMAIL PROTECTED] 4[EMAIL PROTECTED] how can I delete duplicate email entries (records 1, 2) leaving 4. The normal design for *any* table in a relational database is to have a primary key on the table. That ensures that every row in the table can be uniquely identified and accessed, independently of all the others. The primary key may consist of one column or several. In the case of a single column primary key, the value of that column must be unique in every row of the table and the database will ensure that no duplicates are ever stored. If the primary key has several columns, the *combination* of values in the primary key must always be unique on every row in the table and the database will not let you add a row that duplicates the primary key of an existing row. It isn't clear from your question if you established a primary key since you don't provide a definition of the table. If you *did* create a primary key, I'm assuming that the Ref column is it. In this case, the following will do the job: delete from your_table where ref in (1,2) If some other column that you aren't showing was the primary key, use it in the Where clause instead and specify the key values of the rows you want to delete. If you didn't specify a primary key, which I suspect is the case, and the 'Ref' column is just something you added so we'd know which rows you want to delete, there is no simple way to do the delete. You *could* write an application that reads each row in a loop, displays it to you, and then asks if that row should be deleted, then only delete it if the user answers 'yes'. Short of that, I can't think of any way to do it. In that case, you should learn from this and remember to put primary keys on every table that you create from now on - and retrofit them to all of your existing tables. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT question
Hi Roger, I took the suggestion from Brad Eacker and use BETWEEN and now works without problem. However, I decided to do a couple more tests and what I found was that the problem occurs on MySQL version 4.0.18-standard using InnoDB on Linux but does not occur on Mac OS X using the same MySQL version. Andre On 9/20/04 5:52 PM, Roger Baklund [EMAIL PROTECTED] wrote: * Andre Matos I am performing a SELECT and I am getting 0 rows when I run the SELECT direct in the MySQL database and getting 1 when I run using PHP. This is my select: SELECT * FROM scan WHERE TimePointID = 3 AND ScanQCResult = 'n' AND (ScanStatusID 90 OR ScanStatusID 98); Looks ok. I realized latter analyzing this select that I made a mistake using OR at this point: (ScanStatusID 90 OR ScanStatusID 98), it should be AND. Are you sure about that? However, in both cases, I am still getting 0 rows from the database, which is correct. My problem is using the PHP to run the SELECT, if I use OR using the PHP, I got 1 as a result, and if I use AND I got 0 as a result. This is correct, if you have one record with ScanStatusID in the range 90-98. Is anyone can tell me what is going on? You seem to be misinterpreting how logical expressions work. A SQL select statement is a description of the (sub-)set of data you wish to retrieve from the database. This description often includes a WHERE clause, describing wanted records, which again often includes a logical expression. The expression is built up by operands and operators. The logical operators relevant in SQL is AND, OR and NOT. NOT is a negation, this operator takes one operand, the the result is the opposite of the operand. NOT true is false, and NOT false is true. The other two operators, AND and OR, need two operands, one on each side. For the AND operator, BOTH sides of the operator must be true for this part of the expression to be true. For the OR operator, ANY of the sides of the operator must be true for that part of the expression to be true. So, for your expression above, you can not say ...ScanStatusID 90 AND ScanStatusID 98..., because ScanStatusID can not be below 90 AND above 98. ScanStatusID is a single number, it can be below 90 OR above 98. Not both at the same time. -- Roger -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OUTER JOIN bug in 5.0.1?
Josh Trutwin [EMAIL PROTECTED] wrote on 09/21/2004 09:40:03 AM: On Tue, 21 Sep 2004 08:57:21 -0400 [EMAIL PROTECTED] wrote: snip snip again Perhaps another example would help. I've been trying to re-write another join query that's designed to produce an attendance record for each student detailing which classes they've attended and which they've missed. Each occurrence of a student (PK tech_id) attending a class (PK c_id) is recorded in an intersection table class_attneded (tech_id, c_id PK). The absence of a record in this table indicates the student missed the class. So if tech_id 123123 was at classes 1 and 3, there would be records in the class_attended table: tech_id - class_id 1231231 1231233 The query that worked pre 5.0.1 is: SELECT * FROM student s INNER JOIN enrollment e ON e.tech_id = s.tech_id INNER JOIN class_attended ca ON ca.tech_id = s.tech_id RIGHT JOIN class c ON c.c_id = ca.c_id WHERE s.tech_id = '253542' AND c.term_id = '4' AND c.class_date NOW() ORDER BY week_no; In my mind I see this as get all the class records (the table on the right side of the RIGHT JOIN) and if you can match up the class_attended, enrollment and student information do so, otherwise set those values to NULL. In the example above this yeilds 3 rows - the for for class id 2 has NULLs in the other table data. I cannot seem to figure out the 5.0.1 equivalent because I seem to have learned this the wrong way. I tried this, starting with the class table since those are the rows that I want to have displayed regardless of matches. SELECT * FROM class c LEFT JOIN class_attended ca ON c.c_id = ca.c_id INNER JOIN student s ON ca.tech_id = s.tech_id AND ca.tech_id = '253542' INNER JOIN enrollment e ON e.tech_id = s.tech_id WHERE c.term_id = '4' AND c.class_date NOW() ORDER BY week_no; This acts like an INNER JOIN though, I can only get two rows. I've tried mucking around with it, but I just cannot get the outer row for class id = 2 to show. This was so much easier with the bug! :) Thanks for your help, Josh Hmmm. you want to see a student, all of the classes they are enrolled in and how many times they attended? I understand the relationships between the student, class, and class_attended tables (that's all related to attendance and class scheduling) but I do not understand the relationship between student and class. Is that the enrollment table? Does enrollment have a class id field on it? Are there other tables I do not know about that can tell you if a student is _supposed_ to be in a class? If enrollment does relate a student to a class, I propose the following query SELECT s.tech_id, s.name, c.c_id, c.name, count(ca.id) as days_present, count(c.c_id) as classes_held FROM student s INNER JOIN enrollment e on e.tech_id = s.tech_id INNER JOIN class c on c.c_id = e.c_id LEFT JOIN class_attended ca on ca.c_id = c.c_ID WHERE s.tech_ID = 253542 AND c.term_id = 4 AND c.class_date NOW() GROUP BY 1,2,3,4 I used a little MySQL shorthand with that query (I used column numbers in the GROUP BY instead of field names) and I eliminated the single quotes around your numbers (they WERE numbers not strings, right?). If I deduced your entity relationships correctly, this should tell you all of the classes that a person (tech_id=253542) was enrolled in for a term (term_id=4) for classes that have already happened (class_date NOW()), how many times that person has be counted present (count(ca.id)), and how many classes there have been so far (count(c.c_id)). I think we are close. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Batch Querying
I did a little deeper reading and the MySQL C API (probably the same API they wrote the ODBC driver with) does not support multiple statements through most (if not all) of its querying interfaces: http://dev.mysql.com/doc/mysql/en/mysql_query.html http://dev.mysql.com/doc/mysql/en/mysql_real_query.html http://dev.mysql.com/doc/mysql/en/mysql_stmt_prepare.html So the odds are looking rather slim that you will be able to execute more than one statement at a time. Since you are getting syntax errors I assume that you are forced into single-statement execution. Well, at least we were able to help you to use server variables and temp tables :-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Burgoon [EMAIL PROTECTED] wrote on 09/21/2004 10:01:29 AM: Hey. Thanks. First off, I meant to say semicolon (;) in my first post but I mistyped and said comma by accident. I am trying to use semicolon as my delimiter. This method works if I issue statements from PHPMyAdmin, but not through my ODBC app. However, your pseudocode made me realize something I didn't know was true. I had just assumed a temporary table would not persist between issued statements but they do if you have not closed the connection, as you stated. I don't know why I never tried this, but it works for me both with temporary tables and variables which is great. However, the syntax of your open statement still does not work for me. I get a SQL syntax error any time I try combining statements with Semicolons. Since I've been programming using SQL Server for a while now I feel like I SHOULD be able to issue commands in this way but somewhere I heard that mySQL prevents batch queries on purpose. [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] For multiple statements in one submission, have you tried using a semicolon ; not a comma , ? (http://dev.mysql.com/doc/mysql/en/Entering_queries.html) Each command may return a recordset of its own. Be prepared to either cycle through the returned recordsets or ignore them as they arrive. When you create a connection with a MySQL database, you establish an environment where variables and temporary tables can exist that is specifically yours. Just issue your commands in multiple statements WITHOUT CLOSING YOUR CONNECTION and you will be able to use those items. Here is some VB/ADO-like pseudocode to illustrate: set oConn = new Connection set oRS = new Recordset set oRSTimer = new Recordset oConn.open connection string, sUser, sPassword oConn.execute SET @Start_time = NOW() oConn.execute CREATE TEMPORARY TABLE tmpResults SELECT oRS.Open SELECT * FROM tmpResults where Col2='bluegills'; SET @End_Time=Now(), oConn, 0,1,1 oRStimer.Open [EMAIL PROTECTED], @end_time, oConn, 0,1,1 oConn.Execute DROP TABLE tmpResults oConn.Close What I did was open a connection, set a variable, create a temp table by populating it with the results of a SELECT query, get some records from that temp table AND set another variable, then finally get another record that contained the values of both temporary variables. After all that, I dropped my temporary table and closed the connection. I am nearly 100% certain that the combined statements in the oRS.Open... line will work for you. Let us know if it does or doesn't, OK?. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Burgoon [EMAIL PROTECTED] wrote on 09/21/2004 08:54:27 AM: I'm writing my first MySQL app in VB.net using myODBC. However I think this question applies to all languages using MySQL. From what I understand, I am unable to issue a batch statement of commands separated by commas to mySQL. I receive an error whenever I try to do so from my app. For this reason, I am unable to make use of SQL variables and temporary tables. I must instead use persistant tables. Can anyone tell me if this is in fact the case and if so, any suggestions on how to get over this hurdle? Thanks, Jeff -- 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: OUTER JOIN bug in 5.0.1?
On Tue, 21 Sep 2004 10:49:31 -0400 [EMAIL PROTECTED] wrote: Hmmm. you want to see a student, all of the classes they are enrolled in and how many times they attended? I understand the relationships between the student, class, and class_attended tables (that's all related to attendance and class scheduling) but I do not understand the relationship between student and class. Is that the enrollment table? Does enrollment have a class id field on it? Are there other tables I do not know about that can tell you if a student is _supposed_ to be in a class? If enrollment does relate a student to a class, I propose the following query Not quite. The class is probably causing confusion becuase I didn't explain what this application was used for. I teach a single course named ICS311 and this is my gradebook for only this course. I teach the course in many terms (summer, fall, spring, etc). Each class is a single meeting for the course. For example, class number 1 is on 9/24/04, class number 2 is on 10/5/04 etc. Class is a poor choice of words since I only care about this single course. If it helps, I have an ERD here: http://trutwins.homeip.net/gradebook.png For now ignore all the assignment and login stuff. I don't list FK's in ERD's, if you cannot derive them from the model they are: Enrollment.tech_id references Student.tech_id Enrollment.term_id references Term.term_id Class_attended.tech_id references Student.tech_id Class_attended.c_id references Class.c_id (week_no in ERD) Class.term_id references Term.term_id week_no in the ERD is the class Id since there is only one class per week. Week 1, week 2, etc. Enrollment is used because it remembers which students are enrolled in the current term for the attendance record I'm trying to create. I also use it in this query because the withdrawl date might come into play. So what I'm trying to do is display which classes (meetings/whatever) a student has attended and which they have not been at for my ICS311 course. Here's what kind of results I'd like: Student Id: 123123 Name: Josh class_id attended 1 1 2 NULL 3 1 4 1 5 NULL 6 1 So this particular student missed classes 2 and 5 because they did not have a record in the class_attended table, which would have these records: class_id tech_id 1 123123 3 123123 4 123123 6 123123 Hope that helps, let me know if more details would help. SELECT s.tech_id, s.name, c.c_id, c.name, count(ca.id) as days_present, count(c.c_id) as classes_held FROM student s INNER JOIN enrollment e on e.tech_id = s.tech_id INNER JOIN class c on c.c_id = e.c_id LEFT JOIN class_attended ca on ca.c_id = c.c_ID WHERE s.tech_ID = 253542 AND c.term_id = 4 AND c.class_date NOW() GROUP BY 1,2,3,4 Based on the description above this isn't quite what I need. I don't need to GROUP at all, just get the right OUTER JOIN clause to do this. I think we are close. Agreed, many thanks for your persistance in helping with this! Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.5 has been released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 4.1.5, a new version of the popular Open Source/Free Software Database Management System, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is the second 4.1 gamma release, mainly fixing recently discovered bugs in preparation for the upcoming production release. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed: * The Windows Installer packages are now making use of the Windows Installer (MSI) and include a new MySQL Server Instance Configuration Wizard that can be used to easily generate an optimized server configuration and to apply core security settings. * InnoDB: Added configuration option innodb_autoextend_increment for setting the size in megabytes by which InnoDB tablespaces are extended when they become full. The default value is 8, corresponding to the fixed increment of 8MB in previous versions of MySQL. Bugs fixed: * Fixed name resolving of external fields of subqueries if subquery placed in select list of query with grouping. (Bug #5326) * Fixed detection of using same table for updating and selecting in multi-update queries. (Bug #5455) * The values of the max_sort_length, sql_mode, and group_concat_max_len system variables now are stored in the query cache with other query information to avoid returning an incorrect result from the query cache. (Bug #5394) (Bug #5515) * Fixed syntax analyzer with sql_mode=IGNORE_SPACE. It happened to take phrases like default .07 as identifier.identifier. (Bug #5318) * Fixed illegal internal field length of user variables of integer type. This showed up when creating a table as select @variable. (Bug #4788) * Fixed a buffer overflow in prepared statements API (libmysqlclient) when a statement containing thousands of placeholders was executed. (Bug #5194) * Fixed a bug in the server when after reaching a certain limit of prepared statements per connection (97), statement ids began to overlap, so occasionally wrong statements were chosen for execution. (Bug #5399) * Fixed a bug in prepared statements when LIKE used with arguments in different character sets crashed server on first execute. (Bug #4368) * Fixed a bug in prepared statements when providing '-00-00' date to a parameter lead to server crash. (Bug #4231, Bug #4562) * Fixed a bug in OPTIMIZE TABLE that could cause table corruption on FULLTEXT indexes. (Bug #5327) * InnoDB: Fixed a bug that InnoDB only allowed a maximum of 1000 connections inside InnoDB at the same time. A higher number could cause an assertion failure in sync0arr.c, line 384. Now we allow 1000, 1, or 5, depending on the buffer pool size. (Bug #5414) Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQFBUF/MSVDhKrJykfIRAutQAJ4kOnOfH+zFPrcOccPqQrzyafMP8ACfXbu7 9sHpaM3kvFUv3MxBmYXWe8U= =QJe/ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to import data to diff tables
I have a table with 9 columns, one is an auto_increment for primary key. Data set: 2004-09-21 10:35:50,2004-09-21 10:45:48,tcp,111.111.111.111,80,222.222.222.222,1555,4700 Currently I just dump everyting into one table and query it that way. I was hoping to learn how to place the data into different tables. This will help the data files and index files from getting to large. Im just not sure where to start??? Currently I just use load data infile command from a shell script. I was wanting to have the datetime's stored in a different table, but have the same primary key as the data from the table with the Ip's and such. And help is great Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Anomaly in date subtraction
Hi Everybody, The following is my scenario: mysql select mop_id, job_id, end_time, start_time, end_time-start_time from bjs_stat where sol_id = ''; +++-+-+-+ | mop_id | job_id | end_time| start_time | end_time-start_time | +++-+-+-+ | ABH| CDCIK | 2004-09-01 21:29:07 | 2004-09-01 21:29:03 | 4 | | ABH| DFILE | 2004-09-01 21:28:58 | 2004-09-01 21:28:58 | 0 | | ABH| EODBA | 2004-09-01 21:28:59 | 2004-09-01 21:28:58 | 1 | | ABH| EODDP | 2004-09-01 21:26:36 | 2004-09-01 20:58:23 | 6813 | | ABH| FEDRP | 2004-09-01 21:29:02 | 2004-09-01 21:28:59 | 43 | | ABH| FFDAC | 2004-09-01 20:58:20 | 2004-09-01 20:58:19 | 1 | | ABH| FSLRP | 2004-09-01 21:28:57 | 2004-09-01 21:28:50 | 7 | | ABH| LLIEN | 2004-09-01 21:26:41 | 2004-09-01 21:26:39 | 2 | | ABH| RECSF | 2004-09-01 21:26:39 | 2004-09-01 21:26:36 | 3 | | ABH| TDCHK | 2004-09-01 21:29:12 | 2004-09-01 21:29:07 | 5 | | ABH| TODTD | 2004-09-01 21:29:03 | 2004-09-01 21:29:02 | 1 | | ABH| TXOD | 2004-09-01 20:58:23 | 2004-09-01 20:58:20 | 3 | | CBOD | EODAF | 2004-09-01 04:05:27 | 2004-09-01 04:05:11 | 16 | | CBOD | EXCPD | 2004-09-01 04:04:51 | 2004-09-01 04:04:45 | 6 | | CBOD | INOPR | 2004-09-01 04:05:30 | 2004-09-01 04:05:27 | 3 | | CBOD | INTFC | 2004-09-01 04:04:54 | 2004-09-01 04:04:51 | 3 | | CBOD | INTFD | 2004-09-01 04:04:55 | 2004-09-01 04:04:54 | 1 | | CBOD | INTKD | 2004-09-01 04:04:56 | 2004-09-01 04:04:55 | 1 | | CBOD | INTRD | 2004-09-01 04:04:57 | 2004-09-01 04:04:56 | 1 | | CBOD | INTSB | 2004-09-01 04:09:33 | 2004-09-01 04:09:29 | 4 | | CBOD | LADGF | 2004-09-01 04:09:21 | 2004-09-01 04:09:11 | 10 | | CBOD | LADGN | 2004-09-01 04:09:11 | 2004-09-01 04:08:18 | 93 | | CBOD | LADSP | 2004-09-01 04:09:29 | 2004-09-01 04:09:22 | 7 | | CBOD | MISTD | 2004-09-01 04:08:18 | 2004-09-01 04:05:30 | 288 | | CBOD | REGFL | 2004-09-01 04:05:11 | 2004-09-01 04:05:07 | 4 | | CBOD | SSS| 2004-09-01 04:04:45 | 2004-09-01 04:04:40 | 5 | | CBOD | TDS| 2004-09-01 04:05:06 | 2004-09-01 04:04:57 | 49 | - | ISOLOP | BGREP | 2004-09-01 07:37:51 | 2004-09-01 07:37:49 | 2 | | ISOLOP | DEPRP | 2004-09-01 07:37:53 | 2004-09-01 07:37:51 | 2 | | ISOLOP | DOCRP | 2004-09-01 07:37:55 | 2004-09-01 07:37:53 | 2 | | ISOLOP | EXCPE | 2004-09-01 07:37:49 | 2004-09-01 07:37:47 | 2 | | ISOLOP | FBDLK | 2004-09-01 07:37:57 | 2004-09-01 07:37:55 | 2 | | ISOLOP | FFDAR | 2004-09-01 07:38:00 | 2004-09-01 07:37:58 | 42 | - | ISOLOP | FFDVR | 2004-09-01 07:38:03 | 2004-09-01 07:38:02 | 1 | | ISOLOP | IDREP | 2004-09-01 07:37:58 | 2004-09-01 07:37:57 | 1 | | ISOLOP | TODRP | 2004-09-01 07:38:02 | 2004-09-01 07:38:00 | 2 | +++-+-+-+ 36 rows in set (0.00 sec) mysql desc bjs_stat; ++-+--+-++---+ | Field | Type| Null | Key | Default| Extra | ++-+--+-++---+ | sol_id | varchar(8) | | PRI || | | bod_date | date| | PRI | -00-00 | | | mop_id | varchar(10) | | PRI || | | job_id | varchar(5) | | PRI || | | start_time | datetime| YES | | NULL | | | end_time | datetime| YES | | NULL | | | user_id| varchar(15) | YES | | NULL | | ++-+--+-++---+ 7 rows in set (0.00 sec) While in the query when I am checking for the diff in time, records shown just above the underlines are interesting. When the actual time diff is less than 10 secs, result shows 40 secs. Is this a bug? My version is mysql-max 4.1.1 The table was created using load data local inflie at mysql prompt from a plain text separated with '|'. Can any patrons help me? Thanking in Advance, Eldo
Re: Anomaly in date subtraction
Time is converted to numbers then subtracted. -- With best regards, Gleb Kozyrev. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-4.1.5 is released
Hi! InnoDB is a MySQL table type which provides ACID transactions, row-level locking, consistent, non-locking SELECTs (MVCC concurrency control), FOREIGN KEY constraints with CASCADE options, and a commercial hot backup tool. Release 4.1.5 is mainly a bugfix release. InnoDB bug fixing is lagging behind after the summer vacation season, and there are a few important outstanding bugs in 4.1.5. See below about them. The full InnoDB changelog Functionality added: * Added configuration option innodb_autoextend_increment for setting the size in megabytes by which InnoDB tablespaces are extended when they become full. The default value is 8, corresponding to the fixed increment of 8MB in previous versions of MySQL. Bugs fixed: * Fixed a bug that InnoDB only allowed a maximum of 1000 connections inside InnoDB at the same time. A higher number could cause an assertion failure in sync0arr.c, line 384. Now we allow 1000, 1, or 5, depending on the buffer pool size. (Bug #5414) Outstanding bugs: * If you use the my.cnf option innodb_file_per_table, you cannot create TEMPORARY InnoDB type tables, unless you add a database 'tmp' (in Windows 'temp') to your MySQL installation. * Column prefix indexes, where only an initial prefix of a column is taken to an index, do not work yet properly in the UTF-8 character set. Upgrading from earlier MySQL versions: * If you have created or used InnoDB tables with TIMESTAMP columns in MySQL versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to MySQL-4.1.4 or later. The storage format in those MySQL versions for a TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then no rebuild of TIMESTAMP tables is needed. * If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed columns in MySQL versions = 4.1.2, then you have to rebuild those tables after you upgrade to = 4.1.3. The reason is that the sorting order of those characters and the space character changes for some character sets in 4.1.3. See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases where you need to rebuild the table (also MyISAM tables need a rebuild in these cases). Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anomaly in date subtraction
That seems to be vague to me. I would like to know how the time is converted to numbers? is it upto seconds or even beyond that? happier if resolved, Thanks in advance. reg, Eldo. On Tue, 21 Sep 2004 21:46:02 +0300, Gleb Kozyrev [EMAIL PROTECTED] wrote: Time is converted to numbers then subtracted. -- With best regards, Gleb Kozyrev. -- Thanks Regards, Eldo Skaria -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inserting null to not null columns
Hi folks: I have an InnoDB database, the tables created using MySQL Control Center 0.9.4-beta (winXP pro platform). Each table has some columns which I have checked as Nulls Allowed. I am building a pure Java GUI to the database. Got the SQL statements working fine, but have discovered that I can successfully insert rows into a table without including a value for a column which should be blocking nulls. e.g. Table A id (PK, auto increment) name type ssn ( nulls allowed specified) == name and type do not have nulls allowed specified, so I think they should be NOT NULL. they also have no default specified. then insert into A (id,name,ssn) values (NULL,Jane,9) this statement works fine, but I think it should give me an error by saying that I'm trying to insert a row without providing a value for the type column (which has no default specified). Looking at the create statement for the tables, MySQL Control Center has supplied defaults of blanks...can I turn off that preference ? using MySQL 4.0.18 Can anyone provide clarification? thanks... Donna
Custom Auto-Increment Problem
I am trying to create a table (projects) with the following basic column structure: id (primary key) id_client id_clientkey The ideas is that I will fill it with data like this: table: projects id id_client id_clientkey - 1 1 1 2 1 2 3 1 3 4 2 1 5 1 4 6 2 2 7 3 1 etc. where id_owner is the primary key of a 'client' table with associated data and the id_clientkey column auto-increments but on that clients projects. What I am going for is when I perform: INSERT INTO projects SET id_client = 1 the next id_clientkey value would be 5 in this case. Ideally my command wound be: INSERT INTO projects (id_client, id_clientkey) SELECT 1, MAX(id_clientkey) + 1 FROM projects WHERE id_client = 1 but MySQL results in an error when I run this. I am guessing that you can use INSERT ... SELECT on the same table for each argument. I am running this via a PHP front end so I figure I have 2 work arounds: look-up the MAX(id_clientkey) value first then use in a subsequent query but risk a new row created by another user between the 2 transactions OR use a table lock but then risk locking out other users. Side Question: If I go down the lock path (which seems the best) can have other INSERT requests wait until the table is unlocked or will a INSERT request during a lock simply return an error? There will be multiple web users triggering INSERT and UPDATE requests on this table and the extra millisecond of delay waiting inline for the unlock is not a concern to me. I am hoping that there is a more elegant solution than above. I have read through the manual and a few list and Google searches with no avail. Dan T -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Secure logon from VB.net
Thomas,
Are you sure that encrypting the connection string is what your network admin means?
When I hear someone say they want the logon encrypted I would think they mean store
the password of a user encrypted in the db. That is, the application requires the user
to logon and user info is stored in the db to logon against. When the user enters a
username and password the password is encrypted and compared to an encrypted password
in the db to validate the user.
I think you can use ssl to encrypt all communication between your app and the database
but I don't know of a way to pass an encrypted connection string and have the db
decrypt it before it makes a connection. My ignorance doesn't mean its not possible so
maybe someone else can suggest something.
If the idea is to prevent someone from seeing your code and learning a db logon I
guess you could store the connection string encrypted in a config file for your app
then decrypt it before you connect. You'd have to use an encryption that can be
decrypted with a key.
Checkout the System.Security.Cryptography.DESCryptoServiceProvider class
Hope that helps,
Joe Audette
Thomas Trutt [EMAIL PROTECTED] wrote:
Hello all,
Ok i know this may be a simple question but i need a little help.
I am writing a program in VB.net that uses MySQL as a backend. My net admin
wants the log on to be encrypted??
This is currently how i am connecting:
Public LocalSYS As New OdbcConnection(DRIVER={MySQL ODBC 3.51
Driver}; _
SERVER=192.168.0.1; _
DATABASE=DB; _
UID=User; _
PASSWORD=Password; _
OPTION=3;)
As you can see its a public variable that i have declared so that i don't
have to continuously add it for every form in the program.. So the question
i have is how do i change this so that it is encrypted??? Any ideas and
suggestions would be greatly appreciated..
Many thanks,
Tom T
[EMAIL PROTECTED]
http://www.joeaudette.com
http://www.mojoportal.com
Re: Anomaly in date subtraction
On Wed, 22 Sep 2004 00:20:32 +0530, Eldo Skaria [EMAIL PROTECTED] wrote: That seems to be vague to me. I would like to know how the time is converted to numbers? is it upto seconds or even beyond that? happier if resolved, It is converted in such a way that looking at number we can easily read the date mysql select now(), now() + 0; +-++ | now() | now() + 0 | +-++ | 2004-09-21 22:29:30 | 20040921222930 | +-++ Please read the manual. 6.3.4 Date and Time Functions -- With best regards, Gleb Kozyrev. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Custom Auto-Increment Problem
Read this: http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html and post the SHOW CREATE TABLE for your projects table if that didn't answer your question. Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Dan Tappin [EMAIL PROTECTED] wrote on 09/21/2004 03:04:52 PM: I am trying to create a table (projects) with the following basic column structure: id (primary key) id_client id_clientkey The ideas is that I will fill it with data like this: table: projects id id_client id_clientkey - 1 1 1 2 1 2 3 1 3 4 2 1 5 1 4 6 2 2 7 3 1 etc. where id_owner is the primary key of a 'client' table with associated data and the id_clientkey column auto-increments but on that clients projects. What I am going for is when I perform: INSERT INTO projects SET id_client = 1 the next id_clientkey value would be 5 in this case. Ideally my command wound be: INSERT INTO projects (id_client, id_clientkey) SELECT 1, MAX(id_clientkey) + 1 FROM projects WHERE id_client = 1 but MySQL results in an error when I run this. I am guessing that you can use INSERT ... SELECT on the same table for each argument. I am running this via a PHP front end so I figure I have 2 work arounds: look-up the MAX(id_clientkey) value first then use in a subsequent query but risk a new row created by another user between the 2 transactions OR use a table lock but then risk locking out other users. Side Question: If I go down the lock path (which seems the best) can have other INSERT requests wait until the table is unlocked or will a INSERT request during a lock simply return an error? There will be multiple web users triggering INSERT and UPDATE requests on this table and the extra millisecond of delay waiting inline for the unlock is not a concern to me. I am hoping that there is a more elegant solution than above. I have read through the manual and a few list and Google searches with no avail. Dan T -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Secure logon from VB.net
You can connect securly using ssh and perform port forwarding through the
tunnel.
Paul
-Original Message-
From: Joe Audette [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 21, 2004 2:32 PM
To: [EMAIL PROTECTED]
Subject: Re: Secure logon from VB.net
Thomas,
Are you sure that encrypting the connection string is what your network
admin means?
When I hear someone say they want the logon encrypted I would think they
mean store the password of a user encrypted in the db. That is, the
application requires the user to logon and user info is stored in the db to
logon against. When the user enters a username and password the password is
encrypted and compared to an encrypted password in the db to validate the
user.
I think you can use ssl to encrypt all communication between your app and
the database but I don't know of a way to pass an encrypted connection
string and have the db decrypt it before it makes a connection. My ignorance
doesn't mean its not possible so maybe someone else can suggest something.
If the idea is to prevent someone from seeing your code and learning a db
logon I guess you could store the connection string encrypted in a config
file for your app then decrypt it before you connect. You'd have to use an
encryption that can be decrypted with a key.
Checkout the System.Security.Cryptography.DESCryptoServiceProvider class
Hope that helps,
Joe Audette
Thomas Trutt [EMAIL PROTECTED] wrote:
Hello all,
Ok i know this may be a simple question but i need a little help.
I am writing a program in VB.net that uses MySQL as a backend. My net admin
wants the log on to be encrypted??
This is currently how i am connecting:
Public LocalSYS As New OdbcConnection(DRIVER={MySQL ODBC 3.51
Driver}; _
SERVER=192.168.0.1; _
DATABASE=DB; _
UID=User; _
PASSWORD=Password; _
OPTION=3;)
As you can see its a public variable that i have declared so that i don't
have to continuously add it for every form in the program.. So the question
i have is how do i change this so that it is encrypted??? Any ideas and
suggestions would be greatly appreciated..
Many thanks,
Tom T
[EMAIL PROTECTED]
http://www.joeaudette.com
http://www.mojoportal.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Secure logon from VB.net
Thanks,
Actually it is the transmission connection string he is worried
about. I might have to go with an SSL connection and go from there.. The
problems being that I'm not sure what software i have available to me on
the server and what software i can add to the client machines..
this might be another simple question but with SSL can i have 5-6 machines
all connected to the same server with the same user id being used??
Unfortunately I'm new to SSL..
Many thanks,
Tom T
At 03:31 PM 9/21/2004, you wrote:
Thomas,
Are you sure that encrypting the connection string is what your network
admin means?
When I hear someone say they want the logon encrypted I would think they
mean store the password of a user encrypted in the db. That is, the
application requires the user to logon and user info is stored in the db
to logon against. When the user enters a username and password the
password is encrypted and compared to an encrypted password in the db to
validate the user.
I think you can use ssl to encrypt all communication between your app and
the database but I don't know of a way to pass an encrypted connection
string and have the db decrypt it before it makes a connection. My
ignorance doesn't mean its not possible so maybe someone else can suggest
something.
If the idea is to prevent someone from seeing your code and learning a db
logon I guess you could store the connection string encrypted in a config
file for your app then decrypt it before you connect. You'd have to use an
encryption that can be decrypted with a key.
Checkout the System.Security.Cryptography.DESCryptoServiceProvider class
Hope that helps,
Joe Audette
Thomas Trutt [EMAIL PROTECTED] wrote:
Hello all,
Ok i know this may be a simple question but i need a little help.
I am writing a program in VB.net that uses MySQL as a backend. My net admin
wants the log on to be encrypted??
This is currently how i am connecting:
Public LocalSYS As New OdbcConnection(DRIVER={MySQL ODBC 3.51
Driver}; _
SERVER=192.168.0.1; _
DATABASE=DB; _
UID=User; _
PASSWORD=Password; _
OPTION=3;)
As you can see its a public variable that i have declared so that i don't
have to continuously add it for every form in the program.. So the question
i have is how do i change this so that it is encrypted??? Any ideas and
suggestions would be greatly appreciated..
Many thanks,
Tom T
[EMAIL PROTECTED]
http://www.joeaudette.com
http://www.mojoportal.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Secure logon from VB.net
Thomas,
http://dev.mysql.com/doc/mysql/search.php?q=ssllang=encharset=iso-8859-1
It looks like SSL/SSH are only available as of MySQL 4 or higher.
Is your Net Admin really worried about eaves dropping of packets on the local network.
It must be very sensitive data. If you already have a MySQL database on your network
that is lower version than 4 then how do the other apps connect to it securely? Or is
your app the only one that needs this level of security and other app can transmit to
the db in clear text?
Joe Audette
Thomas Trutt [EMAIL PROTECTED] wrote:
Thanks,
Actually it is the transmission connection string he is worried
about. I might have to go with an SSL connection and go from there.. The
problems being that I'm not sure what software i have available to me on
the server and what software i can add to the client machines..
this might be another simple question but with SSL can i have 5-6 machines
all connected to the same server with the same user id being used??
Unfortunately I'm new to SSL..
Many thanks,
Tom T
At 03:31 PM 9/21/2004, you wrote:
Thomas,
Are you sure that encrypting the connection string is what your network
admin means?
When I hear someone say they want the logon encrypted I would think they
mean store the password of a user encrypted in the db. That is, the
application requires the user to logon and user info is stored in the db
to logon against. When the user enters a username and password the
password is encrypted and compared to an encrypted password in the db to
validate the user.
I think you can use ssl to encrypt all communication between your app and
the database but I don't know of a way to pass an encrypted connection
string and have the db decrypt it before it makes a connection. My
ignorance doesn't mean its not possible so maybe someone else can suggest
something.
If the idea is to prevent someone from seeing your code and learning a db
logon I guess you could store the connection string encrypted in a config
file for your app then decrypt it before you connect. You'd have to use an
encryption that can be decrypted with a key.
Checkout the System.Security.Cryptography.DESCryptoServiceProvider class
Hope that helps,
Joe Audette
Thomas Trutt wrote:
Hello all,
Ok i know this may be a simple question but i need a little help.
I am writing a program in VB.net that uses MySQL as a backend. My net admin
wants the log on to be encrypted??
This is currently how i am connecting:
Public LocalSYS As New OdbcConnection(DRIVER={MySQL ODBC 3.51
Driver}; _
SERVER=192.168.0.1; _
DATABASE=DB; _
UID=User; _
PASSWORD=Password; _
OPTION=3;)
As you can see its a public variable that i have declared so that i don't
have to continuously add it for every form in the program.. So the question
i have is how do i change this so that it is encrypted??? Any ideas and
suggestions would be greatly appreciated..
Many thanks,
Tom T
[EMAIL PROTECTED]
http://www.joeaudette.com
http://www.mojoportal.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
[EMAIL PROTECTED]
http://www.joeaudette.com
http://www.mojoportal.com
RE: Custom Auto-Increment Problem
Hi Shawn, First off thanks for the tip. I had read that page once already but after reading twice again after your post I realized that the answer was right there. Wrapping that concept around my brain really hurt but I get it now. I had this: CREATE TABLE projects ( id int auto_increment, id_project int, id _client, PRIMARY KEY (id) ) When I should have this: CREATE TABLE projects ( id int auto_increment, id_client int, PRIMARY KEY (id_client, id) ) which now auto increments based on the id_client value such as: id id_client 1 1 2 1 3 1 1 2 4 1 2 2 1 3 This was the 'elegant' solution to my problem which was not really a problem after all. I have never gone beyond simple individual primary keys before so I never realized that this was so easy to accomplish. Hopefully this post will help some other future newbie out there. Thanks again, Dan T -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 21, 2004 1:29 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Custom Auto-Increment Problem Read this: http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html and post the SHOW CREATE TABLE for your projects table if that didn't answer your question. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JOIN columns with different types
Hi, MySQL seems to be able to JOIN columns with different types, e.g., one column type is int; the other type is varchar. My question is: how badly does that affect the join performance even though both columns are indexed. Thanks! Qunfeng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Secure logon from VB.net
Hi Joe,
My understanding is, and please don't quite me on this one, is
that it is a shared server.. If i'm right the server that my db will be
running on also houses some large db's for our web services, here at
Cornell. The data I'm holding is actually very, very low security, its tick
marks, but it is also the only database being accessed by an outside
client.. The program i wrote is a desktop application that the user can
record tick marks for when they are asked different types of questions at
the refrence and computer operators desk here in the Library. It also
stores Invoice transactions but there is no CC or payment information
stored or used in the actual program. I think what my Net Admins concern is
that someone may get access to my user name and password and somehow gain
access to the server as a whole, even though the user name and password
only has INSERT, DELETE, UPDATE,, and READ, access to my DB.. But i do know
that our servers are usually a nice target for hackers, ie bored computer
Science majors.. :o)
Again many thanks,
Tom T
At 04:00 PM 9/21/2004, you wrote:
Thomas,
http://dev.mysql.com/doc/mysql/search.php?q=ssllang=encharset=iso-8859-1
It looks like SSL/SSH are only available as of MySQL 4 or higher.
Is your Net Admin really worried about eaves dropping of packets on the
local network. It must be very sensitive data. If you already have a MySQL
database on your network that is lower version than 4 then how do the
other apps connect to it securely? Or is your app the only one that needs
this level of security and other app can transmit to the db in clear text?
Joe Audette
Thomas Trutt [EMAIL PROTECTED] wrote:
Thanks,
Actually it is the transmission connection string he is worried
about. I might have to go with an SSL connection and go from there.. The
problems being that I'm not sure what software i have available to me on
the server and what software i can add to the client machines..
this might be another simple question but with SSL can i have 5-6 machines
all connected to the same server with the same user id being used??
Unfortunately I'm new to SSL..
Many thanks,
Tom T
At 03:31 PM 9/21/2004, you wrote:
Thomas,
Are you sure that encrypting the connection string is what your network
admin means?
When I hear someone say they want the logon encrypted I would think they
mean store the password of a user encrypted in the db. That is, the
application requires the user to logon and user info is stored in the db
to logon against. When the user enters a username and password the
password is encrypted and compared to an encrypted password in the db to
validate the user.
I think you can use ssl to encrypt all communication between your app and
the database but I don't know of a way to pass an encrypted connection
string and have the db decrypt it before it makes a connection. My
ignorance doesn't mean its not possible so maybe someone else can suggest
something.
If the idea is to prevent someone from seeing your code and learning a db
logon I guess you could store the connection string encrypted in a config
file for your app then decrypt it before you connect. You'd have to use an
encryption that can be decrypted with a key.
Checkout the System.Security.Cryptography.DESCryptoServiceProvider class
Hope that helps,
Joe Audette
Thomas Trutt wrote:
Hello all,
Ok i know this may be a simple question but i need a little help.
I am writing a program in VB.net that uses MySQL as a backend. My net admin
wants the log on to be encrypted??
This is currently how i am connecting:
Public LocalSYS As New OdbcConnection(DRIVER={MySQL ODBC 3.51
Driver}; _
SERVER=192.168.0.1; _
DATABASE=DB; _
UID=User; _
PASSWORD=Password; _
OPTION=3;)
As you can see its a public variable that i have declared so that i don't
have to continuously add it for every form in the program.. So the question
i have is how do i change this so that it is encrypted??? Any ideas and
suggestions would be greatly appreciated..
Many thanks,
Tom T
[EMAIL PROTECTED]
http://www.joeaudette.com
http://www.mojoportal.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
[EMAIL PROTECTED]
http://www.joeaudette.com
http://www.mojoportal.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Secure logon from VB.net
If MySQL is running on a unix/Linux server you can use the native ssh that
is available on that system and then you don't need anything special in so
far as ssh or SSL is concerned with MySQL. You don't have to use MySQL 4.x.
The basic idea from VB.net is to create an SSH tunnel to the MySQL host
server and forward port 3306.
From the client VB.net application, you connect to localhost( it uses port
3306 by default) and use the username and password for MySQL( on the remote
host). The connection is sent securly through the tunnel and your problem is
solved.
Make secure you close the tunnel after you use it.
Paul
-Original Message-
From: Joe Audette [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 21, 2004 3:01 PM
To: [EMAIL PROTECTED]
Subject: Re: Secure logon from VB.net
Thomas,
http://dev.mysql.com/doc/mysql/search.php?q=ssllang=encharset=iso-8859-1
It looks like SSL/SSH are only available as of MySQL 4 or higher.
Is your Net Admin really worried about eaves dropping of packets on the
local network. It must be very sensitive data. If you already have a MySQL
database on your network that is lower version than 4 then how do the other
apps connect to it securely? Or is your app the only one that needs this
level of security and other app can transmit to the db in clear text?
Joe Audette
Thomas Trutt [EMAIL PROTECTED] wrote:
Thanks,
Actually it is the transmission connection string he is worried
about. I might have to go with an SSL connection and go from there.. The
problems being that I'm not sure what software i have available to me on
the server and what software i can add to the client machines..
this might be another simple question but with SSL can i have 5-6 machines
all connected to the same server with the same user id being used??
Unfortunately I'm new to SSL..
Many thanks,
Tom T
At 03:31 PM 9/21/2004, you wrote:
Thomas,
Are you sure that encrypting the connection string is what your network
admin means?
When I hear someone say they want the logon encrypted I would think they
mean store the password of a user encrypted in the db. That is, the
application requires the user to logon and user info is stored in the db
to logon against. When the user enters a username and password the
password is encrypted and compared to an encrypted password in the db to
validate the user.
I think you can use ssl to encrypt all communication between your app and
the database but I don't know of a way to pass an encrypted connection
string and have the db decrypt it before it makes a connection. My
ignorance doesn't mean its not possible so maybe someone else can suggest
something.
If the idea is to prevent someone from seeing your code and learning a db
logon I guess you could store the connection string encrypted in a config
file for your app then decrypt it before you connect. You'd have to use an
encryption that can be decrypted with a key.
Checkout the System.Security.Cryptography.DESCryptoServiceProvider class
Hope that helps,
Joe Audette
Thomas Trutt wrote:
Hello all,
Ok i know this may be a simple question but i need a little help.
I am writing a program in VB.net that uses MySQL as a backend. My net admin
wants the log on to be encrypted??
This is currently how i am connecting:
Public LocalSYS As New OdbcConnection(DRIVER={MySQL ODBC 3.51
Driver}; _
SERVER=192.168.0.1; _
DATABASE=DB; _
UID=User; _
PASSWORD=Password; _
OPTION=3;)
As you can see its a public variable that i have declared so that i don't
have to continuously add it for every form in the program.. So the question
i have is how do i change this so that it is encrypted??? Any ideas and
suggestions would be greatly appreciated..
Many thanks,
Tom T
[EMAIL PROTECTED]
http://www.joeaudette.com
http://www.mojoportal.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
[EMAIL PROTECTED]
http://www.joeaudette.com
http://www.mojoportal.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN columns with different types
A data type conversion will have to take place and depending on the size of the tables the performance could be quite miserable. Qunfeng wrote: Hi, MySQL seems to be able to JOIN columns with different types, e.g., one column type is int; the other type is varchar. My question is: how badly does that affect the join performance even though both columns are indexed. Thanks! Qunfeng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Secure logon from VB.net
Thomas,
Because the db is lower than version 4 and doesn't support secure connections, unless
the db is on the same box as the web server, then connection strings are already being
passed between your web servers and the db accross the local network using clear text
and those connection strings are probably more fruitfull targets for internal student
hackers than yours would be. So it sounds to me like like your Net Admin is putting
an impossible requirement on your application that is not required for apps using more
critical data. I don't see a way that you can secure your connection string as it is
sent accross the local network unless they upgrade the db to a version that supports
secure connections.
Sorry I can't be of more help.
Best Regards,
Joe
Thomas Trutt [EMAIL PROTECTED] wrote:
Hi Joe,
My understanding is, and please don't quite me on this one, is
that it is a shared server.. If i'm right the server that my db will be
running on also houses some large db's for our web services, here at
Cornell. The data I'm holding is actually very, very low security, its tick
marks, but it is also the only database being accessed by an outside
client.. The program i wrote is a desktop application that the user can
record tick marks for when they are asked different types of questions at
the refrence and computer operators desk here in the Library. It also
stores Invoice transactions but there is no CC or payment information
stored or used in the actual program. I think what my Net Admins concern is
that someone may get access to my user name and password and somehow gain
access to the server as a whole, even though the user name and password
only has INSERT, DELETE, UPDATE,, and READ, access to my DB.. But i do know
that our servers are usually a nice target for hackers, ie bored computer
Science majors.. :o)
Again many thanks,
Tom T
At 04:00 PM 9/21/2004, you wrote:
Thomas,
http://dev.mysql.com/doc/mysql/search.php?q=ssllang=encharset=iso-8859-1
It looks like SSL/SSH are only available as of MySQL 4 or higher.
Is your Net Admin really worried about eaves dropping of packets on the
local network. It must be very sensitive data. If you already have a MySQL
database on your network that is lower version than 4 then how do the
other apps connect to it securely? Or is your app the only one that needs
this level of security and other app can transmit to the db in clear text?
Joe Audette
Thomas Trutt wrote:
Thanks,
Actually it is the transmission connection string he is worried
about. I might have to go with an SSL connection and go from there.. The
problems being that I'm not sure what software i have available to me on
the server and what software i can add to the client machines..
this might be another simple question but with SSL can i have 5-6 machines
all connected to the same server with the same user id being used??
Unfortunately I'm new to SSL..
Many thanks,
Tom T
At 03:31 PM 9/21/2004, you wrote:
Thomas,
Are you sure that encrypting the connection string is what your network
admin means?
When I hear someone say they want the logon encrypted I would think they
mean store the password of a user encrypted in the db. That is, the
application requires the user to logon and user info is stored in the db
to logon against. When the user enters a username and password the
password is encrypted and compared to an encrypted password in the db to
validate the user.
I think you can use ssl to encrypt all communication between your app and
the database but I don't know of a way to pass an encrypted connection
string and have the db decrypt it before it makes a connection. My
ignorance doesn't mean its not possible so maybe someone else can suggest
something.
If the idea is to prevent someone from seeing your code and learning a db
logon I guess you could store the connection string encrypted in a config
file for your app then decrypt it before you connect. You'd have to use an
encryption that can be decrypted with a key.
Checkout the System.Security.Cryptography.DESCryptoServiceProvider class
Hope that helps,
Joe Audette
Thomas Trutt wrote:
Hello all,
Ok i know this may be a simple question but i need a little help.
I am writing a program in VB.net that uses MySQL as a backend. My net admin
wants the log on to be encrypted??
This is currently how i am connecting:
Public LocalSYS As New OdbcConnection(DRIVER={MySQL ODBC 3.51
Driver}; _
SERVER=192.168.0.1; _
DATABASE=DB; _
UID=User; _
PASSWORD=Password; _
OPTION=3;)
As you can see its a public variable that i have declared so that i don't
have to continuously add it for every form in the program.. So the question
i have is how do i change this so that it is encrypted??? Any ideas and
suggestions would be greatly appreciated..
Many thanks,
Tom T
[EMAIL PROTECTED]
http://www.joeaudette.com
http://www.mojoportal.com
--
MySQL General Mailing List
For
Re: OUTER JOIN bug in 5.0.1?
Thanks! Between the ERD and your descriptions I think I've got it. Most of the others on this list who have designed a system like yours (gradebooks or attendance taking) designed it so that it supported multiple instructors for multiple courses each of which have their own class schedules (at least one or two layers of complexity more than your system has). Sorry I was over-complicating things. I believe this will give you the results you need. SELECT s.id as student_id, s.FirstName as name, c.c_id as class_id, if(isnull(ca.c_id), 'absent', 'present') as attendance FROM enrollment e INNER JOIN students s on e.tech_id = s.tech_id INNER JOIN class c ON c.class_date e.startdate and (e.enddate is null or c.class_date e.enddate) LEFT JOIN class_attended ca on ca.c_id = c.c_id WHERE s.tech_ID = 123123 and c.term_ID = 4 ORDER by class_id Shawn Green Database Administrator Unimin Corporation - Spruce Pine Josh Trutwin [EMAIL PROTECTED] wrote on 09/21/2004 12:29:10 PM: On Tue, 21 Sep 2004 10:49:31 -0400 [EMAIL PROTECTED] wrote: Hmmm. you want to see a student, all of the classes they are enrolled in and how many times they attended? I understand the relationships between the student, class, and class_attended tables (that's all related to attendance and class scheduling) but I do not understand the relationship between student and class. Is that the enrollment table? Does enrollment have a class id field on it? Are there other tables I do not know about that can tell you if a student is _supposed_ to be in a class? If enrollment does relate a student to a class, I propose the following query Not quite. The class is probably causing confusion becuase I didn't explain what this application was used for. I teach a single course named ICS311 and this is my gradebook for only this course. I teach the course in many terms (summer, fall, spring, etc). Each class is a single meeting for the course. For example, class number 1 is on 9/24/04, class number 2 is on 10/5/04 etc. Class is a poor choice of words since I only care about this single course. If it helps, I have an ERD here: http://trutwins.homeip.net/gradebook.png For now ignore all the assignment and login stuff. I don't list FK's in ERD's, if you cannot derive them from the model they are: Enrollment.tech_id references Student.tech_id Enrollment.term_id references Term.term_id Class_attended.tech_id references Student.tech_id Class_attended.c_id references Class.c_id (week_no in ERD) Class.term_id references Term.term_id week_no in the ERD is the class Id since there is only one class per week. Week 1, week 2, etc. Enrollment is used because it remembers which students are enrolled in the current term for the attendance record I'm trying to create. I also use it in this query because the withdrawl date might come into play. So what I'm trying to do is display which classes (meetings/whatever) a student has attended and which they have not been at for my ICS311 course. Here's what kind of results I'd like: Student Id: 123123 Name: Josh class_id attended 1 1 2 NULL 3 1 4 1 5 NULL 6 1 So this particular student missed classes 2 and 5 because they did not have a record in the class_attended table, which would have these records: class_id tech_id 1 123123 3 123123 4 123123 6 123123 Hope that helps, let me know if more details would help. SELECT s.tech_id, s.name, c.c_id, c.name, count(ca.id) as days_present, count(c.c_id) as classes_held FROM student s INNER JOIN enrollment e on e.tech_id = s.tech_id INNER JOIN class c on c.c_id = e.c_id LEFT JOIN class_attended ca on ca.c_id = c.c_ID WHERE s.tech_ID = 253542 AND c.term_id = 4 AND c.class_date NOW() GROUP BY 1,2,3,4 Based on the description above this isn't quite what I need. I don't need to GROUP at all, just get the right OUTER JOIN clause to do this. I think we are close. Agreed, many thanks for your persistance in helping with this! Josh
Re: Anomaly in date subtraction
Just to be a little more clear, when you're doing the subtraction like so: 2004-09-01 07:38:00 - 2004-09-01 07:37:58 MySQL converts each to numeric representations thusly: 20040901073800 - 20040901073758 which, like most things in the universe, equals 42, /not/ the 2 I think you were expecting. What you need to do is convert the times to seconds and /then/ subtract: mysql select * from whee; +-+-+ | start | end | +-+-+ | 2004-09-01 07:37:58 | 2004-09-01 07:38:00 | +-+-+ 1 row in set (0.00 sec) mysql SELECT end - start AS numeric_calc, - TIME_TO_SEC(end) - TIME_TO_SEC(start) AS time_calc - FROM whee; +--+---+ | numeric_calc | time_calc | +--+---+ | 42 | 2 | +--+---+ 1 row in set (0.00 sec) Eamon Daly - Original Message - From: Gleb Kozyrev [EMAIL PROTECTED] To: Eldo Skaria [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Tuesday, September 21, 2004 2:31 PM Subject: Re: Anomaly in date subtraction On Wed, 22 Sep 2004 00:20:32 +0530, Eldo Skaria [EMAIL PROTECTED] wrote: That seems to be vague to me. I would like to know how the time is converted to numbers? is it upto seconds or even beyond that? happier if resolved, It is converted in such a way that looking at number we can easily read the date mysql select now(), now() + 0; +-++ | now() | now() + 0 | +-++ | 2004-09-21 22:29:30 | 20040921222930 | +-++ Please read the manual. 6.3.4 Date and Time Functions -- With best regards, Gleb Kozyrev. -- 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: Anomaly in date subtraction
Just to be a little more clear, when you're doing the subtraction like so: 2004-09-01 07:38:00 - 2004-09-01 07:37:58 MySQL converts each to numeric representations thusly: 20040901073800 - 20040901073758 which, like most things in the universe, equals 42, /not/ the 2 I think you were expecting. What you need to do is convert the times to seconds and /then/ subtract: mysql select * from whee; +-+-+ | start | end | +-+-+ | 2004-09-01 07:37:58 | 2004-09-01 07:38:00 | +-+-+ 1 row in set (0.00 sec) mysql SELECT end - start AS numeric_calc, - TIME_TO_SEC(end) - TIME_TO_SEC(start) AS time_calc - FROM whee; +--+---+ | numeric_calc | time_calc | +--+---+ | 42 | 2 | +--+---+ 1 row in set (0.00 sec) Eamon Daly - Original Message - From: Gleb Kozyrev [EMAIL PROTECTED] To: Eldo Skaria [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Tuesday, September 21, 2004 2:31 PM Subject: Re: Anomaly in date subtraction On Wed, 22 Sep 2004 00:20:32 +0530, Eldo Skaria [EMAIL PROTECTED] wrote: That seems to be vague to me. I would like to know how the time is converted to numbers? is it upto seconds or even beyond that? happier if resolved, It is converted in such a way that looking at number we can easily read the date mysql select now(), now() + 0; +-++ | now() | now() + 0 | +-++ | 2004-09-21 22:29:30 | 20040921222930 | +-++ Please read the manual. 6.3.4 Date and Time Functions -- With best regards, Gleb Kozyrev. -- 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: Trying fulltext search
Randy,
could it be that the word 'bird' appears in more than half of all rows in
your dataset? I strongly recommend to spend a few minutes reading the
manual about Fulltext Search.
Regards,
Thomas
On Mon, 20 Sep 2004, Randy Paries wrote:
Hello
I have a table
CREATE TABLE community_files (
id int(3) unsigned NOT NULL auto_increment,
type int(10) unsigned NOT NULL default '0',
category_id int(10) unsigned NOT NULL default '0',
filename varchar(50) NOT NULL default '',
path varchar(255) NOT NULL default '',
description varchar(255) NOT NULL default '',
PRIMARY KEY (id),
UNIQUE KEY id (id),
KEY id_2 (id),
KEY type (type),
KEY catid (category_id),
FULLTEXT KEY description (description)
) TYPE=MyISAM;
Where I run a query like
select * from community_files where description like '%bird%';
I get records back, but if I try to do
SELECT * FROM community_files WHERE MATCH (description) AGAINST ('bird' );
I get nothing back
I am running mysqld Ver 3.23.58 on rh9
Thanks for any help
Randy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Duplicate Entries
Hello All, I am porting from 4.0.0-alpha-nt to 4.0.1-alpha-nt. In which i have a table with two primary key, my older mysql server insert all the records except the duplicate fields(Primary Key). Whereas in the new mysql server it exits whenever it sees a duplicate entry. How to resolve it ? Thanks in advance, Suresh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query with group by
Hi Michael! Talking about the query with group by issue... I'll explain my reasoning below. [...] From the manual, section 7.2.8 How MySQL Optimizes LEFT JOIN and RIGHT JOIN http://dev.mysql.com/doc/mysql/en/LEFT_JOIN_optimization.html: A LEFT JOIN B join_condition is implemented in MySQL as follows: ... * The LEFT JOIN condition is used to decide how to retrieve rows from table B. (In other words, any condition in the WHERE clause is not used.) ... So, the WHERE c2.id IS NULL cannot be applied until after the rows which match the ON clause (and the NULL rows) have been fetched. Thanks for your explanations here, it's clear now I was confused. I missed this LEFT JOIN behaviour. I was in the idea the WHERE xx IS NULL was taken into account. [...] You are certainly right that temporary and filesort are to be avoided. And they will be, if the table is properly indexed. Single column indexing won't help much here, because the WHERE condition, the GROUP BY column, and the MAX column are all different. A multi-column index on (content, location, date), however, will allow mysql to use the index to find the matching rows, find the groups, and calculate the MAX date. I still refuse to use the temporary table solution, call me fussy here. Indeed, I think giving more indexes than necessary is a bit redundant, unless completely necessary. (You will end up having more space on indexes than data itself). It's a handle with care issue for me. Anyway, I don't know if one can program an agregate UDF called something like EXTERNAL_MAX(...) or something, so that we could do like: SELECT EXTERNAL_MAX(date, version) --- i.e: Returns the version value for the row with MAX(date). This, for sure, will be the best solution. ;-) That would have to do the same thing behind the scenes. I have to beg you pardon here. ;-) Think again this solution doesn't require a JOIN, nor a temporary table. I think the UDF solution doesn't have to be less efficient than a MAX or AVG aggregate function by itself. I'm in the process of creating such a monster :-) Let me know if you are curious about and have time to test it, I will test MAX() and EXTERNAL_MAX() against a very large table. Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Duplicate Entries
- Original Message - From: Suresh [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 21, 2004 6:43 PM Subject: Duplicate Entries Hello All, I am porting from 4.0.0-alpha-nt to 4.0.1-alpha-nt. In which i have a table with two primary key, my older mysql server insert all the records except the duplicate fields(Primary Key). Whereas in the new mysql server it exits whenever it sees a duplicate entry. How to resolve it ? Am I understanding you correctly? It *sounds* like you're saying that you have a table with TWO primary keys. As far as I know, it is not possible to have two separate primary keys in a single table under any circumstances. Do you mean that you have a single primary key that has two columns in it? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
the table is read only
Hi I have installed mysql some software on aix5.2 . the edition is MySQL-3.23.58-2.aix5.1.ppc but when I used phpmyadmin to manage the mysql it told me ** #1036 - Table 'gbook' is read only *** while I have granted the data dir(/var/lib/mysql) 777 permission how to resolve it ? help me please ,how to resolve thanks a lot Yours LiRui [EMAIL PROTECTED] 2004-09-20
Re: the table is read only
What are the MySQL permissions for the phpmyadmin user? If you believe that you set the phpmyadmin user up correctly in MySQL, Did you try a 'flush privileges' in MySQL? HTH, James --- ÀîÈñ [EMAIL PROTECTED] wrote: Hi I have installed mysql some software on aix5.2 . the edition is MySQL-3.23.58-2.aix5.1.ppc but when I used phpmyadmin to manage the mysql it told me ** #1036 - Table 'gbook' is read only *** while I have granted the data dir(/var/lib/mysql) 777 permission how to resolve it ? help me please ,how to resolve thanks a lot Yours LiRui [EMAIL PROTECTED] 2004-09-20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date BETWEEN Question
Hello, I currently have a table with a completed DATETIME field. I am trying to run a query that will return all rows *inclusive* of the start and end dates. I have tried the following query: SELECT `name`, `completed` FROM `table` WHERE `completed` BETWEEN '2004-07-21' AND '2004-07-23'; The problem is that this query will only return rows from 2004-07-22, and does not include rows from 2004-07-21 or 2004-07-23, like I need it to. This could be the desired behavior for the BETWEEN operator (I couldn't seem to find any documentation of it in the documents for some reason), so I also tried: SELECT `name`, `completed` FROM `table` WHERE `completed` = '2004-07-21' AND `completed` = '2004-07-23'; This does the same thing, it only returns dates from 2004-07-22 without including the start or end date. I have tried adding a time (e.g. 2004-07-21 00:00:00 and 2004-07-23 23:59:59), but again, only 2004-07-22 is returned. If I remove one of the conditions, I get the correct result. e.g.: SELECT `name`, `completed` FROM `table` WHERE `completed` = '2004-07-21'; Will return all rows on *and* after 2004-07-21, as desired. But with both operators, it does not work. I have tried this query on both 3.23.58 and 4.0.17. I searched the net over and could not find a definitive answer to this problem, so apologies if it's been answered before. TIA, Jeremy Brown -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date BETWEEN Question
Jeremy Brown [InfoSend] wrote: Hello, I currently have a table with a completed DATETIME field. I am trying to run a query that will return all rows *inclusive* of the start and end dates. I have tried the following query: SELECT `name`, `completed` FROM `table` WHERE `completed` BETWEEN '2004-07-21' AND '2004-07-23'; Why are you comparing completed to DATEs, if completed is a DATETIME? Note that '2004-07-23' is '2004-07-23 00:00:00' when interpreted as a DATETIME. The problem is that this query will only return rows from 2004-07-22, and does not include rows from 2004-07-21 or 2004-07-23, like I need it to. This could be the desired behavior for the BETWEEN operator (I couldn't seem to find any documentation of it in the documents for some reason), so I also tried: BETWEEN is inclusive of the endpoints. This is documented in the manual http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html. SELECT `name`, `completed` FROM `table` WHERE `completed` = '2004-07-21' AND `completed` = '2004-07-23'; This does the same thing, it only returns dates from 2004-07-22 without including the start or end date. This tells us BETWEEN is not the problem, as the inequalities produce the same result. Something else is going on. Something strange, but something else. I have tried adding a time (e.g. 2004-07-21 00:00:00 and 2004-07-23 23:59:59), but again, only 2004-07-22 is returned. If I remove one of the conditions, I get the correct result. e.g.: SELECT `name`, `completed` FROM `table` WHERE `completed` = '2004-07-21'; What does SELECT name, completed FROM `table` WHERE completed = '2004-07-21'; return? Will return all rows on *and* after 2004-07-21, as desired. But with both operators, it does not work. I have tried this query on both 3.23.58 and 4.0.17. I searched the net over and could not find a definitive answer to this problem, so apologies if it's been answered before. My guess is that your real query is more complicated than what you've shown us (your table isn't really named table, is it?), and there is some interaction which accounts for this. Perhaps if you showed the full query and the table definition someone could point out the problem/solution. TIA, Jeremy Brown Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: the table is read only
Hi, Giving a permission of '777' to mysql dir is not advisable. More over that may not be the problem. It should be the privilege the phpmyadmin user is having on your Db or the specified table. Reg, Eldo Skaria On Wed, 22 Sep 2004 08:36:21 +0800, [EMAIL PROTECTED] wrote: Hi I have installed mysql some software on aix5.2 . the edition is MySQL-3.23.58-2.aix5.1.ppc but when I used phpmyadmin to manage the mysql it told me ** #1036 - Table 'gbook' is read only *** while I have granted the data dir(/var/lib/mysql) 777 permission how to resolve it ? help me please ,how to resolve thanks a lot Yours LiRui [EMAIL PROTECTED] 2004-09-20 -- Thanks Regards, Eldo Skaria -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Duplicate Entries
Hi, Suresh may be expressing that he has a primary key with two fields. Eldo. On Tue, 21 Sep 2004 19:32:17 -0400, Rhino [EMAIL PROTECTED] wrote: - Original Message - From: Suresh [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 21, 2004 6:43 PM Subject: Duplicate Entries Hello All, I am porting from 4.0.0-alpha-nt to 4.0.1-alpha-nt. In which i have a table with two primary key, my older mysql server insert all the records except the duplicate fields(Primary Key). Whereas in the new mysql server it exits whenever it sees a duplicate entry. How to resolve it ? Am I understanding you correctly? It *sounds* like you're saying that you have a table with TWO primary keys. As far as I know, it is not possible to have two separate primary keys in a single table under any circumstances. Do you mean that you have a single primary key that has two columns in it? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks Regards, Eldo Skaria -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anomaly in date subtraction
HI Daly, Thanks A lot. Eldo Skaria. On Tue, 21 Sep 2004 16:09:03 -0500, Eamon Daly [EMAIL PROTECTED] wrote: Just to be a little more clear, when you're doing the subtraction like so: 2004-09-01 07:38:00 - 2004-09-01 07:37:58 MySQL converts each to numeric representations thusly: 20040901073800 - 20040901073758 which, like most things in the universe, equals 42, /not/ the 2 I think you were expecting. What you need to do is convert the times to seconds and /then/ subtract: mysql select * from whee; +-+-+ | start | end | +-+-+ | 2004-09-01 07:37:58 | 2004-09-01 07:38:00 | +-+-+ 1 row in set (0.00 sec) mysql SELECT end - start AS numeric_calc, - TIME_TO_SEC(end) - TIME_TO_SEC(start) AS time_calc - FROM whee; +--+---+ | numeric_calc | time_calc | +--+---+ | 42 | 2 | +--+---+ 1 row in set (0.00 sec) Eamon Daly - Original Message - From: Gleb Kozyrev [EMAIL PROTECTED] To: Eldo Skaria [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Tuesday, September 21, 2004 2:31 PM Subject: Re: Anomaly in date subtraction On Wed, 22 Sep 2004 00:20:32 +0530, Eldo Skaria [EMAIL PROTECTED] wrote: That seems to be vague to me. I would like to know how the time is converted to numbers? is it upto seconds or even beyond that? happier if resolved, It is converted in such a way that looking at number we can easily read the date mysql select now(), now() + 0; +-++ | now() | now() + 0 | +-++ | 2004-09-21 22:29:30 | 20040921222930 | +-++ Please read the manual. 6.3.4 Date and Time Functions -- With best regards, Gleb Kozyrev. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks Regards, Eldo Skaria -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication is being break
Hi, I have a setup of 2-way replication of mysql. These two servers are linked with 512K line and data being inserted on both side. Sometimes I observed that the network connectivity is correct but the replication sync goes down and comes up after 30 min autometically. Is there any story behind the down/up of replication? Due to this up/down I got some of the data merged. My MySQL version is 4.0.5a beta. I am looking for the solution of this up/down if the network is well established. Regards Naveen
MySQL data files
Hello All, Is there any way by which I canstore data files of mysql in other partition of my system Regards, VijayKumar Dogra
