From: "Pavel Novak"
> | 1 | SIMPLE | o | ALL| NULL | NULL|NULL
> | NULL| 20402 | Using temporary; Using filesort |
Well, this is a clue IMHO... no suitable indexes in o could be found to use
in this query...
Since you use a lot of columns fro
symbulos partners wrote:
Thanks for the extremely useful answer.
Some comments, questions here below.
On Friday 28 Jan 2005 16:38, you wrote:
No, only the BLOB/TEXT columns need to be moved to MyISAM.
Yes, I thought of doing so. The drawback is that you de-normalise the
database. Is that correct?
Are you talking about display width? Use the RPAD function:
http://dev.mysql.com/doc/mysql/en/string-functions.html
mysql> SELECT RPAD('foo', 20, 'x') AS foo;
+--+
| foo |
+--+
| foox |
+--+
___
Jacob Friis Larsen wrote:
On Fri, 28 Jan 2005 11:47:47 +, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
Jacob Friis Larsen <[EMAIL PROTECTED]> wrote on 28/01/2005 11:23:46:
We have a table that grow by 200MB each day.
Should we put data in different tables or is one big table just as fast?
We w
symbulos partners wrote:
Is there any other workaround? The reason because we are using InnoDB is
because there s full support
- for foreign keys,
- for joint queries
- for rollback on commit
Does anybody know any other way of indexing the table in a way, which would
allow full text search?
> > This extreme slowness in dropping a simple index in
> > MySQL defeats the whole strategy of dropping indexes
> > on some tables before a huge insert operation.
>
> See http://dev.mysql.com/doc/mysql/en/alter-table.html ,
> especially these bits:
>
> "Note that if you use any other option to AL
Minh La wrote:
Hi, Can some help me with a date conversion problme
that I am having.
I have a date data in the following format:
'Month Days Year Hour:Minute AM/PM'
Example: 'Aug 21, 2004 2:00 PM'
So far the hours that I have spent have been in vain.
I tried using str_to_date, but it keeps failin
Homam S.A. wrote:
Dropping an index on a MyISAM table should be
instantaneous. It should only take as long as deleting
the idx file from the file system. But it's taking
almost as long as creating the index itself!
Here's my queries and time they took:
/*[10:58:17 AM][367172 ms]*/ alter table MyTab
Homam S.A. wrote:
This extreme slowness in dropping a simple index in
MySQL defeats the whole strategy of dropping indexes
on some tables before a huge insert operation.
See http://dev.mysql.com/doc/mysql/en/alter-table.html ,
especially these bits:
"Note that if you use any other option to ALTER
"Homam S.A." <[EMAIL PROTECTED]> wrote on 01/28/2005 02:27:51 PM:
> Dropping an index on a MyISAM table should be
> instantaneous. It should only take as long as deleting
> the idx file from the file system. But it's taking
> almost as long as creating the index itself!
>
> Here's my queries and
Dropping an index on a MyISAM table should be
instantaneous. It should only take as long as deleting
the idx file from the file system. But it's taking
almost as long as creating the index itself!
Here's my queries and time they took:
/*[10:58:17 AM][367172 ms]*/ alter table MyTable add
index (My
Dathan Pattishall wrote:
Use char
And use fixed-length types for *all* columns... one variable-length
column makes all records variable-length.
Nick
-Original Message-
From: Marc Michalowski [mailto:[EMAIL PROTECTED]
Sent: Friday, January 28, 2005 10:28 AM
To: mysql@lists.mysql.com
S
Minh La wrote:
So far the hours that I have spent have been in vain.
Next time a couple of minutes with the Fine Manual instead? :-)
I tried using str_to_date, but it keeps failing.
Looks like it's not in version 4.0.2?
Quoting the FM:
"STR_TO_DATE() is available as of MySQL 4.1.1."
FWIW,
--
Ha
Hi, Can some help me with a date conversion problme
that I am having.
I have a date data in the following format:
'Month Days Year Hour:Minute AM/PM'
Example: 'Aug 21, 2004 2:00 PM'
So far the hours that I have spent have been in vain.
I tried using str_to_date, but it keeps failing.
Looks lik
Use char
DVP
Dathan Vance Pattishall http://www.friendster.com
> -Original Message-
> From: Marc Michalowski [mailto:[EMAIL PROTECTED]
> Sent: Friday, January 28, 2005 10:28 AM
> To: mysql@lists.mysql.com
> Subject: Fixed with Fields
>
> I was wondering if there is a way to
I was wondering if there is a way to create fixed width fields. Example:
The field is set to 18 but data contained is 11. I need the length to
remain 18. Is there anyway to do this? Thanks for your help.
-Marc
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsub
On Fri, 28 Jan 2005 09:47:24 +0200, Gleb Paharenko wrote:
> Hello.
>
> The mysql program uses user sebyte and password for user ddj.
> When mysql starts it calls for load_defaults(), which put
> the arguments from your config file before the command line options
> that you specified. And than us
"Diana Castillo" <[EMAIL PROTECTED]> wrote on 01/28/2005 11:53:20 AM:
> how do I make a query that does this?
> update tbl_a set location_code=0 where tbl_a.country_id = (select id
from
> countries where has_zones=0)
>
The UPDATE statement (as of 4.0.4) allows you to update multiple tables at
On Fri, 28 Jan 2005 11:47:47 +, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> Jacob Friis Larsen <[EMAIL PROTECTED]> wrote on 28/01/2005 11:23:46:
>
> > We have a table that grow by 200MB each day.
> >
> > Should we put data in different tables or is one big table just as fast?
> > We will fo
What does the error log say? Anything?
Donny
> -Original Message-
> From: Frank Denis (Jedi/Sector One) [mailto:[EMAIL PROTECTED]
> Sent: Friday, January 28, 2005 10:42 AM
> To: Mat
> Cc: mysql@lists.mysql.com
> Subject: Re: 2 gigs limits on MyISAM indexes?
>
> On Fri, Jan 28, 2005 at 0
Thanks for the extremely useful answer.
Some comments, questions here below.
On Friday 28 Jan 2005 16:38, you wrote:
> No, only the BLOB/TEXT columns need to be moved to MyISAM.
Yes, I thought of doing so. The drawback is that you de-normalise the
database. Is that correct?
There are 2 alterna
Thanks for the extremely useful answer.
Some comments, questions here below.
On Friday 28 Jan 2005 16:38, you wrote:
> No, only the BLOB/TEXT columns need to be moved to MyISAM.
Yes, I thought of doing so. The drawback is that you de-normalise the
database. Is that correct?
There are 2 alterna
That depends on what version of MySQL you are using. MySQL 4.1+ has
support for subselects, so you could do what you have there.
I'm assuming that you're running < 4.1, otherwise you would have tried
that code and found it to work...
You may have to get the result of (select id from countries w
[snip]
how do I make a query that does this?
update tbl_a set location_code=0 where tbl_a.country_id = (select id
from
countries where has_zones=0)
[/snip]
Read about multi-table updates here http://www.mysql.com/update
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mys
>Note that this error list is for the Linux version (parts differs for
>another OS). More error descriptions can be found in the header files.
>(forgot currently which ones). If you search the forum for error codes and
>my name than you will find the info (roughly a year+ old)
The typical pla
> > > As you have noticed - you cannot.
> > >
> > > So, all you can do is creating a MyISAM table and copying
> > > the column contents.
> >
> > Are you suggesting to have a full copy of the table in MyISAM format?
>
> No, only the BLOB/TEXT columns need to be moved to MyISAM.
>
> >
> > Is there a
how do I make a query that does this?
update tbl_a set location_code=0 where tbl_a.country_id = (select id from
countries where has_zones=0)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
This may be of use to you:
http://dev.mysql.com/doc/mysql/en/table-size.html
It appears that there is no limit in MySQL itself, but maybe in the
unlying operating system.
Frank Denis (Jedi/Sector One) wrote:
On Fri, Jan 28, 2005 at 04:00:24PM +, Mat wrote:
What Operating System are you run
I've scoured the MySQL manuals... does anyone know if there's any
way to log the amount of data that individual queries produce? Eg:
like bytes transferred in a web server log?
- John
--
---
John May : President
On Fri, 28 Jan 2005, Santino wrote:
At 15:50 -0800 27-01-2005, cristopher pierson ewing wrote:
Shawn,
Okay, it turns out that I can solve my problem by reordering the elements
of the WHERE clause at the end of the query I sent before. I've gotten good
results with the following version (it breaks
symbulos partners <[EMAIL PROTECTED]> wrote on 01/28/2005 11:14:00 AM:
> On Friday 28 Jan 2005 15:41, you wrote:
> > As you have noticed - you cannot.
> >
> > So, all you can do is creating a MyISAM table and copying
> > the column contents.
>
> Are you suggesting to have a full copy of the table
On Fri, Jan 28, 2005 at 04:00:24PM +, Mat wrote:
> What Operating System are you running this on?
Linux 2.6, 64 bits.
MySQL 4.1.9.
> Also, is there anything in the errorlog?
Nothing, but as soon as I restart the server, it enters a strange state
where all slots are full with unauthenti
What Operating System are you running this on? Also, is there anything in the
errorlog?
Is there a limit on the size of .MYI files?
I have a database that worked flawlessly until today. I can't
restart it,
it immediately freezes.
I noticed that the .MYI file of a table has reached e
On Friday 28 Jan 2005 15:41, you wrote:
> As you have noticed - you cannot.
>
> So, all you can do is creating a MyISAM table and copying
> the column contents.
Are you suggesting to have a full copy of the table in MyISAM format?
Is there any other workaround? The reason because we are using Inn
Hello,
> I would need to index FULLTEXT two columns in a InnoDB table. How do you
solve
> the problem?
As you have noticed - you cannot.
So, all you can do is creating a MyISAM table and copying
the column contents.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase
Hello.
In my opinion, you should use SERIALIZABLE transaction isolation level or
SELECT ... LOCK IN SHARE MODE.
"?ngelo M. Rigo" <[EMAIL PROTECTED]> wrote:
> [-- text/plain, encoding 8bit, charset: iso-8859-1, 25 lines --]
>
> Hi
>
> I am using innodb tables to implement transactio
Dear friends,
I would need to index FULLTEXT two columns in a InnoDB table. How do you solve
the problem?
--
symbulos partners
-.-
symbulos - ethical services for your organisation
http://www.symbulos.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscr
Hello,
I am clueless of this query, becaouse it's very slow - between 30 and
60 seconds:
mysql> SELECT DISTINCT o.id AS id, o.orderid AS orderid, o.serialid AS
serialid
-> FROM orders o
-> LEFT JOIN editor e1 ON o.createdby = e1.id
-> LEFT JOIN editor e2 ON o.changedby = e2.id
-> L
Thanks,
That was pretty easy...
And, thank you mysql!
;-)
Yves
On Fri, 28 Jan 2005 08:14:37 -0600, Tom Crimmins <[EMAIL PROTECTED]> wrote:
> [snip]
>
> This box recently crashed as a result of a power outage (possible surge, my
> surge protector may have failed)
> The box doesn't boot up
As long as you've only got 1 instance of Mysql and it's on the default port!
Otherwise use one of the MySQL products.
Kevin Cowley
R&D
Tel: 0118 902 9099 (direct line)
Email: [EMAIL PROTECTED]
Web: http://www.alchemetrics.co.uk
> Fabforce's DBDesigner 4 is fantastic...
*
[[snip]]
> Have you looked into using the MySQL Administrator yet? It's a GUI program
> that should do most of what you want (I know it doesn't diagram but it's
> somewhere to start). You will have to search around to find some other
> ER-based GUI administrator tools because I can't think of an
Is there a limit on the size of .MYI files?
I have a database that worked flawlessly until today. I can't restart it,
it immediately freezes.
I noticed that the .MYI file of a table has reached exactly 2 gigs.
May it be related? Is there anything to do in order to recover the data
an
Mike Jennings <[EMAIL PROTECTED]> wrote on 01/28/2005 07:57:06 AM:
> Hello,
>
>I am looking to export a mysql database to a gui type interface.
> Basically i'm looking at a big database and I wanna dump it to a
> webpage/uml where I can add notes on what each table is and what it
> does, e
Hello.
The mysql program uses user sebyte and password for user ddj.
When mysql starts it calls for load_defaults(), which put
the arguments from your config file before the command line options
that you specified. And than uses the last given argument. It looks like
mysql was invoked with
Hello.
>I am not sure if the key is only built on a single col ( I added
Use SHOW INDEX or SHOW CREATE TABLE statements. See:
http://dev.mysql.com/doc/mysql/en/show-index.html
http://dev.mysql.com/doc/mysql/en/show-create-table.html
I think you should send your query, the output of
Hello.
>/usr/local/mysql/libexec/mysqld: ready for connections.
> Version: '4.0.23' socket: '/tmp/mysql.sock' port: 3306 Source
> distribution
> 050125 15:58:44 mysqld restarted
The mysqld_safe script writes to error log that mysqld was restarted.
So I don't see any useful in erro
Hello.
> Q2. I doodled around a little bit with the SET command and I was able to
> change the session version of the max_allowed_packet server parameter.
Both the client and the server have their own max_allowed_packet variable,
so if you want to handle big packets, you must increase this
Hello.
Not enough information to make a conclusion. Please, send us information
about MySQL and operating system versions. What value does your environment
variable TMPDIR have?
[snip]
I am having a problem starting up mysql.
I am unable to run the mysql_install_db routine. When I run it
Hello.
Your syntax shouldn't work in the MySQL server (comas at the end of CREATE TABLE
statments). What type of storage engine do you use? You can see it with the
following
statement:
SHOW CREATE TABLE MENU_GROUP;
[snip]
DROP DATABASE builder2;
GRANT ALL PRIVILEGES ON *.* TO [EM
Hello.
You can change it by using the --default-storage-engine or --default-table-type
server startup option, or by setting the storage_engine or table_type system
variable.
See:
http://dev.mysql.com/doc/mysql/en/storage-engines.html
symbulos partners <[EMAIL PROTECTED]> wrote:
>
Thanks! That makes senses. And once I chmod as instructed, I can create
directory in the mysql folder.
Tom Crimmins wrote:
[snip]
How can find the user mysql password? The hosting company which I got the
dedicated server package from set that up of course. Should I go to phpAdmin
and reset that p
[snip]
This box recently crashed as a result of a power outage (possible surge, my
surge protector may have failed)
The box doesn't boot up, but the HD is ok... all my docs are there..
So, I'm wondering if it's somehow possible to get the MySQL 3 files and
recreate them on another box we have
[snip]
How can find the user mysql password? The hosting company which I got the
dedicated server package from set that up of course. Should I go to phpAdmin
and reset that password so that I know what it is from now on?
[/snip]
The mysql linux user should not be able to login interactivly, just
On the Pearson Vue website, two MySQL Core exams are listed: 001-002 and
001-003. Which do I choose for the 4.0 exam before February 1?
--
Vincent Williams
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
This was sent to me, off list. I am just forwarding it to it's original
targets. -- Shawn
- Forwarded by Shawn Green ([EMAIL PROTECTED]) on 01/28/2005 09:00 AM
-
Kyle Elmblade <[EMAIL PROTECTED]> wrote on 01/27/2005 04:59:28 PM:
> Hello Simon,
>
> I ran accross the post below in the M
Hello,
I have a server here that ran MySQL 3.23.x
This box recently crashed as a result of a power outage (possible
surge, my surge protector may have failed)
The box doesn't boot up, but the HD is ok... all my docs are there..
So, I'm wondering if it's somehow possible to get the MySQ
I was told to try this:
chown mysql.mysql -R /var/lib/mysql
chmod 750 -R /var/lib/mysql
and it worked afterward. So that's the datadir permission problem.
How can find the user mysql password? The hosting company which I got
the dedicated server package from set that up of course. Should I go to
default-storage-engine=INNODB in [mysqld] section of the config file will make
InnoDB default for a server.
> -Original Message-
> From: symbulos partners [mailto:[EMAIL PROTECTED]
> Sent: Friday, January 28, 2005 8:03 AM
> To: mysql@lists.mysql.com
> Subject: InnoDB engine as default for
The problem isn't really if the database fits into RAM, it's if your
index does, if your searches use the index. Databases can easily
outgrow even the maximum RAM you could install on even a high end
machine. So while it's ideal to fit your database in RAM, it's not
always feasible. Your first
Hello,
I am looking to export a mysql database to a gui type interface.
Basically i'm looking at a big database and I wanna dump it to a
webpage/uml where I can add notes on what each table is and what it
does, even just dumping to a webpage would be fine cause i can edit
from there.
is ther
Hi MySQL ppl,
I recently ran into some problems with uploading large files through a
HTML form and then putting these files into a MySQL BLOB field. I read
about the server parameter max_allowed_packet which turned out to be the
problem.
My script is going to be redistributed to it needs to run
Hi
I am using innodb tables to implement transactions on my system
here is the pseudo code:
SET AUTOCOMMIT = 0
BEGIN
if(erro){
ROLLBACK
} else {
COMMIT
}
When i do commit a transaction and list the data im my web aplication the
records are not acurate .
How can i get the rigth data ?
[snip]
...big...
[/snip]
We are running OpenBSD servers utilizing a dual Xeon processor
architecture with a 1TB RAID. We have a database using MyISAM
tables...here are some stats from phpMyAdmin on one database on this
server --
20 table(s) Sum 347,092,085 --149.6 GB
Two tablea in this
Dear friend,
is it possible to select the InnoDB engine as default for an entire database?
What we want is to create all InnoDB tables, without using the declaration
type=innodb.
--
symbulos partners
-.-
symbulos - ethical services for your organisation
http://www.symbulos.com
--
MySQL Genera
Hi
I didn´t find it in the documentation
I would like to know if the collumn time in the command show full processlist
show the seconds the query is taking to execute!?
Thank´s in advance
-
Yahoo! Acesso Grátis - Internet rápida e grátis. In
I accidently deleted a number of data records (83 records) from a table
using 'delete' SQL command. I want to recover the deleted data. I
checked the table with 'myisamchk'. the result is as follows.
myisamchk seitext.MYI
Checking MyISAM file: seitext.MYI
Data records: 7 Deleted blo
It looks like the default thread stack is not large enough for MySQL 4.1
on Linux running on AMD64.
With NPTL, the database quickly crashed after a few hours of heavy load
(segv).
Without NTPL, I believed it wasn't the case. But after one week mysqld
process were stuck in a strange state.
At 15:50 -0800 27-01-2005, cristopher pierson ewing wrote:
Shawn,
Okay, it turns out that I can solve my problem by reordering the
elements of the WHERE clause at the end of the query I sent before.
I've gotten good results with the following version (it breaks all
the fields in the Fulltext sea
Jacob Friis Larsen <[EMAIL PROTECTED]> wrote on 28/01/2005 11:23:46:
> We have a table that grow by 200MB each day.
>
> Should we put data in different tables or is one big table just as fast?
> We will for new data do select, update and insert and for old data
> only select.
It is not possible
We have a table that grow by 200MB each day.
Should we put data in different tables or is one big table just as fast?
We will for new data do select, update and insert and for old data only select.
Any help would be great.
Thanks,
Jacob
--
MySQL General Mailing List
For list archives: http://li
The maximum single file size of a system is as follows,
*Operating System* *File-size Limit*
Linux 2.2-Intel 32-bit 2GB (LFS: 4GB)
Linux 2.4 (using ext3 filesystem) 4TB
Solaris 9/1016TB
NetWare w/NSS filesystem8TB
win32 w/ FAT/FAT32 2GB/4GB
win32 w/ NTFS 2TB (possibly
Ian Sales (DBA) wrote:
Kevin A. Burton wrote:
Define DoS?
- Denial of Service...
ug... Thats not what I meant... I mean what type of behavior were you
noticing? Just all connections being occupied on the server?
Kevin
--
Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an
Kevin A. Burton wrote:
Jason J. W. Williams wrote:
Has anyone ever had a problem with Alteon load balancers leaving the
MySQL connections half open? After about a minute of heavy use the
Alteon has completely DoS'd our MySQL servers. I know we must be doing
something wrong...just not sure what. Any
Misao schrieb:
Our production databases here are really growing and getting to be rather
big. The question on our minds is; when is a database or table just too big?
We have a few 20-30GB-InnoDB-Tables (growing) without any problems
(mysql 4.1.5gamma).
The limits of mysql are somewhere in the t
Im currently setting up a LAMP server which will be accessed by (a lot of)
students for various courses.
To handle user accounts I use PAM to authenticate ftp-logins against an LDAP
server.
Additionally, I'm looking for a way to authenticate MySQL users against PAM
(or LDAP directly) too, so I
75 matches
Mail list logo
