I'm seeing:
innodb.comHere are some
related websites for:
innodb.com
Sponsored Links
MySQL database designData
ner [mailto:[EMAIL PROTECTED]
>Sent: Wednesday, October 18, 2006 3:28 PM
>To: George Law
>Cc: mysql@lists.mysql.com
>Subject: Re: bin-log with expire_logs_days
>
>I haven't used the server variable you refer to, but instead have
>always used an external command piped in vi
f I were to add 'expire_logs_days 45' to my.cnf and restart mysql, is
mysql going to attempt to purge the logs
> 45 days old and if so... how long does it typically take. We cannot
afford to restart if its going to take
any significant amount of time for it to purge the logs and restart
m what the previous admin who I inherited this from says, this
locks up the whole database while its purging the logs.
Are there any low-impact solutions?
This is a fairly high traffic DB, so locking up the database really is
not an option.
Thanks!
George Law
glaw at ionosphere.net
--
MySQL Gener
show processlist gives you an abbreviated list of queries.
show full processlist gives you the full queries.
>-Original Message-
>From: Feliks Shvartsburd [mailto:[EMAIL PROTECTED]
>Sent: Tuesday, October 03, 2006 1:57 PM
>To: mysql@lists.mysql.com; [EMAIL PROTECTED]
>Subject: Questio
",?" : "?") for (@results);
my $sth2 = $dbh2->prepare("INSERT INTO table2 values
($placeholders);");
$sth2->execute(@results);
$sth2->finish;
}
$sth1->finish;
$dbh1->disconnect();
$dbh2->disconnect();
George Law
[EMAIL PROTECTED]
MSN: [EMAI
damn outlook!
fire up perl's CPAN interface:
perl -MCPAN -eshell
cpan> i /mysql/
...
Bundle Bundle::DBD::mysql (C/CA/CAPTTOFU/DBD-mysql-3.0007.tar.gz)
...
cpan> install Bundle::DBD::mysql
>>>-Original Message-
>>>From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED]
>>>Sent: M
someone correct me if I am wrong.
DBI is the generic database interface module for perl
DBD::mysql is the mysql specific module DBI relies upon to talk to mysql.
cpan> i /mysql/
>>>-Original Message-
>>>From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED]
>>>Sent: Monday, September 25,
crname` char(50) default NULL,
`call_dest_custid` char(20) default NULL,
`call_zone_data` char(20) default NULL,
`calling_party_on_dest_num_type` int(1) default '0',
`calling_party_from_src_num_type` int(1) default '0',
`original_isdn_cause_code` int(1) default '0&#
l Message-
>>>From: Jay Pipes [mailto:[EMAIL PROTECTED]
>>>Sent: Wednesday, August 30, 2006 1:44 PM
>>>To: George Law
>>>Cc: mysql@lists.mysql.com
>>>Subject: RE: Degrading write performance using MySQL 5.0.24
>>>
>>>What type of data are
I see the same type of slow downs using 5.0.18
I am using "load data in file" to load CSV files.
with clean tables, I see fairly quick inserts (ie "instant")
2006-08-30 12:07:15 : begin import into table1
2006-08-30 12:07:15: end import into table1 records (10962)
>From earlier this morning
Love it when that happens :)
Fastest way I can think of is dumping out the structure of the database
with mysqldump -d > database.sql
and then searching the output to see where those columns appear
>>>-Original Message-
>>>From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
>>>Sent: Tuesd
idth char fields.
So, I am eager to see if this sorting idea helps any.
--
George
>>>-Original Message-
>>>From: Dan Buettner [mailto:[EMAIL PROTECTED]
>>>Sent: Friday, August 18, 2006 3:53 PM
>>>To: George Law
>>>Cc: MYSQL General List
&
r [mailto:[EMAIL PROTECTED]
>>>Sent: Friday, August 18, 2006 1:40 PM
>>>To: George Law
>>>Cc: MYSQL General List
>>>Subject: Re: sorting datafile for "load data infile"
>>>
>>>George, I've not heard of that technique - "sorting
89628873 |
| 008bbf9dc9691110800100201c00edff | 89627287 |
Any suggestions on exactly how to sort those fields before
Thanks!!!
--
George Law
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mark,
Perfect! Thanks!!
I altered my table to specify ZEROFILL and that did the the trick.
--
George
>>>-Original Message-
>>>From: Mark Leith [mailto:[EMAIL PROTECTED]
>>>Sent: Wednesday, August 09, 2006 3:56 PM
>>>To: Jonathan Mangin
&
Hello All,
I have what is probably a simple question.
I have a table of phone numbers, broken into npa,nxx,station
So, 8001231234npa =800 nxx=123 station=1234
Some queries pull data from this table in the format:
select * from table where concat(npa,nxx,station)=8001231234
That is
Nick,
It looks like the mysql part has been answered. I was going to suggest
using a ENUM
field myself.
For the PHP part, if you are trying to keep multiple rows in your HTML
correlated,
ie $customer[1] = $mod_type[1] you may not want to use a checkbox field
checkbox fields only pass a field if
I think if you do it as a enum field, it shows as a checkbox in phpmysql
define it as enum 'Yes','No'
>>>-Original Message-
>>>From: Brian E Boothe [mailto:[EMAIL PROTECTED]
>>>Sent: Tuesday, July 25, 2006 11:07 AM
>>>To: mysql@lists.mysql.com
>>>Subject: yes or no checkbox
>>>
>>>kn
Just a shot in the dark...
how about : \T /home/karl/sql_log_$$
I believe, in bash, $$ will give you the pid of the process, so you
should
get a unique (somewhat) file name every time
>>>-Original Message-
>>>From: Karl Larsen [mailto:[EMAIL PROTECTED]
>>>Sent: Tuesday, July 11, 2006
SELECT max(id) FROM menu_links
:)
> -Original Message-
> From: Mike Wexler [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 29, 2006 1:22 PM
> To: Mike
> Cc: mysql@lists.mysql.com
> Subject: Re: MS SQL TOP Replacement
>
> Mike wrote:
> > I have a SELECT statement for MS SQL that is
> >
I had to do some disk space recovery mysql last month...
I have backed up some of the raw .MYI, .MYD files and deleted them from
the data directory. Running 5.0.18, I had to shut down mysql and
restart before it freed up the space.
> -Original Message-
> From: Dan Nelson [mailto:[EMAIL
You don't need to take it to 16 digits :
mysql 4 :
select 1/666;
+---+
| 1/666 |
+---+
| 0.00 |
+---+
mysql 5.0.18:
select 1/666;
++
| 1/666 |
++
| 0.0015 |
+----+
George Law
> -Original Message-
> From: Chris W [mailto:[EMAIL PROTEC
(unix_timestamp(NOW()) - 1148492137)/60;
+-+
| (unix_timestamp(NOW()) - 1148492137)/60 |
+-+
|2.32 |
George Law
> -Original Message-
> From: Brian Menke [mailto:[EMAIL PRO
more than likely someone is harvesting emails off groups.google.com
http://groups.google.com/group/mailing.database.myodbc
Your posting shows top of the list :)
George Law
> -Original Message-
> From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, May 24, 200
there is also a nice tool for Mac OSX called CocoaMySQL.
http://cocoamysql.sourceforge.net/
George Law
> -Original Message-
> From: Rajesh Mehrotra [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 23, 2006 3:58 PM
> To: Rithish Saralaya; mysql@lists.mysql.com
> Subject:
I ran into this same type of question the other evening at a local
linux group.
I think that once you reach the end of the results set the only way to
start back at the beginning of that results set is to do the query
again.
Once option - do your query and grab all the rows
load them into a dat
Nicholas,
just found something on google:
"In general, this cannot be done. Default values cannot be the return of
a
MySQL function (as much as I'd love to use NOW() for default values!).
However, there's one loophole. When inserting, not specifying a value
for
the first timestamp field in a ta
try a search for "old_password"
-Original Message-
From: Amer Neely [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 10, 2006 1:39 PM
To: MySQL List
Subject: MySQL 5 / phpMyAdmin
I've finally got MySQL 5.0 going on my Win2K machine, but phpMyAdmin
2.8.0.3 is now giving me grief. It giv
$query = "INSERT INTO staffs (firstname, lastname, signin) VALUES
('$firstname', '$lastname', NOW())";
I think if you alter the table and set a default value on signin to
NOW()
ALTER TABLE staffs CHANGE signin signin DATETIME DEFAULT 'now()' not
null';
then you could just to :
$query = "INSE
Johan,
have you thought about doing this incrementally?
ie - 25% at a time x 4
to show something for NULL, you can use the
COALESCE function.
ie - COALESCE(column,'nothing')
--
George Law
VoIP Network Developer
864-678-3161
[EMAIL PROTECTED]
MSN: [EMAIL PROTECTED]
-Origin
I think what he is saying is that be began the "transaction" in a
command line client
session from one location but was not able to give the
> mysql> SET FOREIGN_KEY_CHECKS = 1;
> mysql> COMMIT;
commands in the same session. (ie - I owe, I owe, its off to work I go
:) )
This is a good questi
DOH!
Sorry - I totally misread the question!
A quick google for "rename database" shows Adrian is correct!
-Original Message-
From: Adrian Bruce [mailto:[EMAIL PROTECTED]
Sent: Friday, May 05, 2006 9:51 AM
To: George Law
Cc: Hardi OK; mysql@lists.mysql.com
Subject:
Hardi
I rotate tables out on a monthly basis. The way I do it is:
rename table1 to table2
If you need a new copy of table1, you can do :
create table table1 like table2
--
George
-Original Message-
From: Hardi OK [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 04, 2006 11:19 PM
To
try :
select . into outfile '/tmp/t3.csv' FIELDS TERMINATED BY ',' LINES
TERMINATED BY '\n' from table where .
This will create a file in the /tmp directory on the DB server itself
this doesn't do the column headings and your output file cannot already
exist.
-Original Mess
try:
group by substring(timeofclick,1,10)
-Original Message-
From: Randy Paries [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 03, 2006 11:25 AM
To: mysql@lists.mysql.com
Subject: Help with this query. How to do a group by on a datetime just
the month/day/year
Hello,
Not sure if i ca
Shawn,
Perhaps :
UPDATE TABLE_NAME SET FIELD4 = concat(FIELD1,FIELD2);
-Original Message-
From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 03, 2006 9:33 AM
To: Mysql General (E-mail)
Subject: UPDATE question
If I have 4 Fields (FIELD1, FIELD2, FIELD3 &
perl ?
just quickly throwing something together - this is untested.
$done = 0;
$count = 0;
while ($done) {
$dbh->do("analyze table t1");
my $info = $dbh->{mysql_info};
if ($info =~ /some kind of error message/) {
$dbh->do("repair table t1");
my $info2 = $dbh->
Pedro,
I haven't ever seen those file types - but just a thought. Maybe they
are
Just flat files and the extension relates to the "table" contained there
in.
If you have unix shell access, try the "file" command - ie "file
file.bdd"
If it reports plain text - chances are its just a flat file -
'\n' works for me in the command line client
select concat('Co: ABC','\n','Acct: 123','\n','Drive: Summer Special');
+-+
| concat('Co: ABC','\n','Acct: 123','\n','Drive: Summer Special') |
+---
After you give the query, try :
echo mysql_error()."".mysql_info();
This way, you should be able to see exactly what error is coming back
from PHP/MySQL
-Original Message-
From: Tom Lobato [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 20, 2006 1:55 PM
To: Jay Blanchard; mysql@lists
This brings up a question I was asked...
Which is more efficient?
Select . where date between '-mm-dd hh:mm:ss' and '-mm-dd
hh:mm:ss'
Or
Select . where unix_timestamp(date) between
unix_timestamp('-mm-dd hh:mm:ss') and unix_timestamp('-mm-dd
hh:mm:ss')
PHP is picky on what can and cannot be in quotes... I get this all the
time.
Try :
$sql = "INSERT INTO addvalue (`value1`, `value2`,
`sumfeild`) VALUES ('".$value1."', '".$value2."', '".$sumfield."')";
$result=MYSQL_QUERY($sql);
I think part of the error had to do with you missing a ) at the e
Mike,
Maybe :
LAST_DAY(date)
Takes a date or datetime value and returns the corresponding value for
the last day of the month. Returns NULL if the argument is invalid.
mysql> SELECT LAST_DAY('2003-02-05');
-> '2003-02-28'
mysql> SELECT LAST_DAY('2004-02-05');
-> '2004-02-29'
m
You should be able to pull each table (create + inserts) out into a stand
alone file
table1.sql
table2.sql
etc...
and then you can restore table by table.
This might be harder depending on the size of your database tables. If you
have a million rows, chances
are your .sql file is huge and it
Movable Type + OSXServer + MySQL issues.Miles,
David is correct - DBI isn't installed in OSX by default.
check out http://www.brandonhutchinson.com/installing_perl_modules.html for a
brief into
on installing from CPAN.
Make sure you do this as root or through sudo
--
George
- Original
mysqldump has a -w option:
-w, --where= dump only selected records; QUOTES mandatory!
--
George
> -Original Message-
> From: 2wsxdr5 [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 02, 2006 12:30 PM
> To: 'MySQL general mailing list'
> Subject: dumping results of a select
>
ill change a single
quote ' to a double '' so its mysql safe. Then when it reads it
back in from the database
and renders the HTML, it also displays the doubles.
If you look in the database iteself from the mysql command line client
do the double quotes show?
--
George Law
Is your mysql server listening just on localhost (127.0.0.1)?
That is differnet than 192.xxx.xxx.xxx
> -Original Message-
> From: Xiaobo Chen [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, February 28, 2006 11:27 AM
> To: Amaia Anabitarte
> Cc: mysql@lists.mysql.com
> Subject: Re: jdbc:
Schalk ,
You need to specify the unifying column between your ablb and abm tables.
ie - in your where, "and ablb.id=abm.id"
Once you get this so it returns expected results, you can run the query,
prefaced with
"explain" and it will give you an idea on the way mysql is running the
query. Thi
Daniel,
What about just running your import file through dos2unix before piping
it to mysql?
That way, it would leave \n alone (since that is unix format) and
replace any occurrances of
\r\n with plan old \n.
--
George
> -Original Message-
> From: Daniel Kasak [mailto:[EMAIL PROTECTE
Michael,
can you connect using the command line client? sounds to me like it may be
the "old_password" problem.
http://dev.mysql.com/doc/refman/5.0/en/old-client.html
--
George
- Original Message -
From: "Michael Satterwhite" <[EMAIL PROTECTED]>
To: "MySQL List"
Sent: Sunday, Feb
Henry,
Maybe the shell is doing something funky with the parens?
maybe try escaping them with the \ ??
Just a wild guess :)
> -Original Message-
> From: Henry Dall [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 15, 2006 12:06 PM
> To: mysql@lists.mysql.com
> Subject: Problem
Andre,
I tried this a couple weeks ago... I think you want the "on duplicate"
option for the INSERT query.
depends what version you have... I think this was introduced in mysql 4.1
insert into values () on duplicate key update set x=2,y=5;
unfortunately, the server I was testing thi
looks like there is only a partial query
INSERT INTO `jos_banner` VALUES (1,1,'banner','OSM
1',0,46,0,'osmbanner1.png
','http://www.opensourcematters.org','2004-07-07$
there is no ending ', so when it hits the 'jos_banner
/*!4 ALTER TABLE `jos_banner`
it sees it as the end of the field
I have also seen the table locking on deletes and even on large selects
with INNODB. I had converted to INNODB strictly for the "row level
locking" that is the biggest selling point of using INNODB.
So all the avantages of INNODB that are advertised (ie - row level
locking) are mis-represented?
You might get a time out with phpMyAdmin
The many web hosts I have used pretty much have all used php's default
90 second exection time for php pages.
I have a zip code database with 50,000 records and had to do this import
through a ssh session
On the web server using mysqldump on the old server
site
belongs to a previous employer). At the time, I think I had to drop the
unpacked jar file for the mysql
driver into the Tomcat source directory. That is about all I remember
about Tomcat.
--
George Law
> -Original Message-
> From: Chuck Craig [mailto:[EMAIL PROTECTED]
>
Phillip,
You probably didn't need to upgrade - just set the mysql password to use
"old_password":
FTFM:
"As indicated earlier, a danger in this scenario is that it is possible
for accounts that have a short password hash to become inaccessible to
pre-4.1 clients. A change to such an account'
David,
Are you using persistent connections? Sounds like perhaps a persistent
connection is timing out.
Maybe a quick work around would be to call a "check status" routine (ie
- do a show status), just to see if the connection is still there. If
this fails, just do a mysql_connect... Before c
to find
the longest sub string match right in SQL.
Thanks!!
George Law
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ssage-----
From: George Law [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 24, 2006 8:14 AM
To: Nicolas Verhaeghe; mysql@lists.mysql.com
Subject: RE: How can I isolate the integer part of a varchar field and
use it in an ORDER BY?
Nicolas,
Not sure when the "replace" function was int
Nicolas,
Not sure when the "replace" function was introduced into mysql, but I
think it might do...
Use "replace" in your order by, replacing a-z with null chars, leaving
just your numeric digits, then order by
select * from test;
+-+---+
| uid | name |
+-+---+
|
Just a quick question regarding mysql backups.
I know myisam tables are portal from machine to machine, but I know
there are some differences
Between innodb tables.
I am running mysql 5.0.18 on suse linux 10. I have innodb set up so it
stores each table in its own
.idb file.
I've read that i
Andrew,
did you install mysql when you installed suse?
you might have to fire up YaST and install the rpms.
this is a little old... but shoudl help:
http://www.novell.com/coolsolutions/feature/595.html
this is for 9.1, but the procedure should have remained pretty much the same
with suse 10.
e - SHARED mode). That was my big reason for using INNODB for this
table. It just doesn't seem right that the whole table seems to get locked
on the select.
Does this have anything to do with the variable:
| tx_isolation| REPEATABLE-READ |
Thanks
Mark,
Sql is an alias to mysq -u.
What about something like :
declare -a TEAMS=(`echo "query"|sql|sed 's/$/",/g'|sed 's/^/"/g'|sed
's/"$//'`)
since the query returns the results 1 per line, the first sed prefixes
each line with a quote
second sed replaces the newline with quot
ieve it is hardware related
I have to work on an automatic way to rotate these tables every week.
Is there an easy way with SQL to create a new table based on the schema
of an existing table?
--
Thanks!
George Law
g that way
-Original Message-----
From: George Law [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 1:14 PM
To: mysql@lists.mysql.com
Subject: mysql 5 - disk bound ?
Hi All,
I have a question for you all.
Working with 2 innodb tables. One is a table of cdrs (call detail
rec
Hi All,
I have a question for you all.
Working with 2 innodb tables. One is a table of cdrs (call detail
records) with 33 columns and 7 million + rows. Second table is a clone
of the first, meant as a "work" table.
>From the mysql command line client, I gave the query :
insert into table2 sele
ith Apache 2, PHP5, and one of the latest 4.X versions of
mysql.
There are generic RPMs for mysql 5 on mysql's website - but I haven't used
them - I am running 5.0.18, but using the binary distro because I needed to
run both 4.x and 5.x at the same time to migrate some d
elped some what.
I notice in the load data command there is a "CONCURRENT" option - but as
far as I can tell, that only does MyISAM tables? You also suggested
disablign the keys. I think I tried that, but with a table with 7 mil rows,
that takes longer than the load data :-\
TIA.
Ge
k to
perl for this, but that might be my next step.
anyone have any suggestions. I am not posting any table descriptions
here, but will do if needed.
--
George Law
VoIP Network Developer
864-678-3161
[EMAIL PROTECTED]
MSN: [EMAIL PROTECTED]
-
1 D mysql- - 3 77 0 - - sync_p 10:14 -
00:01:46 -
1 S mysql- - 0 75 0 - - 1460 10:17 -
00:00:16 -
1 S mysql- - 1 76 0 - - 322560 10:33 -
00:00:28 -
George Law
VoIP Network Developer
864-678-3161
[EMAIL PROTECTED]
MSN: [EMAIL
MySQL binlog file position 0 0, file name
060106 10:05:08 InnoDB: Started; log sequence number 4 489673016
060106 10:05:08 [Note] /usr/local/mysql/bin/mysqld: ready for
connections.
Version: '5.0.18-standard' socket: '/tmp/mysql.sock' port: 3306 MySQL
Community Edition - Standard (GPL)
George Law
VoIP Network Developer
864-678-3161
[EMAIL PROTECTED]
MSN: [EMAIL PROTECTED]
75 matches
Mail list logo
