Ivan,
- Original Message -
From: John B. Ivski [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Thursday, November 11, 2004 4:55 AM
Subject: Re: InnoDB data files keep growing with innodb_file_per_table
Heikki,
the output shows that there are no dangling transactions, and
Hello.
I'm not a VB expert, but some notes about methods of establishing such
connections can be found at:
http://dev.mysql.com/doc/mysql/en/Windows_and_SSH.html
http://dev.mysql.com/doc/mysql/en/mysql_ssl_set.html
William Blair Wagner [EMAIL PROTECTED] wrote:
I'm kind of new to
Call for Papers
First International Workshop on Managing Context Information in Mobile and
Pervasive Environments (MCMP2005)
www.site.uottawa.ca/~mkhedr/MCMP05
In conjunction with The 6th International Conference
Ivan,
there is something very strange in your tablespace monitor output:
04 5:35:51 INNODB TABLESPACE MONITOR OUTPUT
FILE SPACE INFO: id 0
size 120832, free limit 120064, free extents 3
not full
Ivan,
hmm... could it be that segments 0 1, 0 2, 0 3, etc. were printed close to
the end of the output? The print routine first prints inode pages that are
completely used, and after that other inode pages. Since the tablespace
validation said the tablespace is ok, I guess the segments really
Hi All,
I have the following situation: I want to check a row in a database (list of
jobs that need to be run). So, my script checks the status field, and if it
is not started, then it marks it as locked and running, and when it
finishes, it marks it as completed.
Now, here is what I am
I noticed a few mistakes in my query, which may be causing some
confusion and would probably cause it not to work. But I'll break
everything down.
The NULLs in the second owner column are the indicators that there is
no matching owner in the most recent three months.
Breaking down the query,
Hi Chetan,
Well, I would suggest to add separate index on
either on bsc_id or data_GenTime,
whichever returns smaller result.
Then check with explain that this index is used
May
chetan t wrote:
Hi mike,
as i have mentioned in my previous mail
the table structure which i am using is as fallows,
Afternoon All,
I have the following table structure:
CREATE TABLE properties (
id int(11) NOT NULL auto_increment,
propid varchar(14) NOT NULL default '0',
townid varchar(255) NOT NULL default '',
countyid mediumint(5) NOT NULL default '0',
address text NOT NULL,
price int(14) NOT
-Original Message-
From: David Blomstrom [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 10, 2004 4:08 PM
To: [EMAIL PROTECTED]
Subject: SQL Syntax Problem
$sql = 'SELECT
F.IDArea,
C.IDArea, C.Name, C.Pop, C.Nationality,
C.NationalityPlural, C.NationalityAdjective FROM
What does the explain plan look like?
John Smith wrote:
Afternoon All,
I have the following table structure:
CREATE TABLE properties (
id int(11) NOT NULL auto_increment,
propid varchar(14) NOT NULL default '0',
townid varchar(255) NOT NULL default '',
countyid mediumint(5) NOT NULL default
Hi,
I have a MySQL-4.0.18 installed on a FreeBSD system.
When I run the follow query:
DELETE
FROM table
WHERE client_id = 1
AND row_id IN (2,5,7)
only the first record is deleted.
Am I doing something wrong or is it a MySQL bug?
Thanks
Ronan
--
MySQL General Mailing List
For list
On Thu, 2004-11-11 at 13:58, Victor Pendleton wrote:
What does the explain plan look like?
id select_type table type possible_keys key key_len ref rows
Extra
1 SIMPLE properties ref old,price,countyid countyid 3 const 9233 Using where;
Using filesort
The filesort I know
[snip]
When I run the follow query:
DELETE
FROM table
WHERE client_id = 1
AND row_id IN (2,5,7)
only the first record is deleted.
Am I doing something wrong or is it a MySQL bug?
[/snip]
It is not a bug, just say it out loud
AND row_id is 2 OR 5 OR 7
Once the OR condition is satisfied
Joshua Beall wrote:
Hi All,
I have the following situation: I want to check a row in a database (list of
jobs that need to be run). So, my script checks the status field, and if it
is not started, then it marks it as locked and running, and when it
finishes, it marks it as completed.
Now, here
John,
Have you got a single multi-column index on countyid, price and old, or do
you have individual indexes on each of these fields? The former would be
much better.
Andy
-Original Message-
From: John Smith [mailto:[EMAIL PROTECTED]
Sent: 11 November 2004 14:15
To: Victor
Hi,
could you try adding a key with
ALTER TABLE properties ADD INDEX(countyid,old,price);
It could maybe help getting less rows at a time.
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
If your table is MyISAM you will have to lock the table so that no other
process can access that row (or any other) while you do your check and
update.
http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html
If your table is InnoDb you can still do the table lock or you can lock
just the one row.
On Thu, 2004-11-11 at 14:37, Andy Eastham wrote:
Have you got a single multi-column index on countyid, price and old, or do
you have individual indexes on each of these fields? The former would be
much better.
Its a single column on countyid, when I ran a select and just used
countyid = in
On Thu, 2004-11-11 at 14:36, Philippe Poelvoorde wrote:
Hi,
could you try adding a key with
ALTER TABLE properties ADD INDEX(countyid,old,price);
It could maybe help getting less rows at a time.
I dropped the old and price for the where clause and the number of rows
scanned were the same as
If you build the composit indexes as suggested, does your performance
improve?
John Smith wrote:
On Thu, 2004-11-11 at 13:58, Victor Pendleton wrote:
What does the explain plan look like?
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE
On Thu, 2004-11-11 at 14:59, Victor Pendleton wrote:
If you build the composit indexes as suggested, does your performance
improve?
Erm, do you think it would? Its just that with such a large table and it
being compressed it takes ages?
--
MySQL General Mailing List
For list archives:
I've read the article about 'prepared statement' found in MySQL 4.1, and am
not sure if I understood what 'prepared statement' does and how can it
benefit us. Can anyone elaborate on what 'prepared statement' could do with
examples where possible?
Thanks,
The Newbie Scott
--
MySQL General
From: John Smith [EMAIL PROTECTED]
On Thu, 2004-11-11 at 14:36, Philippe Poelvoorde wrote:
Hi,
could you try adding a key with
ALTER TABLE properties ADD INDEX(countyid,old,price);
It could maybe help getting less rows at a time.
I dropped the old and price for the where clause and
At 07:52 AM 11/11/2004, you wrote:
Afternoon All,
The table is read only for most of the day and will get updated (once I
get the queries down to an acceptable level) nightly...if that helps.
Any suggestions or further tips would be very helpful as its taken me
months to get my code to input the
On Thu, 2004-11-11 at 15:45, Jigal van Hemert wrote:
`price` is still in the ORDER BY, so removing it only from the WHERE clause
will not help really.
No the query I ran didn't have an order by clause (sorry if the one I
pasted did..)
- create an INDEX on the columns in the WHERE clause _and_
On Thu, 2004-11-11 at 15:51, mos wrote:
John,
Create a second table (MyISAM) but this time don't use compression
on the table.
create table newtable select * from oldtable;
Right will run that just now, good idea...just have to avoid the wife as
no doubt it will bog the site
Hi
Can someone offer any advice on a strange problem I have at present...
If I run a certain query (see below) on my local development PC using
mysqlcc it returns in 3.7s.
If I run the exact same query on my live webserver (again using mysqlcc) I
have yet to get a result !!
Both databases have
Jay Blanchard wrote:
[snip]
When I run the follow query:
DELETE
FROM table
WHERE client_id = 1
AND row_id IN (2,5,7)
only the first record is deleted.
Am I doing something wrong or is it a MySQL bug?
[/snip]
It is not a bug, just say it out loud
AND row_id is 2 OR 5 OR 7
Once the OR condition
What does EXPLAIN show for the query on both systems? (I am wondering if
you may have an index on your development system that you do not have on
your production server.)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Graham Cossey [EMAIL PROTECTED] wrote on 11/11/2004
Jay,
It is not a bug, just say it out loud
AND row_id is 2 OR 5 OR 7
Once the OR condition is satisfied once, the query will halt.
The problem is that if I use OR in the where clause, MySQL wont
use the indexes in the row_id column.
One important thing that I forgot to say is I run a
It is a property of Mysql that such a query will benefit greatly from a
composite index. So I would not consider anything else without having tried
this.
Am Thursday 11 November 2004 16:29 schrieb John Smith:
On Thu, 2004-11-11 at 14:59, Victor Pendleton wrote:
If you build the composit
Thanks Shaun
EXPLAIN shows the same 'possible keys' for each table but 'key' and
'key-len' columns are different, as are the 'rows' as well of course.
I guess this points to a probable difference in key definitions?
Can 2 installations with the same table definitions produce different
results
Michael,
What are you talikng about? Queries don't halt on the first row matched.
For example:
It´s my thought, too. But it isn´t happen in my MySQL Server.
Now, doing the same tests you did I got the same results of you.
Well, I´ll inspect my code again looking for some error that I
didn´t
John mailto:[EMAIL PROTECTED]
on Wednesday, November 10, 2004 10:04 PM said:
I want to make a small simple database that searches
by state or zip code for jobs. I would enter just a
job description, job position and job id #. so 3
fields display. I want to enter in the information by
an
At 8:18 -0600 11/11/04, Jay Blanchard wrote:
[snip]
When I run the follow query:
DELETE
FROM table
WHERE client_id = 1
AND row_id IN (2,5,7)
only the first record is deleted.
Am I doing something wrong or is it a MySQL bug?
[/snip]
It is not a bug, just say it out loud
AND row_id is 2 OR 5 OR
Check the results of SHOW CREATE TABLE for the three tables you use and
compare between production and development. You should be able to spot any
differences in your key definitions.
If they are the same on both machines then you should probably run ANALYZE
TABLE against the three tables on
Right thanks for all the tips the 3 column index has done the job,
queries coming back in 0.7 secconds now which is just the job before
they get cached.
Don't know how I missed that one as it was abovious...i even tried
countyid and old...forgot about price..
John
--
MySQL General Mailing
Thanks Shaun
EXPLAIN shows the same 'possible keys' for each table but 'key' and
'key-len' columns are different, as are the 'rows' as well of course.
I guess this points to a probable difference in key definitions?
Can 2 installations with the same table definitions produce different
Fredrik Carlsson wrote:
Hi,
I have problem with my mysqld, when its receiving alot of connections
and at the same time doing insert/delete/update/select on the same table
mysqld segfaults and restarts
041108 0:59:08 Warning: Got signal 14 from thread 162602
Segmentation fault
041708 13:17:54
Hi all,
I have the following select:
select cons_nome as Consultorio,
dent_nome as Dentista,
pac_nome as Paciente,
pac_convenio as Tipo,
Pac_matricula as Matricula,
concat(consulta_dia,
/,
consulta_mes,
/,
consulta_ano) as Data,
concat(Consulta_hora,
:,
Heikki,
Heikki Tuuri wrote:
InnoDB is clearly leaking 'segments' in the system tablespace. They are
probably undo logs. For some reason, a trx commit or purge fails to free
them.
SEGMENT id 0 75994 space 0; page 82136; res 2720 used 2680; full ext 41
fragm pages 32; free extents 0; not full
I tested the memory and it seems ok.
I doubt this is an hardware issue, since version 4.1.3 works perfectly.
Ugo:
Do the production and the test server run on the same hardware?
Are you using the same mysqld binary on both?
What I am suspecting is that the binary on the production system does has
Response at end
Graham Cossey [EMAIL PROTECTED] wrote on 11/11/2004 12:19:17 PM:
Thanks Shaun
EXPLAIN shows the same 'possible keys' for each table but 'key' and
'key-len' columns are different, as are the 'rows' as well of course.
I guess this points to a probable difference in
Ronan Lucio wrote:
snip
The problem is that if I use OR in the where clause, MySQL won't
use the indexes in the row_id column.
Yes, it will, as long as the OR conditions are on the *same* column.
WHERE row_id IN (2,5,7)
and
WHERE (row_id = 2 OR row_id = 5 OR row_id = 7)
are equivalent. I
Heikki Tuuri wrote:
Ivan,
hmm... could it be that segments 0 1, 0 2, 0 3, etc. were printed close
to the end of the output? The print routine first prints inode pages
that are completely used, and after that other inode pages. Since the
tablespace validation said the tablespace is ok, I guess
Hello.
As said at documentation:
Prepared execution is an efficient way to execute a statement more than once.
Good examples you can find in documentation to MySQL, and in
tests/client_test.c.
Scott Hamm [EMAIL PROTECTED] wrote:
I've read the article about 'prepared statement' found in
Scott,
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
regards,
Mark.
On Thu, 2004-11-11 at 15:38, Scott Hamm wrote:
I've read the article about 'prepared statement' found in MySQL 4.1, and am
not sure if I understood what 'prepared statement' does and how can it
Heikki,
Heikki Tuuri wrote:
hmm... could it be that segments 0 1, 0 2, 0 3, etc. were printed close
to the end of the output? The print routine first prints inode pages
that are completely used, and after that other inode pages. Since the
tablespace validation said the tablespace is ok, I guess
*THAT* one was what I already ready and could not understand it since
English isn't my first language.
-Original Message-
From: Mark Maunder [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 11, 2004 12:45 PM
To: Scott Hamm
Cc: 'Mysql ' (E-mail)
Subject: Re: Prepared statement for MySQL
Does anyone know if there is a tool or a good way to recover a single table
from the binlogs ?
I tried
for n in dev-bin.*; do mysqlbinlog $n |grep table_name /tmp/file.sql; done
but it just gives me the first line of the insert/update statement. I guess
you could dump all the bin files out
Hi,
I think I found out what was wrong.
ColfFusion has a tag CFQUERYPARAM that prevents
SQL Injection.
Probably CFQUERYPARAM was removing anything after comma.
Without using CFQUERYPARAM the code works perfectly.
So, I had to create a UDF to remove everything except digits and commas.
Thanks
Sasha Pachev wrote:
I tested the memory and it seems ok.
I doubt this is an hardware issue, since version 4.1.3 works perfectly.
Ugo:
Do the production and the test server run on the same hardware?
One is a single Athlon XP, the other is a dual Athlon MP.
Are you using the same mysqld binary on
Scott,
I've read the article about 'prepared statement' found in MySQL 4.1, and am
not sure if I understood what 'prepared statement' does and how can it
benefit us. Can anyone elaborate on what 'prepared statement' could do with
examples where possible?
In the simplest case, consider this:
[big snip]
These are two different plans. Your development machine is using
the index
yr_mn_pc on the r table and is joining that table last. On your
production
server, the r table is joined second and is joined by the index PRIMARY.
Let me know how the ANALYZE TABLE I suggested in a
Re speed benefits
There aren't any - at least not through the C API. It's at least twice as
slow as writing embedded statements and parsing every time. I'm waiting for
them to announce they've fixed it before we consider this route again.
Kevin Cowley
RD
Tel: 0118 902 9099 (direct line)
Email:
How do the OS statistics look on both boxes. Do top, sar, vmstat or
iostat show any CPU, memory or I/O performance issues? Does anything
odd appear in the /var/log/messages file?
-Jamie
On Thu, 11 Nov 2004 18:42:48 -, Graham Cossey
[EMAIL PROTECTED] wrote:
[big snip]
These are
Paul:
Thank you for your response. Another question:
Would it cause a problem if I configure --with-raid and then never use it in
any programming? Will it add any overhead?
It would help if this feature is available for future use.
Thanks 2nd time.
Kirti
-Original Message-
From: Paul
Sasha,
- Original Message -
From: Sasha Pachev [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Thursday, November 11, 2004 8:05 PM
Subject: Re: InnoDB data files keep growing with innodb_file_per_table
Heikki Tuuri wrote:
Ivan,
hmm... could it be that segments 0 1, 0 2, 0 3,
You want the rows containing the maximum post_d for each movies_id group.
As you've seen, this is tricky. Fortunately, the manual suggests three
solutions to this problem
http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html.
Michael
Seth Leonard wrote:
I have three tables:
All:
I am attempting to move a set of db's from Linux (Mysql 4.0.20) to Windows
(4.1.6). I have everything running, and I can query tables that are all
lower case.
However I cannot issue a query on a table that contains uppercase letters.
This is the my.ini file:
[client]
port=3306
John,
please zip ibdata1, which is 'only' 100 MB, and upload it when you have shut
down mysqld.
I have been simulating your workload, but I only get 25 segments. No leak
seen.
Regards,
Heikki
- Original Message -
From: John B. Ivski [EMAIL PROTECTED]
Newsgroups:
Hello,
I would like to migrate my MySQL servers from 4.0 to 4.1.
As I use PHP as well as Java with these servers I wonder what PHP 4
version would
be compatible with MySQL 4.1.
Has anyone used MySQL 4.1 with PHP yet?
I appreciate your thoughts.
Thanks.
--
MySQL General Mailing List
For list
[snip]
I would like to migrate my MySQL servers from 4.0 to 4.1.
As I use PHP as well as Java with these servers I wonder what PHP 4
version would
be compatible with MySQL 4.1.
Has anyone used MySQL 4.1 with PHP yet?
[/snip]
PHP 4 is compatible with MySQL 4.1. My caution to you would be using
Jay Blanchard wrote:
[snip]
I would like to migrate my MySQL servers from 4.0 to 4.1.
As I use PHP as well as Java with these servers I wonder what PHP 4
version would
be compatible with MySQL 4.1.
Has anyone used MySQL 4.1 with PHP yet?
[/snip]
PHP 4 is compatible with MySQL 4.1. My caution to
Jay Blanchard wrote:
[snip]
I would like to migrate my MySQL servers from 4.0 to 4.1.
As I use PHP as well as Java with these servers I wonder what PHP 4
version would
be compatible with MySQL 4.1.
Has anyone used MySQL 4.1 with PHP yet?
[/snip]
PHP 4 is compatible with MySQL 4.1. My caution to
SELECT
_objectives.id,
_objectives.name,
COUNT(go._iso._objective_id)
FROM go._objectives
INNER JOIN go._subjectHeadings
ON go._subjectHeadings.id = go._objectives.subjectHeadings_id
INNER JOIN go._subjects
ON go._subjects.id = go._objectives.subjects_id
LEFT JOIN go._iso
Jay Blanchard wrote:
[snip]
I would like to migrate my MySQL servers from 4.0 to 4.1.
As I use PHP as well as Java with these servers I wonder what PHP 4
version would
be compatible with MySQL 4.1.
Has anyone used MySQL 4.1 with PHP yet?
[/snip]
fwiw, i build-from-source use a collection of:
Everything seems fine except for this one query.
I'm not sure quite what the results of top will say or what they mean if
it's a Virtual Private Server environment?
Nothing untoward in /var/log/messages or var/log/httpd/error_log or virtual
host httpd logs.
Have just run 'top' on the live
[snip]
of course, YMMV ...
[snip]
Sorry, YMMV? What does that mean?
Is there a reference somewhere for all these acronyms?
Thanks
Graham
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Looking for some informed opinions...
Which is faster for searching by date ranges?
...Times stored in date() columns - temporal values.
...Or dates stored as unix timestamps in an int(10) unsigned column - int
values.
Most of the queries will be searching for rows between certain data ranges
Graham Cossey wrote:
Sorry, YMMV? What does that mean?
Is there a reference somewhere for all these acronyms?
Yes it's called Google.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sorry, YMMV? What does that mean?
YMMV = Your Mileage May Vary
Is there a reference somewhere for all these acronyms?
lots.
google (internet acronyms)
richard
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I noticed something interesting with our slow-query log and am looking for
an answer.
Our slow-query log is set for to record anything over 7 seconds.
In monitoring the server I ran the show full processlist I occasionally
see entries like
| ID | USER | HOST | DATABASE | Query |
[snip]
Sorry, YMMV? What does that mean?
Is there a reference somewhere for all these acronyms?
Yes it's called Google.
Yep, as soon as I posted I knew I should not have :)
It's late and I'm getting lazy...
--
MySQL General Mailing List
For list archives:
I have two tables I want to get out the rows that are different between
them. The results I am getting is almost 50,000 rows, but the two tables,
combined, contain only about 600 rows total. Here is the select statement
-
SELECT dealers.account_no, dealers.DealerName,
[snip]
Have just run 'top' on the live server...
Before running the query I get:
13:56:09 up 45 days, 11:47, 1 user, load average: 0.00, 0.28, 0.44
24 processes: 23 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0%
idle
This will really speed things up:
ALTER TABLE _ISO ADD KEY(_objective_id)
If you do an EXPLAIN on your current query, you would see that no index is
used for the _iso table so the engine has to do a table scan. And, for
future reference, the output of SHOW CREATE TABLES is more informative
What happened is called a Cartesian product. You basically asked for a
combination of every value from one table matched to every value from the
other table. In your case you did have an except-type condition so you
eliminated a few of the matches.
Let me explain it this way: Imagine you have
Hello,
Since I had a problem with except operator and subqueries, I investigated a
topic on mysql version capability. I tried a few examples which were offered
on this mailing list, all of them gave me a syntax error, so I've read a
manual and tried some examples from it. However, things that
Hi All,
I am a bit confused by the MySQL documentation on this subject. From
http://dev.mysql.com/doc/mysql/en/Storage_requirements.html
The maximum size of a row in a MyISAM table is 65,534 bytes. Each BLOB and
TEXT column accounts for only five to nine bytes toward this size.
So, the
[EMAIL PROTECTED] wrote on 11/11/2004 02:05:17 PM:
snip
What you want to do is to write a query that lists every record from
the dealers table OPTIONALLY matched up to rows on the
blackgate_users table wherever the names match. This is what the
LEFT JOIN operator was invented to do (you
Think I found it. I made the changes with explanations of what I did.
If you have any further questions feel free to ask. Oh and this should
be on the list for others to see and maybe learn from
Respectfully,
Ligaya Turmelle
head[DATABASE CONNECTION]/head
body
div class=formdiv
Hello,
What are the plans regarding improving scalability of MySQL? We are
currently trying to decide what technology/product to use for a large
project that will generate ~600TB/year starting in 2012. Any pointers to
related articles or hints how safe is to assume that MySQL will be able
to
--- Ligaya Turmelle [EMAIL PROTECTED] wrote:
Think I found it. I made the changes with
explanations of what I did.
If you have any further questions feel free to ask.
Oh and this should
be on the list for others to see and maybe learn
from
Wow, thanks so much for going to all that
Joshua Beall wrote:
Hi All,
I am a bit confused by the MySQL documentation on this subject. From
http://dev.mysql.com/doc/mysql/en/Storage_requirements.html
The maximum size of a row in a MyISAM table is 65,534 bytes. Each BLOB and
TEXT column accounts for only five to nine bytes toward this
Hello,
Since I had a problem with except operator and subqueries, I investigated a
topic on mysql version capability. I tried a few examples which were offered
on this mailing list, all of them gave me a syntax error, so I've read a
manual and tried some examples from it. However, things that
87 matches
Mail list logo
