Re: NEWBIE to mysql
- Original Message - From: Wileynet [EMAIL PROTECTED] Sent: Friday, February 07, 2003 6:52 PM Subject: NEWBIE to mysql Can anyone tell me why I keep getting an ERROR 1064 with this command ? mysql CREATE TABLE info - ( - name varchar(50) - message varchar(255) - ) - ; You must separate field definitions with commas. Should be mysql CREATE TABLE info - ( - name varchar(50), - message varchar(255) - ) - ; Regards, Bruce - 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
Re: encrypted password
- Original Message - From: Jianping Zhu [EMAIL PROTECTED] Sent: Tuesday, February 04, 2003 1:58 PM I want the passwd field not to be plain text but encrypted. how can i do that? Use the password function: mysql create table users ( - name varchar(12), - pass varchar(16)); Query OK, 0 rows affected (1.14 sec) mysql insert into users values ('bruce',password('mine')); Query OK, 1 row affected (0.12 sec) mysql select * from users where pass = password('mine'); +---+--+ | name | pass | +---+--+ | bruce | 61ce8de3029ff1ab | +---+--+ 1 row in set (0.14 sec) See also http://www.mysql.com/doc/en/Miscellaneous_functions.html for a discussion of other encryption techniques, including MD5 and SHA1. Best regards, Bruce MacDonald Minnesota Public Radio, St. Paul USA - 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
Re: mysqldump ERROR 1064
- Original Message - From: David Angela Ehmer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 04, 2003 11:00 PM Subject: mysqldump ERROR 1064 Hi I am having problems generating the mysqldump file. I have tried a range of options and carefully studied several tutorials I have on using this command. Each time I get the following errror. ERROR 1064 you have an error in your SQL syntax near 'mysqldump -u root -p adrienne netno_db sql.dump' at line 1. I appear to have other problems with some commands. For example if I type; mysqladmin --help at the command line I get the 1064 ERROR Appreciate any suggestions about what may be the problem here David It looks like you're trying to run the mysqldump and mysqladmin commands from within the mysql client rather than from the shell's command line. Quit out of the mysql client and try running them from the command line. And when you specify the -p password on the command line, do not include a space between the -p and the password. Try instead: mysqldump -u root -padrienne netno_db sql.dump Best regards, Bruce - 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
Re: What, if anything, is wrong with UNIX Epoch time stamps?
Michael T. Babcock [EMAIL PROTECTED] wrote, But MySQL doesn't guarantee correctness in time values in the first place. You can still insert 2002-02-31 as a date if you like If you store a date in the database as a unix_timestamp in an unsigned int column, and retrieve the value using the inverse function, errors of this kind are self-correcting: mysql select unix_timestamp('2002-02-31'); +--+ | unix_timestamp('2002-02-31') | +--+ | 1015135200 | +--+ 1 row in set (0.51 sec) mysql select from_unixtime(1015135200); +---+ | from_unixtime(1015135200) | +---+ | 2002-03-03 00:00:00 | +---+ 1 row in set (0.03 sec) Maybe you don't want this kind of thing happening silently; in such a case you must validate the date in the API you're using before submitting the query. Another benefit of using unix_timestamps: daylight saving time/standard time issues are handled, because the unix_timestamps always relate to GMT. Date comparisons thus can take into account time-zone offset. -- Bruce - 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
Re: Possible date bug
John, Am I mistaken or does 2002-02-31 translate into February 31, 2002? I see it now -- right there on Page 646 of the MySQL Manual: | Note that MySQL does no checking whether the date is correct. | If you store an incorrect date, such as '1998-2-31', | the wrong date will be stored. If the date cannot be converted | to any reasonable value, a 0 is stored in the DATE field. | This is mainly a speed issue and we think it is up to the | application to check the dates, and not the server. Bruce MacDonald Minnesota Public Radio - 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
Re: Possible date bug
John, Am I mistaken or does 2002-02-31 translate into February 31, 2002? If it does ... Your observation is interesting. The MySQL manual says the query should set all zeroes if the date value is illegal. This does happen if you enter 32 days for a month, for example. Furthermore, I got two different unix_timestamp values in separate experiments when I queried the 31st-of-February date: mysql select td, unix_timestamp(td), from_unixtime(unix_timestamp(td)) from mine; +-++ ---+ | td | unix_timestamp(td) | from_unixtime(unix_timestamp(td)) | +-++ ---+ | 2002-02-31 00:00:00 | 1014962400 | 2002-03-01 00:00:00 | | 2002-11-31 00:00:00 | 1038722400 | 2002-12-01 00:00:00 | +-++ ---+ 2 rows in set (0.00 sec) ** Note it says March 1st in the third column. Then I decided to add a few more illegal values and do another from_unixtime conversion... mysql insert into mine values (2002-02-29); Query OK, 1 row affected (0.01 sec) mysql insert into mine values (2002-02-30); Query OK, 1 row affected (0.00 sec) mysql select td, unix_timestamp(td), from_unixtime(unix_timestamp(td)) from mine; +-++ ---+ | td | unix_timestamp(td) | from_unixtime(unix_timestamp(td)) | +-++ ---+ | 2002-02-31 00:00:00 | 1015135200 | 2002-03-03 00:00:00 | | 2002-11-31 00:00:00 | 1038722400 | 2002-12-01 00:00:00 | | 2002-02-29 00:00:00 | 1014962400 | 2002-03-01 00:00:00 | | 2002-02-30 00:00:00 | 1015048800 | 2002-03-02 00:00:00 | +-++ ---+ 4 rows in set (0.00 sec) ** Note that the unix_timestamp is different this time for the Feb 31st date and that it says March 3rd in the third column. I am running version 3.23.51 on Red Hat Linux release 6.0 (Hedwig) Kernel 2.2.5-15 on an i486. I have a scheduling application that uses unix_timestamps throughout (stored as unsigned INTs instead of DATETIMEs), and I haven't noticed any such problem with illegal date values. There must be an explanation but scouring the books and manual I could not find one. Bruce MacDonald Minnesota Public Radio - Original Message - From: John Griffin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 04, 2002 3:35 PM Subject: Possible date bug Hi All, I ran the following commands: DROP TABLE IF EXISTS test_date; CREATE TABLE test_date (test_date datetime); INSERT INTO test_date (test_date) VALUES ('2002-02-31'); SELECT * FROM test_date; I got the following results: mysql DROP TABLE IF EXISTS test_date; Query OK, 0 rows affected (0.02 sec) mysql CREATE TABLE test_date (test_date datetime); Query OK, 0 rows affected (0.03 sec) mysql INSERT INTO test_date (test_date) VALUES ('2002-02-31'); Query OK, 1 row affected (0.01 sec) mysql SELECT * FROM test_date; +-+ | test_date | +-+ | 2002-02-31 00:00:00 | +-+ 1 row in set (0.00 sec) mysql Am I mistaken or does 2002-02-31 translate into February 31, 2002? If it does ... I am running MySQL 3.23.41 on Windows 2000. John - 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
Re: Trigger
Neal, I need to build a cache mechanism for a website and I thought I'd use MySQL for persistance. The problem is that I need it to schedule a cleanup task every 24 hours - to remove old cache records. MS SQL Server provides a trigger for scheduling such tasks - I am looking for the functional analog on MySQL. Use a CRON job on the server to have mysql execute SQL statements in batch mode periodically. Write a shell script to invoke mysql in batch mode and take its input from a file. Here's one example: #! /bin/sh /usr/bin/mysql -h localhost -u username -ppassword database-name /path/to/commands.sql where the file 'commands.sql' contains the SQL statements to clean up the cache. Then add an entry to your CRONTAB. The following example would run the shell script at 2am each day: 0 2 * * * /path/to/shellscript See the CRONTAB manual on your system for details. If using Windows to run your MySQL server, use the cmd interpreter, the 'at' command, and the Schedule service. There are versions of CRON available for Windows, too. Bruce MacDonald Minnesota Public Radio - 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
Re: Date caculation is a select query
Chris, would someone mind giving me a example query where I would add 21 days to a datetime field Mysql, query mysql create table member ( - dt datetime ); Query OK, 0 rows affected (0.09 sec) mysql insert into member values ('1991-06-22 14:00:00'); Query OK, 1 row affected (0.05 sec) mysql select DATE_ADD(dt, INTERVAL 21 DAY) from member; +---+ | DATE_ADD(dt, INTERVAL 21 DAY) | +---+ | 1991-07-13 14:00:00 | +---+ 1 row in set (0.00 sec) Bruce MacDonald Minnesota Public Radio - 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