Re: Can't connect URGENT!
On 8 Feb 2004, at 00:29, [EMAIL PROTECTED] wrote: Hi, I have instaled mysql 4.0.17 on a debian server, but i have a big problem. I can only connect to mysql at the server and only with -h localhost. If I try this: mysql -h server -u root from anywhere i receice always the same response: ERROR 2003: Can't connect to MySQL server on 'server' (111) the only way I can connect to the server is typing mysql -h localhost -u root at the server. Can anyone please help? I already search at google and can fine anything. TIA Carlos Baptista Just a thought - have you granted privileges for user 'root' to connect using hostname 'server'? http://www.mysql.com/doc/en/GRANT.html HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can't install DBI on panther
On 27 Jan 2004, at 00:37, tait sanders wrote: i've run both 'perl -MCPAN -eshell' and 'make DBI' and both come back reporting heaps of errors like the following: from Perl.xs:1: /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:380: 30: sys/types.h: No such file or directory /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:411: 19: ctype.h: No such file or directory /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:423: 23: locale.h: No such file or directory /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:440: 20: setjmp.h: No such file or directory /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:446: 26: sys/param.h: No such file or directory /System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:451: 23: stdlib.h: No such file or directory At a guess, I'd say you haven't installed the Developer Tools. That installs /usr/include which you appear to be missing. HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: hierarchical records, I need some help!! ;(
On 13 Jan 2004, at 09:19, Victor Reus wrote: Hi again, Second: specify what you mean with all relations. Can you share some sample data and sample output (that is: what do you expect the query to return). Ok my table noms is like | id | int(11) | | PRI | [NULL] | auto_increment | reference| varchar(255) | | component| varchar(255) | The relation between refefence and component are like parent - child Here is an example Table contains : IDREFERENCE COMPONENT 1 A004 B001 2 B001 C003 3 B001 D003 (I have more records but only this are what i want to take as an example) So the relations are like a hierarchical tree: A004 BOO1 C003 D003 And i want one sentence that takes all the relations. The SQL statement result should be like: REFERENCE COMPONENT AOO4 B001 B001 COO3 B001 COO3 Coudl somebody give me a tip to do it?. Thanks. Victor. Why not SELECT * FROM THE_TABLE WHERE REFERENCE = 'B001' OR COMPONENT = 'B001' This will work given your example. However, I suspect this isn't the full story. Do you also want to see what components make up C003 and D003 in the same query? I think you're after a feature not yet implemented in MySQL - the 'CONNECT BY PRIOR' SELECT statement, just the ticket for hierarchical queries. http://www.mysql.com/doc/en/TODO_future.html - the first item in the list! Here's an article on how it's used in Oracle - is this what you need? http://www.oracleadvice.com/Tips/pkfktree.htm BTW - MySQL people - any idea when this will be implemented? I have a couple of projects with hierarchical records which would just love this query! -- Regards, Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird
On 13 Jan 2004, at 19:49, Chris L. White wrote: Ok I was able to use mysqlcc and as long as I keep the host name localhost it will let me in. But the name of the host should be Napmarilu or the IP number right? When you change it to either one of those it will not let you in. So how would I fix this problem. However on the other hand mysqladmin screen it still only shows the user Administrator and the test database sheets. Any one got any ideas? See the thread entitled mysql database, user table, two root accounts I posted a question about the difference between localhost and 127.0.0.1 Apparently, mysql see's localhost and uses the unix socket. Any other name forces it to use TCP/IP. Perhaps your server doesn't have TCP/IP enabled? (Can you see anything listening on port 3306?) HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: hierarchical records, I need some help!! ;(
On 13 Jan 2004, at 19:11, Jochem van Dieten wrote: I most certainly hope this Oracle idiosyncracy will never make it into MySQL. The SQL standard defines a different syntax for doing recursive queries, using WITH RECURSIVE. I see no reason for MySQL to implement a non-standard way for doing recursive queries when there is a viable alternative (DB2 already uses WITH conform the SQL standard) that has standardized behaviour. I know that a CONNECT BY PRIOR patch is readily available for PostgreSQL but the PostgreSQL developers reject it exactly because it does not follow the SQL standard. I think the PostgreSQL developers are right to do so, non-standard features have caused enough trouble for application portability as it is. Jochem Point taken. I never knew about the WITH RECURSIVE syntax. Certainly, if CONNECT BY PRIOR is an Oracle only extension, then WITH RECURSIVE does sound like the better option of the two. Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database, user table, two root accounts
On 9 Jan 2004, at 22:43, Michael Stassen wrote: As [EMAIL PROTECTED] and [EMAIL PROTECTED] are separate entries in the user table, each with its own password and privileges, they are 2 separate root accounts from mysql's perspective. You could choose to think of them as the same account by keeping their settings the same, or you could choose to think of them as separate root accounts, possibly with separate settings. You could, for example, give root fewer privs when connecting externally than via localhost. Many people, myself included, eliminate [EMAIL PROTECTED] altogether, so that the root user can only connect from localhost, or replace the % with something more limiting (say [EMAIL PROTECTED]). Ask yourself which IPs should be allowed to administer mysql as root and act accordingly. How does MySQL decide which entry to use when authenticating? Eg. if you've two host entries; one '192.%' and the other '192.168.%' - and you connect from 192.168.100.12, which row gets chosen? Perhaps it's the more exact match? i.e. 192.168.% But what if there isn't a more exact match... i.e. choose between '192.%' or '%.168.%' What if there are two entries - 'localhost' and '127.0.0.1' ? -- Regards, Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql database, user table, two root accounts
On 10 Jan 2004, at 17:47, Michael Stassen wrote: Eg. if you've two host entries; one '192.%' and the other '192.168.%' - and you connect from 192.168.100.12, which row gets chosen? As I understand it, 192.168.% is more specific than 192.%, so 192.168.100.12 would match 192.168.% My thoughts exactly. But what if there isn't a more exact match... i.e. choose between '192.%' or '%.168.%' Well, I can't imagine why you would put %.168.% in for host. If you did, I think 192.% would be more specific than %.168.%, but the manual is unclear on that. I suppose you could try it and see. True, I can't imagine why you would want to use %.168.% either; I was just curious. I've just tried it myself... (OK, so I was lazy before! :) - MySQL appears to prefer 192.% over %.168.% What if there are two entries - 'localhost' and '127.0.0.1' ? To mysql, those are not the same. localhost is a unix socket connection, 127.0.0.1 is a TCP/IP connection. So, mysql -u username -p would connect as [EMAIL PROTECTED], but mysql -h 127.0.0.1 -u username -p would connect as [EMAIL PROTECTED] Makes sense. Thanks very much; I was just curious! Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: When does using multiple databases make sense?
On 8 Jan 2004, at 04:12, Paul F wrote: Greetings, I am wandering under what circumstances it is sensible/beneficial to use multiple databases for a single project, and why. The reason I ask is because I am re-developing an existing database with MySQL that someone else created with another engine (DBISAM) and chose to have 5 separate databases used by one application. I've worked on a project where we had 2 databases; one was for live data, the other was used when the application switched to a training mode and so couldn't affect live data. It was a Windows app and connection was via ODBC. We had 2 ODBC sources set up to point to same server, but a different database. (Actually, it was a MS SQL Server, but the same principles apply). It was a simple matter of deciding which source to use depending on whether the app was in training mode or not. I can't think of a reason for 5! :) Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL as document storage?
Hi, (disclaimer - this thread could easily go off topic; I'm interested only in the MySQL aspects of what follows...) At work we are currently investigating ways of filing all our electronic documents. There is commercial software that will do this I know, but I was wondering whether MySQL would be suitable for this type of thing. The 'documents' could be literally any binary file. My idea would be to create a table with a blob column for the document itself, and document title, reference number, keywords, other meta-data. And a web-based front-end to search and serve documents. Although the documents could be any file, the majority would be textual documents (Word documents, PDF, etc). How would one go about indexing such data, since full text searches operate on textual columns? How to cope with columns exceeding the max packet length? Why is there a max_packet_length setting; surely this is low-level stuff that shouldn't affect query and result sizes? Is storing the actual documents in the database such a good idea anyway? Perhaps store the file in a file system somwhere and just store the filename? If anyone has experience in doing (or been dissuaded from doing) this kind of application your thoughts and comments would be appreciated. (If only to tell me don't be so stupid, it'll never work :) Thanks. -- Regards, Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL as document storage?
On 7 Jan 2004, at 21:51, [EMAIL PROTECTED] wrote: This article discusses it briefly: http://php.dreamwerx.net/forums/viewtopic.php?t=6 That's an interesting article. Thanks. A similar table design to what I had in mind (hmmm... how different can these things be! :) I like the idea of splitting up binary data into segments so as reduce the load on the server. I assume this works because MySQL doesn't send all rows over the connection when the query completes, only just the ones you ask for? (In this case, a segment at a time?) Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL as document storage?
On 7 Jan 2004, at 22:24, Rob Brahier wrote: I made an online file manager using PHP and MySQL some years ago, and am now embedding something similar into my office's database front-end. I decided to store our files in the file system rather than the database in order to keep the DB size low. A benefit of this is it takes less time to restore a backup of the database than it would if I were dealing with the extra gigabytes of embedded files (which I can restore on an individual basis). Incremental backups would certainly be smaller. The backup would spot individual files being changed, but being blobs in a table, the one (large) file in the database would have to be backed up for each change? As for indexing, a lot of the document retrieval solutions out there just go by metadata when you do a file search. Business class scanning systems offer you the option of embedding user-supplied metadata in your scanned files so adding your own keywords is an option. In my experience you are better off going by just some supplied keywords and metadata rather than the full text of a document because you end up with more relevant results. The exception to this is when you are just dying to know how many documents contain the word pie. If you find that this is the case then you obviously have the free time needed to build some extra indexes... ;) I think you're right. Thanks for the input. -- Regards, Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to drop more than one tables at once in a database?
On 5 Jan 2004, at 19:58, Bing Du wrote: Greetings, Say, one database has a lot of tables. How can I drop some of them all at once assuming their names follow some pattern like tbl_*? Thanks in advance for any ideas, Take a look at a very similar thread here... http://lists.mysql.com/mysql/156593 The best answer being 'look at the documentation'... http://www.mysql.com/doc/en/DROP_TABLE.html HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1005 during add foreign key
On 5 Jan 2004, at 21:10, Sid Lane wrote: on mysql 4.0.14-standard (x86 Linux) when I try the following: alter table child_table add ( foreign key (column1, column2) references parent_table on delete cascade) ; on an existing innodb table I get: ERROR 1005: Can't create table './dbname/#sql-70f5_b92.frm' (errno: 150) I know the script is syntactically correct since it was used to create the same constraint on the same table (from same create_table script) on dev server so I'm thinking there's something different in my installation/environment (unfortunately I have to admit I didn't keep the best notes from building the original play box). any suggestions would be greatly appreciated! Sid Lane DBA - Site Operations Does this help? http://groups.yahoo.com/group/SimpleORM/message/735 WHAT is the average airspeed velocity of an unladen swallow? African or European? ;) Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert: auto increment field
On 4 Jan 2004, at 15:09, Mike Mapsnac wrote: I have table with 7 fields. First field is id (auto increment). As I understand the value should start from 0 and next value will auto increment. And I shouldn't add insert the value. So the insert statemens below gives me an error. ERROR 1136:Column count doesn't match value count at row 1 insert into product values('456789','t1', 'new', 2, 2, 10); Two options: Use NULL as the value for the auto-increment column. (Probably the easiest method!) (side note: you can then retrieve the real value with LAST_INSERT_ID() function) Or, specify the column names in your query as well, so MySQL can tie up columns and values: insert into product (col2, col3, col4, col5, col6) values('456789','t1', 'new', 2, 2, 10) Without column names, it expects there to be values for all columns. So in your case, just don't specify the auto-increment column. HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: insert: auto increment field
Forwarded to the list for the benefit of all... Begin forwarded message: From: Mike Mapsnac [EMAIL PROTECTED] Date: 4 January 2004 15:42:33 GMT To: [EMAIL PROTECTED] Subject: Re: insert: auto increment field I use both metods and they works But when I make another insert I receive a message ERROR: 1062 Duplicate entry '2147483647' for key 1. Why id (primaty , and auto increment) start from 2147483647 and not from 0 or 1 Thanks From: Steve Folly [EMAIL PROTECTED] To: MySQL MySQL [EMAIL PROTECTED] Subject: Re: insert: auto increment field Date: Sun, 4 Jan 2004 15:27:47 + On 4 Jan 2004, at 15:09, Mike Mapsnac wrote: I have table with 7 fields. First field is id (auto increment). As I understand the value should start from 0 and next value will auto increment. And I shouldn't add insert the value. So the insert statemens below gives me an error. ERROR 1136:Column count doesn't match value count at row 1 insert into product values('456789','t1', 'new', 2, 2, 10); Two options: Use NULL as the value for the auto-increment column. (Probably the easiest method!) (side note: you can then retrieve the real value with LAST_INSERT_ID() function) Or, specify the column names in your query as well, so MySQL can tie up columns and values: insert into product (col2, col3, col4, col5, col6) values('456789','t1', 'new', 2, 2, 10) Without column names, it expects there to be values for all columns. So in your case, just don't specify the auto-increment column. HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Enjoy a special introductory offer for dial-up Internet access limited time only! http://join.msn.com/?page=dept/dialup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Crystal Reports?
On 2 Jan 2004, at 12:26, Eldon Ziegler wrote: Has anyone used Crystal Reports successfully with MySQL? I tried the ODBC driver but it was rejected with a message that the Professional version was needed. The issue might be with Crystal Reports itself and nothing to do with MySQL. Are you using the Standard edition? http://www.crystaldecisions.com/products/crystalreports/editions.asp ... indicates you can only use ODBC with Crystal Reports starting with the Professional edition. HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: MySQL and Crystal Reports?
I've forwarded this reply to the list so other people may benefit from the information. Steve. Begin forwarded message: From: Eldon Ziegler [EMAIL PROTECTED] Date: 2 January 2004 18:58:36 GMT To: Steve Folly [EMAIL PROTECTED] Subject: Re: MySQL and Crystal Reports? I found their capabilities chart less than clear. A search of their web site didn't turn up anything that sounded directly related but there was a patch having to do with recognizing names correctly with MySQL and the C.R. Advanced Edition. Once I installed that patch the Standard Edition works fine with MySQL. Go figure! Eldon At 07:50 am 1/2/2004, you wrote: On 2 Jan 2004, at 12:26, Eldon Ziegler wrote: Has anyone used Crystal Reports successfully with MySQL? I tried the ODBC driver but it was rejected with a message that the Professional version was needed. The issue might be with Crystal Reports itself and nothing to do with MySQL. Are you using the Standard edition? http://www.crystaldecisions.com/products/crystalreports/editions.asp ... indicates you can only use ODBC with Crystal Reports starting with the Professional edition. HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Eldon Ziegler President ProAtion Systems, Inc. www.proation.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem
On 30 Dec 2003, at 16:22, Carlos Andre Moura de Amorim wrote: i have a follow problem, please, help-me [EMAIL PROTECTED] db]# mysql --user=root --password=xx ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Wrong password or root does not have a password. (The 'Using password' describes what you are doing; not what is set up for root). Try without --password option. If this is the case, you would be wise to create a password for root: $ mysqladmin --user=root password the-password HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie question about calculations
On 27 Dec 2003, at 08:59, daryl hansen wrote: How do I make Field3 a calculation, which equals 2 if fields 1 2 where values set to 1? Any pointers in the right direction would be sincerely appreciated. Take a look at 6.3.1.3 and 6.3.1.4 in the MySQL documentation for logical operators and control flow functions. HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie question about calculations
On 27 Dec 2003, at 21:04, daryl hansen wrote: I can't find anything in the manual about proper syntax for this when creating a table. All I want is my Price field to equal the total of my Adults and Children fields. Can someone please draw me a picture? CREATE TABLE Cart ( ID bigint (20) NOT NULL auto_increment , SessionID varchar (50) , Activity_ID varchar (5) , Company_ID varchar (5) , Submitted varchar (5) , Vendor varchar (50) , Activity varchar (50) , Adult_Label varchar (20) , Child_Label varchar (20) , Other_Label varchar (20) , Price_Adult varchar (10) , Price_Child varchar (10) , Price_Other varchar (10) , Price bigint (20) , Tax varchar (10) , Adults bigint (5) , Children bigint (5) , Other varchar (5) , Date date , CreateDate date , KEY `ID` (`ID`) ) Thanks, Daryl Hansen On Dec 26, 2003, at 11:54 PM, Steve Folly wrote: On 27 Dec 2003, at 08:59, daryl hansen wrote: How do I make Field3 a calculation, which equals 2 if fields 1 2 where values set to 1? Any pointers in the right direction would be sincerely appreciated. Take a look at 6.3.1.3 and 6.3.1.4 in the MySQL documentation for logical operators and control flow functions. HTH Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] You can only use these functions from a select statement; not in a CREATE TABLE statement. To be honest, there is (usually[1]) no point in having an extra column that is the always the result of some arithmetic expression involving other columns. It wastes space. You're better off calculating it in the query. Steve. [1] - I say 'usually' - I'm sure there are times when you would want to do this, perhaps caching lengthy calculations; but adding 2 numbers isn't! ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using MySQL as an RCS repository
Hi, I'm not sure if that subject made any sense!? What I'm looking to do is take an existing product we have (a software defect tracker) that uses flat files to store defect entries. Modifications to each defect are recorded as diffs using the perl module RCSLite (like your standard RCS, but all in perl). Because this is all currently in files, in our performance tests we've done we're (obviously!) seeing slow downs when processing a database with large numbers of entries. And that's not just the equivalent of full-text searches. We have sketched out a few database designs that can replace it, but all would involve losing the revision history of each entry (there is some reluctance to do that!). During it's lifetime, each 'defect' in the database gets modified, probably, on average 3 or 4 times, at most, maybe 10. Modifications are usually additional text (not allowed to modify old text), state changes (like open - fixed - ready for test, etc), and who the defect is assigned to. As I said, we have database designs that can replace *most* of what we have already, but I'm interested in the RCS side of things, and whether anybody has any experience of doing this sort of thing (RCS) or some tips to share. Thanks very much. -- Regards, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]