Converting UTC to local time zones


I store hourly data in UTC DATETIME format. I'm trying to return the results in the local time zone for specified location (this one being in -06:00).

I'm a little new to using mysql functions, first off it returns the 26th twice, and second.. the data is wrong.

What am I missing

Code: mysql> select `snapshot` -> , min(temp_f) as min_temp -> , max(temp_f) as max_temp -> from KDFW -> where `snapshot` -> between date_sub(UTC_TIMESTAMP(),interval 6 day) -> and UTC_TIMESTAMP() -> group -> by DATE(CONVERT_TZ(snapshot,'-00:00','-06:00')) -> order by snapshot; +---------------------+----------+----------+ | snapshot | min_temp | max_temp | +---------------------+----------+----------+ | 2006-10-26 04:53:00 | 70 | 70 | | 2006-10-26 06:53:00 | 67 | 86 | | 2006-10-27 06:53:00 | 54 | 68 | | 2006-10-28 06:53:00 | 49 | 75 | | 2006-10-29 06:53:00 | 48 | 82 | | 2006-10-30 06:53:00 | 62 | 78 | | 2006-10-31 06:53:00 | 47 | 69 | +---------------------+----------+----------+ 7 rows in set (0.02 sec)

Thank You
Posted On: Monday 29th of October 2012 06:08:28 AM Total Views:  72
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




CONVERT() not converting properly

I have a table example: Code: id -- score 1 -- 5.66678 2 -- 11.598 3 -- 12.2 4 -- 11.598 5 -- 2.577777 score is set as a VARCHAR currently. id is an integer My simple MySQL query: Code: SELECT * FROM `mysql_table` ORDER BY CONVERT( score, DECIMAL ) DESC For some reason it pulls it something similar to this: Code: id -- score 2 -- 11.598 3 -- 12.2 4 -- 11.598 1 -- 5.66678 5 -- 2.577777 12.2 is stuck between the two 11.598's. Is there a reason for this Am I doing something wrong
VIEWS ON THIS POST

67

Posted on:

Wednesday 24th October 2012
View Replies!

Order by time (after converting to 24hr format)

I have three colums related to time in a table.(There are many other fields) 1. intHrs - To store Hour value of time 2. intMins - To store Minute value of time 3. vchAmPm - To store wheather it is AM or PM I want to select the rows order by time with 24 hour format something like Code: SELECT * FROM table WHERE condition ORDER BY ........
VIEWS ON THIS POST

84

Posted on:

Wednesday 24th October 2012
View Replies!

Will converting a column from enum to Varchar(20) cause data to be lost?

, We have a column that is currently enum, containing a selection of possible choices that are at maximum 15 Chars. We would like to change this column to be a Varchar(20), would this Alter keep the data in the current enum safe, that is convert them to be just Char data under the new Varchar(20) or will conversion from enum to Varchar cause the loss of data If yes, that is conversion from enum to varchat is not possible, what to you suggest as the best method to getting this objective realized
VIEWS ON THIS POST

152

Posted on:

Thursday 25th October 2012
View Replies!

#1045 - Access denied for user 'root'@'localhost' (using password: NO)

When getting this error after trying to login to your local mysql. Do the following: Open your mysql program folder. Find and click on : resetroot.bat Close xammp and restart. Let me know if this helps
VIEWS ON THIS POST

107

Posted on:

Thursday 25th October 2012
View Replies!

Xampp install :Cannot connect to sql on localhost 10061?

I recently installed xampp... Please bear with me as i am totally new to php & sql. I am not sure if i did something wrong when i installed xampp but i am having trouble getting mysql to work. I have turned on sql in the xampp control panel. I am trying to acess sql via the following, Start Menu / Run / CMD CD "C:\Program Files\xampp\mysql\bin\" mysql I get the following message ERROR 2003 : Cannot connect to sql on 'localhost' The command prompt stays as C:\Program Files\xampp\mysql\bin> Am i supposed to set a user name or password I thought i did but i have not done that - is that the problem As you can see - i don't really know what i am doing, so any help would be appreciated.
VIEWS ON THIS POST

