New to Dates - Plain English Please

[David Blomstrom]

I  haven't worked with dates yet and wondered if
someone could give me an overview in plain English.

At the moment, I'm working on a table with a column of
dates in this format:

March 2, 2003
July 7, 2004

If I understand the Manual, the correct format for
these dates in a MySQL table would be like this:

03-02-2003
07-07-2004

Am I right?

When I create a DATE field, do I need to designate a
Length/Value, Attribute, etc.? If so, how long is
07-07-2004 - 8 characters, or ten?

And is it also correct that you can manipulate dates
in this format to display in other formats? For
example, 03-02-2003 could be displayed as March 2,
2003?

I THINK this is what the Manual is talking about
here...

DATE_FORMAT(date,format)
Formats the date value according to the format
string. The following specifiers may be used in the
format string:
Specifier   Description
%a  Abbreviated weekday name (Sun..Sat)
%b  Abbreviated month name (Jan..Dec)
%c  Month, numeric (0..12)
%D  Day of the month with English suffix (0th,
1st, 2nd, 3rd, ...)
%d  Day of the month, numeric (00..31)
%e  Day of the month, numeric (0..31)

If so, this must be something you do with PHP, right?

I guess the most important thingn I need to know is
what format to put my dates in. If 07-07-2004 is the
standard format, and if I can print/display that in
various ways, then I can figure the rest out when I
get to it.

Thanks.



__
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How do you deal with URL's?

[Sergio Salvi]

On Mon, 21 Jun 2004, David Blomstrom wrote:

> Sergio Salvi wrote:
> 

[...]

> 
> Separate data from how it's displayed. I mean, create
> a table called 
> "states" with the fields "state_id", "state_name" and
> "state_url". Put 
> the data in the according field:
> 
> state_id state_name state_url
> 1 Alabama http://www.alabama.gov
> 2 Washington http://access.wa.gov
> and so on
> 
> OK, I see. But I assume you mean  than http without the beginning tag - or do you insert
> that with PHP? It just occurred to me that I probably
> don't need to put  in the database, because I
> probably add that with PHP somehow.

I would add the "a href" HTML tag in the application and store only the 
URL in the database. Just do not remove the "http://"; and assume in your 
application that every URL starts with that because some of them may be 
"https://";. If you are space usage paranoid (hehe), you could create a 
enum("y","n") field called "use_ssl" and remove the "http://"; from every 
URL, setting the "use_ssl" field to "y" when the URL is a https:// one.

Do not assume you'll always display the URLs in an HTML output. What if 
you decide to send it by plain-text mail? Or output in XML to export it to 
another system?

HTH.

[]s,
SAlvi

> 
> 
> --- Bob Ramsey <[EMAIL PROTECTED]> wrote:
> > Personally, I'd split that into 2 fields.  I think
> > that's a better way 
> > to model the data unless there's something I don't
> > know.
> > 
> > Otherwise, try this:
> > 
> > mysql> select * from url;
> > +---+
> > | url   |
> > +---+
> > | http://www.alabama.gov";>Alabama  |
> > | http://access.wa.gov";>Washington |
> > +---+
> > 2 rows in set (0.00 sec)
> > 
> > mysql> select substring(url,locate('">',url)+2, 
> > char_length(url)-locate('">',url
> > )-5) as state from url;
> > ++
> > | state  |
> > ++
> > | Alabama|
> > | Washington |
> > ++
> > 2 rows in set (0.00 sec)
> > 
> > mysql>
> > 
> > 
> > What I had to to was to have mysql take the string:
> > 
> > http://www.alabama.gov";>Alabama
> > 
> > and give me the parts between "> and .  First, I
> > had to find the 
> > position of "> and then add 2 to it.  The substring
> > function in mysql 
> > takes the parameters string, starting_position, and
> > length.  Using 
> > locate, I got the starting postion and added 2 to
> > it.  For length, I had 
> > to use locate again; locating "> gives me the
> > position of the " in ">.  
> > Subtracting 5 gives me the right length after
> > discounting the  and 
> > the 2 positions I'm off from ">.
> > 
> > 
> > Someone more experienced that I can tell you if
> > there's a more effecient 
> > way.  My inclination would be that for best results,
> > you should split 
> > the field in two and build your webpage like this:
> > 
> > $STATE
> > 
> > Hope this helps.
> 
> I think I understand. Thanks.
> 
> 
> 
>   
>   
> __
> Do you Yahoo!?
> New and Improved Yahoo! Mail - 100MB free storage!
> http://promotions.yahoo.com/new_mail 
> 
> 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysqld dead but subsys locked

[Sara]

I have a dedicated server, which went down without any reason. I am runing a couple of 
phpBB databases there.

Now when I try to access the phpBB from browser, I get the following message.

Warning: Can't connect to local MySQL server through socket 
'/var/lib/mysql/mysql.sock' (2)

Using Shell, I logged in ran the command 'service mysqld status' it showed:

'mysql dead but subsys locked'

tried to 'restart' it, Stopping -> [FAILED], Starting -> [OK]

Tried to reboot the server a couple of times, but no gains, the problem still stands 
there.

Searched the net for possible solutions, and I have tried the following solutions till 
now without any outcome.

1- Try chowning /var/lib/mysql to chown -R mysql.mysql /var/lib/mysql

2- You may want also to remove the lock file from '/var/lock/subsys/' which is 
'mysqld'.After this, restart the mysql daemon, and run mysql.
I tried and removed mysql file there many times, without any outcome.

3- killall -9 mysqld ---> may take a couple of attempts
then... /etc/rc.d/init.d/mysqld restart
mysql should turn over and run fine again.

Tried it and got : mysqld: no processes killed

-

Can anyone help me as what to do next?

Thanks in advance for any help as I am desperately looking for some solution.

Thanks,
Sara.

Re: opposite query

[Michael Stassen]

Bob Lockie wrote:
On 06/21/2004 04:26 PM [EMAIL PROTECTED] spoke:
I don't understand. You want every other record except .what?
By flipping the equality the way you did, you should see all of the 
records where a.id = b.id (regardless of what the b.type value is) where 
a.type was not 'X' which is one reasonably valid solution to the statement "all 
of the rows NOT found by the above query"

The more details you give us the better we can help you. If you can 
give us your SHOW CREATE TABLE statements and possibly some sample data, we 
should be able to get at exactly the data you want to see.

Respectfully,
Shawn Green
drop table a;
create table a (
id int auto_increment,
name varchar(20),
primary key (id)
);
drop table b;
create table b (
id int auto_increment,
a_id int not null,
name char(20),
type char,
primary key (id)
);
insert into a (name) values ("row1");
insert into b (a_id, name, type) values(1, "1b", "X");
insert into b (a_id, name, type) values(1, "2b", "Y");
insert into b (a_id, name, type) values(1, "3b", "Y");
insert into b (a_id, name, type) values(1, "4b", "Z");
insert into a (name) values ("row2");
insert into b (a_id, name, type) values(2, "1a", "X");
insert into b (a_id, name, type) values(2, "2a", "Y");
insert into b (a_id, name, type) values(2, "3a", "Y");
insert into b (a_id, name, type) values(2, "4a", "Z");
insert into a (name) values ("row3");
insert into b (a_id, name, type) values(3, "2a", "Y");
insert into b (a_id, name, type) values(3, "3a", "Y");
insert into b (a_id, name, type) values(3, "4a", "Z");
select a.name from a, b where b.type='X' and a.id=b.a_id;
returns row1 and row1 from a.
Now I need a select that will return row3 but not rows 1 or 2.
In another message, Bob Lockie wrote:
I need to return the a records that have no b records of type 'X' but
there may be b records of other types.
  SELECT a.name
  FROM a LEFT JOIN b
  ON a.id=b.a_id AND b.type='X'
  WHERE b.a_id IS NULL;
+--+
| name |
+--+
| row3 |
+--+
1 row in set (0.01 sec)
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How do you deal with URL's?

[David Blomstrom]

Sergio Salvi wrote:

"Doing the way you're suggesting would create some
problems:

