Re: LOAD DATA INFILE (url)
Test USER wrote: Can't get this to work, but i would like to specify LOAD DATA to use an INFILE from an URL. For example LOAD DATA LOCAL INFILE 'http://www.testserver.com/data.csv' But i get an error message saying file not found. Anyone know if this is even possible ? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu it is possible . you have to write your own script which fetches the file (using wget) and placing the file in a directory where it can be accessed. then you can load that file. i currently have something like this running... i make a dump file on the remote server runing a script with crontab. i gzip that file just to make the file transfer faster . the local server then fetches the file (wget) and gunzips it and then i load the file. works pretty good. -- Arno Coetzee Developer Flash Media Group Office : +27 12 342 3490 Mobile : +27 82 693 6180 Fax : + 27 12 430 4269 www.flashmedia.co.za FMG Total messaging solution. For all your GSM (SMS and USSD) messaging needs. Quick and easy do-it-yourself interfaces. http://www.flashmedia.co.za/tms.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Gobi wrote: Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. select VBS_ID , max(Date) from weight -- Arno Coetzee Developer Flash Media Group Office : +27 12 342 3490 Mobile : +27 82 693 6180 Fax : + 27 12 430 4269 www.flashmedia.co.za FMG Total messaging solution. For all your GSM (SMS and USSD) messaging needs. Quick and easy do-it-yourself interfaces. http://www.flashmedia.co.za/tms.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting total results from a table
Alexandra wrote: Hello, I am trying to build a sql statement for MySQL 4.0.x which does count the accumulated total entries in a table per day since a specified start date. For example: Day 1 = 10 Day 2 = 12 Day 3 = 9 The statement I am using gives back the entries per day, starting each day with 0. For example: Day1 = 10 Day2 = 2 Day3 =0 (-3) code: SELECT DATE_FORMAT( timestamp, '%Y%m%d') AS mydate, count(*) AS ct, ID FROM $DB.$T4 WHERE (timestamp = '$date_start' AND timestamp = '.$date_end.235959') AND confirmed = '1' GROUP BY mydate Has anybody an idea how to recraft the statement to get the accumulated entries per day? Thank you for any help, Alexandra Hi Alexandra you were not far off give this a go SELECT DATE_FORMAT( timestamp, '%Y%m%d') AS mydate, count(*) AS ct FROM $DB.$T4 WHERE timestamp = '$date_start' AND timestamp = '.$date_end.235959') AND confirmed = '1' GROUP BY DATE_FORMAT( timestamp, '%Y%m%d') i don't know what the ID field in the select clause is for , so i omitted it. i suspect the ID field in the select clause caused the prob. shout if you need more help. -- Arno Coetzee Developer Flash Media Group Office : +27 12 342 3490 Mobile : +27 82 693 6180 Fax : + 27 12 430 4269 www.flashmedia.co.za FMG Total messaging solution. For all your GSM (SMS and USSD) messaging needs. Quick and easy do-it-yourself interfaces. http://www.flashmedia.co.za/tms.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore using mysqldump (MySQL 4.1.12)
John Doneker wrote: Hi, I am trying to restore from an .sql file created by mysqldump. To restore I am using mysqldump as well. I deleted all of the rows in a table. When I restore it is still empty. I noticed on my screen that the dump does inserts but the restore does not. I am using the -c option. Can someone please help? Thanks you all. Wayne. J. Wayne Doneker BAE Systems York Pa. 717 225 8109 Email: [EMAIL PROTECTED] try this wayne mysql 'databasename' 'filename' -- Arno Coetzee Developer Flash Media Group Office : +27 12 342 3490 Mobile : +27 82 693 6180 Fax : + 27 12 430 4269 www.flashmedia.co.za FMG Total messaging solution. For all your GSM (SMS and USSD) messaging needs. Quick and easy do-it-yourself interfaces. http://www.flashmedia.co.za/tms.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table
s. keeling wrote: Incoming from s. keeling: Incoming from Pooly: 2005/10/3, s. keeling [EMAIL PROTECTED]: I'd like to add a bit of history data to a table (who changed a record last, and when it was last changed). Is this the way to do it? [snip] alter table MEMBERS alter CHG_DATE set default CURRENT_DATE btw, you could do : Grr. Please, what's wrong with this?!? alter table MEMBERS alter MEMBER_INFO varchar(160); ERROR 1064: You have an error in your SQL syntax. Check the manual \ that corresponds to your MySQL server version for the right \ syntax to use near 'varchar(160)' at line 2 The field exists, but I'd like it to accept more chars. Debian/Gnu Linux Testing/Etch, MySQL 4.0 http://dev.mysql.com/doc/mysql/en/alter-table.html -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter table
s. keeling wrote: Incoming from Arno Coetzee: s. keeling wrote: Grr. Please, what's wrong with this?!? alter table MEMBERS alter MEMBER_INFO varchar(160); ERROR 1064: You have an error in your SQL syntax. Check the manual \ that corresponds to your MySQL server version for the right \ syntax to use near 'varchar(160)' at line 2 The field exists, but I'd like it to accept more chars. Debian/Gnu Linux Testing/Etch, MySQL 4.0 http://dev.mysql.com/doc/mysql/en/alter-table.html Yes, I've read it. Your point? Specifically? I am encoutering error 1064, surrounding table names and column names with backticks solves nothing. Is there something _specific_ on that page I'm missing? Thanks for trying. sorry ... bit busy on this side... had a quick look... give this a go... alter table MEMBERS MODIFY MEMBER_INFO varchar(160); hope this works -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find username password on tables
Scott Purcell wrote: Hello, A while back, I created a database, and performed the following: GRANT ALL ON util_db.* to XXX identified by XXX; Problem is, a year later, I need to find the username and password, so I can write to these tables. Can this be accomplished, I am the root user. Thanks, Scott use the mysql database and look in the user table .. you will be able to see all the users there , as well as the privileges. you will have to reset the password for the user you want to use... use the 'grant' statement to reset the password. hope this helps. -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql no series
prathima rao wrote: i have a table slno and percentage slno contains 1 to 15 when i say order by slno it always comes 1,10,11,12,13,14,15,2 i want it in the form 1,2,3,4,5,6,7,8,9,10,11 can u help rao - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: 04/10/2005 2:19 PM Subject: Re: [Fwd: MySQL in C - I need help!] is this fields datatype varchar? does it have to be a text field? change the datatype to int , double etc. depending on your needs -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I forgot the admin password
Johan Höök wrote: Hi Luis, you can start your server with --skip-grant-tables see: http://dev.mysql.com/doc/mysql/en/privileges-options.html /Johan Luis Garay wrote: hi im pretty newbie in mysql. i installed this in my computer few weeks ago and today i want to begin practicing and i cant log in . i suppose i forget the rigth password, how can a blank this?? thank you lgaray No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.3/107 - Release Date: 2005-09-20 I agree with Johan. When i was a newbie(not that i am an expert now ;-) ) i had the same prob - forgetting my root password. have a look at http://dev.mysql.com/doc/mysql/en/resetting-permissions.html good luck Luis -- Arno Coetzee Developer Flash Media Group Office : +27 12 342 7595 Mobile : +27 82 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error in Update Command Text - What am I missing
Blue Wave Software wrote: I'm getting an error from the following SQL Syntax. Can anyone guide me on what I am missing. It's probably obvious but I can't see it I have even resorted to rereading the section on the update command. The CustID Field is the unique Identifier and primary key of the table. The Updated field is a time stamp field. I've even removed the UPDATED='2005-09-18 10:30:56' with no success. Sql Syntax: UPDATE Clients_Master SET Label='', Title='Mrs', FName='Jane', MName='', SName='Doe', Building='', Unit='', STNumber='', STName='', STType='', STSuburb='', STState='', STPostcode='', Postal1='', Postal2='', Postal3='', PSuburb='', PState='', PPostalcode='', PBarcode='', HPhone='', MPhone='', HEmail='', MF='', Maiden='', ClientNo='999', Pcontact='', UPDATED='2005-09-20 04:46:37') WHERE CustID='MNO1234567890' AND UPDATED='2005-09-18 10:30:56' Error Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') WHERE CustID='MNO1234567890' AND UPDATED='2005-09-18 10:30:56'' at line 1 Tec Info: Server - MySQL 4.1.11-nt-max via TCP/IP Client - MySQL Client Version 5.0.4 Regards, Justin Elward Blue Wave Software Pty Limited [EMAIL PROTECTED] Ph. +61 2 4320 6090 Fx. +61 2 4320 6092 --- DISCLAIMER: This message is proprietary to Blue Wave Software Pty Limited (BWS) and is intended solely for the use of the individual or individuals to whom it is addressed. It may contain privileged or confidential information and should not be circulated with out informing BWS prior or used for any purpose other than for what it is intended. If you have received this message in error, please notify the originator immediately. If you are not the intended recipient, you are notified that you are strictly prohibited from using, copying, altering, or disclosing the contents of this message. BWS accepts no responsibility (except where required under Australian law) for loss or damage arising from the use or misuse of the information transmitted by this email including damage from virus. --- UPDATE Clients_Master SET Label='', Title='Mrs', FName='Jane', MName='', SName='Doe', Building='', Unit='', STNumber='', STName='', STType='', STSuburb='', STState='', STPostcode='', Postal1='', Postal2='', Postal3='', PSuburb='', PState='', PPostalcode='', PBarcode='', HPhone='', MPhone='', HEmail='', MF='', Maiden='', ClientNo='999', Pcontact='', UPDATED='2005-09-20 04:46:37' WHERE CustID='MNO1234567890' AND UPDATED='2005-09-18 10:30:56' try this exclude the closing bracket before the where clause. -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup and restore a database in a query #65311;
shuming wang wrote: Hi, Could we do a database dump/backup in a query like below ? mysqldump.exe --default-character-set=gb2312 --opt --host 192.168.0.1 -u root -p -C mydbmydbfile or restore a database in a query like below ? mysql.exe -h 192.168.0.1 -u root -p -C mydbmydbfile Then we can do backup and restore in GUI mode without call mysqldump.exe,mysql.exe in character mode . Best regard! Shuming Wang _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ Hi shuming i did not look at all your options , but ignoring the options it most def is possible. i am currently doing backups like this mysqldump -uuser -ppassword -hIPAddress -n -t dumpfile i then retrieve the files via ftp and then import them as follow mysql -uuser -ppassword -hIPAddress dumpfile hope this helps. contact me if you need any help. -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data sync offline
Alex Greg wrote: Hi, Our company is considering migrating some tablesfrom MyISAM to InnoDB, as it has row-level locking and other improvements over MyISAM. However, one of the things we do at the moment is rsync the MySQL data directory to our development server every night over an 2Mbps ADSL connection (as we have 40GB of data, downloading it all every night isn't a viable option). I am aware that you can't copy the InnoDB binaries around to different installations. Does anyone have any suggestions as to how we could continue to do incremental downloads of this data over a slow connection? Regards, -- Alex make a dump file of only the previous days data compress it , transfer the file , decompress it and import that days records into the other database. remember that you should have a complete copy of the the database on the other server before you start the daily process. -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order By Question
Johan Höök wrote: Hi, the basic thing is that you must never assume anything on what order you're getting your rows back if you're not using an order by. This said I guess one way for you to do this is to add a row-number column, preferbly auto-increment, and then order by that column. /Johan or maybe a datetime field and order by the datetime. i was never a big fan of auto-increment. ;-) -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
Jason Chan wrote: Jason Chan wrote: I have a student Table and a SubjectGrade table Create Table Student( StudentID INT NOT NULL, StudentName VARCHAR(30) ) Create Table SubjectGrade( StudentID INT NOT NULL, Subject VARCHAR(30) NOT NULL, Grade CHAR(1) ) let's say have following record in SubjectGrade 1MathsA 1PhysB 1ChemA 2MathsA 2ChemA 3BioC 3ChemA I want to find out students who have got A in both Maths and Chem How the SQL look like? select s.StudentID , s.StudentName from Student as s , SubjectGrade as sj where s.studentID = sj.studentID and sj.Grade = 'A' and (sj.Subject = 'Maths' or sj.Subject = 'Chem') I think your query will return student 3 as well My apologies. Misunderstood the question... -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practices for deleting and restoring records - moving vs flagging
Saqib Ali wrote: Hello All, What are best practices for deleting records in a DB. We need the ability to restore the records. Two obvious choices are: 1) Flag them deleted or undeleted 2) Move the deleted records to seperate table for deleted records. We have a complex schema. However the the records that need to be deleted and restored reside in 2 different tables (Table1 and Table2). Table2 uses the primary key of the Table1 as the Foriegn key. The Primary key for Table1 is auto-generated. This make the restoring with the same primary key impossible, if we move deleted data to a different table. However if we just flag the record as deleted the restoring is quite easy. Any thoughts/ideas ? There are pros and cons to both ways.(As you pointed out with moving the records to another table) I allways prefer flagging the records. The draw back with flagging the records is that you might sacrifice some speed(depends on the number of records in the table.) If the table does not grow that fast most def just flag the records as deleted. my2c worth -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practices for deleting and restoring records - moving vs flagging
Bastian Balthazar Bux wrote: We need to track the modification to the records too so the route has been to keep them all in a different, specular databases. If the real table look like this: CREATE TABLE `users` ( `id` int(11) NOT NULL auto_increment, `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `username` varchar(32) default NULL, `password` varchar(32) default NULL, PRIMARY KEY (`id`) ); The backup one look like this: CREATE TABLE `users` ( `del__id` int(11) NOT NULL auto_increment, `del__ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `del__flag` char(1) default 'D', `del__note` mediumtext, `id` int(11) NOT NULL auto_increment, `ts` datetime NOT NULL default '-00-00 00:00:00', `username` varchar(32) default NULL, `password` varchar(32) default NULL, PRIMARY KEY (`del__id`) ); That is the first one whit del__* fields added but all indexed removed. Having the same name and similar schema for the two tables make easier have a photo of the database in a defined time slice. Usefull with small, not very often changing databases. hi bastian just a thought. rather stay away from auto_increment PK's and rather generate your own PK. i have run into trouble a couple of times using auto_increment when i made backups and restored the data again. The PK changed and i had records in other tables referencing the old PK , but then the PK changed. -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
Jason Chan wrote: I have a student Table and a SubjectGrade table Create Table Student( StudentID INT NOT NULL, StudentName VARCHAR(30) ) Create Table SubjectGrade( StudentID INT NOT NULL, Subject VARCHAR(30) NOT NULL, Grade CHAR(1) ) let's say have following record in SubjectGrade 1MathsA 1PhysB 1ChemA 2MathsA 2ChemA 3BioC 3ChemA I want to find out students who have got A in both Maths and Chem How the SQL look like? select s.StudentID , s.StudentName from Student as s , SubjectGrade as sj where s.studentID = sj.studentID and sj.Grade = 'A' and (sj.Subject = 'Maths' or sj.Subject = 'Chem') -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
