Field Name whitespace via MyODBC
On the windows machine I have Acess2003. I installed the MyODBC to push the data into the FreeBSD MySQL server. The connection worked like a dream. Kudos to the MyODBC team. The table that the connection created included field names that have spaces in the name. I'm trying to access the data thru my PHP scripts. I tried this: $sql7 = SELECT LERG_7_SHA.LATA ,LERG_7_SHA.SWITCH , `LERG_7_SHA.SHA INDICATOR` , `LERG_7_SHA.H ORG B TDM` FROM LERG_7_SHA WHERE ( LERG_7_SHA.SWITCH = \$npa\ AND `LERG_7_SHA.SHA INDICATOR` = \$nxx\ ) LIMIT 0, 30; And $sql7 = SELECT LERG_7_SHA.LATA ,LERG_7_SHA.SWITCH , \'LERG_7_SHA.SHA INDICATOR\' , \'LERG_7_SHA.H ORG B TDM\' ,\'LERG_7_SHA.H ORG C TDM\' , LERG_7_SHA.HOST , LERG_7_SHA.OCN , LERG_7_SHA.AOCN FROM LERG_7_SHA WHERE (LERG_7_SHA.SWITCH = \$npa\ AND \'LERG_7_SHA.SHA_INDICATOR\' = \$nxx\ ) LIMIT 0, 30; Both do not work. The query breaks down where the backticks are or tries to add the literal string of 'LERG_7_SHA.SHA INDICATOR' in the result data. I have tried with single quotes and double quotes. I can issue this command on the mysql command line and it does work. Can someone help me syntax this line correct? The script.php and the database are on the same FreeBSD machine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication on one slave and two different masters
MySQL only supports one master and many slaves. Later we will add a voting algorithm to automatically change master if something goes wrong with the current master. We will also introduce ``agent'' processes to help do load balancing by sending SELECT queries to different slaves. http://www.mysql.com/doc/en/Replication_Features.html On Wed, 2003-12-10 at 22:21, Lopez David E-r9374c wrote: mysql, query I have a situation come up where we want one slave to act as backup for two different databases located in two different hosts. Can this be done? The master setup is easy. The slave setup is unclear. Can I have two sets of master-host, master-user, and master-password, master-port, master-connect-retry, w/o the mysqld getting confused? My experience is one slave, one master. No FAQ covers multiples master on one slave that I can find. I'm running mysql version 3.23.49 on Solaris. -- http://www.johnleach.co.uk signature.asc Description: This is a digitally signed message part
MySQL client relicense?
Is this true? Has the license for the MySQL client libraries changed from LGPL to GPL? Murray Cumming www.murrayc.com [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alan Cox Sent: Mittwoch, 10. Dezember 2003 10:22 To: [EMAIL PROTECTED] Subject: Re: Fedora Core 2 wishlists On Tue, Dec 09, 2003 at 04:06:35PM -0800, Paul Heinlein wrote: On Tue, 9 Dec 2003, Alan Cox wrote: MySQL 4.x instead of MySQL 3.x Then you can't use MySQL with PHP Is there an instructive URL regarding this issue? The license document. MySQL4 changed the client libraries from LGPL to GPL which makes using mysql with a lot of stuff like PHP infeasible -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reg C API from MySQL
Statically linking using C API only you will need mysqlclient.lib For odbc interface you'll need to make odbc calls in your code and use myodbc.dll (Install myodbc on the client). You can mix calls, but why would you. I have found the C API to be the best solution for us. We statically link for dll avoidance. You can get the source or pre-built client libraries from the MySQL web site. I hope this helps. Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP 825 SE 47th Terrace Cape Coral, FL 33904 - Original Message - From: P Arunachalam [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 11, 2003 4:31 AM Subject: reg C API from MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Field Name whitespace via MyODBC
Brian, I think you are going to need to rename your fields/columns. If you are unable to rename them in mysql then you will probably need to rename them in your jet database with Access and re-import them. Your column names should be literals not wrapped in graves or quotes and should contain no whitespace. Whitespace is frequently used as a delimiter. I also avoid any characters other than alphanumeric and the occasional underscore character in field/column names . It helps avoid OS idiosyncrasies and simplifies naming conventions. I hope this helps. Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP 825 SE 47th Terrace Cape Coral, FL 33904 - Original Message - From: Brian Duke [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 11, 2003 2:50 AM Subject: Field Name whitespace via MyODBC On the windows machine I have Acess2003. I installed the MyODBC to push the data into the FreeBSD MySQL server. The connection worked like a dream. Kudos to the MyODBC team. The table that the connection created included field names that have spaces in the name. I'm trying to access the data thru my PHP scripts. I tried this: $sql7 = SELECT LERG_7_SHA.LATA ,LERG_7_SHA.SWITCH , `LERG_7_SHA.SHA INDICATOR` , `LERG_7_SHA.H ORG B TDM` FROM LERG_7_SHA WHERE ( LERG_7_SHA.SWITCH = \$npa\ AND `LERG_7_SHA.SHA INDICATOR` = \$nxx\ ) LIMIT 0, 30; And $sql7 = SELECT LERG_7_SHA.LATA ,LERG_7_SHA.SWITCH , \'LERG_7_SHA.SHA INDICATOR\' , \'LERG_7_SHA.H ORG B TDM\' ,\'LERG_7_SHA.H ORG C TDM\' , LERG_7_SHA.HOST , LERG_7_SHA.OCN , LERG_7_SHA.AOCN FROM LERG_7_SHA WHERE (LERG_7_SHA.SWITCH = \$npa\ AND \'LERG_7_SHA.SHA_INDICATOR\' = \$nxx\ ) LIMIT 0, 30; Both do not work. The query breaks down where the backticks are or tries to add the literal string of 'LERG_7_SHA.SHA INDICATOR' in the result data. I have tried with single quotes and double quotes. I can issue this command on the mysql command line and it does work. Can someone help me syntax this line correct? The script.php and the database are on the same FreeBSD machine. -- 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: MySQL client relicense?
On Thu, 11 Dec 2003 12:26:48 +0100 [EMAIL PROTECTED] wrote: Is this true? Has the license for the MySQL client libraries changed from LGPL to GPL? yes, it's true. but no, you can use MySQL 4.x with PHP. PHP at this moment _*doesn't distribute bundled libmysql*_ (PHP uses it's own license, not GPL), but you still can build PHP with external libmysql. this fact can affect only Win32 users, because they don't build PHP themselves (in most cases). --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1006: Can't create database 'X'. (errno: 13)
Hello I am having difficulties: I am working on Mac OSX 10.2.8 with mysql 4. I needed to change the data directory of MySQL from its original place (where the application have to be and not the databases because there is not enought room) to an other volume of my computer. I made a symlink from data to data in the other volume and not anymore as it was to var in the same volume before. I copied all files and directories of var in this new directory -data-. I changed with mysql mysql all this file and directories (group and owner). And I made : create database X; ERROR 1006: Can't create database 'X'. (errno: 13) I hope what I explain is clear! What did I forget ? Do I have to put also var in this new other directory? Do I have to reinstall completly MySQL because I corrupted to much this application? I am lost. Could anyone help me? Thanks in advance LN _ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
some problems with ODBC Connection
Dear Sir, I download MySQL 1.4.1 version and ODBC 3.51 Connector. I have some problem with connectoion name and nonblank password. MySQL ODBC Drivers Errors: Client does not support autentifications protocol request by server.consider upgrading MySQL client. If I can connection without password ( use blank password ), I have succesfull connection. What I can I do, download new ODBC version or new MySQL server? Alexander
How to READ/WRITE directly on MyISAM data files ?
Hello, I would like to know how to write directly to MyISAM files, without passing by SELECT or UPDATE queries. I believe this info can be found in the files myisam.h and myisammrg.h, but I am not shure if its safe and how to do it. I would very much like to have an example of this code (C code). thank you Stéphane. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to READ/WRITE directly on MyISAM data files ?
Hi, I would like to know how to write directly to MyISAM files, without passing by SELECT or UPDATE queries. I believe this info can be found in the files myisam.h and myisammrg.h, but I am not shure if its safe and how to do it. I guess it will only be safe if you're sure you've got all bugs out, like in the MySQL engine. So the question would be: WHY? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Invalid Compressed Data Error??
- Original Message - From: [EMAIL PROTECTED] To: Eric Dickner [EMAIL PROTECTED] Sent: Thursday, December 11, 2003 5:17 AM Subject: Re: Invalid Compressed Data Error?? On Wed, Dec 10, 2003 at 09:22:08PM -0500, Eric Dickner wrote: Hello, When I try and unzip the linux pc binary download file mysql-standard-4.0.16-pc-linux-i686.tar I get no further than the first four files before getting this error. I tried a couple of mirrors with the same result. ejd What error do you get? Invalid Compressed Data... but it only happens with the version of the file that I cut onto a CD to transfer to Linux (I have a winmodem and do not connect under linux...) Someone else wrote with the same problem but I doubt it is anything that can be solved from a mysql standpoint. ejd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to READ/WRITE directly on MyISAM data files ?
Good question, I am not shure if I need to. Here's why I am asking: I have a table that does not have a primary key and I need to update only one row. This complicates my Update Query since I cannot specify a Where clause. I did had a primary key RowID (AUTO INCREMENT) to be sble to specify a row in my query. But this Field (RowID) can now be seen when I do a SELECT query. I did not find a way to hide this field. So my first question should be : Is there a way to hide a field ?? Best Regards, Stéphane. -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: 11 décembre, 2003 08:22 To: MySQL (E-mail) Subject: Re: How to READ/WRITE directly on MyISAM data files ? Hi, I would like to know how to write directly to MyISAM files, without passing by SELECT or UPDATE queries. I believe this info can be found in the files myisam.h and myisammrg.h, but I am not shure if its safe and how to do it. I guess it will only be safe if you're sure you've got all bugs out, like in the MySQL engine. So the question would be: WHY? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- 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: How to READ/WRITE directly on MyISAM data files ?
[snip] So my first question should be : Is there a way to hide a field ?? [/snip] SELECT only the information you want. Let's say I have RowID Name Address City And I only want Name Address and City SELECT Name, Address, City FROM table WHERE RowID = 'foo' UPDATE table SET Name = 'foo' WHERE RowID = '12' etcetera A good book on SQL basics will get you a long way on things like this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to READ/WRITE directly on MyISAM data files ?
Hi, I am not shure if I need to. Here's why I am asking: I have a table that does not have a primary key and I need to update only one row. This complicates my Update Query since I cannot specify a Where clause. Well, as you have figured out, without some kind of unique ID, it's pretty much impossible to update a row. I did had a primary key RowID (AUTO INCREMENT) to be sble to specify a row in my query. But this Field (RowID) can now be seen when I do a SELECT query. I did not find a way to hide this field. So my first question should be : Is there a way to hide a field ?? Well, that depends on how you are displaying the data, doesn't it :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to READ/WRITE directly on MyISAM data files ?
Thank you, but I already know the basics of SQL SELECT statements. What I am trying to say is, if a User writes a SELECT clause, I do not want him to see the RowID field. I do not want him to write a long SELECT statement, especially if my table has 20 FIELDS or more. (Can you imagine the user writing these queries all the time). I want him to be able to write SELECT * FROM ATABLE. Thank you, Stéphane. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: 11 décembre, 2003 08:41 To: Stéphane Bischoff; Martijn Tonies; MySQL (E-mail) Subject: RE: How to READ/WRITE directly on MyISAM data files ? [snip] So my first question should be : Is there a way to hide a field ?? [/snip] SELECT only the information you want. Let's say I have RowID Name Address City And I only want Name Address and City SELECT Name, Address, City FROM table WHERE RowID = 'foo' UPDATE table SET Name = 'foo' WHERE RowID = '12' etcetera A good book on SQL basics will get you a long way on things like this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to READ/WRITE directly on MyISAM data files ?
[snip] Thank you, but I already know the basics of SQL SELECT statements. What I am trying to say is, if a User writes a SELECT clause, I do not want him to see the RowID field. I do not want him to write a long SELECT statement, especially if my table has 20 FIELDS or more. (Can you imagine the user writing these queries all the time). I want him to be able to write SELECT * FROM ATABLE. [/snip] Well, that pretty much misses the point then, doesn't it? Are your records not unique? There is no way that you can write an update statement that would perform the operation on the proper record? Can you show us a bit of the table? With more information we can help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication error (1236 - impossible position on slave)
Hi, We have 2 MySQL 4.0.16 with replication enabled (M - S), only InnoDB tables. We're getting Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236) in the slave. The master server had a problem... when it came back (with success) the log have: 031210 15:19:24 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 3 4169000545 InnoDB: Doing recovery: scanned up to log sequence number 3 4171205097 031210 15:19:25 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 45866, file name ./SV07-bin.008 031210 15:19:43 InnoDB: Flushing modified pages from the buffer pool... 031210 15:19:51 InnoDB: Started But in the slave log: 031210 17:02:36 Slave: connected to master '[EMAIL PROTECTED]:xxx',replication resumed in log 'SV07-bin.008' at position 45866 031210 17:02:36 Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236) 031210 17:02:36 Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log 031210 17:02:36 Slave I/O thread exiting, read up to log 'SV07-bin.008', position 45866 With show master status (on master) I get: File = SV07-bin.009 Position = 456371766 And with show slave status (on slave) I get: Master_log_file = SV07-bin.008 Read_master_log_pos = 45866 Relay_log_file = SV08-relay-bin.011 Relay_log_pos = 458600084 Relay_master_log = SV07-bin.008 Slave_io_running = no Slave_sql_running = yes Exec_master_log_pos = 45866 Relay_log_space = 458600084 Is SV07-bin.008 corrupted? How can I debug? Thanks, Eduardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to READ/WRITE directly on MyISAM data files ?
Without a unique identifier, the only way you're gonna get to update right is to use other data in the row to identifiy the record... so with a table structure of name address state your users can select anything they want, filtering w/ a where clause. If you grant them update on the table, then they'll just have to: update your_table set name = 'Dan' where name = 'Daniel' and address = '55 main' and state = 'Virginia'; I don't agree with this approach, mind you, it breaks all kinds of best practices maybe it would help if you explained _why_ you don't want your users to see a numerical id field for each record -Original Message- From: Stéphane Bischoff [mailto:[EMAIL PROTECTED] Sent: Thursday, December 11, 2003 8:43 AM To: 'Jay Blanchard'; MySQL (E-mail) Subject: RE: How to READ/WRITE directly on MyISAM data files ? Thank you, but I already know the basics of SQL SELECT statements. What I am trying to say is, if a User writes a SELECT clause, I do not want him to see the RowID field. I do not want him to write a long SELECT statement, especially if my table has 20 FIELDS or more. (Can you imagine the user writing these queries all the time). I want him to be able to write SELECT * FROM ATABLE. Thank you, Stéphane. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: 11 décembre, 2003 08:41 To: Stéphane Bischoff; Martijn Tonies; MySQL (E-mail) Subject: RE: How to READ/WRITE directly on MyISAM data files ? [snip] So my first question should be : Is there a way to hide a field ?? [/snip] SELECT only the information you want. Let's say I have RowID Name Address City And I only want Name Address and City SELECT Name, Address, City FROM table WHERE RowID = 'foo' UPDATE table SET Name = 'foo' WHERE RowID = '12' etcetera A good book on SQL basics will get you a long way on things like this. -- 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: [RE-REPOST] Openssl support not activated?
Mark Matthews wrote: Greg G wrote: I'm still having trouble figuring this out. Please help! I compiled MySQL 4.1.1 --with-openssl --with-vio and when I look at the variables with mysqladmin, has_openssl is set to NO. I've got OpenSSL 0.9.7c installed. I'm seeing this on both Solaris and Debian hosts. I'm not seeing any errors during the configure or compile phases. What could have happened to cause this, and how can I fix it? Did you create certificates and configure mysqld and the client libraries to find them (as it says to in the manual :))? http://www.mysql.com/doc/en/Secure_connections.html According to 4.4.10.2 Requirements, have_openssl must be YES prior to setting up certificates. Is this in error? -Greg G
RE: How to READ/WRITE directly on MyISAM data files ?
Hello, Here's an example : I have a table named Product. TABLE : PRODUCT +-+-+-+-+-+ |RowID| Name| Company| Price | Warranty | | | | | | | +-+-+-+-+-+ | 1 | PC 1000 | MyCom Inc. | 1000.00| 1 year| | 2 | PC 1000 | MyCom Inc. | 1200.00| 2 year| | 3 | PC 1000 | MyCom Inc. | 1300.00| 3 year| | 4 | PC 2000 | MyCom Inc. | 1200.00| 1 year| | 4 | PC 2000 | MyCom Inc. | 1300.00| 2 year| | 4 | PC 2000 | MyCom Inc. | 1400.00| 3 year| | 4 | PC 3000 | MyCom Inc. | 1500.00| 1 year| | 4 | PC 3000 | MyCom Inc. | 1600.00| 2 year| | 4 | PC 3000 | MyCom Inc. | 1700.00| 3 year| | 4 | PC AR3| SPCom Inc. | 1200.00| 2 year| | 4 | PC AR3| SPCom Inc. | 1300.00| 3 year| | 4 | PC AR4| SPCom Inc. | 1400.00| 4 year| +-+-+-+-+-+ From My Server Side Application (C code) I can Update my Rows using my RowID. Example : UPDATE TABLE product SET Price=2000.00 WHERE RowID=3; But For My Client Side Applications : User logs in my Client app. User types in SELECT * FROM product. User does NOT WANT TO SEE RowID numbers. User wants to see this output : +-+-+-+-+ | Name| Company| Price | Warranty | | | | | | +-+-+-+-+ | PC 1000 | MyCom Inc. | 1000.00| 1 year| | PC 1000 | MyCom Inc. | 1200.00| 2 year| | PC 1000 | MyCom Inc. | 1300.00| 3 year| | PC 2000 | MyCom Inc. | 1200.00| 1 year| | PC 2000 | MyCom Inc. | 1300.00| 2 year| | PC 2000 | MyCom Inc. | 1400.00| 3 year| | PC 3000 | MyCom Inc. | 1500.00| 1 year| | PC 3000 | MyCom Inc. | 1600.00| 2 year| | PC 3000 | MyCom Inc. | 1700.00| 3 year| | PC AR3| SPCom Inc. | 1200.00| 2 year| | PC AR3| SPCom Inc. | 1300.00| 3 year| | PC AR4| SPCom Inc. | 1400.00| 4 year| +-+-+-+-+ I know there are ways to bypass this problem, but it involves much more coding in my Delphi applications on my Client side. So my question is, Is there a way to hide a field from select statements. Of course, if my user wrote SELECT RowId, Name, Company, Price, Warranty FROM product he would get the RowId in his query output. Best Regards, Stéphane. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: 11 décembre, 2003 08:49 To: Stéphane Bischoff; MySQL (E-mail) Subject: RE: How to READ/WRITE directly on MyISAM data files ? [snip] Thank you, but I already know the basics of SQL SELECT statements. What I am trying to say is, if a User writes a SELECT clause, I do not want him to see the RowID field. I do not want him to write a long SELECT statement, especially if my table has 20 FIELDS or more. (Can you imagine the user writing these queries all the time). I want him to be able to write SELECT * FROM ATABLE. [/snip] Well, that pretty much misses the point then, doesn't it? Are your records not unique? There is no way that you can write an update statement that would perform the operation on the proper record? Can you show us a bit of the table? With more information we can help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to READ/WRITE directly on MyISAM data files ?
Hi Stéphane, Here's an example : I have a table named Product. TABLE : PRODUCT +-+-+-+-+-+ |RowID | Name| Company| Price | Warranty | | | | | | | +-+-+-+-+-+ | 1 | PC 1000 | MyCom Inc. | 1000.00| 1 year| | 2 | PC 1000 | MyCom Inc. | 1200.00| 2 year| | 3 | PC 1000 | MyCom Inc. | 1300.00| 3 year| | 4 | PC 2000 | MyCom Inc. | 1200.00| 1 year| | 4 | PC 2000 | MyCom Inc. | 1300.00| 2 year| | 4 | PC 2000 | MyCom Inc. | 1400.00| 3 year| | 4 | PC 3000 | MyCom Inc. | 1500.00| 1 year| | 4 | PC 3000 | MyCom Inc. | 1600.00| 2 year| | 4 | PC 3000 | MyCom Inc. | 1700.00| 3 year| | 4 | PC AR3| SPCom Inc. | 1200.00| 2 year| | 4 | PC AR3| SPCom Inc. | 1300.00| 3 year| | 4 | PC AR4| SPCom Inc. | 1400.00| 4 year| +-+-+-+-+-+ From My Server Side Application (C code) I can Update my Rows using my RowID. Example : UPDATE TABLE product SET Price=2000.00 WHERE RowID=3; But For My Client Side Applications : User logs in my Client app. User types in SELECT * FROM product. Why?? In what application do you allow users to construct their own SQL statement and run UPDATE SQL statements? I never do that and I see no reason why I should. User does NOT WANT TO SEE RowID numbers. Why not? User wants to see this output : +-+-+-+-+ | Name| Company| Price | Warranty | | | | | | +-+-+-+-+ | PC 1000 | MyCom Inc. | 1000.00| 1 year| --8-- | PC AR4| SPCom Inc. | 1400.00| 4 year| +-+-+-+-+ I know there are ways to bypass this problem, but it involves much more coding in my Delphi applications on my Client side. You can do this in the after open event, I think. So my question is, Is there a way to hide a field from select statements. Of course, if my user wrote SELECT RowId, Name, Company, Price, Warranty FROM product he would get the RowId in his query output. How do you expect your users to update rows without an unique ID? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to READ/WRITE directly on MyISAM data files ?
[snip] | 4 | PC AR3| SPCom Inc. | 1200.00| 2 year| | 4 | PC AR3| SPCom Inc. | 1300.00| 3 year| | 4 | PC AR4| SPCom Inc. | 1400.00| 4 year| +-+-+-+-+-+ From My Server Side Application (C code) I can Update my Rows using my RowID. Example : UPDATE TABLE product SET Price=2000.00 WHERE RowID=3; [/snip] Yes, but if you use RowID 4 you'll be updating multiple rows [snip] But For My Client Side Applications : I know there are ways to bypass this problem, but it involves much more coding in my Delphi applications on my Client side. [/snip] So? [snip] So my question is, Is there a way to hide a field from select statements. Of course, if my user wrote SELECT RowId, Name, Company, Price, Warranty FROM product he would get the RowId in his query output. [/snip] Does the user know all of the columns? If so, and he chose to see them that would be his choice, no? Really, it is just bad database design. Each row should have a unique identifier. What is the big deal about the user seeing the RowID? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to READ/WRITE directly on MyISAM data files ?
Just wanted to know if it was possible to hide fields for whatever reason. Judging by your response, the answer is no. Therefore, I will look at other alternatives. Thanks, -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: 11 décembre, 2003 09:09 To: Stéphane Bischoff; MySQL (E-mail) Subject: RE: How to READ/WRITE directly on MyISAM data files ? [snip] | 4 | PC AR3| SPCom Inc. | 1200.00| 2 year| | 4 | PC AR3| SPCom Inc. | 1300.00| 3 year| | 4 | PC AR4| SPCom Inc. | 1400.00| 4 year| +-+-+-+-+-+ From My Server Side Application (C code) I can Update my Rows using my RowID. Example : UPDATE TABLE product SET Price=2000.00 WHERE RowID=3; [/snip] Yes, but if you use RowID 4 you'll be updating multiple rows [snip] But For My Client Side Applications : I know there are ways to bypass this problem, but it involves much more coding in my Delphi applications on my Client side. [/snip] So? [snip] So my question is, Is there a way to hide a field from select statements. Of course, if my user wrote SELECT RowId, Name, Company, Price, Warranty FROM product he would get the RowId in his query output. [/snip] Does the user know all of the columns? If so, and he chose to see them that would be his choice, no? Really, it is just bad database design. Each row should have a unique identifier. What is the big deal about the user seeing the RowID? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to READ/WRITE directly on MyISAM data files ?
So if I follow you, you allow your clients to execute direct sql on the database, both select and update, but _they_ simply don't want to see the row id data? If that's the case, then too bad for them if they are capable of writing sql, then they have to handle the result, or omit the field from the query. The only other option is for you to truss up your application interface, so that you give them an interface that shows just the fields _you_ want them to see. They can't have their cake and eat it too. -Original Message- From: Stéphane Bischoff [mailto:[EMAIL PROTECTED] Sent: Thursday, December 11, 2003 9:00 AM To: 'Jay Blanchard'; MySQL (E-mail) Subject: RE: How to READ/WRITE directly on MyISAM data files ? Hello, Here's an example : I have a table named Product. TABLE : PRODUCT +-+-+-+-+- + |RowID | Name| Company| Price | Warranty | | | | | | | +-+-+-+-+- + | 1 | PC 1000 | MyCom Inc. | 1000.00| 1 year| | 2 | PC 1000 | MyCom Inc. | 1200.00| 2 year| | 3 | PC 1000 | MyCom Inc. | 1300.00| 3 year| | 4 | PC 2000 | MyCom Inc. | 1200.00| 1 year| | 4 | PC 2000 | MyCom Inc. | 1300.00| 2 year| | 4 | PC 2000 | MyCom Inc. | 1400.00| 3 year| | 4 | PC 3000 | MyCom Inc. | 1500.00| 1 year| | 4 | PC 3000 | MyCom Inc. | 1600.00| 2 year| | 4 | PC 3000 | MyCom Inc. | 1700.00| 3 year| | 4 | PC AR3| SPCom Inc. | 1200.00| 2 year| | 4 | PC AR3| SPCom Inc. | 1300.00| 3 year| | 4 | PC AR4| SPCom Inc. | 1400.00| 4 year| +-+-+-+-+- + From My Server Side Application (C code) I can Update my Rows using my RowID. Example : UPDATE TABLE product SET Price=2000.00 WHERE RowID=3; But For My Client Side Applications : User logs in my Client app. User types in SELECT * FROM product. User does NOT WANT TO SEE RowID numbers. User wants to see this output : +-+-+-+-+ | Name| Company| Price | Warranty | | | | | | +-+-+-+-+ | PC 1000 | MyCom Inc. | 1000.00| 1 year| | PC 1000 | MyCom Inc. | 1200.00| 2 year| | PC 1000 | MyCom Inc. | 1300.00| 3 year| | PC 2000 | MyCom Inc. | 1200.00| 1 year| | PC 2000 | MyCom Inc. | 1300.00| 2 year| | PC 2000 | MyCom Inc. | 1400.00| 3 year| | PC 3000 | MyCom Inc. | 1500.00| 1 year| | PC 3000 | MyCom Inc. | 1600.00| 2 year| | PC 3000 | MyCom Inc. | 1700.00| 3 year| | PC AR3| SPCom Inc. | 1200.00| 2 year| | PC AR3| SPCom Inc. | 1300.00| 3 year| | PC AR4| SPCom Inc. | 1400.00| 4 year| +-+-+-+-+ I know there are ways to bypass this problem, but it involves much more coding in my Delphi applications on my Client side. So my question is, Is there a way to hide a field from select statements. Of course, if my user wrote SELECT RowId, Name, Company, Price, Warranty FROM product he would get the RowId in his query output. Best Regards, Stéphane. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: 11 décembre, 2003 08:49 To: Stéphane Bischoff; MySQL (E-mail) Subject: RE: How to READ/WRITE directly on MyISAM data files ? [snip] Thank you, but I already know the basics of SQL SELECT statements. What I am trying to say is, if a User writes a SELECT clause, I do not want him to see the RowID field. I do not want him to write a long SELECT statement, especially if my table has 20 FIELDS or more. (Can you imagine the user writing these queries all the time). I want him to be able to write SELECT * FROM ATABLE. [/snip] Well, that pretty much misses the point then, doesn't it? Are your records not unique? There is no way that you can write an update statement that would perform the operation on the proper record? Can you show us a bit of the table? With more information we can help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
RE: How to READ/WRITE directly on MyISAM data files ?
There's no way to hide a row. However, I can think of a zany solution. Update your tables using a hash of all the data in the row. That way you don't need an id field. Fair warning: this is not a GOOD solution but it does address the problem. Matt -Original Message- From: Stéphane Bischoff [mailto:[EMAIL PROTECTED] Sent: Thursday, December 11, 2003 9:00 AM To: 'Jay Blanchard'; MySQL (E-mail) Subject: RE: How to READ/WRITE directly on MyISAM data files ? Hello, Here's an example : I have a table named Product. TABLE : PRODUCT +-+-+-+-+-+ |RowID| Name| Company| Price | Warranty | | | | | | | +-+-+-+-+-+ | 1 | PC 1000 | MyCom Inc. | 1000.00| 1 year| | 2 | PC 1000 | MyCom Inc. | 1200.00| 2 year| | 3 | PC 1000 | MyCom Inc. | 1300.00| 3 year| | 4 | PC 2000 | MyCom Inc. | 1200.00| 1 year| | 4 | PC 2000 | MyCom Inc. | 1300.00| 2 year| | 4 | PC 2000 | MyCom Inc. | 1400.00| 3 year| | 4 | PC 3000 | MyCom Inc. | 1500.00| 1 year| | 4 | PC 3000 | MyCom Inc. | 1600.00| 2 year| | 4 | PC 3000 | MyCom Inc. | 1700.00| 3 year| | 4 | PC AR3| SPCom Inc. | 1200.00| 2 year| | 4 | PC AR3| SPCom Inc. | 1300.00| 3 year| | 4 | PC AR4| SPCom Inc. | 1400.00| 4 year| +-+-+-+-+-+ From My Server Side Application (C code) I can Update my Rows using my RowID. Example : UPDATE TABLE product SET Price=2000.00 WHERE RowID=3; But For My Client Side Applications : User logs in my Client app. User types in SELECT * FROM product. User does NOT WANT TO SEE RowID numbers. User wants to see this output : +-+-+-+-+ | Name| Company| Price | Warranty | | | | | | +-+-+-+-+ | PC 1000 | MyCom Inc. | 1000.00| 1 year| | PC 1000 | MyCom Inc. | 1200.00| 2 year| | PC 1000 | MyCom Inc. | 1300.00| 3 year| | PC 2000 | MyCom Inc. | 1200.00| 1 year| | PC 2000 | MyCom Inc. | 1300.00| 2 year| | PC 2000 | MyCom Inc. | 1400.00| 3 year| | PC 3000 | MyCom Inc. | 1500.00| 1 year| | PC 3000 | MyCom Inc. | 1600.00| 2 year| | PC 3000 | MyCom Inc. | 1700.00| 3 year| | PC AR3| SPCom Inc. | 1200.00| 2 year| | PC AR3| SPCom Inc. | 1300.00| 3 year| | PC AR4| SPCom Inc. | 1400.00| 4 year| +-+-+-+-+ I know there are ways to bypass this problem, but it involves much more coding in my Delphi applications on my Client side. So my question is, Is there a way to hide a field from select statements. Of course, if my user wrote SELECT RowId, Name, Company, Price, Warranty FROM product he would get the RowId in his query output. Best Regards, Stéphane. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: 11 décembre, 2003 08:49 To: Stéphane Bischoff; MySQL (E-mail) Subject: RE: How to READ/WRITE directly on MyISAM data files ? [snip] Thank you, but I already know the basics of SQL SELECT statements. What I am trying to say is, if a User writes a SELECT clause, I do not want him to see the RowID field. I do not want him to write a long SELECT statement, especially if my table has 20 FIELDS or more. (Can you imagine the user writing these queries all the time). I want him to be able to write SELECT * FROM ATABLE. [/snip] Well, that pretty much misses the point then, doesn't it? Are your records not unique? There is no way that you can write an update statement that would perform the operation on the proper record? Can you show us a bit of the table? With more information we can help. -- 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]
using (hard?) links for tables
hi there!
i have two databases on the same server, and one of the tables ('users')
should be the same in both databases. since this is very specific to
this one server, and other servers running similar databases don't need
that functionality, i'm looking for the easiest way to do this, so i was
wondering whether mysql is smart enough not to couse any mayor mess if i
simply replace the users.* file in one of the datbase's directory with
hardlinks to the other databses files for that table.
anyone know whether this works, and if not, what would be the easiest
way of keeping two tables in differnt databases synced? a cronjob, maybe
(it's not THAT time-critiva).
thanks,
M.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Embedded MySQL example with g++?
Is there any example of makefile for g++ that links libmysqld? So far I can link libmysqld only with gcc-based programs, but g++ gives lots of unresolved symbols (like __pure_virtual etc.). thanks, Mirza Hadzic -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database field length standards
Is anyone aware of international standards for field lengths for commonly used database fields? I.e Firstname Lastname Address City State Etc The basic contact information for a user...? If anyone knows of a standard, please email me a link or reference to it. Thanks :) -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL configure script errors
I am trying to compile MySQL on a machine. The OS is linux. I am following the directions I found on a website. http://www.brtnet.org/linux/lampssl.htm. The source files are in the directory /usr/local/src/mysql-4.0.16. When I run the ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/date --disable-maintainer-mode --with-mysqld-user=mysql --with-unix-socket-path=/tmp/mysql.sock --without-comment --with-low-memory The script fails with the message configure: error: No termcap/curses library found. I confirmed that the relevant packages had been installed after looking on the web to see what was missing. My question is what is the name of the library and is the script complaining that the library is not in the directory /curses/termcap? Any help would be greatly appreciated. Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locks dont lock
Kim G. Pedersen [EMAIL PROTECTED] wrote: I try in my program to make a lock. I try to prevent another session to read and write from the whole table I tried both LOCK TABLES pstockx WRITE and LOCK TABLES pstockx READ but no success. I put in a breakpoint in my program to be sure the lock are held and then I try to make a select from another session. It dont lock me up :( What am I doing wrong ,,is it some settings on server ?? 8128 Query LOCK TABLES pstockx READ 031211 0:22:058133 Connect [EMAIL PROTECTED] on elprint 8133 Query SET SQL_BIG_SELECTS=1 8133 Query select amount from pstockx where productID = 82846 8133 Query EXPLAIN select amount from pstockx where pro ductID = 1 031211 0:23:068128 Query SHOW COLUMNS FROM pstockx 8128 Query SHOW INDEX FROM pstockx 8128 Query select amount from pstockx where productID=8 2846 LOCK TABLE .. READ allows other threads read from the table. As for LOCK TABLE .. WRITE .. do you use QUERY CACHE? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locks dont lock
Kim, I use WRITE locks extensively with MySQL 4 with a large distributed application. Your example logs indicate that your test application obtained a READ lock, which will not block other select requests from accessing the table. Only a WRITE lock will cause subsequent select requests to be held until the lock is released. If your application's break point actually closes the database connection, then the WRITE lock is released, and subsequent selects will be processed. In my application, I have several dozen systems all working on data that it reads in chunks from the MySQL backend. In my application, I do the following: LOCK TABLES the_big_backend_table WRITE (select my data then flag the selected rows as pending) UNLOCK TABLES Over the last year or so, this has worked perfectly. It keeps all the other systems from selecting duplicate data, just as you are trying to do, I assume. If you are doing a JOIN with a table you are trying to lock, you must also specific read locks for each table in your JOIN, if I recall correctly, or else the lock will not hold. I have to do this in another location: LOCK TABLES bigtable AS a WRITE, smalltable AS b READ Again, all of this locking and releasing should have some degree of concurrence. You cannot take a lock, close the database connection, and expect the lock to hold. Your application requesting the lock must keep the session active. Open two command line instances of MySQL to the same database. In one, instruct the database to lock your first table for WRITE access. Then, go to your second instance, and try to select, without shutting down the first or closing it. You will find that you cannot select data from the table as the first MySQL client has locked it for writing. Release the lock with UNLOCK TABLES on the first client, then you will see your second client has noticed the lock release, and has read the data. -- R Hello I try in my program to make a lock. I try to prevent another session to read and write from the whole table I tried both LOCK TABLES pstockx WRITE and LOCK TABLES pstockx READ but no success. I put in a breakpoint in my program to be sure the lock are held and then I try to make a select from another session. It dont lock me up :( What am I doing wrong ,,is it some settings on server ?? 8128 Query LOCK TABLES pstockx READ 031211 0:22:058133 Connect [EMAIL PROTECTED] on elprint 8133 Query SET SQL_BIG_SELECTS=1 8133 Query select amount from pstockx where productID = 82846 8133 Query EXPLAIN select amount from pstockx where pro ductID = 1 031211 0:23:068128 Query SHOW COLUMNS FROM pstockx 8128 Query SHOW INDEX FROM pstockx 8128 Query select amount from pstockx where productID=8 2846 TIA Kim G. Pedersen macaos/elprint Development +45 35373808 -- 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]
Virtual IO and MySQL FS
Hi all! one simple question: what is virtual IO and MySQL FS options in configure scripts used for? I mean what can i do with this babes tuned on? Best Regards! :) -- .. * _ _ __ __ .. * \ \ \ | | __ \ /\ | | || Victor E Medina M * \ \ \ | |__ | |__) / \ | | || Linux - Java - MySQL * | __| | ___/ /\ \ | | || Dpto. Sistemas - Ferreteria EPA * / / / | || | / \|_| || www.superferreteria.com.ve * /_/_/ |__|_| /_/\_(_) || [EMAIL PROTECTED] * || geek by nature - linux by choice .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Field Name whitespace via MyODBC
I can just alter table to rename the fields right? -Original Message- From: Patrick Sherrill [mailto:[EMAIL PROTECTED] Sent: Thursday, December 11, 2003 5:27 AM To: Brian Duke; [EMAIL PROTECTED] Subject: Re: Field Name whitespace via MyODBC Brian, I think you are going to need to rename your fields/columns. If you are unable to rename them in mysql then you will probably need to rename them in your jet database with Access and re-import them. Your column names should be literals not wrapped in graves or quotes and should contain no whitespace. Whitespace is frequently used as a delimiter. I also avoid any characters other than alphanumeric and the occasional underscore character in field/column names . It helps avoid OS idiosyncrasies and simplifies naming conventions. I hope this helps. Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP 825 SE 47th Terrace Cape Coral, FL 33904 - Original Message - From: Brian Duke [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 11, 2003 2:50 AM Subject: Field Name whitespace via MyODBC On the windows machine I have Acess2003. I installed the MyODBC to push the data into the FreeBSD MySQL server. The connection worked like a dream. Kudos to the MyODBC team. The table that the connection created included field names that have spaces in the name. I'm trying to access the data thru my PHP scripts. I tried this: $sql7 = SELECT LERG_7_SHA.LATA ,LERG_7_SHA.SWITCH , `LERG_7_SHA.SHA INDICATOR` , `LERG_7_SHA.H ORG B TDM` FROM LERG_7_SHA WHERE ( LERG_7_SHA.SWITCH = \$npa\ AND `LERG_7_SHA.SHA INDICATOR` = \$nxx\ ) LIMIT 0, 30; And $sql7 = SELECT LERG_7_SHA.LATA ,LERG_7_SHA.SWITCH , \'LERG_7_SHA.SHA INDICATOR\' , \'LERG_7_SHA.H ORG B TDM\' ,\'LERG_7_SHA.H ORG C TDM\' , LERG_7_SHA.HOST , LERG_7_SHA.OCN , LERG_7_SHA.AOCN FROM LERG_7_SHA WHERE (LERG_7_SHA.SWITCH = \$npa\ AND \'LERG_7_SHA.SHA_INDICATOR\' = \$nxx\ ) LIMIT 0, 30; Both do not work. The query breaks down where the backticks are or tries to add the literal string of 'LERG_7_SHA.SHA INDICATOR' in the result data. I have tried with single quotes and double quotes. I can issue this command on the mysql command line and it does work. Can someone help me syntax this line correct? The script.php and the database are on the same FreeBSD machine. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible MyODBC Bug?
First, is there a separate mailing list for MyODBC issues? I have Lotus Script code that uses odbc in an attempt to write data to a MySQL table. I can't get even the very first record inserted because I consistently get the following error: Field mapping failed due to a missing field, Connector 'odbc2' I have examined the MySQL table, the Lotus Notes database/connection objects, and the actual database string. Everything matches perfectly, including number of fields, name of fields, the two non-nullable fields, etc. I can send all 200 lines of code if any one is familiar with LotusScript so as to verify that I'm doing this right; but I have a strong suspicion that this is an ODBC issue. Thanks. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Crashes / Overloads on OpenBSD 3.3 / 3.4
Description: I'm using a standard OpenBSD Distribution (Versions 3.3 and 3.4) I'm running Apache 2.0.48, PHP 4.3 and MySQL 4.0.14 - 4.0.16. The Problem occurs every time I do many querys at one, for example when I'm using the Woltlab Burning Board or phpMyAdmin. I use the same versions on Linux and there are no probs. Sometimes I can see these lines on the screen but not always Number of processes running now: 0 031210 21:21:00 mysqld restarted How-To-Repeat: Just open the webbrowser and create some databases and tables and suddenly you can't get a connect to mysql. You can still see the process in the process list but it doesn't respond. Fix: Submitter-Id: submitter ID Originator:Markus Ullmann [EMAIL PROTECTED] Organization: MySQL support: none Synopsis: Crashes / Overloads on OpenBSD 3.3 / 3.4 Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.16-standard (Official MySQL-standard binary) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: OpenBSD digital-server.de 3.4 GENERIC#60 i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-unknown-openbsd3.4/2.95.3/specs gcc version 2.95.3 20010125 (prerelease, propolice) Compilation info: CC='gcc' CFLAGS='-Wbounded' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: -r--r--r-- 1 root bin 1223580 Dec 6 15:08 /usr/lib/libc.a -r--r--r-- 1 root bin 656756 Dec 6 15:08 /usr/lib/libc.so.30.1 Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-embedded-server' '--with-innodb' 'CFLAGS=-Wbounded' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple languages in the same column
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Puny Sen wrote: Hi All, I'd like to use the same column to store content from multiple languages (English, German, French, Japanese). Here is my understanding of the options available. In MySQL 4.0: - UTF-8 is not currently available as a charset True. - we can connect to the database using useUnicode=truecharacterEncoding=UTF-8 in the connection string. True. - this enables us to store, search and retrieve Unicode content from the column, as long as we always use JDBC with the above connection string, to interact with the db. True. - sorting will not work on the column True. In MySQL 4.1: - UTF-8 is available as a charset Yes, but remember, UTF-8 is an _encoding_ that can store many different character sets, there is a difference. - We still neet to connect to the database using the above connection string (doesn't seem to work otherwise) Unless you set your database's default character set to UTF-8, then yes, you do still need to have 'useUnicode=truecharacterEncoding=UTF-8' in your URL, which tells the driver that you will be mixing character sets in your queries (so encode them as UTF-8), and also tells the server to expect your queries to be encoded in UTF-8 (the driver does a 'SET NAMES UTF-8' on connect in this case). - sorting will work, but only using the general utf8 collation (may not work for Japanese?). More collations will be available soon. True. If you know the column charset and collation that you want to use, you should be able to use CAST on it to get it to a different charset, and the sort using a compatible collation. - [can we cast/convert to a different charset (sjis) and use its collation for sorting? (performance is not really an issue)] I guess I just answered that above :) Please let me know if any of these assumptions are incorrect. They seem to be correct. Please let me know if you run into any issues or inconsistencies with these assumptions, because the combination of Unicode and UTF-8 support in the JDBC driver and the server is new (and can in sometimes be complex, due to the flexibility it offers), and we'd like to get any kinks worked out ASAP! -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/2J6ItvXNTca6JD8RAp3BAJ9sWug9JcCeqWrDGzg6XGc2bUTaWwCgxcap SRKikpcyoo0St5ClUF9G4Dw= =QaD8 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql error
Alex E.Wintermann [EMAIL PROTECTED] wrote:
MySQL 4.1.0-alpha-max-nt
phpMyAdmin 2.5.4
/* BEGIN DUMP */
CREATE TABLE `sp_tovar_vid` (
`id` int(11) NOT NULL auto_increment,
`id_tovar_vid` int(11) NOT NULL default '0',
`name` varchar(100) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
UNIQUE KEY `id` (`id`)
) TYPE=InnoDB CHARSET=cp1251;
INSERT INTO `sp_tovar_vid` VALUES (1, 0, 'string 1', '');
INSERT INTO `sp_tovar_vid` VALUES (2, 1, 'string 2', '');
INSERT INTO `sp_tovar_vid` VALUES (3, 2, 'string 3', '');
INSERT INTO `sp_tovar_vid` VALUES (4, 0, 'string 4', '');
INSERT INTO `sp_tovar_vid` (`id`,`id_tovar_vid`,`name`,`description`) VALUES
('','0','','');
UPDATE `sp_tovar_vid` SET `id` = '0' WHERE `id` = LAST_INSERT_ID() LIMIT 1 ;
/* END DUMP */
/* BEGIN QUERY */
SELECT t1.`id`,
CONCAT(if(t3.`name`='','',CONCAT(t3.`name`,' -
')),if(t2.`name`='','',CONCAT(t2.`name`,' - ')),t1.`name`) AS col1,
t1.`description`
FROM`sp_tovar_vid` AS t1,
`sp_tovar_vid` AS t2,
`sp_tovar_vid` AS t3
WHERE t1.`id_tovar_vid`=t2.`id` AND
t2.`id_tovar_vid`=t3.`id` AND
col1'string'
ORDER BY col1;
/* END QUERY */
QUESTION: why ERROR caused: #1054 - Unknown column 'col1' in 'where clause'
You can't use column alias in the WHERE clause. Use HAVING instead:
http://www.mysql.com/doc/en/Problems_with_alias.html
result wanted
++--+-+
| id | col1 | description |
++--+-+
| 1 | string 1 | |
| 2 | string 1 - string 2 | |
| 3 | string 1 - string 2 - string 3 | |
| 4 | string 4 | |
++--+-+
4 rows in set (0.00 sec)
/**/
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using (hard?) links for tables
It's not recommended since it might cause file locking issues internally.
how 'maybe' would that be? (that table almost never get written to, but
when it is written to, the other one should be updated AFAP. both tables
would almost never be written to at the same time)
Why not just set up a replication between those two servers and replicate
the table?
becasue they are on the same machine, and IMHO replication would be a
bit of an overkill for that, but i'd be happy to be corrected.
thanks,
M.
otherwise copying should work, but you'd still have some locking issues at
the time of copying.
On Thu, 11 Dec 2003, Moritz von Schweinitz wrote:
hi there!
i have two databases on the same server, and one of the tables ('users')
should be the same in both databases. since this is very specific to
this one server, and other servers running similar databases don't need
that functionality, i'm looking for the easiest way to do this, so i was
wondering whether mysql is smart enough not to couse any mayor mess if i
simply replace the users.* file in one of the datbase's directory with
hardlinks to the other databses files for that table.
anyone know whether this works, and if not, what would be the easiest
way of keeping two tables in differnt databases synced? a cronjob, maybe
(it's not THAT time-critiva).
thanks,
M.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[ MySQL: Problems with Innodb ]
Hi, I have a problem and would like a help. I copied a mysql datadir to another place (backup of datadir), but when start up mysql server with datadir it's crash. See below, trace of logfile: 031211 13:28:01 mysqld started031211 13:28:01 InnoDB: Database was not shut down normally.InnoDB: Starting recovery from log files...InnoDB: Starting log scan based on checkpoint atInnoDB: log sequence number 7 1021600303InnoDB: Doing recovery: scanned up to log sequence number 7 1021600256InnoDB: Error: trying to access a stray pointer c0b7bff8InnoDB: buf pool start is at 40388000, number of pages 512031211 13:28:01 InnoDB: Assertion failure in thread 16384 in file ../../innobase/include/buf0buf.ic line 284InnoDB: We intentionally generate a memory trap.InnoDB: Send a detailed bug report to [EMAIL PROTECTED]mysqld got signal 11;This could be because you hit a bug. It is also possible that this binaryor one of the libraries it was linked against is corrupt, improperly built,or misconfigured. This error can also be caused by malfunctioning hardware.We will try our best to scrape up some info that will hopefully help diagnosethe problem, but since we have already crashed, something is definitely wrongand this may fail. key_buffer_size=8388600read_buffer_size=131072sort_buffer_size=137max_used_connections=0max_connections=100threads_connected=0It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 21005 Kbytes of memoryHope that's ok; if not, decrease some variables in the equation. thd=0x83322c0Attempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly wrong...Bogus stack limit or frame pointer, fp=0xbfffd368, stack_bottom=0x706f6e6d, thread_stack=196608, aborting backtrace.Trying to get some variables.Some pointers may be invalid and cause the dump to abort...thd-query at 0x6c6b6a69 is invalid pointerthd-thread_id=0 Successfully dumped variables, if you ran with --log, take a look at thedetails of what thread 0 did to cause the crash. In some cases of reallybad corruption, the values shown above may be invalid. The manual page at http://www.mysql.com/doc/C/r/Crashing.html containsinformation that should help you find out what is causing the crash.031211 13:28:01 mysqld ended Server Installed: mysql-4.0.12 Help me !!!. Osvâneo Ap. Ferreira http://www.ig.com.br
RE: using (hard?) links for tables
What if you were to make a 3rd database, containing shared elements, such as your user
table (I presume we're not talking the MySQL system user table) and then have
necessary permissions granted between your other database users to read that table
jointly, as you can query cross databases...
-Original Message-
From: Moritz von Schweinitz [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 11, 2003 9:26 AM
To: [EMAIL PROTECTED]
Subject: using (hard?) links for tables
hi there!
i have two databases on the same server, and one of the
tables ('users')
should be the same in both databases. since this is very specific to
this one server, and other servers running similar databases
don't need
that functionality, i'm looking for the easiest way to do
this, so i was
wondering whether mysql is smart enough not to couse any
mayor mess if i
simply replace the users.* file in one of the datbase's
directory with
hardlinks to the other databses files for that table.
anyone know whether this works, and if not, what would be the easiest
way of keeping two tables in differnt databases synced? a
cronjob, maybe
(it's not THAT time-critiva).
thanks,
M.
--
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]
Login Problems
Does anyone know why MySQL would only be allowing users with null passwords to connect? It's a fresh install of the server after deinstalling 4.1-ALPHA and installing the production release of 4.0.6. Conor. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Login Problems
By default mySQL comes with a set of users that are allowed to login locally without password. You should create a normal-superuser account, and delete all others, and use this user to administer the rest of the data base and use it to create all others accounts that you might need. To create a new full-blown account: grant all privileges on *.* to your_user@% identified by 'password' with grant option; Best Regards! -- .. * _ _ __ __ .. * \ \ \ | | __ \ /\ | | || Victor E Medina M * \ \ \ | |__ | |__) / \ | | || Linux - Java - MySQL * | __| | ___/ /\ \ | | || Dpto. Sistemas - Ferreteria EPA * / / / | || | / \|_| || www.superferreteria.com.ve * /_/_/ |__|_| /_/\_(_) || [EMAIL PROTECTED] * || geek by nature - linux by choice .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Login Problems
I have already created extra user accounts and none of them can connect once a password has been set. -Original Message- From: Victor Medina [mailto:[EMAIL PROTECTED] Sent: 11 December 2003 18:45 To: Schrodinger Cc: 'MySQL General Lists' Subject: Re: Login Problems By default mySQL comes with a set of users that are allowed to login locally without password. You should create a normal-superuser account, and delete all others, and use this user to administer the rest of the data base and use it to create all others accounts that you might need. To create a new full-blown account: grant all privileges on *.* to your_user@% identified by 'password' with grant option; Best Regards! -- .. * _ _ __ __ .. * \ \ \ | | __ \ /\ | | || Victor E Medina M * \ \ \ | |__ | |__) / \ | | || Linux - Java - MySQL * | __| | ___/ /\ \ | | || Dpto. Sistemas - Ferreteria EPA * / / / | || | / \|_| || www.superferreteria.com.ve * /_/_/ |__|_| /_/\_(_) || [EMAIL PROTECTED] * || geek by nature - linux by choice .. -- 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: Login Problems
[snip] I have already created extra user accounts and none of them can connect once a password has been set. [/snip] Have you FLUSHED the privileges? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb multiple tablespaces benchmark
Hi all, specially to Heikki. Its really amazing that multiple tablespaces are available before 2004, congratulations to Innodb Oy Inc. Right now im migrating from 4.1.0 to 4.1.1 but im figuring out if there is a downgrade in performance in order to use multiple tablespaces, obviously it must be one because handling several files adds an overhead but it something to worry about ? I thougt that these tablespaces would be for each database and not for each table, this is possible ?, factible ?, useful ?, its scheduled ?. Thanx a lot and again some claps for innodb :) Carlos _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1006: Can't create database 'X'. (errno: 13)
At 14:06 +0100 12/11/03, N L wrote: Hello I am having difficulties: I am working on Mac OSX 10.2.8 with mysql 4. I needed to change the data directory of MySQL from its original place (where the application have to be and not the databases because there is not enought room) to an other volume of my computer. I made a symlink from data to data in the other volume and not anymore as it was to var in the same volume before. I copied If the original data directory was named var, then the symlink also should be named var, even if it now points to a directory named data. all files and directories of var in this new directory -data-. I changed with mysql mysql all this file and directories (group and owner). And I made : create database X; ERROR 1006: Can't create database 'X'. (errno: 13) I hope what I explain is clear! What did I forget ? Do I have to put also var in this new other directory? Do I have to reinstall completly MySQL because I corrupted to much this application? I am lost. Could anyone help me? Thanks in advance LN _ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.1.0
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 9 Dec 2003, Mark Needleman wrote: Is there anyway to still get the Solaris 9 64 bit binary for version 4.1.0 - now that 4.1.1 alpha appears to be out the 4.1.0 binaries don't seem to be available on the download page Yes, older 4.1 releases are available from http://downloads.mysql.com/archives.php?p=mysql-4.1 Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE/2M4mSVDhKrJykfIRAkL8AJ9cHefNJM7cKBUSK8MmELIyLjpXFACfSadW gwXGVN5OartlkpHuc8rhpE0= =+g22 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UDF on AMD64
Hi, I just recently built myself a dual Opteron system and installed mysql for AMD64 on it. I previously ran my databases on regular 32 bit systems and had my UDF working fine. The UDF is written in C. Before I try and install the UDF on the new system should I recompile it using AMD64 libraries? Thanks, Ollie --- Ollie Gallardo Support Services Inc 2 Professional Dr Ste 212 Gaithersburg MD 20879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF on AMD64
In the last episode (Dec 11), Ollie Gallardo said: I just recently built myself a dual Opteron system and installed mysql for AMD64 on it. I previously ran my databases on regular 32 bit systems and had my UDF working fine. The UDF is written in C. Before I try and install the UDF on the new system should I recompile it using AMD64 libraries? Yes; UDFs are native machine binaries, and I doubt a 32-bit UDF .so would even load into a 64-bit mysqld. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF on AMD64
Thanks Dan. I will get to it then. Dan Nelson said: In the last episode (Dec 11), Ollie Gallardo said: I just recently built myself a dual Opteron system and installed mysql for AMD64 on it. I previously ran my databases on regular 32 bit systems and had my UDF working fine. The UDF is written in C. Before I try and install the UDF on the new system should I recompile it using AMD64 libraries? Yes; UDFs are native machine binaries, and I doubt a 32-bit UDF .so would even load into a 64-bit mysqld. -- Dan Nelson [EMAIL PROTECTED] --- Ollie Gallardo Support Services Inc 2 Professional Dr Ste 212 Gaithersburg MD 20879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using (hard?) links for tables
I agree with Dan.
We looked into this years ago.
We have MANY machines with many tables used this way.
It is easy when you realise that one SQL command can access multiple
databases, using db1.tablea ... db2.tableb syntax.
Stephen
- Original Message -
From: Dan Greene [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, December 12, 2003 4:54 AM
Subject: RE: using (hard?) links for tables
What if you were to make a 3rd database, containing shared elements, such as
your user table (I presume we're not talking the MySQL system user
table) and then have necessary permissions granted between your other
database users to read that table jointly, as you can query cross
databases...
-Original Message-
From: Moritz von Schweinitz [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 11, 2003 9:26 AM
To: [EMAIL PROTECTED]
Subject: using (hard?) links for tables
hi there!
i have two databases on the same server, and one of the
tables ('users')
should be the same in both databases. since this is very specific to
this one server, and other servers running similar databases
don't need
that functionality, i'm looking for the easiest way to do
this, so i was
wondering whether mysql is smart enough not to couse any
mayor mess if i
simply replace the users.* file in one of the datbase's
directory with
hardlinks to the other databses files for that table.
anyone know whether this works, and if not, what would be the easiest
way of keeping two tables in differnt databases synced? a
cronjob, maybe
(it's not THAT time-critiva).
thanks,
M.
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [RE-REPOST] Openssl support not activated?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Greg G wrote: Mark Matthews wrote: Greg G wrote: I'm still having trouble figuring this out. Please help! I compiled MySQL 4.1.1 --with-openssl --with-vio and when I look at the variables with mysqladmin, has_openssl is set to NO. I've got OpenSSL 0.9.7c installed. I'm seeing this on both Solaris and Debian hosts. I'm not seeing any errors during the configure or compile phases. What could have happened to cause this, and how can I fix it? Did you create certificates and configure mysqld and the client libraries to find them (as it says to in the manual :))? http://www.mysql.com/doc/en/Secure_connections.html According to 4.4.10.2 Requirements, have_openssl must be YES prior to setting up certificates. Is this in error? Sorry, I missed that small fact. What does the file include/my_config.h say about #define HAVE_OPENSSL (it's generated by 'configure'). If HAVE_OPENSSL is not defined, then, for some reason the configure script is not picking up that you want SSL support compiled in. To debug that, you'd need to post the output of configure and/or config.log. In config.log, you should see something like 'configure:22899: checking for OpenSSL', and towards the end of config.log, you should see a '#define HAVE_OPENSSL 1'. Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/2OsvtvXNTca6JD8RAmGMAJ4hNy1FpGYhiO+HavUaxY641yTnKQCfZETR GVnVqlBa/s4oySojtYwqWxw= =jfvG -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solved - median (was Re: mean/median/mode)
On Friday, December 5, Robert Citek wrote:
On Wednesday, December 3, 2003, at 06:27 PM, Robert Citek wrote:
How can I calculate the mean/median/mode from a set of data using SQL?
After a bit of googling, I found this link:
http://mysql.progen.com.tr/doc/en/Group_by_functions.html
and a few answers in the comments towards the bottom.
Below I've included a sample table and the solution I used to calculate
the median.
Regards,
- Robert
-
DROP TABLE IF EXISTS data;
CREATE TABLE data (
name char(1) default NULL,
val int(5) default NULL
) TYPE=MyISAM;
INSERT INTO data VALUES
('a',2), ('a',2), ('a',2), ('a',2), ('a',20),
('b',4), ('b',4), ('b',4), ('b',4), ('b',40);
CREATE TEMPORARY TABLE medians SELECT x.name, x.val
FROM data x, data y
WHERE x.name=y.name
GROUP BY x.name, x.val
HAVING ((COUNT(*)-(SUM(SIGN(1-SIGN(y.val-x.val) = floor((COUNT(*)
+1)/2)) and
((COUNT(*)-(SUM(SIGN(1+SIGN(y.val-x.val) = floor((COUNT(*) +1)/2));
SELECT name, AVG(val) AS median FROM medians group by name;
Output
+--++
| name | median |
+--++
| a| 2. |
| b| 4. |
+--++
Robert,
I don't believe this is correct. I think it only appears correct due to
the particular nature of your sample data. Try it with different data
to see what I mean. For example,
DROP TABLE IF EXISTS data;
CREATE TABLE data (name char(1) default NULL, val int default NULL);
INSERT INTO data
VALUES ('a',1), ('a',2), ('a',2), ('a',2), ('a',3), ('a',6),
('a',7),
('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20);
INSERT INTO data VALUES ('b',2), ('b',3), ('b',4), ('b',5);
DROP TABLE IF EXISTS medians;
CREATE TEMPORARY TABLE medians SELECT x.name, x.val
FROM data x, data y
WHERE x.name=y.name
GROUP BY x.name, x.val
HAVING
((COUNT(*)-(SUM(SIGN(1-SIGN(y.val-x.val) = floor((COUNT(*) +1)/2)) AND
((COUNT(*)-(SUM(SIGN(1+SIGN(y.val-x.val) = floor((COUNT(*) +1)/2));
SELECT name, AVG(val) AS median FROM medians group by name;
Output
+--++
| name | median |
+--++
| a| 6.5000 |
| b| 3.5000 |
+--++
As there are an odd number of values with name = 'a', we should choose
the middle value, 7, as the median, but we got 6.5. (I'm assuming we
agree that the definition of median is the middle value for N odd and
the average of the 2 middle values for N even.)
I tried to see if I could tweak your query to get the right result, but,
between the complexity of your HAVING clause and the wrinkle of getting
separate answers for each value in the name column, I just couldn't wrap
my head around it. So, I went looking and found a supposed solution at
http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html.
It is overly complex, purports to get an answer in a single select, and
is simply wrong in the case where there are an even number of values.
(The author also gives a definition of statistical median, as opposed to
financial median, which doesn't match my Intro Stats book.) But I
understood what it was trying to do and came up with the following:
To get the median of the values in a column:
DROP TABLE IF EXISTS medians;
CREATE TEMPORARY TABLE medians
SELECT x.val medians
FROM data x, data y
GROUP BY x.val
HAVING SUM(y.val = x.val) = COUNT(*)/2
AND SUM(y.val = x.val) = COUNT(*)/2;
SELECT AVG(medians) AS median FROM medians;
Output using my sample data given above
++
| median |
++
| 5. |
++
To get the median of the values in a column for each value in
another column:
DROP TABLE IF EXISTS medians;
CREATE TEMPORARY TABLE medians
SELECT x.name, x.val medians
FROM data x, data y
WHERE x.name=y.name
GROUP BY x.name, x.val
HAVING SUM(y.val = x.val) = COUNT(*)/2
AND SUM(y.val = x.val) = COUNT(*)/2;
SELECT name, AVG(medians) AS median FROM medians GROUP BY name;
Output using my sample data given above
+--++
| name | median |
+--++
| a| 7. |
| b| 3.5000 |
+--++
I've tested these with several different data sets, so I'm fairly
confident they are correct.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling UDF on Sparc Solaris
Has anyone had any success compiling udf_example.so (and getting it to work) on sparc solaris 8? I've tried it with the Sun Forte compilers, using the configure options mentioned in the on-line manual, and also with gcc 2.95.3. I was able to get it to work easily on Linux using gcc 3.3.1. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [RE-REPOST] Openssl support not activated?
Mark Matthews wrote: According to 4.4.10.2 Requirements, have_openssl must be YES prior to setting up certificates. Is this in error? Sorry, I missed that small fact. What does the file include/my_config.h say about #define HAVE_OPENSSL (it's generated by 'configure'). /* OpenSSL */ /* #undef HAVE_OPENSSL */ I can't find anywhere that HAVE_OPENSSL is being #define'd. There's a bunch of #ifdefs and #undefs, but not #define. If HAVE_OPENSSL is not defined, then, for some reason the configure script is not picking up that you want SSL support compiled in. To debug that, you'd need to post the output of configure and/or config.log. I did find these: ./aclocal.m4:AC_DEFINE(HAVE_OPENSSL) ./configure: #define HAVE_OPENSSL 1 In config.log, you should see something like 'configure:22899: checking for OpenSSL', and towards the end of config.log, you should see a '#define HAVE_OPENSSL 1'. Here's the relevant lines from config.log: $ ./configure --with-openssl=/usr/local/ssl --with-vio --prefix=/usr/local/mysql configure:20954: checking for OpenSSL configure:21080: result: no Hmm. That's probably the problem. :) However, I didn't get any messages when running configure. I've even specified --with-openssl-include and --with-openssl-lib since the --with-openssl directory doesn't seem to be getting using to determine those directories. I'm not sure what's happening. I have a lot of trouble debugging configure. -Greg G
RE: Login Problems
Yes I have tried doing that but with no joy. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: 11 December 2003 18:59 To: Schrodinger; Victor Medina Cc: MySQL General Lists Subject: RE: Login Problems [snip] I have already created extra user accounts and none of them can connect once a password has been set. [/snip] Have you FLUSHED the privileges? -- 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: Login Problems
I have removed all localhost user entries and all users should now be able to connect from any host. But when a user attempts to connect with a password they get the usual ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) -Original Message- From: Victor Medina [mailto:[EMAIL PROTECTED] Sent: 11 December 2003 18:56 To: Schrodinger Subject: RE: Login Problems You should delete most others since there is one user identified by something like this [EMAIL PROTECTED] that won't let you connect using password. If you delete those users, you'll be able to connect without problem On Thu, 2003-12-11 at 14:55, Schrodinger wrote: I have already created extra user accounts and none of them can connect once a password has been set. -Original Message- From: Victor Medina [mailto:[EMAIL PROTECTED] Sent: 11 December 2003 18:45 To: Schrodinger Cc: 'MySQL General Lists' Subject: Re: Login Problems By default mySQL comes with a set of users that are allowed to login locally without password. You should create a normal-superuser account, and delete all others, and use this user to administer the rest of the data base and use it to create all others accounts that you might need. To create a new full-blown account: grant all privileges on *.* to your_user@% identified by 'password' with grant option; Best Regards! -- .. * _ _ __ __ .. * \ \ \ | | __ \ /\ | | || Victor E Medina M * \ \ \ | |__ | |__) / \ | | || Linux - Java - MySQL * | __| | ___/ /\ \ | | || Dpto. Sistemas - Ferreteria EPA * / / / | || | / \|_| || www.superferreteria.com.ve * /_/_/ |__|_| /_/\_(_) || [EMAIL PROTECTED] * || geek by nature - linux by choice .. -- .. * _ _ __ __ .. * \ \ \ | | __ \ /\ | | || Victor E Medina M * \ \ \ | |__ | |__) / \ | | || Linux - Java - MySQL * | __| | ___/ /\ \ | | || Dpto. Sistemas - Ferreteria EPA * / / / | || | / \|_| || www.superferreteria.com.ve * /_/_/ |__|_| /_/\_(_) || [EMAIL PROTECTED] * || geek by nature - linux by choice .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Load Data Local Infile problem
I'm load a CSV file with five fields into mysql. It loads up fine. Problem comes in when I try to refresh the data with updates. I grab this file and convert it to CSV every 12 hours. The data shows past 24 hours only, so basically I'm trying to make a permanent archive. I tried creating a PRIMARY key of the first 4 fields to be unique to filter out duplicates. Problem is, when I have that key on, no new data gets written to the database, even clearly NEW records. Table is warlog, fields are time, attacker, coords, defender, status (obviously a game). A unique record would match the first four, or at the very least time and coords. Whenever I go to load data infile the second time around, no records get written if any indexes are present. If no indexes I get duplicates. If I put an index with the duplicates, I only get the first set of data with no updates. Is there something about primary keys I should know about? I've created this database with them and tried every combination, but I can't seem to get the update part to work. -- j http://decision.csl.uiuc.edu/~bambenek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--quote-names doesn't work for databasename
The subject says most of it. I'm moving mysql data from one machine to another. --quote-names works on tables and columns, but it seems to be skipping the database names. (`-` is the character throwing the restore off) I'm doing this on some older mysql boxes (not by much from mysql-server-3.23.54 - mysql-server-3.23.57 My next alternative (that I'm not fond of) is doing the same as the documentation recommends for replication... flush tables with read lock tar -czf blah.tar /var/db/mysql unlock tables. Thanks for any suggestions or help in advance, Gerald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF on AMD64
I'm back with another question. I tried to compile my UDF with the gcc on my Opteron system and I got errors. Errors: /usr/lib64/gcc-lib/amd64-mandrake-linux-gnu/3.3.1/../../../../lib64/crt1.o(.text+0x21): In function `_start': ../sysdeps/x86_64/elf/start.S:92: undefined reference to `main' /root/tmp/ccY5pYQZ.o(.text+0x1a9): In function `DIST': : undefined reference to `sin' /root/tmp/ccY5pYQZ.o(.text+0x1c6): In function `DIST': : undefined reference to `sin' /root/tmp/ccY5pYQZ.o(.text+0x1ec): In function `DIST': : undefined reference to `cos' /root/tmp/ccY5pYQZ.o(.text+0x209): In function `DIST': : undefined reference to `cos' /root/tmp/ccY5pYQZ.o(.text+0x249): In function `DIST': : undefined reference to `cos' /root/tmp/ccY5pYQZ.o(.text+0x29a): In function `DIST': : undefined reference to `sqrt' /root/tmp/ccY5pYQZ.o(.text+0x2a4): In function `DIST': : undefined reference to `atan' /root/tmp/ccY5pYQZ.o(.eh_frame+0x11): undefined reference to `__gxx_personality_v0' collect2: ld returned 1 exit status Here are my header includes. #ifdef STANDARD #include stdio.h #include string.h #else #include my_global.h #include my_sys.h #endif #include math.h #include mysql.h #include m_ctype.h #include m_string.h I can't paste the rest of the code. Sorry. Is there special coding I need to do for AMD64 when writing a UDF? Maybe someone can point me to a sample UDF that compiled on AMD64. Thanks, Ollie Dan Nelson said: In the last episode (Dec 11), Ollie Gallardo said: I just recently built myself a dual Opteron system and installed mysql for AMD64 on it. I previously ran my databases on regular 32 bit systems and had my UDF working fine. The UDF is written in C. Before I try and install the UDF on the new system should I recompile it using AMD64 libraries? Yes; UDFs are native machine binaries, and I doubt a 32-bit UDF .so would even load into a 64-bit mysqld. -- Dan Nelson [EMAIL PROTECTED] --- Ollie Gallardo Support Services Inc 2 Professional Dr Ste 212 Gaithersburg MD 20879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Login Problems
Schrodinger wrote: I have removed all localhost user entries and all users should now be able to connect from any host. But when a user attempts to connect with a password they get the usual ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) In an earlier message, you said you downgraded from mysql 4.1.? to 4.0.6 (Why not 4.0.16?). mysql 4.1 has a different password format in the user table than previous versions. Pre 4.1.x mysql uses 16 byte hashes to store passwords, 4.1.0 uses 45 byte hashes, and 4.1.1 (and up) uses 41 byte hashes. If you kept (or restored) the mysql user table you created in 4.1, you may now be comparing a 16 byte password hash to a 41 or 45 byte hash. As I understand it, the only way that could match is if both are blank. If this is the case for you, use GRANT to reset the passwords to new 16 byte hashes. See http://www.mysql.com/doc/en/Password_hashing.html for more. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySql died a hard death after using grant and won't restart
I can't figure this out. I'm setting privileges for access on a local net to a user [EMAIL PROTECTED] using grant and all of a sudden mysql is dead. I'm running 3.23.31 on Mandrake 7.2. I haven't had any problems in years. Anybody got any thoughts on this? Please reply to [EMAIL PROTECTED] What in the heck could cause a Bogus stack limit or frame pointer, aborting backtrace ?? The applicable part of the .err log is: mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong Bogus stack limit or frame pointer, aborting backtrace Number of processes running now: 0 031211 20:38:21 mysqld restarted 031211 20:38:21 Found invalid password for user: '[EMAIL PROTECTED]'; Ignoring user /usr/sbin/mysqld: ready for connections 031211 21:40:40 /usr/sbin/mysqld: Normal shutdown 031211 21:40:40 /usr/sbin/mysqld: Shutdown Complete 031211 21:40:40 mysqld ended 031211 21:40:47 mysqld started 031211 21:40:47 /usr/sbin/mysqld: Can't open file: 'user.MYD'. (errno: 145) 031211 21:40:47 mysqld ended next I mv /datadir/mysql /datadir/mysql-old then do a mysql_install_db and try again. Same error results mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong Cannot determine thread, ebp=0xb, backtrace may not be correct Bogus stack limit or frame pointer, aborting backtrace -- David C. Rankin, J.D., P.E. Rankin * Bertin, PLLC 510 Ochiltree Street Nacogdoches, Texas 75961 (936) 715-9333 (936) 715-9339 fax -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Statement Help - Is this possible?
Mark, You can find your first missing date in MySQL 4.1.0(alpha) or higher, using sub-selects, something like this: SELECT MIN(DATEADD(LOAD_DATE,1)) AS MISSING_DATE FROM load_cntl AS A WHERE DATEDIFF(NOW(), LOAD_DATE) 30 AND LOAD_DATE (SELECT MAX(LOAD_DATE) FROM load_cntl AS B) AND NOT EXISTS ( SELECT * FROM load_cntl AS B WHERE B.LOAD_DATE = DATEADD(A.LOAD_DATE, 1) ) Good Luck! Charlie [EMAIL PROTECTED]/Shield/Michigan From: Mark Marshall Date: December 10 2003 6:01pm Subject: SQL Statement Help - Is this possible? I have a database table that has one row entered every day by the user. If the user skips a day, it throws off our monthly totals. Assume a table with four columns: id (which is the key), date (-mm-dd), data1 (int), data2 (int) Is there a way to say Select the first missing date in the last 30 days from this table? I know I can select all the rows, ordered by date, and loop through them with PHP to look for first one that's missing. But that seems inefficient. And I know that once in place, in theory there should never be any more missing dates if I just grab the max date and add one. But I don't want to do that. I want the page to be SURE there are no missing dates in the last 30 days. Thanks, Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to READ/WRITE directly on MyISAM data files ?
Hello Stephane, I use myisam for reading. I now use SQL for updating. When I used myisam for updating, tables sometimes corrupted, and I was unable to work out why. Currently I am changing programs to use HANDLER syntax not myisam for reading. This will: - Save the need for table locking. - Allow the use of InnoDB etc. - Allow mysqld to have full control. I recommend you just use SQL for everything. Regards, Stephen - Original Message - From: Stéphane Bischoff [EMAIL PROTECTED] To: MySQL (E-mail) [EMAIL PROTECTED] Sent: Friday, December 12, 2003 12:15 AM Subject: How to READ/WRITE directly on MyISAM data files ? Hello, I would like to know how to write directly to MyISAM files, without passing by SELECT or UPDATE queries. I believe this info can be found in the files myisam.h and myisammrg.h, but I am not shure if its safe and how to do it. I would very much like to have an example of this code (C code). thank you Stéphane. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mean/median/mode
Mike Johnson wrote:
From: Robert Citek [mailto:[EMAIL PROTECTED]
Hello all,
How can I calculate the mean/median/mode from a set of data using SQL?
Mean seems to exist as the average (avg):
select name, avg(value) from table group by name
Is there a way to calculate median and mode with a group by clause?
Median: the value at which 50% of the samples are above and
below that value.
Mode: the most common value
For mode, this should work:
SELECT COUNT(value) AS mode FROM table GROUP BY value ORDER BY mode DESC LIMIT 1;
I expect you meant to include the value itself:
SELECT value, COUNT(value) AS mode FROM table
GROUP BY value ORDER BY mode DESC LIMIT 1;
This will work in many cases, but modes are tricky because:
- There may not be a mode. For a value to be a mode, it must occur at
least twice.
- There may be more than one mode. Any value whose frequency matches
the max frequency is a mode. For example, if values 2, 8, and 13 each
occur 8 times, and all other values occur less frequently, then 2, 8,
and 13 are modes.
I feel that there must be a better way, but the following works:
CREATE TEMPORARY TABLE counts
SELECT val, COUNT(val) AS freq
FROM data GROUP BY val HAVING COUNT(*)1 ORDER BY freq DESC;
SELECT @mode:=MAX(freq) FROM counts;
SELECT val AS mode, freq FROM counts WHERE freq = @mode;
DROP TABLE IF EXISTS counts;
As a further wrinkle, considering the error inherent in statistical
sampling, many statisticians would consider a value whose frequency is
much greater than most frequencies but not quite as high as the max
frequency to be a mode, or at least sort of a mode. For example, if
values 36 and 48 have counts of 102, value 76 has a count of 98, and
every other value occurs no more than 25 times, most would call the
distribution trimodal (3 modes), even though 76 is not strictly a mode.
Put another way, finding the modes is usually about finding the shape of
the distribution. An alternative to calculating the modes would be to
look for them visually by plotting the distribution. Something like this:
SELECT val, COUNT(val) AS frequency, repeat('.',COUNT(val)) AS histogram
FROM data GROUP BY val ORDER BY val;
+--+---+---+
| val | frequency | histogram |
+--+---+---+
|1 | 3 | ... |
|2 | 8 | |
|3 | 3 | ... |
|4 | 3 | ... |
|5 | 2 | ..|
|6 | 1 | . |
|7 | 3 | ... |
|8 | 8 | |
|9 | 1 | . |
| 11 | 2 | ..|
| 12 | 3 | ... |
| 13 | 8 | |
| 14 | 1 | . |
| 15 | 1 | . |
| 16 | 2 | ..|
+--+---+---+
15 rows in set (0.00 sec)
You might want to know something about the size of your counts relative
to your screen width before you try that. You could adjust accordingly,
however, with something like
SELECT val, COUNT(val) AS frequency,
repeat('+',ROUND(COUNT(val)/10)) AS histogram
FROM data GROUP BY val ORDER BY val;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF on AMD64
In the last episode (Dec 11), Ollie Gallardo said: I'm back with another question. I tried to compile my UDF with the gcc on my Opteron system and I got errors. Errors: /usr/lib64/gcc-lib/amd64-mandrake-linux-gnu/3.3.1/../../../../lib64/crt1.o(.text+0x21): In function `_start': ../sysdeps/x86_64/elf/start.S:92: undefined reference to `main' What is your gcc line? The above error indicates that you are trying to build an executable. UDFs must be shared object files. http://www.mysql.com/doc/en/UDF_compiling.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
