RE: problem with 4.0.18
Andrea Riela wrote: Could you help me? well, I've tryed with ktrace, see my kdump: http://www.nesys.it/kdump It's possible, a pid file problem? thanks for your support Regards Andrea -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing utf-8 on MySQL 3.23.58, and preparing for MySQL 4.1
MySQL Listers, Currently I am running MySQL 3.23, which I know is not really designed to handle utf-8. I am eagerly anticipating full utf-8 support in version 4.1. In the meantime, I am curious to know if there are any particular considerations I should take into account when storing utf-8 data in my current database which will make the transition to MySQL 4.1 as smooth as possible. I am new to databasing, and I have never upgraded a database before. In any case, my sites are hosted on a virtual server, where I have little control over the database settings. By reading the archives of this list and looking around on the net, I'm led to believe that it is possible to store utf-8 strings in TEXT columns, and that the only sacrifice is that sorting won't really work. Can I also store utf-8 strings in CHAR and VARCHAR columns? Up until now, I've been storing text as binary data. But in 4.1 it will be possible to designate different columns as different encoding sets. Will there be problems in going from a BLOB column to a utf-8 TEXT column? Will it be any better for me to store my current utf-8 data as TEXT columns and then in 4.1 assign encoding? Will that be possible? Any advice would be greatly appreciated. Thank you. -- 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]
Problem with regexp
Hi,
I'm trying to get a particular pattern to match, and a pattern that
works in Python and other languages is not working in MySQL. I'm using
4.1.0 on RH Linux.
I archive messages from an email list, and we have a standard that
posts to the list that are Off Topic should be labeled by including
'[OT]' in the subject. However, some people are lazy, and type '(OT)',
or '{ot}', etc. I'd like to be able to filter off-topic messages in the
archives, and so a regexp that catches all these variants is what I'm
looking for.
In some python scripts on the site, I use the following pattern:
'[\[\(\{ ][Oo][Tt][ \]\}\)]'. That means an Open bracket/paren/brace or
space, followed by 'ot' in either case, followed by a closing
bracket/paren/brace or space.
Running this in Python, I get a match:
pat = '([\(][Oo][Tt][ \]\)])'
tx = 'This is an (OT) Test'
re.search(pat, tx).groups()
('(OT)',)
But in MySQL, I don't match:
mysql select 'This is an (OT) Test' regexp '[\(][Oo][Tt][ \]\)]' as
mtch;
+--+
| mtch |
+--+
|0 |
+--+
1 row in set (0.00 sec)
Can anyone see the problem here?
___/
/
__/
/
/
Ed Leafe
Linux Love:
unzip;strip;touch;finger;mount;fsck;more;yes;umount;sleep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with regexp
Ed Leafe wrote:
Hi,
I'm trying to get a particular pattern to match, and a pattern that
works in Python and other languages is not working in MySQL. I'm using
4.1.0 on RH Linux.
I archive messages from an email list, and we have a standard that
posts to the list that are Off Topic should be labeled by including
'[OT]' in the subject. However, some people are lazy, and type '(OT)',
or '{ot}', etc. I'd like to be able to filter off-topic messages in the
archives, and so a regexp that catches all these variants is what I'm
looking for.
In some python scripts on the site, I use the following pattern:
'[\[\(\{ ][Oo][Tt][ \]\}\)]'. That means an Open bracket/paren/brace or
space, followed by 'ot' in either case, followed by a closing
bracket/paren/brace or space.
Running this in Python, I get a match:
pat = '([\(][Oo][Tt][ \]\)])'
tx = 'This is an (OT) Test'
re.search(pat, tx).groups()
('(OT)',)
But in MySQL, I don't match:
mysql select 'This is an (OT) Test' regexp '[\(][Oo][Tt][ \]\)]' as mtch;
+--+
| mtch |
+--+
|0 |
+--+
1 row in set (0.00 sec)
Can anyone see the problem here?
___/
/
__/
/
/
Ed Leafe
Linux Love:
unzip;strip;touch;finger;mount;fsck;more;yes;umount;sleep
Unless your string column is defined as binary (or your mysql is 3.23.4
or earlier), pattern matching is case insensitive, so you can just look
for OT instead of [Oo][Tt]. That's not the problem here, though.
According to the manual (http://www.mysql.com/doc/en/Pattern_matching.html
and http://www.mysql.com/doc/en/Regexp.html), you have to double your
backslashes because of the way the pattern is parsed. I couldn't get
that to work either, though. Maybe after my coffee kicks in...
In the meantime, perhaps the following will help:
mysql select 'This is an [OT] Test'
regexp [ [:punct:]]OT[ [:punct:]] as mtch;
+--+
| mtch |
+--+
|1 |
+--+
1 row in set (0.00 sec)
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't enable replication !
Hi guys - I am trying to set up replication but cannot get it enabled on the MASTER server - I have added the bin-log line in my.conf and restarted the server - no logs are created tho - Ther are no unusual message in mysql.err - and the following ' 4 test 3306 60 ' is the only entry in the master.info file - Any ideas would most excellent ! cheers Mike Rabbitt I.T. Applications Manager Legal Advice Bureau : 0845-055-0505 http://www.legaladvicebureau.co.uk This email and any files transmitted with it are confidential and intended solely for the attention and use of the individual or entity to whom they are addressed. If you are not the named addressee or his/her representative you are not authorised to, and must not, read, disseminate, distribute, copy, use, retain or take any action in reliance on the content of the information contained in this message or any part of it. Please notify the sender immediately by email or telephone (+ 44161 908 2221) if you have received this email by mistake and delete this email from your system. Legal Advice Bureau Ltd does not accept responsibility for changes made to this message after it was sent. Please note that neither Legal Advice Bureau Ltd nor the sender accepts any responsibility for viruses contained in this email and attachments (if any). Opinions, conclusions and other information in this message that do not relate to the official business of Legal Advice Bureau Ltd shall be understood as neither given nor endorsed by it unless specifically stated. It is expressly declared that this email does not constitute nor form part of a contract or unilateral obligation. Please note that emails sent to Legal Advice Bureau Ltd will be monitored. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query cache and queries with non-english characters
Recently I've come around that mysql (4.1.0 at least) treats different queries containing non-english characters (in my case characters from cp1251 charset) as the same query and therefore returns wrong results. For example the following queries are regarded as identical while they are not: SELECT FROM table WHERE name LIKE '%' SELECT FROM table WHERE name LIKE '%' Is this a bug or expected behaviour? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with regexp
On Feb 28, 2004, at 10:33 AM, Michael Stassen wrote: In the meantime, perhaps the following will help: mysql select 'This is an [OT] Test' regexp [ [:punct:]]OT[ [:punct:]] as mtch; Hey, thanks - that works! ___/ / __/ / / Ed Leafe http://leafe.com/ http://opentech.leafe.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Feature request related to COMPRESS and UNCOMPRESS functions
Hi! On Feb 27, Lester Hightower wrote: To whom it may concern at Mysql AB: I see from the Mysql 4.1.1 CHANGELOG that new COMPRESS(), UNCOMPRESS(), and UNCOMPRESSED_LENGTH() functions were added. That is great news, and something I have been very interested in for a long time, as evidenced by this mysql mailing list thread, dating back to 12/18/2001: http://marc.10east.com/?t=10086980305r=1w=2 The MARC system (marc.10east.com) was one of the primary reasons for me requesting that this feature be added to Mysql. There is one short-coming in the new COMPRESS()/UNCOMPRESS() functionality that I would like to point out, and request that you address. Here is the background: Anyone that is running a huge system like MARC that has millions of uncompressed blob records in huge tables, needs to be able to migrate, in real-time and without down-time, to compressed blobs. Therefore, we need a way to know if a given field is compressed or not. Running alter table on our tables, to add an am_i_compressed boolean, not only takes an excruciating amount of time and resources, but adds bits to each record that, from a disk-space perspective, we cannot afford. Instead, I would like to be able to run a query like: select IFCOMPRESSED(msg_body, UNCOMPRESS(msg_body), msg_body) from msg_bodies_200402 where clause Note that the IFCOMPRESSED() function is the key, and what I am requesting be added to future versions of Mysql. That function has to be possible. Hopefully you are storing a header with your compressed data, and if so, then the IFCOMPRESSED() is trivial to implement. If you are not storing a header with your compressed data, then this might be more complicated. Adding a header might be a possibility -- which is why I am trying to point this out _EARLY_ in the process before lots of people start using COMPRESS()/UNCOMPRESS(), or maybe zlib, lzo, or whatever library you are using can let you know if the data is compressed -- maybe they store a small header themselves. Anyway, that is the issue that I want to point out and ask for assistance on. Thank you very much for listening to the user community and adding COMPRESS()/UNCOMPRESS(), and please seriously consider this request. There is a header - but it only stores the length of the uncompressed data. And technically, no header can guarantee that the data are compressed. The check you can use is something like 1. test that UNCOMPRESSED_LENGTH returns something realistic, as you know how large a blob in your table can be - it should catch most of the uncompressed rows. 2. try to uncompress the rest - zlib puts crc in the compressed stream, and UNCOMPRESS returns NULL if uncompression fails. so the query could be something like select IF(UNCOMPRESSED_LENGTH(msg_body) 102400, msg_body, IFNULL(UNCOMPRESS(msg_body), msg_body)) ... Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help on PHP code
electroteque wrote: well dude u never showed what the parser returned, i am pretty sure u need curly brackets if u are including more than one line in an if statement well dude u r like totally wrong. -- jimoe at sohnen-moe dot com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't enable replication !
At 15:46 + 2/28/04, Mike Rabbitt wrote: Hi guys - I am trying to set up replication but cannot get it enabled on the MASTER server - I have added the bin-log line in my.conf and restarted the server - no logs are created tho - The option is log-bin and the filename is my.cnf. What did you *actually* do? Ther are no unusual message in mysql.err - and the following ' 4 test 3306 60 ' is the only entry in the master.info file - master.info is a file used by slave servers, not master servers. Any ideas would most excellent ! -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup Questions
Can anyone help me with some questions about backup scripts?
I've written a bash script to take database-level backups of each of the
databases in our copy ofMySQL. We are running MySQL 4.0.15 on Linux
Mandrake 9.1 and using a mix of MyISAM and InnoDB databases. This is my script,
with the userid and password faked for obvious reasons:
#--
#!/bin/bash
#This script makes a separate database-level backup of each of the current
MySQL databases and#deletes backups older than a certain number of
days.#This script is normally invoked via a cron job so that it runs once
per day in the middle of the night.#The crontab entry looks like this:#0
3 * * * sh /home/foo/MySQL/backup.bash /home/foo/MySQL/backup.out
21
DEBUG=1; #DEBUG switchUSERID="foo"; #The userid to use for creating the
backupPASSWORD="bar"; #The password to use for creating the
backupBACKUP_TIMESTAMP=`/bin/date +%Y%m%d"-"%H%M%S`; #The timestamp
(MMDD-HHMMSS) of the backupBACKUP_PATH="/home/foo/MySQL/backup"; #The
directory into which the backup will be
writtenNUMBER_OF_DAILY_BACKUPS_TO_KEEP=7; #The number of generations of
backups to keep
#Display the non-secret values used in this run.if test $DEBUG -eq
1then echo "BACKUP VALUES:"; echo "
Backup timestamp is" $BACKUP_TIMESTAMP; echo " Backup path
is" $BACKUP_PATH; echo " Number of daily backups to keep
=" $NUMBER_OF_DAILY_BACKUPS_TO_KEEP; fi
#For each database currently in MySQL, take a database-level backup, then
list any backups older than a certain number of days, then delete these old
backupsfor ONE_DBNAME in `echo show databases | mysql -s -u $USERID
-p$PASSWORD`do echo echo "Backing up
database" $ONE_DBNAME; /usr/bin/mysqldump --opt --verbose
-u${USERID} -p${PASSWORD} ${ONE_DBNAME} -r
${BACKUP_PATH}"/"${ONE_DBNAME}"."${BACKUP_TIMESTAMP}".sql" echo
" Deleting these old backups for this database..."
/usr/bin/find ${BACKUP_PATH} -mtime +$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name
$ONE_DBNAME'*' -print; #display old backups (if any)
/usr/bin/find ${BACKUP_PATH} -mtime +$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name
$ONE_DBNAME'*' -exec rm '{}' ';'; #delete old backups (if any)done
#-
The script seems to work just fine at the moment but I'm concerned that it
isn't equipped to handle errors very well. Unfortunately, I'm not very clear on
exactly what sort of errors *can* occur when doing backups, let alone how they
can be handled. Can anyone tell me from their own experience what errors we are
likely to encounter in doing mysqldumps and how we can recognize them when they
happen? Also, since I'm pretty new to bash, I'd appreciate some hints on how to
catch errors with a bash script. For instance, if we encounter a full disk while
writing the backup, what sort of error will we get and how will bash know what
the error was so that it can tell the user of the script?
In a nutshell, I'd like to generate a simple email in any case where the
backup doesn't work correctly and send it to the appropriate administrators so
that they can take action. The first action they would take is to look at the
output from the script (which is redirected to a file) so the emailshould
contain the name of that file.
Rhino---rhino1 AT sympatico DOT ca"If you want the best
seat in the house, you'll have to move the cat."
Update question
Here's my table definition: CREATE TABLE `files` ( `id` int(11) NOT NULL auto_increment, `checksum` char(32) NOT NULL default '', `size` bigint(20) NOT NULL default '0', PRIMARY KEY (`id`), KEY `checksum` (`checksum`(8)) This table is used to store information on files. When I delete a file, instead of deleting the row, I set checksum to the empty string and size to 0. When I want to add a new file, I try the following query, and if it returns 0 rows affected, I will then try a regular insert: UPDATE files SET checksum='[md5 of file]', size=[size of file] WHERE checksum='' AND size=0 ORDER BY id LIMIT 1 The idea is to try and reuse the id's of deleted files, and if there aren't any, then I do an INSERT to create a new id. My concern is that if I have many clients doing this, that updates from different clients may catch the same row and I would lose some data. Am I correct? Thanks, Juan
Query Help
Hi Using MySql 4.x and need some help with a query There are two tables Lists Which holds list name +other stuff Members Which holds list name from above, email address + other stuff I want to list all the lists and then which lists a member is associated with. Eg List1 - Member List2 - Not a Member List3 - Member I can do a query that show the lists some is a member or a separate query to show which they are not a member of but not a query to bring the whole thing together Pointers Appreciated. Regards John Berman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CR LF
Hi Using MySql 4.x Simple one this (I think) What do I actually need to use to add a CR and LF with some data that gets imported into my dbase Regards John B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update question
At 18:41 -0400 2/28/04, Juan E Suris wrote: Here's my table definition: CREATE TABLE `files` ( `id` int(11) NOT NULL auto_increment, `checksum` char(32) NOT NULL default '', `size` bigint(20) NOT NULL default '0', PRIMARY KEY (`id`), KEY `checksum` (`checksum`(8)) This table is used to store information on files. When I delete a file, instead of deleting the row, I set checksum to the empty string and size to 0. When I want to add a new file, I try the following query, and if it returns 0 rows affected, I will then try a regular insert: UPDATE files SET checksum='[md5 of file]', size=[size of file] WHERE checksum='' AND size=0 ORDER BY id LIMIT 1 The idea is to try and reuse the id's of deleted files, and if there aren't any, then I do an INSERT to create a new id. My concern is that if I have many clients doing this, that updates from different clients may catch the same row and I would lose some data. Am I correct? No. Thanks, Juan -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
At 23:09 + 2/28/04, John Berman wrote: Hi Using MySql 4.x and need some help with a query There are two tables Lists Which holds list name +other stuff Members Which holds list name from above, email address + other stuff I want to list all the lists and then which lists a member is associated with. Eg List1 - Member List2 - Not a Member List3 - Member I can do a query that show the lists some is a member or a separate query to show which they are not a member of but not a query to bring the whole thing together Have you tried using a UNION of the two queries? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Help
Paul
Err no, new area for me this
My two queries independently look like this
This give me all the lists some one is not a member of
SELECT lists_.Name FROM lists_ WHERE (((lists_.Name_) Not In (select
members_.List_ from members_ where members_.EmailAddr_ like (' em
'
And for all the list someone is in
SELECT ists_.Name_, members_.EmailAddr_
FROM lists_ INNER JOIN members_ ON lists_.Name_ = members_.List_
WHERE (((members_.EmailAddr_) like (' em ')))
So a union joins them somehow ?
Oh em is the variable I feed in
John B
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: 29 February 2004 00:08
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Query Help
At 23:09 + 2/28/04, John Berman wrote:
Hi
Using MySql 4.x and need some help with a query
There are two tables
Lists
Which holds list name +other stuff
Members
Which holds list name from above, email address + other stuff
I want to list all the lists and then which lists a member is associated
with.
Eg
List1 - Member
List2 - Not a Member
List3 - Member
I can do a query that show the lists some is a member or a separate query
to
show which they are not a member of but not a query to bring the whole
thing
together
Have you tried using a UNION of the two queries?
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
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: Query Help
At 0:32 + 2/29/04, John Berman wrote:
Paul
Err no, new area for me this
My two queries independently look like this
This give me all the lists some one is not a member of
SELECT lists_.Name FROM lists_ WHERE (((lists_.Name_) Not In (select
members_.List_ from members_ where members_.EmailAddr_ like (' em
'
And for all the list someone is in
SELECT ists_.Name_, members_.EmailAddr_
FROM lists_ INNER JOIN members_ ON lists_.Name_ = members_.List_
WHERE (((members_.EmailAddr_) like (' em ')))
So a union joins them somehow ?
Yes, though you would need to select the same number of columns in
each query. Read here for more information:
http://www.mysql.com/doc/en/UNION.html
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
procedure entry point question
Hello, I'm working with MySql 4.0.18 on Windows2000 and have a question about the C API function mysql_real_escape_string( ). The program I'm working on compiles and links flawlessly, but at runtime, I get a system pop-up with the error: "The procedure entry point mysql_real_escape_string could not be located in the dynamic link library LIBMYSQL.dll." This is the case whether I compile the program using Cygwin or MSVC, and so far only occurs when I include this one function call. I am hoping that there is just different .dll that I need to download to solve this problem. Any help would be appreciated. Thanks __ICQ#:135430808 Current ICQ status: + More ways to contact me __
Limit connections from same host
How can I limit the connections from the same host in mysql. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Help
Paul
Well getting there, im now selecting the correct number of columns but get a
cant be distinct error ?
John B
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: 29 February 2004 00:47
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Query Help
At 0:32 + 2/29/04, John Berman wrote:
Paul
Err no, new area for me this
My two queries independently look like this
This give me all the lists some one is not a member of
SELECT lists_.Name FROM lists_ WHERE (((lists_.Name_) Not In (select
members_.List_ from members_ where members_.EmailAddr_ like (' em
'
And for all the list someone is in
SELECT ists_.Name_, members_.EmailAddr_
FROM lists_ INNER JOIN members_ ON lists_.Name_ = members_.List_
WHERE (((members_.EmailAddr_) like (' em ')))
So a union joins them somehow ?
Yes, though you would need to select the same number of columns in
each query. Read here for more information:
http://www.mysql.com/doc/en/UNION.html
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
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]
Test mail (please ignore)
test -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Timestamp woes
Hi, (Please note: NEWBIE WARNING, below questions might sound stupid, but feel free to flame.) ;-) I have a table tbl_users with a field dat_an_time which is a timestamp(14). In that I have values such as: 2004022215 20040227042018 20040223015329 etc I have searched google/the manual for the answers to the below questions but I only found answers if the field is a datetime field and not a timestamp, I cannot change the format as I am the new developer of an old (already live) project and lots of scripts are already accessing this table. Please tell me how to do this with my current setup. Two questions: Question 1. In my php script I am taking the value of days_last which should run a select query to display all the records in the last $days_last days (for those of you who dont program in PHP/Perl $days_last is the variable that is a number which the user selects. eg: 10 or 5 or 35 etc) How do I format the query to get the results from my dat_an_time timestamp(14) field? Question 2. The client will be entering 2 fields in this format MMDD, how do I select * for all records that are between $T_field1 and $T_field2 from my dat_an_time timestamp(14) my records are like this: 2004022215 20040227042018 20040223015329 Any help, references to the manual or URLs will be appreciated. Thanks in advance. Ryan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update from 3 to 4 and some privs don't make sense
When from 3 to 4, ran the update_privs_table thngy... All my users used to say Global Privileges USAGE now they are all CREATE TEMPORARY TABLES, LOCK TABLES I am not sure this is good or bad, nor am I sure how these settings got in there. What are your suggestions? I don't think I want any user able to LOCK tables, these are global so I assume all databases are able to be locked by all users. Whats the best way to udpate all these back to the USAGE they were at before. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp woes
[EMAIL PROTECTED] wrote: Hi, (Please note: NEWBIE WARNING, below questions might sound stupid, but feel free to flame.) ;-) I have a table tbl_users with a field dat_an_time which is a timestamp(14). In that I have values such as: 2004022215 20040227042018 20040223015329 etc I have searched google/the manual for the answers to the below questions but I only found answers if the field is a datetime field and not a timestamp, I cannot change the format as I am the new developer of an old (already live) project and lots of scripts are already accessing this table. Please tell me how to do this with my current setup. Two questions: Question 1. In my php script I am taking the value of days_last which should run a select query to display all the records in the last $days_last days (for those of you who dont program in PHP/Perl $days_last is the variable that is a number which the user selects. eg: 10 or 5 or 35 etc) How do I format the query to get the results from my dat_an_time timestamp(14) field? Somewhat new myself but I believe you can do something like SELECT (whatever you need) FROM tbl_users WHERE (UNIX_TIMESTAMP(now()) - ($days_last*24*60*60)) UNIX_TIMESTAMP(dat_and_tim). I will add the the PHP Cookbook and the MySQL Cookbook are godsends and that's where I got the solution. Grab a copy of these to help out - I've got a project due in a week and I'm using these non-stop. -- Scott Plumlee PGP Public key: http://plumlee.org/pgp/ D64C 47D9 B855 5829 D22A D390 F8E2 9B58 9CBF 1F8D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Help
Got it working at last
SELECT lists_.DescShort_ FROM lists_ WHERE (((lists_.Name_) Not In (select
members_.List_ from members_ where members_.EmailAddr_ like (' em
' union SELECT lists_.DescShort_ FROM members_ INNER JOIN lists_ ON
members_.List_ = lists_.Name_ WHERE (members_.EmailAddr_ = (' em '))
My only problem being it now lists the lists Im not a member of and the ones
I am a member of - how on earth do I show on screen which is which ?
Regards
John Berman
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: 29 February 2004 00:47
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Query Help
At 0:32 + 2/29/04, John Berman wrote:
Paul
Err no, new area for me this
My two queries independently look like this
This give me all the lists some one is not a member of
SELECT lists_.Name FROM lists_ WHERE (((lists_.Name_) Not In (select
members_.List_ from members_ where members_.EmailAddr_ like (' em
'
And for all the list someone is in
SELECT ists_.Name_, members_.EmailAddr_
FROM lists_ INNER JOIN members_ ON lists_.Name_ = members_.List_
WHERE (((members_.EmailAddr_) like (' em ')))
So a union joins them somehow ?
Yes, though you would need to select the same number of columns in
each query. Read here for more information:
http://www.mysql.com/doc/en/UNION.html
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
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]
Remove Russ Myrick ceadawg@cox.net
Can someone remove this guy and his challenge response system. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit connections from same host
At 17:22 -0800 2/28/04, Scott Haneda wrote: How can I limit the connections from the same host in mysql. You can put a limit on the number of connections per hour from a given account (WITH MAX_CONNECTIONS_PER_HOUR), but not from a given host. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Help
At 2:45 + 2/29/04, John Berman wrote:
Got it working at last
SELECT lists_.DescShort_ FROM lists_ WHERE (((lists_.Name_) Not In (select
members_.List_ from members_ where members_.EmailAddr_ like (' em
' union SELECT lists_.DescShort_ FROM members_ INNER JOIN lists_ ON
members_.List_ = lists_.Name_ WHERE (members_.EmailAddr_ = (' em '))
My only problem being it now lists the lists Im not a member of and the ones
I am a member of - how on earth do I show on screen which is which ?
Maybe:
Select an extra column in each SELECT. SELECT member, ... UNION
SELECT non-member, ...
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update from 3 to 4 and some privs don't make sense
At 18:12 -0800 2/28/04, Scott Haneda wrote: When from 3 to 4, ran the update_privs_table thngy... All my users used to say Global Privileges USAGE now they are all CREATE TEMPORARY TABLES, LOCK TABLES I am not sure this is good or bad, nor am I sure how these settings got in there. They got there when you updated the grant tables. What are your suggestions? I don't think I want any user able to LOCK tables, these are global so I assume all databases are able to be locked by all users. Whats the best way to udpate all these back to the USAGE they were at before. -- UPDATE user SET Create_tmp_table_priv = 'N', Lock_tables_priv = 'N', ... ; FLUSH PRIVILEGES; You might want to add WHERE User != 'root' to that, if you don't want root accounts to have those privileges disabled. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit connections from same host
on 02/28/2004 07:08 PM, Paul DuBois at [EMAIL PROTECTED] wrote: At 17:22 -0800 2/28/04, Scott Haneda wrote: How can I limit the connections from the same host in mysql. You can put a limit on the number of connections per hour from a given account (WITH MAX_CONNECTIONS_PER_HOUR), but not from a given host. Any hacks to make this happen? In OS X with safari, if someone holds command-R on the keybaord and points it to any site that is talking to mysql, you are going to fill up all your connections in a few seconds. The best I can tell, Safari sends about 100 page requests per 2 seconds. Pretty bad :-( I guess I can limit from same host in apache and do the same, thanks. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit connections from same host
At 20:32 -0800 2/28/04, Scott Haneda wrote: on 02/28/2004 07:08 PM, Paul DuBois at [EMAIL PROTECTED] wrote: At 17:22 -0800 2/28/04, Scott Haneda wrote: How can I limit the connections from the same host in mysql. You can put a limit on the number of connections per hour from a given account (WITH MAX_CONNECTIONS_PER_HOUR), but not from a given host. Any hacks to make this happen? In OS X with safari, if someone holds command-R on the keybaord and points it to any site that is talking to mysql, you are going to fill up all your connections in a few seconds. The best I can tell, Safari sends about 100 page requests per 2 seconds. Pretty bad :-( Um, those are connections to your Web server, not the MySQL server. Connections made by the Web server to your MySQL server are always going to appear to be from the same host. If you want to throttle client connections, you'll need to clearly identify which clients you mean, and what program they are clients *of*. I guess I can limit from same host in apache and do the same, thanks. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimizing a select statement over a database with 50 million recs
Need help on optimizing the select statement: Table structure table1 -- id p_id table2 -- p_id out_id table3 -- out_id name_id table4 --- name_id (unique) prev_id start_id (unique) end_id (unique) Only table4 has unique fields all other fields are not unique. The following select statement does what is required but the tables are really huge ( 50 million records) so need to be optimized SELECT DISTINCT (a.id) FROM table1 a, table1 b, table2 c, table3 d, table4 e, table4 f WHERE a.id =275 AND a.p_id = b.p_id AND a.id != b.id AND a.p_id = c.p_id AND c.out_id = d.out_id AND d.name_id = e.name_id AND e.start_id = f.start_id AND e.end_id = f.end_id AND e.end_id !=0 AND (f.name_id =45 OR f.name_id =56) GROUP BY b.id The explain for the above statement is a | ref | id,p_id,combine | combine | 4 | const | 5281 | Using where; Using index; Using temporary; Using f... c | eq_ref | PRIMARY,p_id | PRIMARY | 4 | a.p_id | 1 | | | d | ref | name_id,out_id | out_id | 4 | b.out_id | 1 | | | b | ref | p_id | p_id | 4 | a.p_id | 3 | Using where | e | range | PRIMARY,start_id,end_id,combine,name_id | PRIMARY | 4 | NULL | 2 | Using where | f | eq_ref | PRIMARY,start_id,end_id,combine,name_id | PRIMARY | 4 | c.name_id | 1 | Using where | I am sure there should be some better way to do this using Inner join or something similar but I am not sure how. It will be helpful if you could suggest me some improvements for this query. If you need any further explanation please let me know. Thanks for your help Karu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