- data redundancy (http://www.alabama.gov
2 Washington http://access.wa.gov
and so on

OK, I see. But I assume you mean  wrote:
> Personally, I'd split that into 2 fields.  I think
> that's a better way 
> to model the data unless there's something I don't
> know.
> 
> Otherwise, try this:
> 
> mysql> select * from url;
> +---+
> | url   |
> +---+
> | http://www.alabama.gov";>Alabama  |
> | http://access.wa.gov";>Washington |
> +---+
> 2 rows in set (0.00 sec)
> 
> mysql> select substring(url,locate('">',url)+2, 
> char_length(url)-locate('">',url
> )-5) as state from url;
> ++
> | state  |
> ++
> | Alabama|
> | Washington |
> ++
> 2 rows in set (0.00 sec)
> 
> mysql>
> 
> 
> What I had to to was to have mysql take the string:
> 
> http://www.alabama.gov";>Alabama
> 
> and give me the parts between "> and .  First, I
> had to find the 
> position of "> and then add 2 to it.  The substring
> function in mysql 
> takes the parameters string, starting_position, and
> length.  Using 
> locate, I got the starting postion and added 2 to
> it.  For length, I had 
> to use locate again; locating "> gives me the
> position of the " in ">.  
> Subtracting 5 gives me the right length after
> discounting the  and 
> the 2 positions I'm off from ">.
> 
> 
> Someone more experienced that I can tell you if
> there's a more effecient 
> way.  My inclination would be that for best results,
> you should split 
> the field in two and build your webpage like this:
> 
> $STATE
> 
> Hope this helps.

I think I understand. Thanks.





__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How do you deal with URL's?

[Sergio Salvi]

On Mon, 21 Jun 2004, David Blomstrom wrote:

> Suppose I have a field with the names of states,
> linked to their home pages:
> 
> http://www.alabama.gov/";>Alabama
> http://access.wa.gov/";>Washington
> 
> If I display this on a webpage, I'll get the names of
> the states, linked to their home pages. But is there a
> simple strategy that will let me to display the names
> UNLINKED on another page, or do I have to create a
> second field that lists simple state names, with no
> URL's?

Doing the way you're suggesting would create some problems:

- data redundancy (http://www.alabama.gov
2 Washington http://access.wa.gov
...and so on

Then you can easily list only state names (ordered by state_name, for 
example), not showing the URL.

[]s,
Salvi.

> 
> Thanks.
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How do you deal with URL's?

[Bob Ramsey]

Personally, I'd split that into 2 fields.  I think that's a better way 
to model the data unless there's something I don't know.

Otherwise, try this:
mysql> select * from url;
+---+
| url   |
+---+
| http://www.alabama.gov";>Alabama  |
| http://access.wa.gov";>Washington |
+---+
2 rows in set (0.00 sec)
mysql> select substring(url,locate('">',url)+2, 
char_length(url)-locate('">',url
)-5) as state from url;
++
| state  |
++
| Alabama|
| Washington |
++
2 rows in set (0.00 sec)

mysql>
What I had to to was to have mysql take the string:
http://www.alabama.gov";>Alabama
and give me the parts between "> and .  First, I had to find the 
position of "> and then add 2 to it.  The substring function in mysql 
takes the parameters string, starting_position, and length.  Using 
locate, I got the starting postion and added 2 to it.  For length, I had 
to use locate again; locating "> gives me the position of the " in ">.  
Subtracting 5 gives me the right length after discounting the  and 
the 2 positions I'm off from ">.

Someone more experienced that I can tell you if there's a more effecient 
way.  My inclination would be that for best results, you should split 
the field in two and build your webpage like this:

$STATE
Hope this helps.
bob
David Blomstrom wrote:
Suppose I have a field with the names of states,
linked to their home pages:
http://www.alabama.gov/";>Alabama
http://access.wa.gov/";>Washington
If I display this on a webpage, I'll get the names of
the states, linked to their home pages. But is there a
simple strategy that will let me to display the names
UNLINKED on another page, or do I have to create a
second field that lists simple state names, with no
URL's?
Thanks.
	
		
__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How do you deal with URL's?

[David Blomstrom]

And here's a follow up question...

When you want to designate URL's or other html tags in
a database table, do you use the same tag containers
you use in your html - < and > - or do (or can) you
use the equivalents, like <

Using <> is kind of tricky, since they're invisible
when I'm working in design view. I make most of my
tables on a webpage, preview them in a browser, then
copy them into a spreadsheet, so that pretty much
rules out invisible elements.



__
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How do you deal with URL's?

[David Blomstrom]

Suppose I have a field with the names of states,
linked to their home pages:

http://www.alabama.gov/";>Alabama
http://access.wa.gov/";>Washington

If I display this on a webpage, I'll get the names of
the states, linked to their home pages. But is there a
simple strategy that will let me to display the names
UNLINKED on another page, or do I have to create a
second field that lists simple state names, with no
URL's?

Thanks.




__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Insert data if not duplicate based on order

[John McCaskey]

I don't think its possible in one query.  One thing you can do is lock
the table when you select the 20 rows and determine whether to do the
insert.  Then unlock when done.  This avoids the concurrency issue you
are having, but it may cause unacceptable perfomance if you have a lot
of queries hitting this table.  Maybe someone else has a better
solution.

John A. McCaskey

-Original Message-
From: Grant Giddens [mailto:[EMAIL PROTECTED] 
Sent: Monday, June 21, 2004 4:59 PM
To: [EMAIL PROTECTED]
Subject: Insert data if not duplicate based on order


Hi,

  I have a table that has many rows.  I want to be
able to insert a new row only if has a unique field of
the last 20 rows when I sort them by date.

Currently before I insert a new row, I will select a
specific field for 20 rows and run a for loop in php
looking for a match with the data I'm getting ready to
insert.  If a match occurs I will skip the insert
command.

This normally works fine, but I have seen occasions
when 2 people visit my website at the same exact time.
 In this case, sometimes I might insert the data twice
because I think I'm spinning in the for loop for 2
site visitors simultaneously.

I know this is bad syntax, but this is what I imagine
a insert query to look like:


INSERT INTO $table (datax, datay, dataz)
   VALUES ('x', 'y', 'z') IF 'x' unique WHEN
ordering by 'z' DESC LIMIT 20

In this example, 'z' is a datetime field and 'x' is a
varchar.  I don't mind duplicate entries in my table,
but I don't want duplicates entries to occur within 20
rows when sorting by date.

Is this possible to do with one INSERT command?  I saw
the IGNORE statment in the mysql manual, but I don't
think it really aplies to what I'm trying to do here.

Any suggestions would be appreciated,
Thanks,
Grant 


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Insert data if not duplicate based on order

[Grant Giddens]

Hi,

  I have a table that has many rows.  I want to be
able to insert a new row only if has a unique field of
the last 20 rows when I sort them by date.

Currently before I insert a new row, I will select a
specific field for 20 rows and run a for loop in php
looking for a match with the data I'm getting ready to
insert.  If a match occurs I will skip the insert
command.

This normally works fine, but I have seen occasions
when 2 people visit my website at the same exact time.
 In this case, sometimes I might insert the data twice
because I think I'm spinning in the for loop for 2
site visitors simultaneously.

I know this is bad syntax, but this is what I imagine
a insert query to look like:


INSERT INTO $table (datax, datay, dataz)
   VALUES ('x', 'y', 'z') IF 'x' unique WHEN
ordering by 'z' DESC LIMIT 20

In this example, 'z' is a datetime field and 'x' is a
varchar.  I don't mind duplicate entries in my table,
but I don't want duplicates entries to occur within 20
rows when sorting by date.

Is this possible to do with one INSERT command?  I saw
the IGNORE statment in the mysql manual, but I don't
think it really aplies to what I'm trying to do here.

Any suggestions would be appreciated,
Thanks,
Grant 


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Adding replica server in a mixed MyISAM/InnoDB multi-master env?

[Tabor J. Wells]

On Thu, Jun 10, 2004 at 12:04:52PM -0400,
Tabor J. Wells <[EMAIL PROTECTED]> is thought to have said:

> I have a fairly complicated and large MySQL installation that I need to add
> a new slave server to and I'm uncertain about the best way to get this done
> with the least risk to my live environment and least downtime of my existing
> masters. I'm hoping that someone here can suggest the best way for me to do
> this.
> 
> Here's the setup:
> 
> ServerA (Redhat 9, MySQL 4.0.16)
>   Replicates 4 dbs from ServerB
>   Replicates 2 dbs to ServerB
> 
> ServerB (RedHat 9, MySQL 4.0.16)
>   Replicates 4 dbs to ServerA
>   Replicates 2 dbs from ServerA
>   Has 19 other dbs
> 
> I need to add ServerC which would contain replicas of all 23 dbs 
> (which is about 60gb of data total). ServerC is a RedHat 7.3 server and
> would be running MySQL 4.0.20.
> 
> The problem is that those dbs contain a mixture of InnoDB and MyISAM tables
> How do I get a consistent snapshot of those dbs and get the replication set
> up?
> 
> Is it safe to just shutdown ServerB, copy all of the mysql data dir (including
> the ibdata files) to ServerC, restart ServerB, and then change the my.cnf of
> ServerC to set a new server-id, master-host, replicate-do-db entries and start 
> ServerC?
> 
> Is there anything else that needs to be done to ensure the consistency of
> the data and that the replication will work as expected?
> 
> If this isn't the best way to set up replication for an already-running
> server with a mixture of InnoDB and MyISAM tables, what is?

For the sake of the list archives and anyone else that needs to do this in
the future, the method I suggested above does in fact work just fine and
doesn't require the use of mysqldump --opt as Egor suggested. It took about
5.5 hrs to rsync -ac the mysql data directory from ServerB to ServerC over a
100M connection while ServerB's mysql instance was shut down. 

-- 

Tabor J. Wells [EMAIL PROTECTED]
Fsck It! Just another victim of the ambient morality

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: opposite query

[Bob Lockie]

On 06/21/2004 05:02 PM Brent Baisley spoke:
The opposite of the query would be a.type!='X' and there is no related 
record in table b. Not sure if that is what you what
It isn't what I want because there could be other a.type other than 'X'.
Oops, that should be b.type
I need to return the a records that have no b records of type 'X' but 
there may be b records of other types.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: opposite query

[Bob Lockie]

On 06/21/2004 04:26 PM [EMAIL PROTECTED] spoke:
I don't understand. You want every other record except .what?
By flipping the equality the way you did, you should see all of the records
where a.id = b.id (regardless of what the b.type value is) where a.type was
not 'X' which is one reasonably valid solution to the statement "all of the
rows NOT found by the above query"
The more details you give us the better we can help you. If you can give us
your SHOW CREATE TABLE statements and possibly some sample data, we should
be able to get at exactly the data you want to see.
Respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
drop table a;
create table a (
id int auto_increment,
name varchar(20),
primary key (id)
);
drop table b;
create table b (
id int auto_increment,
a_id int not null,
name char(20),
type char,
primary key (id)
);
insert into a (name) values ("row1");
insert into b (a_id, name, type) values(1, "1b", "X");
insert into b (a_id, name, type) values(1, "2b", "Y");
insert into b (a_id, name, type) values(1, "3b", "Y");
insert into b (a_id, name, type) values(1, "4b", "Z");
insert into a (name) values ("row2");
insert into b (a_id, name, type) values(2, "1a", "X");
insert into b (a_id, name, type) values(2, "2a", "Y");
insert into b (a_id, name, type) values(2, "3a", "Y");
insert into b (a_id, name, type) values(2, "4a", "Z");
insert into a (name) values ("row3");
insert into b (a_id, name, type) values(3, "2a", "Y");
insert into b (a_id, name, type) values(3, "3a", "Y");
insert into b (a_id, name, type) values(3, "4a", "Z");
select a.name from a, b where b.type='X' and a.id=b.a_id;
returns row1 and row1 from a.
Now I need a select that will return row3 but not rows 1 or 2.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: opposite query

[Brent Baisley]

The opposite of the query would be a.type!='X' and there is no related 
record in table b. Not sure if that is what you what, but this is what 
the query would look like:

select name from a left join b on a.id=b.id where b.id is null and 
a.type!='X'

On Jun 21, 2004, at 4:04 PM, Bob Lockie wrote:
I have:
select name from a, b where a.type='X' and a.id=b.id;
I want a query to return all the rows that were NOT found by the above 
query.

I can't simply do:
select name from a, b where a.type!='X' and a.id=b.id;
because there is more than one row in b for each type!='X' but there 
is only one row in b for each type='X'

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: opposite query

[SGreen]

I don't understand. You want every other record except .what?

By flipping the equality the way you did, you should see all of the records
where a.id = b.id (regardless of what the b.type value is) where a.type was
not 'X' which is one reasonably valid solution to the statement "all of the
rows NOT found by the above query"

The more details you give us the better we can help you. If you can give us
your SHOW CREATE TABLE statements and possibly some sample data, we should
be able to get at exactly the data you want to see.

Respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   
  
  Bob Lockie   
  
  <[EMAIL PROTECTED]To:   MySQL Mailing List <[EMAIL 
PROTECTED]>  
  ca>  cc: 
  
   Fax to: 
  
  06/21/2004 04:04 Subject:  opposite query
  
  PM   
  
   
  
   
  




I have:
select name from a, b where a.type='X' and a.id=b.id;

I want a query to return all the rows that were NOT found by the above
query.

I can't simply do:
select name from a, b where a.type!='X' and a.id=b.id;
because there is more than one row in b for each type!='X' but there is
only one row in b for each type='X'

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]







-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Replication on same machine...

[Victor Medina]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi all!
Jonathan Villa wrote:
| I have MySQL 4.1 installed via rpm.  I just installed a new hd because I
| want another MySQL installation on the same machine but acting as a
| replication server.
- --8<
It won't matter as long as you keep all you mysql
installation-config-instances in separated locations. Also make sure
they'll be listening differents ports and using differents my.cnf's.
|
| At the same time, I want to try out MaxDB on this new installation.  Does
- ---8<
MaxDB uses some other port/set-of-config files than MySQL server. There
should not be a problem with this.
| that mean that I can't set up a master/slave replication with the hdb and
| hda?  Or can I go ahead and have a different config with the new instance
| and simply follow the instructions for setting up replication as normal?
YEAP! =) as long as they are in _SEPARATE_ configs
|
| Also, I have the source rpm, I figure I can tweak it to install into my
It could be _WAY_ easier to just do a compile/install the old way, just
change the prefix option of the configure script to poit to differents
locations: --prefix=/opt/mysql-1  --prefix=/opt/mysql-2 ... and so on
| new location which is /mysql2 .  Is that all I need to do make sure that
| it won't interfere with the current install?  How about command such as
| mysqladmin and mysql, etc
|
|
They don't even care as long as you tell them the right ip/port. =)
SO... if performace is not a problem and is just for testing, go ahead!
Best Regards!
- --
~ |...|
~ |  _    _|Victor Medina M   |
~ |\ \ \| |  _ \ / \   |Linux - Java - MySQL  |
~ | \ \ \  _| | |_) / _ \  |Dpto. Sistemas - Ferreteria EPA   |
~ | / / / |___|  __/ ___ \ |[EMAIL PROTECTED]  |
~ |/_/_/|_|_| /_/   \_\|Tel: +58-241-8507325 - ext. 325   |
~ ||Cel: +58-412-8859934  |
~ ||geek by nature - linux by choice  |
~ |...|
- ---
.- Este mensaje está digitalmente firmado para garantizar
~   su origen
.- El intercambio de llaves públicas se realiza a petición
~   de las partes interesadas via e-mail
- ---
.- This message has been digitally signed
.- Public Key (PGP or GPG) available upon request
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFA1z6c8WJSBCrOXJ4RAhVgAJ0YoXk23mIYiH8kjyBKkvAbu8IJJgCfSgkc
oowua4XLtl/ge2qkPs/omDM=
=yx9d
-END PGP SIGNATURE-

