What happens when you type
$ /usr/bin/mysql -h localhost -u mark -p
Does this work? I'm off home now so won't be able to check your reply
for about 2 hours.
Regards
David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273 - Work
+
David,
This is what I got:
[EMAIL PROTECTED]:~$ aliases
bash: aliases: command not found
[EMAIL PROTECTED]:~$ which mysql
/usr/bin/mysql
[EMAIL PROTECTED]:~$ $PATH
bash: /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games: No such file or
directory
[EMAIL PROTECTED]:~$
I don't have a c
Try typing at the command prompt
$ aliases
$ which mysql
$ echo $PATH
The first one will list any aliases that have been setup. The second
will tell you the directory the system thinks it is getting mysql from
and the third will list your PATH environment variable. If you see an
alias named mys
David,
How do I do that?
Thanks!
Mark
On Wednesday 21 December 2005 11:37 pm, Logan, David (SST - Adelaide) wrote:
> Hi Mark,
>
> Have you checked to see if you any aliases set? It might be using that
> instead of the mysql command. May well be worth checking your path to
> ensure you aren't pi
Hi Mark,
Have you checked to see if you any aliases set? It might be using that
instead of the mysql command. May well be worth checking your path to
ensure you aren't picking up a script called mysql or something similar.
Regards
David Logan
Database Administrator
HP Managed Services
148 Fr
I have the following setup - mysql 4.0.24 running on Debian Linux stable.
I set up a user 'mark' with a password. When I log into my Linux box as user
'mark', I cannot connect to mysql - I get this funny error message:
[EMAIL PROTECTED]:~$ mysql -h localhost -u mark -p
mysql: unknown option '--u
- Original Message -
From: "James Harvard" <[EMAIL PROTECTED]>
To:
Sent: Wednesday, December 21, 2005 9:08 PM
Subject: Re: Are primary keys essential?
Thanks the on & off-list replies, but I obviously didn't explain my
situation very well!
My app is essentially creating summary re
Hi,
I am presently using MySQL ODBC driver version 3.51.06 with MySQL 4.1.
Now I need to upgrade the MySQL to latest i.e 5.0.17. Can I still use
the old ODBC driver (3.51.06) with the latest MySQL?
Thanks in advance.
Regards,
Abdul Rasheed.
The information contained in this ele
>
> Shawn, I'm not quite clear what you are saying in your second last
> paragraph. When you have this situation:
>
> ID (autogenerated) PART_NOPART_DESCRIPTION
> 1 A01 Widget
> 2 B03Grapple Grommet
> 3
Hi James,
If the tables you use have primary keys that _YOU_ don't need or use then,
for you, they are not essential.
I do have a question, though. You are working with SQL tables, aren't you?
And from wherever they came from or exist, I presume from your reply that
you don't need the PKs.
Hi James,
Not AFAIK, one can create tables without specifying a PK and there is no
objection. Data is stored quite happily and you should be able to use
your FK's to access other data. It must be voluntary because the ALTER
TABLE DML statement has
DROP PRIMARY KEY drops the primary index. Note: I
In hindsight my thread title was misleading - sorry. Should have been "are
primary keys _always_ essential?".
JH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Thanks the on & off-list replies, but I obviously didn't explain my situation
very well!
My app is essentially creating summary reports from large amounts of data. It
is _not_ doing the actual data warehousing. It's international trade data.
The data tables contain foreign keys for stuff like d
Hi James,
An internal, unique, auto-increment PK is a good idea on dynamic, large
tables. Smaller, mostly static, tables can often do without a PK. In a
backend DB it's a godsend. What would happen if I depended on the
programming in the client-side code to handle the PK?? It would be
impossi
Hello,
The mysql 4.1.16 shell does not accept alternate character set (koi8r -
russian) keyboard input. It accepts koi8r input fine in non interactive
mode as:
mysql our_database < our_command.sql
and it displays both english and koi8r fine on screen.
It was comiled: configure --with-charse
The PK thread has reminded me of a question I had but never resolved when
designing the table structure of the big data warehouse app I was droning on
about just now in the aforementioned thread. As need to import some hundreds of
millions of rows in the next week, I think now would be a good id
Auto-incremented integers (be it bigint, mediumint, etc) are, from a
purist point of view, better than "natural" primary keys, like part
number etc. Read Practical Issues in Database Management, by Fabian
Pascal. He argues against natural primary keys, because the business
rules that underly
I admit I too am in the habit of always defining an auto_increment primary key,
but recently gathered my courage and omitted it from a match-up table joining a
table of users to a table of categories they were allowed to use - an
auto-generated primary key would have been completely redundant. I
Rhino,
What I do is put the ID (integer Primary Key, auto-increment, unique) first.
First key in every file.
Then define my indexes.
You could, do it the other way as you ask. But, I found this way is
consistent.
It can be traced anywhere on any file. Timestamp on important or critical
files
- Original Message -
From: <[EMAIL PROTECTED]>
To: "Kenneth Wagner" <[EMAIL PROTECTED]>
Cc: "mysql" ; "Rhino" <[EMAIL PROTECTED]>
Sent: Wednesday, December 21, 2005 5:15 PM
Subject: Re: Reason for Auto-increment primary keys?
"Kenneth Wagner" <[EMAIL PROTECTED]> wrote on 12/21/2005
0
On 12/21/05, Mark Matthews <[EMAIL PROTECTED]> wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Nathan Gross wrote:
> > On 12/21/05, Hassan Schroeder <[EMAIL PROTECTED]> wrote:
> >> Nathan Gross wrote:
> >>
> /* hint: java.sql.PreparedStatement */
> >>> I thought we are talking a
Kenneth Wagner wrote:
Speed. Especially where related files are concerned. Foreign keys. Links
on integer
fields are faster, smaller and more efficient. Keys remain smaller and
faster.
This in my mind is one of the biggest reasons to use an AUTO_INCREMENT
column as a primary key when other c
"Kenneth Wagner" <[EMAIL PROTECTED]> wrote on 12/21/2005
04:27:53 PM:
> Hi Rhino,
>
> Excellent question. Felt as you do, initially.
>
> Here's what changed my mind.
>
> Integer keys are fast. And small. Hence, they take very little RAM
space.
>
> They are contiguous. A missing PK is easy to
If this trend is real, it doesn't seem like a very good trend to me. For
example, if you were keeping track of parts in a warehouse, why would anyone
make a table that looked like this:
ID (autogenerated PK) PART_NOPART_DESCRIPTION
1 A01
OK,
I tried the following queries:
1. Use the ORDER BY and not the LIMIT <-- still slow
2. Use the LIMIT and not the ORDER BY <-- fast
So it looks like it's building a whole temp table with all the items and then
doing the ORDER BY.
Just as a test, I removed the INDEX fr
Hi Rhino,
>Maybe
I'm just "old school" but I've always thought that you should
>choose a primary key based on data that is actually in the table
>whenever possible, rather than generating a new value out of thin
air.
Mebbe every db list should drag this out for re-examination once a year
o
OK thanks for your help, so my summary:
(1) spreading merged tables across disks will only help concurrent
queries (in my data-warehouse application I'm doing things serially).
(2) there's no efficiency in the way a merge table splits the indexes
into smaller files -- if anything, it will be less
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Nathan Gross wrote:
> On 12/21/05, Hassan Schroeder <[EMAIL PROTECTED]> wrote:
>> Nathan Gross wrote:
>>
/* hint: java.sql.PreparedStatement */
>>> I thought we are talking about a mysql specific function, which would
>>> not be in the vanilla j
Hi Rhino,
Excellent question. Felt as you do, initially.
Here's what changed my mind.
Integer keys are fast. And small. Hence, they take very little RAM space.
They are contiguous. A missing PK is easy to find. There's a gap in the
number sequence.
Can't do this with the part description. No
One technique that I see a lot on this mailing list is people putting
auto-incremented integer primary keys on their tables.
Maybe I'm just "old school" but I've always thought that you should choose a
primary key based on data that is actually in the table whenever possible,
rather than gener
On 12/21/05, Hassan Schroeder <[EMAIL PROTECTED]> wrote:
> Nathan Gross wrote:
>
> > Woa! Let me verify. If I pass a qry string:
> > "SELECT Anyfield from Anytable where Anyfield = 'The man was 100% correct'
> > "
> > to a Connector/J Statement (or PreparedStatement via parameters), the
> > drive
Russell,
>... I need to get any matches on table 1 and then populate them to the
>rest of the family at the address. So in the example above, Mary would
>also get a phone number - I only have a first initial in table 2, so
>that would hot Joe and James.
From what you say, I am unclear why Mary g
Nathan Gross wrote:
> Woa! Let me verify. If I pass a qry string:
> "SELECT Anyfield from Anytable where Anyfield = 'The man was 100% correct' "
> to a Connector/J Statement (or PreparedStatement via parameters), the
> driver will automatically [behind the scenes] escape the percent sign?
Again,
It'll be a few hours before I get home and can do this query with the
variations you mentioned. I don't know if I need the separate index key on
`salesrank` or not. I thought it would speed up my query be having it.
In the previous email I included the results from doing an EXPLAIN. It
DBTools Software is pleased to announce the new DBManager Professional 3.2.1
Enterprise Edition. This version fixes the bugs reported for 3.2.0 and add
small new features. See the list of changes:
NEW FEATURES
a.. Datasheet View redesigned to be more productive
b.. Implemented Datasheet S
Ok, so the next step would be to try the original query with just the
LIMIT clause, and then just the ORDER BY (but not both).
The results of "select count(*)" query would be helpful to know just
how many records mysql is trying to sort and limit.
And do you really need a separte index key on `sa
I have a couple of tables like so:
FName FInitial
SName SName
Address1Address1
Address2Address2
Address3Address3
Zip Zip
Phone
I need to match the phone numbers to the a
On 12/21/05, Hassan Schroeder <[EMAIL PROTECTED]> wrote:
> Nathan Gross wrote:
>
> >>/* hint: java.sql.PreparedStatement */
> >
> > I thought we are talking about a mysql specific function, which would
> > not be in the vanilla j2se/j2ee pkg's. Actually, you know, I don't
> > even know exactly wh
OK guys,
Here is are my tables and test SELECT queries. Before every query I did a
FLUSH TABLES command. Before starting these test, I did and ANALYIZE and
OPTIMIZE on all the tables. Hopefully the formatting of this email isn't too
bad.
product table:
show create table pn
Nathan Gross wrote:
>>/* hint: java.sql.PreparedStatement */
>
> I thought we are talking about a mysql specific function, which would
> not be in the vanilla j2se/j2ee pkg's. Actually, you know, I don't
> even know exactly what function I need to be looking for!
We're talking about escaping c
got it, heehe thanks, here is my query:
select
*
from
person as p
left join expense as e
on p.id=e.id
&& e.no=(select no from expense where no=e.no order by no desc limit 1)
- Original Message -
From: [EMAIL PROTECTED]
To: Eris Ristemena
Cc: mysql
On 12/21/05, Hassan Schroeder <[EMAIL PROTECTED]> wrote:
> Nathan Gross wrote:
>
> >>Nearly every mysql client library has some kind of function to perform this
> >>escaping for you. Please refer to the manual of the client library you are
> >>using to see if your client has it or if it escapes con
On 12/21/05, JamesDR <[EMAIL PROTECTED]> wrote:
> Nathan Gross wrote:
>
>
>
> >
> > I get two lists from different sources which I merge into the database
> > via a Java program. Since these two lists themselves sometimes get
> > their data from the same source, my program first does a SELECT on t
Hello
> idname no id cust
> 1a 2 1 y
> 2b null null null
> 3c null null null
Obviously this query should be redesigned, however it does produce
results which you want:
select id
, name
, no
, cust
In your last query (whichever form you use) use a LEFT JOIN instead of an
INNER JOIN and make sure you list your `person` table first.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Eris Ristemena" <[EMAIL PROTECTED]> wrote on 12/21/2005 12:15:15 PM:
> Ok, that's easy, i
Ok, that's easy, i see. But now how can i also get who hasn't done the
expense?
person
idname
1 james
2 michael
3 jack
expense
noidexp
1 12000
2 21000
3 1 500
so that the result should be:
id
Nathan Gross wrote:
>>Nearly every mysql client library has some kind of function to perform this
>>escaping for you. Please refer to the manual of the client library you are
>>using to see if your client has it or if it escapes content as part of the
>>execution of certain methods or calls.
>
>
Brandon E Hofmann <[EMAIL PROTECTED]> wrote on 12/21/2005
11:53:49 AM:
>
> Is it possible to have one stored procedure reference a result set
> generated by another stored procedure that it called?
>
> For example:
>
> sp_2 calls sp_1 to put a result set in a temporary table
>
> sp_2 then doe
Nathan Gross <[EMAIL PROTECTED]> wrote on 12/21/2005 11:49:10 AM:
> On 12/21/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> >
> > Nathan Gross <[EMAIL PROTECTED]> wrote on 12/21/2005 11:20:50 AM:
> >
> >
> > > On 12/20/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > > > Nathan Gross
Is it possible to have one stored procedure reference a result set
generated by another stored procedure that it called?
For example:
sp_2 calls sp_1 to put a result set in a temporary table
sp_2 then does a join with the same temporary table created by sp_1 for a
final result set
I have a com
On 12/21/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
>
> Nathan Gross <[EMAIL PROTECTED]> wrote on 12/21/2005 11:20:50 AM:
>
>
> > On 12/20/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > > Nathan Gross <[EMAIL PROTECTED]> wrote on 12/20/2005 05:34:58 PM:
> > >
> > > > Hi;
> > >
Nathan Gross wrote:
I get two lists from different sources which I merge into the database
via a Java program. Since these two lists themselves sometimes get
their data from the same source, my program first does a SELECT on the
varchar field (unique index) [to ensure that this data is not ye
Nathan Gross <[EMAIL PROTECTED]> wrote on 12/21/2005 11:20:50 AM:
> On 12/20/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > Nathan Gross <[EMAIL PROTECTED]> wrote on 12/20/2005 05:34:58 PM:
> >
> > > Hi;
> > > [Mysql 4x] In a table where I get data from another program, I have
> > > many
On 12/20/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Nathan Gross <[EMAIL PROTECTED]> wrote on 12/20/2005 05:34:58 PM:
>
> > Hi;
> > [Mysql 4x] In a table where I get data from another program, I have
> > many records (about 1000) that have illegal chars in a [unique]
> > indexed varchar
I can't take it any longer. Come on list this is an easy one!! We have
only had this question asked about once every other week this year. Shame
on you lurkers who knew the answer but didn't kick in. 8-(
Eris,
What you are looking for can be called the "group wise maximum" because
you want t
Ok, i change the table to make it clear.
I have two tables, person and expense
person
idname
1 james
2 michael
expense
noidexp
1 12000
2 21000
3 1 500
where expense.no is an autoincrement column.
how can
>
> To get the maximum t2.no value for each t1.id value, try ...
>
> SELECT
> t1.id,
> t1.name,
> t2.id,
> MAX(t2.no)
> FROM t1 LEFT JOIN t2 USING (id)
> GROUP BY t1.id;
>
> but because of the MAX() / GROUP BY aggregation, adding t2.cust to the
> query will not give you the t2
Eris,
>...what i need is a distinct t1.id with maximum t2.no, so that the result
>should be like this:
>id name no id cust
>1 a 2 1 y
>2 b null null null
>3 c null null null
To get the maximum t2.no value for each t1.id value, try ...
SELECT
t1.id,
t1.name,
t2.id,
MAX(t2.no)
FROM
wangxu wrote:
> I can't operate utf8 characters within command-line in linux operating system.
> Mysql doesn't support?
I can enter utf8 characters using the mysql command-line client just
fine -- SuSE 9.1, MySQL 4.1.13 on this system.
FWIW!
--
Hassan Schroeder - [EMA
Hank <[EMAIL PROTECTED]> wrote on 12/21/2005 09:39:50 AM:
> Grant,
>
> You can just to a "desc pn_pricecompare_catprod" and "desc
> pn_pricecompare_product" and post the results. The CREATE TABLE
> statements would be OK, but the describes are better.
>
> The flush the query cache, I think if
This is referencing an old thread in the mailing list:
http://archives.neohapsis.com/archives/mysql/2004-q3/4484.html
I'm trying to write a justification for upgrading to MySQL 5.0 and I
know it fixed some issues with LEFT/RIGHT joins. In particular I
thought it fixed bugs 1591, 1677 and 3765
Grant,
You can just to a "desc pn_pricecompare_catprod" and "desc
pn_pricecompare_product" and post the results. The CREATE TABLE
statements would be OK, but the describes are better.
The flush the query cache, I think if you do a "flush tables".
-Hank
--
MySQL General Mailing List
For list
That's why I suggested simply using the supplied my-huge.cnf config file,
because I assume it was contructed by some MySQL uber-guru who does know how
much RAM to allocate to each variable!
James Harvard
> Like I mentioned before, I am tweaking the .cnf files by blind trial and
> error. I w
hi all,
i have this small problem. I hope someone can help me out here.
i have two table with one-to-many relations,
t1
id name
1 a
2 b
3 c
t2
no id cust
1 1 x
2 1 y
using join statement like this:
select * from t1 left join t2 using (id)
i get this resul
Thank Hank. I will try this. When you say the table descriptions, do you
mean for me to post my CREATE TABLE syntax of how I created the table?
Thanks,
Grant
Hank <[EMAIL PROTECTED]> wrote: I don't think the problem is going to be
solved with the my.cnf file.
Here's what I would try
I don't think the problem is going to be solved with the my.cnf file.
Here's what I would try..
1- run and time the original query without the ORDER BY or LIMIT clauses
2- run and time the following breakdown queries, to see if the
indexes are at least working correctly:
-- test catprod
SELECT
Hi James.
Thanks for the tips. I tried your below SQL call of quering just one
column. The query didn't speed up.
I think I am running out of RAM and thus caching the temp table to disk. My
server is currently using the default my.cnf file. I will try the large and
huge example
>>uploaded it:
>>
>>http://img394.imageshack.us/img394/4808/topgraphtoday9fp.jpg
>>http://img394.imageshack.us/img394/3403/bottomgraphweeks2vf.jpg
>
>
> Nice graphs. What tool do you use to make them?
http://www.cacti.net/
on the 'additional scripts' page you get scripts for almost
everything t
Hello.
> (a) The docs say that spreading the underlying tables across different
> disks can make queries faster. I don't quite understand how this will
> work in a normal query: if I do a SUM(amount) over the entire table,
> will it be quicker if the table is spread across different disks? I
Hello.
Add composite index (tValidFrom, tValidTo) and use constant or variable
instead of now(). Force MySQL to use this composite index.
Mattias Håkansson wrote:
> Hello People,
>
> I have some indexing problem on using the fieldtype 'date' as
> restriction in a query.
> I use MySQL
Hello.
Check if the problem still exists on the latest release 5.0.17 in case
you have an older version. Please, provide a repeatable test case, or
at least the source of your stored procedure and CREATE statements for
tables which it uses.
Sujay Koduri <[EMAIL PROTECTED]> wrote:
>hi
On Wednesday 21 December 2005 14:01, Christian Meisinger wrote:
> Christian Meisinger wrote:
> >>i added a jpg of our cacti graph.
> >>the top graph shows avg load of today.
> >>and the bottom graphs show 4 weeks back.
> >
> > mh where are my jpgs... i can't post attachments here?
>
> uploaded it:
Hi all,
I have some troubles about autocasting and unsensitive case.
Look at this:
mysql> select data_fattura from 2005_ordini where data_fattura = 9911;
| -00-00 |
| -00-00 |
| -00-00 |
| -00-00 |
| -00-00 |
| -00-00 |
| -00-00 |
| -00-00 |
|
Christian Meisinger wrote:
>>i added a jpg of our cacti graph.
>>the top graph shows avg load of today.
>>and the bottom graphs show 4 weeks back.
>
>
> mh where are my jpgs... i can't post attachments here?
uploaded it:
http://img394.imageshack.us/img394/4808/topgraphtoday9fp.jpg
http://img394
> i added a jpg of our cacti graph.
> the top graph shows avg load of today.
> and the bottom graphs show 4 weeks back.
mh where are my jpgs... i can't post attachments here?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[
last week i upated to 4.1.16 and the average load increased.
today i switched back to 4.1.15 and the average load decreased
immediately.
i added a jpg of our cacti graph.
the top graph shows avg load of today.
and the bottom graphs show 4 weeks back.
has anyone else the same problem?
what's wrong
hi all,
I am executing a stored proc that has a top level cursor to loop over a user
set, and then execute certain transactions for each user. After processing
about 5 users, it always generates: ERROR 2013 (HY000): Lost connection to
MySQL server during query. However, if I restart from the same u
I've recently been doing some big table query optimisation, but after getting
the query tweaked to hit the right index the query time is under very
livable-with, despite the fact that MySQL seems to be examining more rows for
my query than for yours. However the 'rows' column of thhe explain out
Tomas,
thanks that was the solution I couldn't find out.
I had to change only ON clause to master_tbl.ID=detail_tbl.Master_ID .
Thanks also to Felix, I know I could use referential integrity but I knew
there is a way how to do it without it and I was trying to find that way.
Dusan.
- Orig
On 21/12/2005, Dušan Pavlica wrote:
> Hello,
> I have master-detail tables and I would like to delete in one
> statement master record and all detail records but not every master
> record has details. MySQL versions 4.1.10 and higher. Could someone
> help me, please, to create such a query?
>
What about
DELETE master_tbl,detail_tbl FROM master_tbl LEFT JOIN detail_tbl ON
master_tbl.ID=detail_tbl.ID WHERE .
T.R.
Dušan Pavlica napsal(a):
Hello,
I have master-detail tables and I would like to delete in one statement master record and all detail records but not every master rec
On Wednesday 21 December 2005 08:12, you wrote:
> Are you mean this problom only disposed in compile ?
yes and no...this way you tell mysql to use readline instead of libedit...so
the problem is (at least that was the problem for me) related to the lib
which is used for input processing...
> >
Hello,
I have master-detail tables and I would like to delete in one statement master
record and all detail records but not every master record has details.
MySQL versions 4.1.10 and higher.
Could someone help me, please, to create such a query?
Example:
CREATE TABLE `master_tbl` (
`ID` int(
83 matches
Mail list logo
