A query which is constantly being run takes about 3 seconds when not cached,
and I was wondering if there were any way to speed this up. There are
several tables being joined and sorted by latest date with a LIMIT of 10.
All fields being joined by are indexed. So I'm not sure what else I can do.
Jonathan,
I'm not exactly sure what you want to do..
Do you want to identify the entries in the table where the email addresses
are the same as another entry but the name and address details differ...
or.. do you want to find entries where the name and address information is
the same but email a
Lee,
UPADTE table SET description = REPLACE(description, '\r\n', '');
should work...
REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string from_str replaced
by the string to_str. mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
Th
On Thu, 08 Jul 2004 21:12:01 -0400, "leegold" <[EMAIL PROTECTED]>
said:
> I have a situation were a table field item has a
> situation like: \r\ndata\r\n. So an item's got
> line breaks in there with it in the field and
> it's screwing things up for me. How do I do a global
> clean-up of this and
Hi all.
I just tried adding a foreign key constraint, and crashed MySQL ( 4.0.18 ).
I tried it again and crashed it again :(
The SQL I'm using is:
---
alter table Leads
add foreign key fk_LeadNo ( LeadNo ) references Prospects ( LeadNo );
---
The tables involved:
`Prospects` (
`LeadNo` mediumint(8
Hi all,
I am working on a search result query in which the user types a keyword and
the top 3 results for each category are displayed with 24 or so total results
on the page. The database is large (8 GB) and the table in which the fulltext
search occurs has 3 million rows.
Tables:
product
cate
Nickolai,
this very much sounds like a hardware fault. No MySQL or InnoDB bug should
be able to freeze the WHOLE operating system. And SELECT ... INTO OUTFILE
... is a very basic operation in the database.
Regards,
Heikki
- Original Message -
From: ""Nickolai Nielsen"" <[EMAIL PROTECTE
Daniel,
I have a few things that you might want to check (in order of most likely):
1. What is the maximum number of file descriptors set?
If this is too low you will be running into problems. Here is a paste from
the MySQL manual that is very helpful:
For more information you can read the full m
Jack Coxen wrote:
> If you database contains time-based data you could age out old records. I
> only need to keep data for 6 months so I run a nightly script to delete any
> records more than 6 months old. And before anyone asks...yes, I also run
> another script to ANALYZE/OPTIMIZE my tables.
>
I have a situation were a table field item has a
situation like: \r\ndata\r\n. So an item's got
line breaks in there with it in the field and
it's screwing things up for me. How do I do a global
clean-up of this and remove all \r and \n for
all of that col. in my table? If it was flat-file
I'd run
On Thu, Jul 08, 2004 at 09:23:17PM +0300, Egor Egorov wrote:
> Jeremy Zawodny <[EMAIL PROTECTED]> wrote:
>
> >> >> > So I haven't really done much to optimize things, as this seems like a
> >> >> > fairly light load. I'm running 4.0.20 on FreeBSD 4.8 (port build w/Linux
> >> >> > Threads).
> >> >
Hi David, the link you provided is quite interesting. Is such
database(translucent database) actually exist? Or is it just a concept?
Thanks
"David Dick" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> G'day Sarah,
>
> Pater Wayner has apparently written a book on the subject of ho
On Thursday 08 July 2004 02:35 pm, Chip Wiegand said something like:
> I was sent an excel file from a remote office, and need to put the data
> into a mysql database to be displayed on our web site. I removed a few
> lines of fluff from the excel file and saved it as .csv (using .csv
> (ms-dos)).
Jeffrey,
That actually does a good job, thank you. The reason I am doing this is
so that I can delete the duplicates. However, the query you gave me
shows me the first match of a duplicate. Is there a way to show the
last match of a duplicate instead? I am wanting to save the first
entries but
try:
lines terminated by '\r\n';
> -Original Message-
> From: Chip Wiegand [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 08, 2004 3:36 PM
> To: [EMAIL PROTECTED]
> Subject: problem importing .csv (excel format) into mysql
>
>
> I was sent an excel file from a remote office, and need
I was sent an excel file from a remote office, and need to put the data
into a mysql database to be displayed on our web site. I removed a few
lines of fluff from the excel file and saved it as .csv (using .csv
(ms-dos)). When I try to import the file it gives me a duplicate entry for
key 1 err
Hi,
I tried but didn't work. Here is my script:
#
# @Name : NTT_dbcreator.2004-06-07.v1-001.andre.sql
# @DESCRIPTION : NTT Database Creator
# @CREATED : Jun 07, 2004 by Andre Matos - [EMAIL PROTECTED]
# @VERSIO
G'day Sarah,
Pater Wayner has apparently written a book on the subject of how to
store and use information in a database that will be of limited use to
attackers. His website is below, with an oreilly article describing the
book. Haven't read it myself yet.
http://www.wayner.org/books/td/
htt
I have a table with several keys. When I try to delete anything from
this table, I get data corruption and have to repair it with myisamchk.
Selects, updates work fine.
Here's the create table statement:
CREATE TABLE `postsearch` (
`postId` int(11) NOT NULL default '0',
`weblogId` int(11)
> I am trying to get rid of duplicate user info entries in a database. I
> am assuming that the email address is unique but other information, like
> first name, last name, address, etc are not. The "email" field is not a
> unique field in the database. I was trying something like the
> follo
I am trying to get rid of duplicate user info entries in a database. I
am assuming that the email address is unique but other information, like
first name, last name, address, etc are not. The "email" field is not a
unique field in the database. I was trying something like the
following, but not
Thanks for the hint - i found it on MySQL.com 's documentation on ALTER
table
---
ALTER TABLE works by making a temporary copy of the original table. The
alteration is performed on the copy, then the original table is deleted
and the new one is renamed. .
--
Of course in th
I think you may need to use a scripted approach in order to avoid
overflowing your disk.
1. Create a new empty table with the correct structure.
2. Copy some "block" of records (like those with id values between 1 and
1000) into the new table
3. Delete the same records (the ones you just duplicat
I've got two Linux x86 servers: master and slave, both with MySQL 4.0.20
from the same package. On the master I do:
SET CHARACTER SET cp1250_latin2;
UPDATE `my_tab` SET `my_field`='my_cp1250_string';
and got this in both master's and slave's log.
On master the data is properly translated into lati
Hello,
I would like to mention a potential bug with using the silent install
feature of mySQL with the mysql-4.0.20d-win.zip for windows. I have
WindowsXP Professional for an OS. I was setting up mySQL to generate a
setup.iss file. I found that the setup.iss file that was generated did
not have
Thanks Shawn
I did think about this approach due to the disk limitation.
I still want to know, if
"ALTER TABLE MODIFY ..."
can do the same for me and if it has imitations or if anyone has any
suggestions of why it should not be done, especially when I am dealing
with Gigs of data (more than 43
Hello All
I have a table (about 72 GB of data).
When the table was created, not much attention was paid to the design
aspect.
For ex.,
id int(10) should only by tinyint(3)
name char(35) should just be char(20)
and many more like that.
I do not have enough space on the hard drive to create a te
On Thu, 8 Jul 2004, Derek Shaw wrote:
> The subject warning message has come up in several different threads
> over the past few months. It also appears in newsgroups and other web
> sites. It does not appear to be related to architecture, kernel version
>
> No solution, workaround, nor indeed a
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
MySQL Connector/J 3.1.3, a new version of the Type-IV all-Java JDBC
driver for MySQL has been released.
Version 3.1.3 is the first BETA release of the 3.1 series that is
suitable for use with either MySQL-4.1 or MySQL-5.0 (and is required
when using
Hi Kate,
You use two different verbs in the explanation of your problem, update and
insert, and I am not at all sure if the problem is with your statement or
with your intent.
UPDATE is the SQL command to *change* values in existing data
INSERT is the SQL command to *add* new data to a table
So
I just tried installing 4.1.3 on my development machine today. To my
dismay, I couldn't get it to start properly. I was upgrading from 4.1.2,
which I installed identically to the procedure below.
On to the actual problem. When I started MySQL the first time I used
'/etc/init.d/mysql start'.
The subject warning message has come up in several different threads
over the past few months. It also appears in newsgroups and other web
sites. It does not appear to be related to architecture, kernel version
No solution, workaround, nor indeed any explanation of its implications
has been gi
It sounds to me that you need only 1 database on a secure server, behind a
secured website. Each data entry operator (the members of your family?)
would need to either access data entry pages on your site (via HTTPS or SSH
or some other encrypted channel) or the database directly through SSH. If
y
Hi list, I'm having the following problem when I tried to connect to MySQL
server through terminal mysql client it displayed "Can't create thread errno
(11)" , I worked around the variables thread_stack and thread_cache_size,
its initial value was 64KB for thread_stack and 20 for thread_cache_size;
Hi,
I have a table called Bookings which holds start times and end times for
appointments, these are held in Booking_Start_Date and Booking_End_Date. I
have a page on my site that runs a query to produce a grid to show
availiability per day for the next ten days for each user of the system.
Use
Bruce,
>From your one of your other threads, your object hierarchy looks like:
state
university name
school name (school of medicine/engineering/accounting/etc..)
dept name
course/class name
class section
class day/time
instructor
To me, it seems
[EMAIL PROTECTED] wrote:
> We have installed a mysql database on our our server. The data and log
> files are stored on an external SCSI disk array which is connected to
> our server using FC cable connected to SCSI port. The mysql process is
> running on the local machine but the data and logs a
Jeremy Zawodny <[EMAIL PROTECTED]> wrote:
>> >> > So I haven't really done much to optimize things, as this seems like a
>> >> > fairly light load. I'm running 4.0.20 on FreeBSD 4.8 (port build w/Linux
>> >> > Threads).
>> >>
>> >> Reliability and performance is not what you should expect to fin
Thank you very much Lachlan and SpamVortex! I appreciate the help!
Jonathan
>>>[EMAIL PROTECTED] 07/07 6:59 pm >>>
You can do it one of two ways.. Either you can do a
"self join" like the following:
select t1.userid
from answers t1,
answers t2
where t1.qid = 5
and lower(t1.ans
Assume DB1
tab1
tab2
...
Do
$mysqldump -u user DB1 > DB1.sql
Remember if you have a password set for this user (which ideally should
be) use the -p option.
$mysql -u user -e "create database DB2"
This line will create the new database
$mysql -u root DB2 < DB1.sql
This will put all your tables
In the last episode (Jul 08), mac said:
> to keep you up to date:
>
> we tested with a simple dd-read on different machines and mount-points:
>
> 400MB file (created with "dd if=/dev/urandom of=file100Mb bs=512
> count=80" on the intel-linux-box):
BTW - a 512-byte blocksize is basically test
I'm trying to retrieve a certain ID row from a table and use Update to
insert it into a specific row of another table. I'm not getting any syntax
error but I'm also not getting the update to work
UPDATE linksProjects LEFT JOIN categories ON linksProjects.categoryId =
categories.categoryId SET lin
Hmm...that seems like a bug...if those commas are inside quotes, they should
not be interpreted as field delimiters. You might want to check if there are
any stray quotes elsewhere on the line that are messing up the
interpretation. If not, then I would think that to be a bug.
j- k-
I was wondering if it is possible to create a secure database system
using MySQL/PHP combination?
I have the following in mind:
I wanted to store all my( and my brothers and sisters) important
document
information such as birth certificate, SSN, passport number, travel
documents, insurance(car, h
Hi,
Will mysqldump import the entire DB? Including tables and ALL
the data in the tables?
I assume I must "create" the DB on the target system *then* load
or run the file created by mysqldump? Is there a tutorial on
this - I get the feeling the man page does not have the
"soup to nuts" solution.
not sure if it's what you're looking for...
but you can always do mysql>"system -uuser1 -ppassword mailto:[EMAIL PROTECTED]
Sent: Thursday, July 08, 2004 10:21 AM
To: [EMAIL PROTECTED]
Subject: Scripts
Hi List,
Let's suppose that I have a script to create one database (tables,
indexes, etc).
H
shawn...
thanks for the response...
here's my basic situation. i'm creating an app that's going to have
university class schedules from a number of universities...
as i get down to the table that will house the actual class schedule
information, i'm looking at what informational fields need to b
Have you tried writing the script using the source command?
drop database;
create database;
source populate_db;
-Original Message-
From: Andre MATOS
To: [EMAIL PROTECTED]
Sent: 7/8/04 12:20 PM
Subject: Scripts
Hi List,
Let's suppose that I have a script to create one database (table
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
hi there,
to keep you up to date:
we tested with a simple dd-read on different machines and mount-points:
400MB file (created with "dd if=/dev/urandom of=file100Mb bs=512
count=80" on the intel-linux-box):
reading the file with dd to /dev/null on
hi
this SQL frezes the system:
SELECT * FROM journal into OUTFILE 'c:/Backup/current/journal.asc' FIELDS
terminated by '|' LINES terminated by '\r\n'
this started after the table was converted to InnoDB, usualy it runs normaly
the first time, but on 2-5 run it frezes the system so i have to reboo
I appreciated your first email, regardless of whether or not I was able to
make it work. Any response is welcome! I am having some trouble with the
final result using the CVS file: some of the content uses commas i.e.
"Chinchilla Zúñiga, Guillermo" and gets split up between two fields. This
who
Hi List,
Let's suppose that I have a script to create one database (tables,
indexes, etc).
How can I call another script from this script? For example:
DROP DATABASE test;
CREATE DATABASE test;
(here call another script)
back to the previous script
Thanks.
Andre
--
Andre Matos
[EMAIL PR
Bruce,
It all depends on what you are storing in foo1 and foo2.
For a one-to-many relationship, you need to have that ID column in your
child table to reference the parent record.
The "classic" example of a one-to-many relationship is Companies to
Employees. In the real world there are som
ps...
i'm using mysql v12.22 d4.0.20 with berkely tables for the transaction
processing.
thanks...
hi...
i have an issue/prob and i'm trying to figure out the best approach...
i have multiple tables foo1, foo2
foo1:foo2:
- name - name
- id - foo1_id
- id
hi...
i have an issue/prob and i'm trying to figure out the best approach...
i have multiple tables foo1, foo2
foo1:foo2:
- name - name
- id - foo1_id
- id
- unique key (id, name, foo1_id)
my question: does mysql provide a way to do a unique k
You created a nearly infinite loop!! Relax, it's a mistake we ALL have
made. ;-D
You start you loop at 1000 and keep increasing your numbers by 1 until they
become less than 10 (which should never happen). Eventually you will reach
the max value for @x's datatype then it should fail with an error
On Thu, 8 Jul 2004 [EMAIL PROTECTED] wrote:
> I am happy even if mysqladmin reports that mysqladmin shutdown has
> failed instead of hanging so that I can kill/stop the process using
> cruder methods.
I am about 99.9% sure that all gentle methods of stopping mysqld will
fail; it's had all the fil
I'm trying to call this simple store procedure on my XP with "mysql Ver
14.3 Distrib 5.0.0-alpha, for Win95/Win98 (i32)", server is
5.0.0-alpha-max-debug-log:
CREATE PROCEDURE test_repeat ()
BEGIN
SET @x = 1000;
REPEAT
SET @x = @x + 1;
UNTIL @x < 10
EN
Look for a substring beginning with 'FOREIGN KEY' in the result of 'SHOW
CREATE TABLE tblname'.
PB
- Original Message -
From: Tom Roos
To: [EMAIL PROTECTED]
Sent: Thursday, July 08, 2004 10:05 AM
Subject: foreign keys..
hi
how does 1 know if a foreign key is defined?
Sanjay Arora wrote:
On Thu, 2004-07-08 at 00:19, Paul DuBois wrote:
At 22:34 +0530 7/7/04, Sanjay Arora wrote:
I am using Mysql on RH Linux 9. I am getting the following error.
Command and result are given below alongwith \s output for diagnostic
purposes.
I have created a dns database and am tryin
Hi!
On Jul 06, Vincent Bouret wrote:
> Hi,
>
> I got the following values:
> key_buffer_size = 256M
> myisam max extra sort file size = 8000M
> myisam max sort file size = 8000M
> myisam sort buffer size = 128M
>
> But that big table (MYD = 2397 MB), rows = 5 355 866 still won't index
> in full
Tom Roos wrote:
hi
how does 1 know if a foreign key is defined? describe tablename and show index from
tablename dont sufice
tks
SHOW CREATE TABLE tablename;
or
SHOW TABLE STATUS LIKE 'tablename';
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubsc
James Raff wrote:
I have MYSQL 3.23.32 on a Cobalt 550 platform. I see from the FAQ's that sub
That's a *very* old version. The latest 3.23 is 3.23.58. The current
production release is 4.0.20. You should consider upgrading.
queries will not work on MYSQL < 4.1. Is there a way to use JOIN stat
hi
how does 1 know if a foreign key is defined? describe tablename and show index from
tablename dont sufice
tks
Disclaimer
http://www.shoprite.co.za/disclaimer.html
Hi folks,
Is there a way to speed up count(*) operations on InnoDB tables?
I have a table with roughly 18 million rows in it which has the
following
structure:
CREATE TABLE `tbl_test` (
`col_serial` varchar(32) default NULL,
UNIQUE KEY `col_serial` (`col_serial`)
) TYPE=InnoDB;
+--
I have MYSQL 3.23.32 on a Cobalt 550 platform. I see from the FAQ's that sub
queries will not work on MYSQL < 4.1. Is there a way to use JOIN statements
instead or do these fail too.
eg: SELECT some_ID from someTable NOT IN (Select some_ID from ANOTHERTABLE)
Or do I have to redesign my website!
You will need to use the "full" INSERT...SELECT syntax:
INSERT ()
SELECT
FROM
For auto_increment columns, I assume that your housekeep_ID columns is one
of those, you do not include it in either list. For each column in your
INSERT clause you must provide a value for that column with your
I have ben trying to install MySQL v4 for Linux on a machine that had a
previous v3 installed badly. I have removed all of the old v3 or so I
thought. When I try and put the new v4 on it says that it cannot do this as
there is still a v4 on the machine. What can I do to overide?
Michael Johnson
On 8 Jul 2004 at 7:28, Victor Pendleton wrote:
> Does the older MyODBC version properly work? I have run into some oddities
> with the latest MyODBC driver.
Forgot to mention...
Same result with
2.50.33.00
Ian
--
>
> -Original Message-
> From: Ian Gibbons
> To: [EMAIL PROTECTED]
>
Does the older MyODBC version properly work? I have run into some oddities
with the latest MyODBC driver.
-Original Message-
From: Ian Gibbons
To: [EMAIL PROTECTED]
Sent: 7/8/04 7:08 AM
Subject: 4.1.3-beta-nt-log Select Count(1) returns as unknown type in
ASP/vbscript
Hi,
Whilst testing
Are you able to communicate with the database server at all. Login, query,
etc.?
-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 7/7/04 11:38 PM
Subject: mysqladmin shutdown command hangs
Hello:
We are using mysql 4.0.17 on Linux
We have installed a mysql data
Probably not. At this point, I'd say his options are limited to either
deleting data to free up some space or getting a bigger hard drive. Unless
his situation is like mine and he only needs to keep the data for a certain
period of time, the bigger hard drive is definitely the way to go. And
sin
Hi,
Whilst testing some asp websites with Mysql Beta 4.1.3-nt-log I have noticed that
doing a
Select Count(1) as var FROM tableName
returns as an unknown variable type in vbscript.
Previously with the 3 / 4.0 version it returned as a long.
I am using the latest MyODBC 3.51.08.00
Is
Hi,
Add
skip-log-warnings
in your my.cnf. log-warnings seems to have been enabled by default since
4.0.19.
Regards,
Jocelyn
- Original Message -
From: "Don MacAskill" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 08, 2004 11:29 AM
Subject: Aborted connection error
Hi all,
Thanks for the help.
So I can "Insert into archive_table select * from active_table where
condition='true'", followed by a delete from active_table...
If my archive_tabler is nearly exactly the same but has two more fields
(housekeep_id, and now()), how do I modify the above statement to
I just switched from 4.0.18-max to 4.0.20-max on AMD64 and I'm getting
tons of these in my error log:
040708 2:24:12 Aborted connection 65531 to db: 'db' user: 'user' host:
`10.1.1.27' (Got an error reading communication packets)
When I switch back to 4.0.18-max, they go away.
These are the s
On Thursday 08 July 2004 09:19, Louie Miranda might have typed:
>
> As you can see, it updates the whole "random" field on the table. What
> i would like to do is, update each and everyone of it. using a random
> program, which i have. Its on my first email.
>
> > ###
> > #!/bin/sh -x
> > for ran
Btw, here are the sample database i have.
mysql> select * from dump;
+-+--+--+
| countid | random | name |
+-+--+--+
| 1 | theeyahs | randpass |
| 2 | ciuwaise | randpass |
| 3 | ciuwaise | randpass |
| 4 | ciuwaise | ra
Im trying to work on a program that can update each field and insert a
random value on it. But when i run this program it only updates the
whole random field.
Can anyone help me? Im pretty clueless how to work this right. :/
###
#!/bin/sh -x
for random in `pwgen --no-capitalize -N1`
do
sleep 0
do
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 05.07.2004, at 15:51, JOUANNET, Rodolphe wrote:
do you use innodb or myisam tables ?
myisam tables.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (Darwin)
iD8DBQFA7P39vkHn/oGTPXURAs+EAJ9dV7ZOQWolUHQcO/CF9UHTx/XhAgCffhfY
73za73KJJjTaGsz1vHxVoqg=
On Thu, 2004-07-08 at 00:19, Paul DuBois wrote:
> At 22:34 +0530 7/7/04, Sanjay Arora wrote:
> >I am using Mysql on RH Linux 9. I am getting the following error.
> >Command and result are given below alongwith \s output for diagnostic
> >purposes.
> >
> >I have created a dns database and am trying
81 matches
Mail list logo
