Re: basic sql join question...

[Philip George]

On May 30, 2005, at 1:28 AM, [EMAIL PROTECTED] wrote:


you can just play :


  set @total:=0;
  select name,price,quantity, price*quantity as
  subtotal,@total:[EMAIL PROTECTED]
  from fruits;


select @total as "grand total";



works great.  thanks very, very much.

- philip



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

Re: basic sql join question...

[mfatene]

Hi Philip,
what yoy called gand total is in @total for evevy row.

you can just play :

> >   set @total:=0;
> >   select name,price,quantity, price*quantity as
> >   subtotal,@total:[EMAIL PROTECTED]
> >   from fruits;

select @total as "grand total";

that's all !

Mathias

Selon Philip George <[EMAIL PROTECTED]>:

> On May 29, 2005, at 2:34 PM, [EMAIL PROTECTED] wrote:
>
> >   you can use mysql variables :
> >
> >   set @total:=0;
> >   select name,price,quantity, price*quantity as
> >   subtotal,@total:[EMAIL PROTECTED]
> >   from fruits;
> >
> >
> > ++---+--+--
> > +---+
> >   | name   | price | quantity | subtotal |
> > @total:[EMAIL PROTECTED] |
> >
> > ++---+--+--
> > +---+
> >   | orange | 1 |2 |2 |
> > 2 |
> >   | banana | 1 |4 |4 |
> > 6 |
> >
> > ++---+--+--
> > +---+
> >
> >   The total column will be incremented by subtotal in each row.
>
>
> actually, i need a grand total of the entire ticket:
>
> 1 orange x 0.97 = 0.97
>   + 3 pears  x 1.09 = 3.27
> _
>   4.24  < grand total
>
>
> i think you're right that variables can be used to do this, but i can't
> figure out how to get a grand total for the entire sale.
>
> but, i wouldn't scratch your head about it too much.  i sort of gave up
> on the idea in favor of doing the calculation in the client code at
> runtime after selecting all the pertinent data.
>
> thanks though.  :)
>
> - philip
>
>
>
>
> --
> 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: performance on single column index with few distinct values

[mfatene]

Hi,
remember that using index can be slower that FTS. if there is a low index column
selectivity, it's normal that your query doesn't use it.

try using some hints to force index usage (like in oracle) or put your table as
an index organiztion table.

look also at myisam merge tables.

mathias



Selon Terence <[EMAIL PROTECTED]>:

