[Q] moving database to server in new timezone
I'm, using mysqldump to dump a complete database on a server in The Netherlands and want to load the generated SQL onto a server in the USA to make an exact copy of the database. However, I have Timestamp(14) fields that are dumped as e.g. 2004101015 local time and is read as local time too on the USA server. Ie, the time is then off by 10 odd hours. How can I dump the database to generate SQL timestamps in GMT and also make it being read as GMT on the other side? Thanks __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Q] Will an index increase performance here?
Table employee: employeeNumber : int (primary key) age: int salary : float Would an index improve performance for these queries? SELECT * FROM USERS ORDER BY age DESC LIMIT 100,200 (index on age) SELECT * FROM USERS ORDER BY salary DESC LIMIT 200,300 (index on salary) Is it OK if the index has duplicate keys (people with same age or salaries?) Thanks! __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Q] setting DATE field with default NOW value?
In a table definition, how do I get a date field to be assigned as DEFAULT the current date? I tried: dateField DATE DEFAULT CURDATE() but that gives me syntax errors Help? __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Q] INSERT INTO ... SELECT
Is there a limit on the number of rows that can be inserted in this way? Ie, at what point should I start worrying about out of memory errors or something similar? So, how many rows can/should be returned by the SELECT clause? __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL query help required
Hi, Background: I run a prediction league for soccer competitions. For every game, I want to show how many predictions were submitted per scoreline, ie. 10% said 1-0 20% said 0-1 30% said 2-3 etc. My prediction table has a predictionA and predicionB column with the submitted scores. Currently I do something like SELECT MAX(predictionA), MAX(predictionB) and then check for all the possible scorelines... if I got 2 and 3, then the possibilities would be: 2-0, 2-1, 2-2, 2-3 1-0, 1-1, 1-2, 1-3 0-0, 0-1, 0-2, 0-3 If the count for any predictions is 0 (ie no-one prediction that score), it gets omitted rather than saying 0%. These I do with individual selects in a code for loop. Is there a better way to do this with less calls to the database? Thanks! __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem setting/activating password
Hi, I am created a user by using: GRANT ALL ON *.* TO username@% IDENTIFIED BY password I did this from the mysql command line tool, logged in as root. The command succeeds and the new user is created in the mysql.user table, but the password does not work. I can only use this new user if I use a empty string password (no password). I also tried to do the SET PASSWORD afterwords, no luck. I then tried the Windows GUI / Administration to grant access and then set the password, but again, only an empty string password is accepted. I am using mysql 4.0.13. How do I actually get the password validation to be activated and instruct the server that the new user must provide his password? __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
user@% vs user@localhost question
This follows on a previous mail from me: When using GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY password I could not get the password authentication to kick in. Only supplying no password (empty string) succeeded. Even after doing SET PASSWORD and FLUSH PRIVILEGES. Then I tried GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY password And now it works the new password must be supplied for the user to logon. Does the % domain not include the localhost domain? If not, what is the use of the % domain? When should I use % and when localhost ? __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem setting/activating password
The problem is that when I only define the % host, then no password is required to log in... (not a wanted feature!). I CAN log in, i just have to specify no password. The password checking is only done for localhost --- [EMAIL PROTECTED] wrote: What about using the username only instead of username@% ? According to manual (section 7.34) it should be identic in behaviour: [...] The simple form user is a synonym for user@%. [...] Does it do the same? Lian P.S. Andy, sorry for posting by mistake to you. ;-| -Original Message- From: Riaan Oberholzer [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 9:19 AM To: [EMAIL PROTECTED] Subject: Problem setting/activating password Hi, I am created a user by using: GRANT ALL ON *.* TO username@% IDENTIFIED BY password I did this from the mysql command line tool, logged in as root. The command succeeds and the new user is created in the mysql.user table, but the password does not work. I can only use this new user if I use a empty string password (no password). I also tried to do the SET PASSWORD afterwords, no luck. I then tried the Windows GUI / Administration to grant access and then set the password, but again, only an empty string password is accepted. I am using mysql 4.0.13. How do I actually get the password validation to be activated and instruct the server that the new user must provide his password? __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]