HI,
the following insert/update produced error. I m using MySQL 5.0.
$insert_sql = qq {insert into inventory
(prodcode,qty,lastupdatedate,prodname,basename,vendorname,cost)
values (?,?,?,?,?,?,?) ON DUPLICATE KEY
UPDATE
qty=$q
Hi Guys
I am trying to display 1 random record taken from 2 tables.
I have tried the following;
SELECT table1.record1, table1.record2, table2.record1 FROM table1, table2
ORDER BY RAND() Limit 1
With no luck... So now I am trying to use CREATE VIEW but also with no
luck... Can anybody help out?
At 07:42 PM 1/13/2005, Dathan Pattishall wrote:
DISABLE / ENABLE keys is very fast even for your dataset. Basically when
disabled it only respects UNIQUE type keys when loading data and
rebalances the binary tree when enabled.
Dathan
Dathan,
So you're saying it will not create a temporary t
DISABLE / ENABLE keys is very fast even for your dataset. Basically when
disabled it only respects UNIQUE type keys when loading data and
rebalances the binary tree when enabled.
Dathan
> -Original Message-
> From: mos [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 13, 2005 5:24 P
Hi,
MySQL 4.1.9, a new version of the popular Open Source/Free Software
Database Management System has been released. It is now available in source
and binary form for a number of platforms from our download pages at
http://dev.mysql.com/downloads/ and mirror sites.
Note that not all mirror sites
I am importing data into a large table, 100 million rows, and I want to use
"Alter table disable keys" prior to executing a Load Data...,. But after
loading the data, if I execute a "Alter Table ... enable keys" will MySQL
create a second table, load the data into the second table, and then
reb
Dathan Pattishall wrote:
Hmm that's a range, that should do a table scan in 4.0.18,
Yes... I believe it did but since its a memory table it went by really
quick.
since a
memory table type is just a hash table. In 4.1 I believe it supports
ranges since the table is more of a myISAM type.
Yes.
Hmm that's a range, that should do a table scan in 4.0.18, since a
memory table type is just a hash table. In 4.1 I believe it supports
ranges since the table is more of a myISAM type.
Is there an index on TIMESTAMP?
Does the range cover more then 30% of the table?
> -Original Message-
Raphael Matthias Krug wrote:
Sasha
P.S. I have a theory that a habit of printing computer documentation
is a road block to becoming a "guru". At least, I have not yet
encountered a "guru" that printed much, while at the same time it
seems like a struggling user prints a lot. You cannot be 100% s
Under 4.0.18 we were loading about 800M of data into a memory table to
get better performance from some of our main queries.
Via crontab we would DELETE older links in the memory table and then
INSERT links from a myisam table.
This process under 4.1.7 is MUCH slower. Specifically the DELETE i
Thanks for your suggestions, everyone, but I have followed the manual and
repeated the installation process (with it's very nice GUI, by the way) more
than once, and I still have a problem.
Perhaps it isn't that simple after all.
I had similar problem on this laptop when I installed version 4, an
I forgot one element
LOAD DATA LOCAL INFILE 'a.csv' INTO TABLE Users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(id,firstName,lastName);
Brad ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysq
Steve Grosz <[EMAIL PROTECTED]> writes:
>Can anyone tell me a good way to import individual column data into a
>table? Is there a tool to assist with this?
If your data is a .csv file [a.csv] similar to:
1,"Steve","Grosz"
2,"Brad","Eacker"
Consistent with the output from Excel. You could use
[snip]
Does anyone now the maximum number of records and fields in MySQL 4.0.22
Win 32?
[/snip]
You keep putting records in the system until you run out of disk space.
I have one database now that is
20 table(s) 335,411,461 Records 149.6 GB
--
MySQL General Mailing List
For list archive
Hi,
Does anyone now the maximum number of records and fields in MySQL 4.0.22 Win 32?
Regards,
--
Saiful Akbar
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I think you mean that you want to import .txt or .csv data into an mysql
table...
http://phpmyadmin.net can do that via a web form to upload plus has many other
good
admin features.
-Original Message-
From: Steve Grosz [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 13, 2005 1:56 PM
NaviCat
Thanks
Donny Lairson
President
29 GunMuse Lane
P.O. box 166
Lakewood NM 88254
http://www.gunmuse.com
469 228 2183
-Original Message-
From: Steve Grosz [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 13, 2005 2:56 PM
To: mysql@lists.mysql.com
Subject: Import Excel data into tab
Can anyone tell me a good way to import individual column data into a
table? Is there a tool to assist with this?
Thanks,
Steve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
There may be other ways to get at the information you want. What is the
purpose of your query?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
2wsxdr5 <[EMAIL PROTECTED]> wrote on 01/13/2005 01:57:31 PM:
> I have these 2 queries.
>
> SELECT count(*) gifts
> FROM gift g
Hello.
Changing the basedir variable doesn't automatically changes the datadir
variable.
This can produce the problems. I've reported a bug:
http://bugs.mysql.com/bug.php?id=7873
You can use my suggested patch or specify the options in the option file. See:
http://dev.mysql.com/doc/
Hello.
Use CAST(). See:
http://dev.mysql.com/doc/mysql/en/Charset-CAST.html
Jens Schreiber <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I'm using 4.1.8-standard on linux and get this:
>
> CREATE TABLE test (field1 CHAR(20) BINARY NOT NULL, field2 CHAR(20)
> NOT NULL) TYPE=MyISAM;
Hello.
> I unchecked the 'install as windows service' and tried to start it
> standalone from a command prompt, but I got "ERROR 2003 : CAN'T
> CONNECT TO SERVER ON 'LOCALHOST' <10061>
What program have you started? It looks like it was mysql client program.
Start mysqld instead. Have you
Mattias,
have you observed these:
http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html
Do you have table names containing accent characters like ä?
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoD
How about ...
select @a:=count(*) from ... where ...
union
select @b:=count(*) from ... where ...
union
select @[EMAIL PROTECTED];
PB
---
2wsxdr5 wrote:
I have these 2 queries.
SELECT count(*) gifts
FROM gift g
WHERE g.this and g.that
SELECT count(*) events
FROM events e
WHERE e.this and e.the
Can subquery return more than one column in Mysql 4.1.7?
SELECT (select user.last_name, user.first_name from actor where
user.id=6) as last_name, first_name
(select count(*) from account where account.status = 'progress') as
progress_count,
ERROR 1241 (21000): Operand should contain 1 column(s
I have these 2 queries.
SELECT count(*) gifts
FROM gift g
WHERE g.this and g.that
SELECT count(*) events
FROM events e
WHERE e.this and e.the other thing
is there a way to put these into one query.
SELECT count(g.*) gifts, count(e.*)
FROM gift g, event e
WHERE . . . .
so far nothing seems t
You obviously do not understand auto-increment capability. You wouldn't
"get that value" of the sequence, because you would always retrieve the
same value. You would have to add a row to the 'extra' table for the
auto-increment field to work as designed, then use the highest value of
auto-inc
"Scott Purcell" <[EMAIL PROTECTED]> wrote on 01/13/2005 01:05:01 PM:
> I understand the auto-increment capability, but how would one share
> it amount three different tables?
>
> Would one have to do a rig, and create an extra table with increment
> in it, then get that value to update table 1,
Your first fix failure (command prompt start) sounds like you started the
CLIENT not the SERVER.
CLIENT = mysql.exe
SERVER = mysql-nt.exe or mysqld-nt.exe or... (there are several versions
that come with each distribution)
Your second fix failure matches with your installation error. There is n
I understand the auto-increment capability, but how would one share it amount
three different tables?
Would one have to do a rig, and create an extra table with increment in it,
then get that value to update table 1, then repeat process again to get new
increment and put it into table 2, etc.?
Is there an equivalent to Oracle's INSERT ... RETURNING? Note that
there is a related discussion in the archives about getting the
auto-incremented id, but that doesn't cover the more general case of
wanting to retrieve a value that has been altered by a trigger into a
variable.
thanks in adva
>How do I exclude some rows in a table? I am merging columns from three
>tables all of which show all congressional districts in all states. I
>want to exclude those congressional districts in TX, PA and ME. My coding
>that brings up data for all congressional districts is shown below.
On Thu, 13 Jan 2005 11:21:31 -0600, Scott Purcell wrote:
>
> I would like to create a sequence object in mysql that I could use in
> multiple tables through a application I am developing.
Sequences are currently not supported in MySQL.
Jochem
--
MySQL General Mailing List
For list archives: h
You want AUTO_INCREMENT,
http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html.
PB
-
Scott Purcell wrote:
Hello,
I would like to create a sequence object in mysql that I could use in multiple
tables through a application I am developing.
Could someone please send my the syntax of how
[snip]
I would like to create a sequence object in mysql that I could use in
multiple tables through a application I am developing.
Could someone please send my the syntax of how to create a simple
sequence that starts at 1 increments by 1.
[/snip]
Are you talking about auto-increment?
http://d
Hello,
I would like to create a sequence object in mysql that I could use in multiple
tables through a application I am developing.
Could someone please send my the syntax of how to create a simple sequence that
starts at 1 increments by 1.
thanks,
If I make a connection as follows:
$getcnnctd = @mysql_connect('this', 'that', 'bigsecret');
then select my db, then
$res = mysql_query("select * from thedb");
when finished using the db etc., am I assuming correctly that I should:
mysql_free_result($res); to free the memory up
and
mysql_close(
OK, just for yucks, I tried two versions of this in the SQL Server Query
Analyzer. The group by version had 1/3 the cost of the subquery version. The
last_name field had a clustered index.
I then did the same thing in MySQL, and lacking any other cost measurement,
looked at execution time. Before
There are mostly just 2 or 3 corrupted tables out of about 100 in each
database/schema/namespace and if possible we would like to keep the
information in the other tables.
At 2005-01-13 17:47, you wrote:
If you don't have any data in innodb, delete and recreate the
tablespace including the frm f
Whoops - every c/c++ program must have a main function.
That's what the cryptic message was...
Andy
On Wed, 2005-01-12 at 17:35 +, Andy Ford wrote:
> Hi everyone.
> I have been putting together a c++ wrapper for the mysql c libraries and
> have stumbled across the following error...
>
> >g++
If you don't have any data in innodb, delete and recreate the
tablespace including the frm files. This will give you a fresh 4.1
table space to import into.
-Eric
On Thu, 13 Jan 2005 16:37:39 +0100, Mattias J <[EMAIL PROTECTED]> wrote:
> Earlier today we tried upgrading one of our MySQL servers
Hi, Can anyone help please, I am trying to install 4.1 onto this machine as
a windows service but installation will not proceed and I get the message:-
"Resource messagedlg-confirmation not found"
So:-
I unchecked the 'install as windows service' and tried to start it
standalone from a command p
> I have table account (see below). I need to get count of received,
> count of send and cound of cancelled records. I know that I can do
> this in 3 queries.
> #1. select count(*) from account where status='received';
> #2. select count(*) from account where status='send';
> #3. select count(*) fr
Ian Sales wrote:
- I always concatenate the wanted criteria (status, in this case) with a
unique ID field in order to be absolutely certain I'm counting every
instance of each value of status. I've not investigated to see whether I
*really* need to do this, but it feels safer...
- ian
Then that
On 13 Jan 2005 at 10:47, Sid Lane wrote:
> hope this isn't considered too off-topic but...
>
> I have been working on standardizing the directory trees on our MySQL
> servers (a la OFA for those who speak Oracle) but when I repoint the
> socket parameter in my.cnf all my perl scripts barf w/:
>
Earlier today we tried upgrading one of our MySQL servers from 4.0.15 to
4.1.8 using RPMs. This resulted in corrupted InnoDB tables seemingly
randomly selected across the different databases (about 40 databases with
similar schemas plus an addition 5 with different tables).
The error message wh
hope this isn't considered too off-topic but...
I have been working on standardizing the directory trees on our MySQL
servers (a la OFA for those who speak Oracle) but when I repoint the
socket parameter in my.cnf all my perl scripts barf w/:
"Can't connect to local MySQL server through socket
'/
[snip]
What is the purpose of DISTINCT here?
[/snip]
It's a hold over from other RDBMSs that I have worked with in the past.
The syntax is still valid and I pretty much automatically type it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http:/
Jay Blanchard wrote:
[snip]
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. s
With conditions in the WHERE clause. Add something like
AND fh1109.state NOT IN ('TX', 'PA', 'ME')
to yours.
Michael
[EMAIL PROTECTED] wrote:
How do I exclude some rows in a table? I am merging columns from three
tables all of which show all congressional districts in all states. I
want to
[EMAIL PROTECTED] wrote:
I would think that the Jan solution should execute in a single pass
through the table. The Dave (subquery) version will probably need to make 3 passes through the table get the same results.
- er, it's Ian, not Jan :-)
As was mentioned in another response, the inequalit
Hi,
I guess you juat should add on a " AND fh1109.state NOT IN
('TX','PA','ME')"
Also I think your AND conditions are a bit overdetermining, you can
probably do just :
"WHERE fh1109.state = ssa1202.state and
fh1109.cd = ssa1202.cd and
fh1109.state = vapall.state and
fh1109.cd = vapall.cd and
A
Something like:
select
fh1109.state,
fh1109.cd,
fh1109.party,
fh1109.representative,
ssa1202.total,
((total-children*percentunder18)/vapall)*100,
ssa1202.retired_workers,
ssa1202.disabled_workers,
ssa1202.widow,
ssa1202.wives_and_husbands,
ssa1202.children
from ssa1202, fh1109, vapall
[snip]
How do I exclude some rows in a table? I am merging columns from three
tables all of which show all congressional districts in all states. I
want to exclude those congressional districts in TX, PA and ME. My
coding
that brings up data for all congressional districts is shown below.
We are pleased to announce the release of the DBManager Professional 3.0.2.
This version is a major bug fix with a small number of new features:
Fixes
a.. Fixed Load Dump which affected most of the engines supported
b.. Closing Workspace with other Views opened will close all views
c.. Fixed
[snip]
What is the difference between '='' and '<>'?
[/snip]
= EQUAL TO
<> NOT EQUAL TO
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is the difference between '='' and '<>'?
TH
On Thu, 13 Jan 2005 09:13:07 -0500, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> I would think that the Jan solution should execute in a single pass through
> the table. The Dave (subquery) version will probably need to make 3 passes
> through
How do I exclude some rows in a table? I am merging columns from three
tables all of which show all congressional districts in all states. I
want to exclude those congressional districts in TX, PA and ME. My coding
that brings up data for all congressional districts is shown below.
Than
I would think that the Jan solution should execute in a single pass
through the table. The Dave (subquery) version will probably need to make
3 passes through the table get the same results.
As was mentioned in another response, the inequality comparisons
(=,<,>,etc.) are much faster than a LI
A varchar will take up less disk space than a char. A char is padded to
fill it's length, so a index on char will be much larger than a
varchar, depending on content.
Numbers work differently. An index on a number column should be faster
than the same sized char or varchar column. First a forem
I'm a MySQL newbie, though I've worked with SQL Server and other RDBMS for a
long time. That said, I'm *guessing* that the stored proc solution would be
the fastest, since its execution plan is precomputed, but a quick look seems
to say that stored procs are only available in v5, still in alpha.
O
[snip]
What query suppose to be faster? Jan or Dave?
[/snip]
Queries using equalities are generally faster than LIKE. YMMV.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[snip]
This is what I was looking for. Why the query is call cross-tab?
[/snip]
It is called a crosstab because of the way the reults resemble a results
table. Here is an article that shows the crosstab in larger use
http://www.evolt.org/article/Using_MySQL_and_PHP_to_Present_Excel_Spread
sh
What query suppose to be faster? Jan or Dave?
Jan Solution:
SELECT
date,
SUM(IF(CONCAT(status,id) LIKE 'received%',1,0)) as received,
SUM(IF(CONCAT(status,id) LIKE 'send%',1,0)) as send,
SUM(IF(CONCAT(status,id) LIKE 'cancelled%',1,0)) as cancelled
FROM
account
GROUP BY date;
On Wed, 12 Jan 2005
Mads Kristensen <[EMAIL PROTECTED]> wrote on 13/01/2005 12:42:13:
> Is it true that I can only store 65535 bytes (2^16-1) in a field of type
> BLOB? I thought that a BLOB was supposed to be able to hold 2^16 bytes
> of data.
>
> If it is indeed only possible to store 2^16-1 bytes I would like
This is what I was looking for. Why the query is call cross-tab?
TH
On Thu, 13 Jan 2005 02:09:45 +, Ian Sales <[EMAIL PROTECTED]> wrote:
> Jerry Swanson wrote:
>
> >| Field| Type
> > | Null | Key | Default | Extra |
> >+--+-
Ok. I am replying to my own post here ;-)
I guess that the reason is the fact that only 2 bytes are reserved for
storing the size of the BLOB, and since an unsigned short's maximum
value is 2^16-1 this is also the maximum size of the BLOB.
So, mystery solved :-)
- Mads
Mads Kristensen wrote:
Is it
[snip]
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from a
On Thursday 13 January 2005 12:42, Mads Kristensen might have typed:
> If it is indeed only possible to store 2^16-1 bytes I would like to know
> why this has been implemented in that way (yes, I am always this curious
> ;-)
http://dev.mysql.com/doc/mysql/en/String_type_overview.html
--
MySQL G
Hello,
I'm using 4.1.8-standard on linux and get this:
CREATE TABLE test (field1 CHAR(20) BINARY NOT NULL, field2 CHAR(20)
NOT NULL) TYPE=MyISAM;
INSERT INTO test VALUES('xxx','yyy')
SELECT * FROM test WHERE field1=field2
ERROR 1267 (HY000): Illegal mix of collations (latin1_bin,IMPLICIT)
and (l
Is it true that I can only store 65535 bytes (2^16-1) in a field of type
BLOB? I thought that a BLOB was supposed to be able to hold 2^16 bytes
of data.
If it is indeed only possible to store 2^16-1 bytes I would like to know
why this has been implemented in that way (yes, I am always this curi
Jerry Swanson wrote:
[...]
I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from account where s
Sign the list
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hi,
- 10 products in both cases. One time the column is a MediumInt, the
other time a BigInt. I know there is a difference in disk space usage, but
is there also one in performance at all ?
I'm not sure, this apply to your case. I had set a unique index on a
char(50) and it was 2x slower than
74 matches
Mail list logo