> Hi Daniel,
>
> Here's some more data which might help. This query does not appear to be
> using the composite index although there is one on the master_id and
> ticket_id. The below query takes about 3 seconds. Anyway I think I will
> still move to seperate tables for each master id. Thanks for the reply.
>
> EXPLAIN SELECT *
> FROM helpdesk_tickets ht
> WHERE ht.master_id = '1'
> ORDER BY ticket_id DESC
> LIMIT 0,10
>
> id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra,
> 1,SIMPLE,ht,range,master_id,master_id,4,,168439,Using where; Using
> filesort,
>
> CREATE TABLE `helpdesk_tickets` (
>  `ticket_id` int(5) NOT NULL auto_increment,
>  `running_id` int(5) NOT NULL default '0',
>  `master_id` int(5) NOT NULL default '0',
>  `category_id` int(5) NOT NULL default '0',
>  `sub_category_id` int(5) NOT NULL default '0',
>  `priority_id` int(5) NOT NULL default '0',
>  `location_id` int(5) NOT NULL default '0',
>  `status_id` int(5) NOT NULL default '0',
>  `user_logging_id` int(5) NOT NULL default '0',
>  `user_problem_id` int(5) default '0',
>  `other_user_problem` varchar(255) NOT NULL default '',
>  `title` varchar(255) NOT NULL default '',
>  `description` text NOT NULL,
>  `submit_date` datetime default NULL,
>  `submit_month` tinyint(2) NOT NULL default '0',
>  `complete_date` datetime default NULL,
>  `feedback_id` int(5) NOT NULL default '0',
>  `review_id` int(5) NOT NULL default '0',
>  `feedback_comments` text NOT NULL,
>  `review_comments` text NOT NULL,
>  `significance_id` int(5) NOT NULL default '0',
>  `time_spent` int(5) NOT NULL default '0',
>  `requires_ams_update` enum('y','n') NOT NULL default 'n',
>  `duplicate_ticket` enum('y','n') NOT NULL default 'n',
>  `friendly_id` int(5) NOT NULL default '0',
>  `due_date` datetime NOT NULL default '-00-00 00:00:00',
>  PRIMARY KEY  (`ticket_id`),
>  UNIQUE KEY `problem_id` (`ticket_id`),
>  KEY `priority_id` (`priority_id`),
>  KEY `category_id` (`category_id`),
>  KEY `sub_category_id` (`sub_category_id`),
>  KEY `location_id` (`location_id`),
>  KEY `user_logging_id` (`user_logging_id`),
>  KEY `user_problem_id` (`user_problem_id`),
>  KEY `status_id` (`status_id`),
>  KEY `submit_date` (`submit_date`),
>  KEY `running_id` (`running_id`),
>  KEY `submit_month` (`submit_month`),
>  KEY `master_id` (`master_id`),
>  KEY `comp_ticket_master` (`ticket_id`,`master_id`),
>  FOREIGN KEY (`user_logging_id`) REFERENCES `intranet_user_login`
> (`user_login_id`),
>  FOREIGN KEY (`sub_category_id`) REFERENCES
> `helpdesk_sub_category_master` (`sub_category_id`),
>  FOREIGN KEY (`category_id`) REFERENCES `helpdesk_category_master`
> (`category_id`)
> ) TYPE=InnoDB CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='InnoDB free:
> 97280 kB'
>
> Daniel wrote:
>
> >Is there a composite index on (master_id, ticket_id)? Since your queries
> >are selecting on a particular master_id, and ordering by ticket_id, along
> >with the limit I think MySQL would be able to use such an index in an
> >optimization.
> >
> >-Daniel
> >
> >-Original Message-
> >From: Terence [mailto:[EMAIL PROTECTED]
> >Sent: Friday, May 27, 2005 10:52 PM
> >To: mysql@lists.mysql.com
> >Subject: performance on single column index with few distinct values
> >
> >
> >Hi list,
> >
> >I have run into problems on a master table for our helpdesk. We have the
> >following table:
> >
> >ticket_id (int) - autoincrement (indexed)
> >master_id (int) (indexed)
> >
> >Master ID is used to distinguish multiple helpdesks. In this table there
> >are 100k records, but only 10 distinct master_id's.
> >For example:
> >
> >ticket_id   master_id
> >1   1
> >2   1
> >3   2
> >4   2
> >5   3
> >...  ...
> >
> >When trying to do pagination I use the following SQL:
> >
> >SELECT ticket_id
> >FROM my_table
> >WHERE master_id = '1'
> >ORDER BY ticket_id DESC
> >LIMIT 0,10
> >
> >The problem is that there are 20k records where master_id = 1, so the
> >lookup is pretty slow especially when I start joining other tables. When
> >joining other tables the query gets slower and slower, I guess because
> >the lookups on joining tables result in fewer rows being joined when
> >using EXPLAIN.
> >
> >SELECT *
> >FROM helpdesk_tickets ht, helpdesk_category_master hcm,
> >helpdesk_sub_category_master hscm
> >WHERE ht.master_id = '1'
> >AND ht.category_id = hcm.category_id
> >AND ht.sub_category_id = hscm.sub_category_id
> >ORDER BY ticket_id DESC
> >LIMIT 0,10
> >
> >I have thought of options such as using temporary tables to just grab
> >the last 10 tickets and then do an IN query, however I need to display
> >tota

Re: user

[mfatene]

so contunue here http://dev.mysql.com/doc/mysql/en/news-5-0-x.html

mathias

Selon almo geeth <[EMAIL PROTECTED]>:

> hai .
>
>  send me the details of database server feature comparison between 4.0
> and 5.0 beta version .if we upgrade our server form 4.0 to 5.0 or 4.1 what
> are all the changes will happen with our tables and databases.let me know the
> details of functions what wil support in 4.1 and 5.0 afer upgradtion ..i
> already visit the site of urs but in that one there is only databaser server
> feature comparison between 4.0 and 4.1 not 5.0 ..
> so i kindly request u to send me the details of the above one as soon as
> possile.
>
> thanking u ...
>
> almo
>
>
> Yahoo! India Matrimony: Find your life partneronline.



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

Re: mysql and php

[mfatene]

sorry for trying to help, but if it was my problem, i would have found something
in the second url.

mathias


Selon [EMAIL PROTECTED]:

> This is very old, several years, info you've been passing along.
> Mysql switches are on by default now.  Mac OSX (Server for me) is not
> 10.4 Tiger, you've posted 10.2 Jaguar info, one version prior to 10.3
> Panther which was itself released over a 18 months ago.
>
> Thanks anyway.  :)
> Gil
>
>
> On May 29, 2005, at 5:34 AM, [EMAIL PROTECTED] wrote:
>
> > hi,
> > php has to be configured with mysql support. see
> > http://www.whoopis.com/howtos/php-mysql-osx.html
> >
> > and a general google search :
> > http://www.google.fr/search?q=mysql+php+mac+os&hl=fr&lr=&start=30&sa=N
> >
> > Mathias
> >
> >
> > Selon Michael Stassen <[EMAIL PROTECTED]>:
> >
> >
> >> You haven't given us much to go on, other than "it doesn't work,"
> >> which
> >> isn't really helpful.  We can't give you "specific ideas where to
> >> look"
> >> until you give us specific details of what's wrong.  Please describe
> >> what happens.  Do you get an error message?  What is it?  While
> >> you're
> >> at it, show us the relevant lines of code (hide the password, of
> >> course).
> >>
> >> You say mysql, php, and apache are working separately.  I take it
> >> that
> >> php scripts are running via web access (apache)?  I ask because some
> >> previous Mac OS upgrades replaced the apache config with a newer one
> >> with php turned off.
> >>
> >> Meanwhile, the Apache user is irrelevant.  The php script just
> >> needs a
> >> valid *mysql* user and password to authenticate.
> >>
> >> "bounce" = restart
> >>
> >> Michael
> >>
> >> [EMAIL PROTECTED] wrote:
> >>
> >>
> >>>  From your first message below, are you suggesting a setting in
> >>> Apache
> >>> needs to be ticked?
> >>> I can do it if I know where to look.  I am no expert, just one
> >>> who  uses
> >>> tis setup, i.e., "it just works."  I have used the previous
> >>> version for
> >>> years and it "just worked."  That's why I have no idea  what could
> >>> possibly be different.
> >>>
> >>> Not to say I haven't built these before, but this should be
> >>> working!  :(
> >>> What's led me to this problem is that I have phpmyadmin installed
> >>> and
> >>> it can't access mysql as  it could just fine before.
> >>>
> >>> I have had help from the guys at phpmyadmin who have no sent me
> >>> here.
> >>> No one "gets it."
> >>>
> >>> So, any specific ideas where to look???
> >>>
> >>> Thanks,
> >>> Gil
> >>>
> >>> p.s. I don't know what you mean by "bounce the MYSQL DB."
> >>>
> >>> On May 28, 2005, at 9:47 PM, sol beach wrote:
> >>>
> >>>
> >>>
>  I don't do MACs, but here is a shot in the dark.
>  In some/many cases Apache gets invoke as user "nobody";
>  which would be the the "OS user" that needs to be granted access to
>  MYSQL.
> 
>  I'm willing to bet that the failure to connect into MYSQL via
>  PHP from
>  Apache is due
>  to a permissions/login/authorization issue.
> 
> 
> >>>
> >>> You also wrote:
> >>> If you enable LOGGING & bounce the MYSQL DB, some clue about what
> >>> is  or
> >>> is not
> >>> happening to MYSQL will be written to the logfile.
> >>>
> >>> Discern what the clues are reporting and fix the problem.
> >>>
> >>>
>  HTH & YMMV
> 
>  On 5/28/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> 
> 
> > I'm on Mac OSX and Mysql is built-in, so is php.
> >
> > My php has built-in support for mysql.
> >
> > Both php and mysql are up and running.  I have tested both.
> > All this
> > is running on this machine, so is Apache.
> >
> > I can log into mysql at the CLI with my superuser and access the
> > 'mysql' db -the only one I currently have.
> >
> > I have a good php test connect script but cannot connect to mysql.
> > I have checked the mysql variables and entered the socket location
> > into the script, no help.
> >
> > What else can possibly be wrong?  Is there something in a mysql
> > config file or something that is off?
> >
> > This is an upgrade install of Mac OSX 10.4 Server over 10.3 Server
> > (I'm not a wiz) but have used it long enough.  I have changed
> > nothing
> > and it worked fine before.
> >
> > Any suggestions?  Again, I did not build this it's all built-in
> > and
> > working fine.  I simply can't connect with php.  Separately, mysql
> > and php both work.
> >
> > Thanks,
> > Gil
> >
> >>
> >>
> >> --
> >> 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]

user

[almo geeth]

hai .
 
 send me the details of database server feature comparison between 4.0 and 
5.0 beta version .if we upgrade our server form 4.0 to 5.0 or 4.1 what are all 
the changes will happen with our tables and databases.let me know the details 
of functions what wil support in 4.1 and 5.0 afer upgradtion ..i already visit 
the site of urs but in that one there is only databaser server feature 
comparison between 4.0 and 4.1 not 5.0 ..
so i kindly request u to send me the details of the above one as soon as 
possile.
 
thanking u ...
 
almo 


Yahoo! India Matrimony: Find your life partneronline.

Displaying Dynamic Japanese

[Mark Sargent]

Hi All,

I have my settings in php.ini set for UTF-8, and the encoding for the 
database table's column that is using Japanese to UTF-8. Now, if I view 
the data stored in that column in phpmyadmin, via say, firefox, it 
displays in UTF-8, but, if I pull the code from the database and display 
it in a UTF-8 set page, it is just ? marks, although static J text 
displays fine...any thoughts on this..? Driving me nutz. Cheers.


Mark Sargent.

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

Porting Application to MySQL

[George Sexton]

I have an application that right now supports SQL Server, Access, DB2,
Oracle 8 & 9, Sybase SQL Anywhere, and PostgreSQL. I thought since version
5.0 was finally supporting views, I would try porting my app to it. These
comments are based on testing 5.0.4 under Linux.

Here are my initial results. I'd appreciate any helpful comments. The
problems I've seen so far are:

DEFAULT clause limitations
TABLE IDENTIFIER CASE SENSITIVITY

So far, I'm stuck and it doesn't look like MySQL 5.0.x will achieve the
level of standards compliance needed to run my cross platform/cross database
application.

**
DEFAULT clause limitations
**
My first pass was to set my date/time fields to DATETIME with DEFAULT NOW().
After some unhelpful error messages I found:

http://dev.mysql.com/doc/mysql/en/create-table.html
The DEFAULT clause specifies a default value for a column. With one
exception, the default value must be a constant; it cannot be a function or
an expression. This means, for example, that you cannot set the default for
a date column to be the value of a function such as NOW() or CURRENT_DATE.
The exception is that

So I thought OK, I'll change to TIMESTAMP with CURRENT_TIMESTAMP. Then I ran
into the limitation that you cannot have more than one timestamp column in a
table with a default of CURRENT_TIMESTAMP()

These are really silly limitations that should be removed in the 5.0.x beta
series. Preferably, datetime should allow DEFAULT NOW().

**
TABLE IDENTIFIER CASE SENSITIVITY
**

My reading of the documentation says that table identifier case sensitivity
depends upon the underlying file system, and that the default can be changed
by a startup variable to the mysql daemon.

I can just see the conversation ...

Me: Gee Mr. Big ISP, I need you to change a global setting on your database
for your customer to run my application:
Mr. Big ISP: Rrrrighttt

There really needs to be a session level setting to control this issue. This
is really ridiculous. The SQL Standard says that identifiers are not case
sensitive. My application is probably OK, but this is just ridiculous. Even
if I fix my App, I'll have to verify every sql statement under MySQL to make
sure that it works with it.

George Sexton
MH Software, Inc.
http://www.mhsoftware.com/
Voice: 303 438 9585
 


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

Re: performance on single column index with few distinct values

[Terence]

Hi Daniel,

Here's some more data which might help. This query does not appear to be 
using the composite index although there is one on the master_id and 
ticket_id. The below query takes about 3 seconds. Anyway I think I will 
still move to seperate tables for each master id. Thanks for the reply.


EXPLAIN SELECT *
FROM helpdesk_tickets ht
WHERE ht.master_id = '1'
ORDER BY ticket_id DESC
LIMIT 0,10

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra,
1,SIMPLE,ht,range,master_id,master_id,4,,168439,Using where; Using 
filesort,


CREATE TABLE `helpdesk_tickets` (
`ticket_id` int(5) NOT NULL auto_increment,
`running_id` int(5) NOT NULL default '0',
`master_id` int(5) NOT NULL default '0',
`category_id` int(5) NOT NULL default '0',
`sub_category_id` int(5) NOT NULL default '0',
`priority_id` int(5) NOT NULL default '0',
`location_id` int(5) NOT NULL default '0',
`status_id` int(5) NOT NULL default '0',
`user_logging_id` int(5) NOT NULL default '0',
`user_problem_id` int(5) default '0',
`other_user_problem` varchar(255) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`description` text NOT NULL,
`submit_date` datetime default NULL,
`submit_month` tinyint(2) NOT NULL default '0',
`complete_date` datetime default NULL,
`feedback_id` int(5) NOT NULL default '0',
`review_id` int(5) NOT NULL default '0',
`feedback_comments` text NOT NULL,
`review_comments` text NOT NULL,
`significance_id` int(5) NOT NULL default '0',
`time_spent` int(5) NOT NULL default '0',
`requires_ams_update` enum('y','n') NOT NULL default 'n',
`duplicate_ticket` enum('y','n') NOT NULL default 'n',
`friendly_id` int(5) NOT NULL default '0',
`due_date` datetime NOT NULL default '-00-00 00:00:00',
PRIMARY KEY  (`ticket_id`),
UNIQUE KEY `problem_id` (`ticket_id`),
KEY `priority_id` (`priority_id`),
KEY `category_id` (`category_id`),
KEY `sub_category_id` (`sub_category_id`),
KEY `location_id` (`location_id`),
KEY `user_logging_id` (`user_logging_id`),
KEY `user_problem_id` (`user_problem_id`),
KEY `status_id` (`status_id`),
KEY `submit_date` (`submit_date`),
KEY `running_id` (`running_id`),
KEY `submit_month` (`submit_month`),
KEY `master_id` (`master_id`),
KEY `comp_ticket_master` (`ticket_id`,`master_id`),
FOREIGN KEY (`user_logging_id`) REFERENCES `intranet_user_login` 
(`user_login_id`),
FOREIGN KEY (`sub_category_id`) REFERENCES 
`helpdesk_sub_category_master` (`sub_category_id`),
FOREIGN KEY (`category_id`) REFERENCES `helpdesk_category_master` 
(`category_id`)
) TYPE=InnoDB CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='InnoDB free: 
97280 kB'


Daniel wrote:


Is there a composite index on (master_id, ticket_id)? Since your queries
are selecting on a particular master_id, and ordering by ticket_id, along
with the limit I think MySQL would be able to use such an index in an
optimization.

-Daniel

-Original Message-
From: Terence [mailto:[EMAIL PROTECTED]
Sent: Friday, May 27, 2005 10:52 PM
To: mysql@lists.mysql.com
Subject: performance on single column index with few distinct values


Hi list,

I have run into problems on a master table for our helpdesk. We have the 
following table:


ticket_id (int) - autoincrement (indexed)
master_id (int) (indexed)

Master ID is used to distinguish multiple helpdesks. In this table there 
are 100k records, but only 10 distinct master_id's.

For example:

ticket_id   master_id
1   1
2   1
3   2
4   2
5   3
...  ...

When trying to do pagination I use the following SQL:

SELECT ticket_id
FROM my_table
WHERE master_id = '1'
ORDER BY ticket_id DESC
LIMIT 0,10

The problem is that there are 20k records where master_id = 1, so the 
lookup is pretty slow especially when I start joining other tables. When 
joining other tables the query gets slower and slower, I guess because 
the lookups on joining tables result in fewer rows being joined when 
using EXPLAIN.


SELECT *
FROM helpdesk_tickets ht, helpdesk_category_master hcm, 
helpdesk_sub_category_master hscm

WHERE ht.master_id = '1'
AND ht.category_id = hcm.category_id
AND ht.sub_category_id = hscm.sub_category_id
ORDER BY ticket_id DESC
LIMIT 0,10

I have thought of options such as using temporary tables to just grab 
the last 10 tickets and then do an IN query, however I need to display 
totals, so that would require me to run the query again.


My questions are:

1) Is there any point to having an index on a column with so few unique 
values?
2) Would it make more sense to have multiple master tables for each 
helpdesk? Such as:

