Re: Fastest way to log IP's
Storing the IP addresses as integers requires less memory but incurrs the cost of a call to these functions for every IP address. If you are going to use these addresses in string comparisons it's best to store them as varchars. - Asad On Thu, 2 Feb 2006, Brian Dunning wrote: I'm logging IP addresses and also searching for existing ones. Does anyone happen to know which is more resource intensive: storing the IP's as integers using INET_NTOA() and INET_ATON() to convert them at each query; or simply storing the IP as a varchar? In each case the IP field would be the primary key. -- 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]
max_connections
Are there any statistics that I can use to determine how high or low I should set max_connections? I am using MySQL as the backend for a Java web application that will need to accommodate a moderate amount of traffic. Also, if max_connections is set too high relative to the needs of the application, does this waste system resources? In other words, does MySQL reserve system resources based on the value of max_connections or does it dynamically reserve/release system resources based on what the application's current needs are? The application itself uses a connection pool and hence idle connections are recycled and reused. Any help would be appreciated. Thanks. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Linking to documents on another server
Hi. If you place these documents in the appropriate directory on the remote machine, a directory in which they can be served over the internet, then you should be able to link to them using a hyperlink reference (i.e. http://). The other option is to have PHP grab these documents for you using a Unix command such as wget or curl and place them on the server. Since this upload is automated, it won't require additional learning on the part of the staff. HTH Asad On Thu, 15 Dec 2005, Darrell Troth wrote: I am designing an intranet for a law firm. Everything works fine. My problem is that they need to include the documents that are relative to specific cases in their database. These documents are on a different computer and I cannot find how to link to them at their current location. I have found how to upload the documents, but this defeats the purpose and would cause me to train legal aides to do something different. I am running MYsql 5.0 on Windows XP with Apache and PHP. Thanks, darrell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Phone Number Column Advice
Hello. I posted a similar question a while back and received a bunch of responses. It depends on how you plan to use the numbers. If you plan to use them in calculations, then storing them as INTs is best. Also, breaking a number into separate components makes sense if you plan to use these components as qualifiers in WHERE clauses. I decided to store my numbers as VARCHARS after considering these issues and to allow for maximum flexibility although this is an inefficient use of memory. HTH Asad On Fri, 29 Jul 2005, Scott Purcell wrote: Hello, I am waffling on how to handle a column of phone numbers. I am not sure what is common practice on the database side. Talking to some co-workers, some feel it would be fine to use a varchar and others think there should be 3 integer columns in the database for each number? Anyone have advice or know of best-practice on this subject? Thanks, Scott -- 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]
Phone Number Storage
Is it better to store phone numbers as strings or as integers? Offcourse, storing them as integers saves space but this requires extra processing of the user's input (i.e. CPU time). Are there any other advantages/disadvantages of doing it one way or the other? - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Character Set
I need in depth information on the character set used by MySQL including invisible/escape characters/codes. Is there online documentation that I can read on this? Thanks. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary and Foreign Keys
Is it good practice to have a primary key for a table even though the table has a foreign key that is the primary key of another table? If so, why is this the case? I would appreciate if you point me to some resources that point out the advantages and disadvantages of both approaches. Thank you. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary and Foreign Keys (Follow Up)
As a follow up to my question, I did want to mention that the foreign key I am using is unique. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary and Foreign Keys (Follow Up)
Hello. I appreciate your input. To clarify, I will provide you with an example: I have a table called business and another one called food_business. The field business_id is a primary key of table business and a foreign key of table food_business. In this case, the foreign key is unique and although this is a one-to-one relationship, it would be inappropriate to merge these 2 tables since food_business is a specialization of business and will therefore contain fields that only apply to food businesses and not to any generic business. - Asad On Wed, 15 Jun 2005, Stefan Kuhn wrote: If your FK really is unique, you don't need two tables. Example First table Second Table IDFK 1 1 2 2 3 3 ... So you can make this one table. On other words, it would be a one-to-one relation. And this would be one table. Only with a one-to-many relation two tables make sense, but then your FK can't be unique. Stefan Am Wednesday 15 June 2005 12:41 schrieb Asad Habib: As a follow up to my question, I did want to mention that the foreign key I am using is unique. - Asad -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- 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]
Year Data Type
For purposes of comparison, is data of type Year treated the same as integers? I think this is the case because a year is represented using either 2 or 4 digits, but I am not absolutely certain. Any help would be appreciated. Thank you. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange Error in MySQL
Hello. I am receiving the following error when trying to insert into a field of type text: #1030 - Got error 139 from storage engine The data to be inserted should fit easily into a text field. In fact, this error only occurs for a particular record and other records with much larger data sets are not throwing the same error. Has anyone experienced something similar? I checked via Google but did not find any resources that helped. Any help would be greatly appreciated. Thanks. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange Error in MySQL
Hello. Yes, there are. In fact, other records have more data for this field in them and inserts for those records never threw an error. - Asad On Tue, 10 May 2005, Jay Blanchard wrote: [snip] Hello. I am receiving the following error when trying to insert into a field of type text: #1030 - Got error 139 from storage engine The data to be inserted should fit easily into a text field. In fact, this error only occurs for a particular record and other records with much larger data sets are not throwing the same error. Has anyone experienced something similar? I checked via Google but did not find any resources that helped. Any help would be greatly appreciated. Thanks. [/snip] The row is too big. Are there other columns into which a large amount of data are being placed? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting up a Chemical database
One table should be sufficient to handle this with one record for each product. - Asad On Mon, 4 Apr 2005, Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: I have a customer who has sent my close to 300 chemical products in word format? How do I translate this into mysql tables? -- 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]
GROUP BY, ORDER BY clauses
Does MySQL 4.1 support the use of GROUP BY and ORDER BY used in conjunction with one another? I have tried to execute several queries with both these clauses but the result set I get is different from what I expect. My queries read as follows: SELECT *, *, * FROM * WHERE * GROUP BY * ORDER BY * Also, does GROUP BY only work on fields that are strings (i.e. CHAR, VARCHAR, TEXT, etc.). Thanks in advance. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY, ORDER BY clauses
Sorry for the confusion. In this case I am using the * to denote a field name instead of the wild card character. - Asad On Wed, 30 Mar 2005 [EMAIL PROTECTED] wrote: Asad Habib [EMAIL PROTECTED] wrote on 03/30/2005 10:53:38 AM: Does MySQL 4.1 support the use of GROUP BY and ORDER BY used in conjunction with one another? I have tried to execute several queries with both these clauses but the result set I get is different from what I expect. My queries read as follows: SELECT *, *, * FROM * WHERE * GROUP BY * ORDER BY * Also, does GROUP BY only work on fields that are strings (i.e. CHAR, VARCHAR, TEXT, etc.). Thanks in advance. - Asad I hope you don't expect your query to actually work. The SELECT clause is the only place where you can use the * wildcard to mean all columns. If you wanted to frame a sample query but leave out information, I have been using an ellipsis (three dots together) to indicate the missing piece(s) like this: SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ... However, I generally do not leave out EVERYTHING in a query, like the one above. I usually only leave out the parts that aren't important to the information I am trying to convey. If I want to indicate that there was something specific the user needs to replace, I put that inside of angle brackets like this SELECT a list of columns from your table , a list of aggregate functions on columns from your table FROM a table name GROUP BY all of the columns in your SELECT clause that are not part of an aggregate function These are just my conventions. Use them only if you like them. I am not nor will I ever become the style police for this list. I just thought you could use a little help in creating better sample queries :-) You asked if you can use GROUP BY and ORDER BY in the same query. Absolutely!! I do it frequently. You also asked if GROUP BY works on different column types. Absolutely!! You can group on any type of field or combination of datatypes supported by MySQL (with the exception of TEXT and BLOB fields as they usually contain more data than is practical to use to form aggregates. I would recommend that you do not use a BLOB or TEXT field in a GROUP BY unless it is unavoidable. IF you MUST do it, then you should manually specify what portion of the field to use) May I suggest some reading? http://dev.mysql.com/doc/mysql/en/select.html http://dev.mysql.com/doc/mysql/en/group-by-functions-and-modifiers.html http://dev.mysql.com/doc/mysql/en/blob.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL and phpMyAdmin
I just installed phpMyAdmin, made the appropriate changes in the config.inc.php file, and got the following error when accessing it via both IE and Safari on Mac OS X Panther client. #1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client I am using MySQL 4.1.10, the latest stable release, with phpMyAdmin-2.6.1-pl3, which the documentation says are compatible. Any help would be greatly appreciated. Thanks. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Authenticating a User to MySQL
I just installed MySQL 4.1 on Mac OS X Panther Client. I am now having trouble authenticating users(root, admin) to MySQL. I am able to start MySQL but not using a specific user name and password. When I type ./mysql in /usr/local/mysql/bin, I am able to access MySQL. However, I have no privileges this way. However, when I try the following: ./mysql -u root -p password I get an error that states ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) Any help would be greatly appreciated. Thanks. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY Clause
I am trying to use GROUP BY with a field of type text that is set to NOT NULL by default. However, in practice this field does not always contain a string for every record and defaults to the empty string in this case. When I try to use GROUP BY with this field in a SELECT statement, only 1 record is retrieved. Anyone experience a similar problem? Your help would be greatly appreciated. Thanks. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installation Instructions for MySQL for Mac OS X Panther
Does anyone know where I can find in-depth, precise instructions to install MySQL on Mac OS X Panther client? - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.1.10 is released
Are foreign key and other constraints enforced by the db server in this
version or is this something that the programmer has to ensure via
application logic?
- Asad
On Tue, 15 Feb 2005, Heikki Tuuri wrote:
Hi!
InnoDB is the MySQL table type that supports foreign keys, transactions,
non-escalating row-level locking, all SQL-92 transaction isolation levels,
multiversion concurrency control, savepoints, multiple tablespaces, and a
non-free online binary hot backup tool.
MySQL-4.1.10 is mainly a bugfix release.
Windows users of the my.cnf option innodb_file_per_table should upgrade to
this version, because this fixes the bug introduced to the Windows version
of 4.1.9, and earlier versions contained the critical bug in
innodb_file_per_table.
Functionality added or changed:
* When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the
operating system version at run time and use the fcntl() file flush method
on Mac OS X versions 10.3 and later. Apple had disabled fsync() in Mac OS X
for internal disk drives, which caused corruption at power outages.
* A shared record lock LOCK_REC_NOT_GAP is now taken for a matching record
in the foreign key check because inserts can be allowed into gaps.
* Relaxed locking in INSERT...SELECT, single table UPDATE...SELECT and
single table DELETE...SELECT clauses when innobase_locks_unsafe_for_binlog
is used and isolation level of the transaction is not serializable. InnoDB
uses consistent read in these cases for a selected table.
Bugs fixed:
* Fixed a bug introduced in 4.1.9 to the Windows version if you used
innodb_file_per_table. mysqld would stop and complain about Windows error
number 87 in a file operation. (Bug #8021)
* Corrected the handling of trailing spaces in the ucs2 character set. (Bug
#7350)
* Use native tmpfile() function on Netware. All InnoDB temporary files are
created under sys:\tmp. Previously, InnoDB temporary files were never
deleted on Netware.
* Fix a race condition that could cause the assertion
space-n_pending_flushes == 0 to fail in fil0fil.c, in
@code{fil_space_free()}, in DROP TABLE or in ALTER TABLE.
* ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY ... complained about bad
foreign key definition. (Bug #7831)
* Fix a theoretical hang over the adaptive hash latch in InnoDB if one runs
INSERT ... SELECT ... (binlog not enabled), or a multi-table UPDATE or
DELETE, and only the read tables are InnoDB type, the rest are MyISAM. (Bug
#7879)
Upgrading to 4.1.9:
* If you have created or used InnoDB tables with TIMESTAMP columns in MySQL
versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to
MySQL-4.1.4 or later. The storage format in those MySQL versions for a
TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then
no rebuild of TIMESTAMP tables is needed.
* If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed
columns in MySQL versions = 4.1.2, then you have to rebuild those tables
after you upgrade to = 4.1.3. The reason is that the sorting order of those
characters and the space character changes for some character sets in 4.1.3.
See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases
where you need to rebuild the table. Also MyISAM tables have to be rebuilt
or repaired in these cases.
* If you have used column prefix indexes on UTF-8 columns or other multibyte
character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when
you upgrade to 4.1.6 or later.
* If you have used accent characters (ASCII codes = 128) in database names,
table names, constraint names, or column names in versions 4.1, you cannot
upgrade to 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use
RENAME TABLE to overcome this if the accent character is in the table name
or the database name, or rebuild the table.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Special Characters
Does anyone know where I can find a list of characters that cannot be inserted into a MySQL database as part of a string? I know that MySQL does not accept single and double quotes and that these have to replaced by their name code equivalents. Are there any other characters that are unacceptable? Any help would be greatly appreciated. Thanks. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