opposite query

[Bob Lockie]

I have:
select name from a, b where a.type='X' and a.id=b.id;
I want a query to return all the rows that were NOT found by the above 
query.

I can't simply do:
select name from a, b where a.type!='X' and a.id=b.id;
because there is more than one row in b for each type!='X' but there is 
only one row in b for each type='X'

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Replication on same machine...

[Jonathan Villa]

I have MySQL 4.1 installed via rpm.  I just installed a new hd because I
want another MySQL installation on the same machine but acting as a
replication server.

At the same time, I want to try out MaxDB on this new installation.  Does
that mean that I can't set up a master/slave replication with the hdb and
hda?  Or can I go ahead and have a different config with the new instance
and simply follow the instructions for setting up replication as normal?

Also, I have the source rpm, I figure I can tweak it to install into my
new location which is /mysql2 .  Is that all I need to do make sure that
it won't interfere with the current install?  How about command such as
mysqladmin and mysql, etc


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Intel Compiler

[Mike Miller]

I am trying to use my binary Intel installation to compile PHP (which seems 
to be doing a _very_ simple program).  So in a statically linked 
installation these references are defined in the embedded C library, but if 
one is linking it with another application, it isn't.
 Possibly the intel C library would need to be distributed with the binary 
application for the sake of linking with other applications?  Thoughts?


To quote the Intel C++ Compiler release notes:
"The Intel C++ Compiler uses two routines _intel_fast_memcpy and 
_intel_fast_memset to perform memcpy and memset operations that are not 
macro expanded to __builtin_memcpy and __builtin_memset in the source code. 
These are found in libirc. If you use the gcc compiler to link your 
application or if you directly call the linker, ld, you might find these 
unresolved symbols. For this reason, Intel recomends using the Intel C++ 
Compiler for linking, using the same compiler options used during the 
compilation phase. However, if you see these as undefined externals, either 
add -lirc to your link line, or change your includes so that memcpy and 
memset will be macro expanded to the builtin forms and recompile. The Intel 
C++ Compiler for IA-32 based applications calls a routine intel_proc_init 
from the main routine of any program to ensure that the processor is 
correctly set up. This routine is also found in libirc. These routines used 
further entry points from glibc, so -lirc needs to be placed before -lc on 
your command line."

