I finally got the script working. Seems to run smooth on my FreeBSD 4.11system:
MYSQLDUMP=`which mysqldump 2/dev/null` ||
MYSQLDUMP=/usr/local/bin/mysqldump
MYSQLDUMP_ARGS=--opt --extended-insert -h localhost -umyuser -pmypassword
mydb
ARCHDIR=/backup/mysql
NAME=db_dump
# Remove archives older
Hello
I need to move my databases from on server to an other.
As lots of data are in production I cannot just stop mysql for 1/2 an
hour and reopen it on the new server.
What I expect to do is while backuping and restoring to the new server,
I wish to set the original server in read only mode
Good morning Andreas -
The --opt flag implies --extended-insert, in addition to some other
options, to generate an optimized (fast) dump file. See man
mysqldump. You don't need both but having both shouldn't hurt.
To restore, pipe your dump file back into the mysql client, a la
mysql -u user
How do I call a MySQL stored procedure from an ASP application and get the
value of an Out Parameter?
I've got the following Stored Procedure defined in my database:
DELIMITER $$
DROP PROCEDURE IF EXISTS `bpa`.`sp_GetNextInv` $$
CREATE PROCEDURE `sp_GetNextInv`(
IN nChapterID Int,
OUT cInvNo
Wow, I'm really sorry about that. Left out a zero. I should stop answering
questions before the holiday weekend.
I was suggesting a minor change to 500 to see if that would work. Everything I've read about adjusting for table full errors always
specifies both. Since only one was changed, MySQL
Instead of locking the table, why not just change the privileges for the specific accounts so they have select only privileges?
Then you still of write access through admin accounts if need be.
- Original Message -
From: Jehan PROCACCIA [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Hi All,
I compressed a MyISAM table successfully with MYISAMPACK, followed by MYISAMCHK.
Both tables (MyISAM + Compressed ) have exactly the same number of rows with
Count(*).
But when I give a SELECT query with EXPLAIN on both tables, I get different
number of rows.
For example: EXPLAIN SELECT
What am I missing here?
select m.*, d.discussion, d.discussion_id, u.user_id, t.topic_id
from forums_messages
left join forums_members m, forums_discussions d, users u, forums_topics t
on m.topic_id = t.topic_id, t.discussion_id = d.discussion_id
where m.topic_id = 1;
Is it legal to do multiple
Steffan A. Cline wrote:
What am I missing here?
select m.*, d.discussion, d.discussion_id, u.user_id, t.topic_id
from forums_messages
left join forums_members m, forums_discussions d, users u, forums_topics t
on m.topic_id = t.topic_id, t.discussion_id = d.discussion_id
where m.topic_id = 1;
This is what I finally settled on. It seems to work well.
select m.*, d.discussion, d.discussion_id, u.user_id, u.icon, u.nick_name
from forums_messages m
left join forums_topics t
on m.topic_id = t.topic_id
left join forums_discussions d
on t.discussion_id = d.discussion_id
left join users u
on
I'd like to sum up values from two different columns in one table that
is constructed like this:
CREATE TABLE `nominations`
( `id` smallint(5) unsigned NOT NULL auto_increment,
`name` varchar(255) default NULL,
`SectionA_Nominee1` varchar(255) NOT NULL default 'n/a',
Yes it's legal to do multiple join. No, the way you declared you joins is not legal. At the very least it confusing. Do you want to
left join discussion, users and topics, or just memebers?
I'm not sure if MySQL would accept things in that order. I always specifically declare my joins (left or
Cor -
Those numbers are an estimate, not a hard number, of how many rows
MySQL thinks it will have to exmaine to find what you're looking for.
They could be different because your compressed table is brand-new and
all the indexes are clean and up to date, whereas your original table
has
I personally would be wary of a solution like what you're proposing
(locking all tables, that is).
The problem I see is this - you lock all tables and proceed to move
your data over to another host. Meanwhile, clients could well be
queueing up insert or update commands that are simply blocking,
I have a table `event` with two keys:
`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`location_id` MEDIUMINT(8) UNSIGNED NOT NULL,
`timestamp` DATETIME NOT NULL,
`type` ENUM('0','1','2','3','4','5','6','7','8','9') NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`location_id`,`timestamp`,`type`)
Since one person can be listed in both 1 and 2 in each section
(just not
in the same row) I need to sum up that person's votes between the
two
columns. I've tried summing two subqueries but it errors out
because of
returning two values.
Unclear. Where is a user's 1 or 2 recorded? What is a
Here's a sample of some votes in the table:
SectionA Nominee1 SectionA Nominee2
Cynthia Smith n/a
Maggie Doe n/a
Maggie Doe Cynthia Smith
Maggie Doe Cynthia Smith
Maggie Doe Cynthia Smith
OK so it seems to be a bad idea ... I was expecting that I missed a
magic command like set readonly on all databases ...
however, still thinking in a probably bad solution , what about setting
unix file system acces mode to the database files to read only (400)
wouldn't be a good idea ? (if I
On Jun 30, 2006, at 10:44 AM, Rob Desbois wrote:
That leaves me with ON DUPLICATE KEY UPDATE. It's not amazingly
helpful as you have to provide a column to update - however I can
just say e.g.
ON DUPLICATE KEY UPDATE id=id
The problem with this is that if I then do SELECT LAST_INSERT_ID
John Hicks wrote:
I don't see a question here.
But that won't stop me from giving a little advice :)
It is generally more important to keep things simple (by not
denormalizing) than to try to optimize performance by complicating
things significantly.
Moreover, I can't see how combining several
A specific character that would occupy less space? You mean like using lower case instead of upper case? All characters use the same
amount of space (unless your using Chinese or something).
I don't think reducing the number of columns will help. Usually you take other performance enhancing
Is there a Select statement I can use to get table names so that I could use
other Select statement syntax on the results?
What I'd like to do is this,
SHOW Replace(TABLES, 'tbl','') Like 'tbl%';
But this doesn't work so I need a Select statement that can do the same thing.
Thanks in
Pardon me, i think I din't express myself properly.
I did not only combining data elements.
My structure was:
Table_1{
id_table1 (int)
COLLUM 1 (int)
COLLUM 2 (int)
}
Table_2{
id_table1 (int)
COLLUM 1 (int)
COLLUM 2 (int)
}
Being a relationship between table_1 and table_2
It's basically a log that people needs to be able to search with
wildcards in... the log grows many thousand records per day and never
gets smaller, so searches just gets slower and slower. There is a sort
field, the timestamp which is used in the searches, but it only makes
the searches
Wow, that is a tough one.
My question would be, how often is older data really accessed? Could
you start incorporating a default date range like past 3 months or
past 1 year into all searches, but allow people to override it if
needed? Then if you add an index on the timestamp column it would
In the last episode (Jun 30), Ed Reed said:
Is there a Select statement I can use to get table names so that I
could use other Select statement syntax on the results?
What I'd like to do is this,
SHOW Replace(TABLES, 'tbl','') Like 'tbl%';
But this doesn't work so I need a Select
Opps!
Thanks for the quick reply, Dan. But I forgot to mention I'm running MySQL
4.1.11
Any other suggestions?
Thanks again.
Dan Nelson [EMAIL PROTECTED] 6/30/06 2:48:57 PM
In the last episode (Jun 30), Ed Reed said:
Is there a Select statement I can use to get table names so that I
I forgot I have two databases snortdb and archive_snortdb with same
schema.
Syslog did not distinguish between the two(2).
Both were suffering from the 4GB limit.
Once I increased max_row the error stopped on the client.
Brent, Thanks again,
Raymond
-Original Message-
From: Brent
I'm seeing something that I don't think should be happening, but I'm
not sure if it's a MySQL bug.
To allow some of my stored procedures to operate concurrently, I make
a temporary memory copy of some shared tables that are accessed by the
procedures. The temporary heap table has the same name
Is there a way with InnoDB tables to see all related tables/columns?
Basically what I want to do is make a script that somehow will dynamically
create a backup of a single user record. But I don't want to have to
manually add a new table or column everytime to the script.
So for example, I have
That is, as far as I know, impossible. Mysql does not know which
tables are related to which ones.
Peter
On 6/30/06, Daevid Vincent [EMAIL PROTECTED] wrote:
Is there a way with InnoDB tables to see all related tables/columns?
Basically what I want to do is make a script that somehow will
That doesn't seem right. I know that MYISAM tables don't understand
relations, but INNODB tables most certainly understand foreign key
constraints and in fact cause me a lot of grief sometimes when trying to
insert a record or create a new table that violates said constraints ;-)
DÆVID
Oh, InnoDB tables? I was assuming MyISAM tables.. :) And I'm not a
total expert, but that's my understanding.
Peter
On 6/30/06, Daevid Vincent [EMAIL PROTECTED] wrote:
That doesn't seem right. I know that MYISAM tables don't understand
relations, but INNODB tables most certainly understand
Daevid,
I have a 'users' table. And there are all kinds of related
tables like 'logs', 'preferences', etc. that all have a FK
reference back to this user ID.
I want to make a query or use some PHP to build a list of
all tables in my DB that are references.
Query to find tables with FK
EXCELLENT! Thanks!
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]
Sent: Friday, June 30, 2006 8:31 PM
To: Daevid Vincent
Cc: mysql@lists.mysql.com
Subject: Re: Need way to see all relations?
Daevid,
I have a 'users' table. And there are all kinds of related
Hi,
In the /data/database_name directory I have found a few files I don't know
what they are used for. I have seen that some of them are pretty big. I
don't think it is safe to delete them, but can I do something to decrease
their size at least?
Here are those files and their sizes in MB:
1
36 matches
Mail list logo
