Hi Harald,
> Replacing the index on member_id by a compound index on (member_id,
> id) might help.
Thank you very much for your suggestion. After replacing the index with
compound index, the query works very fast now.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mys
I was just reading a forum post on mysql, someone said it was possible in
mysql 4 to insert data into more than one table at once. Is this possible,
if so, where in the docs can I see a example of this?
--
-
Scott Haneda
Hi,
When an attempt is made to Create a Function that was dropped previously the mysql
gets restarted.
I have loaded the so into /usr/lib directory.
The mysql is statically linked .
The mysql error log stated that it has got signal 11 & has the following
key_buffer_size=8388600
read_buffer_si
I know how to import files ending with a .sql
extension with phpMyAdmin. However, I just downloaded
a database table that I want to import into a local
database - but it has a .dmp extension. What's the
best way to get this into a MySQL table?
Am I correct that the .dmp extension suggests
somethin
(Apologies in advance for the crossposting, but I asked the same questions
on the MySQL Windows list and didn't get any replies)
I need a simple backup mechanism for MySQL (3.2x) that will backup all
databases on a server. Something that can be run from a scheduled Windows
batch file. From what
easily delete old data through
> dropping the old tables, instead of using DELETE. (At present, we're
> inserting data into this table faster than we can remove it...)
>
> I was wondering if anyone has any experience and advice to
> give on going
> down this path, that they w
I finally know what the problem is.
The problem is the new ODBC Driver (3.51.08). I wasn't sure until today,
because i uninstalled it (at least that's what i tried) and installed
version 3.51.06 again. But when i went to see the MyODBC3.DLL file it was
still version 3.51.08!! So i deleted this
I am stuck on how to design a set of tables. I hope someone can help me. I
am using mysql 4.0.18-standard.
A little background, this is a mailing list manager, it support multiple
account holders. User_id is the account holders id, this is unique auto inc
and carried across all table to make su
hi
i have a large text blog that i want to extract strings out of.
the large text blob looks like "Name: john doe Date: 060604
Subject: this is subject Text: ";
i want to just extract 'john doe' '060604' and 'this is subject'
from the text blob
can i do this w mysql queries, or do i have to us
MySQL Server 4.1.1 alpha
MyODBC Connector: 3.51.08
Hello all,
I am experiencing the following problem: I have an dialog-based MFC app using the
CDatabase class to connect to a MySQL DB using ODBC. My connection string specifies a
DSN with the connection information. Additionally, this is a Unic
Okay, I got the RPM package installed but when I use rpm -I
mysql-server-4.0.20-0.i386.rpm, I am told that I need several files, most of which I
can't find in the Debian package library. I need the following files: /usr/bin/perl,
sh-utils and sh, where can I get these?
Hello All,
Thank you for reading this email.
I have the following Configuration
Windows 2000 Server
MySQL Win32 Binaries Installed
Current Release of Cygwin installed
I am trying to access mysql from the bash shell rather than the command
line.
It was working fine until now, but for some reason I
Hello,
We're running 1 web server (apache 2 & php) / 1 dedicated DB server
(MySQL 4.0.20-standard) and are experiencing serious performance issues
on the DB during some load testing.
Hardware on both
Dual Xeon 2.8GHz, 2GB RAM
The database size is a little under 1 GB.
Naturally, we started takin
Hello,
I have MySQL ODBC Connector 3.51.08 for Windows. I downloaded the installation package
from mysql.com. Is this driver thread-safe? If not, how do I compile a thread-safe
version? The instructions I found online seem to refer to the Linux platform.
Sincerely,
Silvio Lopes de Oliveira
Hello All,
We recently upgraded our mysql server from 3.23 to 4.0.18 and have found
that all of our Unicode characters are now being displayed as question marks
(?). Anyway this only happens when viewing over the web as when we view via
the shell mysql we can see the characters fine. In addition i
> Hello:
>
> I posted this before but I have made some changes since then (not that
> they helped much). I have a Debian (woody) server running a good sized
> database (7.2GB of db files), Mysql 4.0.18. I am running Apache 1.3.29
> + perl and using mysql as the backend. In my.cnf, I set
> max_
Hello:
I posted this before but I have made some changes since then (not that
they helped much). I have a Debian (woody) server running a good sized
database (7.2GB of db files), Mysql 4.0.18. I am running Apache 1.3.29
+ perl and using mysql as the backend. In my.cnf, I set
max_connections=3
--- matt ryan <[EMAIL PROTECTED]> wrote:
> >Do you ever delete from this table?
>
> Temp table is trunicated before the EBCDIC file is
> loaded
I meant the history table :)
> >Have you removed the unecessary duplicate key on
> the first column of your primary key?
>
> Have not touched the DIC in
You were so very close to getting what you wanted!
What is causing the problem is the comma (,) in your FROM clause. MySQL
permits two methods of declaring an INNER JOIN. The first is by using the
keyphrase "INNER JOIN" the second is with a comma in your table list. Here
is how to rephrase your
Michael Gale wrote:
Hello,
Right now we are running a mysql-max db with InnoDb for transaction tracking. This is a older version of mysql running
mysql-3.23 build 50. We are planning on upgrading to mysql 4.0 latest in a couple of weeks (hopefully).
Anyways in order to do proper backups with Inn
Hello List,
I have a problem that I am looking your help for. Would greatly appreciate it. Here is
what I am trying to do:
create table C (cId tinyint(4), cName varchar(10));
insert into C values (1,'Cat01'), (2,'Cat02'), (3,'Cat03'), (4,'Cat04');
create table SC (scId tinyint(4), cId tinyint(4
Do you ever delete from this table?
Temp table is trunicated before the EBCDIC file is loaded
Have you removed the unecessary duplicate key on the first column of your primary key?
Have not touched the DIC index yet, I need a backup server to change
indexes, it would take the main server down for t
I'm new so this may be whacked but here goes:
Isn't the grant statement tailed with the grant option?
i.e.
GRANT ALL ON table_or_database_name to 'somebody'@'localhost' WITH GRANT
OPTION;
Somebody verify that please.
> -Original Message-
> From: John R. Porter [mailto:[EMAIL PROTECTED]
At 11:26 -0700 7/21/04, ginger cheng wrote:
Hello, MySQL gurus,
I just installed mysql 4.0.20 on an athlon i686 linux red
hat. Then I copy the my-huge.cnf to /etc/my.cnf and only added these
2 lines to the file
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/host.pid
when I u
I don't see how using a multi value insert would be
any faster than the insert between the tables. It
would certainly be faster than one insert statement
per row, but I don't think it would be faster than
insert ... select ...
The only reason I suggested an extended syntax insert
earlier was beca
Hello, MySQL gurus,
I just installed mysql 4.0.20 on an athlon i686 linux red hat. Then I copy
the my-huge.cnf to /etc/my.cnf and only added these 2 lines to the file
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/host.pid
when I use 'sudo -b mysqld_safe --user=mysql' to star
mysql -i < filename.sql
matt ryan wrote:
Lopez David E-r9374c wrote:
Since you have a temp table created (no keys I assume), use the command
mysqldump -v -e -n -t dbname tablename > filename.sql
This creates a file that inserts the records back into the same table
it also does not do an insert
Hello,
Right now we are running a mysql-max db with InnoDb for transaction tracking.
This is a older version of mysql running
mysql-3.23 build 50. We are planning on upgrading to mysql 4.0 latest in a couple of
weeks (hopefully).
Anyways in order to do proper backups with InnoDB I am g
Lopez David E-r9374c wrote:
Since you have a temp table created (no keys I assume), use the command
mysqldump -v -e -n -t dbname tablename > filename.sql
This creates a file that inserts the records back into the same table
it also does not do an insert ignore
I need the records to go into the
'GRANT GRANT ON table_or_database_name ...' gave a syntax error.
'GRANT GRANT OPTION ON table_or_database_name ...' worked, but the result
was as before, i.e. the new user still couldn't grant access to the table or
database.
Inspection of the grant tables showed the following:
Table 'user'
Field
Egor,
It is the proper binary from mysql.com.
mysql-standard-4.1.3-beta-pc-linux-i686.tar.gz
I have also filed a bug report for this problem. #4673
There is some extra information in the bug report describing what my
application does.
On Wed, 21 Jul 2004 17:59:58 +0300, Egor Egorov <[EMAIL PROT
There is REPLACE... SELECT...
http://dev.mysql.com/doc/mysql/en/REPLACE.html
Thanks
Aman
On Wed, 2004-07-21 at 11:32, aman wrote:
> Replace will be useful if you mean to delete the previous duplicates and
> then enter the new data.
> Moreover there is no REPLACE... SELECT like there is INSERT...
As indicated earlier in one of my responses, INSERT... SELECT will allow
you to grab a subset from one table and put in the other. You can also
use the WHERE clause to apply conditions to your subset.
Aman Raheja
http://www.techquotes.com
On Wed, 2004-07-21 at 11:22, bruce wrote:
> andrew...
>
Replace will be useful if you mean to delete the previous duplicates and
then enter the new data.
Moreover there is no REPLACE... SELECT like there is INSERT... SELECT
which make life easy!
Aman Raheja
http://www.techquotes.com
On Wed, 2004-07-21 at 11:12, Laercio Xisto Braga Cavalcanti wrote:
>
gerald
It appears your problem is reading in blob columns is possibly exhausting
resources
I would suggest you read in more fine-grained (smaller) chunks
Here is some sample code for you to use
Start the chunking size at your requested size and then attenuate the size
by 1/2 with each iteration
andrew...
thanks for the response... load_data looks reasonable, however, it appears
that it won't be applicable for my situation.
i have a table with 5-6 items in each row. the table that i want to insert
the data into, needs to have a subset of these items in each row...
there doesn't appear t
Also, if you have any doubts whether your changes took the right effect,
check the mysql database on your server and check out the tables that
store privileges assigned by your GRANT query.
Aman Raheja
http://www.techquotes.com
On Wed, 2004-07-21 at 11:11, Martin Gainty wrote:
> John-
>
> GRANT
Java is case sensitive, this is the way JDBC wants you to work. The
Exception is generated by JDBC, not due to an error from MySQL. I would
recommend checking JDBC documentation.
Aman Raheja
http://www.techquotes.com
On Wed, 2004-07-21 at 10:55, Ying Lu wrote:
> Hello,
>
> I have a question ab
You can use replace command instead of insert. It will handle duplicate
rows.
Regards,
Laercio.
-Original Message-
From: aman [mailto:[EMAIL PROTECTED]
Sent: quarta-feira, 21 de julho de 2004 13:01
To: [EMAIL PROTECTED]
Subject: RE: insertion of multiple rows
I agree with Andrew - th
John-
GRANT privilege ON table_or_database_name TO [EMAIL PROTECTED] IDENTIFIED BY
'password'
in this case you need to
GRANT GRANT ON table_or_database_name TO [EMAIL PROTECTED] IDENTIFIED BY
'password'
Hope this helps,
Martin Gainty
SQL Consultant
USA 001-617-852-7822
From: "John R. Porter"
I agree with Andrew - this is quicker.
And if you area transferring data from one table to the other, you may
wanna look at
http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html
Thanks
Aman Raheja
http://www.techquotes.com
On Wed, 2004-07-21 at 10:51, Andrew Hill wrote:
> Hi Bruce,
>
> You migh
Hello,
I have a question about column name case sensitive. Currently, we use
MySQL 3.23.32 running on Linux.
I am trying to use JDBC to get email from table T1
T1(
id char(3),
name varchar(12),
Emailvarchar(16)
)
Now If I say,
try{
...
String email =
getString("email");
ndering if anyone has any experience and advice to
> give on going
> down this path, that they would be willing to share?
>
> Specifically, I'm concerned by the possibility of inserting data into
> the "wrong" table: Say a client notes that the date is 20040721
> 23:59
Hi Bruce,
You might be interested in the LOAD DATA INFILE syntax. See
http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html.
The command can either be used to replace duplicate records on unique
keys, or can ignore them.
Cheers,
--
Andrew Hill
Developer
Awarez Ltd.
Kirkman House, 12-14 Whitfield Str
"bruce" <[EMAIL PROTECTED]> wrote on 21/07/2004 16:35:34:
> hi...
>
> quick question that i can't find an answer to. i have 500-100 rows of
data
> that need to be inserted. i can do it a row at a time.
>
> is there a way i can do the inserts all at one time, reducing the hits
on
> the database
asily delete old data through
> dropping the old tables, instead of using DELETE. (At present, we're
> inserting data into this table faster than we can remove it...)
>
> I was wondering if anyone has any experience and advice to
> give on going
> down this path, that they w
hi...
quick question that i can't find an answer to. i have 500-100 rows of data
that need to be inserted. i can do it a row at a time.
is there a way i can do the inserts all at one time, reducing the hits on
the database?? and if i can do the inserts all at once, how do i handle
possible duplic
any experience and advice to give on going
down this path, that they would be willing to share?
Specifically, I'm concerned by the possibility of inserting data into
the "wrong" table: Say a client notes that the date is 20040721
23:59:58, and so decides to insert the data into the tbl
Lachlan Mulcahy wrote:
MySQL Version: 4.0.18
Server OS: windows 2000, or 2003
Memory 2 gig
CPU(s) dual 2.6-3ghz xeon 500-2mb cache (cpu load is low)
Disks (RAIDs and Independent disk speed/types) 8x72 gig 15,000 rpm scsi
II u320 raid 5 dell perc setup
--
MySQL General Mailing List
For list arch
"Levi Campbell" <[EMAIL PROTECTED]> wrote:
> Hi, I'm trying to install MySQL on Debian Linux on an old computer whe =
> had lying around. (Pentium 2 or three) and I'm trying to install the RPM =
> files so I can install the software. my problem is with the Perl debian =
> packages, I can't get the
I load all the data into a table with no keys
then I insert this data into a table with 225 million records, this
large table has the primary key, this is what takes a LONG time
Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysq
Nathan <[EMAIL PROTECTED]> wrote:
> Does anyone know of any written stats on how much overhead for CPU/ Disk
> IO replication has for a single master and a single slave?
> I am looking for any detailed stats on the proformance issues associated
> with replication.
In a very rough oversimplificati
Richard Clarke <[EMAIL PROTECTED]> wrote:
> I have switched to the mysql standard binary and it helped in no way
> at all. If anything it made the situation worse. It seems that mysql
> grows even worse than before.
> Mysql uses an extra 1MB roughly every 5-10 seconds.
"mysql standard binary" - i
a client notes that the date is 20040721
23:59:58, and so decides to insert the data into the tblname_20040721
table - and then, when the insert happens, it's now 20040722 00:00:01.
If the table has a timestamp column that is set by the MySQL server,
then the timestamp will be in 20040722, but
I know you are on the right track: Standardize first, then deduplicate.
In my experience, SQL is not a great language for doing this kind of text
manipulation standardizing sometimes requires some rather complex logic.
You should be able to easily justify the cost of a commercial app to
stand
Have you tried playing around with your configuration parameters,
specifically the sort buffer? I'm guessing that what is making the
query slow is not the search, but the sorting of 9k rows if I read your
results right. Easy enough to test, just drop the ORDER BY and see how
long it takes. If i
hi
i'm trying to compile my C app using mysql embedded stuff, but i get the following
linking error. is this a mysql 4.0.20 bug? should i replace -lmysqld with
-lmysqlclient when compiling for embeeded mysql? (does solve the problem.)
gcc -L/usr/lib/mysql -ofdlog fdlog.o util.o fdipc.o dbl
Greetings all,
I tried the PHP list with this one and can't seem to get an answer so I
thought I'd try here.
I upgraded from MySQL 3.23 to 4.0. I placed all the new mysql files in
/usr/local/mysql/bin, /usr/local/mysql/include and
/usr/local/mysql/lib. I compiled PHP5 and it is still using th
Validating addresses into a standard format for deduplication is a field in
its own right.
If you want to do this in a meaningful manner than you'll need to validate
the addresses prior to database load using a product such as Global Address
and then to do the comparison which an application that
You might also find SOUNDEX useful.
http://dev.mysql.com/doc/mysql/en/String_functions.html
Peter
> -Original Message-
> From: Duncan Hill [mailto:[EMAIL PROTECTED]
> Sent: 21 July 2004 15:10
> To: [EMAIL PROTECTED]
> Subject: Re: Mailing Address Deduplication
>
>
> On Wednesday 21 July
Hello All,
Thank you for reading this email.
I am trying to figure this problem out, but I need some help.
I am working on a Windows 2000 Server. I have used MySQL for the past 2 years on this
system.
I recently installed the DEFAULT version of CYGWIN on my machine.
Everyone was wonderful
On Wednesday 21 July 2004 15:04, Andrew Kuebler might have typed:
> I know this is not necessarily a MySQL question, but everyone on this
> listserv is always so helpful and I was wondering if anyone had any
> pointers on how to deduplicate a list of mailing address since there can
> be so many inc
I know this is not necessarily a MySQL question, but everyone on this
listserv is always so helpful and I was wondering if anyone had any
pointers on how to deduplicate a list of mailing address since there can
be so many inconsistencies on how an address can be written (road vs rd vs
rd., etc). I
I created a new user with a GRANT statement like:
GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO 'demouser'@'localhost'
IDENTIFIED BY 'testpwd' WITH GRANT OPTION
If I then log in as 'demouser' I can execute SELECT etc. statements on the
'test' database, but if I try to create another user with
You don't say how you retrieved the data or how you are checking the
size of the returned data, but beware.
Most languages will interperate a byte with a value of zero as the end
of a string.
Assigning the result to a string, or doing a length() of a returned
block will give a false answer.
jon
I would try these 3 query statement to find the users for the bracketing
times (start and stop):
SELECT @starttime = max(time)
FROM logtable
WHERE IP='66.50.xxX.245'
AND Date='2004-07-05'
AND Time <='16:15:00'
AND RecordType = 'Start';
SELECT @endtime = min(time)
FROM log
In article <[EMAIL PROTECTED]>,
Batara Kesuma <[EMAIL PROTECTED]> writes:
> Hi,
> I use InnoDB for my table, and I think I have hit the point where I
> can't do query optimization any more.
> The query is as simple as below:
> SELECT SQL_NO_CACHE diary_id, LEFT(body, 28) AS body , id FROM
> diar
hi all,
I try to port some of the mysql api to a microcontroller and now i wonder
if i miss something.
I try to retreive a blob (256K) in chuck of 1024 bytes. I use the keywork
substring with index 1 and size 1024 and the sql server only return 252
bytes instead of 1024.
I wonder if i can use a
Try this from the manual,
http://dev.mysql.com/doc/mysql/en/Server_system_variables.html.
init_connect
A string to be executed by the server for each client that connects. The
string consists of one or more SQL statements. To specify multiple
statements, separate them by semicolon characte
Jan Kirchhoff <[EMAIL PROTECTED]> wrote on 21/07/2004 13:08:38:
> what a about the very simple approach?
> This should be very fast if you habe indexes on ip, date, time and
> record_type.
I would have though it would be best to have a composite index on
date/time/record_type, which is what
Help needed.
To have the possibility to make a nodeview in visual basic, I have made
a child-parent relation in one of my tables.
Table fields:
TTNodeId (autonumber, indicating a node)
NodeParent (NodeNumber of the parent)
NodeLevel (Level of the node)
NodeName (Name of the node)
To make it reada
[EMAIL PROTECTED] wrote:
Problem: Spam Abuse
IP of offender: 66.50.xxX.245
Date of offense: 2004-07-05
Time of offense: 16:15
Now if I query the database based on date and ip address, I get the
following:
Id Date Time Record TypeFull
Name IP Add
Ok, this may or may not be a tricky one I will try and be succinct in my
statement.
I have a database (mysql 4.0) with radius log entries for each day, we
receive emails about Acceptable Use Abuses and must figure out exactly
who was online with a certain IP address when the abuse occurred. As yo
Hi All,
I'm working on a guestbook and have a problem with updating a table with php..
in the guestbook entry, i have a textarea that allows users to enter their comments,
in multiple lines.
however, once i get it out to prepare to put into sql database, it becomes a single
line. i know t
Hello.
(B
(BI'd like to ask about backup/restore(roll forward recovery) solutions
(Busing mysqldump and binary logs.
(B
$B!&(JVersion : 4.0.20
$B!&(JUsing 1 database.(ex. sample1db)
$B!&(JAll tables are InnoDB.
(B
(B
(BI'm considering the following solution.
(B-
(Bbackup
Thanks for your reply that's was really what i thought.
> My understanding is that you have to have MySQL 4.1 for this sort of
thing
> to work at all. Which is why I am so keen to see 4.1 reach production
> ASAP. Pre-4.1, you can store UTF-8, but it will not sort correctly.
>
> Alec
Jean-
Hi,
I'm using a mysql 4.0 version and i wanted to know whether i can realize
a multi-language site with eastern languages like japanese.
I'm using a database which has already data encoded with the latin
charset (iso-8859-1) and need to include japanese data.
How may I proceed to have comparisons
Hi,
I use InnoDB for my table, and I think I have hit the point where I
can't do query optimization any more.
The query is as simple as below:
SELECT SQL_NO_CACHE diary_id, LEFT(body, 28) AS body , id FROM
diary_comment WHERE member_id='343' ORDER BY id DESC LIMIT 15;
And it took about 5 seconds
According to:
http://dev.mysql.com/doc/mysql/en/COMMIT.html
>With START TRANSACTION, autocommit remains disabled until you end the
>transaction with COMMIT or ROLLBACK. The autocommit mode then reverts
>to its previous state.
Is there anyway to disable the revert? I don't want to have to SET
@@
on 7/20/04 11:05 PM, Michael Stassen at [EMAIL PROTECTED] wrote:
> What is user_id? You didn't mention it before. Will the user with
> user_id=123 be able to make a 'work' group and the user with user_id=456
> also be able to make a 'work' group? That is, you have multiple lists
> (group) for e
80 matches
Mail list logo
