Re: MySQL Slave

[dpgirago]

> Hi

> Is there a way to find out when the MySQL Slave has been restarted.

> Thanks and Regards

> Kaushal


You can determine the server actions by looking through the slave log 
file.

David

Re: Merge Tables and Replication

[dpgirago]

> [EMAIL PROTECTED] wrote:

>> 
>> Does this sound about right? Anybody see any road hazards? If not, and
>> this line of thinking is reasonable, should the DB with the older 
records
>> also be replicated so that when a new old records table needs to be
>> created, I don't have to repeat everything on the slave?
>> 

> Most of the problems documented here
>  and
> some of it here
> 

> The problems that stand out

> - A MERGE table cannot maintain uniqueness constraints over the
>   entire table.
>
> - Key reads are slower. When you read a key, the MERGE storage engine
>   needs to issue a read on all underlying tables to check which one
>   most closely matches the given key. To read the next key, the MERGE
>   storage engine needs to search the read buffers to find the next
>   key.
>
> -- 
> raj shekhar

Thanks, raj, for underscoring the key reads issue.

That might be a deal breaker...

David

Merge Tables and Replication

[dpgirago]

The recent thread about merge tables led me to explore using them with 
replication.

I see bug reports for the 4.0.** series regarding replication and merge 
tables, and I've read the manual about merge table problems in the 5.0.** 
series ( we are using 5.0.22 ), but I'm curious if anyone has any 
experiences to share?

We've had replication working well now for almost 4 years, and as many 
tables are approaching 2 million records, it would appear that using a 
merge table design might be one way to accomplish a type of incremental 
backup. 

By this I mean, since all our data are static ( only inserts and selects 
), the older data could be moved to a separate DB and only the "active" DB 
with the newest tables/records and the merge tables could be mysqldumped 
each night.
 
Does this sound about right? Anybody see any road hazards? If not, and 
this line of thinking is reasonable, should the DB with the older records 
also be replicated so that when a new old records table needs to be 
created, I don't have to repeat everything on the slave?

It always makes me uneasy, fiddling with something that is working... 

Thanks, 

--David.

Re: No database selected error when running mysql_tzinfo_to_sql

[dpgirago]

> Its example line is

> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

> There's a trailing "mysql", which specifies the "mysql" database.
> It also specifies all zoneinfo, not "America/".

Thanks, Tim. I totally missed the trailing mysql. Duh! 

--David.

Re: No database selected error when running mysql_tzinfo_to_sql

[dpgirago]

> On Mon, Mar 10, 2008 at 11:07 AM,  <[EMAIL PROTECTED]> wrote:
>> I'm getting an error trying to run this command:
>>
>>  root>  mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ | mysql -u xxx 
-p
>>  xxx
>>
>>  ERROR 1046 (3D000) at line 1: No database selected
>
>mysql -D mysql -u xxx -p < mysql_tzinfo_to_sql 
/usr/share/zoneinfo/America/
>
>The -D flag selects the database `mysql`, which is where the time
> zone information belongs.  The < redirect reads from the "file"
> (which, in this case, is actually a redirected STDOUT) into the
> database.
>
>-- 
> 
>
> Daniel P. Brown
> Senior Unix Geek
> 

Thanks Daniel. 

The command worked with this syntax:

root> mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ | mysql -D mysql -u 
xxx -p xxx

Still curious though why the databse needed to be explicitly selected now 
when I don't recall having to do that before. 

mysql_tzinfo_to_sql surely must know which db.tables to update... 

--David.

No database selected error when running mysql_tzinfo_to_sql

[dpgirago]

I'm getting an error trying to run this command:

root>  mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ | mysql -u xxx -p 
xxx

ERROR 1046 (3D000) at line 1: No database selected

Funny thing is I know I've run this on 2 other servers with identical 
software without issue, however I see that I've run yum update on this box 
but not on the other two.

/var/log> "Nov 13 14:51:58 Updated: tzdata.noarch 2007h-1.el5"

OS: CentOS 5
Mysql Version: 5.0.22

Google not helping much with this.

Kinda stumped...

--David.

Re: Statement for a date range

[dpgirago]

> I have a report that contains a WHERE statement to report on a date 
range in
> Pentaho.
>
> WHERE
> InOut.MOVEMENTDATE >= (CURRENT_DATE - 7)
>
> I want to show on the report what the first date of this range is.  Can
> someone help me with this statement?  So if the last seven days is 
January
> 24, 2008 to January 31, 2008, I want that to appear like that on the 
report,
> but how?
> Mike

select whatever ... where InOut.MovementDate >= ( CURRENT_DATE - 7 )

order by someEntryDateField desc limit 1;

 

Re: Possible cause of error when inserting into text field ??

[dpgirago]

> Hi ,   

> Someone using typo3 try to update a field "tx_templavoila.flex" into a 
table tt_content but get kicked out of typo3 without error message. The 
user tried with PHPmyadmin but got the same result.  First thought coming 
to mind is the field is too small for the data being inserted but the 
field has actualy 16000 bytes but > is defined as mediumtext so there is 
plenty space left. I looked at the av_row_length but as far as i 
understand it is used to get the max size of the table. Which doesn't seem 
to be the issue right now. I wonder if there is any other obvious 
variables i should be looking at or something i'm missing in the big 
picture?   

> Thanks ! 


> Éric Fournier 

Eric, 

What is typo3 ?

And what do you mean, the user "got kicked out of typo3"?

David

mysql client command \G and blob fields

[dpgirago]

I've been experimenting with storing image data in blob fields, and I was 
surprised that using the mysql client to select the field with a 
semi-colon query terminator resulted in the binary data being dumped to 
the terminal (with lots of beeping) while using the \G query terminator 
resulted in just a few symbols such as 

imgdata: ëPNG
?
(where imgdata is the field name) and no beeps. 

I'm _not_ surprised with the beeps due to trying to view the binary data, 
just the disparity in behavior when using the different terminators.

I've been searching the manual for about an hour now and can't seem to 
find a reference to this difference in behavior in the mysql client.

Nothing urgent here. Just wondering if anyone can shed some light on this 
as I've always gotten the same results either way, though I'm certainly no 
expert using blob data types.

Thanks,

David

Query Optimization

[dpgirago]

I need to check a date_time value in a number of tables for 
"up-to-date-ness." 

The following query is part of a larger PHP script that runs as a cron job 
every 10 minutes:


select 
case 
# If within 2 minutes, do nothing.
when (unix_timestamp() - unix_timestamp(date_time)) < 120 
then 'ok' 
 
# If between 2 and 60 minutes old, send an email each time 
the script is called (q 10 min).
when (unix_timestamp() - unix_timestamp(date_time)) >= 120 
 && 
(unix_timestamp() - unix_timestamp(date_time)) < 3600 
then 'email'

# If over an hour old, send out one email per hour. 
when (unix_timestamp() - unix_timestamp(date_time)) >= 
3600 && 
(unix_timestamp() - unix_timestamp(date_time)) % 3600 > 
2999 && 
(unix_timestamp() - unix_timestamp(date_time)) % 3600 < 
3600 
then 'email'

end 
as 'test'

from  mytable order by date_time desc limit 1;



This seems to run OK, but I'd be interested if anyone sees any way to 
improve it.

Thanks,

--David.

Re: Replication Issue with Upgrade from 4.0.x to 5.0.x

[dpgirago]

> Dave:
> There are no uncommented entries in /etc/hosts.deny
>
> Baron:
> The all servers have a unique server-id in their respective my.cnf's
>
> When I try to connect directly from the slave to the new master, I get: 
>
> ERROR 2003 (HY000): Can't connect to MySQL server on '1xx.1xx.1xx.xx' 
(113)
>
> I'll make a super user to test... 

Duh. I needed to explicitly allow port 3306 on the new master.

Thanks for the hints, guys.

David

Re: Replication Issue with Upgrade from 4.0.x to 5.0.x

[dpgirago]

David Campbell wrote:
> [EMAIL PROTECTED] wrote:
>> I'm in process of upgrading a master server from 4.0.24-log to 
5.0.22-log 
>> in a single master-slave environment.  I've previously upgraded the 
slave 
>> to 5.0.22-log and restarted replication without issue. 
>> 
>> The current master is running on RH9 and the slave is running on CentOS 
5, 
>> which is what I'm trying to upgrade the master to, also. 
>> 
>> After synchronizing the databases, I've run 'reset master' and 'reset 
>> slave' on their respective servers, then 'change master to...' on the 
>> slave, but 'show slave status' always displays:   'Slave_IO_Running: 
No'
>> 
>> And here is the log entry from the slave:
>> 
>> 071113  9:16:19 [ERROR] Slave I/O thread: error connecting to master 
>> '[EMAIL PROTECTED]:3306': Error: 'Lost connection to MySQL server 