usr/local/mysql/lib/libmysqlclient.a(libmysql.o): In function `read_rows':
libmysql.o(.text+0xe60): undefined reference to `_intel_fast_memcpy'
/usr/local/mysql/lib/libmysqlclient.a(libmysql.o): In function 
`mysql_real_connect.':
libmysql.o(.text+0x3044): undefined reference to `_intel_fast_memcpy'
/usr/local/mysql/lib/libmysqlclient.a(my_malloc.o): In function `my_memdup':
my_malloc.o(.text+0x31): undefined reference to `_intel_fast_memcpy'
/usr/local/mysql/lib/libmysqlclient.a(my_malloc.o): In function `my_strdup':
my_malloc.o(.text+0x100): undefined reference to `_intel_fast_memcpy'
/usr/local/mysql/lib/libmysqlclient.a(my_malloc.o): In function 
`my_strdup_with_length':
my_malloc.o(.text+0x132): undefined reference to `_intel_fast_memcpy'
/usr/local/mysql/lib/libmysqlclient.a(my_alloc.o)(.text+0x327): more 
undefined references to `_intel_fast_memcpy' follow
collect2: ld returned 1 exit status
configure: failed program was:
#line 56453 "configure"
#include "confdefs.h"
/* Override any gcc2 internal prototype to avoid an error.  */
/* We use char because int might match the return type of a gcc2
   builtin and then its argument prototype would still apply.  */
char mysql_error();

int main() {
mysql_error()
; return 0; }
-Mike
_
Add photos to your messages with MSN Premium. Get 2 months FREE*  
http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: R: why CPU is high while disks are idle in a table scan???

[SGreen]

There are 3 major factors determining your performance: 1) the speed of the
CPU, 2) the size if your RAM, and 3) the data transfer speed of your disks.

Since most computers do not let you change your CPU on the fly I will
assume that for some process A it will require the same number of clock
cycles to execute whether your data is in memory or on your disk. Being a
constant, we will leave it out for a moment.

RAM is much faster than disk for storage and retrieval. Thus, if you have
more RAM, you will generally have better performance.

That leave DISK I/O as the factor you are trying to change in order to
improve your performance, right?  Let's think through several scenarios and
figure out why what you did made only a small difference to your
performance in the tests you ran.

TEST 1) Random read of 10 rows

Whenever the database engine has to find a value in a database it has only
two choices. It can find a value in an index or it can perform a table scan
to find the value. Since I assume you used your primary key as your search
value for both tables then both lookups were resolved by index search.
MySQL uses binary tree indexes. That means for any search in an index of
size X you will have to perform roughly N=(log-base-2( X) ) comparisons to
find your value. For an index containing 1.5 Million items it would take
(log-base-2(1.5e6)) or about 21 comparisons to locate your value in the
index. For an index that contains 16 million we get the number
N=(log-base-2(1.6e7)) or about  24 comparisons to find your number.   How
much longer did you think the search engine would need to do those 3 extra
comparisons?  What  takes the longest is transferring all of the data for
the index into RAM from the disk then moving the read heads to the correct
location to retrieve your record. The actual lookup is blazingly fast. 16
million integers (assuming 32 bits per integer + 8 bytes for record
pointers) is only 192 MB so the entire list fits nicely into most server's
memory.

TEST 2) Insert 10 rows of new data

This performance will vary by database engine you are using (MyISAM or
InnoDB) as does the storage method. Suffice it to say that by the time the
disk has finished physically writing 10 rows of data to the media, the
CPU has already updated your index and is waiting on a chance to commit its
cache to disk. Both engines can grow the end of their indices and data
storage on disk efficiently so dumping new data into a singly-indexed table
is fast. Since all of the data was sent to the end of the dataspace, there
was no need to split data pages to insert rows mid-space.   What can kill
INSERT performance is if there are several indices on a table. Each new
record will have to be added to each index causing a nearly linear slowdown
in INSERT performance.

What splitting your data into smaller tables WILL help with is:
1) you are more fault tolerant. The failure of one 10% "block" of your data
will not affect in the availability of the other 90%.
2) you can move parts of your data to other disks. Using multiple I/O
channels should be faster than a single channel.
3) it will be easier to backup and archive your database.  You can move
parts of your data to "near-line" storage if it is old and not frequently
needed.
4) You can apply different indexing methods to your most active data blocks
than you need for your older records, improving your response time. You
save total disk space by adding indexes to just the most active portions of
your data.
5) If your application is smart enough, you can split your data over
several servers. This would be a SERIOUS performance increase.
6) Write-locking a table only locks up a portion of your data (MyISAM).
This improves concurrency and consistency.

Negative  things about splitting your data:
1) You will have to code more MERGE tables, UNION queries, and  table JOINS
depending your circumstances. This is a negative due to the increased
development time and maintenance complexity.
2) Certain aggregate queries will be much less efficient or unavailable.
3) Requires an external partitioning processes to decide which table should
receive new data.

Depending on your application, you may be able to gain significant
increases in performance by splitting your tables. I hope I was able to
shed some light on why I think your tests didn't show much difference
between the two storage schemes.

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   
 
  "Leonardo
 
  Francalanci" To:   "Mysql" <[EMAIL PROTECTED]>   
 
  <[EMAIL PROTECTED]cc:
  
  tel.ie> 

DBD::mysql problem

[Kairam, Raj]

> I am unable to install DBD::mysql in my environment shown below
> RedHat Linux 9
> perl v5.8.0
> DBI-1.42
> mysqld Ver 4.0.18-standard
> 
> After unzipping and untarring the DBD-mysql-2.9003.tar.gz ( obtained from CPAN ), in 
> the DBD-mysql-2.9003 directory
> I tried the following
> mkdir /tmp/mysql-static
> cp /usr/lib/mysql/*.a  /tmp/mysql-static
> perl Makefile.PL --libs="-L/tmp/mysql-static -lmysqlclient"
> make
> 
> It fails.
> When I looked at the Makefile created by 'perl Makefile.PL ..' command, I find there 
> are lines that cause make to fail
> Mostly they are constants that are not properly quoted.
> 
> Is there a proper tar.gz file that I can use in my ( RH Linux 9) environment stated 
> above that someone was able to successfully install?
Is it possible I am trying to use a version of DBD with wrong versions of Linux, perl 
and MySql individually or when used together as above ?

> Any advice or help will be very much appreciated.
> 
> Thanks
> Raj Kairam
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: R: R: why CPU is high while disks are idle in a table scan???

[Jeremy Zawodny]

On Mon, Jun 21, 2004 at 07:37:50PM +0200, Leonardo Francalanci wrote:
> > Oh.  You didn't say thta. :-)
> 
> Well, I wrote
> " Doing a simple "SELECT SUM(MYFIELD) FROM MYTABLE" I noticed "
> but don't worry ;)

Oh.  Damned Mondays.  I never liked 'em. :-(

> > How much RAM does your machine have?  If it's nearly all cached,
> > you'll be CPU bound rather than disk bound.
> 
> 1Gb.
> 
> I'm using Solaris 8.
> 
> The SUM() works as expected now (disks works a lot), but accessing by key
> is very cpu-intense and still 0% work on disk.
> 
> To sum up: table scans are very disk-intense (which make sense),
> accessing by key leaves disk on idle (I see that it should not work
> as with a table scan, but at least a little!)

Hmm.
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

[book] High Performance MySQL -- http://highperformancemysql.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Where to start for performance issues?

[Brent Baisley]

Performance for anything, database or otherwise, comes down to finding 
where the bottleneck is. Thankfully, there are only a limited number of 
places the bottleneck can be: RAM, CPU, Disk I/O, Network. Sounds easy, 
you have a 1 in 4 chance of picking the right one without doing any 
analysis. Of course, it's not that easy.

If your my.cnf file is not configured optimally, MySQL may be loading 
up the RAM it is allowed to use (perhaps showing a CPU spike), but then 
starting swapping out to disk (temp files) when it hits it's RAM 
allocation. Since this is MySQL hitting a RAM allocation limit and not 
the system running out of RAM, you won't see the OS indicating page 
outs, which would indicate not enough RAM.

Usually, the two parameters that help the most in the my.cnf file is 
the key_buffer and sort_buffer_size. If your complaints are coming from 
Forum and Picture Gallery, your bottleneck may be in disk I/O. I always 
try to put the OS on it's own disk like you, but if I only have two 
disks, I'll mirror the two disks to increase read speeds. Although it's 
then double important that you system has enough RAM since your writes 
will be slower (think slow memory paging). Of course, if you want to 
live dangerously, which you are just about doing anyway with no 
mirroring, you can stripe your disks and get better read and write 
speeds.
But first play with you my.cnf parameters to see if things speed up. 
Also, read the manual:
http://dev.mysql.com/doc/mysql/en/Optimising_the_Server.html

On Jun 21, 2004, at 12:10 AM, Eric Wagar wrote:
I have a Sun Netra T1 with 512MB of ram and 2 x 9G drives.  (One is
exclusively the MySQL 3.23.56 files, and the other is OS, Apache, etc.)
I am also using the default medium (I think) my.cnf.

Currently, the complaints are coming from a php Forum and a php picture
Gallery.  From the OS side, I don't see any problems, along with the
Apache side.  So, I am leaning towards an improper MySQL configuration.

Where do I go to get more information on where to start looking for
performance issues for the DB?
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

thread_stack and out of memory issues...

[Alejandro Heyworth]

Hi!
My Config:
Running Redhat 9.0 Linux 2.4.20-8bigmem SMP
8GB mem
250GB raid array
My server:
MySQL 4.1.2-alpha-standard
We are running into strange memory related errors with our system.
1) Warning: Asked for 262144 thread stack, but got 126876
On start up, we get the above warning.
How can I help MySQL get the stack space that it expects?
I changed thead_stack in my.cnf to 256k (from 192k) just to test the param 
out and it had no effect.

2) Out of Memory; check if mysqld or some other process uses all available 
memory -and- Out of memory (needed 56 bytes)

These errors are usually generated after MySQL dies.
These usually are produced after a large insert of a let's say 100,000 
records from our c client app using:

INSERT DELAYED INTO kneejoint VALUES (),(),(), etc...
Our C client inserter application works well, so I am leaning toward our 
server/distro configuration as the problem.

I'm inserting the records using our client because we are receiving 
real-time stress/strain data from a remote input source.

By the way, max_allowed_packet is large enough, so that is not the problem.
3) Can't create new thread (errno 11)
I am assuming that the server can't start a new client thread because of 
memory issues.

I have monitored the system and it doesn't look like any other app is using 
much memory at all.  In fact, it looks like most mem is cached when 
examined using top.

I haven't been able to interpret any of these error properly, so any help 
would be appreciated! 

R: R: why CPU is high while disks are idle in a table scan???

[Leonardo Francalanci]

> Oh.  You didn't say thta. :-)

Well, I wrote
" Doing a simple "SELECT SUM(MYFIELD) FROM MYTABLE" I noticed "
but don't worry ;)


> How much RAM does your machine have?  If it's nearly all cached,
> you'll be CPU bound rather than disk bound.

1Gb.

I'm using Solaris 8.

The SUM() works as expected now (disks works a lot), but accessing by key
is very cpu-intense and still 0% work on disk.

To sum up: table scans are very disk-intense (which make sense), accessing
by
key leaves disk on idle (I see that it should not work as with a table scan,
but at least a little!)


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: R: why CPU is high while disks are idle in a table scan???

[Jeremy Zawodny]

On Mon, Jun 21, 2004 at 07:10:59PM +0200, Leonardo Francalanci wrote:
> > Hard to say, but in a table scan the CPU does have a lot of work to
> > do.  It needs to do about 16,000,000 comparisons (based on your info).
> 
> Why comparison? It's a sum...

Oh.  You didn't say thta. :-)

> And the table is not small: 272,000,000 bytes!
> 
> And disk is very low (almost 0%)

How much RAM does your machine have?  If it's nearly all cached,
you'll be CPU bound rather than disk bound.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

[book] High Performance MySQL -- http://highperformancemysql.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

R: why CPU is high while disks are idle in a table scan???

[Leonardo Francalanci]

Sorry, now it runs as espected (but I don't know what happened, maybe doing
other queries
I emptied out the cache)

Thank you anyway for your replies.

And: could somebody answer my previous question?

I wrote:


I have to deal with pretty big data, such a table (say BIG_TABLE) with
16.000.000 of rows.
The table has just one primary index (a simple int).

I thought that splitting data among multiple tables would have boosted
performance,
so I put my data in 16 different tables (everything is on one disk).
The first table had IDs from 1 to 1.500.000, the second the range
151-300 and so on.
When I made performance tests, I found that I didn't get any performance
gain.

So I tried (just for test purpose) to create a table of 10 rows (say
SMALL_TABLE) with some data
(IDs from 1.000.000 to 1.100.000) from my big table BIG_TABLE.
Tests were like follows: (pseudocode)

for 10 times
read a column from BIG_TABLE where ID=


for 10 times
read a column from SMALL_TABLE where ID=


The results are the same!
There is no noticeable difference between a table with 16M rows and a table
with 100K rows!
Is that because my IDs are simple int from 1 to n (without gaps)?

I've tried to do 10 inserts in both tables, and BIG_TABLE takes 45
seconds while
SMALL_TABLE takes 43 secs (not such a big difference for a table 160 times
smaller!)

The only big performance gain I got is from a SUM on a big range, because
MySql in this case
does a full scan on the SMALL_TABLE which is faster than an access by index
on the BIG_TABLE.

Am I doing something wrong? Isn't splitting supposed to improve performance?
Or it is true
only if you can split data on multiple disks?


Thank you

Leonardo


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why CPU is high while disks are idle in a table scan???

[Brent Baisley]

If all your queries are on columns that are indexed (query by key), 
then all your data for your query may actually fit into cache. If you 
are only pulling data from a "key" field, then MySQL doesn't need to 
actually access the whole table, just the index.
You usually see high disk usage when you are accessing a lot of data, 
which is not necessarily the same as accessing a lot of rows.

On Jun 21, 2004, at 12:57 PM, Leonardo Francalanci wrote:
I'm doing some tests on a 16.000.000 rows table.
Doing a simple "SELECT SUM(MYFIELD) FROM MYTABLE" I noticed
that disks are at 0.1%, while cpu arrives up to 80%.
How is that possible? My HDs are IDE. MySql has around
30Mb of ram, I thought it should read a lot from disk.
Even doing lots of queries by key I get only high cpu usage,
not disk reads.
Is that normal?
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

R: why CPU is high while disks are idle in a table scan???

[Leonardo Francalanci]

> Hard to say, but in a table scan the CPU does have a lot of work to
> do.  It needs to do about 16,000,000 comparisons (based on your info).

Why comparison? It's a sum...
And the table is not small: 272,000,000 bytes!

And disk is very low (almost 0%)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why CPU is high while disks are idle in a table scan???

[Jeremy Zawodny]

On Mon, Jun 21, 2004 at 06:57:51PM +0200, Leonardo Francalanci wrote:
> I'm doing some tests on a 16.000.000 rows table.
> Doing a simple "SELECT SUM(MYFIELD) FROM MYTABLE" I noticed
> that disks are at 0.1%, while cpu arrives up to 80%.
> How is that possible? My HDs are IDE. MySql has around
> 30Mb of ram, I thought it should read a lot from disk.
> 
> Even doing lots of queries by key I get only high cpu usage,
> not disk reads.
> 
> Is that normal?

Hard to say, but in a table scan the CPU does have a lot of work to
do.  It needs to do about 16,000,000 comparisons (based on your info).

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

[book] High Performance MySQL -- http://highperformancemysql.com/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

why CPU is high while disks are idle in a table scan???

[Leonardo Francalanci]

I'm doing some tests on a 16.000.000 rows table.
Doing a simple "SELECT SUM(MYFIELD) FROM MYTABLE" I noticed
that disks are at 0.1%, while cpu arrives up to 80%.
How is that possible? My HDs are IDE. MySql has around
30Mb of ram, I thought it should read a lot from disk.

Even doing lots of queries by key I get only high cpu usage,
not disk reads.

Is that normal?




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Pocket PC, Flash, Linux server, Apache, MySQL, PHP

[Steve Howard]

Hi,

I am researching possible solutions for a simple database application
delivered to a Pocket Windows PDA (PocketPC). The database will be a subset
of a larger company-wide database held on a Linux server using MySQL and
PHP.

The PDA database will be used by a small number of people - probably no more
than 10 initially - and they will make only a small number of edits to the
database, fewer than 5 daily under normal circumstances.

The server and database already exist, so no configuration changes are
possible, but I can make changes to the database if required.

I'd love data replication with the server to be automatic if possible, but
if I have to build something to handle that then so be it.

For the sake of a nice clean, updatable interface, and to allow for some
other multimedia facilities which we can consider unrelated to this
database, the interface will be Macromedia Flash.



I am looking for the least painful method to enable the above. I have
encountered numerous suggestions including

- Wrapping the Flash interface inside a eVC wrapper, eVB, e.Net (C#) ...
- Using a local Access database, synchronising that with a server copy and
then synchronising the server copy of the access database with the main
MySQL application.
- Saving a record of local updates to text/xml file then passing the saved
data to the server for processing on synchronisation. If user A makes
updates, a record of those updates is then saved to a special table for
users B, C, D ... so that when they synchronise they can download and apply
the same edits. . but an automatic replication would be far more
elegant.


So far all of this is speculation. Obviously I have to make a choice as to
what methods I will apply and what technologies to employ. Has anyone here
done anything similar to above? Can you share comments, suggestions, tools
used?


TIA


Steve



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Create DB help

[Brian Reichert]

On Mon, Jun 21, 2004 at 12:01:06PM -0400, Tony Martino wrote:
> I believe it is exactly what I need.
> What I need to knwo now... could you please tell me WHERE I need to try:
>  "mysql -u{username} -p {databasename} Thank you very much in advance
> 
> Tony

-- 
Brian Reichert  <[EMAIL PROTECTED]>
37 Crystal Ave. #303Daytime number: (603) 434-6842
Derry NH 03038-1713 USA BSD admin/developer at large

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Create DB help

[Tony Martino]

Hello,

I am new with using mySQL.
I have a little problem

I am starting a new web site on which I wanna use the "same" DB I am using on another 
site (that was created by somebody else).  Instead of starting from scratch, I would 
like to use a copy of the DB we have, since we know it is already good and tested. 

I have an .sql file for the database structure.

I found this:
http://www.jsw4.net/info/list-archives/mysql/02-wk49/msg00049.html

I believe it is exactly what I need.
What I need to knwo now... could you please tell me WHERE I need to try:
 "mysql -u{username} -p {databasename}

MySQL backup not backing up all tables

[Danny Smitherman]

I am having trouble with a nightly backup of our MySQL database. Using the
mysqldump command, we dump our entire database to a backup directory. But
consistently the backup file contains only 33 of the 88 tables in the
database. The 33 getting backed up are the first 33 of the 88 as sorted
alphabetically by table name.

Here are the relevant lines in the backup script:

mysqldump --opt ACA >/home/sites/home/users/admin/dump/ACA/`date
--date '1 day ago' +%m%d`.all.ACA
mysqldump mysql >/home/sites/home/users/admin/dump/ACA/`date --date '1
day ago' +%m%d`.mysql