helpdesk_tickets_1
helpdesk_tickets_2
helpdesk_tickets_3 etc.
and then using a session value to query the table?
3) Any other tips or advice? (I notice my query time doubles from 100k 
rows to 150k rows)


Thanks for any help...

Terence

 



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

multi-thread insert into innodb table with auto_increment column cause deadlock

[Gu Lei]

Description:
 SQL state: HYT00
 Native error: 1213
 SQL Errortext: Deadlock found when trying to get lock; try restarting
transaction

SQL STATEMENT:
insert into OCHARTORGANIZATION
(CORPID,MODIFIED,MODIFIER,ORGIID,PARENTIID)
values(?,?,?,?,?)

mysql> desc OCHARTORGANIZATION;
+---+--+--+-+---++
| Field | Type | Null | Key | Default   |
Extra  |
+---+--+--+-+---++
| CORPID    | varchar(32)  | YES  | | NULL 
|    |
| MODIFIED  | timestamp    | YES  | | CURRENT_TIMESTAMP
|    |
| MODIFIER  | varchar(255) | YES  | | NULL 
|    |
| ORGIID    | varchar(32)  |  | PRI |  
|    |
| PARENTIID | varchar(32)  | YES  | | NULL 
|    |
| SN    | int(11)  |  | UNI | NULL  |
auto_increment |
+---+--+--+-+---++
6 rows in set (0.00 sec)

client:  OS fedora3   MyODBC3.51.10  unixODBC 2.2.9
server: OS RedHat Adavanced server3.0 MySQL max 4.1.10

My program use multi-threads to insert records into some tables by
MyODBC. Each
thread has one connection with AUTOCOMMIT OFF.
When insert into other tables it's OK.
Error occured when execute this statement concurrently:
insert into OCHARTORGANIZATION
(CORPID,MODIFIED,MODIFIER,ORGIID,PARENTIID)
values(?,?,?,?,?)

[EMAIL PROTECTED] mysql]$ cat /etc/odbcinst.ini
[MySQL]
Description = ODBC for MySQL
Driver  =
/home/hyli/asterisk_software/MyODBC-3.51.10-pc-linux-i686/lib/libmyodbc3.so
Setup   = /usr/lib/libodbcmyS.so
FileUsage   = 1

If I only run one thread to insert all records, error won't occur.
If my program connects to the local mysqld(on the same machine with my
program),
error won't occur.
Local mysqld's version is also MySQL max 4.1.10

Before I added this code everything is OK.

    char autocommit[]="SET AUTOCOMMIT=0";
    rc =
SQLExecDirect(hstmtset,(SQLCHAR*)autocommit,sizeof(autocommit)-1);

I really need some solution.

Best regards,

Gu Lei
-- 

Re: mysql and php

[Michael Stassen]

[EMAIL PROTECTED] wrote:

Remember guys, I went through this on the phpmyadmin list before  
turning here.


That doesn't help us much, as we haven't seen that conversation.

I was told to run the command 'mysqladmin variables' to find out where 
the socket was; I did that successfully and it showed up  at 
/var/mysql/mysql.sock.


And viola, now with mysqld running, it does show up there, at /var/ 
mysql/mysql.sock.  When I shutdown mysqld it is gone, from /var/mysql.


Earlier, you wrote:

> What I do get is why running 'mysqladmin -variables' reports the
> location of mysql.sock and there seems to be no mysql.sock anywhere
> on this machine!  Not in /tmp not /var/mysql (where it says it is).

At that point, then, mysqld was not running, so connecting would have 
been impossible.  Now mysqld is running, so connecting *should* be 
possible.  Indeed, you report success connecting via the mysql client.


Unfortunately, this puts us back at square one.  To diagnose the 
problem, we need the php error message when the connect fails.  I 
suspect the message you showed before was from when mysql wasn't 
running.  I could be wrong, but with just bits and pieces, rather than a 
clear narrative, it's hard to know what's going on.


(Btw, once in a recent post of mine, Philip referred to my leaving  out 
the leading / at 'var', that was simply a typo and it was in the  script 
post.)


So, running 'mysql variables' is giving me the socket path and I'm  
still lost as to what's going why I cannot connect with a simple php  
script.


php can tell you what's wrong, if you let it.  The code you showed us 
doesn't really check for errors.  Try changing it to this:




Make sure mysqld is running (/var/mysql/mysql.sock exists, can connect 
with mysql client) then try this script.  If it fails to connect, post 
the error message.



Help?!  I'll look for the mysql error log.


The default location of the log is the file hostname.err in mysql's data 
directory (where hostname is the name of your machine).  I don't think 
the mysql log will help though, as the error appears to be on the php side.



Gil


Meanwhile, I should point out that while it is true that Mac OS X Server 
10.4 ships with mysql and php, for some reason they've been shipped with 
incompatible defaults, as you are seeing.  Have you read what Apple has 
to say on the matter 
?  If you 
haven't, then you probably haven't changed permissions on /var/mysql so 
that www, the apache user, can access the socket.  They suggest


  sudo chmod 775 /var/mysql

but

  sudo chmod 711 /var/mysql

should be sufficient (and safer).

They also suggest editing/creating /etc/php.ini to make the correct 
location of mysql's socket file the default for php, though this 
shouldn't be necessary, as you are specifying the location in your 
connection string.


If you're still having problems, inclde the output of

  cd /var/mysql && ls -l

in your next post.

Michael

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

Re: mysql and php

[cma7b5]

This is very old, several years, info you've been passing along.   
Mysql switches are on by default now.  Mac OSX (Server for me) is not  
10.4 Tiger, you've posted 10.2 Jaguar info, one version prior to 10.3  
Panther which was itself released over a 18 months ago.


Thanks anyway.  :)
Gil


On May 29, 2005, at 5:34 AM, [EMAIL PROTECTED] wrote:


hi,
php has to be configured with mysql support. see
http://www.whoopis.com/howtos/php-mysql-osx.html

and a general google search :
http://www.google.fr/search?q=mysql+php+mac+os&hl=fr&lr=&start=30&sa=N

Mathias


Selon Michael Stassen <[EMAIL PROTECTED]>:


You haven't given us much to go on, other than "it doesn't work,"  
which
isn't really helpful.  We can't give you "specific ideas where to  
look"

until you give us specific details of what's wrong.  Please describe
what happens.  Do you get an error message?  What is it?  While  
you're
at it, show us the relevant lines of code (hide the password, of  
course).