>> during query'  errno: 2013  retry-time: 60  retries: 86400
>> 071113  9:17:35 [Note] Slave I/O thread killed while connecting to 
master>

> grep deny /etc/hosts.deny

> Dave

Dave:
There are no uncommented entries in /etc/hosts.deny

Baron:
The all servers have a unique server-id in their respective my.cnf's


When I try to connect directly from the slave to the new master, I get: 

ERROR 2003 (HY000): Can't connect to MySQL server on '1xx.1xx.1xx.xx' (113)

I'll make a super user to test... 

David

Replication Issue with Upgrade from 4.0.x to 5.0.x

[dpgirago]

I'm in process of upgrading a master server from 4.0.24-log to 5.0.22-log 
in a single master-slave environment.  I've previously upgraded the slave 
to 5.0.22-log and restarted replication without issue. 

The current master is running on RH9 and the slave is running on CentOS 5, 
which is what I'm trying to upgrade the master to, also. 

After synchronizing the databases, I've run 'reset master' and 'reset 
slave' on their respective servers, then 'change master to...' on the 
slave, but 'show slave status' always displays:   'Slave_IO_Running: No'

And here is the log entry from the slave:

071113  9:16:19 [ERROR] Slave I/O thread: error connecting to master 
'[EMAIL PROTECTED]:3306': Error: 'Lost connection to MySQL server 
during query'  errno: 2013  retry-time: 60  retries: 86400
071113  9:17:35 [Note] Slave I/O thread killed while connecting to master

The replication user has 'replication slave' privileges, and basically 
I've duplicated my.cnf from the old to the new master.

I've done some Googling and searching of archives but not much luck.

Fortunately, I've been trying out the master upgrade on a temp machine, 
and it's fairly easy to revert back to the current master server. 

But if anyone has thoughts or suggestions about what to try next, I'd be 
most appreciative.

Thanks,

David. 

Re: Slave Misbehavin'

[dpgirago]

>>> [EMAIL PROTECTED] wrote:
>>> Howdy,
>>> 
>>> I'm trying to add a second slave, slave2, running MySQL 5.0.22 on 
CentOS5 
>>> to our system that currently has one master and one slave, slave1, 
running 
>>> 4.0.24, and somehow slave2 somehow ends up with too many records in many 

>>> of the 30 tables in the database. 
>>> 
>>> Steps taken:
>>> 
>>> 1. Stopped new records from being inserted into the master, and 
confirmed 
>>> with count(*)'s that both master and slave1 were in a static state.
>>>
>>> 2. Stopped mysqld and commented out in my.cnf the master connection 
>>> parameters (user, host, password, port) on slave2.
>>> 
>>> 3. Deleted master.info, all mysql-bin and relay-bin files from the mysql 

>>> data directory on slave2. 
>>> 
>>> 4. Deleted all .MYD, .MYI, and .frm files from the replication database 
>>> directory on slave2. 
>>> 
>>> 5. rsync'd the .MYD, .MYI, .frm files from slave1 to slave2.

>> And in the meantime, slave1's data is being changed because the master 
>> is sending it replication events, no?  You need to run STOP SLAVE on 
>> slave1 before rsyncing it.  After STOP SLAVE, run SHOW SLAVE STATUS and 

>> record the output, then rsync, then START SLAVE on slave1 again.

> I don't think so. I stopped all activities on the master (step 1), and 
> slave1 
> therefore shouldn't have any changes made to it. I should have noted 
that 
> only inserts are done on the master -  no updates or deletes. 

>>> 6. Restarted mysqld on slave2 (now not running as a slave).
>>> 
>>> 7. Confirmed that record counts were consistent across master, slave1 and 

>>> slave2.
>>> 
>>> 8. Stopped mysqld on slave2, uncommented master connection parameters in 

>>> my.cnf, and restarted mysqld.
>>> 
>>> 9. Got log file and log position parameters with 'show master status' on 

>>> the master.

>> TOO LATE.  The horse has left the barn and you're closing the door 
>> behind it!  You should instead get the replication coordinates from 
>> slave1 with SHOW SLAVE STATUS during step 5.  You're cloning slave2 
from 
>> slave1, so slave2 tells the truth, not the master, which has done a 
>> whole bunch of work while you were going through these steps.

> No, slave1 can't do any work except as directed by the master, which has 

> had all activities stopped on it. 
 
>>> 10. Ran 'Change master to... with all fields filled in.
>>> 
>>> 11.  Ran 'slave start' on slave2.
>>> 
>>> 12. Rechecked record counts on slave2, and they were too large and out 
of 
>>> sync with slave1 and master.
>>> 
>>> I poked around in the data on slave2 and found a number of records had 

>>> been duplicated, and that accounted for the higher record counts. 
>>> 
>>> After starting the application that inserts data into the master, I 
>>> determined that new records are being inserted correctly into slave2.
>>> 

To update this thread, I got the second slave working by getting a 
snapshot of the master via mysqldump (rather than rsync'ing the individual 
tables/files), backed-up then deleted log files on the master, then used 
"change master to..." command to sync up the slaves to the master. My best 
guess is that there was some corruption in the master bin-log files, 
though I can't say for certain if this was the case or how it might have 
occurred.

Thanks to those who responded.

David

Re: Slave Misbehavin'

[dpgirago]

Hi,

[EMAIL PROTECTED] wrote:
> Howdy,
> 
> I'm trying to add a second slave, slave2, running MySQL 5.0.22 on CentOS 
5 
> to our system that currently has one master and one slave, slave1, 
running 
>  4.0.24, and somehow slave2 somehow ends up with too many records in 
many 
> of the 30 tables in the database. 
> 
> Steps taken:
> 
> 1. Stopped new records from being inserted into the master, and 
confirmed 
> with count(*)'s that both master and slave1 were in a static state.
> 
> 2. Stopped mysqld and commented out in my.cnf the master connection 
> parameters (user, host, password, port) on slave2.
> 
> 3. Deleted master.info, all mysql-bin and relay-bin files from the mysql 

> data directory on slave2. 
> 
> 4. Deleted all .MYD, .MYI, and .frm files from the replication database 
> directory on slave2. 
> 
> 5. rsync'd the .MYD, .MYI, .frm files from slave1 to slave2.

- And in the meantime, slave1's data is being changed because the master 
- is sending it replication events, no?  You need to run STOP SLAVE on 
- slave1 before rsyncing it.  After STOP SLAVE, run SHOW SLAVE STATUS and 
- record the output, then rsync, then START SLAVE on slave1 again.

I don't think so. I stopped all activities on the master (step 1), and 
slave1 
therefore shouldn't have any changes made to it. I should have noted that 
only
inserts are done on the master -  no updates or deletes. 

> 6. Restarted mysqld on slave2 (now not running as a slave).
> 
> 7. Confirmed that record counts were consistent across master, slave1 
and 
> slave2.
> 
> 8. Stopped mysqld on slave2, uncommented master connection parameters in 

> my.cnf, and restarted mysqld.
> 
> 9. Got log file and log position parameters with 'show master status' on 

> the master.

- TOO LATE.  The horse has left the barn and you're closing the door 
- behind it!  You should instead get the replication coordinates from 
- slave1 with SHOW SLAVE STATUS during step 5.  You're cloning slave2 from 

- slave1, so slave2 tells the truth, not the master, which has done a 
- whole bunch of work while you were going through these steps.

No, slave1 can't do any work except as directed by the master, which has 
had
all activities stopped on it. 
 
> 10. Ran 'Change master to... with all fields filled in.
> 
> 11.  Ran 'slave start' on slave2.
> 
> 12. Rechecked record counts on slave2, and they were too large and out 
of 
> sync with slave1 and master.
> 
> I poked around in the data on slave2 and found a number of records had 
> been duplicated, and that accounted for the higher record counts. 
> 
> After starting the application that inserts data into the master, I 
> determined that new records are being inserted correctly into slave2.
> 
> Seriously out of ideas here.
> 
> Thanks,
> 
> David 

Slave Misbehavin'

[dpgirago]

Howdy,

I'm trying to add a second slave, slave2, running MySQL 5.0.22 on CentOS 5 
to our system that currently has one master and one slave, slave1, running 
 4.0.24, and somehow slave2 somehow ends up with too many records in many 
of the 30 tables in the database. 

