Re: MySQL Error 1045
Hi, On 21-9-2010 5:25, Tim Thorburn wrote: Ignore that ... it's amazing how you can solve problems with enough caffeine and enough time away from a computer screen >.> It's also amazing how frustrating it is for those who are searching for the problem you mentioned to only find threads with 'solutions' such as "ignore this", "found it myself", "never mind, solved", etc. -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Error 1045
Ignore that ... it's amazing how you can solve problems with enough caffeine and enough time away from a computer screen >.> On 9/20/2010 10:58 PM, Tim Thorburn wrote: Hello, A few days ago I ran into Error 1045 when attempting to add a new database user to my development machine. The error only occurred when trying to connect to the database as a new user which I found a little odd. After searching Google I discovered that this error is caused by a timeout - I should probably mention that my development machine is Win7 Ultimate 64-bit; I was using MySQL 5.1.45. Any thoughts on what I can do now? TIA, -Tim
MySQL Error 1045
Hello, A few days ago I ran into Error 1045 when attempting to add a new database user to my development machine. The error only occurred when trying to connect to the database as a new user which I found a little odd. After searching Google I discovered that this error is caused by a timeout - I should probably mention that my development machine is Win7 Ultimate 64-bit; I was using MySQL 5.1.45. Having no luck solving error 1045 I chose to uninstall MySQL and re-install the most up to date version from mysql.com being 5.1.50, again choosing Windows 64-bit. To uninstall I first went to the control panel to remove MySQL, however this did not remove the Windows service. I did find that running the installer file will remove existing services - which is how I was finally able to remove it. I believe I've now gotten as close to a clean uninstall of MySQL as I can at this point. When I run the installer now it gets up to the final processing configuration stage, but when it attempts to apply security settings I'm again presented with the dreaded error 1045 Access denied for user 'r...@localhost' (using password: YES). The error message goes on to suggest that I open TCP port 3306 in my firewall. This machine came pre-installed with McAfee AntiVirus Plus - for good or bad it's worked fine for me up until this point. I've opened port 3306 under Ports and System Services; yet the error still persists. Any thoughts on what I can do now? TIA, -Tim
Re: SHA1 returns binary value
On Sep 17, 2010, at 7:08 AM, Tompkins Neil wrote:
> Hi
>
> Why when I run the command (MySQL 5.1) SELECT SHA1('abc'); is it returned as
> a binary value and not a string value ?
If you mean, why is it a binary rather than a nonbinary (character) string,
that's true of most of the encryption or compression functions in 5.1. This was
changed
in 5.5. http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html says:
"
Many encryption and compression functions return strings for which the result
might contain arbitrary byte values. If you want to store these results, use a
column with a VARBINARY or BLOB binary string data type. This will avoid
potential problems with trailing space removal or character set conversion that
would change data values, such as may occur if you use a nonbinary string data
type (CHAR,VARCHAR, TEXT).
Some encryption functions return strings of ASCII characters: MD5(),
OLD_PASSWORD(), PASSWORD(), SHA(),SHA1(). As of MySQL 5.5.3, their return value
is a nonbinary string that has a character set and collation determined by the
character_set_connection and collation_connection system variables. Before
5.5.3, these functions return binary strings. The same change was made for
SHA2() in MySQL 5.5.6.
"
--
Paul DuBois
Oracle Corporation / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Encryption with MYSQL
Hi,
On Mon, Sep 20, 2010 at 4:59 AM, Tompkins Neil
wrote:
> Any ideas why my statement like SELECT SHA1('abc') AS my_sha is being
> returned as binary value, I thought it should be returning HEX in anycase ?
Maybe because that's what it's intended to do.
http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_sha1
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: numbering the result set rows for insertion into another table
On Mon, Sep 20, 2010 at 7:36 AM, Shawn Green (MySQL) wrote: > Hello Hank, > > On 9/18/2010 9:35 PM, Hank wrote: >> >> I have the following pseudo code running on mysql 4.x: >> >> set @cnt:=0; >> insert ignore into dest_table >> select t1.field1, t1.field2, t1.field3, t2.field1, >> t1.field3, t2.ts, @cnt:=...@cnt+1 >> from table1 as t1 left join table2 as t2 using (field1, field2) >> order by t2.ts; >> >> This works perfectly to sequentially number the result set rows >> inserted into dest_table in order of t2.ts (a timestamp field). >> >> In my upgrade to mysql 5.1.14-community, the numbers returned by @cnt >> are not in order... they trend upward from 0 to the number of records >> inserted, but they're far from "in order"... so somehow mysql is >> inserting the rows in some strange order. >> >> How can I fix this so it works in both mysql 4.x and 5.x? >> > > I am not sure you can fix this to work properly in a single statement for > 5.1.14. The order of operations appears out of sequence to what you need. > > When executing an SQL statement, there are several stages to the processing. > 1)gather rows and filter on matches (FROM ... and JOIN ...) > 2)filter the results of 1 (WHERE) > 3)apply any GROUP BY > 4)filter the results of 3 (HAVING) > 5)sort the results (ORDER BY) > 6)window the results (LIMIT) > > It appears that computation of your @cnt variable is performed BEFORE the > ORDER BY and not after the ORDER BY. This is completely in line with how > the SQL Standard says a query should operate. What if you wanted to ORDER > BY on the @cnt column and we did not compute it until after that stage of > processing? That would break standards compatibility. To make this work the > way you want, you need to create a temporary table with the results of your > query sorted the way you want them. Then, query that temporary table and add > your column of sequential numbers to the first results. > > > There may possibly be a saving grace for you, though. 5.1.14 was a very > early release in the 5.1 series. It is possible that someone else noticed > the same problem and a later version may be operating as you want. We are > currently releasing 5.1.50 which contains 34 rounds of bugfixes above and > beyond your current 5.1.14. I suggest you upgrade and try again. Even if > this does not fix the behavior to act as you want, the upgrade will at least > remove your exposure to hundreds of identified bugs. > > -- > Shawn Green > MySQL Principal Technical Support Engineer > Oracle USA, Inc. > Office: Blountville, TN > Hello Shawn, Many thanks for your detailed reply. This is a test/dev box which I do plan to upgrade to the newest mysql version (5.1.x or maybe 5.5.x) in a couple of weeks. But I found a solution to my problem... I'm not setting the @cnt value in the statement, but I added a second statement right after it to do this, which works as I intended: set @cnt:=0; update dest_table set hc...@cnt:=...@cnt+1 order by ts; This works for both mysql 4.x and 5.1.15. -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: numbering the result set rows for insertion into another table
Hello Hank, On 9/18/2010 9:35 PM, Hank wrote: I have the following pseudo code running on mysql 4.x: set @cnt:=0; insert ignore into dest_table select t1.field1, t1.field2, t1.field3, t2.field1, t1.field3, t2.ts, @cnt:=...@cnt+1 from table1 as t1 left join table2 as t2 using (field1, field2) order by t2.ts; This works perfectly to sequentially number the result set rows inserted into dest_table in order of t2.ts (a timestamp field). In my upgrade to mysql 5.1.14-community, the numbers returned by @cnt are not in order... they trend upward from 0 to the number of records inserted, but they're far from "in order"... so somehow mysql is inserting the rows in some strange order. How can I fix this so it works in both mysql 4.x and 5.x? I am not sure you can fix this to work properly in a single statement for 5.1.14. The order of operations appears out of sequence to what you need. When executing an SQL statement, there are several stages to the processing. 1)gather rows and filter on matches (FROM ... and JOIN ...) 2)filter the results of 1 (WHERE) 3)apply any GROUP BY 4)filter the results of 3 (HAVING) 5)sort the results (ORDER BY) 6)window the results (LIMIT) It appears that computation of your @cnt variable is performed BEFORE the ORDER BY and not after the ORDER BY. This is completely in line with how the SQL Standard says a query should operate. What if you wanted to ORDER BY on the @cnt column and we did not compute it until after that stage of processing? That would break standards compatibility. To make this work the way you want, you need to create a temporary table with the results of your query sorted the way you want them. Then, query that temporary table and add your column of sequential numbers to the first results. There may possibly be a saving grace for you, though. 5.1.14 was a very early release in the 5.1 series. It is possible that someone else noticed the same problem and a later version may be operating as you want. We are currently releasing 5.1.50 which contains 34 rounds of bugfixes above and beyond your current 5.1.14. I suggest you upgrade and try again. Even if this does not fix the behavior to act as you want, the upgrade will at least remove your exposure to hundreds of identified bugs. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Transfer database to MySQL.Huge effort?
Hello, Basically you need to do it manually, no auto import tool will get things 100% 1. create tables, substituting datatypes from source database to compatible mysql (especially the integer types) 2. create keys that will give same result 3. pump the data, you might have to write scripts to do on the fly conversion instead of direct export import 4. test the data? How are you planning to implement the stored procedure if you are not going to move them? otherwise if you are willing to pay, I bet there are companies that do this :) Jangita | +254 76 918383 | MSN & Y!: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -Original Message- From: Luis Suzuki [mailto:luissuz...@live.com] Sent: 19 September 2010 12:59 AM To: mysql@lists.mysql.com Subject: Transfer database to MySQL.Huge effort? I want to transfer a database(not MySQL) to MySQL(only the tables,not stored procedures,views etc.).Using SQLWays I gotseveral problems(It needs some expertise).Now using SQLMaestro DataWizard I exported some tables to csv format forimporting with mysqlimport,I previously created the database with CREATE DATABASE (database in simpliest form).Now,when I use mysqlimport to import the csv formatted tables it tells that I do not have the table xyz.I thought themysqlimport command could automatically read the imported file and create the table with adequate structure and name according to the read/imported file.The number of tables to import are almost two hundred and previously creating in MySQL almost two hundred tableswith structure compatible to the imported database tables is a huge effort.Are there any easier method to follow?I am a relatively new user of database software. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: clone a database on the same machine
That entirely depends on your requirement, if you need it to be accessible with the same username and password, then configure it with the same username and password. thanks Andy. Quoting Uwe Brauer : On Mon, 20 Sep 2010 12:14:06 +0200, Johan De Meersman wrote: > He did suggest doing mysqladmin create :-p The only thing which is not clear to me is whether db_org and db_clone should have the same user (and maybe the same password.) Uwe Brauer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=a.sm...@ukgrid.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: clone a database on the same machine
On Mon, Sep 20, 2010 at 12:48 PM, Uwe Brauer wrote: > > On Mon, 20 Sep 2010 12:14:06 +0200, Johan De Meersman < > vegiv...@tuxera.be> wrote: > > > He did suggest doing mysqladmin create :-p > > The only thing which is not clear to me is whether > db_org and db_clone > should have the same user (and maybe the same password.) > Databases and users are entirely separate concepts in MySQL. You could grant the same user access to both databases, or you could create a new user that only has access to the clone, however you feel works for you. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: clone a database on the same machine
> On Mon, 20 Sep 2010 12:14:06 +0200, Johan De Meersman > wrote: > He did suggest doing mysqladmin create :-p The only thing which is not clear to me is whether db_org and db_clone should have the same user (and maybe the same password.) Uwe Brauer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: clone a database on the same machine
oopss...sorry...i did not see that line On Mon, Sep 20, 2010 at 3:44 PM, Johan De Meersman wrote: > He did suggest doing mysqladmin create :-p > > > On Mon, Sep 20, 2010 at 11:58 AM, Ananda Kumar wrote: > >> With the method you mentioned, you need to have the new db name already >> present. >> You can do it this way or the edit dump file way. >> >> regards >> anandkl >> >> On Mon, Sep 20, 2010 at 3:19 PM, Johan De Meersman >> wrote: >> >>> Not the way he does it :-) >>> >>> If you use "--databases", mysqldump will add "create database" and "use >>> database" statements. if you specify the db without that parameter, it >>> won't. >>> >>> >>> On Mon, Sep 20, 2010 at 11:34 AM, Ananda Kumar wrote: >>> The dump file has to be edited to replace old db name to the new db name. regards anandkl On Mon, Sep 20, 2010 at 3:00 PM, Uwe Brauer wrote: > Hello > > I would like to clone a database db_org to db_clone on the same machine. > Could > I use the dump command for this? Should the user of both db > be the same? > Something like > # mysqladmin create db_clone -u DB_user > > mysqldump -p db_org > clone.sql > mysql -p -D db_clone < clone.sql > > > thanks > > Uwe Brauer > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=anan...@gmail.com > > >>> >>> >>> >>> -- >>> Bier met grenadyn >>> Is als mosterd by den wyn >>> Sy die't drinkt, is eene kwezel >>> Hy die't drinkt, is ras een ezel >>> >> >> > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel >
Re: clone a database on the same machine
He did suggest doing mysqladmin create :-p On Mon, Sep 20, 2010 at 11:58 AM, Ananda Kumar wrote: > With the method you mentioned, you need to have the new db name already > present. > You can do it this way or the edit dump file way. > > regards > anandkl > > On Mon, Sep 20, 2010 at 3:19 PM, Johan De Meersman wrote: > >> Not the way he does it :-) >> >> If you use "--databases", mysqldump will add "create database" and "use >> database" statements. if you specify the db without that parameter, it >> won't. >> >> >> On Mon, Sep 20, 2010 at 11:34 AM, Ananda Kumar wrote: >> >>> The dump file has to be edited to replace old db name to the new db name. >>> >>> regards >>> anandkl >>> >>> On Mon, Sep 20, 2010 at 3:00 PM, Uwe Brauer wrote: >>> >>> > Hello >>> > >>> > I would like to clone a database db_org to db_clone on the same >>> machine. >>> > Could >>> > I use the dump command for this? Should the user of both db >>> > be the same? >>> > Something like >>> > # mysqladmin create db_clone -u DB_user >>> > >>> > mysqldump -p db_org > clone.sql >>> > mysql -p -D db_clone < clone.sql >>> > >>> > >>> > thanks >>> > >>> > Uwe Brauer >>> > >>> > >>> > -- >>> > MySQL General Mailing List >>> > For list archives: http://lists.mysql.com/mysql >>> > To unsubscribe: >>> http://lists.mysql.com/mysql?unsub=anan...@gmail.com >>> > >>> > >>> >> >> >> >> -- >> Bier met grenadyn >> Is als mosterd by den wyn >> Sy die't drinkt, is eene kwezel >> Hy die't drinkt, is ras een ezel >> > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: clone a database on the same machine
With the method you mentioned, you need to have the new db name already present. You can do it this way or the edit dump file way. regards anandkl On Mon, Sep 20, 2010 at 3:19 PM, Johan De Meersman wrote: > Not the way he does it :-) > > If you use "--databases", mysqldump will add "create database" and "use > database" statements. if you specify the db without that parameter, it > won't. > > > On Mon, Sep 20, 2010 at 11:34 AM, Ananda Kumar wrote: > >> The dump file has to be edited to replace old db name to the new db name. >> >> regards >> anandkl >> >> On Mon, Sep 20, 2010 at 3:00 PM, Uwe Brauer wrote: >> >> > Hello >> > >> > I would like to clone a database db_org to db_clone on the same machine. >> > Could >> > I use the dump command for this? Should the user of both db >> > be the same? >> > Something like >> > # mysqladmin create db_clone -u DB_user >> > >> > mysqldump -p db_org > clone.sql >> > mysql -p -D db_clone < clone.sql >> > >> > >> > thanks >> > >> > Uwe Brauer >> > >> > >> > -- >> > MySQL General Mailing List >> > For list archives: http://lists.mysql.com/mysql >> > To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com >> > >> > >> > > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel >
Re: clone a database on the same machine
Not the way he does it :-) If you use "--databases", mysqldump will add "create database" and "use database" statements. if you specify the db without that parameter, it won't. On Mon, Sep 20, 2010 at 11:34 AM, Ananda Kumar wrote: > The dump file has to be edited to replace old db name to the new db name. > > regards > anandkl > > On Mon, Sep 20, 2010 at 3:00 PM, Uwe Brauer wrote: > > > Hello > > > > I would like to clone a database db_org to db_clone on the same machine. > > Could > > I use the dump command for this? Should the user of both db > > be the same? > > Something like > > # mysqladmin create db_clone -u DB_user > > > > mysqldump -p db_org > clone.sql > > mysql -p -D db_clone < clone.sql > > > > > > thanks > > > > Uwe Brauer > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com > > > > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: clone a database on the same machine
The dump file has to be edited to replace old db name to the new db name. regards anandkl On Mon, Sep 20, 2010 at 3:00 PM, Uwe Brauer wrote: > Hello > > I would like to clone a database db_org to db_clone on the same machine. > Could > I use the dump command for this? Should the user of both db > be the same? > Something like > # mysqladmin create db_clone -u DB_user > > mysqldump -p db_org > clone.sql > mysql -p -D db_clone < clone.sql > > > thanks > > Uwe Brauer > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com > >
clone a database on the same machine
Hello I would like to clone a database db_org to db_clone on the same machine. Could I use the dump command for this? Should the user of both db be the same? Something like # mysqladmin create db_clone -u DB_user mysqldump -p db_org > clone.sql mysql -p -D db_clone < clone.sql thanks Uwe Brauer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Encryption with MYSQL
Any ideas why my statement like SELECT SHA1('abc') AS my_sha is being
returned as binary value, I thought it should be returning HEX in anycase ?
I'm using MySQL community server 5.1.43, with Innodb tables.
Cheers
Neil
On Sat, Sep 18, 2010 at 10:43 AM, Johan De Meersman wrote:
> hex() and unhex() should do the trick in mysql (not base64, but same idea).
> Those will transform every byte into a pair of hexadecimal digits - thus
> limited to 0-9 and A-F - and reverse the process.
>
>
> On Fri, Sep 17, 2010 at 2:13 PM, Tompkins Neil <
> neil.tompk...@googlemail.com> wrote:
>
>> Sorry Johan, how exactly do you do this ?
>>
>>
>> On Fri, Sep 17, 2010 at 1:11 PM, Johan De Meersman wrote:
>>
>>> Simply base64-encode the returned binary string before offering it to
>>> your client.
>>>
>>> On Fri, Sep 17, 2010 at 1:22 PM, Tompkins Neil <
>>> neil.tompk...@googlemail.com> wrote:
>>>
Hi,
I need to encrypt a string like 'hello world', using a passkey. But I
also
need to be able to decrypt the encrypted phrase using the same passkey.
I
noticed in MySQL there are functions like
AES_ENCRYPT()<
http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_aes-encrypt
>
However, I need the encrypted phrase to be returned as a string, since
it
will be passed to a URL as a parameter. Does anyone have any
suggestions on
how to overcome this issue, using MySQL.
I know that there are many components available out there, but I can't
install any third party components on the hosting server.
Cheers
Neil
>>>
>>>
>>>
>>> --
>>> Bier met grenadyn
>>> Is als mosterd by den wyn
>>> Sy die't drinkt, is eene kwezel
>>> Hy die't drinkt, is ras een ezel
>>>
>>
>>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
