Michael Satterwhite wrote:
-
As it is, all I can suggest is to JOIN on all 90 tables - and hope MySQL
can handle the query - and that you can type all of them without error.
Note that if you use a UNION query as you suggest above, you will get
the last login FOR EACH DAY - not the overall last
There is a thread over at /. about WikiMedia being out due to a power outage
http://slashdot.org/articles/05/02/22/0151213.shtml?tid=95
MySql is getting bashed pretty hard in some cases as apparently, in power
failures, you get database corruption. (this is all from the posts, not my
opinion at
SH MySql is getting bashed pretty hard in some cases as apparently, in power
SH failures, you get database corruption.
One can do a simple test
start lengthy transaction and plug off the computer while the
transaction is still in progress.
Will your DBMS recover the errors after switching your
This is not surprising behaviour. If you subtract 12 from 10, you get
-2 every time.
If you want your result to be positive, make it the first parameter
and subtract the smaller (earlier) time from it. If your result is
negative, then you know you have crossed into another day (13:00:00 -
Hi
OS and MySQL version:
Darwin, Mac OS X 10.2.8, MySQL 4.0.21-standard
I have 2 mysql servers happily running on the same box. The default
server being on 3306 and socket mysql.sock. With a second server
running on 3307 with socket mysql1.sock.
I can start and stop these fine from the command
I've just been wondering if the length parameter of a VARCHAR column has
any effect on storage efficiency or space requirements. Afaik, VARCHAR
columns only store the amount of data actually written into them and
require no significantly more memory. So to be especially flexible with
a
Hi again,
I worked on the problem and found a way to make the LOAD INDEX INTO
CACHE work on my main tables now.
I actually found out that some of my indexes using varchar could be
optimized because they aren't used for search but ordering
(alphabetical and such). So having a index length of 255
Mike Rains ha scritto:
I've just been wondering if the length parameter of a VARCHAR column has
any effect on storage efficiency or space requirements. Afaik, VARCHAR
columns only store the amount of data actually written into them and
require no significantly more memory. So to be especially
Hello,
I am using mysql Ver 14.6 Distrib 4.1.5-gamma, for sun-solaris2.8 (sparc)
I am having a problem with using the following query from a perl program:
LOAD DATA LOCAL INFILE ? REPLACE INTO TABLE $table
The error I get is:
DBD::mysql::st execute failed: The used command is not allowed with
I'm trying to install MySQL-server-4.1.10-0.i386.rpm on Fedora Core 2 but I
keep getting a Package Not Found dialog box saying Unlocatable Package
libmysqlclient.so.10 Required By ('perl-DBD-MySQL', '2.9003', '4').
I've installed the client, devel and shared packages but still get the same
Hi
I run a Postfix MTA attached to a mysql DB with various domains on it.
A domain consists of email addresses.
When I want to delete the domain the referenced email addresses should
be deleted, too. But that doesn't work and I don't know why.
here are the two tables domains and users:
CREATE
Could this possibly be a problem with a bad fsync implementation in
linux ( 2.6.5)?
See: http://www.ussg.iu.edu/hypermail/linux/kernel/0403.2/0527.html
Scott Haneda schrieb:
There is a thread over at /. about WikiMedia being out due to a power outage
I'm just curious to know if the length of the indexes on a varchar
column work in the same way or if they have a fixed lenght.
anybody knows ?
I don't see how they could be fixed length, since VARCHAR itself is
not fixed-length. Ergo, it makes sense that the prefix limitation is
the upper
Hello all,
Have a little problem with to make a sql-query as I want to have it...
The problem is I need to check in 3 tables and count out and get it presentated
per n_id like
n_id counted
01 5
02 10
03 2
My tables look as follows...
In sub:
id
n_id
In us:
id
email
In
Foreign keys are only supported within InnoDB tables (on both sides)..
Mark
Mark Leith
Cool-Tools
http://www.cool-tools.co.uk
-Original Message-
From: Philipp Snizek [mailto:[EMAIL PROTECTED]
Sent: 22 February 2005 10:30
To: Mysql List (E-mail)
Subject: referencial integrity problem
[snip]
Can I select from one db and insert into another?
also...Can I join to a remote db?
[/snip]
Yes, if you can share the connection. Consider...
INSERT INTO db1.tableI (`stuff`)
SELECT `stuff`
FROM db2.tableII
Doing a join to a remote database is not possible as you would have to
have two
Foreign keys are only supported within InnoDB tables (on both
sides)..
so using a table 'users' like
create table users (
email varchar (80) primary key unique not null,
belongs_to integer not null references domains on delete cascade
);
without foreign keys could help?
I couldn't
You need to make *both* of your table definitions include
Type=InnoDB; *then*, the cascading delete should work fine.
This is what I have done upon Keith's suggestion. I have changed all
my tables to Type=innodb.
Still nothing. Maybe mysqlcc or mysql administrator deliver wrong
information?
Hi,
I am trying to compile the official source rpm on a Cobalt Raq4i using the
command:
rpm --rebuild --clean MySQL-4.1.10-0.src.rpm
But I get the follwing error:
examples/ha_tina.cc: In method `int ha_tina::rnd_init(bool = 1)':
examples/ha_tina.cc:612: `MADV_SEQUENTIAL' undeclared (first use
Hi,
MySql 4.0.14
How can I do this?
Update Table1, Table2 set Table1.Field1 =
Table2.Field1 + Table1.Field1 where Table1.KeyField =
Table2.KeyField
Tried the above with no success.
Field1, in both tables is of type Text.
regards
Good evening folks, I'm seeing some odd behavior in MySQL 4.0.21
running on Mac OS X 10.3.7
I'm trying to compare two identical tables and find the rows
that are new/modified. I can't use a timestamp column because
the new table is constantly regenerated. So I'm using a large
WHERE clause and
Rene Churchill [EMAIL PROTECTED] wrote on 02/22/2005 09:21:29 AM:
Good evening folks, I'm seeing some odd behavior in MySQL 4.0.21
running on Mac OS X 10.3.7
I'm trying to compare two identical tables and find the rows
that are new/modified. I can't use a timestamp column because
the new
Hi,
I was wondering if there is a way to specify how verbose the logfile
should be in my.cnf. I've tried searching the manual and google, but
have not yet found anything (besides the -v commandline option but thats
not it). Any helpd and.or pointers would be greatly appreciated.
Johan
--
MySQL
On Tuesday 22 February 2005 09:08 am, Johan Jonkers wrote:
I was wondering if there is a way to specify how verbose the logfile
should be in my.cnf. I've tried searching the manual and google, but
have not yet found anything (besides the -v commandline option but thats
not it). Any helpd
Begumisa Gerald M [EMAIL PROTECTED] wrote on 02/22/2005 02:03:43 AM:
Hi,
I'm writing an application that uses InnoDB tables to provide
transactional integrity. The front-end is a web-based interface.
I'd like to know - is there a way one can issue a query to test whether
a
particular
I am considering the use of a new OS X machine with the Free BSD back end.
I would like answers to the following if anyone has the experience.
What switches should be set for compiling? I only need the Innodb storage
engine.
Which is the best compiler to use for MySQL on an Apple
One last question, maybe :)
Is there any way to empty the key buffer once the server is started ?
That would be handy :)
Thanks
On Tue, 22 Feb 2005 11:13:29 +0100, HMax [EMAIL PROTECTED] wrote:
Hi again,
I worked on the problem and found a way to make the LOAD INDEX INTO
CACHE work on my
Jeff Smelser wrote:
On Tuesday 22 February 2005 09:08 am, Johan Jonkers wrote:
I was wondering if there is a way to specify how verbose the logfile
should be in my.cnf. I've tried searching the manual and google, but
have not yet found anything (besides the -v commandline option but thats
not
You could just use the OS X installer from the mysql web site. It's
compiled with InnoDB support and installation can't get any easier.
If you do want to squeak out as much performance as you can, you should
search on mysql G5 (or G4) compiler flags. This has been discussed
before, but I'm not
Hi
I must be blind. Please help a DB-Newbie. What's wrong here:
create table users (
email varchar (80) unique not null,
ownerdomain int not null,
foreign key (ownerdomain) references domains on delete cascade
) type=innodb;
MySQL sais: ERROR 1005: Can't create table
Your subject line is misleading; there is nothing in the body of your post
suggesting that you are getting some kind of row lock, nor is there any
query.
Have you looked in the MySQL manual? If you have a look at this URL -
http://dev.mysql.com/doc/mysql/en/innodb-error-codes.html - you'll see
I am running MySQL and Perl on Windows 2003. I am trying to use the
mysqlhotocpy script for backup. Mysqlhotcopy will execute but returns an
error.
Command:
e:\mysql\scripts\mysqlhotcopy.pl -u root -p password db_name /backup_test
Error:
The system cannot find the path specified.
Executing
what is the limit to the number of records that can be inserted via an extended
insert statement. I know mysqldump creates a new insert statement every X
records, but I forgot what that number is, and I'm trying to duplicate the
action.
thanks,
-L
Crouch, Luke H. [EMAIL PROTECTED] wrote on 02/22/2005 12:18:51
PM:
what is the limit to the number of records that can be inserted via
an extended insert statement. I know mysqldump creates a new insert
statement every X records, but I forgot what that number is, and I'm
trying to
Martijn Tonies [EMAIL PROTECTED] wrote on 02/21/2005 10:20:28 AM:
Hi,
why not try:
SELECT COUNT(s.Id)+COUNT(se.Id)
FROM subs s
INNER JOIN subs_erased se ON s.Id=se.Id
WHERE s.Id=1;
/Johan
This won't return the same result if there's no entries
in subs_erased for the ID =
thanks much. exactly what I needed.
-L
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 22, 2005 11:22 AM
To: Crouch, Luke H.
Cc: mysql@lists.mysql.com
Subject: Re: extended insert limit?
Crouch, Luke H. [EMAIL PROTECTED] wrote on
Jim Grill [EMAIL PROTECTED] wrote on 02/18/2005 03:17:39 PM:
Hi,
I need some SQL guru help on this one.
I'm trying to re factor an existing application where a number of clicks
grouped by keyword for two different time periods are needed. For
example, a user picks a date range and
Gerald Taylor [EMAIL PROTECTED] wrote on 02/18/2005 10:18:29 AM:
I have a database thats full of ingredients that are placed in various
categories. and then there are mixtures that are allowed to
be labelled with a certain grade based on the quality and
composition of the ingredients it is
I have a database thats full of ingredients that are placed in various
categories. and then there are mixtures that are allowed to
be labelled with a certain grade based on the quality and
composition of the ingredients it is comprised from.
But the formulas are not always the same, as
I have upgraded from mysql 3.23 to 4.1.10 everything seems to be working.
I'm trying to run the mysql_fix_privilege_tables script to update the
privileges and I'm getting the following error:
/usr/bin/mysql_fix_privilege_tables: line 185: /usr/bin/mysql: No such
file or directory
/usr/bin/mysql
I know this is possible but I'm not real sure of the command to use. I
have 2 tables that are pretty much identical except for one column. What I
want to do is moved data from one table column to the other table column
based on a matching id number that is also a column in both tables called
id.
James,
unfortunately, you cannot move InnoDB tables in that way, like you would be
able to move MyISAM tables just by copying the .MYI, .MYD, and .frm files
over to the other database installation.
In the future, we may add a feature that allows one to copy 'clean' .ibd
files across
Hi there!
I have a table, defined as follows:
CREATE TABLE `WebSiteDomainNames` (
`ID` int(10) unsigned NOT NULL auto_increment,
`WebSite` int(10) unsigned NOT NULL default '0',
`DomainName` int(10) unsigned NOT NULL default '0',
`Alias` char(16) default NULL,
PRIMARY KEY (`ID`),
At 15:00 -0500 2/22/05, Gustafson, Tim wrote:
Hi there!
I have a table, defined as follows:
CREATE TABLE `WebSiteDomainNames` (
`ID` int(10) unsigned NOT NULL auto_increment,
`WebSite` int(10) unsigned NOT NULL default '0',
`DomainName` int(10) unsigned NOT NULL default '0',
`Alias`
I have a small question. Whenever I try to dump a sizeable Innodb
table, lets say 33,000,000 records I find that mysqldump cannot handle
that kind of load and usually freezes. I am not sure if it is my
system, as its only a pentium 4 1.7ghz celeron running on windows
server, but with my mysql
phpninja [EMAIL PROTECTED] wrote on 02/22/2005 03:37:37 PM:
I have a small question. Whenever I try to dump a sizeable Innodb
table, lets say 33,000,000 records I find that mysqldump cannot handle
that kind of load and usually freezes. I am not sure if it is my
system, as its only a pentium 4
Hello,
I am having trouble getting the auto_increment function to begin at a set
value. When I search the docs, I find information like:
Posted by Michael Craig on September 6 2002 9:51pm [
http://dev.mysql.com/doc/mysql/comment.php?id=1058action=delete Delete] [
Rene Churchill [EMAIL PROTECTED] wrote on 02/22/2005 04:23:47 PM:
[EMAIL PROTECTED] wrote:
Rene Churchill [EMAIL PROTECTED] wrote on 02/22/2005 03:39:05
PM:
Hi Shawn,
This is what I wound up going with:
SELECT b.id,
if(a.a = b.a, NULL, b.a),
if(a.b
I am using the memory
table in 4.1 to auto increment is there a limit to how big that number can
get?
ThanksDonny LairsonPresident29
GunMuse LaneP.O. box 166Lakewood NM 88254http://www.gunmuse.com469 228 2183
My Memory table hit
16Mb and locked up. Is there something in my.cnf that I don't have
correct. I thought I set it to 128MB memory tables.
max_connections = 3500max_user_connections =
1500key_buffer = 750Mmyisam_sort_buffer_size = 130Mjoin_buffer_size
= 128Mread_buffer_size =
On Tuesday, February 22, 2005 16:12, [EMAIL PROTECTED] wrote:
I am using the memory table in 4.1 to auto increment is there a limit
to how big that number can get?
It is only limited by the size of your int. I would suggest declaring the
column unsigned. This will give you twice the
Peter,
a buggy fsync() in Linux is one of the possible reasons here. If an InnoDB
tablespace gets corrupt in a power outage, it is most probably caused by a
bad fsync() implementation or configuration in the operating system or
hardware. An fsync() call should write the data physically to disk
[EMAIL PROTECTED] wrote:
This is what I wound up going with:
SELECT b.id,
if(a.a = b.a, NULL, b.a),
if(a.b = b.b, NULL, b.b),
if(a.c = b.c, NULL, b.c),
(NOT (a.a = b.a) AND
(a.b = b.b) AND
(a.c =
We are getting lots
of Overhead in our MEMORY table when we delete rows that are to old. So
How do we delete from the table an not consumer MEMORY that we want
later?
ThanksDonny LairsonPresident29
GunMuse LaneP.O. box 166Lakewood NM 88254http://www.gunmuse.com469 228 2183
Scott Haneda wrote:
There is a thread over at /. about WikiMedia being out due to a power outage
http://slashdot.org/articles/05/02/22/0151213.shtml?tid=95
MySql is getting bashed pretty hard in some cases as apparently, in power
failures, you get database corruption. (this is all from the posts,
[EMAIL PROTECTED] wrote:
We are getting lots of Overhead in our MEMORY table when we delete
rows that are to old. So How do we delete from the table an not
consumer MEMORY that we want later?
Your last 3 questions lack any details of what you're trying to do, or
what problem you're
Many data centers do not allow customers to install their own UPS inside
the rack. I am not sure if this is the case with Wikipedia, but it is
definitely the case at the data center we are hosted in. I would love
to shove one in after reading the horror stories at Livejournal and now
I was under the impression that fsync() was only buggy in Linux in the
2.4 kernels. Is it still problematic in 2.6 series?
Greg
--
[EMAIL PROTECTED]
Meetup.com
Heikki Tuuri wrote:
Peter,
a buggy fsync() in Linux is one of the possible reasons here. If an
InnoDB tablespace gets corrupt in a
Greg Whalin wrote:
Many data centers do not allow customers to install their own UPS
inside the rack. I am not sure if this is the case with Wikipedia,
but it is definitely the case at the data center we are hosted in. I
would love to shove one in after reading the horror stories at
On Wed, 23 Feb 2005 00:22:55 +0200, Heikki Tuuri wrote:
a buggy fsync() in Linux is one of the possible reasons here. If an InnoDB
tablespace gets corrupt in a power outage, it is most probably caused by a
bad fsync() implementation or configuration in the operating system or
hardware. An
Don't want this to roll to far down the list.
My Memory table hit 16Mb and locked up. Is there something in my.cnf that
I don't have correct. I thought I set it to 128MB memory tables.
max_connections = 3500
max_user_connections = 1500
key_buffer = 750M
myisam_sort_buffer_size =
Greg,
looks like Jens Axboe and others are still working to get fsync() safe in
Linux-2.6.xx:
Hi!
If I understood correctly, Wikipedia did have an UPS at their data center.
But the problem cut also that off.
Regards,
Heikki
- Original Message -
From: Daniel Kasak [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, February 23, 2005 12:56 AM
Subject: Re:
Daniel Kasak wrote:
Greg Whalin wrote:
Many data centers do not allow customers to install their own UPS
inside the rack. I am not sure if this is the case with Wikipedia,
but it is definitely the case at the data center we are hosted in. I
would love to shove one in after reading the horror
Jochem,
- Original Message -
From: Jochem van Dieten [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, February 23, 2005 1:01 AM
Subject: Re: wikipedia down, slashdot covering, mysql mentioned
On Wed, 23 Feb 2005 00:22:55 +0200, Heikki Tuuri wrote:
a buggy fsync()
On Feb 22, 2005, at 10:44 AM, Boyd E. Hemphill wrote:
I am considering the use of a new OS X machine with the Free BSD back
end.
I would like answers to the following if anyone has the experience.
We use MySQL (exclusively InnoDB) on G5 XServes.
What switches should be set for compiling? I only
I read How My SQL Optimizes Order By
(http://dev.mysql.com/doc/mysql/en/order-by-optimization.html),
and I'm aware of its severe limitation due to the
one-index-per-table rule.
However, even when I follow all the roles, I'm still
getting filesort instead of using the index order.
So I created an
Actually with the query below it does avoid filesort,
but once I use anything other than the equal operator
(e.g. ColC 5), it reverts back to filesort.
Any thoughts?
--- Homam S.A. [EMAIL PROTECTED] wrote:
I read How My SQL Optimizes Order By
- Original Message -
From: Homam S.A. [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: February 22, 2005 8:12 PM
Subject: Avoiding filesort #2
Actually with the query below it does avoid filesort,
but once I use anything other than the equal operator
(e.g. ColC 5), it reverts back
Hi
First, I am pretty sure that what the manual says is that MySQL only
USES one index per request, not one index per table. I would try adding an
index that starts with ColC (and maybe only ColC). Your index starts with
ColA but you do not use it in your WHERE portion of the statement.
Ok folks your little words of wisdom crunched our problem out and and we
built a metacrawler that is faster than momma and dogpile because we now
NEVER touch a harddrive while searching. We will be applying this search to
our Blogging software at firebasesoftware.com by Monday.
Thanks
Even if I used ColA in the query, it still uses
filesort if any keypart uses something other than an
equal operator, like a range, IN operator, IS NOT
NULL, IfNull(), etc.
Rearranging the composite index to make the sort
column the first one won't help because:
1) It's not part of the WHERE
On Tue, Feb 22, 2005 at 03:46:34PM -0600, Scott Purcell [EMAIL PROTECTED]
wrote:
Hello,
ALTER TABLE tbl_name AUTO_INCREMENT = 1000
will start your records at 1000
But it does not work for myself. How can I get the auto_increment to
begin at a set starting point? I know I could insert a
If I needed a brute force DB on an x86 platform would
using Version 3 vs 4 buy me any performance today ?
90% inserts, 5% simple selects, 5% other.
Sorry if a lame question
-pete
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
This may be more of a PHP question than a MySQL question but here goes:
I have a small database with two tables in it. It's a database of
Botanical Gardens in the US.
Table 1 contains the botanical gardens and has 8 fields:
(I'm abbreviating for brevity and clarity): id, name, address, town,
You need to do a join on the tables,
Simplest way is
Select * from gardens a, state b where a.state_id = b.id
Assuming id in the state table is actually what your planning on joining on.
Try to do it on the mysql command line before doing in code to make sure you
actually have the data you
On Tuesday, February 22, 2005 22:08, jsf wrote:
This may be more of a PHP question than a MySQL question but here
goes:
I have a small database with two tables in it. It's a database of
Botanical Gardens in the US.
Table 1 contains the botanical gardens and has 8 fields:
(I'm
Hi Shawn,
Thanks for taking time to respond to this.
[...]Usually the database sets and releases locks like that in
response to a series of statements on the order of sub-seconds, not
for the several seconds to minutes that may be required of an
application-level lock).
I am new to this list and to MySQL returning to Data Bases after many
years away.
I am developing multilingual web sites with all the info extracted from
databases.
In using MySQLCC/SQLyog to stack up some static base data into MyISAM
tables the field type is text I discovered that some
At 08:47 PM 2/22/2005, you wrote:
Even if I used ColA in the query, it still uses
filesort if any keypart uses something other than an
equal operator, like a range, IN operator, IS NOT
NULL, IfNull(), etc.
Rearranging the composite index to make the sort
column the first one won't help because:
1)
80 matches
Mail list logo