Steps taken:

1. Stopped new records from being inserted into the master, and confirmed 
with count(*)'s that both master and slave1 were in a static state.

2. Stopped mysqld and commented out in my.cnf the master connection 
parameters (user, host, password, port) on slave2.

3. Deleted master.info, all mysql-bin and relay-bin files from the mysql 
data directory on slave2. 

4. Deleted all .MYD, .MYI, and .frm files from the replication database 
directory on slave2. 

5. rsync'd the .MYD, .MYI, .frm files from slave1 to slave2.

6. Restarted mysqld on slave2 (now not running as a slave).

7. Confirmed that record counts were consistent across master, slave1 and 
slave2.

8. Stopped mysqld on slave2, uncommented master connection parameters in 
my.cnf, and restarted mysqld.

9. Got log file and log position parameters with 'show master status' on 
the master.

10. Ran 'Change master to... with all fields filled in.

11.  Ran 'slave start' on slave2.

12. Rechecked record counts on slave2, and they were too large and out of 
sync with slave1 and master.

I poked around in the data on slave2 and found a number of records had 
been duplicated, and that accounted for the higher record counts. 

After starting the application that inserts data into the master, I 
determined that new records are being inserted correctly into slave2.

Seriously out of ideas here.

Thanks,

David 

MySQL DateTime Source on CentOS 5

[dpgirago]

I'm running Apache/MySQL/PHP5 on a CentOS 5 server and have started to get 
some strange date and time inconsistencies between OS time and MySQL time 
("now()"). 

1. MySQL Time:
[EMAIL PROTECTED] mysql -bA
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

([EMAIL PROTECTED]) [(none)])  select now();
+-+
| now() |
+-+
| 2007-09-13 14:07:37 |
+-+
1 row in set (0.00 sec)

([EMAIL PROTECTED]) [(none)])  exit
Bye


2. OS Time:
[EMAIL PROTECTED] date
Thu Sep 13 08:09:12 STDST 2007


I'm at a loss as to where MySQL is getting this time from as it has always 
matched the OS time.

Thoughts appreciated.

Thanks,

David 

Re: Implement a logging table; avoiding conflicting inserts

[dpgirago]

> Given: MySQL 4.0.12, I need to implement a pageview log with a
> resolution of 1 day.
>
> I propose this table:
>
> CREATE TABLE `pageviews` (
>   `id` int(11) NOT NULL auto_increment,
>  `date` date NOT NULL default '-00-00',
>  `url` char(120) NOT NULL default '',
>  `views` mediumint(9) NOT NULL default '0',
>  PRIMARY KEY  (`id`),
>  UNIQUE KEY `date` (`date`,`url`),
>  KEY `url` (`url`)
>) TYPE=InnoDB;>>
>
>
> So that an update will look like:
>  > UPDATE pageviews SET views=views+1 WHERE date='' AND
> url=''
> 
> Of course I need to INSERT the record if one does not match my WHERE.
> This would be easy if I had 4.1 -- "INSERT ... ON DUPLICATE KEY UPDATE",
> I think -- but I do not. 

Would the "REPLACE" method work?

David

Re: finding count of spaces in a string

[dpgirago]

> [EMAIL PROTECTED] wrote:
>> We have numerous identical tables with a varchar column that holds data 

>> like this: "0 0 0 1 0 1 0 25 7 0 139 0 9". Essentially there are a 
bunch 
>> of integers with a single space as a separator. There _should_ be no 
more 
>> than 30 entries ( and 29 spaces ), but sometimes the system misfires 
and 
>> there are more or less.  Is there a MySQL solution to getting a count 
of 
>> the spaces present in the field, figuring that spaces + 1 will equal 
>> entries? It's fairly straight forward using a PHP application, but I'd 
>> like to get the DB server to accomplish this task. Not having much luck 

>> finding a solution in the manual. 
>
> SELECT
> CHAR_LENGTH(field_name) - CHAR_LENGTH(REPLACE(field_name, ' ', '')) as 
> num_spaces FROM my_table;
>
> Cheers,
>
>Jay

Thanks Baron, Jay, Rolando, and Francesco. 

Just what I needed.

David

finding count of spaces in a string

[dpgirago]

We have numerous identical tables with a varchar column that holds data 
like this: "0 0 0 1 0 1 0 25 7 0 139 0 9". Essentially there are a bunch 
of integers with a single space as a separator. There _should_ be no more 
than 30 entries ( and 29 spaces ), but sometimes the system misfires and 
there are more or less.  Is there a MySQL solution to getting a count of 
the spaces present in the field, figuring that spaces + 1 will equal 
entries? It's fairly straight forward using a PHP application, but I'd 
like to get the DB server to accomplish this task. Not having much luck 
finding a solution in the manual. 

Thanks,

David

Re: Reset a auto increment field?

[dpgirago]

> Is there away to reset an auto incrementing field count? I have a 
> database that currently has 935 records in it but because I have 
> deleted a few the current number used for NEW records is 938 :) How 
> can I get it to count the records and assign a record number based on 
> the total count?
>
> Hope that makes sense! Thanks for looking! :)
>
> --

> Jason Pruim
> Raoset Inc.
> Technology Manager
> MQC Specialist
> 3251 132nd ave
> Holland, MI, 49424
> www.raoset.com
> [EMAIL PROTECTED]

AFAIK, you need to drop and then recreate the auto-increment field, 
otherwise you'll get holes when you delete a record.

David

Re: 2007 MySQL Community Survey - Got Ten Minutes to Spare?

[dpgirago]

Problems again with the survey design and functionality. Page 4 asks 
questions about Falcon, and to be honest I don't know anything about 
Falcon, but you've required answers to advance and only offered "yes' and 
"no' as choices.  Without a "Don't know" choice as an option, your results 
will be totally skewed. I designed survey questionnaires in my past life 
as a psychologist, and this one is fatally flawed. I'm done. I won't try 
again.

David 

> Thanks for the input!  Hopefully, I've fixed the problem.
>
> Please retry.  It should have saved your previous answers.
>
> Thanks much!
>
> Jay