I'm a newbie with MySQL, so I don't know what other info to provide.

I appreciate your help.
 
Danny S.

*
Adventure Cycling Association
Inspiring people of all ages to travel by bicycle.
800/755-2453, 406/721-1776
www.adventurecycling.org 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: how to randomnly select just 1 record from the table?

[Binay]

well i have only 4 records in the table. When i use below mentioned query it
always return the same record irrespective of time delay. Now wht can i do?

Thanks
Binay

> "Binay" <[EMAIL PROTECTED]> wrote on 21/06/2004 15:03:34:
>
>
> > I need to select randomnly just one record from the table.
> > how can i do? please help me out.
>
> SELECT * FROM tbl_name ORDER BY RAND() LIMIT 1 ;
>
> Alec
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't drop database

[Michael Stassen]

When you DROP DATABASE 'base', mysql drops all its tables then drops the db. 
 At the filesystem, this means deleting all the table files from the 'base' 
subdirectory of mysql's data directory, then deleting the 'base directory 
itself.  If 'base' contains a file that isn't a table file, however, that 
file won't be deleted by mysql, which causes the attempt to delete the 
directory to fail.  Since directory 'base' still exists, mysql sees it as db 
'base' still exists.  So check in 'base' for any leftover files and either 
delete them or move them somewhere else, then try again.

