Re: [NEWBIE] How To Trim Database To N Records

[Dominik Klein]

Is there a query that will, say, trim a table down to a million rows (with
some sort order, of course, as I'm interested in deleting the oldest ones)?


If you have got a datecolumn, you might also want to delete anything 
that is older than x days (2 in my example):


DELETE FROM database.table WHERE datecolumn <= 
DATE_SUB(sysdate(),INTERVAL 2 day);


Regards
Dominik

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

RE: Fulltext and reserved words

[Taco Fleur]

Hi,

I did everything but rebuild, I restarted the server but the variable still
has the same value. And I don't know how to rebuild the table, I read the
documentation, but I guess I am just missing something there, like I have no
idea as a newbie where to enter those commands etc. 


Kind regards,
 

Taco Fleur 

Free Call 1800 032 982 or Mobile 0421 851 786
Pacific Fox http://www.pacificfox.com.au an industry leader with commercial
IT experience since 1994 .

*   
Web Design and Development 
*   
SMS Solutions, including developer API
*   
Domain Registration, .COM for as low as fifteen dollars a year,
.COM.AU for fifty dollars two years!


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 13 April 2006 4:41 PM
To: mysql@lists.mysql.com
Subject: RE: Fulltext and reserved words

Hi Taco - interesting thread.

Did you rebuilt the FullText indexes after restarting the server?

>From the 5.0.18 manual pages:

12.7.5. Fine-Tuning MySQL Full-Text Search


Most full-text variables described in this section must be set at server
startup time. A server restart is required to change them; they cannot be
modified while the server is running. 

Some variable changes require that you rebuild the FULLTEXT indexes in your
tables. Instructions for doing this are given at the end of this section. 

The minimum and maximum lengths of words to be indexed are defined by the
ft_min_word_len and ft_max_word_len system variables. (See Section 5.2.2,
Server System Variables.)

The default minimum value is four characters; the default maximum is version
dependent. If you change either value, you must rebuild your FULLTEXT
indexes.

For example, if you want three-character words to be searchable, you can set
the ft_min_word_len variable by putting the following lines in an option
file:

[mysqld]
ft_min_word_len=3

Then you must restart the server and rebuild your FULLTEXT indexes. Note
particularly the remarks regarding myisamchk in the instructions following
this list. 


Regards

Keith

In theory, theory and practice are the same; in practice they are not.

On Thu, 13 Apr 2006, Taco Fleur wrote:

> To: mysql@lists.mysql.com
> From: Taco Fleur <[EMAIL PROTECTED]>
> Subject: RE: Fulltext and reserved words
> 
> Thanks for that, you saved the day. I am now trying to change that 
> variable, I did a search through the ini files and changed the only 
> file that had ft_min_word_len in it to ft_min_word_len = 2 restarted 
> the server did SHOW VARIABLES and it still shows as 4, not sure what I 
> am missing, going through the documentation but just can't find 
> anything specific.
> 
> Kind regards,
> 
> Taco Fleur

--
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: Fulltext and reserved words

[mysql]

Hi Taco - interesting thread.

Did you rebuilt the FullText indexes after restarting the 
server?

>From the 5.0.18 manual pages:

12.7.5. Fine-Tuning MySQL Full-Text Search


Most full-text variables described in this section must be 
set at server startup time. A server restart is required to 
change them; they cannot be modified while the server is 
running. 

Some variable changes require that you rebuild the FULLTEXT 
indexes in your tables. Instructions for doing this are 
given at the end of this section. 

The minimum and maximum lengths of words to be indexed are 
defined by the ft_min_word_len and ft_max_word_len system 
variables. (See Section 5.2.2, Server System Variables.)

The default minimum value is four characters; the default 
maximum is version dependent. If you change either value, 
you must rebuild your FULLTEXT indexes.

For example, if you want three-character words to be 
searchable, you can set the ft_min_word_len variable by 
putting the following lines in an option file:

[mysqld]
ft_min_word_len=3

Then you must restart the server and rebuild your FULLTEXT 
indexes. Note particularly the remarks regarding myisamchk 
in the instructions following this list. 


Regards

Keith

In theory, theory and practice are the same;
in practice they are not.

On Thu, 13 Apr 2006, Taco Fleur wrote:

> To: mysql@lists.mysql.com
> From: Taco Fleur <[EMAIL PROTECTED]>
> Subject: RE: Fulltext and reserved words
> 
> Thanks for that, you saved the day. I am now trying to 
> change that variable, I did a search through the ini files 
> and changed the only file that had ft_min_word_len in it 
> to ft_min_word_len = 2 restarted the server did SHOW 
> VARIABLES and it still shows as 4, not sure what I am 
> missing, going through the documentation but just can't 
> find anything specific.
> 
> Kind regards,
> 
> Taco Fleur 

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

~problem with select into outfile~

[Mohammed Abdul Azeem]

Hi 

Iam having a problem using "select into outfile" command, iam getting
the following error. can anyone help me trace the problem.

mysql> SELECT * INTO OUTFILE'/home/public1/data.txt' FROM temp_table
WHERE last_time_update = "2006-04-01";
ERROR 1 (HY000): Can't create/write to file
'/home/public1/data.txt' (Errcode: 13)

Thanks in advance,
Abdul.


This email has been Scanned for Viruses!
  www.newbreak.com



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

innodb vs myisam

[Luke Vanderfluit]

Hi.

I have the following myisam table:

The table is only 32,000 rows, but over 60Megs in size. And mysql seems
to be wanting to write to that file alot, so it may well be trying to
seek all over the disk looking for the right spot all the time.

Does innodb do a better job at keeping the file on the disk smaller?

Does an innodb table take up less disk space than myisam?

Is an innodb table compacter and would therefore require less disk seek
time or I/O than myisam?

Kind regards.
Luke.


--
Luke



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

Re: [NEWBIE] How To Trim Database To N Records

[Shawn Green]

--- "David T. Ashley" <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> I'm a beginning MySQL user ...
> 
> I have a table of log entries.  Over time, the entries could grow to
> be
> numerous.  I'm like to trim them to a reasonable number.
> 
> Is there a query that will, say, trim a table down to a million rows
> (with
> some sort order, of course, as I'm interested in deleting the oldest
> ones)?
> 
> The sorting isn't a problem.  I've just never seen an SQL statement
> that
> will drop rows until a certain number remain ...
> 
> Thanks, Dave.
> 

There are two ways to do this but this is the simplest:

(this should set the value of @lastID to whatever is the 11st
oldest ID)
SELECT @lastID:= ID, @lastDate:= datecolumn
FROM yourtablename
ORDER BY datecolumn desc, id desc
LIMIT 10,1;

Now, assuming that there are several records within the same second
(very possible depending on your traffic) and that you want to limit
the database to exactly 10 rows (see previous query), then this
should work for you:

DELETE FROM yourtablename
WHERE datecolumn < @lastDate
   or (datecolum = @lastDate
  AND id < @lastID);

Let us know how it works...

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

__
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: question

[Shawn Green]

--- Takanobu Kawabe <[EMAIL PROTECTED]> wrote:

> Is there any way to set  many columns of the table  in one line?
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 

The UPDATE command is not limited to acting on just one column at a
time or (depending on your version) one table at a time.

An example single-table, multi-column update:

UPDATE user_account SET lastname='Jones', married='Y' WHERE ID = 16;

An example multi-table, multi-column update:

UPDATE old_table o
INNER JOIN bulk_changes b
  ON o.pkid = b.pkid
SET o.Col_1 = b.Col_1, o.Col_2 = b.Col_2, o.Col_3 = b.Col_3,
o.updatetime = CURDATE();

Details are here:
http://dev.mysql.com/doc/refman/4.1/en/update.html

I hope that helps,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

__
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: [NEWBIE] How To Trim Database To N Records

[Steve Edberg]

At 11:15 PM -0400 4/12/06, David T. Ashley wrote:

Hi,

I'm a beginning MySQL user ...

I have a table of log entries.  Over time, the entries could grow to be
numerous.  I'm like to trim them to a reasonable number.

Is there a query that will, say, trim a table down to a million rows (with
some sort order, of course, as I'm interested in deleting the oldest ones)?

The sorting isn't a problem.  I've just never seen an SQL statement that
will drop rows until a certain number remain ...

Thanks, Dave.


Something like this might work (untested):

   select @n:=count(*) from your_table
   delete from your_table order by time_stamp limit @n-100

Of course, you'd want to try it on a test table first, not live data! 
This assumes 100 is the max number of records you want to keep, 
you want to delete the oldest records based on the time_stamp column, 
AND that the record count when you do this delete is always > 
100. You'd need to do some additional checking first if that 
isn't the case, as I don't know at the moment what the behavior for a 
negative or zero limit is (the docs below should tell you).


More info:

   http://dev.mysql.com/doc/refman/4.1/en/user-variables.html
   http://dev.mysql.com/doc/refman/4.1/en/example-user-variables.html
   http://dev.mysql.com/doc/refman/4.1/en/delete.html

steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

[NEWBIE] How To Trim Database To N Records

[David T. Ashley]

Hi,

I'm a beginning MySQL user ...

I have a table of log entries.  Over time, the entries could grow to be
numerous.  I'm like to trim them to a reasonable number.

Is there a query that will, say, trim a table down to a million rows (with
some sort order, of course, as I'm interested in deleting the oldest ones)?

The sorting isn't a problem.  I've just never seen an SQL statement that
will drop rows until a certain number remain ...

Thanks, Dave.



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

customized variations on words with fulltext

[Taco Fleur]

Hi all,
 
sorry for all the questions lately, just getting my feet wet in MySQL.
 
Is there any way to create my own variations on words so that MySQL will
include those in the fulltext search?
 
What I am after is for example if someone enters VB, I'd like the search to
also look for;
- Visual Basic
- VisualBasic
 
Is this possible, if so how?
 
Thanks in advance for any help.
 
Kind regards,
 

Taco Fleur 

Free Call 1800 032 982 or Mobile 0421 851 786
Pacific Fox   http://www.pacificfox.com.au an
industry leader with commercial IT experience since 1994 .

*   

Web Design and Development 
*   

SMS Solutions, including developer API
*   

Domain Registration, .COM for as low as fifteen dollars a year,
.COM.AU for fifty dollars two years!

 

question

[Takanobu Kawabe]

Is there any way to set  many columns of the table  in one line?

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

comparing 2 dbs and generating 'upgrade sql'

[starmonkey]

hey guys,

A while ago I remembered using SQLYog or some other tool that could  
look at two databases (say dev and test versions) and work out the  
differences in structure between the two, and generate some SQL to  
'upgrade' one to the other (bunch of alter table commands basically).


I'm wondering if there's any way to do this using free tools, as I  
believe SQLYog was commercial?


thanks,
sm



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

RE: Fulltext and reserved words

[Taco Fleur]

Thanks for that, you saved the day.
I am now trying to change that variable, I did a search through the ini
files and changed the only file that had ft_min_word_len in it to
ft_min_word_len = 2 restarted the server did SHOW VARIABLES and it still
shows as 4, not sure what I am missing, going through the documentation but
just can't find anything specific. 


Kind regards,
 

Taco Fleur 

Free Call 1800 032 982 or Mobile 0421 851 786
Pacific Fox http://www.pacificfox.com.au an industry leader with commercial
IT experience since 1994 .

*   
Web Design and Development 
*   
SMS Solutions, including developer API
*   
Domain Registration, .COM for as low as fifteen dollars a year,
.COM.AU for fifty dollars two years!


-Original Message-
From: Dan Nelson [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 13 April 2006 3:56 AM
To: Taco Fleur
Cc: mysql@lists.mysql.com
Subject: Re: Fulltext and reserved words

In the last episode (Apr 12), Taco Fleur said:
> I am in need to search for sql reserved words with MATCH AGAINST it 
> turns it doesn't return anything when I feed it a reserved word like 
> 'sql' is there anything I can do about that?

By default the full-text indexer skips words less than four characters long.
If you set ft_min_word_len=3 and rebuild your index, "sql" should get
indexed.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html

-- 
Dan Nelson
[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: MySql Error Number 1130

[mysql]

When you are trying to connect to port 3307, for 
example, are you specifying that port from your remote 
machine? Or are you connecting to the mysqld listening on 
port 3306?

You could try shutting down the server listening on port 
3306, and then connecting to port 3307. See if the error 
message changes or goes away.

shell># mysql -h host_name -u root -px --port=3307

I think you may need to specify the absolute IP address in 
the user table, instead of a wildcard '%'.

See if this helps. Use your root username and password
in place of 'tommy'. I did not want to mess up my root user
account! Use the IP address of your remote machine you want 
to connect to mysql with, in place of 10.0.0.5.

mysql> create user 'tommy'@'10.0.0.5'
-> identified by '12345';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.*
-> to 'tommy'@'10.0.0.5'
-> identified by '12345';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where user = 'tommy' \G
** 1. row *
 Host: 10.0.0.5
 User: tommy
 Password:  snipped
  Select_priv: Y
  Insert_priv: Y
  Update_priv: Y
  Delete_priv: Y
  Create_priv: Y
Drop_priv: Y
  Reload_priv: Y
Shutdown_priv: Y
 Process_priv: Y
File_priv: Y
   Grant_priv: N
  References_priv: Y
   Index_priv: Y
   Alter_priv: Y
 Show_db_priv: Y
   Super_priv: Y
Create_tmp_table_priv: Y
 Lock_tables_priv: Y
 Execute_priv: Y
  Repl_slave_priv: Y
 Repl_client_priv: Y
 Create_view_priv: Y
   Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
 Create_user_priv: Y
 ssl_type:
   ssl_cipher:
  x509_issuer:
 x509_subject:
max_questions: 0
  max_updates: 0
  max_connections: 0
 max_user_connections: 0
1 row in set (0.00 sec)

HTH

Keith

In theory, theory and practice are the same;
in practice they are not.

On Wed, 12 Apr 2006, Duzenbury, Rich wrote:

> To: mysql@lists.mysql.com
> From: "Duzenbury, Rich" <[EMAIL PROTECTED]>
> Subject: MySql Error Number 1130
> 
> Hi all,
> 
> I am setting up two additional instances of mysql on my mysql server,
> which is running version 5.0.18-standard-log.
> 
> I've got the additional instances set up, and they are running.  I can
> see that they are bound to the proper ports.  
> 
> I can connect to them locally like
> mysql -p --port=3306
> mysql -p --port=3307
> mysql -p --port=3320
> 
> I have always been able to connect remotely from my workstation to the
> base server on 3306.  
> 
> My problem is that I cannot connect remotely to either of the new
> additional instances running on 3307 or 3320.  Anytime I attempt to do
> so, I receive MySQL Error Number 1130, Host 'nnn.nnn.nnn.nnn' is not
> allowed to connect to this MySQL server.  
> 
> I've been googling and reading manuals all day, and haven't made a dent.
> 
> 
> User Table
> *** 1. row ***
>  Host: %
>  User: root
>  Password: (redacted)
>   Select_priv: Y
>   Insert_priv: Y
>   Update_priv: Y
>   Delete_priv: Y
>   Create_priv: Y
> Drop_priv: Y
>   Reload_priv: Y
> Shutdown_priv: Y
>  Process_priv: Y
> File_priv: Y
>Grant_priv: Y
>   References_priv: Y
>Index_priv: Y
>Alter_priv: Y
>  Show_db_priv: Y
>Super_priv: Y
> Create_tmp_table_priv: Y
>  Lock_tables_priv: Y
>  Execute_priv: Y
>   Repl_slave_priv: Y
>  Repl_client_priv: Y
>  ssl_type:
>ssl_cipher:
>   x509_issuer:
>  x509_subject:
> max_questions: 0
>   max_updates: 0
>   max_connections: 0
> *** 2. row ***
>  Host: localhost
>  User: root
>  Password: (redacted)
>   Select_priv: Y
>   Insert_priv: Y
>   Update_priv: Y
>   Delete_priv: Y
>   Create_priv: Y
> Drop_priv: Y
>   Reload_priv: Y
> Shutdown_priv: Y
>  Process_priv: Y
> File_priv: Y
>Grant_priv: Y
>   References_priv: Y
>Index_priv: Y
>Alter_priv: Y
>  Show_db_priv: Y
>Super_priv: Y
> Create_tmp_table_priv: Y
>  Lock_tables_priv: Y
>  Execute_priv: Y
>   Repl_slave_priv: Y
>  Repl_client_priv: Y
>  ssl_type:
>ssl_cipher:
>   x509_issuer:
>  x509_subject:
> max_questions: 0
>   max_updates: 0
>   max_connections: 0
> 
> 
> db Table:
> *** 1. row ***
>  Host: %
>

MySql Error Number 1130

[Duzenbury, Rich]

Hi all,

I am setting up two additional instances of mysql on my mysql server,
which is running version 5.0.18-standard-log.

I've got the additional instances set up, and they are running.  I can
see that they are bound to the proper ports.  

I can connect to them locally like
mysql -p --port=3306
mysql -p --port=3307
mysql -p --port=3320

I have always been able to connect remotely from my workstation to the
base server on 3306.  

My problem is that I cannot connect remotely to either of the new
additional instances running on 3307 or 3320.  Anytime I attempt to do
so, I receive MySQL Error Number 1130, Host 'nnn.nnn.nnn.nnn' is not
allowed to connect to this MySQL server.  

I've been googling and reading manuals all day, and haven't made a dent.


User Table
*** 1. row ***
 Host: %
 User: root
 Password: (redacted)
  Select_priv: Y
  Insert_priv: Y
  Update_priv: Y
  Delete_priv: Y
  Create_priv: Y
Drop_priv: Y
  Reload_priv: Y
Shutdown_priv: Y
 Process_priv: Y
File_priv: Y
   Grant_priv: Y
  References_priv: Y
   Index_priv: Y
   Alter_priv: Y
 Show_db_priv: Y
   Super_priv: Y
Create_tmp_table_priv: Y
 Lock_tables_priv: Y
 Execute_priv: Y
  Repl_slave_priv: Y
 Repl_client_priv: Y
 ssl_type:
   ssl_cipher:
  x509_issuer:
 x509_subject:
max_questions: 0
  max_updates: 0
  max_connections: 0
*** 2. row ***
 Host: localhost
 User: root
 Password: (redacted)
  Select_priv: Y
  Insert_priv: Y
  Update_priv: Y
  Delete_priv: Y
  Create_priv: Y
Drop_priv: Y
  Reload_priv: Y
Shutdown_priv: Y
 Process_priv: Y
File_priv: Y
   Grant_priv: Y
  References_priv: Y
   Index_priv: Y
   Alter_priv: Y
 Show_db_priv: Y
   Super_priv: Y
Create_tmp_table_priv: Y
 Lock_tables_priv: Y
 Execute_priv: Y
  Repl_slave_priv: Y
 Repl_client_priv: Y
 ssl_type:
   ssl_cipher:
  x509_issuer:
 x509_subject:
max_questions: 0
  max_updates: 0
  max_connections: 0


db Table:
*** 1. row ***
 Host: %
   Db: mysql
 User: root
  Select_priv: Y
  Insert_priv: Y
  Update_priv: Y
  Delete_priv: Y
  Create_priv: Y
Drop_priv: Y
   Grant_priv: Y
  References_priv: Y
   Index_priv: Y
   Alter_priv: Y
Create_tmp_table_priv: Y
 Lock_tables_priv: Y

Host table is empty.

Here is one of the processes:
mysql 5972  5960  0 16:17 pts/000:00:00 /usr/sbin/mysqld
--no-defaults -
-port=3307 --socket=/srv/mysql/lx07/mysql.sock
--pid-file=/srv/mysql/lx07/lx09.p
id07 --datadir=/srv/mysql/lx07/data --log=/srv/mysql/lx07/mysql.log
--skip-locki
ng --key_buffer=256M --max_allowed_packet=16M --table_cache=256
--sort_buffer_si
ze=16M --read_buffer_size=8M --read_rnd_buffer_size=4M
--myisam_sort_buffer_size
=64M --thread_cache_size=8 --query_cache_size=16M --thread_concurrency=8
--tmpdi
r=/tmp/ --innodb_file_per_table --innodb_data_home_dir=/srv/mysql/lx07
--innodb_
data_file_path=ibdata1:100M:autoextend
--innodb_log_group_home_dir=/srv/mysql/lx
07/ --innodb_log_arch_dir=/srv/mysql/lx07/
--innodb_buffer_pool_size=128M --inno
db_additional_mem_pool_size=20M --innodb_log_file_size=32M
--innodb_log_buffer_s
ize=8M --innodb_flush_log_at_trx_commit=1 --innodb_lock_wait_timeout=50

I have made sure to flush privileges, and I've restarted the server
several times, to no avail.  I am running out of things to try, and
hoping someone can see my (what is most likely) obvious error.

Thank you.

Regards,
Rich

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

Re: Select a value between dates.

[gerald_clark]

George Law wrote:


This brings up a question I was asked...



Which is more efficient?



Select . where date between '-mm-dd hh:mm:ss' and '-mm-dd
hh:mm:ss'

 


This one.




Or



Select . where unix_timestamp(date) between
unix_timestamp('-mm-dd hh:mm:ss') and unix_timestamp('-mm-dd
hh:mm:ss')


 


This one cannot use an index.







 




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

RE: Select a value between dates.

[George Law]

This brings up a question I was asked...

 

Which is more efficient?

 

Select . where date between '-mm-dd hh:mm:ss' and '-mm-dd
hh:mm:ss'

 

Or

 

Select . where unix_timestamp(date) between
unix_timestamp('-mm-dd hh:mm:ss') and unix_timestamp('-mm-dd
hh:mm:ss')

 

 

 

Fw: Select a value between dates.

[Rhino]

I'm putting your question back on the mailing list where it belongs. That 
enables others to help and to learn from the discussion, either now or in 
the future via the archives.


--
Rhino

- Original Message - 
From: "H L" <[EMAIL PROTECTED]>

To: <[EMAIL PROTECTED]>
Sent: Wednesday, April 12, 2006 2:00 PM
Subject: Re: Select a value between dates.



>From: "Rhino" <[EMAIL PROTECTED]>

To: "H L" <[EMAIL PROTECTED]>,
Subject: Re: Select a value between dates. Date: Tue, 11 Apr 2006 
16:35:15 -0400



- Original Message - From: "H L" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, April 11, 2006 4:05 PM
Subject: Select a value between dates.


I am far from an mysql expert... but is there a way to select between 
dates in a table and check if a value exists in all fields between dates. 
If one day between those dates cannot be booked i dont want to get it in 
my SUM resultset.


I want to calculate Sum between those dates and i one idea i have is to 
use the count to determine if i can use the result but it does not feels 
right. ..


anyway the query looks like this

SELECT
`companyobjects`.`objectid`,
`companyobjects`.`objectname`,
Sum(`completereservationcredentials`.`price`),
Count(`completereservationcredentials`.`price`)
FROM
`completereservationcredentials`
Inner Join `companyobjects` ON 
`completereservationcredentials`.`objectid` = `companyobjects`.`objectid`

WHERE
`completereservationcredentials`.`avaliable` = 1 AND
`completereservationcredentials`.`reservationid` = 0 AND
`completereservationcredentials`.`thedate` >= '2006-08-01' AND
`completereservationcredentials`.`thedate` < '2006-08-10' AND
`companyobjects`.`companyid` = 'somecompanyid'
GROUP BY
`completereservationcredentials`.`objectid`
ORDER BY
`companyobjects`.`objectname` ASC


Your question and your SQL seem to be contradicting one another.

You seem to be asking whether it is possible to determine if a given date 
from a table lies between two arbitrary dates. Of course it can, as you 
demonstrate in your SQL:



`completereservationcredentials`.`thedate` >= '2006-08-01' AND
`completereservationcredentials`.`thedate` < '2006-08-10' AND


By the way, you might find it more concise to say:

`completereservationcredentials`.`thedate` between '2006-08-01' AND 
'2006-08-09'


(I subtracted one day from the original end date of the range to make sure 
that the August 10 was not chosen; your original query wanted the date 
only if was LESS THAN August 10. The between keyword always gets dates 
that in a given range and INCLUDES both ends of the range.)


You also talk about using sum() on days or dates but your query is 
actually summing prices, which is quite a different matter.


I think you need to clarify just what you want to know and ask a clearer 
question.


--
Rhino


Hi thanks!

Well in this case i want to query on the keys objectid and date to check 
if object can be booked or not.
If one of days between is booked i do not want to calculate price for any 
day which it does now. I want it to discard that object as it cannot be 
booked that period. I do not want a false sum result of price for 13 days 
instead of 14 days..


So, basically, you only want to return data if there are rows for each and 
every date in the date range?


If that is what you mean, I think you'll need to modify your WHERE to return 
rows only when every single day in the range is available for booking; if 
one or more days in the range are not available for booking, don't return 
any rows at all. An EXISTS might be handy for that problem.


Something like this might do the job, although I've never tried a query with 
multiple exists in it before and don't know if it will work the way I'm 
picturing:



WHERE
EXISTS (select * from completereservationcredentials where 
completereservationcredentials.thedate = '2006-08-01') AND
EXISTS (select * from completereservationcredentials where 
completereservationcredentials.thedate = '2006-08-02') AND

...
EXISTS (select * from completereservationcredentials where 
completereservationcredentials.thedate = '2006-08-10')


In other words, write a separate EXISTS subquery for each individual date 
within the range and be sure to connect each of the EXISTS with AND. The 
EXISTS query doesn't actually return any data: in an EXISTS query, the 
'SELECT *' really means "return a true/false flag".


I can't try this myself since I don't have a recent enough copy of MySQL - 
no subquery support in my version! - but it's worth a try.


It's pretty ugly though, especially if you have a date range of more than a 
very few days, since you'd have to write one subquery for each date in the 
range. There may well be a much easier way to do it but that's all I can 
think of offhand. Maybe someone else on the list can suggest a better 
approach.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.1/309 - Release Date: 11/04/2006


--
MySQL General Mai

Re: Looking for advice on how to store and query some data

[Shawn Green]

--- Russell Horn <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> I'm storing data against a bunch of people and want to track how it
> changes. So, I have a person table where everyone has a person ID and
> a
> results table a bit like this:
> 
> | personID | classification | date   |
> | 1| 0  | 2005-11-10 |
> | 2| 3  | 2005-11-10 |
> | 3| 1  | 2005-11-10 |
> | 4| 0  | 2005-11-10 |
> | 1| 3  | 2005-12-01 |
> | 4| 2  | 2005-12-03 |
> | 1| 2  | 2005-12-23 |
> | 5| 1  | 2006-01-03 |
> | 2| 2  | 2006-12-03 |
> 
> This lets me see how things change as a pattern, for example
> comparing a
> a 
> 
> SELECT classification WHERE DATE < '2006-01-01' GROUP BY
> classification
> 
> and comparing it to:
> 
> SELECT classification WHERE DATE >= '2006-01-01' AND DATE <=
> '2006-01-31' GROUP BY classification
> 
> But is there a way I can select every personID's most recently
> expressed
> preference?
> 
> I hope this makes sense - in the table above, person 1 had a
> classification of 0 at 10th November, but this changed to 2 on 23rd
> Decembner. Can I write a query to select personID once together with
> their latest preference, or indeed their preference as expressed at a
> specific point in time?
> 
> Thanks as ever for any suggestions.
> 
> Russell.
> 

Yet another variety of the FAQ -
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

In this case, what you are trying to maximize is the date per
person_id. It should be fairly easy for you to convert the examples to
match your situation.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

__
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: Best practices

[Shawn Green]

Answers intermingled below

--- Bruno B B Magalh�es <[EMAIL PROTECTED]> wrote:

> Hi guys I need some help with two things...
> 
> I have the following table:
> 
> CREATE TABLE `telephones` (
>`contact_id` int(20) unsigned NOT NULL default '0',
>`telephone_id` int(20) unsigned NOT NULL default '0',
>`telephone_country_code` char(5) NOT NULL default '',
>`telephone_area_code` char(5) NOT NULL default '',
>`telephone_number` char(20) NOT NULL default '',
>`telephone_extension` char(5) NOT NULL default '',
>`telephone_primary` int(1) unsigned NOT NULL default '0',
>`telephone_type_id` int(1) unsigned NOT NULL default '0',
>`telephone_inserted` datetime NOT NULL default '-00-00
> 00:00:00',
>`telephone_updated` datetime NOT NULL default '-00-00
> 00:00:00',
>`telephone_deleted` datetime NOT NULL default '-00-00
> 00:00:00'
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 
> As you can see I have a column called telephone_deleted, and I was  
> thinking about instead of deleting a record I would change the  
> telephone_delete with a valid date time when it happened. With that I
>  
> think it would avoid loosing records as a mistake, as it would just  
> need to reset the delete date and time. But my question is it  
> scalable? Any other ideas, as I am using "IS NOT NULL" to find the  
> records that haven't been deleted.
> 

What do you mean by "scalable"?  If you mean "can I use an index to
locate records that have or have not been deleted" the answer would be
yes.

> Another thing is how can I build a statistical analisys of  
> telephones, for example xx% belongs to country_code X and another xx%
>  
> belongs to country_code Y, but here's the trick part: I would like it
>  
> fetch it in a date range, for example what was the evolution between 
> 
> date X and date Y... I have this working now with a cronjob  
> performing a logging operation in a table like this which stores all 
> 
> statistics regarding every entity in the system:
> 
> CREATE TABLE `flx_contacts_stats` (
>`stat_date` date NOT NULL default '-00-00',
>`stat_entity` char(64) NOT NULL default '',
>`stat_key` char(128) NOT NULL default '0',
>`stat_value` int(10) unsigned NOT NULL default '0',
>KEY `stat_date` (`stat_date`,`stat_entity`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> 
> For example with this kind of data:
> 2005-12-04; phone_countrycodes; 55; 63
> 2005-12-04; phone_areacodes; 473, 32; 1
> 2005-12-04; phone_areacodes; 53, 32; 1
> 2005-12-04; phone_areacodes; 54, 32; 1
> 2005-12-04; phone_areacodes; 11, 55; 1
> 2005-12-04; phone_areacodes; 21, 55; 62
> 
> How can I do this on the fly without using any generic table to store
>  
> stats? I suspect that storing stats this way is not practical in  
> terms of portability and that's not definitely a good practice. Or  
> this kind of data is necessarily stored separated?
> 

If the data in the reports is truly static, then you GAIN performance
by only making your server compute it once. Storing the results of
statistical analysis is a commonly used practice when it comes to data
warehousing and OLAP system design. In your case, it especially makes
sense from a performance standpoint to query the smaller
`flx_contact_stats` table rather than to recompute those values every
time you need them.

> 
> Thanks in advance for any kind of advice in this matter.
> 
> Regards,
> Bruno B B Magalhaes
> 

Best Wishes!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

__
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: Return virtual records

[Shawn Green]

--- Ed Reed <[EMAIL PROTECTED]> wrote:

> Thanks for the response but neither one of the responses I've
> received does exactly what I need since they don't return multiple
> rows.
>  
> Are there any other ideas out there?
>  
> Thanks
> 
> >>> "Jay Blanchard" <[EMAIL PROTECTED]> 4/7/06 12:37:32 PM >>>
> [snip]
> Anyone have an idea on this?
> 
> Can anyone explain how I might be able to return a numbers of records
> based on the sum of a quantity in a field in the same table? (After I
> read that it sounds even confusing to me).
> 
> Let me explain. I have records like this,
> 
> Part# Qty
> 1254 5
> 1414 2
> 14758 1
> 1254 6
> 1024 3
> 1254 1
> 
> 
> Now if I did a query like this
> Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254
> 
> I would expect my results to look like this
> Part# Sum(Qty)
> 1254 12
> 
> But what I really want is this
> Part#
> 1254
> 1254
> 1254
> 1254
> 1254
> 12541254
> 1254
> 12541254
> 1254
> 1254
> 
> So 12 virtual records for the count of the records returned from the
> Sum()
> [/snip]
> 
> Please do not hijack threads, open a new e-mail and send it to the
> list
> address.
> 
> SELECT REPEAT(Part#, count(Part#)) FROM table WHERE Part# = '1254'
> 

What you are asking MySQL to do is not a normal request. There are
probably better ways to solve your issue than by creating "fake" or
"virtual" data. 

As was posted before, what is the real reason you want to auto-generate
separate rows of data? If we understood your REAL problem (not your
request, we understand that) we could probably help you find a faster,
more robust solution than the one you  are proposing.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

__
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]

Do foreign keys affect query speed?

[barney]

That's the whole question.

Do foreign keys (FKs) affect query speed?

'Course the answer could lead to sub-questions , e.g.,
"If so, how best to optimize a query for them?"

And I guess a corollary question would be whether implementing FKs slows down 
MySQL processing in general, but that can probably wait for another post.

Don't think it really matters for this question, but WinXP SP2, 3G cpu, 1G RAM, 
PHP v4.3.11, Apache v2.0.55,

Make a good day ...
 ... barn


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

Do foreign keys affect query speed?

[barney]

That's the whole question.

Do foreign keys (FKs) affect query speed?

'Course the answer could lead to sub-questions , e.g.,
"If so, how best to optimize a query for them?"

And I guess a corollary question would be whether implementing FKs slows down 
MySQL processing in general, but that can probably wait for another post.

Don't think it really matters for this question, but WinXP SP2, 3G cpu, 1G RAM, 
PHP v4.3.11, Apache v2.0.55, MySQL v4.1.14, and user v61.

Make a good day ...
 ... barn





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

MySQL 5.0.20 has been released

[Joerg Bruehe]

Hi,


MySQL 5.0.20, a new version of the popular Open Source Database
Management System, has been released. The Community Edition is now
available in source and binary form for a number of platforms from our
download pages at
   http://dev.mysql.com/downloads/ and mirror sites.

Note that not all mirror sites may be up to date at this point in time -
if you can't find this version on some mirror, please try again later or
choose another download site.

This is a bugfix release for the current production release family.

Please refer to our bug database at http://bugs.mysql.com/ for more
details about the individual bugs fixed in this version.

This section documents all changes and bug fixes that have been applied
since the last official MySQL release. If you would like to receive
more fine-grained and personalised update alerts about fixes that are
relevant to the version and features you use, please consider
subscribing to MySQL Network (a commercial MySQL offering). For more
details please see http://www.mysql.com/network/advisors.html.

We welcome and appreciate your feedback!


Functionality added or changed:
  * InnoDB: The InnoDB storage engine now provides a descriptive error
message if ibdata file information is omitted from my.cnf.
(Bug #16827 (http://bugs.mysql.com/16827))
  * The NDBCluster storage engine now supports INSERT IGNORE and REPLACE
statements. Previously, these statements failed with an error.
(Bug #17431 (http://bugs.mysql.com/17431))
  * Builds for Windows, Linux, and Unix (except AIX) platforms now have
SSL support enabled, in the server as well as in the client
libraries. Because part of the SSL code is written in C++, this does
introduce dependencies on the system's C++ runtime libraries in
several cases, depending on compiler specifics.
(Bug #18195 (http://bugs.mysql.com/18195))
  * The syntax for CREATE PROCEDURE and CREATE FUNCTION statements now
includes a DEFINER clause. The DEFINER value specifies the security
context to be used when checking access privileges at routine
invocation time if the routine has the SQL SECURITY DEFINER
characteristic. See Section 17.2.1, "CREATE PROCEDURE and CREATE
FUNCTION Syntax," for more information.
When mysqldump is invoked with the --routines option, it now dumps
the DEFINER value for stored routines.
(http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html)

Functionality added already in 5.0.19 (and documented),
repeated here just as a reminder:
  * Added the mysql_upgrade program that checks all tables for
incompatibilities with the current version of MySQL Server and
repairs them if necessary. This program should be run for each MySQL
upgrade (rather than mysql_fix_privilege_tables). See Section 5.6.2,
"mysql_upgrade --- Check Tables for MySQL Upgrade."
(http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html)

Bugs fixed:
  * Security Fix: Checks for permissions on database operations could
be performed in a case-insensitive manner (a user with permissions
on database MYDATABASE could by accident get permissions on database
myDataBase), if the privilege data were still cached from a previous
check. (Bug #17279 (http://bugs.mysql.com/17279))
  * InnoDB: The LATEST FOREIGN KEY ERROR section in the output of SHOW
INNODB STATUS was sometimes formatted incorrectly, causing problems
with scripts that parsed the output of this statement.
(Bug #16814 (http://bugs.mysql.com/16814))
  * When using ORDER BY with a non-string column inside GROUP_CONCAT()
the result's character set was converted to binary.
(Bug #18281 (http://bugs.mysql.com/18281))
See also Bug #14169 (http://bugs.mysql.com/14169).
  * SELECT ... WHERE column LIKE 'A%' when column had a key and used the
latin2_czech_cs collation.
(Bug #17374 (http://bugs.mysql.com/17374))
  * Complex queries with nested joins could cause a server crash.
(Bug #18279 (http://bugs.mysql.com/18279))
  * The server could deadlock under heavy load while writing to the
binary log. (Bug #18116 (http://bugs.mysql.com/18116))
  * A SELECT ... ORDER BY ... from a view defined using a function could
crash the server. An example of such a view might be CREATE VIEW AS
SELECT SQRT(c1) FROM t1. (Bug #18386 (http://bugs.mysql.com/18386))
  * A DELETE using a subquery could crash the server.
(Bug #18306 (http://bugs.mysql.com/18306))
  * REPAIR TABLE, OPTIMIZE TABLE, and ALTER TABLE operations on
transactional tables (or on tables of any type on Windows)
could corrupt triggers associated with those tables.
(Bug #18153 (http://bugs.mysql.com/18153))
  * MyISAM: Performing a bulk insert on a table referenced by a trigger
would crash the table. (Bug #17764 (http://bugs.mysql.com/17764))
  * MyISAM: Keys for which the first part of the key was a CHAR or
VARCHAR column using the UTF-8 character set and longer than 254
bytes could be

RE: Return virtual records

[Ed Reed]

Thanks for the response but neither one of the responses I've received does 
exactly what I need since they don't return multiple rows.
 
Are there any other ideas out there?
 
Thanks

>>> "Jay Blanchard" <[EMAIL PROTECTED]> 4/7/06 12:37:32 PM >>>
[snip]
Anyone have an idea on this?

Can anyone explain how I might be able to return a numbers of records
based on the sum of a quantity in a field in the same table? (After I
read that it sounds even confusing to me).

Let me explain. I have records like this,

Part# Qty
1254 5
1414 2
14758 1
1254 6
1024 3
1254 1


Now if I did a query like this
Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254

I would expect my results to look like this
Part# Sum(Qty)
1254 12

But what I really want is this
Part#
1254
1254
1254
1254
1254
12541254
1254
12541254
1254
1254

So 12 virtual records for the count of the records returned from the
Sum()
[/snip]

Please do not hijack threads, open a new e-mail and send it to the list
address.

SELECT REPEAT(Part#, count(Part#)) FROM table WHERE Part# = '1254'


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

RE: Error on T_echo ?? what is this ?

[Chrome]

D'oh! I copied the section and *still* missed the closing bracket lol

Well caught :)

Dan

 
---
http://chrome.me.uk
 

-Original Message-
From: George Law [mailto:[EMAIL PROTECTED] 
Sent: 12 April 2006 19:41
To: Chrome; Brian E Boothe; mysql@lists.mysql.com
Subject: RE: Error on T_echo ?? what is this ? 

PHP is picky on what can and cannot be in quotes... I get this all the
time.

Try : 
$sql = "INSERT INTO addvalue (`value1`, `value2`,
`sumfeild`) VALUES ('".$value1."', '".$value2."', '".$sumfield."')";

$result=MYSQL_QUERY($sql);

I think part of the error had to do with you missing a )  at the end,
closing the MYSQL_QUERY();



-Original Message-
From: Chrome [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 12, 2006 1:37 PM
To: 'Brian E Boothe'; mysql@lists.mysql.com
Subject: RE: Error on T_echo ?? what is this ? 

Looks like you're missing a semicolon (;) at the end of this statement:

$result=MYSQL_QUERY("INSERT INTO addvalue (`value1`, `value2`, 
`sumfeild`)"."VALUES ('$value1', '$value2', '$sumfield')"

HTH

Dan
 
---
http://chrome.me.uk
 

-Original Message-
From: Brian E Boothe [mailto:[EMAIL PROTECTED] 
Sent: 13 April 2006 05:13
To: mysql@lists.mysql.com
Subject: Error on T_echo ?? what is this ? 

i,m getting the following error on my MySQL Code inserting data into a 
database, .?

   *Parse error*: parse error, unexpected T_ECHO in 
c:\inetpub\wwwroot\projects\testsum\adddata.php on line 30
   
LINE 30 is   echo 
"Query Finished";

<<<<--here is ALL my code -->>>>>
  

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


__ NOD32 1.1485 (20060412) Information __

This message was checked by NOD32 antivirus system.
http://www.eset.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]


__ NOD32 1.1485 (20060412) Information __

This message was checked by NOD32 antivirus system.
http://www.eset.com



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

RE: Error on T_echo ?? what is this ?

[George Law]

PHP is picky on what can and cannot be in quotes... I get this all the
time.

Try : 
$sql = "INSERT INTO addvalue (`value1`, `value2`,
`sumfeild`) VALUES ('".$value1."', '".$value2."', '".$sumfield."')";

$result=MYSQL_QUERY($sql);

I think part of the error had to do with you missing a )  at the end,
closing the MYSQL_QUERY();



-Original Message-
From: Chrome [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, April 12, 2006 1:37 PM
To: 'Brian E Boothe'; mysql@lists.mysql.com
Subject: RE: Error on T_echo ?? what is this ? 

Looks like you're missing a semicolon (;) at the end of this statement:

$result=MYSQL_QUERY("INSERT INTO addvalue (`value1`, `value2`, 
`sumfeild`)"."VALUES ('$value1', '$value2', '$sumfield')"

HTH

Dan
 
---
http://chrome.me.uk
 

-Original Message-
From: Brian E Boothe [mailto:[EMAIL PROTECTED] 
Sent: 13 April 2006 05:13
To: mysql@lists.mysql.com
Subject: Error on T_echo ?? what is this ? 

i,m getting the following error on my MySQL Code inserting data into a 
database, .?

   *Parse error*: parse error, unexpected T_ECHO in 
c:\inetpub\wwwroot\projects\testsum\adddata.php on line 30
   
LINE 30 is   echo 
"Query Finished";

<<<<--here is ALL my code -->>>>>
  

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


__ NOD32 1.1485 (20060412) Information __

This message was checked by NOD32 antivirus system.
http://www.eset.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]

Re: Fulltext and reserved words

[Dan Nelson]

In the last episode (Apr 12), Taco Fleur said:
> I am in need to search for sql reserved words with MATCH AGAINST it
> turns it doesn't return anything when I feed it a reserved word like
> 'sql' is there anything I can do about that?

By default the full-text indexer skips words less than four characters
long.  If you set ft_min_word_len=3 and rebuild your index, "sql" should
get indexed.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html

-- 
Dan Nelson
[EMAIL PROTECTED]

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

RE: Error on T_echo ?? what is this ?

[Chrome]

Looks like you're missing a semicolon (;) at the end of this statement:

$result=MYSQL_QUERY("INSERT INTO addvalue (`value1`, `value2`, 
`sumfeild`)"."VALUES ('$value1', '$value2', '$sumfield')"

HTH

Dan
 
---
http://chrome.me.uk
 

-Original Message-
From: Brian E Boothe [mailto:[EMAIL PROTECTED] 
Sent: 13 April 2006 05:13
To: mysql@lists.mysql.com
Subject: Error on T_echo ?? what is this ? 

i,m getting the following error on my MySQL Code inserting data into a 
database, .?

   *Parse error*: parse error, unexpected T_ECHO in 
c:\inetpub\wwwroot\projects\testsum\adddata.php on line 30
   
LINE 30 is   echo 
"Query Finished";

<<<<--here is ALL my code -->>>>>
  

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


__ NOD32 1.1485 (20060412) Information __

This message was checked by NOD32 antivirus system.
http://www.eset.com



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

Re: Error on T_echo ?? what is this ?

[Peter Brawley]

Brian E Boothe wrote:
i,m getting the following error on my MySQL Code inserting data into a 
database, .?


  *Parse error*: parse error, unexpected T_ECHO in 
c:\inetpub\wwwroot\projects\testsum\adddata.php on line 30
 LINE 30 is   
echo "Query Finished";

Wrong list.

The echo is unexpected because you're missing a semicolon at the end of 
the previous line. Also should 'sumfeild' be 'sumfield'?


PB


--here is ALL my code -->
  // $result=MYSQL_QUERY("INSERT INTO orders 
(id,title,message,who,date,time)".
 $result=MYSQL_QUERY("INSERT INTO addvalue (`value1`, `value2`, 
`sumfeild`)".

 "VALUES ('$value1', '$value2', '$sumfield')"
//INSERT INTO `orders` (`OrderNo`, `CompanyName`, 
`BillingAddress`, `City`, `StateOrProvince`, `PostalCode`, 
`PhoneNumber`, `FaxNumber`, `WebPage`, `ContactFirstName`, 
`ContactLastName`, `EmailAddress`, `Notes`, `Customer`, `Startdate`, 
`Completedate`, `Biddate`, `Bidamount`, `ProjectInfo`, `ElecProjCost`, 
`ElecProjBill`, `ElecRem`, `CtrlProjCost`, `CtrlProjBill`, `CtrlRem`, 
`OthrProjCost`, `OthrProjBill`, `OthrRem`, `BondAm`, `BondBill`, 
`BondRem`)

   //confirm

echo "Query Finished";

?>




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.1/309 - Release Date: 4/11/2006


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

Re: Error on T_echo ?? what is this ?

[Eric Braswell]

This is not a PHP list and that's a PHP error.

But you are missing a semi-colon at the end of the line "...`BondRem`)".

Eric

Brian E Boothe wrote:
i,m getting the following error on my MySQL Code inserting data into a 
database, .?



`OthrProjBill`, `OthrRem`, `BondAm`, `BondBill`, `BondRem`)
   //confirm

echo "Query Finished";

?>






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

RE: Error on T_echo ?? what is this ?

[Jason Teagle]

I believe you are missing the trailing semicolon from the previous line of
code, before the "//confirm" comment. It's a rather cryptic way of saying it
found "echo" when it expected something else first. Gotta love those PHP
error messages.

--
Jason Teagle
[EMAIL PROTECTED]


> -Original Message-
> From: Brian E Boothe [mailto:[EMAIL PROTECTED]
> Sent: 13 April 2006 05:13
> To: mysql@lists.mysql.com
> Subject: Error on T_echo ?? what is this ?
>
>
> i,m getting the following error on my MySQL Code inserting data into a
> database, .?
>
>*Parse error*: parse error, unexpected T_ECHO in
> c:\inetpub\wwwroot\projects\testsum\adddata.php on line 30
>
> LINE 30 is   echo
> "Query Finished";
>
> --here is ALL my code -->
>//--php/Mysql Code by Brian E Boothe  //
> //throw data from form into MySQL database routine //
> //initilize Adddatta to mysql database, //
>
> //if($_POST['submit']) //If submit is hit
> //{
>//then connect as user
>//change user and password to your mySQL name and password
>mysql_connect("localhost","root","goobers");
>
>//select which database you want to edit
>mysql_select_db("test");
>
>//convert all the posts to variables:
>   $value1 = $_POST['value1'];
>   $value2 = $_POST['value2'];
>   $sumfield = $_POST['sumfield'];
>
>//Insert the values into the correct database with the right fields
>//mysql table = news
>//table columns = id, title, message, who, date, time
>//post variables = $title, $message, '$who, $date, $time
>   // $result=MYSQL_QUERY("INSERT INTO orders
> (id,title,message,who,date,time)".
>   $result=MYSQL_QUERY("INSERT INTO addvalue (`value1`, `value2`,
> `sumfeild`)".
>   "VALUES ('$value1', '$value2', '$sumfield')"
>  //INSERT INTO `orders` (`OrderNo`, `CompanyName`, `BillingAddress`,
> `City`, `StateOrProvince`, `PostalCode`, `PhoneNumber`, `FaxNumber`,
> `WebPage`, `ContactFirstName`, `ContactLastName`, `EmailAddress`,
> `Notes`, `Customer`, `Startdate`, `Completedate`, `Biddate`,
> `Bidamount`, `ProjectInfo`, `ElecProjCost`, `ElecProjBill`, `ElecRem`,
> `CtrlProjCost`, `CtrlProjBill`, `CtrlRem`, `OthrProjCost`,
> `OthrProjBill`, `OthrRem`, `BondAm`, `BondBill`, `BondRem`)
> //confirm
>
>  echo "Query Finished";
>
> ?>
>
> --
> 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: Error on T_echo ?? what is this ?

[Jay Blanchard]

[snip]
i,m getting the following error on my MySQL Code inserting data into a 
database, .?

   *Parse error*: parse error, unexpected T_ECHO in 
c:\inetpub\wwwroot\projects\testsum\adddata.php on line 30
   
LINE 30 is   echo 
"Query Finished";

--here is ALL my code -->
  

It is a PHP error, not MySQL. You didn't end your line (;) beginning
with "VALUES(

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

Error on T_echo ?? what is this ?

[Brian E Boothe]

i,m getting the following error on my MySQL Code inserting data into a 
database, .?


  *Parse error*: parse error, unexpected T_ECHO in 
c:\inetpub\wwwroot\projects\testsum\adddata.php on line 30
  
   LINE 30 is   echo 
"Query Finished";


--here is ALL my code -->
   
  //select which database you want to edit

  mysql_select_db("test");

  //convert all the posts to variables:
 $value1 = $_POST['value1'];
 $value2 = $_POST['value2'];
 $sumfield = $_POST['sumfield'];

  //Insert the values into the correct database with the right fields
  //mysql table = news
  //table columns = id, title, message, who, date, time
  //post variables = $title, $message, '$who, $date, $time
 // $result=MYSQL_QUERY("INSERT INTO orders 
(id,title,message,who,date,time)".
 $result=MYSQL_QUERY("INSERT INTO addvalue (`value1`, `value2`, 
`sumfeild`)".

 "VALUES ('$value1', '$value2', '$sumfield')"
//INSERT INTO `orders` (`OrderNo`, `CompanyName`, `BillingAddress`, 
`City`, `StateOrProvince`, `PostalCode`, `PhoneNumber`, `FaxNumber`, 
`WebPage`, `ContactFirstName`, `ContactLastName`, `EmailAddress`, 
`Notes`, `Customer`, `Startdate`, `Completedate`, `Biddate`, 
`Bidamount`, `ProjectInfo`, `ElecProjCost`, `ElecProjBill`, `ElecRem`, 
`CtrlProjCost`, `CtrlProjBill`, `CtrlRem`, `OthrProjCost`, 
`OthrProjBill`, `OthrRem`, `BondAm`, `BondBill`, `BondRem`)

   //confirm

echo "Query Finished";

?>

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

Re: Can't search words of three letters?

[afan]

I ca try that one too.
Since, I'm bad with reg expressions, what would be solution for something
like "match searched word and it's plural - if one word entred, or match
all words - if more words entered"?

thanks

-afan


> Why not try regex?
>
> http://dev.mysql.com/doc/refman/4.1/en/regexp.html
>
> On 4/12/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>> Hi to all,
>> I have to build a search of products on web catalog site. It has to
>> search
>> a whole words to avoid when somebody search for a pin and get "*pin*k
>> shirt".
>> I was suggested to use MATCH() AGAINST() and it works perfect - until I
>> dscovered that I'm getting alwayes 0 results if search for cap, pen, mug
>> -
>> three-letter words. Since, these are one of the most searched words (we
>> are in promotin industry) it's a big problem for me.
>> I found on http://dev.mysql.com/doc/refman/4.1/en/fulltext-boolean.html
>> that the default value of minimum character length is 4?!?!?
>>
>> Does anybody solved this problem?
>> is there any other solution to suggest?
>>
>> Thanks for any help.
>>
>> -afan
>>
>>
>> --
>> 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 search words of three letters?

[Shen139]

On 4/12/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> Hi to all,


hi

I have to build a search of products on web catalog site. It has to search
> a whole words to avoid when somebody search for a pin and get "*pin*k
> shirt".
> I was suggested to use MATCH() AGAINST() and it works perfect - until I
> dscovered that I'm getting alwayes 0 results if search for cap, pen, mug -
> three-letter words. Since, these are one of the most searched words (we
> are in promotin industry) it's a big problem for me.
> I found on http://dev.mysql.com/doc/refman/4.1/en/fulltext-boolean.html
> that the default value of minimum character length is 4?!?!?
>
> Does anybody solved this problem?
> is there any other solution to suggest?


You should simply add this line to your mysql configuration file:
ft_min_word_len=3
and then you should rebuild the index of your table with:
REPAIR TABLE  QUICK

Thanks for any help.
>
> -afan
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


--
http://www.openwebspider.org
http://www.eviltime.com

-

" Time is what we want most, but what we use worst "

Re: Can't search words of three letters?

[Paul Halliday]

Why not try regex?

http://dev.mysql.com/doc/refman/4.1/en/regexp.html

On 4/12/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Hi to all,
> I have to build a search of products on web catalog site. It has to search
> a whole words to avoid when somebody search for a pin and get "*pin*k
> shirt".
> I was suggested to use MATCH() AGAINST() and it works perfect - until I
> dscovered that I'm getting alwayes 0 results if search for cap, pen, mug -
> three-letter words. Since, these are one of the most searched words (we
> are in promotin industry) it's a big problem for me.
> I found on http://dev.mysql.com/doc/refman/4.1/en/fulltext-boolean.html
> that the default value of minimum character length is 4?!?!?
>
> Does anybody solved this problem?
> is there any other solution to suggest?
>
> Thanks for any help.
>
> -afan
>
>
> --
> 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]

Can't search words of three letters?

[afan]

Hi to all,
I have to build a search of products on web catalog site. It has to search
a whole words to avoid when somebody search for a pin and get "*pin*k
shirt".
I was suggested to use MATCH() AGAINST() and it works perfect - until I
dscovered that I'm getting alwayes 0 results if search for cap, pen, mug -
three-letter words. Since, these are one of the most searched words (we
are in promotin industry) it's a big problem for me.
I found on http://dev.mysql.com/doc/refman/4.1/en/fulltext-boolean.html
that the default value of minimum character length is 4?!?!?

Does anybody solved this problem?
is there any other solution to suggest?

Thanks for any help.

-afan


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

RE: FW: New to TRIGGER and CALL. Example gives errors. (repost)

[Shawn Green]

I don't have a 5.0+ server to test with right now but this should work

--- Daevid Vincent <[EMAIL PROTECTED]> wrote:

> I was using SQLYog 5.03 RC1.
> 
> vmware ~ # mysql --version
> mysql  Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using
> readline 5.1
> 
> But just to sanity check. I ssh'd in and tried this at the mysql
> command
> line utility:
> 
> vmware ~ # mysql somedatabase
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
> 
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 415 to server version: 5.0.19-log
> 
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> 
> mysql> delimiter //
> mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys
> -> FOR EACH ROW
> -> BEGIN
> -> IF NEW.skey < 1 THEN
> -> 
> Display all 187 possibilities? (y or n)
> -> EW.skey = 1;

You need a SET command here. That's how MySQL's SQL does assignments.
That's also the wrong name, you need the table name of NEW not EW

SET NEW.skey = 1;

> -> ELSEIF NEW.skey > 9 THEN
> -> 
> Display all 187 possibilities? (y or n)
> -> EW.skey = 9;

Same comment here:
SET NEW.skey = 9

> -> END IF;
> -> END;//

(Same comment as from other thread.) No ; after the END or END IF
statements
http://dev.mysql.com/doc/refman/5.0/en/begin-end.html
http://dev.mysql.com/doc/refman/5.0/en/flow-control-constructs.html

> delimiter ;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the
> manual
> that corresponds to your MySQL server version for the right syntax to
> use
> near '.skey = 1;
> ELSEIF NEW.skey > 9 THEN
> EW.skey = 9;
> END IF;
> END' at line 5
> mysql> delimiter ;
> mysql> 
> 
>

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

__
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]

FW: Mysql C API character set

[He, Gang]

 

 



From: He, Gang 
Sent: Wednesday, April 12, 2006 8:43 PM
To: '[EMAIL PROTECTED]'
Subject: Mysql C API character set

 

Hi,

 

I install MySQL server 4.1.12 in Redhat Linux in Japanese environment (
env LANG=ja_JP.UTF-8 ), 

I setup MySQL server utf8 character set, the configuration is as
follows:

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Default to using old password format for compatibility with mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

old_passwords=1

default-character-set=utf8

 

[mysql.server]

user=mysql

basedir=/var/lib

 

[mysqld_safe]

err-log=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

Then, I start MySQL server, and type mysql command, the result is as
follows:

mysql> show variables like 'char%';

+--++

| Variable_name| Value  |

+--++

| character_set_client | utf8   |

| character_set_connection | utf8   |

| character_set_database   | utf8   |

| character_set_results| utf8   |

| character_set_server | utf8   |

| character_set_system | utf8   |

| character_sets_dir   | /usr/local/mysql/share/mysql/charsets/ |

+--++

7 rows in set (0.00 sec)

 

But, I write a sample code using MySQL C API 4.1.10, the code is as
follows:

int main(int argc, char* argv[])

{

MYSQLm_conn;

MYSQL_RES   *res;

MYSQL_ROW   row;

 

mysql_init(&m_conn);

mysql_real_connect(&m_conn, "localhost", "root", "123456", "mysql",
0, NULL, 0);

 

mysql_query(&m_conn, "show variables like 'character_set_%'");

res=mysql_store_result(&m_conn);

while((row=mysql_fetch_row(res))!=NULL)

{

   printf(row[0]); printf("\t"); printf(row[1]);

   printf("\n");

}

 

mysql_free_result(res);

mysql_close(&m_conn);

return 0;

}

The output result is as follows:

character_set_clientlatin1

character_set_connectionlatin1

character_set_database  utf8

character_set_results   latin1

character_set_serverutf8

character_set_systemutf8

character_sets_dir  /usr/local/mysql/share/mysql/charsets/

 

I want to why character_set_client , character_set_connection and
character_set_results in the output result be latin1, not utf8 ?

If I want to change MySQL C API default value when the application call
mysql_real_connect() function, what I should do ?

Can I change MySQL C API default value via modifying '/etc/my.cnf '
configuration file ? ( I think that the best way is to modify
configuration file )

 

 

Thanks

Gang

 

 

 

 

 

Fulltext and reserved words

[Taco Fleur]

I am in need to search for sql reserved words with MATCH AGAINST
it turns it doesn't return anything when I feed it a reserved word like
'sql'
is there anything I can do about that?
 
Thanks in advance
 
Kind regards,
 

Taco Fleur 

Free Call 1800 032 982 or Mobile 0421 851 786
Pacific Fox   http://www.pacificfox.com.au an
industry leader with commercial IT experience since 1994 .

*   

Web Design and Development 
*   

SMS Solutions, including developer API
*   

Domain Registration, .COM for as low as fifteen dollars a year,
.COM.AU for fifty dollars two years!

 

RE: FW: New to TRIGGER and CALL. Example gives errors. (repost)

[Gordon]

REMOVE the semicolon ";" from " END;//"

SQLyog has a problem with all of the procedures, functions and triggers RE
the DELIMITER syntax.

> -Original Message-
> From: Daevid Vincent [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 11, 2006 10:00 PM
> To: mysql@lists.mysql.com
> Cc: 'Shawn Green'
> Subject: RE: FW: New to TRIGGER and CALL. Example gives errors. (repost)
> 
> I was using SQLYog 5.03 RC1.
> 
> vmware ~ # mysql --version
> mysql  Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline
> 5.1
> 
> But just to sanity check. I ssh'd in and tried this at the mysql command
> line utility:
> 
> vmware ~ # mysql somedatabase
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
> 
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 415 to server version: 5.0.19-log
> 
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> 
> mysql> delimiter //
> mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys
> -> FOR EACH ROW
> -> BEGIN
> -> IF NEW.skey < 1 THEN
> ->
> Display all 187 possibilities? (y or n)
> -> EW.skey = 1;
> -> ELSEIF NEW.skey > 9 THEN
> ->
> Display all 187 possibilities? (y or n)
> -> EW.skey = 9;
> -> END IF;
> -> END;//
> delimiter ;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near '.skey = 1;
> ELSEIF NEW.skey > 9 THEN
> EW.skey = 9;
> END IF;
> END' at line 5
> mysql> delimiter ;
> mysql>
> 
> 
> > -Original Message-
> > From: Shawn Green [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, April 11, 2006 12:41 PM
> > To: Daevid Vincent; mysql@lists.mysql.com
> > Subject: Re: FW: New to TRIGGER and CALL. Example gives
> > errors. (repost)
> >
> >
> >
> > --- Daevid Vincent <[EMAIL PROTECTED]> wrote:
> >
> > > This may have been lost, so I'm reposting hoping for a clue
> > as to why
> > > the
> > > mySQL example onlie gives me errors...
> > > -Original Message-
> > > Sent: Sunday, April 09, 2006 7:41 PM
> > >
> > > I'm trying to follow the example in the manual to create a trigger:
> > > http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html
> > >
> > > #DROP TRIGGER upd_check;
> > > delimiter //
> > > CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys
> > > FOR EACH ROW
> > > BEGIN
> > >   IF NEW.skey < 1 THEN
> > >   SET NEW.skey = 1;
> > >   ELSEIF NEW.skey > 9 THEN
> > >   SET NEW.skey = 9;
> > >   END IF;
> > > END;//
> > > delimiter ;
> > >
> > > All I'm trying to do is enforce that my starkeys.skey column is
> > > always in
> > > the range of 1 through 9. I was planning to start with this example
> > > and work
> > > my way up. Ideally it should check on UPDATE or INSERT. The manual
> > > recommended:
> > >
> > > "It can be easier to define a stored procedure separately and then
> > > invoke it
> > > from the trigger using a simple CALL statement. This is also
> > > advantageous if
> > > you want to invoke the same routine from within several triggers."
> > >
> > > But I don't know how to do that yet.
> > >
> > > vmware public_html # mysql --version
> > > mysql  Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using
> > > readline 5.1
> > >
> > > But I just get these errors:
> > >
> > > Error Code : 1064
> > > You have an error in your SQL syntax; check the manual that
> > > corresponds to
> > > your MySQL server version for the right syntax to use near
> > 'delimiter
> > > //
> > > CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys
> > > FOR EACH ROW
> > > BEG' at line 2
> > > (0 ms taken)
> > >
> > > Error Code : 1064
> > > You have an error in your SQL syntax; check the manual that
> > > corresponds to
> > > your MySQL server version for the right syntax to use near 'ELSEIF
> > > NEW.skey
> > > > 9 THEN
> > >   SET NEW.skey = 9' at line 1
> > > (0 ms taken)
> > >
> > > Error Code : 1064
> > > You have an error in your SQL syntax; check the manual that
> > > corresponds to
> > > your MySQL server version for the right syntax to use near 'END IF'
> > > at line
> > > 1
> > > (0 ms taken)
> > >
> > > Error Code : 1064
> > > You have an error in your SQL syntax; check the manual that
> > > corresponds to
> > > your MySQL server version for the right syntax to use near 'END' at
> > > line 1
> > > (0 ms taken)
> > >
> > > Error Code : 1064
> > > You have an error in your SQL syntax; check the manual that
> > > corresponds to
> > > your MySQL server version for the right syntax to use near '//
> > > delimiter' at line 1
> > > (0 ms taken)
> > >
> >
> > This looks suspiciously like an interface issue, not a coding issue.
> > How are you delivering these commands to your MySQL server and is it
> > v5.0 or newer?
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> 
> 
> --
> MySQL General Mailing List
> For list archives

RE: counting keywords

[Steve Edberg]

At 1:55 PM +1000 4/12/06, Taco Fleur wrote:

Hello Steve,

Your suggestion works like a charm, I am now trying to get my head around
your following statement, I can't seem to get a grip on what you mean. Is
there anyway you could elaborate a little on the following? It would be
really nice if I could get the solution to work with full text and be able
to use stemming etc.



 where document_id is a foreign key pointing at the table containing your

fulltext. This would be easier to extend to handle synonym handling too, and
you could do all the suffix handling/stemming you need (eg; to take care of
plurals). I've done something like that as well, and included an extra field
for the metaphone version of the word, to match approximate spellings.



Assume table structures something like this:

   WordTable:
   wordchar(32) not null # or whatever your max word 
length is likely to be

   word_count  integer unsigned not null
   document_id integer unsigned not null

   DocTable:
   document_id integer unsigned not null auto_increment primary key
   doc_author
   doc_date
   ...
   doc_bodytext

In your original post, you needed to get exact counts of words 
appearing in the document body; MySQL's full text search can't do 
that. What you could do in this case is some preprocessing on the 
doc_body when you insert it. You would scan the text, ignoring the 
insignificant words (eg; and, the, a, is, and so on), and then 
transform the remaining words into a canonical form (eg; glasses, 
glassy, glass all become glass), and then insert into the WordTable. 
Then, when a search is performed, you translate the search terms 
using the same algorithm and search the WordTable. You might also 
have a SynonymTable that you could use to translate all synonyms to a 
standard term before insertion into the WordTable and before 
searching.


There are various stemming algorithms around; the Porter Algorithm 
was one of the earlier ones, and the one I have worked with some:


   http://www.tartarus.org/martin/PorterStemmer/

It worked pretty well, but I needed to maintain an exception list for 
some words it incorrectly translated. Also, you have to decide 
whether similar terms like 'anthropologist' and 'anthropology' are 
identical as far as your search is concerned.


The Snowball or Porter2 algorithm is apparently an improvement on the original:

   http://snowball.tartarus.org/algorithms/english/stemmer.html

A google for 'stemming algorithms' also turned up this:

   http://www.comp.lancs.ac.uk/computing/research/stemming/

And if you're dealing with non-English words, then you'll have to 
look for native language stemmers or modify the rules in the above 
algorithms.


If you had a table set up as above, you could do either searches 
based on the word table (where you could return documents sorted by 
the number of times the search words appeared), or using a fulltext 
search on the doc_body. I would imagine that for many documents the 
order of results would be similar. The fulltext algorithm also 
weights words more heavily based on their uniqueness.


If you want to do searching based on approximate spellings, you could 
add an additional column to WordTable:


   word_approxchar(32) not null

and store either the soundex version of the word -

   http://en.wikipedia.org/wiki/Soundex

- or the more accurate (in my experience) metaphone algorithm -

   http://en.wikipedia.org/wiki/Metaphone

Then the search terms would be run through two transforms: a stemmer, 
and a 'metaphoner'.


Lastly, it appears that MySQL 5.1 has a new plug-in API -

   http://dev.mysql.com/doc/refman/5.1/en/plugin-api.html

- which includes the ability to modify/replace fulltext parser 
behavior. It looks like you might be able to create custom functions 
to do most or all of the above using user-defined functions, 
presumably with relatively high efficiency.


steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: mySQL Backups

[Terry Burton]

On 4/12/06, Tim Lucia <[EMAIL PROTECTED]> wrote:
> Would you not lock tables on the slave?  The idea of catching it up implies
> this is way it is done.  Catching up means once replication can proceed once
> the tables are unlocked (on the slave).
>
> At least that is the way I read it...

On the slave I perform a slave stop, (optional) lock tables,
mysqldump, slave start. Since the slave is a dedicated backup
instance, and is never connected to directly by applications, the
backup is entirely transparent to database users. My suggestion is for
the purpose of simplifying such a setup such that it can be performed
from a single MySQL instance.


Tez

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

RE: [SOLVED] Making result rows to one row

[Peter Lauri]

Yes, you are correct. I tried it but I got some errors. I simplified my
query and tried it, and it worked. Then I added the more complicated parts
after that, and it works VERY good.

This is what makes life worth living :)

-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 11, 2006 9:55 PM
To: Peter Lauri
Cc: mysql@lists.mysql.com
Subject: Re: Making result rows to one row

Peter,

> Peter Brawley said:
>
> SELECT 
>   ..., 
>   GROUP CONCAT(LPAD(strokes,3,' ') SEPARATOR '') AS '  1  2  3  4  5  6  7
> 8  9'
> FROM  tour_player_score tps
> INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id
> WHERE tour_player_id=175
> GROUP BY tsh.id
>
> This worked, however, it gives me all results in one cell. I would like to
> have them sorted in one cell per strokes. Is that possible? 
Sure, that's what the pivot table example at 
http://www.artfulsoftware.com/queries.php#36 does. Your query would be 
something like...

SELECT player_id, 
MAX( IF(hole=1,strokes,'') ) AS 'Hole 1'
MAX( IF(hole=2,strokes,'') ) AS 'Hole 22, 
...
FROM tbl
GROUP BY player_id ...


PB


> The reason for
> this is that I have to use each hole as ORDER criteria. If the SUM is
equal,
> then I have to check the sum of the last nine holes. If they are the same,
> then I have to check the result of hole 18, 17, 16 etc.
>
> This is my exact query as is now:
>
> SELECT tps.tour_player_id, GROUP_CONCAT(LPAD(strokes,2,' ') SEPARATOR '-')
> AS hole_scores, sum(tps.strokes) AS 'score' FROM tour_player_score tps
INNER
> JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id GROUP BY
> tps.tour_player_id ORDER BY score;
>
> Best regards,
> Peter Lauri
>
>
>
>   


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.0/306 - Release Date: 4/9/2006


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