You say mysql, php, and apache are working separately.  I take it  
that

php scripts are running via web access (apache)?  I ask because some
previous Mac OS upgrades replaced the apache config with a newer one
with php turned off.

Meanwhile, the Apache user is irrelevant.  The php script just  
needs a

valid *mysql* user and password to authenticate.

"bounce" = restart

Michael

[EMAIL PROTECTED] wrote:


 From your first message below, are you suggesting a setting in   
Apache

needs to be ticked?
I can do it if I know where to look.  I am no expert, just one  
who  uses
tis setup, i.e., "it just works."  I have used the previous   
version for

years and it "just worked."  That's why I have no idea  what could
possibly be different.

Not to say I haven't built these before, but this should be  
working!  :(
What's led me to this problem is that I have phpmyadmin installed  
and

it can't access mysql as  it could just fine before.

I have had help from the guys at phpmyadmin who have no sent me   
here.

No one "gets it."

So, any specific ideas where to look???

Thanks,
Gil

p.s. I don't know what you mean by "bounce the MYSQL DB."

On May 28, 2005, at 9:47 PM, sol beach wrote:




I don't do MACs, but here is a shot in the dark.
In some/many cases Apache gets invoke as user "nobody";
which would be the the "OS user" that needs to be granted access to
MYSQL.

I'm willing to bet that the failure to connect into MYSQL via  
PHP from

Apache is due
to a permissions/login/authorization issue.




You also wrote:
If you enable LOGGING & bounce the MYSQL DB, some clue about what  
is  or

is not
happening to MYSQL will be written to the logfile.

Discern what the clues are reporting and fix the problem.



HTH & YMMV

On 5/28/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:



I'm on Mac OSX and Mysql is built-in, so is php.

My php has built-in support for mysql.

Both php and mysql are up and running.  I have tested both.   
All this

is running on this machine, so is Apache.

I can log into mysql at the CLI with my superuser and access the
'mysql' db -the only one I currently have.

I have a good php test connect script but cannot connect to mysql.
I have checked the mysql variables and entered the socket location
into the script, no help.

What else can possibly be wrong?  Is there something in a mysql
config file or something that is off?

This is an upgrade install of Mac OSX 10.4 Server over 10.3 Server
(I'm not a wiz) but have used it long enough.  I have changed  
nothing

and it worked fine before.

Any suggestions?  Again, I did not build this it's all built-in  
and

working fine.  I simply can't connect with php.  Separately, mysql
and php both work.

Thanks,
Gil




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










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

Re: mysql and php

[mfatene]

hi,
php has to be configured with mysql support. see
http://www.whoopis.com/howtos/php-mysql-osx.html

and a general google search :
http://www.google.fr/search?q=mysql+php+mac+os&hl=fr&lr=&start=30&sa=N

Mathias


Selon Michael Stassen <[EMAIL PROTECTED]>:

> You haven't given us much to go on, other than "it doesn't work," which
> isn't really helpful.  We can't give you "specific ideas where to look"
> until you give us specific details of what's wrong.  Please describe
> what happens.  Do you get an error message?  What is it?  While you're
> at it, show us the relevant lines of code (hide the password, of course).
>
> You say mysql, php, and apache are working separately.  I take it that
> php scripts are running via web access (apache)?  I ask because some
> previous Mac OS upgrades replaced the apache config with a newer one
> with php turned off.
>
> Meanwhile, the Apache user is irrelevant.  The php script just needs a
> valid *mysql* user and password to authenticate.
>
> "bounce" = restart
>
> Michael
>
> [EMAIL PROTECTED] wrote:
>
> >  From your first message below, are you suggesting a setting in  Apache
> > needs to be ticked?
> > I can do it if I know where to look.  I am no expert, just one who  uses
> > tis setup, i.e., "it just works."  I have used the previous  version for
> > years and it "just worked."  That's why I have no idea  what could
> > possibly be different.
> >
> > Not to say I haven't built these before, but this should be working!  :(
> > What's led me to this problem is that I have phpmyadmin installed and
> > it can't access mysql as  it could just fine before.
> >
> > I have had help from the guys at phpmyadmin who have no sent me  here.
> > No one "gets it."
> >
> > So, any specific ideas where to look???
> >
> > Thanks,
> > Gil
> >
> > p.s. I don't know what you mean by "bounce the MYSQL DB."
> >
> > On May 28, 2005, at 9:47 PM, sol beach wrote:
> >
> >
> >> I don't do MACs, but here is a shot in the dark.
> >> In some/many cases Apache gets invoke as user "nobody";
> >> which would be the the "OS user" that needs to be granted access to
> >> MYSQL.
> >>
> >> I'm willing to bet that the failure to connect into MYSQL via PHP from
> >> Apache is due
> >> to a permissions/login/authorization issue.
> >>
> >
> > You also wrote:
> > If you enable LOGGING & bounce the MYSQL DB, some clue about what is  or
> > is not
> > happening to MYSQL will be written to the logfile.
> >
> > Discern what the clues are reporting and fix the problem.
> >
> >> HTH & YMMV
> >>
> >> On 5/28/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >>
> >>> I'm on Mac OSX and Mysql is built-in, so is php.
> >>>
> >>> My php has built-in support for mysql.
> >>>
> >>> Both php and mysql are up and running.  I have tested both.  All this
> >>> is running on this machine, so is Apache.
> >>>
> >>> I can log into mysql at the CLI with my superuser and access the
> >>> 'mysql' db -the only one I currently have.
> >>>
> >>> I have a good php test connect script but cannot connect to mysql.
> >>> I have checked the mysql variables and entered the socket location
> >>> into the script, no help.
> >>>
> >>> What else can possibly be wrong?  Is there something in a mysql
> >>> config file or something that is off?
> >>>
> >>> This is an upgrade install of Mac OSX 10.4 Server over 10.3 Server
> >>> (I'm not a wiz) but have used it long enough.  I have changed nothing
> >>> and it worked fine before.
> >>>
> >>> Any suggestions?  Again, I did not build this it's all built-in and
> >>> working fine.  I simply can't connect with php.  Separately, mysql
> >>> and php both work.
> >>>
> >>> Thanks,
> >>> Gil
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>



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

Re: mysql and php

[cma7b5]

Ok, afraid I'm having to ask: how do access the mysql log file?  Or,  
how to enable logging... I gather this will cause a verbose mode from  
the CLI?

Not sure what I should be doing here.

Thanks in advance.

As for the 3 elements listed below --wouldn't those be present in the  
mysql db already if I can get in through the CLI, which I can?



On May 29, 2005, at 6:39 PM, sol beach wrote:


Gil,

THREE elements need to be match what is stored inside MYSQL DB.
1) username
2) password
3) hostname

You need to enable MYSQL logging, restart the DB, & try to login  
from PHP.
Then, take a look at what MYSQL shows in its logfile & post the  
results here!


YMMV

On 5/29/05, Peter <[EMAIL PROTECTED]> wrote:


Hi,

here is a workaround that might help you.

I guess you have a Network card. So instead of using 'localhost'  
as host
use your ip e.g '192.168.0.1;. That will force MySQl use TCP/IP  
instead

of socket.

Just have in mind that:

1. In /etc/my.cnf You should comment "skip-networking" if presenet

2. Make sure your user is allowed to make connections from YOUR_IP

3. Make sure you do not have Iptables rule that may block your  
access.



Peter

[EMAIL PROTECTED] wrote:


Remember guys, I went through this on the phpmyadmin list before
turning here.

I was told to run the command 'mysqladmin variables' to find out   
where

the the socket was; I did that successfully and it showed up  at
/var/mysql/mysql.sock.

And viola, now with mysqld running, it does show up there, at /var/
mysql/mysql.sock.
When I shutdown mysqld it is gone, from /var/mysql.

(Btw, once in a recent post of mine, Philip referred to my  
leaving  out
the leading / at 'var', that was simply a typo and it was in the   
script

post.)

So, running 'mysql variables' is giving me the socket path and I'm
still lost as to what's going why I cannot connect with a simple php
script.

Help?!  I'll look for the mysql error log.

Gil


On May 29, 2005, at 3:59 PM, Philip George wrote:



mysql.sock is created by mysqld when it starts, and destroyed when
it stops.




haha.  you're absolutely right.  that doesn't make any sense.  i
wasn't thinking about the nature of socket files when i wrote that.

now that i go back and read the post i was referring to, i  
think  the

poster was actually talking about the privileges of the  enclosing
folder, not the socket itself.  my bad.

but, regardless, i think it's a path problem anyway, as i mentioned
earlier.

pardon the misfire.  running on zero sleep.

- philip


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












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








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

Re: mysql and php

[sol beach]

Gil,

THREE elements need to be match what is stored inside MYSQL DB.
1) username
2) password
3) hostname

You need to enable MYSQL logging, restart the DB, & try to login from PHP.
Then, take a look at what MYSQL shows in its logfile & post the results here!

YMMV