>J.R. Bullington wrote:
>>   I received the same as David. The 
question was:
>> 
>> What OS do you currently use? Please check all that apply: (page 3, 
question 4 or 5):
>> 
>> I chose Linux 2.6.x and Windows. It kept telling me that I needed to 
answer the question.
>> 
>> I changed the answer to just Linux and it let me thru.
>> 
>> Just to provide a little more info for you.
>> 
>> J.R.
>> 
>> 
>> 
>>   From: 
[EMAIL PROTECTED]
>> Sent: Tuesday, August 07, 2007 9:35 AM
>> To: "'Jay Pipes'" <[EMAIL PROTECTED]>, mysql@lists.mysql.com
>> Subject: RE: 2007 MySQL Community Survey - Got Ten Minutes to Spare? 
>> 
>> Having a moment of altruism, I started doing the survey only to find 
that 
>> it wouldn't let me advance to the next page (from either page 1 or page 
2, 
>> can't recall). I kept getting an error of "an answer is required of 
this 
>> question" even when I had provided one. No good deed goes unpunished 
>> perhaps...
>> 
>> David
>> 
>>> So I was gonna take this "survey" (I don't need or care about the 
book,
>>> just wanted to help you out) and honestly, it's more like a "quiz" --
>>> needless to say I didn't do it. 
>>>
>>> :-| 
>> 
 -Original Message-
 From: Jay Pipes [mailto:[EMAIL PROTECTED] 
 Sent: Friday, August 03, 2007 2:33 PM
 To: mysql@lists.mysql.com
 Subject: 2007 MySQL Community Survey - Got Ten Minutes to Spare?

 2007 MySQL Community Survey - Bribe Included

 I've created a survey on SurveyMonkey that I am hoping to get 
 a bunch of 
 responses for. The survey will help the community team identify how 
 (in)effectively we communicate development and other goals 
 and also what 
 features you, our community users, most want in future versions of 
 MySQL. So, hey, give us ten minutes of your time and help us 
 make MySQL 
 better.

 A Blatant Bribe for Participating

 And for those who need a bribe, we'll be giving away two Apress books 

 (each) to 3 random survey takers. The survey is anonymous, but if you 

 would like to go into the drawing for the books, just include 
 your email 
 address in the very last question...otherwise, just leave it blank.

 Link to the 2007 Community survey:

 http://www.surveymonkey.com/s.aspx?sm=ONCdTFPxjyq55NAUBUJ_2fvQ_3d_3d

 Thanks much!

 Jay Pipes
 Community Relations Manager, North America
 MySQL, Inc.

RE: 2007 MySQL Community Survey - Got Ten Minutes to Spare?

[dpgirago]

Having a moment of altruism, I started doing the survey only to find that 
it wouldn't let me advance to the next page (from either page 1 or page 2, 
can't recall). I kept getting an error of "an answer is required of this 
question" even when I had provided one. No good deed goes unpunished 
perhaps...

David

> So I was gonna take this "survey" (I don't need or care about the book,
> just wanted to help you out) and honestly, it's more like a "quiz" --
> needless to say I didn't do it. 
>
> :-| 


>> -Original Message-
>> From: Jay Pipes [mailto:[EMAIL PROTECTED] 
>> Sent: Friday, August 03, 2007 2:33 PM
>> To: mysql@lists.mysql.com
>> Subject: 2007 MySQL Community Survey - Got Ten Minutes to Spare?
>> 
>> 2007 MySQL Community Survey - Bribe Included
>> 
>> I've created a survey on SurveyMonkey that I am hoping to get 
>> a bunch of 
>> responses for. The survey will help the community team identify how 
>> (in)effectively we communicate development and other goals 
>> and also what 
>> features you, our community users, most want in future versions of 
>> MySQL. So, hey, give us ten minutes of your time and help us 
>> make MySQL 
>> better.
>> 
>> A Blatant Bribe for Participating
>> 
>> And for those who need a bribe, we'll be giving away two Apress books 
>> (each) to 3 random survey takers. The survey is anonymous, but if you 
>> would like to go into the drawing for the books, just include 
>> your email 
>> address in the very last question...otherwise, just leave it blank.
>> 
>> Link to the 2007 Community survey:
>> 
>> http://www.surveymonkey.com/s.aspx?sm=ONCdTFPxjyq55NAUBUJ_2fvQ_3d_3d
>> 
>> Thanks much!
>> 
>> Jay Pipes
>> Community Relations Manager, North America
>> MySQL, Inc.

Master-Slave System Using Different Versions of MySQL

[dpgirago]

There was a comment this week about a v5 master having problems connecting 
to a v4 slave, or vice versa.

Can someone shed some light on this issue. 

A manual reference is good, too.

Thanks,

David

Re: Create Table Warning

[dpgirago]

> This is a create table statement output from mysqldump from a 4.0.24 
> installation. 
> 
> Restoring on 5.0.22 gives a warning. 
> 
> Can anyone enlighten me?
> 
> I guess I'm a little behind on my reading...
> 
> David
> 
> mysql> CREATE TABLE `container` (
> ->   `carrier` varchar(128) NOT NULL default '',
> ->   `lock1` varchar(7) NOT NULL default '',
> ->   `lock2` varchar(7) NOT NULL default '',
> ->   `lock3` varchar(7) NOT NULL default '',
> ->   `lock4` varchar(7) NOT NULL default '',
> ->   `lock5` varchar(7) NOT NULL default '',
> ->   `lock6` varchar(7) NOT NULL default '',
> ->   `lock7` varchar(7) NOT NULL default '',
> ->   `lock8` varchar(7) NOT NULL default '',
> ->   `lock9` varchar(7) NOT NULL default '',
> ->   `lock10` varchar(7) NOT NULL default '',
> ->   `lock11` varchar(7) NOT NULL default '',
> ->   `lock12` varchar(7) NOT NULL default '',
> ->   `lock13` varchar(7) NOT NULL default '',
> ->   `lock14` varchar(7) NOT NULL default '',
> ->   `lock15` varchar(7) NOT NULL default '',
> ->   `lock16` varchar(7) NOT NULL default '',
> ->   `lock17` varchar(7) NOT NULL default '',
> ->   `lock18` varchar(7) NOT NULL default '',
> ->   `lock19` varchar(7) NOT NULL default '',
> ->   `lock20` varchar(7) NOT NULL default '',
> ->   `lock21` varchar(7) NOT NULL default '',
> ->   `lock22` varchar(7) NOT NULL default '',
> ->   `lock23` varchar(7) NOT NULL default '',
> ->   `lock24` varchar(7) NOT NULL default '',
> ->   PRIMARY KEY  (`carrier`)
> -> ) TYPE=MyISAM;
> Query OK, 0 rows affected, 1 warning (0.00 sec)

I would say the warning is caused by TYPE=MyISAM.  TYPE= is deprecated in 
5.x, and is 
being replaced by ENGINE=.  If you execute SHOW WARNINGS right after a 
statement that 
generates warnings, you will see the warning text.

Cheers
Baron

%%

mysql> show warnings;
+-+--+--+
| Level   | Code | Message  |
+-+--+--+
| Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 
'ENGINE=storage_engine' instead |
+-+--+--+
1 row in set (0.00 sec)

%%

Yup, that's it.

Thanks, Baron and Rhys.

Kind Regards,

David

Create Table Warning

[dpgirago]

This is a create table statement output from mysqldump from a 4.0.24 
installation. 

Restoring on 5.0.22 gives a warning. 

Can anyone enlighten me?

I guess I'm a little behind on my reading...

David

mysql> CREATE TABLE `container` (
->   `carrier` varchar(128) NOT NULL default '',
->   `lock1` varchar(7) NOT NULL default '',
->   `lock2` varchar(7) NOT NULL default '',
->   `lock3` varchar(7) NOT NULL default '',
->   `lock4` varchar(7) NOT NULL default '',
->   `lock5` varchar(7) NOT NULL default '',
->   `lock6` varchar(7) NOT NULL default '',
->   `lock7` varchar(7) NOT NULL default '',
->   `lock8` varchar(7) NOT NULL default '',
->   `lock9` varchar(7) NOT NULL default '',
->   `lock10` varchar(7) NOT NULL default '',
->   `lock11` varchar(7) NOT NULL default '',
->   `lock12` varchar(7) NOT NULL default '',
->   `lock13` varchar(7) NOT NULL default '',
->   `lock14` varchar(7) NOT NULL default '',
->   `lock15` varchar(7) NOT NULL default '',
->   `lock16` varchar(7) NOT NULL default '',
->   `lock17` varchar(7) NOT NULL default '',
->   `lock18` varchar(7) NOT NULL default '',
->   `lock19` varchar(7) NOT NULL default '',
->   `lock20` varchar(7) NOT NULL default '',
->   `lock21` varchar(7) NOT NULL default '',
->   `lock22` varchar(7) NOT NULL default '',
->   `lock23` varchar(7) NOT NULL default '',
->   `lock24` varchar(7) NOT NULL default '',
->   PRIMARY KEY  (`carrier`)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Re: Mysql 5.0.* client connection to Mysql 4.0.* Database

[dpgirago]

Yes, I did try. The issue was the security level setting on the new DB 
server, and not the password stuff. I've upgraded apache, mysql, and php 
in the last few weeks, and 3-4 issues became painfully apparent when going 
from php4 to php5, so I guess I was operating under similar assumptions 
and frustrations. 

My apologies to all. I'll try to keep from functioning in iamadummy mode.

David


**
Have you tried this yet?  While 4.0 client can't connect to a v.5
server unless the passwords have explicitly been stored as
old_password, I have never run into an issue connecting a 5.x client
to a 4.0 server.

 - michael


On 6/7/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Is it possible to connect to a 4.0.18 DB from the client of a 5.0.27
> system? I know there is an old_passwords function. Can this be used
> somehow from a DOS command prompt?
>
> David


-- 
 - michael dykman
 - [EMAIL PROTECTED]

 - All models are wrong.  Some models are useful.

Mysql 5.0.* client connection to Mysql 4.0.* Database

[dpgirago]

Is it possible to connect to a 4.0.18 DB from the client of a 5.0.27 
system? I know there is an old_passwords function. Can this be used 
somehow from a DOS command prompt?

David

Restoring Large Tables Created with --extended-insert in mysqldump

[dpgirago]

Using mysqldump and mysql (Distribution 5.0.22) on CentOS:

[?]  Is it theoretically possible to create a mysqldump file using the 
default --opt option (i.e., with extended-inserts...) that would create 
packet sizes so large that the restore of the backup would fail because 
max_allowed_packet would be exceeded?

I found the maximum value I could set "net_buffer_length" to was 1M on 
mysql but it was 16M on mysqldump.
 
The manual page for mysqldump says:

max_allowed_packet 
The maximum size of the buffer for client/server communication. The 
maximum is 1GB. 
net_buffer_length 
The initial size of the buffer for client/server communication. When 
creating multiple-row-insert statements (as with option --extended-insert or 
--opt), mysqldump creates rows up to net_buffer_length length. If you increase 
this variable, you should also ensure that the net_buffer_length variable in 
the MySQL server is at least this large.

Thanks,
David

Re: Replication Complication

[dpgirago]

> Howdy,
>
> Having trouble getting  "Slave_IO_Running" to start. The master-slave 
> system was working before, but I think the master and slave binlogs have 

> been reset (truncated). 
>
> I'm unsure how to proceed. 
>
> Am I SOL? 

mysql> Load Data From Master;

seems to have done it.

David

Replication Complication

[dpgirago]

Howdy,

Having trouble getting  "Slave_IO_Running" to start. The master-slave 
system was working before, but I think the master and slave binlogs have 
been reset (truncated). 

I'm unsure how to proceed. 

Am I SOL? 

David

Re: Bin logs and mysql 4

[dpgirago]

So if one is doing a full mysqldump every night, all bin-logs can be 
deleted after this? 

If bin-logging is disabled, will master/slave syncing still occur?

David

> Issuing a 'reset master' will purge all of the logs as well. I wouldn't
> just rm them, as they are being tracked in the index file. 
>
> If you aren't running a slave, then these files are only good for data
> recovery purposes. Say a DBA goes crazy and deletes all of the databases
> mid-day (too much stress). You could restore the previous nights backup
> and run these bin logs up to the point of the delete command - a little
> bit of editing would be needed to do this, but you get the idea.
>
> For this to work smoothly, you need to reset the logs after every
> backup. If your using mysqldump, just add the --delete-master-logs
> option. 
>
>  If you want to turn the logs off, remove log-bin and log-bin-index
> from the conf file.
>
> 
> Regards,
> Scott Tanner


>On Thu, 2007-04-26 at 09:47 -0700, Scott Haneda wrote:
> > > In the short term, see the manual page for PURGE MASTER LOGS.  In 
the
> > > long term, write a cron job.
> > > 
> > > innotop (http://sourceforge.net/projects/innotop) also has a new
> > > feature, unreleased because I just wrote it a few hours ago, which 
will
> > > help you figure out which binlogs can be purged s> keystroke 
:-)--delete-master-logs
> > 
> > I don't quite get this, if SHOW SLAVE STATUS shows empty result set, 
and I
> > am just running one server, not a master + slave setup at all, its 
really
> > rather simple.
> > 
> > So, how would I ever know what logs I can safely delete or purge?
> > 
> > Do I really need to use mysql to purge them or can I just `rm` them?
> > 
> > I guess I could push this to cron?
> > PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY);
> > 
> > My question is, what are these logs really good for, I assume 
restoration,
> > and from what I read, but how do I know how far back I should keep?
> > 
> > thanks
> > -- 
> > -
> > Scott HanedaTel: 415.898.2602
> >  Novato, CA U.S.A. 

Re: Borland C++ Builder 2006 DLL Woes

[dpgirago]

Thanks, Mark. We did the same thing - used the ADO Class with MyOBDC. I 
put a call in to the sales rep. Maybe we'll get an answer someday.

David


| We have settled on the ado.net connector www.mysql.com after dbExpress 
| pains. Although apparently dbExpress does work.
| What BDS 2006 service pack are you on? Keep us all posted if you solve 
it.
|
| Mark
|
| [EMAIL PROTECTED] wrote:
|>  We just purchased The Borland Developer Studio 2006 IDE and are having 

|>  significant problems using dbExpress objects to communicate with MySQL 

|>  servers (both 4 and 5). Curiously, we can perform inserts but not 
selects, 
|>  even though identical code in C++ Builder 6 worked just fine. The DLL 
in 
|>  C++ Builder 2006 is dbxmys30.dll. Anybody having similar issues?
|>
|>  Thanks,
|>
|>  David
|>
|>  David P. Giragosian, Psy.D.
|>  Database and Software Developer
|>  MD Anderson Cancer Center
|>  Houston, TX
|>  713-792-7898

Borland C++ Builder 2006 DLL Woes

[dpgirago]

We just purchased The Borland Developer Studio 2006 IDE and are having 
significant problems using dbExpress objects to communicate with MySQL 
servers (both 4 and 5). Curiously, we can perform inserts but not selects, 
even though identical code in C++ Builder 6 worked just fine. The DLL in 
C++ Builder 2006 is dbxmys30.dll. Anybody having similar issues?

Thanks,

David

David P. Giragosian, Psy.D.
Database and Software Developer
MD Anderson Cancer Center
Houston, TX
713-792-7898

MySQL Daylight Savings Time Patch

[dpgirago]

Is there a DST patch for MySQL 4.0.x series?

I've been getting scary emails from our sys and net admins about impending 
doom.

Thanks,

David

Re: rounding digits after decimal sign

[dpgirago]

Dimitar Vassilev  asks: 

Hello,
Can anyone point me to a section of manual or link how to reduce digits
after
a decimal sign?
I have a table

mysql> desc part;
+-+--+--+-+-+---+
| Field   | Type | Null | Key | Default | Extra |
+-+--+--+-+-+---+
| kod | varchar(255) | YES  | | NULL|   |
| bulstat | double(15,5) | YES  | MUL | NULL|   |
| Land| varchar(3)   | YES  | MUL | NULL|   |
| ID  | varchar(15)  | YES  | MUL | NULL|   |
| IME | varchar(100) | YES  | MUL | NULL|   |
| VALUE   | double(15,5) | YES  | | NULL|   |
| CNT | smallint(5)  | YES  | | NULL|   |
| Date| datetime | YES  | | NULL|   |
+-+--+--+-+-+---+

and in bulstat column i have digits like  831690750.0
which i would like round to the last whole digit.
Also could someone give a hint how to recode digits in order to anonymize
data.

+

mysql\> select round(12.3456789, 3);
mysql\> 12.345

Can you say more about this:
"Also could someone give a hint how to recode digits in order to anonymize
data."

David

RE: Mysqldump Files

[dpgirago]

Hi Mikhail,

I don't think that would save much space, in terms of file size. The 
tables that are actively getting inserts are large and growing larger 
(~750,000 records), and those that have no activity are either currently 
empty or have less than a hundred records in them. So just dumping the 
active tables will comprise I'd guess 99% or more of the database size. 

David

--

Hi David,

Let me point you in a bit different direction. You are already running
replication as it seems from your E-mail

So, why not just run chained replication from second to the third server
and use "replicate-do-table = [table_name]" in my.cnf of the third
server to limit selection of tables to be used by web server. Or do full
replication to another server from the first one for full backup?

Regards,

Mikhail Berman

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 29, 2007 2:33 PM
To: mysql
Subject: Mysqldump Files

Howdy Guys and Gals,

We are acquiring data on background radiation in a master-slave server 
environment (RH9.0, MySQL 4.0.24) at the rate of approximately 19,000 
records per day.  The data are insert-only into about 25 of 31 tables in

the database -- no updates are ever applied to the data. Information
from 
the database is used via select statements for graphical display and 
report generation amongst other uses. 

A PHP backup script using mysqldump runs as a cron job each night from a

third server which also functions as an intranet webserver. After 1 1/2 
years of operation, the mysqldump file of the entire database is roughly

760 MB, and this takes under 2 minutes to create.  Once bzipped and 
tarred, the entire file is 31.7 MB in size, and this part of the backup 
process now takes 46-47 minutes. 

The rate of acquisition of data will be fairly constant, and up to 3
years 
of data will be kept on the live master-slave, so simply doubling all 
these values seems a realistic expectation for a full backup of the 
database after 3 years.  Data older than 3 years would be deleted from
the 
master-slave system.

How long it would be reasonable to keep doing a full dump of the
database 
versus using mysqldump with a where clause, i.e., doing a daily 
incremental backup, say of the last 24 hours. 

Also, what are the key mysqldump and/or server variables to pay
attention 
to in order to process these large, maybe multi-gigabyte dump files?

Thanks,

David

Mysqldump Files

[dpgirago]

Howdy Guys and Gals,

We are acquiring data on background radiation in a master-slave server 
environment (RH9.0, MySQL 4.0.24) at the rate of approximately 19,000 
records per day.  The data are insert-only into about 25 of 31 tables in 
the database -- no updates are ever applied to the data. Information from 
the database is used via select statements for graphical display and 
report generation amongst other uses. 

A PHP backup script using mysqldump runs as a cron job each night from a 
third server which also functions as an intranet webserver. After 1 1/2 
years of operation, the mysqldump file of the entire database is roughly 
760 MB, and this takes under 2 minutes to create.  Once bzipped and 
tarred, the entire file is 31.7 MB in size, and this part of the backup 
process now takes 46-47 minutes. 

The rate of acquisition of data will be fairly constant, and up to 3 years 
of data will be kept on the live master-slave, so simply doubling all 
these values seems a realistic expectation for a full backup of the 
database after 3 years.  Data older than 3 years would be deleted from the 
master-slave system.

How long it would be reasonable to keep doing a full dump of the database 
versus using mysqldump with a where clause, i.e., doing a daily 
incremental backup, say of the last 24 hours. 

Also, what are the key mysqldump and/or server variables to pay attention 
to in order to process these large, maybe multi-gigabyte dump files?

Thanks,

David

Re: Problems with INSERT

[dpgirago]

> I keep getting the following error:
>
> 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 's 
> resources than dual monitor mode to generate the video output. Running 
> in dual' at line 2
>
> when I try to add:
>
> Note : Single-monitor mode uses more of your computer's resources than 
> dual monitor mode to generate the video output. Running in dual monitor 
> mode is recommended because it is more efficient.
>
>
> My PHP code to add Q & A is:
>
> mysql_query("INSERT INTO faqs
> (Question, Answer, Category, Date) VALUES('$question', '$answer', 
> '$category', CURDATE() ) ")
> or die(mysql_error()); 
>
> If I enter the above text directly intophpMyAdmin, all is fine.
>
> Any help is greatly appreciated.
>
> Thanks
>
> Haig

Haig,

$questionEscaped = mysql_real_escape_string($question);

mysql_query("INSERT INTO faqs
(Question, Answer, Category, Date) VALUES('$questionEscaped', '$answer', 
'$category', CURDATE() ) ") 
or die(mysql_error());

You need to escape the single quote in your FAQ.

David

Re: MySQL Cookbook, second edition, now available

[dpgirago]

> The second edition of MySQL Cookbook (O'Reilly, 2006) is now available.

> The second edition brings the recipes up to date for MySQL 5.0/5.1. For
> example, it covers views, stored routines, triggers, and events. The
> second edition also adds coverage for Ruby (using the Ruby DBI module).

> More information is available at the book's web site:

>   http://www.kitebird.com/mysql-cookbook/

> -- 
> Paul DuBois, MySQL Documentation Team
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com

Paul,

I see PHP mentioned once in the table of contents under session 
management. Are there many PHP specific examples in this edition of the 
cookbook?

David 

Re: Calling Stored Procedures from PHP

[dpgirago]

>> You seem to be mimicking the prepared query feature of mysqli in PHP5. 
Do you 
>> have the mysqli extension available?  If so, you can use things like:
>>
>> http://www.php.net/manual/en/function.mysqli-stmt-bind-param.php
>>
>> which has an example as to how to utilize a prepared query.
>>
>> 

> I never really looked into mysqli, so far I only used the mysql 
> extension in PHP.
> So maybe that will help.
> thanks

Filipe,

As someone else suggested, the mysql extension also works just fine:

\n";
while ($line = mysql_fetch_array($result2, MYSQL_ASSOC)) {
echo "\t\n";
foreach ($line as $col_value) {
echo "\t\t$col_value\n";
}
echo "\t\n";
}
echo "\n";

?>

David

Re: perl/dbi - insert into server2.db2.table2 select * from server1.db1.table1

[dpgirago]

| Hi All,
|
| I have 2 separate mysql servers and need to import data from a table on
| sever1 to
| a table on server2.  This would need to be done in Perl.
|
| The script in question already has open handles to both servers, so I
| know I can
| select all the rows from server1 and process them one by one and insert
| into the table on server2, but I was wondering if there was a more
| simple way to do this which would allow me to transfer the data from one
| database handle directly to another?
|
| Looking on google, the closest example I can find is something like :
|
| #!/usr/bin/perl
| use DBI;
|
| $dbh1=.;
| $dbh2=.;
| ...
|
| $statement = "select a,b,c,d,... from table1 where condition='$value'";
| $sth=$dbh1->prepare($sql);
| my @results;
| while (@results = $sth->fetchrow_array) {
|
|  # build placeholders based on num of fields
|  my $placeholders;
|  $placeholders .= ($placeholders ? ",?" : "?") for (@results);
|
|  my $sth2 = $dbh2->prepare("INSERT INTO table2 values
| ($placeholders);");
|  $sth2->execute(@results);
|  $sth2->finish;
| }
|
| $sth1->finish;
| $dbh1->disconnect();
| $dbh2->disconnect();
|
| George Law
| [EMAIL PROTECTED]
| MSN: [EMAIL PROTECTED]
| Phone: 864-678-3161

George,

Did you try to "INSERT INTO db2.tablename SELECT fields from db1.tablename
where field='Somevalue'" across different servers? Don't know if it's
possible but it would certainly be more efficient.

David



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

Re: auto_increment field start value

[dpgirago]

 At 14:16 -0500 9/22/06, [EMAIL PROTECTED] wrote:
>> I seem to recall that when creating a table, you could designate an
>> auto_increment field to begin counting at zero(0) instead of one (1),
but I
>> can't find an example in the documents.
>>

> Don't store 0 in an AUTO_INCREMENT column.

Thanks Dan and Paul,

Since I can't store 0 in the column, I won't ;>)

For clarification,  I'm writing a DICOM application and will store a
complicated query result in table before further processing of the data. I
guess I'll deal with the "starts at  1, not 0" issue in the application.

David



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

Re: auto_increment field start value

[dpgirago]

OK. If you assign to auto_increment any number higher than what currently
exists in the column, it changes the value and the incremented sequence
from that point. But apparently you can't assign the value zero to the
column, even if the table is empty.

> Thanks, Dan, but I can't get it to work. Defining a column like this:
> << a int not null auto_increment=0 primary key >>
> throws an error, and while the alter table statement seems to work ok,
> whether the table is empty or not, it has no effect on subsequent
inserts.
> I'm wondering if 4.0.16 has not implemented this feature.
>
>> David - there's some info in the online docs here:
>> http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
>> Specifically:
>
>> To start with an AUTO_INCREMENT value other than 1, you can set that
>> value with CREATE TABLE or ALTER TABLE, like this:

>> mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

>> HTH,
>> Dan

>> On 9/22/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>>> I seem to recall that when creating a table, you could designate an
>>> auto_increment field to begin counting at zero(0) instead of one
(1),but I
>>> can't find an example in the documents.
>>>
>>> I'm using 4.0.16 and table type=myisam.
>>>
>>> David



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

Re: auto_increment field start value

[dpgirago]

Thanks, Dan, but I can't get it to work. Defining a column like this:
<< a int not null auto_increment=0 primary key >>
throws an error, and while the alter table statement seems to work ok,
whether the table is empty or not, it has no effect on subsequent inserts.
I'm wondering if 4.0.16 has not implemented this feature.

> David - there's some info in the online docs here:
> http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
> Specifically:

> To start with an AUTO_INCREMENT value other than 1, you can set that
> value with CREATE TABLE or ALTER TABLE, like this:

> mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

> HTH,
> Dan

> On 9/22/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>> I seem to recall that when creating a table, you could designate an
>> auto_increment field to begin counting at zero(0) instead of one (1),
but I
>> can't find an example in the documents.
>>
>> I'm using 4.0.16 and table type=myisam.
>>
>> David



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

auto_increment field start value

[dpgirago]

I seem to recall that when creating a table, you could designate an
auto_increment field to begin counting at zero(0) instead of one (1), but I
can't find an example in the documents.

I'm using 4.0.16 and table type=myisam.

David



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

Re: help with insert +php

[dpgirago]

> [EMAIL PROTECTED] escribió:
>> Marcelo,
>>
>> The usual advice is to echo out the query string and examine it for
syntax.
>> Assuming it has form data in it for the variables, then try
copying/pasting
>> it into the mysql client and run it from there. Let us know how that
goes.
>>
>> David
>>
> Already done that and it inserts one row

Have you run  mysql_error($link) after mysql_query() in PHP?





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

Re: help with insert +php

[dpgirago]

Marcelo,

The usual advice is to echo out the query string and examine it for syntax.
Assuming it has form data in it for the variables, then try copying/pasting
it into the mysql client and run it from there. Let us know how that goes.


David



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

Re: Insane execution time for JOIN query

[dpgirago]

>> On 8/31/06, Harrison Fisk ** <[EMAIL PROTECTED]> *** wrote:
>> Hello,
>>
>> On Aug 31, 2006, at 5:12 AM, Kim Christensen wrote:
>>
>>> Hey list;
>>>
>>> I posted this message under an earlier thread which touched the same
>>> subject - but I realized my case could be slightly different, thus
>>> posting a new thread here. Sorry for any inconvenience.
>>>
>>> I have two tables, one of which is a list over products which all have
>>> unique product id's (product_id). The other table, items, is a list
>>> over the products' corresponding prices and stock info; one row per
>>> each one of our supplier that has the item.
>>
>> The problem is that your product_id is an INT in one table and a
>> VARCHAR in the other.  This will cause MySQL to have to do
>> conversions of data types, which means it can't use indexes.  Switch
>> product_id to be an INT and both tables and it should solve your
>> performance problems.

> Ouch. I reduced the DELETE... query time to 0.4 secs in total after
> changing the second table's product_id to an INT. Thanks alot, I sure
> wouldn't have seen this on my own after staring myself blind on this -
> you've saved me lots of work!
>
> I'm sure gonna be more careful with the data types next time! :-)

Having taken a weeklong MySQL course with Harry a number of years ago, I'm
not surprised by his knowledge and acumen. It's a benefit to us all that he
still frequents this mailing list.

David Giragosian



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

Re: Table permissions - before the table is created?

[dpgirago]

Chris Jones wrote:
> Can the mysql administrator grant permissions on a table in an existing
> database if that table doesn't exist yet?  This is part of planning for
an
> upgrade to an existing application.  The mysql administrator won't be
there
> when I add the table to the existing database and I don't have grant
> privileges.

You can't do this using a grant statement because it is bound by existing
tables. You may be able to manipulate the mysql db tables directly - I was
able to insert a row in mysql.tables_priv for a nonexistent table - but I
don't know how this will play out once the table actually is created.
Perhaps give it a try on a test db...?

David



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

RE: Installing MySQL on Linux, HELP!

[dpgirago]

And then Eve said...

>After some trouble with rpm, I managed to create a file of installed
>packages. Redhat 9 says I have installed:

>MySQL-client-4.0.1-2
>perl-DBD-MySQL-2.1021-3
>mysql-3.23.58-1.9
>mysql-server-3.23.58-1.9

That's right. That's what you get with RH9. I'm repeating myself here from 
my email to you directly last evening, but I'd use the very clear, very 
accurate manual to build and install from source given at 
http://www.InternetSecurityGuru.com. Everything in that manual, related 
especially to MySQL, works just as documented. 

>And I am not having any luck with these...starting them, installing them,
>compiling them. I have read manuals, taken advice, but nothing 
comprehensive
>to get things to work.

>I would prefer to install a version of at least 4 for a server.

The manual uses 4.0.16 but you could substitute whatever version you'd 
like. 

>Any help is appreciated!

Help was given. You refused it.

>Thanks,
>Eve

BTW, at a MySQL training course I attended, the instructor advised _NOT_ 
to go the rpm route.

dave

MySQL upgrade on RedHat 9.0

[dpgirago]

I've MySQL-3.23.54a-11 installed and running on RH 9.0 via the RedHat 
rpm's. I'm trying to upgrade to MySQL-4.0.17-0 via rpm's from MySQL.com. I 
found references from web searches and through the archives from this list 
that suggest that it's as easy as: 

shell> rpm -Uvh --nodeps MySQL* 

but this results in numerous error messages saying that various files from 
4.0.17 conflict with files from 3.23.54a-11.

So then I tried uninstalling 3.23.54 first, then running:

shell> rpm -i MySQL* 

but this also did not work. Although when I ran:

shell> rpm -qa | grep MySQL* 

at this point, I got the correct list of rpm's that I was trying to 
install. However, no mysqld was found. 

I'd much appreciate some guidance. 

dave

Query to Find Max Sum of 60 Consecutive Fields from 60 Consecutive Records

[dpgirago]

Howdy MySQL Subscribers.

I'm hoping some MySQL users more experienced than myself can shed light on 
a work-related question that has recently arisen. We have a database with 
24 meter tables similar to meter1 below. 

mysql> explain meter1;
++--+---+---+---++
| Field   | Type  | Null   | Key | 
Default| Extra |
++--+---+---+---++
| date_time | datetime  |   |  
  |  -00-00 00:00:00   ||
| count_per_minute  | int(11)   |   || 
0||
| dose_equiv_per_minute | double unsigned | YES  | | NULL| 
  |
| counts| varchar(255)  | YES 
| | NULL|   |
++--++--++---+

The tables are populated with data from neutron monitoring stations; data 
are collected from the monitors and inserted into their respective tables 
once per minute. Currently there are some 45,000+ records in each table, 
though once in production mode we expect significantly higher record 
counts ( the schedule of data backups and table truncations has yet to be 
determined ).  The data looks like this: 

mysql> select date_time, count_per_minute, dose_equiv_per_minute from 
meter1 order by date_time desc limit 4, 10;
+-+---++
| date_time| count_per_minute | dose_equiv_per_minute 
|
+-+---++
| 2003-11-25 19:51:01 | 2310   | 0.483598  |
| 2003-11-25 19:50:01 | 2316   | 0.484855  |
| 2003-11-25 19:48:01 |  772| 0.161618   |
| 2003-11-25 19:47:01 | 3846   | 0.80516 |
| 2003-11-25 19:46:01 |8   | 0.001675|
| 2003-11-25 19:44:01 | 2313   | 0.484227  |
| 2003-11-25 19:43:00 | 2313   | 0.484227  |
| 2003-11-25 19:41:00 | 1032   | 0.216049  |
| 2003-11-25 19:40:00 | 3587   | 0.750938  |
| 2003-11-25 19:39:00 |7   | 0.001465|
+-+---++

We have a web GUI written in PHP that calls a C program which queries the 
database, analyzes the data, and creates a PDF report. One of many 
analytical tasks of this program is to find the maximum sum of the field, 
`dose_equiv_per_minute`, from any 60 consecutive records. In other words, 
we need to sum the values from `dose_equiv_per_minute` for records 1 
through 60, 2  through 61, 3  through 62, etc... then determine which is 
the max sum. Currently this is being handled on the client-side through 
the C program, which selects all of the (many thousands of ) records, then 
does the math and loops through them, group by group. As you can imagine, 
if one were creating reports from all 24 monitors, the number-crunching 
can be quite time consuming (upwards of 5 minutes at present for just over 
6 weeks worth of data). So the question is, can we save some time by 
asking the MySQL server to crunch the numbers, and if so, what would the 
query be to accomplish this task?

Any and all suggestions greatly appreciated.

TIA , 

dave 

Re: Primary key

[dpgirago]

(snip)


 Does creating a primary key
 on an int immediatly involve this one to be not null, and to
 auto-increment?
>>>
>>>
>>>
>>> NOT NULL - yes, but if you want to have AUTO_INCREMENT column you
>>> should declare it as AUTO_INCREMENT.
>>
>>
>> Thank you very much for your reply.
>> But I read that a PRIMARY KEY is a "globally unique identifier" for a
>> table. As it is an identifier, it should never be null (ok, that's
>> what you said), and it should be unique... Then, if I don't set
>> AUTO_INCREMENT to my column ID, it still should be unique, and then
>> increment itself... No?
>
>
> Not all unique identifiers are computer-generated.  If you leave off
> AUTO_INCREMENT but still make it a primary key, uniqueness will be
> enforced on whatever the application(s) enter(s) as the value, but
> nothing will be plugged in.
>
> Bruce Feist
>
>
>>
>Ok, I'm not sure Iunderstood the real (technical) reason, but I
>understood that I had to use the AUTO_INCREMENT to be sure of a nice
>functionning of my database.
>Thank you very much all.

Let me give it a try. The primary key is *required* to be NOT NULL because the
value in the key *must* identify one and only one record in the table. If you
don't want to manually insert UNIQUE values in the ID column to accomplish this,
for example, in the mysql client, or by some other method such as an application
that writes data to the table, then using an AUTO_INCREMENT column attribute
will *enforce* the necessary uniqueness that a primary key requires. It is this
way be design and definition.




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

Re: Suggestions for choosing GUI Language that has a MySQL DB backend

[dpgirago]

The application will be run on windows2000. Or maybe Linux ... there is some
choice ;-)

>>Will this run on Windows or Linux?


Thank you, Martin, Mitko, John Griffin, David Christensen, Michael T. Babcock,
and sundisk
for your replies.

(Sorry for the delay...I work in several locations on Thursdays)

Delphi or Kylix... So it sounds like I'm headed for Linux-land.

David



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

Suggestions for choosing GUI Language that has a MySQL DB backend

[dpgirago]

Hi MySQL Listers,

I'm wondering if someone might provide some guidance about what programming
language might best accomplish the following.

I have been asked to write an interactive GUI program that allows for the
display of results from related queries.
For example, launching the application wil generate the first query, which will
return a list of names of individuals.
Selecting a name triggers another query that returns demographic information
about that person and a list of their interests.
Selecting an interest from the list triggers another query that returns a list
of venues such as museums or restaurants where the person can explore that
interest.
And selecting the venue triggers a query that returns information such as
driving directions or food menu.

I have the MySQL database designed and tested, and the queries work as intended.
Also, the load on mysqld will be limited, so performance is not a major issue.
Unfortunately, my employer does not want a web-based solution, so PHP is out.
And all the activity has to occur in various sections of a single screen.

My apologies if I'm posting to the wrong list. But if someone has accomplished
something similar to the above using a MySQL backend, I would appreciate any
advice or direction that might be provided.

Thank you for reading this post.

David



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

Re: Thoughts on MySQL training?

[dpgirago]

Hi Rachel,

I attended a MySQL weeklong training last October -- 2002.  It was 2 days of
querying and data manipulation and 3 days of administration. I did not have a
very significant history with MySLQ before the training, but I had read the
entire manual, so the first 2 days were very engaging. But since I had not done
any real administration before hand,  the administration training tended to all
run together by day 3. To be fair, there were several experienced DBA's in
attendance, so the pace by necessity was fairly brisk to meet the needs of the
majority of the students. I guess I can say that the course at the very least
gave me a solid foundation in MySQL basics and enough of a running start to feel
comfortable with performing database backups and starting master / slave
replication. I think the price is probably about average for tech training these
days.

The printed materials handed out were very good, but the print was tiny for
these tired old eyes.

DPG



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

Re: Error 28

[dpgirago]

Perhaps it's this...

C:\>perror 28
Error code  28:  No space left on device


David



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

Re: ERWin 4.1 not applicable for MySQL

[dpgirago]

I have successfully used Erwin 4.1 to connect to and model MySQl databases. Read
the Erwin tutorial. You need to create an ODBC connection first.

David

sql, query



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

Best MySQL - PHP Book(s)

[dpgirago]

Howdy,

Can anyone recommend a good reference book for using PHP with MySQL?
I've read and enjoyed Paul's 'DoorStop' and 'CookBook' and have a fairly good
grasp of MySQL. I've also written a bare bones database browser for Win32 using
Apache, MySQL, and PHP, so I'm not an absolute newbie. But a book that gives
lots of information and examples about using PHP with MySQL - from the ground
up, so to speak - would be helpful.

Thank you all for your time.

David



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

Suggested Addition to MySQL Reference Manual

[dpgirago]

Howdy All,

The MySQL Reference Manual lists 2 limitations on the way the mysql client
parses /* ...  */ comments, these being:

  Single-quote and double-quote characters are taken to indicate the
beginning of a quoted string, even within a comment.
  If the quote is not matched by a second quote within the comment, the
parser doesn't realise the comment has ended.
  If you are running mysql interactively, you can tell that it has gotten
confused like this because the prompt changes from mysql> to '> or ">.

 A semicolon is taken to indicate the end of the current SQL statement and
anything following it to indicate the beginning of the next statement.
 (Section 6.1.6 Comment Syntax)

I would like to suggest that a third condition be included in this list. It
seems that either of the other 2 comment characters, *  --  * or  *  #  *  are
also
recognized by the parser within /*  ...  */.

I discovered this by accident when running a database / table creation script.
Within the SQL statements creating one table, a comment line such as
this appeared:  /* some words here -- is this field related to foo?  */.  Next
came several field / attribute statements that were syntactically correct,
followed by
another comment:  /* some more words here  */.  The script ran without error,
but the fields between these two comments were not created. Apparently
the parser recognized the double-dash-and-space within the first  /* ...  */ ,
commented out the closing  */ of the first comment,  and everything else until
the closing   */
of the second comment.  I then experimented with the * # * character and found
that the same behavior occurred.

I know it's never a good idea to nest comments. And now I really know it's never
 a good idea to nest comments. Some of us are just slow learners.

David Giragosian, Psy.D.
MD Anderson Cancer Center
Houston, Texas



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: error in crerating a database

[dpgirago]

The correct syntax for creating a database is:

mysql > create database databse_name;

mysqladmin is a command line program, not for use inside the mysql client
program.

David



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: translation dictionary

[dpgirago]

I guess you'd have to use MySQL 4.1 to assign different character sets
to different columns. Should be straight-forward at time of table creation.

David

***
Hello,

I'm interested in building a translation dictionary:
russian - french. My database would have only one
table which contains in each entry a the word and its
translation. I intend to put such a dictionary on a
web page.

I would like to ask for some ideas on how to build
such a dictionary given that I can not use more than
one set of characters in mysql server. ( the russian
words need on set of characters , the french ones
another ).

I would appreciate any hint.

kind regards,

Felix



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: suspected bug...

[dpgirago]

I could be lame, but I couldn't reproduce the error.
The query worked ok for me -- using 4.0.4-beta-max-nt


>i posted this to the newsgroups and got told it was probably a sql
>parser bug so here goes...
>
>if u try to execute a query such as:
>
>insert into foo (id,textfield) values (54,'\\fred\\')
>
>the query fails
>
>if i add a space after the last \\ it works
>this is a real pain personally cos we use a lot of that stuff :(
>any workarounds or fixes or whatnots?
>
>many thanks
>
>lauren

David



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Converting DBs

[dpgirago]

[filter fodder = sql,query,queries,smallint ]

you haven't indicated what the column attributes are,
but you might try putting quotes around the  TRUE and FALSE
values ( assuming they are being inserted into char or varchar
columns).

David



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: list current db

[dpgirago]

[filter fodder = sql,query,queries,smallint]

mysql > select database();

David

**
Jonathan said:
**

A few days ago I asked about how to know which database I am currently
in, I got an answer and I also replied to thank the send but the email
was returned. Any way I forgot to document it. I remember the answer
listed three methods to do it, they are "mysql> \s", "mysql> status".

Could you (or someone) point me the third method?



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: problems with GRANT, user, databases

[dpgirago]

why don't you try...

mysql > SHOW GRANTS for 'junk'@'localhost'

just to see what has been granted at present?

David



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Creating a fixed length char field...

[dpgirago]

If your create table statement has both char's and varchar's in it, then MySQL
converts the char's all  to
varchar's.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Searching on indexed char field...

[dpgirago]

sql, mysql...

oops, forgot the quotes>>

 You might also try indexing only a portion of the char[50] index.

>> index (UserName(20)), for example...

David



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Searching on indexed char field...

[dpgirago]

sql, mysql...

 You might also try indexing only a portion of the char[50] index.

index UserName(20), for example...

David



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Colonel in Cuba with MySQL question

[dpgirago]

I inadvertantly deleted the colonel's question, which I believe concerned
winmysqladmin.
  Please give us more info. You should be able to see your databse(s) under the
database tab.

David



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: CASE statement within SUM()

[dpgirago]

That's also right:

 select sum( case 1 when score=7 then score  *  2  else  score  end) from "foo";






[EMAIL PROTECTED] on 03/07/2003
11:28:03 AM

To:   Peter D Bethke <[EMAIL PROTECTED]>, MySQL List
  <[EMAIL PROTECTED]>
cc:(bcc: David P. Giragosian/MDACC)

Subject:  Re: CASE statement within SUM()



At 11:29 -0500 3/7/03, Peter D Bethke wrote:
>Hello listers,
>
>In mysql, is it possible imbed a conditional CASE statement within a SUM()
>function to effect how the SUM() returns the final value for a set of rows?
>
>For example, if I have a table "foo" with column "score", and "score" has
>the rows with the values 1 to 10, can I write the SUM() in such a way to
>double the value of the number 7 for example to the final sum? Ie when the
>SUM() function encounters the number 7, add (score*2) instead of just score
>to the final total.
>
>Something like:
>
>SUM(CASE WHEN score = 7 THEN score*2 ELSE score END) AS final_sum
>
>(Of course this doesn't seem to work, but it's the best I can approximate)

There's no reason it shouldn't work.  What was the exact query you used,
and what was the result? What error message, for example.

>
>Any help, much much appreciated.
>
>Best,
>
>Peter D Bethke


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: CASE statement within SUM()

[dpgirago]

Peter,

I think something like this will work:

mysql> select sum(  if(score=7,  score * 2,  score)) from table_name;

David



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php