Michael
michael watson (IAH-C) wrote:
Hi
I am running Suse Linux 8.2 and MySQL 3.23.55
A funny thing happens:
%> mysqladmin drop base
Database "base" dropped
%> mysqladmin create base
Can't create database 'base'. Database exists.
And if I log in to MySQL, there it is - undropped!
So why is mysqladmin telling me the database is dropped when it isn't?
Thanks in advance!
Mick

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: how to randomnly select just 1 record from the table?

[Alec . Cawley]

"Binay" <[EMAIL PROTECTED]> wrote on 21/06/2004 15:03:34:


> I need to select randomnly just one record from the table.
> how can i do? please help me out.

SELECT * FROM tbl_name ORDER BY RAND() LIMIT 1 ;

Alec

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Distinct Records Based On One Field - fixed

[Michael Stassen]

I think you still did it wrong, assuming the first column is IDs, but no 
matter.  How do you determine which row from Table2 is "the last record 
inserted with that number."?

Michael
PhistucK wrote:
I'm posting it again coz I did it all wrong. so here it is again:
I got a little problem.
I want to show distinct results based on only one field of the record,
field1.
Here's an example. I want to show records from Table1 & Table2 and each
number in field1\tfield1 must be output only once (unique), the last record
inserted with that number.
If my tables are:
Table1:
   field1 field2 field3
1 5Five   Five
2 4Four   Four
3 3Three Three
4 6Six  Six
5 8Eight  Eight
6 9Nine   Nine
Table2:
   tfield1 tfield2
1 5One
2 4Bla
3 5Blah
4 3Bluh
5 5Bla bla
5 6Bl.a
7 8B.La
8 9Lba
9 3LAb
I want to output this:
Output
field1 field2 field3  tfield2
1 4Four   Four   Bla
2 5FiveFive   Bla bla
3 6Six  Six Bl.a
4 8Eight  Eight  B.La
5 9Nine   Nine   Lba
6 3Three Three LAb
 Is there a way to do that? without making another table everytime I insert
records to the second? I mean, by an sql command?
 Thanks in advance.
PhistucK

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

how to randomnly select just 1 record from the table?

[Binay]

Hi all

I need to select randomnly just one record from the table.
how can i do? please help me out.

Thanks in advance
binay

Can't drop database

[michael watson (IAH-C)]

Hi

I am running Suse Linux 8.2 and MySQL 3.23.55

A funny thing happens:

%> mysqladmin drop base
Database "base" dropped
%> mysqladmin create base
Can't create database 'base'. Database exists.

And if I log in to MySQL, there it is - undropped!

So why is mysqladmin telling me the database is dropped when it isn't?

Thanks in advance!

Mick

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Returning where COUNT > 5

[Daniel Clark]

With a join and group by I think you have HAVING.

>>Hi all
>>
>>how to do this in MySQL? Returning only records with COUNT > 5?
>>
>>SELECT 
>>  `groups`.`groupsDescr`,
>>  `roles`.`roles_Agroup`,
>>  `roles`.`rolesDescr`,
>>  COUNT(`roles`.`rolesDescr`) AS TOTAL
>>FROM
>>  `roles`
>>  INNER JOIN `groups_roles` ON (`roles`.`rolesID` =
>>`groups_roles`.`fkrolesID`)
>>  INNER JOIN `groups` ON (`groups_roles`.`fkgroupsID` =
>>`groups`.`groupsID`)
>>GROUP BY `roles`.`rolesDescr`
>>WHERE COUNT(`roles`.`rolesDescr`) GT 5
>>
>>
>>MySQL 4.0.12 give an error... maybe because i'm using WHERE COUNT?
>>
>>Thanx for your time.




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Full Text Index on Large Tables - Not Answered

[SGreen]

Why are you putting a list of all skills into one field?  That's not a very
"relational" method of storing your data and you will have a much harder
time trying to extract skills from a text list of fields than if you used a
very standard database practice called "normalization"

Personally I would create 3 tables: one to contain a list of all people,
one that contains a list of all skills, and a third that will contain an
entry for each skill a person has (using either the primary key or a unique
key from both tables)

CREATE TABLE Person (
ID int auto_increment primary key,
Name varchar(20),

KEY (name)
)

CREATE TABLE Skill (
ID int auto_increment primary key,
SkillName varchar(50)
KEY (SkillName)
)

CREATE TABLE PERSON_SKILL (
Person_ID int,
Skill_ID
PRIMARY KEY(Person_ID, Skill_ID)
)

The primary key on the PERSON_SKILL table prevents the same person from
being assigned the same skill more than once.

Now, you can use text and numerical indices and not  full-text indices and
table scans to find your skills list. This is MUCH faster!!!

To get a list of all skills for a person:

SELECT p.Name, s.SkillName
FROM Person p
INNER JOIN PERSON_SKILL ps
  ON ps.Person_ID = p.ID
INNER JOIN Skill s
  ON s.ID = ps.SkillID
WHERE p.Name = 'somename here'

To get a list of people with a skill:
SELECT p.Name, s.SkillName
FROM Person p
INNER JOIN PERSON_SKILL ps
  ON ps.Person_ID = p.ID
INNER JOIN Skill s
  ON s.ID = ps.SkillID
WHERE s.SkillName = 'some skill here'

Regards,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   
 
  "Paul Chu"   
 
  <[EMAIL PROTECTED]To:   "'Paul Chu'" <[EMAIL 
PROTECTED]>, <[EMAIL PROTECTED]>
  net> cc: 
 
   Fax to: 
 
  06/18/2004 10:02 Subject:  RE: Full Text Index on Large 
Tables  - Not Answered
  PM   
 
   
 
   
 




Appreciate any help at all

Thanks, Paul