On 5/29/05, Peter <[EMAIL PROTECTED]> wrote:
> Hi,
> 
> here is a workaround that might help you.
> 
> I guess you have a Network card. So instead of using 'localhost' as host
> use your ip e.g '192.168.0.1;. That will force MySQl use TCP/IP instead
> of socket.
> 
> Just have in mind that:
> 
> 1. In /etc/my.cnf You should comment "skip-networking" if presenet
> 
> 2. Make sure your user is allowed to make connections from YOUR_IP
> 
> 3. Make sure you do not have Iptables rule that may block your access.
> 
> 
> Peter
> 
> [EMAIL PROTECTED] wrote:
> > Remember guys, I went through this on the phpmyadmin list before
> > turning here.
> >
> > I was told to run the command 'mysqladmin variables' to find out  where
> > the the socket was; I did that successfully and it showed up  at
> > /var/mysql/mysql.sock.
> >
> > And viola, now with mysqld running, it does show up there, at /var/
> > mysql/mysql.sock.
> > When I shutdown mysqld it is gone, from /var/mysql.
> >
> > (Btw, once in a recent post of mine, Philip referred to my leaving  out
> > the leading / at 'var', that was simply a typo and it was in the  script
> > post.)
> >
> > So, running 'mysql variables' is giving me the socket path and I'm
> > still lost as to what's going why I cannot connect with a simple php
> > script.
> >
> > Help?!  I'll look for the mysql error log.
> >
> > Gil
> >
> >
> > On May 29, 2005, at 3:59 PM, Philip George wrote:
> >
> >>> mysql.sock is created by mysqld when it starts, and destroyed when
> >>> it stops.
> >>>
> >>
> >> haha.  you're absolutely right.  that doesn't make any sense.  i
> >> wasn't thinking about the nature of socket files when i wrote that.
> >>
> >> now that i go back and read the post i was referring to, i think  the
> >> poster was actually talking about the privileges of the  enclosing
> >> folder, not the socket itself.  my bad.
> >>
> >> but, regardless, i think it's a path problem anyway, as i mentioned
> >> earlier.
> >>
> >> pardon the misfire.  running on zero sleep.
> >>
> >> - philip
> >>
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >>
> >>
> >
> >
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
>

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

Re: mysql and php

[Peter]

Hi,

here is a workaround that might help you.

I guess you have a Network card. So instead of using 'localhost' as host 
use your ip e.g '192.168.0.1;. That will force MySQl use TCP/IP instead 
of socket.


Just have in mind that:

1. In /etc/my.cnf You should comment "skip-networking" if presenet

2. Make sure your user is allowed to make connections from YOUR_IP

3. Make sure you do not have Iptables rule that may block your access.


Peter

[EMAIL PROTECTED] wrote:
Remember guys, I went through this on the phpmyadmin list before  
turning here.


I was told to run the command 'mysqladmin variables' to find out  where 
the the socket was; I did that successfully and it showed up  at 
/var/mysql/mysql.sock.


And viola, now with mysqld running, it does show up there, at /var/ 
mysql/mysql.sock.

When I shutdown mysqld it is gone, from /var/mysql.

(Btw, once in a recent post of mine, Philip referred to my leaving  out 
the leading / at 'var', that was simply a typo and it was in the  script 
post.)


So, running 'mysql variables' is giving me the socket path and I'm  
still lost as to what's going why I cannot connect with a simple php  
script.


Help?!  I'll look for the mysql error log.

Gil


On May 29, 2005, at 3:59 PM, Philip George wrote:

mysql.sock is created by mysqld when it starts, and destroyed when  
it stops.




haha.  you're absolutely right.  that doesn't make any sense.  i  
wasn't thinking about the nature of socket files when i wrote that.


now that i go back and read the post i was referring to, i think  the 
poster was actually talking about the privileges of the  enclosing 
folder, not the socket itself.  my bad.


but, regardless, i think it's a path problem anyway, as i mentioned  
earlier.


pardon the misfire.  running on zero sleep.

- philip


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









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

Re: mysql and php

[cma7b5]

Remember guys, I went through this on the phpmyadmin list before  
turning here.


I was told to run the command 'mysqladmin variables' to find out  
where the the socket was; I did that successfully and it showed up  
at /var/mysql/mysql.sock.


And viola, now with mysqld running, it does show up there, at /var/ 
mysql/mysql.sock.

When I shutdown mysqld it is gone, from /var/mysql.

(Btw, once in a recent post of mine, Philip referred to my leaving  
out the leading / at 'var', that was simply a typo and it was in the  
script post.)


So, running 'mysql variables' is giving me the socket path and I'm  
still lost as to what's going why I cannot connect with a simple php  
script.


Help?!  I'll look for the mysql error log.

Gil


On May 29, 2005, at 3:59 PM, Philip George wrote:

mysql.sock is created by mysqld when it starts, and destroyed when  
it stops.




haha.  you're absolutely right.  that doesn't make any sense.  i  
wasn't thinking about the nature of socket files when i wrote that.


now that i go back and read the post i was referring to, i think  
the poster was actually talking about the privileges of the  
enclosing folder, not the socket itself.  my bad.


but, regardless, i think it's a path problem anyway, as i mentioned  
earlier.


pardon the misfire.  running on zero sleep.

- philip


--
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: basic sql join question...

[mfatene]

Hi,
you can use mysql variables :

set @total:=0;
select name,price,quantity, price*quantity as
subtotal,@total:[EMAIL PROTECTED]
from fruits;

++---+--+--+---+
| name   | price | quantity | subtotal | @total:[EMAIL PROTECTED] |
++---+--+--+---+
| orange | 1 |2 |2 | 2 |
| banana | 1 |4 |4 | 6 |
++---+--+--+---+

The total column will be incremented by subtotal in each row.

Mathias

Selon Philip George <[EMAIL PROTECTED]>:

> actually, i've decided this is sort of a moot point, since i can do
> this calculation in the client app.
>
> no sql required.
>
> thanks.
>
> - philip
>
>
> --
> 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]

Cannot start mysql due to possibly a bug

[Mouse Doctor X]

Nice time of the day.
I apologize I am writing on this address because didn't find START NEW THREAD 
on mailing lists page.

The problem is this: I have the freshest version of Gentoo Linux, and mysql 
within it. Just didn't modify /etc/mysql/my.cnf, commenting only one line: 
skip-innodb.

Have read about first mysql startup, and launched mysql_install_db as root. All 
went ok. Then, /etc/init.d/mysql start, started ok. 
When giving first command:  /usr/bin/mysqladmin -u root -h my_host password 
'new-password', it sends me somewhere saying: 
/usr/bin/mysqladmin: connect to server at 'my_host' failed
error: 'Lost connection to MySQL server during query'

When inspecting /var/log/mysql/mysqld.err log file, find there that 
/var/mysql.frm is not found. 
... out of comment. Also, when stopping mysql, it refuses to be stopped.

Help! Thanks in advance

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

Re: mysql and php

[cma7b5]

Yes, now-a-days php is enabled by default... well on OSX Server it is  
and Mysql support is built-in and turned on.


You do have to start Mysql on OSX Server.

Gil

On May 29, 2005, at 3:35 PM, [EMAIL PROTECTED] wrote:


re-submitted

 re-send :

  hi,
  php has to be configured with mysql support. see
  http://www.whoopis.com/howtos/php-mysql-osx.html

  and a general google search :
  http://www.google.fr/search?q=mysql+php+mac 
+os&hl=fr&lr=&start=30&sa=N


  Mathias




Selon Michael Stassen <[EMAIL PROTECTED]>:


You haven't given us much to go on, other than "it doesn't  
work," which
isn't really helpful.  We can't give you "specific ideas where  
to look"
until you give us specific details of what's wrong.  Please  
describe
what happens.  Do you get an error message?  What is it?  While  
you're
at it, show us the relevant lines of code (hide the password, of  
course).


You say mysql, php, and apache are working separately.  I take  
it that
php scripts are running via web access (apache)?  I ask because  
some
previous Mac OS upgrades replaced the apache config with a newer  
one

with php turned off.

Meanwhile, the Apache user is irrelevant.  The php script just  
needs a

valid *mysql* user and password to authenticate.

"bounce" = restart

Michael

[EMAIL PROTECTED] wrote:


 From your first message below, are you suggesting a setting  
in  Apache

needs to be ticked?
I can do it if I know where to look.  I am no expert, just one who


uses

tis setup, i.e., "it just works."  I have used the previous   
version



for


years and it "just worked."  That's why I have no idea  what could
possibly be different.

Not to say I haven't built these before, but this should be  
working!



