Problem Installation
20Hi; I had a working installation of MySQL, and then I zapped it. I found out the hard way that when you delete symlinks, you also delete the files to which they are linked :( I wiped my entire /usr/local/include dir. I'm using ports on FreeBSD 6.2. As it happens, I had a complete copy of the identical include dir on another machine, so I tarballed it and copied it over. Unfortunately, that didn't seem to help. I rebuild MySQL from ports and everything looks fine. But when I try to fire it up I discover it's not up. When I run /usr/local/bin/mysqld_safe it starts up and immediately dies. What log file would tell me what happened? Also, strangely, if I move the /usr/local/include/mysql folder and rebuild MySQL, it *doesn't* rebuild that folder! I would have assumed that folder would have been built in the installation of MySQL. Please advise. TIA, Scott No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail
Problem Installation
Hi; [After waiting 4 hours, I don't think my post posted, so I'm re-posting] I had a working installation of MySQL, and then I zapped it. I found out the hard way that when you delete symlinks, you also delete the files to which they are linked :( I wiped my entire /usr/local/include dir. I'm using ports on FreeBSD 6.2. As it happens, I had a complete copy of the identical include dir on another machine, so I tarballed it and copied it over. Unfortunately, that didn't seem to help. I rebuild MySQL from ports and everything looks fine. But when I try to fire it up I discover it's not up. When I run /usr/local/bin/mysqld_safe it starts up and immediately dies. What log file would tell me what happened? Also, strangely, if I move the /usr/local/include/mysql folder and rebuild MySQL, it *doesn't* rebuild that folder! I would have assumed that folder would have been built in the installation of MySQL. Please advise. TIA, Scott No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail
Re: Undelete a droped table
Sorry, Microsoft SQL! From: Panos Tsapralis [EMAIL PROTECTED] Sent: Wednesday, November 08, 2006 6:48 AM To: [EMAIL PROTECTED] Subject: Re: Undelete a droped table I suppose you mySQL - not mSQL (there was a lite RDBMS once named mSQL - I don't know whether it exists any more...). On 11/8/06, scott [EMAIL PROTECTED] wrote:As a note: mSQL has an attach function that will allow you to take those files and recreate the database by only giving it the database name and the file names. It's a way to put back a backup made of the physical database if you backup directly from the SQL data directory and not through the SQL backup utility and then through your tape or other backup. Scott. From: Panos Tsapralis [EMAIL PROTECTED] Sent: Tuesday, November 07, 2006 1:10 PM To: Eric Lommatsch [EMAIL PROTECTED] Subject: Re: Undelete a droped table Eric, as far as SQL is concerned, there is no way to recover dropped tables and, to my knowledge, there is no MYSQL-specific way to do that either, so, your only hope is to have a rather fresh backup of your database. BTW, this gives me an idea: I suppose that dropping a database table is a two-task process, namely deleting the table's attributes from the database metadata (an SQL operation) and deleting the table's files from the file-system. So, wouldn't it be preferrable to move the table's files into the Recycle Bin (or the Trash or some other stand-by place into the file-system) instead of permanently deleting them? In that case, it would be possible to recover a dropped table by creating a blank table into the database (equivalent to the one that got dropped) and then replace its empty data files with those from the Recycle Bin. HTH, Panos Tsapralis. On 11/7/06, Eric Lommatsch wrote: Hello, I have accidently droped a table from one of my database's. The table was an innodb table. Is there anyway that I can recover the table or is that information gone for good if I don't have a current backup of that table? Thank you Eric H. Lommatsch Programmer MICRONix, Inc. 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED] -- Panos Tsapralis, Senior Software / Systems Engineer, +306948076902, Athens, GREECE. -- Panos Tsapralis, Senior Software / Systems Engineer, +306948076902, Athens, GREECE.
auto_increment
Hi All, I'm trying to restore a database where the first column is set to auto_increment. When I apply the inserts created from the mysqldump I get errors about the auto_increment column. Is there a may to turn it off while I run the inserts or do I have to modify the table? Thanks, Scott Johnson [EMAIL PROTECTED] Tel.: (514) 917-4922 Fax: (514) 673-0011 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
setting the data directory.
Hi All, I am trying to find out how to change the location of the data files. I have not yet found a generic entry for the my.conf file to set a data and log location. Can I move the exiting data directory form the mySQL tree and put a soft link to a new location on another partition? thanks, Scott Johnson [EMAIL PROTECTED] Tel.: (514) 917-4922 Fax: (514) 673-0011 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Crystal reports with MySQL
Hi, You simply install the MyODBC interface and away you go. You simply create a connect and Us crystal as if it was a connection to the other windows database. hahaha I have included the link below: http://dev.mysql.com/downloads/connector/odbc/3.51.html Scott. [EMAIL PROTECTED] www.Wilty.ca Tel: 514-917-4922 Fax: 514-673-0011 -Original Message- From: Mayuresh Kshirsagar [mailto:[EMAIL PROTECTED] Sent: Monday, July 18, 2005 3:03 PM To: mysql@lists.mysql.com Subject: Crystal reports with MySQL I need to use crystal reports with MySQL database. We have MySQL query browser to query audit database of meta directory. Can I use ODBC connection for my crystal report with MySQL database? If yes, then please let me know what ODBC driver I need to install? OR how can I make connection to MySQL database to crystal reports? -- 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: zip code search within x miles
Hi,
Just as a speed note! It's faster to calculate a square and find those
records and then drop the corners with the circle calculation.
Scott.
-Original Message-
From: Eamon Daly [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 19, 2005 11:20 AM
To: Scott Haneda; MySql
Subject: Re: zip code search within x miles
I don't think anyone's replied with an actual great circle
calculation. Here's our zipcode table, populated with data
from zipcodedownload.com (and note the index on zipcode,
latitude, and longitude:
CREATE TABLE `zipcodes` (
`zipcode` char(5) NOT NULL default '',
`zipcode_type` enum('S','P','U','M') NOT NULL default 'S',
`city` char(45) NOT NULL default '',
`city_type` enum('D','A','N') NOT NULL default 'D',
`state` char(75) NOT NULL default '',
`state_code` char(2) NOT NULL default '',
`area_code` char(3) default NULL,
`latitude` double(12,6) NOT NULL default '0.00',
`longitude` double(12,6) NOT NULL default '0.00',
KEY `city_state_code` (`zipcode`,`city`,`state_code`),
KEY `position` (`zipcode`,`latitude`,`longitude`)
) TYPE=MyISAM
Here's the calculation to get zipcodes within 5 miles of
02134:
SELECT
b.zipcode, b.state,
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) +
COS(a.latitude*0.017453293) *
COS(b.latitude*0.017453293) *
POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
AS distance
FROM zipcodes a, zipcodes b, zipcodes c
WHERE
a.zipcode = '02134' AND # -- Your starting zipcode
a.zipcode = c.zipcode AND
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) +
COS(a.latitude*0.017453293) *
COS(b.latitude*0.017453293) *
POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
= 5 # -- Your target radius
GROUP BY distance
Takes about half a second on our box. Here's the explain:
*** 1. row ***
table: a
type: ref
possible_keys: city_state_code,position,default_city
key: position
key_len: 5
ref: const
rows: 2
Extra: Using where; Using index; Using temporary; Using filesort
*** 2. row ***
table: c
type: ref
possible_keys: city_state_code,position,default_city
key: position
key_len: 5
ref: const
rows: 2
Extra: Using where; Using index
*** 3. row ***
table: b
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 70443
Extra: Using where
In case you're curious about the number of zipcodes:
mysql select count(*), count(distinct zipcode) from zipcodes;
+--+-+
| count(*) | count(distinct zipcode) |
+--+-+
|70443 | 42471 |
+--+-+
Can't take credit for the SQL, by the way; I'm pretty sure I
found it on Google.
Eamon Daly
- Original Message -
From: Scott Haneda [EMAIL PROTECTED]
To: MySql mysql@lists.mysql.com
Sent: Friday, April 15, 2005 5:37 PM
Subject: zip code search within x miles
How are sites doing the search by zip and coming up with
results within x
miles? Is there some OSS zip code download that has been created for
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: zip code search within x miles
Hi all, I have done this type of setup before. There is a company that puts out a zipcode file with the Latitude and longitude in it. You can then calculate what's in the distance you are looking for. Scott. -Original Message- From: Robert Dunlop [mailto:[EMAIL PROTECTED] Sent: Friday, April 15, 2005 6:51 PM To: Scott Haneda; MySql Subject: Re: zip code search within x miles I haven't looked into this recently, but as long as 15 years ago there were zip - mile libraries available; I assume similar libraries still exist. The ones I worked with all used only the first 3 digits. Full five digit and nine digit zips get to pretty specific geographic locations, a level of accuracy not needed when telling people what resource exist within x miles. You might try google something like: (zipcode OR zip) distance software library HTH Bob - Original Message - From: Scott Haneda [EMAIL PROTECTED] To: MySql mysql@lists.mysql.com Sent: Friday, April 15, 2005 3:37 PM Subject: zip code search within x miles How are sites doing the search by zip and coming up with results within x miles? Is there some OSS zip code download that has been created for this? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- 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: Query last record in table
Hi Tia, I'm not sure, but my old SQL trick was to do a select * top 1 from subscribers order by acctid desc Scotty. -Original Message- From: Mike Blezien [mailto:[EMAIL PROTECTED] Sent: July 5, 2004 2:52 PM To: MySQL List Subject: Query last record in table Hello, what is the most effecient way to query the last record in a table, if querying the primary key(acctid) column ?? I've tried select *,max(acctid) as lastid from subscribers where acctid = 'lastid' group by acctid but this returns nothing ?? TIA :) -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.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]
DBD or InnoDB or not?
Hi Every one, I'm back to using Mysql after being away doing too many Microsoft job. hahaha I'm installing my first MySQL in about five years and I'm perplexed with the the added formats of batabase table. What are the pro's and con's of the DBD and InnoDB formats over the origional Binary? Just as a note, I'm trying to setup a small company with DBmail and MySQL. So file blobs and back logs of saved data will be the norm. I'm also going to be storing faxes as well. Thanks for your time, Scott Johnson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Questions about MySQL's INSERT syntax....
Hi Scott,
Yes this will work. But, you the columns you are leaving out, must be auto fill or
allow blank/Null entries.
Scotty.
Original Message:
From: Scott Fletcher [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Questions about MySQL's INSERT syntax
Date: Thu, 17 Jun 2004 12:28:16 -0400
Hi! The SQL's INSERT Syntax that have been frequently been used is
--snip--
INSERT INTO TABLE1
(COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5)
VALUES
('ONE','TWO','THREE','FOUR','FIVE')
--snip--
where the TABLE1 have 5 columns, COLUMN1, COLUMN2, COLUMN3, COLUMN4,
COLUMN5. What I wanna know is will MySQL accept a fewer columns
instead of all, like this...
--snip--
INSERT INTO TABLE1
(COLUMN1,COLUMN3)
VALUES
('ONE','THREE')
--snip--
Because on some database software/application, it won't accept this if
the table get very long with many columns and I was forced to use all of
hte columns in the SQL Syntax. I don't know how MySQL react to this
with a very long table and with many columns.
Thanks,
Scott F.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql syntax help
I have a db with slightly over 614,000 records of names and addresses. In the address column, there are quite a few records like 123 any rd # 2 319 w. 1st st # B 4321 test blvd # 42 etc I want to replace all the number signs with the actual word 'number'. Is there a SQL command I can use for this or do I need the help of a scripting language (php or vb)? I was trying to construct something like update into table.column select where column like '%#%' replace with '%number%' but of course that is not going to work. I am a SQL newb btw. Any help appreciated. Thanks Scott - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Question
Hello, Does anyone know where I should look on the http://www.mysql.com web site or anywhere else I can find documentation for being able to SSH to the box and setup additional databases as necessary using the MySQL command line tools? Scott Scott = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