-Original Message-
From: Paul Chu [mailto:[EMAIL PROTECTED]
Sent: Friday, June 18, 2004 10:16 AM
To: [EMAIL PROTECTED]
Subject: Full Text Index on Large Tables

Hi,

If I have a table with 100 - 200 million rows and I want to search
For records with specific characteristics.

Ex.
Skills varchar(300)
 Skill id's 10   15
 Accounting finance etc.

Is it advisable to created a field with skill ids and then use the
Skills column in a full text index

Thanks for your help, Paul




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]







-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Fastest way to load a master table removing duplicates - Not Answered

[SGreen]

Paul,

These opinions reflect my own experience, your mileage may vary.

I the "fastest" way would depend on your source data. For most data dumps I
use LOAD DATA INFILE as it will read both delimited and fixed width text.
When applied to a table with NO indexes (indices?)  on it I will get the
best results translating data files into a MySQL database.

Now, how to detect dupes and not break your primary keys. There are
several ways of checking the integrity of your source data.
you ask: "If  inserted records have a duplicate SSN I don't want to insert
those but put  them in a duplicate table or flag them" and "Insert the row
and ignore duplicate using insert into master ( .)"
Those are similar in what you are trying to do. Let's look at flagging or
re-logging duplicate source rows first.

It would be very useful to apply some "artificial" uniqueness constraint to
your random source data unless you are absolutely certain that you can
identify a primary key.  I typically do this (when I need to) by importing
the data into a table with an auto_incrementing ID field. That way I can
easily identify each and every row in my source data. Using this field it
is rather simple to collect which rows duplicate themselved in the columns
you need to have unique.

SELECT 
FROM import_table
GROUP BY 
having count(1) >1

 represents the list of columns in which you do not want data
duplicated.

To copy those to another table takes two steps:CREATE a list of your
duplicated primary key columns, then INSERT the duplicated rows into the
other table

CREATE TEMPORARY TABLE tmpDupes
SELECT 
FROM import_table
GROUP BY 
having count(1) >1

INSERT duplicated_table ()
SELECT 
FROM import_table
INNER JOIN tmpDupes
  ON ...
  AND ...
  AND ...   
  
  net> cc: 
 
   Fax to: 
 
  06/18/2004 10:02 Subject:  RE: Fastest way to load a 
master table removing duplicates -   
  PMNot Answered   
 
   
 
   
 




Appreciate any help at all

Thanks, Paul



-Original Message-
From: Paul Chu [mailto:[EMAIL PROTECTED]
Sent: Friday, June 18, 2004 10:16 AM
To: [EMAIL PROTECTED]
Subject: Fastest way to load a master table removing duplicates

Hi all,



I want to load a Master table millions of rows for other sources with a
unique index on e.g. ssn social sec number.

If  inserted records have a duplicate SSN I don't want to insert those
but put  them in a duplicate table or flag them.

.

The primary key will be an auto-increment field. There will be  other
indexes such as zipcode..



What is the fastest way to load these rows and remove duplicates ?

Assume I load the rows to be inserted into another table.



1.   Check if the ssn already exists before inserting the row ?



2.  Insert the row and ignore duplicate using

insert into master ( .)

  select .. From loaddata



I have lots of  files with data that can be saved to load tables and
then inserted into the master table.





Regards, Paul







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]







-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Distinct Records Based On One Field - fixed

[PhistucK]

I'm posting it again coz I did it all wrong. so here it is again:
I got a little problem.
I want to show distinct results based on only one field of the record,
field1.
Here's an example. I want to show records from Table1 & Table2 and each
number in field1\tfield1 must be output only once (unique), the last record
inserted with that number.
If my tables are:
Table1:
   field1 field2 field3
1 5Five   Five
2 4Four   Four
3 3Three Three
4 6Six  Six
5 8Eight  Eight
6 9Nine   Nine
Table2:
   tfield1 tfield2
1 5One
2 4Bla
3 5Blah
4 3Bluh
5 5Bla bla
5 6Bl.a
7 8B.La
8 9Lba
9 3LAb
I want to output this:
Output
field1 field2 field3  tfield2
1 4Four   Four   Bla
2 5FiveFive   Bla bla
3 6Six  Six Bl.a
4 8Eight  Eight  B.La
5 9Nine   Nine   Lba
6 3Three Three LAb

 Is there a way to do that? without making another table everytime I insert
records to the second? I mean, by an sql command?

 Thanks in advance.

PhistucK

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: database size

[Alec . Cawley]

Ruslan Spivak <[EMAIL PROTECTED]> wrote on 21/06/2004 12:22:04:

> I have quite big database - around 15Gb(~ 180 million of rows).
> My problem is that after deleting 70million of rows(with 'delete from'
> statement) database file size didn't decrease - it's still 15Gb.
> 
> Can anybody give me advice, what i'm doing wrong, why db size is the
> same after deleting around 1/3 of all rows.
> Thanks in advance

I am assuming you are using MyISAM table type. MySQL does not normally 
compress the files when deleting records because that would mean shuffling 
up all the data in the file, which would be a very slow operation. 
Instead, it leaves holes which are filled in when new records are added. 
If you want to reclaim the space, you must OPTIMIZE the tables, which 
rebuilds them in a new file and discards the old.

Alec



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

failure to connect using a c program

[cathal . carr3]

Hi,

I am trying to execute a c program which extracts data from a mysql database.
I have installed mysql and created the desired database called "one"

The program uses the following if statment to try to connect to the database
and flag if connection is not successful:
---
if (!mysql_real_connect(&mysql,"localhost","root","XXX","one",0,NULL,0))

{

printf("\nError connecting to database\n\n");

printf("Error: %s\n\n",mysql_error(&mysql));
exit(1);

}

where root is the username and XXX denotes the place wher I filled in my
password. The username and password used are the same as ones I use to successfully
connect to the database by starting up the client.


However, when I try to execute the code the program terminates at this point
and prints out the following:

Error connecting to database

Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.
sock' (2)


I understand that it is very difficult to spot the problem without viewing
the entire code but can anyone hazard a guess to why this is happening?
Does the c program need to be in any specific mysql directory?



note: if I try to connect to the server from the command line, while the
MySQL server is not started, using "./mysql -u root -pXXX" it will return
the error:
"Can't connect to local MySQL server through socket '/tmp/mysql.sock'"
Does the fact that the location of mysql.sock is different in each case?



Thanks in advance,
Cathal



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

database size

[Ruslan Spivak]

Hello.

I have quite big database - around 15Gb(~ 180 million of rows).
My problem is that after deleting 70million of rows(with 'delete from'
statement) database file size didn't decrease - it's still 15Gb.

Can anybody give me advice, what i'm doing wrong, why db size is the
same after deleting around 1/3 of all rows.
Thanks in advance

Best regards,
Ruslan


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Full text search problem

[Paul McNeil]

Good morning.  Not knowing too much about PHP it looks like you are
searching for
`name`,`colour`,`gender`,`breed`,`location`,`description`
Where there is a whitespace in the name.

Could you use,

