Re: Date Field Reverting to 0000-00-00 Format
Michael Stassen wrote: Just a quick reply for now . . . Ummm, if you delete the numbers to the *left* of the decimal point, 2005-01-15 10:15:42.41837 will turn into .41837, which is still not a valid datetime. You need to delete the numbers to the *right* of the decimal point (which I expect you meant), *and* you need to delete the decimal point. Then you'll have a valid datetime (e.g. 2005-01-15 10:15:42). I meant to say to the *right*, including the decimal point. I still get the date column set to -00-00 when I upload the edited file. Here's an example of one of the tables where the date was retained successfully, with the string to the right of the decimal point being trimmed: 1 Cobweb 2005-01-13 15:21:50.654149 2 Lace Weight 2005-01-13 15:21:50.654149 3 Sock2005-01-13 15:21:50.654149 And the CREATE query for this table: CREATE TABLE StandardWeightType ( standard_wt_type_id int UNSIGNED NOT NULL AUTO_INCREMENT, standard_wt_desc varchar(50) NULL, standard_wt_lud datetime NULL, PRIMARY KEY (standard_wt_type_id) )ENGINE=MyISAM DEFAULT CHARSET=utf8; select * from standardweighttype: +-+--+-+ | standard_wt_type_id | standard_wt_desc | standard_wt_lud | +-+--+-+ | 1 | Cobweb | 2005-01-13 15:21:50 | | 2 | Lace Weight | 2005-01-13 15:21:50 | | 3 | Sock | 2005-01-13 15:21:50 | | 4 | Fingering| 2005-01-13 15:21:50 | | 5 | Baby | 2005-01-13 15:21:50 | | 6 | Sport| 2005-01-13 15:21:50 | | 7 | Double Knitting | 2005-01-13 15:21:50 | | 8 | Worsted | 2005-01-13 15:21:50 | | 9 | Aran | 2005-01-13 15:21:50 | +-+--+-+ I'm not sure why it worked for this table and not the Yarn table. I'll try out the proposed solutions later today. I do need to be able to use the date column since I will be inserting new records into the database via a web application that I will be developing; I am moving the data over from a PostgreSQL database which I'd developed earlier since I've decided to concentrate on MySQL for the time being. -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find records not in many-to-many table?
Obviously you can do a join when the names of id columns are different as well. Look in the doc for that. This has nothing to do with your problem of finding rows not in another table - it is a basic sql thing. Stefan Am Sunday 26 March 2006 00:47 schrieb barney: Thanks, Stefan, But that only works if both tables have the same field name, doesn't it? If I use select FileKey from dl_files left join dl_merges using (FileID) where FileID is null MySQL returns Unknown column 'articles.dl_files.FileID' in 'on clause'. Correct me if I'm wrong, but wouldn't I have to modify the column name in one of the tables in order for that to work? Or is there a syntax in the join lexicon that I can use to alias one of the column names within the query? I can't modify the existing table structure(s). Apologies if I'm being dense. Make a good day ... ... barn Use select first_table.id from first_table left join second_table using (id) where second_table.id is null Stefan Am Saturday 25 March 2006 19:10 schrieb barney: Folk, This may be off-base for this list, but I've run out of places to look, sorry. I can't seem to find this anywhere, although I'm certain I've seen it before. How can I identify all the records in a table that are not referenced in a many-to-many table? I have a [unique] table of files and a [unique] table of attributes. These are linked in a merge table which is many-to-many. I need to find all items in the file table that are not referenced in the merge table in order to add appropriate attributes for those records. The attribute list is 26-30 records and the file table is currently about 3,200 records, which could make for a merge table of 96,000 records. I tried using an IN statement against a sub-select of unique file ids in the merge table, but either that will not work or I did not craft it properly The query hit 6 million records before I aborted it sigh /. I'm certain this can be done ... I seem to remember a similar process from the DB2 corporate days ... but I just cannot wrap my head around it. Anybody have any ideas, please? Make a good day ... ... barn -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Z�lpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
getting started with mysql
I've just started trying to use mysql (debian/linux
4.0.24_Debian-10sarge1-log), which I'm finding quite frustrating.
I have a bunch of .csv files to try to import. They all have a first
line containing field names.
When I try load data ...
I get errors no matter what I try
mysql use milestone;
Database changed
mmysql load data local infile 'categories/milecodes.csv' into table
milecodes
- fields terminated by ',' enclosed by '' ignore 1 lines
- columns (key,year,where,content,form,itemform,itemcontent)
- ;
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 'columns (key,year,where,content,form,itemform,itemcontent)' at
m
mysql load data local infile 'categories/milecodes.csv' into table
milecodes
- fields terminated by ',' enclosed by '' ignore 1 lines
- ;
ERROR 1146: Table 'milestone.milecodes' doesn't exist
mysql drop table if exists milecodes;
Query OK, 0 rows affected (0.00 sec)
mysql create table milecodes
- (keyprimary key,
- yearint,
- where enum('Eur', 'NAmer', 'Other'),
- content char,
- formchar,
- itemformchar,
- itemcontent char
- );
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 'primary key,
yearint,
where enum('Eur', 'NAmer',
mysql
I get no more joy from mysqlimport. What is wrong?
% mysqlimport --force --local --ignore-lines=1 --fields-terminated-by
',' --fields-enclosed-by ''
--columns=key,year,where,content,form,itemform,itemcontent
mysqlimport Ver 3.4 Distrib 4.0.24, for pc-linux-gnu (i386)
Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Loads tables from text files in various formats. The base name of the
text file must be the name of the table that should be used.
If one uses sockets to connect to the MySQL server, the server will open and
read the text file directly. In other cases the client will open the text
file. The SQL command 'LOAD DATA INFILE' is used to import the rows.
Usage: mysqlimport [OPTIONS] database textfile...
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqlimport client
The following options may be given as the first argument:
--print-defaultsPrint the program argument list and exit
--no-defaults Don't read default options from any options file
--defaults-file=# Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read
--character-sets-dir=name
Directory where character sets are
--default-character-set=name
Set the default character set.
-c, --columns=name Use only these columns to import the data to.
Give the
column names in a comma separated list. This is
same as
giving columns to LOAD DATA INFILE.
-C, --compress Use compression in server/client protocol.
-#, --debug[=name] Output debug log. Often this is 'd:t:o,filename'
-d, --deleteFirst delete all rows from table.
--fields-terminated-by=name
Fields in the textfile are terminated by ...
--fields-enclosed-by=name
Fields in the importfile are enclosed by ...
--fields-optionally-enclosed-by=name
Fields in the i.file are opt. enclosed by ...
--fields-escaped-by=name
Fields in the i.file are escaped by ...
-f, --force Continue even if we get an sql-error.
-?, --help Displays this help and exits.
-h, --host=name Connect to host.
-i, --ignoreIf duplicate unique key was found, keep old row.
--ignore-lines=#Ignore first n lines of data infile.
--lines-terminated-by=name
Lines in the i.file are terminated by ...
-L, --local Read all files through the client
-l, --lock-tables Lock all tables for write.
--low-priority Use LOW_PRIORITY when updating the table
-p, --password[=name]
Password to use when connecting to server. If
password is
not given it's asked from the tty.
-P, --port=#Port number to use for connection.
-r, --replace If duplicate unique key was found, replace old row.
-s, --silentBe more silent.
-S, --socket=name Socket file to use for connection.
-u, --user=name User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
Variables (--variable-name=value)
and
Client does not support authentication protocol requested by server
Hello
No one at [EMAIL PROTECTED] seemed to know; i am posting here a few days.
A strange case - why would two seemingly identical circumstances yield these
different results?
Machines involved:
Machines A and B - two systems identical in OS versions and MySQL server
installations. A third machine (machine C) tries to connect to A and B, via
Perl DBI, via remote IP. Strangely, connection C-A succeeds, but when attempt
to connect C-B fails with the error in my subject line.
Both A and B are Fedora Core 4 (same kernel). Both have MySQL version
5.1.7.beta installed, configured with defaults, and the servers (and clients)
are running fine on both A and B. In both systems the file /etc/my.cnf has
the line old_passwords=1 (in the [mysqld] section).
I run a perl script from machine C, with exact same connecting code, (except
for the username, passowrd, database, etc.), looping over the two sets of
authentication tokens; a connection to A is established, but, trying to connect
to B, the script returns:
DBI connect('database=cif;host=xxx.xxx.xxx.xxx;port=3306','',...) failed:
Client does not support authentication protocol requested by server; consider
upgrading MySQL client at ./test_load_db.pl line 126
(actual tokens x-ed out)
Furthermore, when I try to connect from C to B, WITHOUT perl, just using the
command-line mysql client (on C)
(i.e.,
promptmysql -uusername -password -Dcif -hxxx.xxx.xxx.xxx
),
I succeed!
What could ever be the problem with the failing Perl DBI (C-B) connection, if
everything is OK with a C-A connection (with and without Perl), and, in
addition, a command-line C-B connection works?
Also, FWIW, I don't have root on machine C; i'm a mere client at the mercy of
administrators (I suppose I could install a local DBI, if need be, but fear it
might be a hastle and a lot of time, which I can't afford at the moment.)
TIA
andrew
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: getting started with mysql
Michael Friendly wrote:
I've just started trying to use mysql (debian/linux
4.0.24_Debian-10sarge1-log), which I'm finding quite frustrating.
I have a bunch of .csv files to try to import. They all have a first
line containing field names.
When I try load data ...
I get errors no matter what I try
Don't just try things in hopes of success. Look up the correct syntax in the
manual and use it. It's usually quicker and less frustrating that way.
mysql use milestone;
Database changed
mmysql load data local infile 'categories/milecodes.csv' into table
milecodes
- fields terminated by ',' enclosed by '' ignore 1 lines
- columns (key,year,where,content,form,itemform,itemcontent)
- ;
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 'columns (key,year,where,content,form,itemform,itemcontent)'
You have a syntax error, as the message is telling you. The word columns is
not part of the correct syntax, so you should leave it out. See the manual for
details http://dev.mysql.com/doc/refman/4.1/en/load-data.html.
You also have a problem with your first column name, which I describe below.
mysql load data local infile 'categories/milecodes.csv' into table
milecodes
- fields terminated by ',' enclosed by '' ignore 1 lines
- ;
ERROR 1146: Table 'milestone.milecodes' doesn't exist
Does it?
mysql drop table if exists milecodes;
Query OK, 0 rows affected (0.00 sec)
mysql create table milecodes
- (keyprimary key,
- yearint,
- where enum('Eur', 'NAmer', 'Other'),
- content char,
- formchar,
- itemformchar,
- itemcontent char
- );
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 'primary key,
You shouldn't use key as a column name, as it's a reserved word. It's
possible (if you quote it with backticks *every* time), but not a good idea.
Also, every column needs a type, including the primary key. Typically, you use
an unsigned, auto_increment INT column for the primary key. You probably need
something like
CREATE TABLE milecodes
( id INT UNSIGNED NOT NULL PRIMARY KEY,
year INT,
...
The manual gives the complete CREATE TABLE syntax
http://dev.mysql.com/doc/refman/4.1/en/create-table.html and full details on
column types http://dev.mysql.com/doc/refman/4.1/en/data-types.html.
I get no more joy from mysqlimport. What is wrong?
% mysqlimport --force --local --ignore-lines=1 --fields-terminated-by
',' --fields-enclosed-by ''
--columns=key,year,where,content,form,itemform,itemcontent
mysqlimport Ver 3.4 Distrib 4.0.24, for pc-linux-gnu (i386)
Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Loads tables from text files in various formats. The base name of the
text file must be the name of the table that should be used.
If one uses sockets to connect to the MySQL server, the server will open
and
read the text file directly. In other cases the client will open the text
file. The SQL command 'LOAD DATA INFILE' is used to import the rows.
Usage: mysqlimport [OPTIONS] database textfile...
Well, there's the syntax. Your command line, quoted above, has
mysqlimport [OPTIONS]
It appears that you specified neither the database nor the textfile.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to take dump of a query instead of table / database
select criteria into outfile name of output file The path for the output file must be writeable by the user underwhich mysql is running. -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com On Fri, 24 Mar 2006, Pure Web Solution wrote: you can manipulate mysqldump using the where clause (check the man) You might find the following usefull, but would not be so good for restoring. from the command line mysql --database=db_name --execute=select * from users -u username -ppassword output.file Pure Web Solution http://www.purewebsolution.co.uk PHP, MYSQL, Web Design Web Services abhishek jain [EMAIL PROTECTED] wrote: Dear Friends, I need to take the backup of a query, is it possible. If yes how. -- Regards Abhishek jain www.smsengine.co.uk Pure Web Solution http://www.purewebsolution.co.uk PHP, MYSQL, Web Design Web Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Client does not support authentication protocol requested by server
In the last episode (Mar 26), Andrew said: What could ever be the problem with the failing Perl DBI (C-B) connection, if everything is OK with a C-A connection (with and without Perl), and, in addition, a command-line C-B connection works? The password on B is a new-style password, and perl on C was built with old mysqlclient libraries? -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: /var/log/mysql.000001 Not Found?
David P. Donahue wrote: /usr/libexec/mysqld: File '/var/log/mysql.01' not found (Errcode: 13) [ERROR] Could not use /var/log/mysql for loggins (error 13). Turning logging off for the whole duration of the MySQL server process. [ERROR] Aborting If I touch the file, the error is for mysql.02, then mysql.03. What would be causing this, and how should I go about fixing it? Any help would be much appreciated, thanks. Running: perror 13 OS error code 13: Permission denied It would seem that /var/log/mysql.xxx isn't using the correct permissions For what its worth, you should be logging to /var/log/mysql/foo, and make sure that /var/log/mysql can be read/written to by the mysql server HTH -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528 Mobile: +614 12 593 292 / Skype: colincharles Using MySQL for a FLOSS application? http://dev.mysql.com/doc/refman/5.0/en/mysql-floss-license-exception.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Client does not support authentication protocol requested by server
In the last episode (Mar 26), Andrew said: What could ever be the problem with the failing Perl DBI (C-B) connection, if everything is OK with a C-A connection (with and without Perl), and, in addition, a command-line C-B connection works? The password on B is a new-style password, and perl on C was built with old mysqlclient libraries? I'm sorry, this seems to be getting overlooked, each time i attempt to convey it: For BOTH A and B, the Operating System, the Mysql server and client versions configurations, and (apparently) everything else relevant to mysql, IS IDENTICAL! (including firewall, etc.etc.) Yet, C connects to A, but not to B (within the SAME script and loop)! If it /is/ a matter of new-style vs. old-style password, PLEASE tell me where else to look. On both A and B, the files /etc/my.cnf are identical (with old_passwords=1 in each). Where else should I look for a difference in configurations? If C (with MySQL 4.0.25-standard-log) connects to A (5.1.7-beta), as it is now (WITHOUT any upgrading), doesn't that indicate that C is capable of connecting to B as well (B being identical to A)? (Something akin to transitivity here...) completely stumped. TIA andrew -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error Messages
rb wrote: Hi! Does anyone know the cause of the following error message Ignoring query to other database When I log onto the mysql monitor and use any command I get this message. This is strange because last time I used my copy of mysql it worked just fine. I am using MAC OSX and I was wondering if the latest OSX update may have caused this issue. This is the only change on my machine. Or may be I have just forgotten how to log in properly. Your MySQL client is configured to only run queries for a certain database and ignore all others. Do check your command line options and the my.cnf config file Also, is this with MySQL 5.0.19? -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528 Mobile: +614 12 593 292 / Skype: colincharles Using MySQL for a FLOSS application? http://dev.mysql.com/doc/refman/5.0/en/mysql-floss-license-exception.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Client does not support authentication protocol requested by server
Andrew
Your client application is trying to authenticate with the old mysql
method, you need to update your client script or alternatively reset
password to old version using:
SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');
Hope this helps
Regards
Jeremy
--
Jeremy Oliver
Bluejays PC Repair
tel 020 8656 1056
mob 07855833401
www.bluejayspc.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Field Reverting to 0000-00-00 Format
Lola J. Lee Beno wrote: snip Here's an example of one of the tables where the date was retained successfully, with the string to the right of the decimal point being trimmed: 1Cobweb2005-01-13 15:21:50.654149 2Lace Weight2005-01-13 15:21:50.654149 3Sock2005-01-13 15:21:50.654149 And the CREATE query for this table: CREATE TABLE StandardWeightType ( standard_wt_type_id int UNSIGNED NOT NULL AUTO_INCREMENT, standard_wt_desc varchar(50) NULL, standard_wt_lud datetime NULL, PRIMARY KEY (standard_wt_type_id) )ENGINE=MyISAM DEFAULT CHARSET=utf8; select * from standardweighttype: +-+--+-+ | standard_wt_type_id | standard_wt_desc | standard_wt_lud | +-+--+-+ | 1 | Cobweb | 2005-01-13 15:21:50 | | 2 | Lace Weight | 2005-01-13 15:21:50 | | 3 | Sock | 2005-01-13 15:21:50 | | 4 | Fingering| 2005-01-13 15:21:50 | | 5 | Baby | 2005-01-13 15:21:50 | | 6 | Sport| 2005-01-13 15:21:50 | | 7 | Double Knitting | 2005-01-13 15:21:50 | | 8 | Worsted | 2005-01-13 15:21:50 | | 9 | Aran | 2005-01-13 15:21:50 | +-+--+-+ I'm not sure why it worked for this table and not the Yarn table. I'll try out the proposed solutions later today. I do need to be able to use the date column since I will be inserting new records into the database via a web application that I will be developing; I am moving the data over from a PostgreSQL database which I'd developed earlier since I've decided to concentrate on MySQL for the time being. I take it back. It is true that the all-zero datetime is what you get for invalid input, so I jumped to the conclusion (sorry) that mysql was treating your datetimes with decimals as invalid. Your reply prompted me to try it, and I found, to my surprise, that mysql simply dropped the decimals. So, why isn't this happening for the yarn data? My best guess is that there is an extra tab right before the datetimes in yarn_date.txt. When you do the import, does mysql report any warnings? With an extra tab in each row of your 3-line sample data, I get Query OK, 3 rows affected, 6 warnings (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 6 at the end. If you have mysql 4.1 or higher, you can run SHOW WARNINGS; to get the details. With one extra tab per row, I got +-+--+---+ | Level | Code | Message | +-+--+---+ | Warning | 1264 | Data truncated; out of range for column 'yarn_lud' at row 1 | | Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns | | Warning | 1264 | Data truncated; out of range for column 'yarn_lud' at row 2 | | Warning | 1262 | Row 2 was truncated; it contained more data than there were input columns | | Warning | 1264 | Data truncated; out of range for column 'yarn_lud' at row 3 | | Warning | 1262 | Row 3 was truncated; it contained more data than there were input columns | +-+--+---+ 6 rows in set (0.00 sec) So, take a look at yarn_date.txt and let us know. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Client does not support authentication protocol requested by server
Andrew wrote: In the last episode (Mar 26), Andrew said: What could ever be the problem with the failing Perl DBI (C-B) connection, if everything is OK with a C-A connection (with and without Perl), and, in addition, a command-line C-B connection works? The password on B is a new-style password, and perl on C was built with old mysqlclient libraries? That is the most likely explanation, I think. I'm sorry, this seems to be getting overlooked, each time i attempt to convey it: For BOTH A and B, the Operating System, the Mysql server and client versions configurations, and (apparently) everything else relevant to mysql, IS IDENTICAL! (including firewall, etc.etc.) No. They are *supposed* to be identical, but clearly they are not. I think you're asking the wrong question. You are asking why two identical instances would behave differently. Well, they wouldn't. I think a better question is, what's different? Yet, C connects to A, but not to B (within the SAME script and loop)! Right. The exact same client works for A, but not for B. Thus, A and B are different. If it /is/ a matter of new-style vs. old-style password, PLEASE tell me where else to look. On both A and B, the files /etc/my.cnf are identical (with old_passwords=1 in each). Where else should I look for a difference in configurations? First, the global configuration file, /etc/my.cnf, can be overridden by a server-specific option file and/or command line options. You can verify what the server on B is doing with SHOW VARIABLES LIKE 'old_passwords'; One possibility is that this will return OFF for server B. I would *guess* that this is not your problem, but it is worth checking. Second, the difference in authentication is that before 4.1, mysql used a 16-byte password hash, while 4.1.1 and up use a 41 byte hash. Hence, the password column of the user table is 41 bytes long for both server A and B. Now, the old_passwords setting causes mysql to create *new* passwords as 16 byte hashes by default, *but it has no effect on pre-existing passwords*. One likely possibility is simply that the user's password was created on A *after* setting old_passwords to ON, but was created on B *before* setting old_passwords to ON. That would yield a password hash that is 16 bytes on server A, but 41 bytes on server B. As a result, old-style clients can connect to A, but not to B. In that case, you can fix this by resetting the user's password on server B. If C (with MySQL 4.0.25-standard-log) connects to A (5.1.7-beta), as it is now (WITHOUT any upgrading), doesn't that indicate that C is capable of connecting to B as well (B being identical to A)? (Something akin to transitivity here...) It would, if A and B were identical. Hence A and B are not identical. completely stumped. TIA andrew If neither of my suggestions helps, let us know. Someone will surely come up with another suggestion. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
deadlock - further information
Hello.
We have a web application for our intranet, which uses MySQL for db
management. The application has a way of logging query errors that are
generated in the system.
We get a mysql error recorded as - Deadlock found when trying to get
lock;
try restarting transaction when one of the files tries to create a
temporary table. The SQL query for it is as below -
CREATE TEMPORARY TABLE TBL_B AS SELECT TA.FLD_MSG_ID FROM TBL_A TA WHERE
{... conditions for selection ...}
I looked through the MySQL lists, but I could not get info as to why a
deadlock is created in this scenario. I also do not know how to
replicate
the scenario, as this error is not logged always. Is there a particular
case
when this happens?
I looked through
http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html and it says
SELECT ... FROM is a consistent read, reading a snapshot of the
database
and setting no locks ... So why is a lock being taken in the first
place?
I am at a loss. Can someone shed light on this, or point me to some
resource?
in 4.1.xx, you can use the my.cnf option innodb_locks_unsafe_for_binlog to
make InnoDB to use a consistent read in the SELECT tables in CREATE ...
SELECT. Read the caveats about the my.cnf option, though.
Thanks. However, whatever is written in
http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html on
innodb_locks_unsafe_for_binlog was enough to scare me to not use it in my
my.cnf :o)
Also I believe this variable is available as of MySQL 4.1.4, whereas ours in
4.1.11. Upgradation is not a viable option for us right now.
This is explained at:
http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html
I noticed that this behavior is broken in 5.0. I filed the bug report
http://bugs.mysql.com/bug.php?id=18350 about this. Thank you for bringing
this up.
No problemos.. :o)
Environment -
OS : RHEL 3
DB : MySQL 4.1.11 using INNoDB.
Regards,
Rithish.
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM
tables
http://www.innodb.com/order.php
Regards,
Rithish.
Re: deadlock - further information
Rithish Saralaya wrote: snip in 4.1.xx, you can use the my.cnf option innodb_locks_unsafe_for_binlog to make InnoDB to use a consistent read in the SELECT tables in CREATE ... SELECT. Read the caveats about the my.cnf option, though. Thanks. However, whatever is written in http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html on innodb_locks_unsafe_for_binlog was enough to scare me to not use it in my my.cnf :o) Also I believe this variable is available as of MySQL 4.1.4, whereas ours in 4.1.11. Upgradation is not a viable option for us right now. As 11 4, 4.1.11 is newer than 4.1.4, so this variable is available to you. 4.1.11 is almost a year old. Have you read the list of bugs fixed since then? http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbee error (1044)
Shawn Sharp wrote: I get the following error while trying to create the following database mysql mysql zm_create.sql.in ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' You are logged into mysql as the anonymous user, ''@'localhost'. By default, the anonymous user does not have permission to create databases. I tried to run the following script /usr/bin/mysql_install_db --user=mysql It does not create mysql database in the correct directory I still only see the 2 databases 2? I see only 1, test. That's to be expected, though. You can only see databases you have access to. The anonymous user is not authorized to access the mysql db, so it is not listed. Only dbs you have access to are listed. Typically, only the mysql root user has access to the mysql db, so it is only listed when you are logged in as root. /usr/bin/mysqlshow +---+ | Databases | +---+ | test | +---+ Thanks Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbee error (1044)
Shawn Sharp wrote: Dilipkumar, Thanks much for the tipit did the job! Now we query mysql to see if the new mysql database is seen by mysql and it still only sees test: No, you can't see the mysql db, because you don't have permission to access it. I expect you are still logged in as the anonymous user, who can only work with the test db. You have some reading to do. You've run mysql_install_db, now you need to secure the initial accounts: http://dev.mysql.com/doc/refman/4.1/en/default-privileges.html. Next you'll want to read up on how mysql's privilege system works: http://dev.mysql.com/doc/refman/4.1/en/privilege-system.html Then you should read about creating and managing user accounts: http://dev.mysql.com/doc/refman/4.1/en/user-account-management.html [EMAIL PROTECTED]:/usr/bin mysqlshow +---+ | Databases | +---+ | test | +---+ [EMAIL PROTECTED]:/usr/bin Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: deadlock - further information
As 11 4, 4.1.11 is newer than 4.1.4, so this variable is available to you. Gawwddd... I really made an ass of myself that time... :o) Thanks for pointing it out though. 4.1.11 is almost a year old. Have you read the list of bugs fixed since then? http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html Thanks for this too. However, db upgradation is not a viable option for us right now. However, we are sure to do that in the future. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld_safe and timezone settings
This was done as root and shows that TZ works. dk:/usr/local/mysql # bin/mysql -V bin/mysql Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using readline 5.0 dk:/usr/local/mysql # echo $TZ dk:/usr/local/mysql # bin/mysqld_safe --user=mysql [1] 802 dk:/usr/local/mysql # Starting mysqld daemon with databases from /usr/local/mysql/data dk:/usr/local/mysql # bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.18-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select now(); +-+ | now() | +-+ | 2006-03-27 09:26:35 | +-+ 1 row in set (0.05 sec) mysql Bye dk:/usr/local/mysql # support-files/mysql.server stop Shutting down MySQL...STOPPING server from pid file /usr/local/mysql/data/dk.pid 060327 09:26:45 mysqld ended done [1]+ Donebin/mysqld_safe --user=mysql dk:/usr/local/mysql # export TZ=America/Argentina/Mendoza dk:/usr/local/mysql # bin/mysqld_safe --user=mysql [1] 889 dk:/usr/local/mysql # Starting mysqld daemon with databases from /usr/local/mysql/data dk:/usr/local/mysql # bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.18-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select now(); +-+ | now() | +-+ | 2006-03-27 04:27:09 | +-+ 1 row in set (0.00 sec) mysql Bye -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
