Mauricio,
have you used upper case letters in table names? What does mysqld print to
the .err log?
Please observe the following:
http://dev.mysql.com/doc/mysql/en/Moving.html
On Windows, InnoDB internally always stores database and table names in
lowercase. To move databases in a binary format
Terry,
thank you for reporting this. Since the cardinality reported by SHOW TABLE
STATUS is just an estimate, this is not strictly a bug, but it is best to
correct this anyway.
I modified the cardinality estimation algorithm to 4.0 in March 2004, but
forgot the case where the index B-tree just
Thanks for your reply.
I think on this machine MySQL was installed from an RPM distribution... All the
settings are default...
I seem to have an unisual setup as far as the logfiles are concerned. There are no
localhost.log and localhost.err files on this machine, nor any .log or .err files in
Hello everyone
I hope this is the right list for my question if not please let me know.
First I've got to say that I'm not an experienced query-builder so maybe
the answer to my problem is very easy. But I have no idea how I could solve
this problem and all my research (Internet and Forums) did
I find there is not much documentation about MaxDB.
Anybody know why do we need MaxDB?
Do you have a list of feature that is available on MaxDB only?
Thanks,
Joseph
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL
Hello Joseph,
you might want to look at these places:
General Overview: http://www.mysql.com/products/maxdb/index.html
Documentation, Articles, HowTos: http://dev.mysql.com/doc/maxdb/
Cheers,
Alexander Schröder
SAP DB, SAP Labs Berlin
-Original Message-
From:
Sergei,
ok, this explains it. Your buffer pool is too small. Look at the MySQL
manual.
All operations in InnoDB happen inside transactions. ALTER TABLE essentially
does
INSERT INTO newtable SELECT * FROM oldtable;
and sets lots of shared row locks on oldtable.
Best regards,
Heikki Tuuri
No problem, Heikki.
It's just a bit disconcerting to see '1' when '0' is expected. As for
larger numbers, I realise that the rowcount is only an estimate for
InnoDB.
Cheers
Terry
- Original Message -
Terry,
thank you for reporting this. Since the cardinality reported by SHOW
In article [EMAIL PROTECTED],
Salzgeber Olivier [EMAIL PROTECTED] writes:
I need to create a search Query to find all the hotels which have specific
services and sparetime activities.
For example:
Search for hotels where location is Bern and the hotel has sparetime
activity 2 and 5 AND
Yes, these are normally temp files.
-Original Message-
From: Sergei Skarupo
To: [EMAIL PROTECTED]
Sent: 7/15/04 12:09 AM
Subject: #sql-... files
Greetings All,
I see some files in the data dir with names like #sql-85d_4.MYI. Some of
these are pretty old. Are these the temp tables that
Hi,
I'm working on an event-driven mysql library in perl, and for that was
busy doing several low level tests, and I noticed the following:
If I send a CREATE_DB for a database that doesn't exist yet I get
something like this in strace:
write(5, \4\0\0\0\5foo, 8)= 8
read(5,
Our MySQL database server currently runs on a Win32 platform. If we
setup replication do the slaves have to also run on Win32 or can a
slave be a different platform such as Linux?
Tim
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
The slaves can be different. You will want to watch out for case
sensitivity.
-Original Message-
From: Tim McDonough
To: mysql-l
Sent: 7/15/04 7:28 AM
Subject: Replication with Different OS
Our MySQL database server currently runs on a Win32 platform. If we
setup replication do the
I'll be trying to make a SP on 5.0 that returns values through odbc but
it doesn't work, mysql client works fine; there is a limitation or
odbc not support return values yet?
ODBC Version 3.51.06
--
Greetings, Alejandro
mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list
Justin Swanhart wrote:
Indexes can generate vast amounts of random i/o.
Because of rotational latency, random i/o can really
slow you down, especially if you are using IDE or SATA
disks because they can't do tagged queueing like SCSI
disks can.
If you have the budget for it, I would consider
I am not familiar with oscommerce but since you are posting your question
here I assume that is has a MySQL backend.
You should be able to set up replication to do this. If you are planning to
run a full oscommerce database to store the offline products information,
you will need to use the
Hello everyone,
I'm having a hard time with the following query. It
retrieves about 3K rows from a few tables. One of them
contains over 40M rows. When run on a 3Ghz server with
1G of RAM it returns the rows in more than 1 mini. I
don't think that's normal.
Here's the output of EXPLAIN:
mysql
On Wed, 14 Jul 2004 11:17:20 -0500, Peter Brawley
[EMAIL PROTECTED] said:
Match() returns a graded relevance rating, snip...
you are looking for, though what you're looking for isn't entirely clear
from your post.
It'll do OK. I would like to suggest if there's a wish list to make
On 15 Jul 2004 at 6:27, Patrick Drouin [EMAIL PROTECTED]
wrote:
I'm having a hard time with the following query. It
retrieves about 3K rows from a few tables. One of them
contains over 40M rows. When run on a 3Ghz server with
1G of RAM it returns the rows in more than 1 mini. I
don't think
I would change the storage type on the table to an int and see how your
pass-through works then.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Daniel Cummings [EMAIL PROTECTED] wrote on 07/14/2004
09:08:55 PM:
I'm using SqlPassthrough In VFP 8.0
Ive got a
David,
With kindest respect, I think that what you are asking is really a
question about HTML formatting style and has very little to do with MySQL
or its TEXT data type. Please try to keep your posts relevant to MySQL. If
you really do have a question that relates to MySQL, please rephrase
Bonjour Arnaud,
--- Arnaud [EMAIL PROTECTED] wrote:
On 15 Jul 2004 at 6:27, Patrick Drouin
Your indexes look good, but I see that you have some
varchar fields.
Maybe
you could run an optimize table on these tables?
I'm running it at the moment, I will follow-up on the
list when it's done.
What version of MySQL are you using? Have you checked the cardinality on
these tables?
-Original Message-
From: Patrick Drouin
To: [EMAIL PROTECTED]
Sent: 7/15/04 8:27 AM
Subject: Need help optimizing query
Hello everyone,
I'm having a hard time with the following query. It
retrieves
Thank you very much.
Works perfectly now.
-Ursprüngliche Nachricht-
Von: Harald Fuchs [mailto:[EMAIL PROTECTED]
Gesendet: Donnerstag, 15. Juli 2004 13:58
An: [EMAIL PROTECTED]
Betreff: Re: query w multiple tables and expressions
This gives all hotels having at least one of the required
Hello Victor,
What version of MySQL are you using? Have you
checked the cardinality on
these tables?
Problem solved! Optimizing the table brought the query
time down to 17 secs Wow!
Thanks for the input Victor and merci to Arnaud for
the quick fix.
Patrick
I know it's naff but I've found it quicker to use myisamchk with row-sort
than it is to get the MySQL daemon to regenerate keys (and if you know
you're not changing the data file you can tell myisamchk not to copy the
data), unless I've missed something in the MySQL config ...
The only way I know
Tim Brody wrote:
You may find that the 'dic' KEY isn't necessary, as it's the first part of
your PRIMARY KEY.
I've found better performance for multi-column keys by putting the columns
in order of least variance first, e.g. for a list of dates:
1979-04-23
1979-07-15
1980-02-04
1980-06-04
You want
You almost had it right, your FINAL AND was blowing your syntax. You are
not really aggregating anything so you also do not need the GROUP BY
clause, Your ORDER BY clause should keep all of the hotels listed for you
.
SELECT *
FROM stammdaten
INNER JOIN relation_services ON stammdaten.id_PK
On Thursday, July 15, 2004, 9:10:43 AM, matt wrote:
mr Justin Swanhart wrote:
Indexes can generate vast amounts of random i/o.
Because of rotational latency, random i/o can really
slow you down, especially if you are using IDE or SATA
disks because they can't do tagged queueing like SCSI
disks
Shawn,
Many thanks for your very informative reply. It seems my server host will
not allow remote direct access to the databases; access is only by using
the cPanel software, which also gives access to a web interface of
something called phpMyAdmin which works just about ok for very basic
Consider replicating to some slave servers and dividing reads among them.
I already replicate to slaves, and sites will do read only queries off
these slaves
99.9 % of the tables are read only anyway, the only tables we update or
insert into, are very very small and fast.
These big tables are
Hum,
Well, I'm back with another one... When adding a
join to the previous query, it sloows down once again
even though it retrieves less datat. Here's the info :
mysql explain SELECT ti.posi, ti.docid, d.filename,
ti.id, c.name
FROM corpus_documents cd, corpus c, documents d,
tokens_ins ti,
matt on Thursday, July 15, 2004, 11:58:31 AM, wrote:
Consider replicating to some slave servers and dividing reads among them.
mr I already replicate to slaves, and sites will do read only queries off
mr these slaves
mr 99.9 % of the tables are read only anyway, the only tables we update or
mr
Those logs are not the kind of logs you are thinking of.. these are DB
logs which maintaining a running record of each insert/update
transaction for internal use by the DB.. The size can be changed in
my.cnf, but they are critical to the proper functioning of the database
and resizing them
Have you considered splitting this into two queries? One query can gather
information from your token and token_ins tables. The other would join the
first queries results to the other tables to complete your original query.
Depending on your data, one of these subqueries should return a smaller
adburne el Thursday, July 15, 2004, 12:45:05 PM, escribió:
aaca matt on Thursday, July 15, 2004, 11:58:31 AM, wrote:
Consider replicating to some slave servers and dividing reads among them.
mr I already replicate to slaves, and sites will do read only queries off
mr these slaves
mr 99.9 % of
- Original Message -
From: matt ryan [EMAIL PROTECTED]
I need the DIC in the key to keep the record unique, I have thousands
with everything identical except the DIC.
In your schema you had DIC in the PRIMARY KEY and an additional
(unnecessary?) KEY on DIC.
I was confused on the
1. Though I was not able to find it explicitly stated in the online
manual, I'm inferring from what I did find that the server v4.1.3 Win32
cannot store fractions of a second in DateTime columns (though oddly
enough, several existing DataTime functions do handle them). It was
stated that support
David Blomstrom [EMAIL PROTECTED] wrote on 07/14/2004 10:16:41
PM:
snip
I was just curious how most of you do it. Do you just
dump in your text, or do you go to extra lengths to
make sure each paragraph begins on a new line in your
database? Whay code do you use for line breaks, so it
looks
Hi Tom,
===
2. Will an ADO sqlTransaction that is begun but not explicitly
committed or rolled back (i.e. the session was terminated before the
transaction could be concluded) be automatically rolled back or
automatically committed by the server?
===
This question has a rather high duh factor
At 10:54 -0700 7/15/04, Tom Kirkman wrote:
1. Though I was not able to find it explicitly stated in the online
manual, I'm inferring from what I did find that the server v4.1.3 Win32
cannot store fractions of a second in DateTime columns (though oddly
enough, several existing DataTime functions do
You can stop creating the logs like rsl156-bin.00[123] (the bin logs) by
commenting out the log-bin line in your my.cnf, and restarting mysql.
You need them, though, if your server is a replication master.
More on the logs here:
http://dev.mysql.com/doc/mysql/en/Log_Files.html
Jim
Michael
You might be out of luck with MySQL ... sorry.
You may need to switch to a database that has a parallel query
facility. Then - every query becomes a massive table scan but gets
divided into multiple concurrent subqueries - and overall the job
finishes in a reasonable amount of time. The
Shawn-
Thanks for responding.
The field UserId is Int and its length is 11.
I could try different types of INTs.
In reference to this comment I used the following code.
I created a table from the parameter and it stored it in a table as a
double.
Create Table ChkValues Select ?pnUserId From
We have a Dell Poweredge 1750 with dual 3GHz Xeon's and 4GB of memory. It is
connected to a EMC CX600 through a Brocade Silkworm 2800 and QLogic HBA running
qla2300 v6.07.
Anyways, we are attempting to migrate to the 4 tree from 3.23.56. When we began doing
so I started running some
Hi,
I'm trying to create an index using the command:
CREATE INDEX urlindex ON internet_usage (urlid)
| Field| Type | Null | Key | Default | Extra |
| urlid| int(10) unsigned | | | 0 | |
But I keep getting the following error:
ERROR 3 at line
Are you files being created in /tmp? How big is this partition?
-Original Message-
From: J S
To: [EMAIL PROTECTED]
Sent: 7/15/04 2:29 PM
Subject: ERROR 3 at line 1: Error writing file '/proxydb/mysql/tmp/STTbtrac'
(Errcode: 27
Hi,
I'm trying to create an index using the command:
CREATE
That's the whole point. Eliminate your indexes and your load problems are
solved. Especially given the fact that you insert ignore and don't use the
primary key to validate uniqueness.
matt ryan [EMAIL PROTECTED]
07/15/2004 11:38 AM
To:
cc: [EMAIL PROTECTED]
Reordering the primary key wouldn't necessarily speed up the key check.
The reason for selecting a particular sequence within a primary key is to
put the columns that are available the most often - upfront - so that the
index will have at least something to bite on.
Can you parittion further?
a few suggestions...
Your slow inserts could be a concurrancy issue. If
lots of users are doing selects that could be
interfering with your inserts, especially if they use
a n odbc/jdbc app that locks the table for the entire
read. Jdbc reads do that when you tell it to stream
the contents of
Insert ignore doesn't insert the record if there is a
duplicate. It simply doesn't insert the row. Without
the IGNORE clause, the query would generate an error
insert of silenty ignoring the insert.
--- [EMAIL PROTECTED] wrote:
That's the whole point. Eliminate your indexes and
your load
Hello, I have a db that I did a mysqldump on. It contained different
entries for products some of which had the ascii code for the reg. mark.
When I looked at the export file, it had exported the reg mark itself
instead of the ascii code.
Is this normal, and how can I fix it...I've got bunches
Matt,
I've been reading this thread for a while and at this point, I would say
that you would need to provide the table structures and queries that you are
running.
For example, we have one table that has 8 billion rows in it and it close to
100 gigs and we can hammer it all day long without any
I've used it, with oracle, but oracles index
searches are better, hit
the best one first, then 2nd best, then 3rd, but I
really dont want to
go to oracle, it's too complicated for my tech's
Oracle rarely performs index merges, but it does have
the ability to do, which mysql lacks.
Query
Are you files being created in /tmp? How big is this partition?
I wondered about that, but I've set the mysql tmpdir to /proxydb/mysql/tmp,
and the error does actually say: Error writing file
'/proxydb/mysql/tmp/STTbtrac'
Also I couldn't see any mysql files in /tmp. It's tricky isn't it?!
JS.
I can no longer reproduce 3.23.58 running faster than 4.1.3-beta. If
anything, 4.1 is running faster, especially because of the query cache,
which helps in my situation. I'm not sure what (if anything) has changed in
my environment.
Here's another issue. We are running a Beowulf cluster under
Hi folks,
Installed Fedora Core 2 with mysql and then removed the mysql packages via the
gui widget. Then installed mysql 4 from scratch. Created a symlink, mysql.
added the user 'mysql'. chown'd etc the /usr/local/mysql stuff.
the command (as root)
/usr/local/mysql# bin/safe_mysqld
Victor,
Thanks for the prompt attention to my posting.
I am test running the queries from a different machine on a small network
under windows xp. I setup a few machines running small queries like select
one record, update the record, wait a few seconds, then select another
record at random etc.
Hi,
I am having trouble with a LEFT JOIN. I have three tables: Users, Projects
and Allocations. Users can be allocated to zero or more projects and a
project can be allocated to zero or more users. Table descriptions are
below.
How can i select a users details and all of the pojects they are
Hi,
I'm quite new to mysql and I need to join two tables:
Items
IDItems Name
1 Orange
2 Apple
3 Bread
4 Milk
...
Invoice
IDInvoice IDItems Qty
1001 1 10
1001 3 2
1002 2 5
...
I need to get all items and for items included in invoice
Matt,
I think I might have missed the start of this thread, as I can't seem to
find it.
Could you please post the following info (I don't mind if you just mail it
directly to me to save the list):
MySQL Version:
Server OS:
Server Hardware configuration:
- Memory
- CPU(s)
- Disks (RAIDs and
mysqld is trying to tell you that it doesn't have permission to create
/var/lib/mysql/mysql.sock to accept incoming connections.
You either need to have mysql create the sock file in another place
(such as /tmp/) or give it write permission to /var/lib/mysql/
-Eric
On Thu, 15 Jul 2004 17:05:13
I have been tryying to come up with a way to deal with this without adding
too much bloat in server side code, was hoping someone could help me with
some SQL to make this easier.
Mysql 4
Given a simple case a table 'data' with 'sort_order' and 'copy'
I currently list the images, ordered by
Thank you very much, this fixed it!
I made the buffer pool as well as the log file size 256M for now. The MySQL/InnoDB
manual suggests that the buffer pool size could be increased up to 80% of the physical
memory size, and this s4erver has 2G. It is not clear to me how much of a gain in
Hello,
I'm tempted to use the AES_ENCRYPT/AES_DECRYPT function in MySQL; as
documentation says the implementation is 128-bit AES. Thus the keys
are 16bytes in length. Now the 'key_str' in the manual can be
any length; how is the key generated from that?
also, for multi block encryptions what
I need some help with a DATE_ADD problem.
I can't seem to run a query with DATE_ADD(date, INTERVAL expr type)
where expr and type are dynamic.
It works fine with date as dynamic.
I am attempting to add 1 year to 2004-04-15 for a DueDate of 2005-04-15
and add 3 months to 2004-03-10 for a DueDate of
I haven't been able to reproduce the problem on another computer.
I executed this query on control center and no longer get the 'x'...
update table set blob_field=null
where blob_field is null or blob_field=''
but problems continues... when i build a query that includes a datetime
field, i get
Hello,
Are there any plans to implement foreign keys with deferred integrity
constraint checking in the InnoDB storage engine in a future release of
the MySQL Server?
Many thanks,
- glenn
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
With DATE_ADD(date,INTERVAL expr type), date and expr can be dynamic, but
type must be literally one of the types in the list. It cannot come from a
column or function. It would be nice if it could, but it cannot.
One solution would be to break nperiod into n, an int, and period, a
char(x).
69 matches
Mail list logo