$query_results = sprintf("SELECT * FROM dogslost WHERE
`name` LIKE '% %'", $crit_results);


God Bless

Paul C. McNeil
Developer in Java, MS-SQL, MySQL, and web technologies.















GOD BLESS AMERICA!
To God Be The Glory!

-Original Message-
From: Terry Riley [mailto:[EMAIL PROTECTED]
Sent: Monday, June 21, 2004 5:08 AM
To: [EMAIL PROTECTED]
Subject: Re: Full text search problem


Pieter,

I think FTS minimum WORD size is 4 characters - you may to be searching
with 3 on 'May May'.

Not having ever used FTS; I believe you can adjust it to count 3-character
words by changing the configuration, but I'm not sure where - and it would
then need re-indexing, if I'm not mistaken.

Hope that helps

Terry

--Original Message-

> Hi
>
> I have a fulltext search on a dbase for lost pets.
> My problem is the following:
>
> I have dog in the database called "May May" which doesnt show up in the
> search results. A dog called "Doggy Doggy" does show up however. I
> guess the problem is that MySql sees "May May" as being a date or
> something and doesnt do a text compare.
>
> Here is my query, from php.
> $query_results = sprintf("SELECT * FROM dogslost WHERE  MATCH
> (`name`,`colour`,`gender`,`breed`,`location`,`description`) AGAINST
> ('%s' IN BOOLEAN MODE)", $crit_results);
>
> any ideas?
>
> Regards
> Pieter
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Distinct records based on one field

[PhistucK]

I got a little problem.
I want to show distinct results based on only one field of the record, field1.
Here's an example. I want to show records from Table1 and the number in field1 must 
not show more than once.
If my table is this:
Table1
  field1 field2 field3 field4 
  5 Five Five One 
  4 Four Four Bla 
  5 Five Five Blah 
  3 Three Three Bluh 
  5 Five Five Bla bla 
  6 Six Six Bl.a 
  8 Eight Eight B.La 
  9 Nine Nine Lba 
  3 Three Three LAb 

I want to output this:
Output
  field1 field2 field3 field4 
  5 Five Five Bla bla 
  6 Six Six Bl.a 
  8 Eight Eight B.La 
  9 Nine Nine Lba 
  3 Three Three LAb 



Is there a way to do that? without making another table everytime? I mean, by an sql 
command?

Thanks in advance.

PhistucK

Re: disconnecting from server

[Egor Egorov]

[EMAIL PROTECTED] wrote:
> 
> I am using the command "./mysqld_safe --user=3Dmysql &" to connect to the=
> 
> server but how does one disconnect?
> 

With the above command you start the MySQL server, not connect.
To stop MySQL server use command:
shell> mysqladmin shutdown

http://dev.mysql.com/doc/mysql/en/mysqladmin.html



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: disconnecting from server

[Carl Fretwell]

exit; ?

- Original Message - 
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, June 21, 2004 10:12 AM
Subject: disconnecting from server


> Hi,
> 
> I am using the command "./mysqld_safe --user=mysql &" to connect to the
> server but how does one disconnect?
> 
> Thanks in advance
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Professional certification

[Carsten Pedersen]

Hi Brian,

On Fri, 2004-06-18 at 19:48, Brian Mansell wrote:
> I took the certification exam this morning and passed.

Congratulations!

>  When should I
> expect to receive the certificate (and other items) in the mail?

It usually takes 4-6 weeks for the certificate to arrive at your
address.

Best regards,

/ Carsten

-- 
Warning: Certification can seriously increase your wealth!
http://www.mysql.com/certification

Carsten Pedersen
Coordinator of Development, Certification Manager
MySQL AB, http://www.mysql.com
Office: +45 56 36 16 10


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Full text search problem

[Matt W]

Hi Pieter,

That's because "may" is a stopword in MySQL's full-text indexing, by
default (like "can," "the," etc).  You can define your own stopword file
with the ft_stopword_file variable.  And you can find the default, built-in
list of stopwords in the file myisam/ft_static.c of the source
distribution.

Hope that helps.

(Oh, also what Terry said in his reply!)


Matt


- Original Message -
From: "Pieter Botha"
Sent: Monday, June 21, 2004 3:54 AM
Subject: Full text search problem


> Hi
>
> I have a fulltext search on a dbase for lost pets.
> My problem is the following:
>
> I have dog in the database called "May May" which doesnt show up in the
> search results. A dog called "Doggy Doggy" does show up however. I guess
> the problem is that MySql sees "May May" as being a date or something
> and doesnt do a text compare.
>
> Here is my query, from php.
> $query_results = sprintf("SELECT * FROM dogslost WHERE  MATCH
> (`name`,`colour`,`gender`,`breed`,`location`,`description`) AGAINST
> ('%s' IN BOOLEAN MODE)", $crit_results);
>
> any ideas?
>
> Regards
> Pieter


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

performance splitting data

[Leonardo Francalanci]

I have to deal with pretty big data, such a table (say BIG_TABLE) with
16.000.000 of rows.
The table has just one primary index (a simple int).

I thought that splitting data among multiple tables would have boosted
performance,
so I put my data in 16 different tables (everything is on one disk).
The first table had IDs from 1 to 1.500.000, the second the range
151-300 and so on.
When I made performance tests, I found that I didn't get any performance
gain.

So I tried (just for test purpose) to create a table of 10 rows (say
SMALL_TABLE) with some data
(IDs from 1.000.000 to 1.100.000) from my big table BIG_TABLE.
Tests were like follows: (pseudocode)

for 10 times
read a column from BIG_TABLE where ID=


for 10 times
read a column from SMALL_TABLE where ID=


The results are the same!
There is no noticeable difference between a table with 16M rows and a table
with 100K rows!
Is that because my IDs are simple int from 1 to n (without gaps)?

I've tried to do 10 inserts in both tables, and BIG_TABLE takes 45
seconds while
SMALL_TABLE takes 43 secs (not such a big difference for a table 160 times
smaller!)

The only big performance gain I got is from a SUM on a big range, because
MySql in this case
does a full scan on the SMALL_TABLE which is faster than an access by index
on the BIG_TABLE.

Am I doing something wrong? Isn't splitting supposed to improve performance?
Or it is true
only if you can split data on multiple disks?


Thank you

Leonardo


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

disconnecting from server

[cathal . carr3]

Hi,

I am using the command "./mysqld_safe --user=mysql &" to connect to the
server but how does one disconnect?

Thanks in advance


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

UTF-8 With MySQL 4.0.18 and PHP 2.5.5

[Dave G]

MySQL List,
At my request, my virtual hosting service has upgraded to MySQL
4.0.18 so that I can have better UTF-8 character support. Full Unicode
support is important to me as I do bilingual Japanese/English sites. My
understanding is that in 4.0.18 I can specify that a TEXT field is
specifically in UTF-8, or other encodings if I desire.
However, usually I use phpMyAdmin to manage my database, and in
the version that I'm running - 2.5.5, it doesn't seem to have any
special selectors or anything to indicate the character encoding of any
one field or column.
My question is, do I have to step outside of phpMyAdmin to
ensure that the correct encoding is being used? If I do so, will that
affect future database management from within phpMyAdmin?

Any advice would be much appreciated.

-- 
Yoroshiku!
Dave G
[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Full text search problem

[Terry Riley]

Pieter,

I think FTS minimum WORD size is 4 characters - you may to be searching 
with 3 on 'May May'.

Not having ever used FTS; I believe you can adjust it to count 3-character 
words by changing the configuration, but I'm not sure where - and it would 
then need re-indexing, if I'm not mistaken.

Hope that helps

Terry

--Original Message-  

> Hi
> 
> I have a fulltext search on a dbase for lost pets.
> My problem is the following:
> 
> I have dog in the database called "May May" which doesnt show up in the 
> search results. A dog called "Doggy Doggy" does show up however. I 
> guess the problem is that MySql sees "May May" as being a date or 
> something and doesnt do a text compare.
> 
> Here is my query, from php.
> $query_results = sprintf("SELECT * FROM dogslost WHERE  MATCH 
> (`name`,`colour`,`gender`,`breed`,`location`,`description`) AGAINST 
> ('%s' IN BOOLEAN MODE)", $crit_results);
> 
> any ideas?
> 
> Regards
> Pieter
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Full text search problem

[Pieter Botha]

Hi
I have a fulltext search on a dbase for lost pets.
My problem is the following:
I have dog in the database called "May May" which doesnt show up in the 
search results. A dog called "Doggy Doggy" does show up however. I guess 
the problem is that MySql sees "May May" as being a date or something 
and doesnt do a text compare.

Here is my query, from php.
$query_results = sprintf("SELECT * FROM dogslost WHERE  MATCH 
(`name`,`colour`,`gender`,`breed`,`location`,`description`) AGAINST 
('%s' IN BOOLEAN MODE)", $crit_results);

any ideas?
Regards
Pieter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysql-connect is working very slow (2)

[Marten Lehmann]

Hello,
regarding --skip-name-resolve: I read, that values in the Host-column of 
the mysql-authorization must be localhost or ip-addresses to work. Since 
everyone can connect from everywhere, I used 'localhost' and '%'. Is % 
treated the same? Is the ip-address-rule obligatory or just a 
should-have? Is --skip-name-resolve treated as if wouldn't be there if 
the values in Host aren't of type ip-address?

Regards
Marten

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysql-connect is working very slow

[Marten Lehmann]

Hello,
this weekend, one of our caching nameservers failed. Since then, all 
mysql-connects were running very slow. I removed the failing nameserver 
in /etc/resolv.conf. Later, I was setting up another caching nameserver 
instead and inserted it in /etc/resolv.conf. I rebooted the 
mysql-machine. Nothing helped so far. What does mysql take so long to 
connect? I guess it's some sort of lookup, but I don't know what in 
detail. Since there's no failing nameserver anymore, I don't understand, 
why it is still taking so long. Is it performing ident lookups also? How 
can I check which nameserver mysql is using? I also tried 
--skip-name-resolve in the mysqld-startup, but this also didn't help. 
But connecting from localhost is fast as always.

Regards
Marten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Merge tables and Fulltext?

[Santino]

Hello,
Is there any plan to support fulltext indexes in Merge Tables?
Thank You.
Santino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqld not starting

[Egor Egorov]

Matt Brei <[EMAIL PROTECTED]> wrote:
> 
> Last night I upgraded from 3.23 to 4.0.20 and when I try to start mysqld
> it fails.  The only entries I have in my error log are:
> 
> 040620 23:44:19  mysqld started
> 040620 23:44:19  mysqld ended
> 
> I know this isnt' enough info to troubleshoot the problem.  How can I
> set the log level to give me more detail so I can figure out why the
> service keeps dying?  Any help would be greatly appreciated.
> 

Check error log file for error message.



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]