Re: Capitalize Input via Auto Complete?
On Thu, Sep 16, 2010 at 2:35 PM, Todd Lyons tly...@ivenue.com wrote: On Wed, Sep 15, 2010 at 6:50 AM, Johan De Meersman vegiv...@tuxera.be wrote: I commonly set up a tunnel to the SSH server at the office and then another tunnel from that server to my development rig, so I can run MySQL WB at home on my database at work. Is that what you mean? Pretty much, yeah. I've not got just the one database, though, so it's usually too much of a bother to set up all the tunnels, and I just SSH straight through and use the commandline :-) Still, the SSH tunnel capability in the client is nice for hosts that are only one jump away. It works for multiple jumps too. You just have to enable X11 forwarding either in the appropriate config files or do it on the commandline. Note that it's possible that a severely paranoid sysadmin may well have disabled it in the sshd service on one or more of the machines that you're ssh'ing to. # my workstation has ForwardX11=yes in ~/.ssh/config ssh host1 # then from there: ssh -X host2 On host 2, running a gui app pops it up on my local desktop. You misunderstand me :-) I specified in the client, referring to the Workbench app, not the SSH client. ssh-level jumps have always been possible, and work with whatever too you want them to. The convenience of the Workbench-integrated jump, is that I don't have to bother setting up the tunnel manually, remember what particular host I tied to what particular local IP/port combo, et cetera. I've not just the one server to admin, you know - I just ordered 20 licenses for ZRM backup, to cover the most important machines. The rest of them aren't important enough to warrant that cost, they'll manage fine with a nightly dump. So, as I said; I use workbench (occasionally) for hosts that are 0 or 1 jump away; for the rest I just go there through plain SSH and do stuff commandline. -- Regards... Todd I seek the truth...it is only persistence in self-delusion and ignorance that does harm. -- Marcus Aurealius -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- 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: Update record count
On Fri, Sep 17, 2010 at 3:51 AM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: So if 10 rows of A match your conditions, 1 row from B match your conditions, and 10 rows from C match your conditions, then this query produces 10*1*10 total row combinations. Umm. It's friday, so I may be rather off it, too, but aren't the conditions inclusive ? They're AND, not OR. A Cartesian join on a(10 rows), b(1 row) and c(10 rows) would produce as you say. I would say that the number of rows is the number where * c.f3 is x, y or z * AND where b.f4 = yen * FROM the set produced by the join condition. Check the number of rows in c that match f3 in (x, y, z). Match those with rows in b as per join. This will yield the same or less rows. Filter those that match b.f4 = yen. This again will yield same or less. Match those with a as per join. This, too, will yield same or less, and will be your final number of rows. -- 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: Need restart mysql when time changed
This is a correct description of behaviour. Did you have a question ? :-) On Fri, Sep 17, 2010 at 5:52 AM, win.a win@gmail.com wrote: I fond my mysql db os time was not correct so i sync with ntpdate ,when testing my app which depend on the date was not the current os time .After restarting Mysql ,the app goes well. All you best What we are struggling for ? The life or the life ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Encryption with MYSQL
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
SHA1 returns binary value
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 ?
Cheers
Neil
Re: Encryption with MYSQL
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
Re: SHA1 returns binary value
Use UNHEX(SHA1('abc')) to get the string value
On Sep 17, 2010, at 5:38 PM, 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 ?
Cheers
Neil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Add Arrange New Column
I have an existing table with a few columns I created a few months ago. Sadly I left out the column I would like to use for my Primary Key and wanted to know what is the best way to add a column to an existing table but also I want the column to appear first before any other columns since it's the 'id' column and will be set to PRIMARY KEY NOT NULL. Thanks for any assistance. -Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Add Arrange New Column
alter table mytable add id int not null primary key auto_increment first; I hope it can help you. -- João Cândido de Souza Neto Carlos Mennens carlosw...@gmail.com escreveu na mensagem news:aanlktikfc89rhercrg5pr9yxfv8semvdv4kp9r67n...@mail.gmail.com... I have an existing table with a few columns I created a few months ago. Sadly I left out the column I would like to use for my Primary Key and wanted to know what is the best way to add a column to an existing table but also I want the column to appear first before any other columns since it's the 'id' column and will be set to PRIMARY KEY NOT NULL. Thanks for any assistance. -Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
prime number table
I got a file of the list of the 1st 1270607 prime numbers (the 1270607th prime is 1999, beat the $227 book at http://www.amazon.com/prime-numbers-Carnegie-institution-Washington/dp/B0006AH1S8). the file is an output of a python script. the file size is about 12Mb. Then I created a simeple mysql table prime as mysql desc prime; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | oid | int(10) unsigned | NO | PRI | NULL| auto_increment | | pv | int(10) unsigned | YES | | NULL|| | descript | text | YES | | NULL|| +--+--+--+-+-++ mysql show create table prime; --+ | Table | Create Table --+ | prime | CREATE TABLE `prime` ( `oid` int(10) unsigned NOT NULL AUTO_INCREMENT, `pv` int(10) unsigned DEFAULT NULL, `descript` text, PRIMARY KEY (`oid`) ) ENGINE=MyISAM AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 The table file size is (prime.frm,prime.MYD,prime.MYI) = (9k; 24,817KB; 12,754KB) Then I do mysql create table prm select * from prime order by prime.oid; mysql alter table prm modify oid int unsigned primary key auto_increment; mysql desc prm; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | oid | int(10) unsigned | NO | PRI | NULL| auto_increment | | pv | int(10) unsigned | YES | | NULL|| | descript | text | YES | | NULL|| +--+--+--+-+-++ mysql show create table prm; +---+-- | Table | Create Table +---+-- | prm | CREATE TABLE `prm` ( `oid` int(10) unsigned NOT NULL AUTO_INCREMENT, `pv` int(10) unsigned DEFAULT NULL, `descript` text, PRIMARY KEY (`oid`) ) ENGINE=InnoDB AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 | +---+-- The table file prm.frm is only 9KB My question is that how come it's SO SMALL? (currently the colum description in both tables prime and prm are empty except one identical row, with very short string value. Is is recommend to index the other twoo columns? 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: Update record count
-Original Message-
From: Shawn Green (MySQL) [mailto:shawn.l.gr...@oracle.com]
Sent: Thursday, September 16, 2010 9:51 PM
To: Jerry Schwartz
Cc: mysql@lists.mysql.com
Subject: Re: Update record count
On 9/16/2010 5:12 PM, Jerry Schwartz wrote:
I should be able to figure this out, but I'm puzzled. Here's a simplified
example:
UPDATE a JOIN b ON a.kb = b.kb JOIN c ON b.kc = c.kc
SET a.f1 = NOW(),
b.f2 = NOW()
WHERE c.f3 IN ('x', 'y', 'z')
AND b.f4 = 'yen';
It seems to me that if there are 3 rows found in `c` that match a total of
10
rows in `a` that each, in turn, matches 1 row in `b`, then the total number
of
qualifying would be 10 + 10 - 20.
That should also be the number of rows changed.
Somehow the numbers reported by MySQL don't seem to match up in my real
case,
even though the results seem to be what I want. The numbers reported were
way
too high, and I don't understand it.
I can supply more information, if necessary, but have I gone off the rails
somehow?
Look at this like the database sees the problem:
SELECT a.f1, b.f2, c.f3, b.f4
FROM a JOIN b ON a.kb = b.kb JOIN c ON b.kc = c.kc
WHERE c.f3 IN ('x', 'y', 'z')
AND b.f4 = 'yen';
What you should be able to notice:
* Each matching row from a is combined with each matching row from b
* Each combination of (a,b) rows is combined with each matching row from c
So if 10 rows of A match your conditions, 1 row from B match your
conditions, and 10 rows from C match your conditions, then this query
produces 10*1*10 total row combinations.
That should explain why your numbers are higher than expected.
[JS] Thanks --- I think. I realize that left out some crucial details, and
also made a mistake when posing my example. Let me use some more suggestive
field names.
UPDATE `prod` JOIN `prod_price` ON `prod`.`prod_id` = `prod_price`.`prod_id`
JOIN `pub` ON `prod`.`pub_id` = `pub`.`pub_id`
SET `prod`.`prod_changed` = 1,
`prod_price`.`prod_price_tax` = .7
WHERE `pub`.`pub_id `IN ('x', 'y', 'z')
AND `prod_price`.`prod_price_curr` = 'yen';
- `prod`.`prod_id` is a unique key for `prod`, but is not unique in
`prod_price`
- `pub`.`pub_id` is a unique key for `pub`, but is not unique in `prod`
Assume that
- For `pub`.`pub_id` = 'x' we match 3 rows in `prod`:
* `prod`.`prod_id` = 'a'
* `prod`.`prod_id` = 'b'
* `prod`.`prod_id` = 'c'
- For `pub`.`pub_id` = 'y' we match 5 rows in `prod`:
* `prod`.`prod_id` = 'm'
* `prod`.`prod_id` = 'n'
* `prod`.`prod_id` = 'o'
- For `pub`.`pub_id` = 'z' we match 2 rows in `prod`:
* `prod`.`prod_id` = 'q'
* `prod`.`prod_id` = 'r
That should give us a total of 10 rows for this part of our search. Now assume
that
- For each value of `prod`.`prod_id` there are 2 matching rows in
`prod_price`, BUT for each value of `prod`.`prod_price` only one row matches
the WHERE condition `prod_price`.`prod_price_curr` = 'yen'. For that reason we
have a 1:1 relationship between the rows in `prod` and the remaining rows from
`prod_price`.
So are you saying that the count of rows selected would be
3 x 10 x 10
even though the actual number of rows we're working on is only 10 (10 rows
from `prod` and 1 row from `prod_price` for each row from `prod`)?
I know that when it reports the number of rows affected, it adds up the
numbers from each table; but a SELECT based upon the criteria used in my
UPDATE reports the number of records I would expect.
Regards,
--
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: prime number table
The data isn't in the .frm; That only holds the structure of the table. Your data is in the .MYD file of the same name, and indexes are in the .MYI file. -Original Message- From: Elim PDT [mailto:e...@pdtnetworks.net] Sent: Friday, September 17, 2010 11:29 AM To: mysql@lists.mysql.com Subject: prime number table I got a file of the list of the 1st 1270607 prime numbers (the 1270607th prime is 1999, beat the $227 book at http://www.amazon.com/prime-numbers-Carnegie-institution-Washington/dp/B0006AH1S8). the file is an output of a python script. the file size is about 12Mb. Then I created a simeple mysql table prime as mysql desc prime; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | oid | int(10) unsigned | NO | PRI | NULL| auto_increment | | pv | int(10) unsigned | YES | | NULL|| | descript | text | YES | | NULL|| +--+--+--+-+-++ mysql show create table prime; --+ | Table | Create Table --+ | prime | CREATE TABLE `prime` ( `oid` int(10) unsigned NOT NULL AUTO_INCREMENT, `pv` int(10) unsigned DEFAULT NULL, `descript` text, PRIMARY KEY (`oid`) ) ENGINE=MyISAM AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 The table file size is (prime.frm,prime.MYD,prime.MYI) = (9k; 24,817KB; 12,754KB) Then I do mysql create table prm select * from prime order by prime.oid; mysql alter table prm modify oid int unsigned primary key auto_increment; mysql desc prm; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | oid | int(10) unsigned | NO | PRI | NULL| auto_increment | | pv | int(10) unsigned | YES | | NULL|| | descript | text | YES | | NULL|| +--+--+--+-+-++ mysql show create table prm; +---+-- | Table | Create Table +---+-- | prm | CREATE TABLE `prm` ( `oid` int(10) unsigned NOT NULL AUTO_INCREMENT, `pv` int(10) unsigned DEFAULT NULL, `descript` text, PRIMARY KEY (`oid`) ) ENGINE=InnoDB AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 | +---+-- The table file prm.frm is only 9KB My question is that how come it's SO SMALL? (currently the colum description in both tables prime and prm are empty except one identical row, with very short string value. Is is recommend to index the other twoo columns? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
innodb_autoinc_lock_mode and replication mode
I couldn't find much information on innodb_autoinc_lock_mode and implications on mixed mode replication. Does the same caution for innodb_autoinc_lock_mode=2 and statement-based replication apply to mixed mode replication? Kyong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
