2006/11/16, Peter Brawley [EMAIL PROTECTED]:
Michael,
SELECT t1.id, t1.version, t1.value
FROM data t1
LEFT JOIN data t2 ON t1.id=t2.id AND t1.version t2.version
WHERE t2.id IS NULL;
I had almost the same problem and I found this solution very smart...
even smarter than I can understand
Dear All,
I am using mysql-4.0.26, and I have a very large innodb table(10G) .
When I try to moved the table from one mysqld to another one by mysqldump to a
script and import the script on target server, I have problem:
1) if I don't use --no-autocommit option when mysqldump, it will take
Dear All,
I have a very large table(10G) in innodb, and I want move it to another
mysql server.
Of cause, mysqldump --quick mydb mytable mytable.sql mysql -h newhost
newdb mytable.sql can do this for me, except:
1) if I use --no-autocommit while dumping, I will get error
# Error: 1197
Hi
I have more fields with values as
the-object1
the-object2
the-object45
I must to change only the part begins them to other words, example:
the-object2 must become an-object2
Which syntax I can use?
Thanks in advance
--
http://www.spacemarc.it
--
MySQL General Mailing List
For list
2006/11/17, Mike Kruckenberg [EMAIL PROTECTED]:
If it's values you are updating you can use the replace() string
function to do something like this:
update table1 set field1=replace(field1,'the','an');
To demonstrate:
mysql select replace(the-object1,the,an);
I can't find how to handle B.C. dates. I am not concerned about the
Julian to Gregorian cutover issue, and I simply need to be able to
represent dates such as the birth and death of Aristotle, 384-322 B.C..
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To
Sorry - what's your question?
#1 will work, or you can increase the value for the variable named in
the error message in scenario #2.
Dan
On 11/17/06, sofox [EMAIL PROTECTED] wrote:
Dear All,
I am using mysql-4.0.26, and I have a very large innodb table(10G) .
When I try to moved the table
I have a mysql table with 100K rows approximately.
We are creating the following index:
create index closed_base_cust_and_job on backlog_dev.closed_bases(cust_no,
jobno);
Both cust_no and jobno are varchars(255)
There is 56 fields in the table and no other indeces except on the primary
key.
John, I would guess with about 100,000 rows, it might take a couple
minutes to create an index on two varchar-255 columns. With modern
hardware anyway. Very rough estimate.
Factors include amount of RAM, speed of disks, speed of processors,
other processes running and either locking table or
Scratch that, I just created a 10 row table with 2 varchar255's.
Creating a 2 col index on it took 2.09 seconds. Could take longer due
to all the other fields you have in your table but a couple of minutes
at the outside.
Dan
On 11/17/06, Dan Buettner [EMAIL PROTECTED] wrote:
John, I
That is what I thought. Then why would it be going at 99% CPU for over 20
minutes? What factors would cause that? We are using version 4.1.20standard.
Would the fact that a website was still connected to DB cause the problem?
Dan you rock!
On 11/17/06, Dan Buettner [EMAIL PROTECTED] wrote:
On 11/17/06, John Kopanas [EMAIL PROTECTED] wrote:
That is what I thought. Then why would it be going at 99% CPU for over 20
minutes? What factors would cause that? We are using version 4.1.20
standard.
Would the fact that a website was still connected to DB cause the problem?
Normally, no
Wouldn't that would also change theater to anaater? You need the
hyphens:
mysql SELECT REPLACE(theater, the-, an-);
+---+
| REPLACE(theater, the-, an-) |
+---+
| theater |
Is there a command at the command line that can tell me if I am using MyISAM
or InnoDB? Thanks :-).
--
John Kopanas
[EMAIL PROTECTED]
http://www.kopanas.com
http://www.cusec.net
http://www.soen.info
This is will tell you your default storage engine type
should you create a table without specifying an engine:
show variables like 'storage engine';
If you want to create a table with a specific engine,
specify it at the end od the CREATE TABLE like this:
CREATE TABLE ( ... ) ENGINE=MyISAM;
show variables like 'storage_engine';
I forgot the underscore in the lastmessage
- Original Message -
From: Rolando Edwards [EMAIL PROTECTED]
To: John Kopanas [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Friday, November 17, 2006 12:30:51 PM GMT-0500 US/Eastern
Subject: Re: How Do I
For any specific table if you do:
show create table tablename;
It will tell you what the able was created using. To create tables using
a specific engine add ENGINE=name to the end of your create statement.
To see the default that is used (I think this is what the table_type
variable does):
This is in 4.0, it has changed in more recent versions.
Mike Kruckenberg wrote:
For any specific table if you do:
show create table tablename;
It will tell you what the able was created using. To create tables
using a specific engine add ENGINE=name to the end of your create
statement.
To
show variables like 'table_type'; (MySQL 4)
show variables like 'storage_engine'; (MySQL 5)
Both of these work. However, in future releases of MySQL
table_type will goes away because it was kept from backward
compatiblity with MySQL 4
- Original Message -
From: Mike Kruckenberg [EMAIL
The first time I tried to run the mysqlhotcopy script, I got an error
that indicated that Perl couldn't find the DBI module.
So, I found a site (http://www.quicomm.com/apm_dbddbi.htm) that
stepped me through making and installing
MySQL DBD and the DBI modules(? don't know if I'm using the
Well, I'm not sure about the exact definition of 'all'.
NULL OR TRUE /* result is TRUE, example (NULL = NULL) OR (1 = 1) */
and so on. Didn't try to execute the actual SQL with such a close,
though.
Thanks,
Michael
-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL
DBD::mysqlPP is the pure Perl driver for DBI. Is there a reason you are
using that instead of DBD::mysql? I haven't done Perl/MySQL on OS X
before but on other platforms (and from the error you're getting here)
DBD::mysql is the typical driver for using DBI with MySQL. It also is
the driver
Thanks, I just couldn't remember English for 'ternary'.
Remembering the and/or tables for the logic became trivial
when I replaced 'NULL' for 'UNKNOWN' but 'UNKNOWN' doesn't exist
as an SQL keyword. I suspect the founding fathers chose NULL over
words like UNKNOWN to try avoid philosophical
We're working on a site that will most likely be up 24 hours a day.
What is the best backup strategy for the database?
The client will be using hosting services, but they haven't' picked
anyone yet.
I've been playing around with mysqlimport (pretty straightforward)
and mysqlhotcopy
James, for a truly 24/7 site, MySQL replication is a better answer
than mysqlhotcopy. You leave your master running all the time, and
replicate data off to another slave server, which you back up. For
slave backups, you can use any of a few different schemes, such as
mysqlhotcopy, shut down
On 11/17/06, Dan Buettner [EMAIL PROTECTED] wrote:
James, for a truly 24/7 site, MySQL replication is a better answer
than mysqlhotcopy. You leave your master running all the time, and
replicate data off to another slave server, which you back up. For
slave backups, you can use any of a few
Ho everyone
I have a column in a table defined as float
mynumber float(20,2)
if we say mynumber column in a row is 100 , when I run this SQL :
UPDATE Table SET mynumber=mynumber-100.15
the mynumber column will be 00 not 999899.85
what is the problem ?
--
echo Hello World :)
--
In the last episode (Nov 18), Ahmad Al-Twaijiry said:
I have a column in a table defined as float
mynumber float(20,2)
if we say mynumber column in a row is 100 , when I run this SQL :
UPDATE Table SET mynumber=mynumber-100.15
the mynumber column will be 00 not 999899.85
At 03:29 PM 11/17/2006, Ahmad Al-Twaijiry wrote:
Ho everyone
I have a column in a table defined as float
mynumber float(20,2)
if we say mynumber column in a row is 100 , when I run this SQL :
UPDATE Table SET mynumber=mynumber-100.15
the mynumber column will be 00 not 999899.85
Thanks Dan Mos
I have many tables that use float (in production database), if I
convert all of the float column to DOUBLE or DECIMAL (using alter ),
is there any impact or anything I should know that could happen to me
?
is there any page that describe the differences between FLOAT, DECIMAL
In the last episode (Nov 18), Ahmad Al-Twaijiry said:
Thanks Dan Mos
I have many tables that use float (in production database), if I
convert all of the float column to DOUBLE or DECIMAL (using alter ),
is there any impact or anything I should know that could happen to
me?
is there any
sofox wrote:
Message: Multi-statement transaction required more than
'max_binlog_cache_size'
bytes of storage; increase this mysqld variable and try again
Why don't you try doing what it suggests?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
I have a text file with over 500K rows of data in it. The problem is
that the data is no seperated by commas but instead occupy a certain
amount of characters. So for instance:
ID 1 -11
NAME 12-50
COMPANY_NAME 51-100
...
How would you parse import this data into mysql?
Thanks for your help
I did a little shell script to do it. the key was the shell variable IFS:
Normally IFS=
to make it work right I set it as follows:
IFS=
Yes, thats a newline between the quotes
John Kopanas wrote:
I have a text file with over 500K rows of data in it. The problem is
that the data is no
In the last episode (Nov 17), John Kopanas said:
I have a text file with over 500K rows of data in it. The problem is
that the data is no seperated by commas but instead occupy a certain
amount of characters. So for instance:
ID 1 -11
NAME 12-50
COMPANY_NAME 51-100
...
How would you
I am trying to figure out how this would work? How does LOAD DATA
figure out when one column begins and another ends when some of the
data are addresses with spaces in them?
On 11/17/06, Dan Nelson [EMAIL PROTECTED] wrote:
In the last episode (Nov 17), John Kopanas said:
I have a text file
In the last episode (Nov 17), John Kopanas said:
On 11/17/06, Dan Nelson [EMAIL PROTECTED] wrote:
In the last episode (Nov 17), John Kopanas said:
I have a text file with over 500K rows of data in it. The problem
is that the data is no seperated by commas but instead occupy a
certain
I've got a FreeBSD system (named 'db1') running mysql 4.1.14 with
innodb. I am running a dump onto another system in preparation for
setting up a third system as a slave. (I can't use the flush tables
with read lock because the db1 has a single tablespace file and I
need to have per-database
Hi,
Why can't you try copying the data directory itself to the target server?
usually data directory resides in,
for windows C:\Program Files\MySQL\MySQL Server 5.0\data
for Unix Linux -/usr/local/mysql, /var/lib/mysql respectively.
or from the path where you installed mysql.
Thanks
Hi,
If you are particular about a table, i.e what engine my table uses?
Try,
mysql show table status like 'tablename' \G
Thanks
ViSolve DB Team.
- Original Message -
From: John Kopanas [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Friday, November 17, 2006 10:43 PM
Subject: How
Hi,
LOAD DATA restricts only on BLOB,TEXT,ENUM and FIXED DATA. Others are
equally supported [INSERT stmt]. Likewise for mysqlimport also.
If the data file has no delimiter other than space[even the name,
addresss,etc contain spaces], then the target table will not the one what is
41 matches
Mail list logo