64

Posted on:

Thursday 25th October 2012
View Replies!

Can't connect to local MySQL server through socket '/tmp/mysql.sock' (46)

Can't connect to local MySQL server through socket '/tmp/mysql.sock' (46) I get this error when I try to communicate with a table I made using a text file (last posted item). Leoj :: pass => for the table realestate... User-----Host---------Type------------Privileges----------Grant leoj------localhost-----wildcard: %-----ALL PRIVILEGES-------Yes yroot----localhost-----global-----------ALL PRIVILEGES-------Yes conn.php: PHP Code: realestate.sql: PHP Code: --phpMyAdminSQLDump--version2.6.1-rc1--http://www.phpmyadmin.net----Host:localhost--GenerationTime:Feb04,2005at01:35AM--Serverversion:4.0.22--PHPVersion:4.3.10----Database:`vangon77_dcb`----------------------------------------------------------------Tablestructurefortable`re_admin`--CREATETABLE`re_admin`(\t`AdminID`varchar(50)NOTNULLdefault'',\t`AdminPass`varchar(32)NOTNULLdefault'',\t`AdminName`varchar(100)NOTNULLdefault'',\t`AdminEmail`varchar(150)NOTNULLdefault'',\tPRIMARYKEY\t(`AdminID`))TYPE=MyISAM;----Dumpingdatafortable`re_admin`--INSERTINTO`re_admin`VALUES('test','test','YourName','gert@websitedesigns.co.za');--------------------------------------------------------------Tablestructurefortable`re_agents`--CREATETABLE`re_agents`(\t`AgentID`int(10)NOTNULLauto_increment,\t`username`varchar(50)NOTNULLdefault'',\t`password`varchar(32)NOTNULLdefault'',\t`FirstName`varchar(150)NOTNULLdefault'',\t`LastName`varchar(150)NOTNULLdefault'',\t`resume`textNOTNULL,\t`phone`varchar(50)NOTNULLdefault'',\t`cellular`varchar(50)NOTNULLdefault'',\t`pager`varchar(50)NOTNULLdefault'',\t`ResumeImages`textNOTNULL,\t`email`varchar(150)NOTNULLdefault'',\t`logo`varchar(255)NOTNULLdefault'',\t`RegDate`int(10)NOTNULLdefault'0',\t`ExpDate`int(10)NOTNULLdefault'0',\t`AccountStatus`varchar(20)NOTNULLdefault'pending',\t`PriorityLevel`int(1)NOTNULLdefault'0',\t`offers`int(3)NOTNULLdefault'0',\t`news`char(1)NOTNULLdefault'y',\t`NewsletterType`varchar(10)NOTNULLdefault'plain',\t`days10`char(1)NOTNULLdefault'n',\t`days5`char(1)NOTNULLdefault'n',\t`days1`char(1)NOTNULLdefault'n',\tPRIMARYKEY\t(`AgentID`),\tUNIQUEKEY`username`(`username`),\tUNIQUEKEY`email`(`email`))TYPE=MyISAMAUTO_INCREMENT=1;----Dumpingdatafortable`re_agents`----------------------------------------------------------------Tablestructurefortable`re_banners`--CREATETABLE`re_banners`(\t`ClientID`int(10)NOTNULLdefault'0',\t`BannerID`int(10)NOTNULLauto_increment,\t`BannerURL`varchar(255)NOTNULLdefault'',\t`BannerFile`varchar(255)NOTNULLdefault'',\t`BannerAlt`varchar(255)NOTNULLdefault'',\t`BannerType`varchar(50)NOTNULLdefault'',\t`bCat`int(10)NOTNULLdefault'0',\t`bSub`int(10)NOTNULLdefault'0',\tPRIMARYKEY\t(`BannerID`))TYPE=MyISAMAUTO_INCREMENT=1;----Dumpingdatafortable`re_banners`----------------------------------------------------------------Tablestructurefortable`re_categories`--CREATETABLE`re_categories`(\t`CategoryID`int(10)NOTNULLauto_increment,\t`CategoryName`varchar(255)NOTNULLdefault'',\tPRIMARYKEY\t(`CategoryID`),\tUNIQUEKEY`CategoryName`(`CategoryName`))TYPE=MyISAMAUTO_INCREMENT=5;----Dumpingdatafortable`re_categories`--INSERTINTO`re_categories`VALUES(3,'ForSale');INSERTINTO`re_categories`VALUES(4,'ForRent');--------------------------------------------------------------Tablestructurefortable`re_listings`--CREATETABLE`re_listings`(\t`ListingID`int(10)NOTNULLauto_increment,\t`AgentID`int(10)NOTNULLdefault'0',\t`CategoryID`int(10)NOTNULLdefault'0',\t`SubcategoryID`int(10)NOTNULLdefault'0',\t`address`textNOTNULL,\t`city`varchar(100)NOTNULLdefault'',\t`state`varchar(100)NOTNULLdefault'',\t`country`varchar(150)NOTNULLdefault'',\t`ShortDesc`textNOTNULL,\t`DetailedDesc`textNOTNULL,\t`Price`float(15,2)NOTNULLdefault'0.00',\t`PropertyType`varchar(50)NOTNULLdefault'',\t`neighbourhood`textNOTNULL,\t`rooms`int(2)NOTNULLdefault'0',\t`bathrooms`int(2)NOTNULLdefault'0',\t`fireplace`char(1)NOTNULLdefault'n',\t`garage`int(2)NOTNULLdefault'0',\t`SquareMeters`float(15,2)NOTNULLdefault'0.00',\t`LotSize`float(15,2)NOTNULLdefault'0.00',\t`HomeAge`int(3)NOTNULLdefault'0',\t`NearSchool`char(1)NOTNULLdefault'n',\t`NearTransit`char(1)NOTNULLdefault'n',\t`NearPark`char(1)NOTNULLdefault'n',\t`OceanView`char(1)NOTNULLdefault'n',\t`LakeView`char(1)NOTNULLdefault'n',\t`MountainView`char(1)NOTNULLdefault'n',\t`OceanWaterfront`char(1)NOTNULLdefault'n',\t`LakeWaterfront`char(1)NOTNULLdefault'n',\t`RiverWaterfront`char(1)NOTNULLdefault'n',\t`image`textNOTNULL,\t`DateAdded`int(10)NOTNULLdefault'0',\t`visits`int(10)NOTNULLdefault'0',\tPRIMARYKEY\t(`ListingID`))TYPE=MyISAMAUTO_INCREMENT=1;----Dumpingdatafortable`re_listings`----------------------------------------------------------------Tablestructurefortable`re_mail_archive`--CREATETABLE`re_mail_archive`(\t`subject`varchar(255)NOTNULLdefault'',\t`message`text,\t`MailDate`int(10)NOTNULLdefault'0')TYPE=MyISAM;----Dumpingdatafortable`re_mail_archive`----------------------------------------------------------------Tablestructurefortable`re_prices`--CREATETABLE`re_prices`(\t`PackageName`varchar(50)NOTNULLdefault'',\t`PriceID`int(10)NOTNULLauto_increment,\t`PriceValue`float(5,2)NOTNULLdefault'0.00',\t`Duration`varchar(10)NOTNULLdefault'',\t`PriorityLevel`int(1)NOTNULLdefault'0',\t`offers`int(10)NOTNULLdefault'0',\tPRIMARYKEY\t(`PriceID`))TYPE=MyISAMAUTO_INCREMENT=7;----Dumpingdatafortable`re_prices`--INSERTINTO`re_prices`VALUES('GrowUp',5,25.00,'1',3,15);INSERTINTO`re_prices`VALUES('StartMeUp!',4,14.95,'1',1,3);INSERTINTO`re_prices`VALUES('ShakeUp',6,34.95,'2',4,10);--------------------------------------------------------------Tablestructurefortable`re_priority`--CREATETABLE`re_priority`(\t`PriorityID`int(10)NOTNULLauto_increment,\t`PriorityName`varchar(50)NOTNULLdefault'',\t`PriorityLevel`int(1)NOTNULLdefault'0',\tPRIMARYKEY\t(`PriorityID`),\tUNIQUEKEY`PriorityName`(`PriorityName`))TYPE=MyISAMAUTO_INCREMENT=8;----Dumpingdatafortable`re_priority`--INSERTINTO`re_priority`VALUES(1,'Premium',5);INSERTINTO`re_priority`VALUES(2,'Gold',4);INSERTINTO`re_priority`VALUES(3,'Platinium',3);INSERTINTO`re_priority`VALUES(4,'Bronze',2);INSERTINTO`re_priority`VALUES(7,'Standart',1);--------------------------------------------------------------Tablestructurefortable`re_settings`--CREATETABLE`re_settings`(\t`id`int(1)NOTNULLdefault'0',\t`SiteTitle`varchar(255)NOTNULLdefault'',\t`SiteKeywords`varchar(255)NOTNULLdefault'',\t`SiteDescription`text,\t`ContactEmail`varchar(150)NOTNULLdefault'',\t`CompanyAddress`text,\t`PayPalEmail`varchar(150)NOTNULLdefault'',\t`SellerID`int(5)NOTNULLdefault'0',\t`Agreement`textNOTNULL)TYPE=MyISAM;----Dumpingdatafortable`re_settings`--INSERTINTO`re_settings`VALUES(1,'RealEstates','realestate,sales,rent,forrent,forsale,house,houses,apartment,apartments,studio,studios,rooms','FindpropertyforsaleandrentaccrosstheUSA','gert@websitedesigns.co.za','Kingston,23Str.\r\n23332State','gert@websitedesigns.co.za',84734,'yourtext\r\n\r\nsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfg\r\n\r\nsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfgsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfg\r\n\r\n\r\nsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfgsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfgsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfgsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfg\r\n\r\nsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfg\r\n\r\nsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfgsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfgsdfsdfgsdfgsdfgsdfgsdfsdfgsdfgsdfgdfg');--------------------------------------------------------------Tablestructurefortable`re_stats`--CREATETABLE`re_stats`(\t`BannerID`int(10)NOTNULLdefault'0',\t`impressions`int(10)NOTNULLdefault'0',\t`clicks`int(10)NOTNULLdefault'0',\t`mydate`int(10)NOTNULLdefault'0',\t`ip`varchar(50)NOTNULLdefault'')TYPE=MyISAM;----Dumpingdatafortable`re_stats`----------------------------------------------------------------Tablestructurefortable`re_subcategories`--CREATETABLE`re_subcategories`(\t`SubcategoryID`int(10)NOTNULLauto_increment,\t`SubcategoryName`varchar(255)NOTNULLdefault'',\t`CategoryID`int(10)NOTNULLdefault'0',\tPRIMARYKEY\t(`SubcategoryID`))TYPE=MyISAMAUTO_INCREMENT=10;----Dumpingdatafortable`re_subcategories`--INSERTINTO`re_subcategories`VALUES(3,'Houses',4);INSERTINTO`re_subcategories`VALUES(4,'Appartments',4);INSERTINTO`re_subcategories
VIEWS ON THIS POST