:(

What's led me to this problem is that I have phpmyadmin  
installed and

it can't access mysql as  it could just fine before.

I have had help from the guys at phpmyadmin who have no sent  
me  here.

No one "gets it."

So, any specific ideas where to look???

Thanks,
Gil

p.s. I don't know what you mean by "bounce the MYSQL DB."

On May 28, 2005, at 9:47 PM, sol beach wrote:




I don't do MACs, but here is a shot in the dark.
In some/many cases Apache gets invoke as user "nobody";
which would be the the "OS user" that needs to be granted  
access to

MYSQL.

I'm willing to bet that the failure to connect into MYSQL via  
PHP from

Apache is due
to a permissions/login/authorization issue.




You also wrote:
If you enable LOGGING & bounce the MYSQL DB, some clue about  
what is



or


is not
happening to MYSQL will be written to the logfile.

Discern what the clues are reporting and fix the problem.



HTH & YMMV

On 5/28/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:



I'm on Mac OSX and Mysql is built-in, so is php.

My php has built-in support for mysql.

Both php and mysql are up and running.  I have tested both.   
All this

is running on this machine, so is Apache.

I can log into mysql at the CLI with my superuser and access the
'mysql' db -the only one I currently have.

I have a good php test connect script but cannot connect to  
mysql.
I have checked the mysql variables and entered the socket  
location

into the script, no help.

What else can possibly be wrong?  Is there something in a mysql
config file or something that is off?

This is an upgrade install of Mac OSX 10.4 Server over 10.3  
Server
(I'm not a wiz) but have used it long enough.  I have changed  
nothing

and it worked fine before.

Any suggestions?  Again, I did not build this it's all built- 
in and
working fine.  I simply can't connect with php.  Separately,  
mysql

and php both work.

Thanks,
Gil




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





















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

Re: basic sql join question...

[Philip George]

On May 29, 2005, at 2:34 PM, [EMAIL PROTECTED] wrote:


  you can use mysql variables :

  set @total:=0;
  select name,price,quantity, price*quantity as
  subtotal,@total:[EMAIL PROTECTED]
  from fruits;

   
++---+--+-- 
+---+
  | name   | price | quantity | subtotal |  
@total:[EMAIL PROTECTED] |
   
++---+--+-- 
+---+
  | orange | 1 |2 |2 |  
2 |
  | banana | 1 |4 |4 |  
6 |
   
++---+--+-- 
+---+


  The total column will be incremented by subtotal in each row.



actually, i need a grand total of the entire ticket:

   1 orange x 0.97 = 0.97
 + 3 pears  x 1.09 = 3.27
_
 4.24  < grand total


i think you're right that variables can be used to do this, but i can't  
figure out how to get a grand total for the entire sale.


but, i wouldn't scratch your head about it too much.  i sort of gave up  
on the idea in favor of doing the calculation in the client code at  
runtime after selecting all the pertinent data.


thanks though.  :)

- philip




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

Re: mysql and php

[Philip George]

mysql.sock is created by mysqld when it starts, and destroyed when it 
stops.


haha.  you're absolutely right.  that doesn't make any sense.  i wasn't 
thinking about the nature of socket files when i wrote that.


now that i go back and read the post i was referring to, i think the 
poster was actually talking about the privileges of the enclosing 
folder, not the socket itself.  my bad.


but, regardless, i think it's a path problem anyway, as i mentioned 
earlier.


pardon the misfire.  running on zero sleep.

- philip


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

Re: mysql and php

[mfatene]

re-submitted

 re-send :

  hi,
  php has to be configured with mysql support. see
  http://www.whoopis.com/howtos/php-mysql-osx.html

  and a general google search :
  http://www.google.fr/search?q=mysql+php+mac+os&hl=fr&lr=&start=30&sa=N

  Mathias
> >
> >
> > Selon Michael Stassen <[EMAIL PROTECTED]>:
> >
> > > You haven't given us much to go on, other than "it doesn't work," which
> > > isn't really helpful.  We can't give you "specific ideas where to look"
> > > until you give us specific details of what's wrong.  Please describe
> > > what happens.  Do you get an error message?  What is it?  While you're
> > > at it, show us the relevant lines of code (hide the password, of course).
> > >
> > > You say mysql, php, and apache are working separately.  I take it that
> > > php scripts are running via web access (apache)?  I ask because some
> > > previous Mac OS upgrades replaced the apache config with a newer one
> > > with php turned off.
> > >
> > > Meanwhile, the Apache user is irrelevant.  The php script just needs a
> > > valid *mysql* user and password to authenticate.
> > >
> > > "bounce" = restart
> > >
> > > Michael
> > >
> > > [EMAIL PROTECTED] wrote:
> > >
> > > >  From your first message below, are you suggesting a setting in  Apache
> > > > needs to be ticked?
> > > > I can do it if I know where to look.  I am no expert, just one who
> uses
> > > > tis setup, i.e., "it just works."  I have used the previous  version
> for
> > > > years and it "just worked."  That's why I have no idea  what could
> > > > possibly be different.
> > > >
> > > > Not to say I haven't built these before, but this should be working!
> :(
> > > > What's led me to this problem is that I have phpmyadmin installed and
> > > > it can't access mysql as  it could just fine before.
> > > >
> > > > I have had help from the guys at phpmyadmin who have no sent me  here.
> > > > No one "gets it."
> > > >
> > > > So, any specific ideas where to look???
> > > >
> > > > Thanks,
> > > > Gil
> > > >
> > > > p.s. I don't know what you mean by "bounce the MYSQL DB."
> > > >
> > > > On May 28, 2005, at 9:47 PM, sol beach wrote:
> > > >
> > > >
> > > >> I don't do MACs, but here is a shot in the dark.
> > > >> In some/many cases Apache gets invoke as user "nobody";
> > > >> which would be the the "OS user" that needs to be granted access to
> > > >> MYSQL.
> > > >>
> > > >> I'm willing to bet that the failure to connect into MYSQL via PHP from
> > > >> Apache is due
> > > >> to a permissions/login/authorization issue.
> > > >>
> > > >
> > > > You also wrote:
> > > > If you enable LOGGING & bounce the MYSQL DB, some clue about what is
> or
> > > > is not
> > > > happening to MYSQL will be written to the logfile.
> > > >
> > > > Discern what the clues are reporting and fix the problem.
> > > >
> > > >> HTH & YMMV
> > > >>
> > > >> On 5/28/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > > >>
> > > >>> I'm on Mac OSX and Mysql is built-in, so is php.
> > > >>>
> > > >>> My php has built-in support for mysql.
> > > >>>
> > > >>> Both php and mysql are up and running.  I have tested both.  All this
> > > >>> is running on this machine, so is Apache.
> > > >>>
> > > >>> I can log into mysql at the CLI with my superuser and access the
> > > >>> 'mysql' db -the only one I currently have.
> > > >>>
> > > >>> I have a good php test connect script but cannot connect to mysql.
> > > >>> I have checked the mysql variables and entered the socket location
> > > >>> into the script, no help.
> > > >>>
> > > >>> What else can possibly be wrong?  Is there something in a mysql
> > > >>> config file or something that is off?
> > > >>>
> > > >>> This is an upgrade install of Mac OSX 10.4 Server over 10.3 Server
> > > >>> (I'm not a wiz) but have used it long enough.  I have changed nothing
> > > >>> and it worked fine before.
> > > >>>
> > > >>> Any suggestions?  Again, I did not build this it's all built-in and
> > > >>> working fine.  I simply can't connect with php.  Separately, mysql
> > > >>> and php both work.
> > > >>>
> > > >>> Thanks,
> > > >>> Gil
> > >
> > >
> > > --
> > > 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: basic sql join question...

[mfatene]

re-submitted :

 re-send :
  Hi,
  you can use mysql variables :

  set @total:=0;
  select name,price,quantity, price*quantity as
  subtotal,@total:[EMAIL PROTECTED]
  from fruits;

  ++---+--+--+---+
  | name   | price | quantity | subtotal | @total:[EMAIL PROTECTED] |
  ++---+--+--+---+
  | orange | 1 |2 |2 | 2 |
  | banana | 1 |4 |4 | 6 |
  ++---+--+--+---+

  The total column will be incremented by subtotal in each row.

  Mathias

> > Selon Philip George <[EMAIL PROTECTED]>:
> >
> > > actually, i've decided this is sort of a moot point, since i can do
> > > this calculation in the client app.
> > >
> > > no sql required.
> > >
> > > thanks.
> > >
> > > - philip
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> > >
> >
> >
> >
>
>
>



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

Re: MySQL+InnoDB Licenses

[mfatene]

This has never arrived :
 Hi,
 the only right answers sould be here :
 http://www.mysql.com/company/legal/licensing/


 Mathias
>
> Selon Daniel Kiss <[EMAIL PROTECTED]>:
>
> > Hi All,
> >
> > I would have a question about licensing MySQL.
> >
> > I am writing an application that relies on MySQL+InnoDB (uses MySQL as a
> > database backend). I will distribute my program under GPL (get fees only
> > for official support). Do I or my client have to buy MySQL+InnoDB
> > licenses in this case?
> >
> > Thank you,
> > Daniel
> >
> >
> > --
> > 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: load data infile. Empty input field -> integer. How make NULL ?

[mfatene]

Hi,
If you let a tabulation, you will have 0 for numbers.
supposing this is the file tab.txt :
"a" 1   "c"
"a" "c"

mysql> load data infile "c:/tab.txt" into table tab fields terminated by "\t"
enclosed by "\"" lines terminated by "\r\n";
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 1

mysql> select * from tab;
+--+--+--+
| a| b| c|
+--+--+--+
| a|1 | c|
| a|0 | c|
+--+--+--+
2 rows in set (0.00 sec)


IF you want to handle NULL values, you must change empty values to "\N" :
*** new tab.txt
"a" 1   "c"
"a" \N  "c"


mysql> load data infile "c:/tab.txt" into table tab fields terminated by "\t"
enclosed by "\"" lines terminated by "\r\n";
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from tab;
+--+--+--+
| a| b| c|
+--+--+--+
| a|1 | c|
| a| NULL | c|
+--+--+--+
2 rows in set (0.00 sec)



Mathias

Selon Pete Lancashire <[EMAIL PROTECTED]>:

> I've been surfing the list / google but can't find what to do
>
> I have a tab sep file, where there are empty fields i.e.
> 
>
> the fields are associated with a mysql table integer type.
>
> how do I get the empty fields to become NULL instead of
> 0 (zero) with a warning ?
>
> Thanks,
>
> -pete
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>



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

Re: mysql and php

[Michael Stassen]

Philip George wrote:

one of the posts at the bottom of one of those pages mentions that the 
permissions on the sock file might not be right.


mine is:

srwxrwxrwx  1 mysqlwheel0 29 May 06:41 mysql.sock

you probably already know all this, but just in case... to get those 
permissions set up:




chown mysql:wheel /tmp/mysql.sock
chmod 4777 /tmp/mysql.sock



- philip


Sorry, but this is nonsense.  mysql.sock is created by mysqld when it 
starts, and destroyed when it stops.  Once you stop mysqld, there won't 
be any file to chown or chmod.

Michael

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

Re: mysql and php

[Michael Stassen]

mysqld won't start if it can't create the socket, so if mysqld is 
running, you've got a socket somewhere.  Once you find out where, you 
can fix your connection problem by setting the appropriate path in your 
php script.


I don't think you need a fresh install.

Michael

[EMAIL PROTECTED] wrote:


Ok, thanks all.

I believe I need a fresh installation.
What I do get is why running 'mysqladmin -variables' reports the  
location of mysql.sock and there seems to be no mysql.sock anywhere  on 
this machine!  Not in /tmp not /var/mysql (where it says it is).


Gil


On May 29, 2005, at 1:59 PM, Philip George wrote:

one of the posts at the bottom of one of those pages mentions that  
the permissions on the sock file might not be right.


mine is:

srwxrwxrwx  1 mysqlwheel0 29 May 06:41 mysql.sock

you probably already know all this, but just in case... to get  those 
permissions set up:




chown mysql:wheel /tmp/mysql.sock
chmod 4777 /tmp/mysql.sock



- philip





On May 29, 2005, at 12:33 PM, Philip George wrote:



try looking at these two pages:

http://dev.mysql.com/doc/mysql/en/access-denied.html
http://dev.mysql.com/doc/mysql/en/connecting-disconnecting.html

search them both for '2002' (multiple instances on one of the pages).

there a couple suggestions for things to try.

hth.

- philip



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

load data infile. Empty input field -> integer. How make NULL ?

[Pete Lancashire]

I've been surfing the list / google but can't find what to do

I have a tab sep file, where there are empty fields i.e.


the fields are associated with a mysql table integer type.

how do I get the empty fields to become NULL instead of
0 (zero) with a warning ?

Thanks,

-pete


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

Re: mysql and php

[Philip George]

On May 29, 2005, at 11:13 AM, [EMAIL PROTECTED] wrote:




I get these:
Warning: mysql_connect(): Can't connect to local MySQL server through 
socket '/var/mysql/mysql.sock' (13) in 
/Library/WebServer/testpad/test.php on line 3


Warning: mysql_select_db(): Can't connect to local MySQL server 
through socket '/tmp/mysql.sock' (2) in 
/Library/WebServer/testpad/test.php on line 3


Warning: mysql_select_db(): A link to the server could not be 
established in /Library/WebServer/testpad/test.php on line 3
Can't connect to local MySQL server through socket '/tmp/mysql.sock' 
(2)


Checking mysqladmin shows this var/mysql/mysql.sock.



also, i think it's possible that you'll need a leading slash in front 
of var/mysql/mysql.sock in mysqladmin.


/var/mysql/mysql.sock

so that it definitely matches what you've got in your mysql_connect() 
call.


speaking of which, there are several interesting notes on this subject 
on the mysql_connect() page on the php site:


http://us3.php.net/manual/en/function.mysql-connect.php

but, my guess is that the missing leading slash in the socket path in 
mysqladmin is placing the sock file in some weird place that php won't 
find.  also, make sure there's a mysql directory in /var of course.



- philip









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

Re: mysql and php

[cma7b5]

Ok, thanks all.

I believe I need a fresh installation.
What I do get is why running 'mysqladmin -variables' reports the  
location of mysql.sock and there seems to be no mysql.sock anywhere  
on this machine!  Not in /tmp not /var/mysql (where it says it is).


Gil


On May 29, 2005, at 1:59 PM, Philip George wrote:

one of the posts at the bottom of one of those pages mentions that  
the permissions on the sock file might not be right.


mine is:

srwxrwxrwx  1 mysqlwheel0 29 May 06:41 mysql.sock

you probably already know all this, but just in case... to get  
those permissions set up:




chown mysql:wheel /tmp/mysql.sock
chmod 4777 /tmp/mysql.sock



- philip





On May 29, 2005, at 12:33 PM, Philip George wrote:



try looking at these two pages:

http://dev.mysql.com/doc/mysql/en/access-denied.html
http://dev.mysql.com/doc/mysql/en/connecting-disconnecting.html

search them both for '2002' (multiple instances on one of the pages).

there a couple suggestions for things to try.

hth.

- philip


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





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





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

Re: mysql and php

[cma7b5]

I don't see anything I haven't tried there, i.e., defining the sock  
in the connection.


Thanks.


On May 29, 2005, at 1:33 PM, Philip George wrote:


try looking at these two pages:

http://dev.mysql.com/doc/mysql/en/access-denied.html
http://dev.mysql.com/doc/mysql/en/connecting-disconnecting.html

search them both for '2002' (multiple instances on one of the pages).

there a couple suggestions for things to try.

hth.

- philip


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





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

Re: mysql and php

[Philip George]

one of the posts at the bottom of one of those pages mentions that the 
permissions on the sock file might not be right.


mine is:

srwxrwxrwx  1 mysqlwheel0 29 May 06:41 mysql.sock

you probably already know all this, but just in case... to get those 
permissions set up:




chown mysql:wheel /tmp/mysql.sock
chmod 4777 /tmp/mysql.sock



- philip





On May 29, 2005, at 12:33 PM, Philip George wrote:


try looking at these two pages:

http://dev.mysql.com/doc/mysql/en/access-denied.html
http://dev.mysql.com/doc/mysql/en/connecting-disconnecting.html

search them both for '2002' (multiple instances on one of the pages).

there a couple suggestions for things to try.

hth.

- philip


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




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

Re: mysql and php

[Philip George]

try looking at these two pages:

http://dev.mysql.com/doc/mysql/en/access-denied.html
http://dev.mysql.com/doc/mysql/en/connecting-disconnecting.html

search them both for '2002' (multiple instances on one of the pages).

there a couple suggestions for things to try.

hth.

- philip


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

MySQL+InnoDB Licenses

[Daniel Kiss]

Hi All,

I would have a question about licensing MySQL.

I am writing an application that relies on MySQL+InnoDB (uses MySQL as a 
database backend). I will distribute my program under GPL (get fees only 
for official support). Do I or my client have to buy MySQL+InnoDB 
licenses in this case?


Thank you,
   Daniel


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

Re: mysql and php

[cma7b5]

I have the php app Gallery, a php web based based photo gallery  
running and that works fine.
mysqld is running and I can login through the CLI and use the db  
'mysql' with root or with my superuser, so that's OK.  All that seems  
to have transfered over from upgrading the OS without a hitch.  All I  
did was restart Mysql.  Php was already turned on in httpd this time  
around, iirc.  Test php info script work as well.


But when I try to connect to mysql with this script I can't connect:


I get these:
Warning: mysql_connect(): Can't connect to local MySQL server through  
socket '/var/mysql/mysql.sock' (13) in /Library/WebServer/testpad/ 
test.php on line 3


Warning: mysql_select_db(): Can't connect to local MySQL server  
through socket '/tmp/mysql.sock' (2) in /Library/WebServer/testpad/ 
test.php on line 3


Warning: mysql_select_db(): A link to the server could not be  
established in /Library/WebServer/testpad/test.php on line 3

Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Checking mysqladmin shows this var/mysql/mysql.sock.

All this is running on the same machine I'm sitting at.

Thanks for any and all tips, remember I'm an amateur.  :)

Gil


On May 29, 2005, at 2:27 AM, Michael Stassen wrote:

You haven't given us much to go on, other than "it doesn't work,"  
which isn't really helpful.  We can't give you "specific ideas  
where to look" until you give us specific details of what's wrong.   
Please describe what happens.  Do you get an error message?  What  
is it?  While you're at it, show us the relevant lines of code  
(hide the password, of course).


You say mysql, php, and apache are working separately.  I take it  
that php scripts are running via web access (apache)?  I ask  
because some previous Mac OS upgrades replaced the apache config  
with a newer one with php turned off.


Meanwhile, the Apache user is irrelevant.  The php script just  
needs a valid *mysql* user and password to authenticate.


"bounce" = restart

Michael

[EMAIL PROTECTED] wrote:


 From your first message below, are you suggesting a setting in   
Apache needs to be ticked?
I can do it if I know where to look.  I am no expert, just one  
who  uses tis setup, i.e., "it just works."  I have used the  
previous  version for years and it "just worked."  That's why I  
have no idea  what could possibly be different.
Not to say I haven't built these before, but this should be  
working!  :(
What's led me to this problem is that I have phpmyadmin installed  
and  it can't access mysql as  it could just fine before.
I have had help from the guys at phpmyadmin who have no sent me   
here.  No one "gets it."

So, any specific ideas where to look???
Thanks,
Gil
p.s. I don't know what you mean by "bounce the MYSQL DB."
On May 28, 2005, at 9:47 PM, sol beach wrote:


I don't do MACs, but here is a shot in the dark.
In some/many cases Apache gets invoke as user "nobody";
which would be the the "OS user" that needs to be granted access  
to  MYSQL.


I'm willing to bet that the failure to connect into MYSQL via PHP  
from

Apache is due
to a permissions/login/authorization issue.



You also wrote:
If you enable LOGGING & bounce the MYSQL DB, some clue about what  
is  or is not

happening to MYSQL will be written to the logfile.
Discern what the clues are reporting and fix the problem.


HTH & YMMV

On 5/28/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:



I'm on Mac OSX and Mysql is built-in, so is php.

My php has built-in support for mysql.

Both php and mysql are up and running.  I have tested both.  All  
this

is running on this machine, so is Apache.

I can log into mysql at the CLI with my superuser and access the
'mysql' db -the only one I currently have.

I have a good php test connect script but cannot connect to mysql.
I have checked the mysql variables and entered the socket location
into the script, no help.

What else can possibly be wrong?  Is there something in a mysql
config file or something that is off?

This is an upgrade install of Mac OSX 10.4 Server over 10.3 Server
(I'm not a wiz) but have used it long enough.  I have changed  
nothing

and it worked fine before.

Any suggestions?  Again, I did not build this it's all built-in and
working fine.  I simply can't connect with php.  Separately, mysql
and php both work.

Thanks,
Gil

Re: 'ERROR 1045 (28000): Access denied' when trying to LOAD DATA INFILE.

[Gleb Paharenko]

Hello.



You should have FILE privilege.  The FILE privilege is administrative privilege 
that

can only be granted globally (using ON *.* syntax).







Jeroen Van Goey <[EMAIL PROTECTED]> wrote:

> Hi all,

> 

> I connect via ssh from  a Linux RedHat 9 box to a remote FreeBSD

> server. There I can run MySQL and successfully add/remove tables etc.

> However, when I tried to upload a comma separated value file, I got an

> access denied error, although all privileges are granted to my

> account. What should I do/am I doing wrong?

> 

> 

> 

> [EMAIL PROTECTED] jeroen]$ uname -rs

> Linux 2.4.21-4.EL

> 

> [EMAIL PROTECTED] jeroen]$ ssh [EMAIL PROTECTED]

> Password:

> 

> [EMAIL PROTECTED] ~> uname -rs

> FreeBSD 5.3-RELEASE

> 

> [EMAIL PROTECTED] ~> mysql -V

> mysql  Ver 14.7 Distrib 4.1.10, for portbld-freebsd5.3 (i386)

> 

> [EMAIL PROTECTED] ~> mysql -ubiogeek -p

> Enter password:

> 

> mysql> use biogeek;

> Database changed

> 

> mysql> LOAD DATA INFILE 'zipcode.data' INTO TABLE zipcodes FIELDS

> TERMINATED BY ',';

> ERROR 1045 (28000): Access denied for user 'biogeek'@'localhost'

> (using password: YES)

> 

> mysql> show grants for 'biogeek'@'localhost';

> +--=

> ---+

> | Grants for [EMAIL PROTECTED]   =20

>  |

> +--=

> ---+

> | GRANT USAGE ON *.* TO 'biogeek'@'localhost' IDENTIFIED BY PASSWORD '*66..=

> .' |

> | GRANT ALL PRIVILEGES ON `biogeek`.* TO biogeek'@'localhost'=20

>   |

> +--=

> ---+

> 2 rows in set (0.00 sec)

> 

> 

> 

> PS: I do not have root access.

> 



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




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

Re: Could not initialize master info structure, check permisions on master.info

[Gleb Paharenko]

Hello.



Are you restoring the database on the running slave? What is the

sequence of steps which you're doing? Find the master.info file - 

it is usually located in the data directory and check it's permissions.

See:

  http://dev.mysql.com/doc/mysql/en/slave-logs.html



Is it possible that your dump has CHANGE MASTER statement inside

(have been created with --master-data command line option)?





>Ok here goes the query :-

>

>While restoring the database from backup, I am getting the following

>error.

>

>" ERROR 1201 at line 1: Could not initialize master info structure,

>check permisions on master.info " .

>

>I am doing  " change master to " and immediately after it " start slave;

>" is issued.

>

>I think I am getting this error from one of these two commands.

>

>Can anybody tell me why I am getting this error and how severe is this

>problem.

>

[EMAIL PROTECTED] wrote:



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




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

Re: basic sql join question...

[Philip George]

actually, i've decided this is sort of a moot point, since i can do 
this calculation in the client app.


no sql required.

thanks.

- philip


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

Re: basic sql join question...

[Jochem van Dieten]

On 5/29/05, Philip George wrote:
> On 5/29/05, Philip George wrote:
>> On 5/29/05, Jochem van Dieten wrote:
>>> http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html
>> 
>> already read that.  the join in my example is more complicated than
>> anything depicted on that page.
>>
>> please explain.
> 
> actually i should say that there are no examples of SUM() or AVG() --
> or any of the other GROUP BY functions -- that are used with a join on
> that page.

You can't always solve your problems by following an example.
Sometimes you have to recognize the patterns, apply your own knowledge
and extend the examples. The MySQL documentation, with its focus on
examples instead of formal definitions, isn't the easiest for that, so
I would strongly suggest learning SQL from a source that pays more
attention to formal definitions.

Jochem

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

Re: basic sql join question...

[Philip George]

The join is irrelevant. Your join returns a resultset and you can just
pretent that resultset is a single table:

SELECT field1, field2, field3
FROM (very complicated join) AS simpletable
GROUP BY ...
WITH ROLLUP

Just copy-pate your join into this and fix the fieldnames.



aaahhh

okay, i'm close:

mysql>  selectticket_details.quantity,
  product.name,
  product.price,
  (product.price * ticket_details.quantity) as subtotal
from  product,
  ticket_details
where ticket_details.ticket = 
'9f2d7b86-213d-1028-88b7-09e76b61a517' AND

  ticket_details.product = product.id
group by subtotal
with rollup
;

+--++---+--+
| quantity | name   | price | subtotal |
+--++---+--+
|1 | orange |  0.97 | 0.97 |
|3 | pear   |  1.09 | 3.27 |
|3 | pear   |  1.09 | NULL |
+--++---+--+


the NULL is in the wrong column.  where is my mistake?

thanks, jochem.

- philip



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

Re: basic sql join question...

[Philip George]

http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html



already read that.  the join in my example is more complicated than 
anything depicted on that page.


please explain.



actually i should say that there are no examples of SUM() or AVG() -- 
or any of the other GROUP BY functions -- that are used with a join on 
that page.


that's why i mentioned the join.  sorry.  i should have been more clear.

- philip







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

Re: basic sql join question...

[Jochem van Dieten]

On 5/29/05, Philip George wrote:
> On May 29, 2005, at 1:41 AM, Jochem van Dieten wrote:
>>
>> http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html
> 
> already read that.  the join in my example is more complicated than
> anything depicted on that page.

The join is irrelevant. Your join returns a resultset and you can just
pretent that resultset is a single table:

SELECT field1, field2, field3
FROM (very complicated join) AS simpletable
GROUP BY ...
WITH ROLLUP

Just copy-pate your join into this and fix the fieldnames.

Jochem

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

Re: performance on single column index with few distinct values

[Jochem van Dieten]

On 5/28/05, Terence wrote:
> 
> Master ID is used to distinguish multiple helpdesks. In this table there
> are 100k records, but only 10 distinct master_id's.
> 
> ticket_id   master_id
> 1   1
> 2   1
> 3   2
> 4   2
> 5   3
> ...  ...

> SELECT *
> FROM helpdesk_tickets ht, helpdesk_category_master hcm,
> helpdesk_sub_category_master hscm
> WHERE ht.master_id = '1'
> AND ht.category_id = hcm.category_id
> AND ht.sub_category_id = hscm.sub_category_id
> ORDER BY ticket_id DESC
> LIMIT 0,10

With proper foreign keys all tickets are guaranteed to match
categories and subcategories. That means you can push down the LIMIT
to just the helpdesk_tickets table:

SELECT *
FROM (
SELECT *
FROM helpdesk_tickets
WHERE master_id = 1
   ORDER BY ticket_id DESC
   LIMIT 0,10)
  NATURAL JOIN helpdesk_category_master
  NATURAL JOIN helpdesk_sub_category_master

With an index on (master_id, ticket_id) this should (nearly) constant-time.


> I have thought of options such as using temporary tables to just grab
> the last 10 tickets and then do an IN query, however I need to display
> totals, so that would require me to run the query again.

At which point does running the query again become faster then your
current method?

Jochem

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