267

Posted on:

Thursday 25th October 2012
View Replies!

SELECT command denied to user 'zxcvbnm'@'localhost' for table 'id'

; There is a simple (i'd even say typo) error in this query (the red part). The red part has an extra part .id But how come I get the following error: SELECT command denied to user 'zxcvbnm'@'localhost' for table 'id' ================================= SELECT cmsmembers.id FROM cms_members INNER JOIN . . . INNER JOIN cms_group.id ON cms_group.id = cms_group_members.group_id WHERE cms_group_members.member_id=2 ================================== Its missleading I guess, why not the classic "#1064 - You have an error in your SQL syntax;"
VIEWS ON THIS POST

84

Posted on:

Monday 29th October 2012
View Replies!

Problem converting a MS SQL function to a MYSQL function

I am trying to convert the followng MS SQL funtion to a MYSQL function CREATE FUNCTION formatFloatToMin (@unit float(15)) RETURNS nvarchar(30) AS BEGIN declare @Return nvarchar(30) select @return = convert(varchar(5),dateadd(mi,@unit*60,0),108) return @return end Can anyone please help me I am very new to MYSQL and have a task of migrating a MS SQL db to MYSQL!!
VIEWS ON THIS POST

111

Posted on:

Monday 29th October 2012
View Replies!

Can't connect to MySQL server on 'localhost'

I'm on XP, running MySQL along with php. I used some tutorial to setup the server and a database. Yesterday everything was working great, but then today I got that error (the one which is the title of this post). After some searching it seems the problem is likely that sever just isn't on. But how do I know if it's on Also, I've tried "mysqld start" in the command line but it didn't change anything. Please help,
VIEWS ON THIS POST

234

Posted on:

Monday 29th October 2012
View Replies!

Error 1242 - but works in localhost

I have a update query that fail with the error "Subquery returns more than 1 row" The statement: update table1 as t1 left join table2 as t2 on ( t1.item_id = t2.itemid ) left join table3 as t3 on ( t2.model_id = t3.modelid ) set t1.item_id = ( select itemid from table2 where t3.modelname = 'Vectra' and itemname = 'Kit' ) -- this select returns only one register where t3.modelname = 'Vectra' and t2.itemname = '125' -- this returns some rows. A similar select statement works: select * from table1 as t1 left join table2 as t2 on ( t1.item_id = t2.itemid ) left join table3 as t3 on ( t2.model_id = t3.modelid ) where t3.modelname = 'Vectra' and t2.itemname = '125' -- this returns some rows. Some advice
VIEWS ON THIS POST

45

Posted on:

Monday 29th October 2012
View Replies!

Error 2003: Can't connect to MySQL server on 'localhost' (99)

I am facing some kind of problem. The error I am getting it's look like "Errorno = 2003 Error Message = Can't connect to MySQL server on 'server IP address' (99)". This error is coming most of the time. 1) the server is also running when I got this error. 2) I checked the config file, there is no parameter defined for the mysql port, it might be using the default port no 3306. 3) I have checked the server with telnet also, I can connect to the server with the default port no, it's showing connected. Can anybody help me ...
VIEWS ON THIS POST

90

Posted on:

Monday 29th October 2012
View Replies!

What is ODBC@localhost error?

Any expert help me on this Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:\xampp\htdocs\index.php on line 9
VIEWS ON THIS POST

139

Posted on:

Monday 29th October 2012
View Replies!

\ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

just five minutes before mysql was working fine... in directory /var/lib/mysql i renamed a database. now it is giving the following error... \ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Please somebody help.... Im working in Fedora 8
VIEWS ON THIS POST

182

Posted on:

Monday 29th October 2012
View Replies!

Error #1267 - Problem with MYSQL code. Works locally but not on server

, I have a mysql join with some criteria which works perfect locally. i test the mysql in phpmyadmin and it generates the output perfectly, I paste the EXACT same mysql into a clone database on my server and i get this error: #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_bin,IMPLICIT) for operation '=' Does anyone have any idea why this might be happening I have run out of things to try, i simply don't understand how it can work locally but not on my website's server...
VIEWS ON THIS POST

191

Posted on:

Monday 29th October 2012
View Replies!

Config problem, localhost OK, host not

I've clearly doing something wrong. I've installed MySql 5.0.51a-3ubuntu5.1-log on my laptop. (i've been an amateur sysadmin on linux with mysql for a decade). If I connect to it using "localhost" or no host name, it all works perfectly. If I specify a hostname, it fails. Code: mysql mysql works fine. Code: mysql mysql -h localhost works fine. Code: mysql mysql -h tools.pfarrell.com fails with ERROR 2003 (HY000): Can't connect to MySQL server on 'tools.pfarrell.com' (111) Of course, tools.pfarrell.com is the laptop; Localhost resolves as always to 127.0.0.1 and tools.pfarrell.com resolves to (172.16.4.72) which the wireless card's IP on the laptop. I don't know of any firewall on the box. A simple telnet test shows that nothing is listening to port 3306 with the name, i.e. Code: telnet localhost 3306 returns some server stuff but Code: telnet tools.pfarrell.com 3306 Trying 172.16.4.72... telnet: Unable to connect to remote host: Connection refused Thus I assume I have something configured wrong, but have no idea what.
VIEWS ON THIS POST

47

Posted on:

Monday 29th October 2012
View Replies!

Tool(s) for keeping localhost dbs in synch?

, Have this recurring issue, and I don't know any good solutions. Hoping someone can lend some insite. At my workplace, we mainly develop locally and then push our changes onto a remote server. What ends up happening is over time, with multiple people using/updating the remote server, my localhost dbs get out of sync with the db on the server. I would like an easy way to keep my local db fairly up to date with the server db. Any good tools to do this Ideally I would like some sort of tool that lets me click and get a dump of remote db, and then put that in my local db. Files pose similar problems, but right now we are using Subversion to keep them up to date.
VIEWS ON THIS POST

62

Posted on:

Monday 29th October 2012
View Replies!

Major mysql slowdown out of the blue on localhost

Alright, so I just switchted to my laptop from my desktop which is running the following config: PHP 5.2.5 Mysql 5.0.45 Pretty much the default wamp server 2.0 config and the mysql imports I'm runing right now for a pretty small database (I'm pretty sure it's empty) with 38 tables is taking 750s... WTF I recently installed the following software, but I don't see how it would make a difference: Azuereus Vuze Peergaurdian Flashget Blocklist manager Furthermore while the import is running everything is significantly slowed down, ie launching applications is taking way longer. The file I am importing is 2.43mb and is taking 700s... Something is wrong right I'm pretty sure it was way faster before. I dont think there is a significant downturn for performance for the overall computer. Anyway ideas on how to benchmark this I might just reinstall wamp, because I dont see any other options. It seems like a really ambigious issue. , I'm home for the weekend from college, I will have access back to desktop on Monday. So... obviously there is something wrong. The mysql database is not running any queries and the computer is pretty much idle. I am tyring to execute the following query and it is saying that there is a syntax error at 'mysql etc.': mysql -u root -p -h localhost makemylifefun d:/wamp/mysql_imports/littlebops.sql I do have a password... Should i enter it after the -p command Really sorry. I do google and i feel embarrased for asking for help with commands... http://www.cyberciti.biz/faq/import-mysql-dumpfile-sql-datafile-into-my-database/
VIEWS ON THIS POST

129

Posted on:

Monday 29th October 2012
View Replies!

Need to move sql database to local for new CMS website design

, I am in the process of a website redesign with Drupal CMS and after reading through the tutorials, I understand I have to move SQL over to my local so I can start on the new CMS design for the website. Here are the steps I've taken.... 1. installed XAMPP (using this software so I don't have to install an apache web server, I've heard it's very difficult. 2. installed Navicat (this software is a database server admin and development tool. It lets you access and share infomation in a secure way. 3. I have made the connection to the database on my hosts server (using Navicat software) to the new Drupal databased I've created. 4. Here's where I'm stuck. I don't understand how to move a production copy of the new Drupal database over to my local using the XAMPP. 5. It's my understanding that while I'm doing the congfiguration of the new CMS for the new website that you need the database on the local so it can read and write some of the files. 6. Obviously I can't upload and do the design work from the hosts server because it will overwrite some of the files that are currently on the hosts server that my current php language is using. (I currently have a SQL databased for a forum, (2) CMS databases for the existing website). Any help would be appreciated on how-to get the new Drupal database to the local using XAMPP so I can start the website redesign locally using Drupal CMS.
VIEWS ON THIS POST

70

Posted on:

Monday 29th October 2012
View Replies!

Remote update of MySQL from local Access file

Hi all, hoping to get a little direction here! We have a local MS Access database with customer info. This info is also available on a PHP 4.4.2/MySQL 4.1.21 powered website on a Windows server . We manually update this info every so often, but would prefer to have an automatic system in place. Is it possible to do this Would we have an easier time on a Linux server running Apache Any and all help/direction is appreciated.
VIEWS ON THIS POST

69

Posted on:

Monday 29th October 2012
View Replies!

Transfer from remote server to localhost not going well!!

Here's my situation and history of trying to resolve this, in as much detail as I can think of providing. Maybe someone can tell me what I'm doing wrong. (In all of the following: names, actual passwords and db names have been replaced with fictional ones.) Here's my basic setup for the task, which is to restore a database from the remote server to my local machine. MySQL installation: ....C:\Program Files\MySQL\MySQL Server 5.0 Binary file: ....C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe Database backup file (forum.sql): ....C:\Program Files\MySQL\MySQL Server 5.0\data\forum.sql Database folder (forum): ....C:\Program Files\MySQL\MySQL Server 5.0\data\forum My goal is to import forum.sql into forum. A simple database restoration, right So I start with Start -> Run -> cmd First thing that appears on the screen is: Quote: Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Documents and Settings\John Smith> I typed: cd C:\ This is what appeared: C:\> Then I typed: C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe -uroot -pbigbird forum < C:\Program Files\MySQL\MySQL Server 5.0\data\forum.sql And got this error: Quote: The system cannot find the file specified. The file is where I said it is. Still, I changed the location, putting it right in C:\ itself (C:\forum.sql) Then I typed: C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe -uroot -pbigbird forum < C:\forum.sql And got this error: Quote: 'C:\Program' is not recognized as an internal or external command, operable program or batch file. huh\t Why didn't it say that before\t So I tried putting double quotes around program files, like so: C:\"Program Files"\MySQL\MySQL Server 5.0\bin\mysql.exe -uroot -pbigbird forum < C:\forum.sql And got this error: Quote: 'C:\"Program Files"\MySQL\MySQL' is not recognized as an internal or external command, operable program or batch file. hmmmmm... So I tried double quotes around all multi-word spaced phrases: C:\"Program Files"\MySQL\"MySQL Server 5.0"\bin\mysql.exe -uroot -pbigbird forum < C:\forum.sql Aha! I thought it was going to work! But then this error came up: Quote: ERROR 1153 (08S01) at line 603: Got a packet bigger than 'max_allowed_packet' bytes Okay, so I increased the max_allowed_packet, restarted the MySQL service, and tried again the same command: C:\"Program Files"\MySQL\"MySQL Server 5.0"\bin\mysql.exe -uroot -pbigbird forum < C:\forum.sql Now I'm getting this error: Quote: ERROR: Unknown command '\"'. ERROR: Unknown command '\b'. ERROR: Unknown command '\m'. ERROR: Unknown command '\n'. -> *scratches head* So I removed the double quotes and tried again: C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe -uroot -pbigbird forum < C:\forum.sql Producing this error: Quote: ERROR: Unknown command '\P'. ERROR: Unknown command '\M'. ERROR: Unknown command '\M'. ERROR: Unknown command '\b'. ERROR: Unknown command '\m'. ERROR: Unknown command '\n'. -> I've tried reversing slashes, tried with or without semi-colons at the end. Nothing is working. So yeah, I'm really stumped here. What is the problem\t
VIEWS ON THIS POST

75

Posted on:

Monday 29th October 2012
View Replies!