PHP/MySQL vs ASP/MSSQL?

[Marc Pidoux]

I've been using ASP for years but I have to work on a bigger project 
involving many users and data so of course, Access is not an option 
anymore. Since it's a project requiring thousand of files and several 
applications all linked together, I can't create it once and change it 
later. Basically, which option is the best between PHP/MySQL and 
ASP/MSSQL? It's not a giant project but it might include around 20'000 
members interacting daily through forums, blogs, messages etc...


Thanks,

Marc


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

mysqlbug

[Tracy]

Dear sir,
 I am trying to uninstall my old Mysql version 4.0, however, I don't know
how to. Please give me some directions on how to do that.
 Thanks!
 Sincerely,
Tracy Huang

Re: Edit MySQL through MS Access?

[Marc Pidoux]

Yes! That's so cool, took me a little while to have it working but 
it works.


I can retrieve a MySQL table in MS Access and even upload a new table 
from MS Access to MySQL but I cannot update it from MS Access, when I 
try to upload an updated table, I get an error "Table already exists" 
which makes sense but I want to update/overwrite it. Something like 
downloading the table from MySQL, edit some of the fields and data in MS 
Access and reupload the whole thing. Is this possible too?


Thanks again!

Marc


Dan Nelson wrote:


In the last episode (Nov 03), Marc Pidoux said:
 


Eh, eh, sorry, stupid question for some of you, I'm sure...

I'm wondering if there is a way to edit a MySQL DB through MS Access
like you can for an MSSQL DB? I want to edit a lot of data, tables
etc... and doing it through phpMyAdmin just isn't very efficient.
   



Sure.  Just install the MySQL ODBC connector and link to the tables
same as you would for MS SQL.  There is a whole section in the manual
detailing this:

http://dev.mysql.com/doc/refman/5.0/en/msaccess.html

 

SQL help for qty Sold YTD...

[wodev]

I cannot figure this one out. I have a Category table with 50,000
records, an Inventory table with over 2 million records. A Sales table
with 500,000 records. And a LineItem table with 800,000 records pairing
the Inventory ID with the Sales Transaction ID and Quantity. I need to
generate a Quantity sold year to date for a certain vendor. The vendor
code can be found in the Category table which has a relationship with
Inventory. I am trying a SQL statement like this:

select sum(li.quantity) as qtysoldytd from LineItem li, Sales sa where
(li.saletranID=sa.saletranID and YEAR(sa.solddate)=2005) and li.InvID
IN (select invID from Inventory where categoryid IN (select categoryid
from Category where vendcode='AA')) 

this yields null when I know there are sales for that vendor in 2005.
Simplified schemas for the tables are as follows:
Category:
++--+--+-+-++
| Field  | Type | Null | Key | Default | Extra  
   |
++--+--+-+-++
| vendcode   | char(3)  | YES  | MUL | NULL|
   |
| categoryID | int(10) unsigned |  | PRI | NULL|
auto_increment |
++--+--+-+-++

Inventory:
+--+---+--+-+-+---+
| Field| Type  | Null | Key | Default | Extra |
+--+---+--+-+-+---+
| categoryID   | int(11)   | YES  | MUL | NULL|   |
| invID| int(10)   |  | PRI | 0   |   |
| itemnum  | int(11)   | YES  | MUL | NULL|   |
+--+---+--+-+-+---+

Sales:
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra
 |
+--+--+--+-+-++
| saletranID   | int(10) unsigned |  | PRI | NULL|
auto_increment |
| solddate | datetime | YES  | | NULL|  
 |
+--+--+--+-+-++

LineItem:
++-+--+-+-+---+
| Field  | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| invID  | int(10) | YES  | MUL | NULL|   |
| quantity   | int(10) | YES  | | NULL|   |
| saletranID | int(10) | YES  | MUL | NULL|   |
++-+--+-+-+---+

Can anybody shed some light on this and if this is even possible. I have
indexes in place and the query is still slow to pull.
Thanks a million,
Nathan


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

Re: RES: Delivery by Demand

[SGreen]

Yes, it is a client-side behavior to the extent that the MySQL server does 
not "page" through data. It gets the complete results unless you ask for a 
LIMIT, then it stops building results after it meets the criteria of your 
LIMIT. 

I really do not know much about Oracle administration and communication 
protocols so I am just guessing.I believe that even your Oracle 
clients had to ask for data in "pages" instead of the full set.  Are you 
sure your Oracle server was really "holding" those results for you and 
only delivering batches of 100 records?  That seems very much like a 
client-side behavior that was just hard for you to notice. It could have 
been designed as part of the client library As I said, I just don't 
know but I know others on the list have had some extensive Oracle 
experience. Maybe one of them can weigh in on this

With MySQL, the behavior you want to emulate is definitely something you 
control from the client-end either by using the LIMIT clause or by pulling 
down single rows in batches. You have to remember, though, that while the 
client is processing it's latest "batch" of rows that the server still 
holds onto a complete result set and has to maintain an open connection to 
your client. It's really in the best interest of performance for your 
client to spend as little time getting the data from the server. That 
means that you should only write queries that ask for the data you 
actually need and you should get the data out of the server as soon as 
possible. That way the server has more resources available to deal with 
other queries.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



"Fabricio Mota" <[EMAIL PROTECTED]> wrote on 11/03/2005 10:52:34 PM:

> Shawn,
> 
> So are you telling me that it's a configuration in Client, but not in
> Server?
> 
> FM
>   -Mensagem original-
>   De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
>   Enviada em: quarta-feira, 2 de novembro de 2005 17:37
>   Para: Fabricio Mota
>   Cc: mysql@lists.mysql.com
>   Assunto: Re: Delivery by Demand
> 
> 
> 
> 
>   "Fabricio Mota" <[EMAIL PROTECTED]> wrote on 11/02/2005 10:23:46 
AM:
> 
>   > Hi all,
>   >
>   > In the past, I worked as a Oracle user. I've noted that in oracle 
(or
> maybe
>   > in that configuration), when we request a great amount of data, such
> like:
>   >
>   > select * from million_records_table
>   >
>   > It does not delivers the entire table at the first moment. It 
delivers a
>   > little amount of data - such like a single page containing about 100
>   > records - and awaits the cursor request the Record No 101 for fetch 
the
> next
>   > set of data.
>   >
>   > In MySQL - at least, with the default configuration I have used - it
> does
>   > not happens. It delivers all the million of records existent in the
> table,
>   > inconditionally. I know that there is the clause LIMIT , to limit 
the
>   > first N records existing in the query, but is there a way to warrant 
a
>   > "delivery by demand", such Oracle does, without to have to alter the
>   > application's SQL code?
>   >
>   > Thank you
> 
>   I know that in the C-API (and others) there are two commands to 
retrieve
> records from the server. One is mysql_store_result() which will bring 
all of
> your results into your machine in a single pull.
> 
>   The second is mysql_use_result(). That command sets up a transfer 
process
> of pulling the rows from the server one at a time. If you need 100 rows 
of
> data, you issue 100 mysql_fetch_row() commands. You are responsible for
> storing the records for later re-use.
> 
>   Is that the flexibility you are looking for?
> 
>   http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html
> 
>   Shawn Green
>   Database Administrator
>   Unimin Corporation - Spruce Pine

INSERT DELAYED crash in 5.0.15

[gem]

>Description:
I have a 1M hit a day web server that logs ad hits to mysql.  Always
been solid as a rock.  Most recently on mysql 4.1.14.  We upgraded
our backup servers to 5.0.15, no problem.  We upgraded the live
server and the mysqld crashes about once a second.

Here is the SQL that crashes it:

INSERT DELAYED INTO views 
SET ad_id='4', 
host='pool-68-239-6-162.bos.east.verizon.net',
ip='1156515490'


Remove the DELAYED and no problems.

Here is a stack trace:

0x81876bc handle_segfault + 668
0xb7f6edfd _end + -1348763991
0xb7d6b307 _end + -1350876237
0x816994e _ZN15Field_varstring5storeEPKcjP15charset_info_st + 206
0x81043d7 _ZN11Item_string13save_in_fieldEP5Fieldb + 87
0x81c9f79 _Z36fill_record_n_invoke_before_triggersP3THDR4ListI4ItemES4_bP19Table
_triggers_list14trg_event_type + 137
0x81f39a6 _Z12mysql_insertP3THDP13st_table_listR4ListI4ItemERS3_IS5_ES6_S6_15enu
m_duplicatesb + 3158
0x81a4b9f _Z21mysql_execute_commandP3THD + 25135
0x81a9113 _Z11mysql_parseP3THDPcj + 483
0x81a9d3d _Z16dispatch_command19enum_server_commandP3THDPcj + 2781
0x81ab594 handle_one_connection + 2340
0xb7f67463 _end + -1348795121
0xb7dccd64 _end + -1350476272


>How-To-Repeat:
Dunno, needs a high load.

>Fix:
Dunno.  Never had mysql problems before.

>Submitter-Id:  
>Originator:[EMAIL PROTECTED]
>Organization:  Rellim
>MySQL support: none 
>Synopsis:  INSERT DELAYED crash
>Severity:  serious
>Priority:  medium
>Category:  mysql
>Class: sw-bug
>Release:   mysql-5.0.15 (Source distribution)

>C compiler:gcc (GCC) 3.4.4
>C++ compiler:  g++ (GCC) 3.4.4
>Environment:

System: Linux cms 2.6.13.2 #3 SMP Wed Sep 28 17:25:36 PDT 2005 i686 unknown
Architecture: i686

Some paths:  /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc 
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc/i686-pc-linux-gnu/3.4.4/specs
Configured with: /mnt/www3/usr/local/src/gcc-3.4.4/configure --prefix=/usr 
--exec-prefix=/usr --enable-shared --disable-libgcj --disable-libf2c
Thread model: posix
gcc version 3.4.4
Compilation info: CC='gcc'  CFLAGS=''  CXX='g++'  CXXFLAGS=''  LDFLAGS=''  
ASFLAGS=''
LIBC: 
lrwxrwxrwx1 root root   13 Sep 20  2003 /lib/libc.so.6 -> 
libc-2.3.1.so
-rwxr-xr-x1 root root  1435624 Mar  4  2003 /lib/libc-2.3.1.so
-rw-r--r--1 root root  2425490 Mar  4  2003 /usr/lib/libc.a
-rw-r--r--1 root root  178 Mar  4  2003 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql' '--with-openssl' 
'--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile'


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

RES: Delivery by Demand

[Fabricio Mota]

Shawn,

So are you telling me that it's a configuration in Client, but not in
Server?

FM
  -Mensagem original-
  De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
  Enviada em: quarta-feira, 2 de novembro de 2005 17:37
  Para: Fabricio Mota
  Cc: mysql@lists.mysql.com
  Assunto: Re: Delivery by Demand




  "Fabricio Mota" <[EMAIL PROTECTED]> wrote on 11/02/2005 10:23:46 AM:

  > Hi all,
  >
  > In the past, I worked as a Oracle user. I've noted that in oracle (or
maybe
  > in that configuration), when we request a great amount of data, such
like:
  >
  > select * from million_records_table
  >
  > It does not delivers the entire table at the first moment. It delivers a
  > little amount of data - such like a single page containing about 100
  > records - and awaits the cursor request the Record No 101 for fetch the
next
  > set of data.
  >
  > In MySQL - at least, with the default configuration I have used - it
does
  > not happens. It delivers all the million of records existent in the
table,
  > inconditionally. I know that there is the clause LIMIT , to limit the
  > first N records existing in the query, but is there a way to warrant a
  > "delivery by demand", such Oracle does, without to have to alter the
  > application's SQL code?
  >
  > Thank you

  I know that in the C-API (and others) there are two commands to retrieve
records from the server. One is mysql_store_result() which will bring all of
your results into your machine in a single pull.

  The second is mysql_use_result(). That command sets up a transfer process
of pulling the rows from the server one at a time. If you need 100 rows of
data, you issue 100 mysql_fetch_row() commands. You are responsible for
storing the records for later re-use.

  Is that the flexibility you are looking for?

  http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html

  Shawn Green
  Database Administrator
  Unimin Corporation - Spruce Pine

Re: How to add a host so 'root' can connect to the server from it

[Xiaobo Chen]

Sorry, Sean, I think I forgot in the emails to you to mention that the
MySQL server is running on a Solaris machine, I am trying to connect the
database from a XP machine which is the one I am using.

I tried the same program in other Solaris machine, it works fine. I also
noticed that the other Solaris machines are in the same subnet as the one
in which Mysql server is running, all of them have 129.173.23.*, but the
XP machine is different: 129.173.105.*, could it be possible the firewall
on the Solaris system (I already turned off the firewall on the XP when I
tried to connect to the mysql server.) block the connection from the XP to
the port 3306 though I can use ssh or putty, winscp from the XP machine to
remotely log in those Solaris machines.

Again, thank you very much for your time and patience. I do appreciate it.

Xiaobo

> Its been a while since i've dealt much with MySQL permissions, but do you
> need
> to explicitly state 'localhost' as the machine in some circumstances.
>
> The other thing i thought of is a guess, as i don't program Java, but have
> had
> an analogous problem using Perl. It it possible that Java is dealing with
> the
> passwords using the old password scheme and the server has the passwords
> in
> the new scheme (or the other way around)?
>
> Just a couple guesses that are probably incorrect.
>
> On Thursday 03 November 2005 16:02, [EMAIL PROTECTED] wrote:
>> Answers intermixed. See below
>>
>> "Xiaobo Chen" <[EMAIL PROTECTED]> wrote on 11/03/2005 04:28:08 PM:
>> > Thank so much for the detailed explaination. I do appreciate it.
>> >
>> > It's more clear now. But I still have a question:
>> >
>> > I do see 'root' after: select user,host from mysql.user;
>> >
>> > Then I did this as you said:
>> >
>> > GRANT SELECT ON mydatabase.* TO [EMAIL PROTECTED] IDENTIFIED BY
>>
>> 'mypassword';
>>
>> > then I issue:
>> >
>> > select * from mysql.user where user='root';
>> >
>> > I found the select previlege is still 'N'.
>> >
>> > Besided this, how do I know 'mydatabase' from those tables in 'mysql'
>> > database that 'mydatabase' is allowed to be connected by 'root' from
>> the
>> > IP. I am confusing here because the 'user' table only give the
>>
>> association
>>
>> > betweem 'host' and user 'root' in the Mysql server. But where is the
>> > database association?
>> >
>> > wait a minute, yes, I see. When I issue:
>> >
>> > select * from mysql.db where user='root';
>> >
>> > I did see the association and the SELECT_priv is 'Y' there.
>>
>> That's correct, the `user` table controls GLOBAL permissions. The `db`
>> table controls database-specific permissions (there can be multiple
>> databases on any server).
>>
>> > But, in the java program running in the local XP machine still can NOT
>> > connect to the database existing in the other Solaris machine!!
>> >
>> > I tried in the local XP machine:
>> >
>> > telnet theserver 3306
>> >
>> > I failed.
>> >
>> > I guess this is why I can not connect to the server. Could be it
>>
>> possible
>>
>> > that the Solaris machine deny any request from the PC to the port
>> 3306?
>>
>> I
>>
>> > can ssh to the server, or using winscp.
>>
>> There are several possibilities here:
>> XP is denying outbound connections to your Solaris machine (quite
>> likely)
>> Solaris is denying connections from your XP machine (not likely)
>> Firewalls, routers, or proxy servers between the XP and Solaris machines
>> are blocking the connection attempt.
>>
>> If you were able to connect to the MySQL server, your MySQL error would
>> say that you "could not authenticate" not "could not connect"
>>
>> > I am really confused here. Is it a mysql issue or the system
>>
>> configuration
>>
>> > issue on the server?
>>
>> I think it's a system configuration issue on the XP machine. I believe
>> the
>> XP firewall is getting in your way.
>>
>> > Btw, I can run the same java program from other machine in Solaris
>>
>> system
>>
>> > to connect the database as the user 'root'. Does this imply that it's
>> > administration issue?
>>
>> Network administration, not MySQL administration (yet).
>>
>> > Thanks for your kind help and patience.
>> >
>> > Xiaobo
>>
>> My pleasure!
>> Shawn Green
>> Database Administrator
>> Unimin Corporation - Spruce Pine
>>
>> 
>
> --
> Sean Peters
> Senior Programmer, WIREData Inc.
> [EMAIL PROTECTED]
>
> "The software required Windows 2000 or better, so i Downloaded Linux"
>
>


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

Quick split() and data parse

[Scott Haneda]

I have a field `fedex`, in it is text date, in tab sep \n delimeted form,
for example:

90FedEx Home Delivery (Residential) 14.06
20FedEx Express Saver   22.63

There can be x rows of data in the field, only every 3 "fields" to the data.
So the above could be 5 lines in total, or one, whatever.

Another field, `shipping_method` is what I am updating:
Update orders set shipping_method = 20 where id = xxx

So I know the shipping_method in advance, what I need to do, is get to the
price data, and also update:

Update orders set shipping_method = 20, price = x.xx where id = xxx

Is there some way I can do some string parsing on the text field and get to
the 3rd item where the passed in shippign_method equals?

-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



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

Re: How to add a host so 'root' can connect to the server from it

[Xiaobo Chen]

Thanks for your speedy reply.

I already turn off the firewall in the XP machine. I have no idea about
the system sonfiguration on the Solaris machine.

I did this experiment as well: I installed Mysql 4.1.5 on XP machine, then
use the same java program to connect to the 'test' data base from the
installation. It works because I didn't network to anywhere I guess.

Maybe, I should bring this to the system administrtor. It looking like a
networking issue.

Again, thank you very much for your patience and help.

Xiaobo

> Answers intermixed. See below
>
> "Xiaobo Chen" <[EMAIL PROTECTED]> wrote on 11/03/2005 04:28:08 PM:
>
>> Thank so much for the detailed explaination. I do appreciate it.
>>
>> It's more clear now. But I still have a question:
>>
>> I do see 'root' after: select user,host from mysql.user;
>>
>> Then I did this as you said:
>>
>> GRANT SELECT ON mydatabase.* TO [EMAIL PROTECTED] IDENTIFIED BY
> 'mypassword';
>>
>> then I issue:
>>
>> select * from mysql.user where user='root';
>>
>> I found the select previlege is still 'N'.
>>
>> Besided this, how do I know 'mydatabase' from those tables in 'mysql'
>> database that 'mydatabase' is allowed to be connected by 'root' from the
>> IP. I am confusing here because the 'user' table only give the
> association
>> betweem 'host' and user 'root' in the Mysql server. But where is the
>> database association?
>>
>> wait a minute, yes, I see. When I issue:
>>
>> select * from mysql.db where user='root';
>>
>> I did see the association and the SELECT_priv is 'Y' there.
>>
>
> That's correct, the `user` table controls GLOBAL permissions. The `db`
> table controls database-specific permissions (there can be multiple
> databases on any server).
>
>
>> But, in the java program running in the local XP machine still can NOT
>> connect to the database existing in the other Solaris machine!!
>>
>> I tried in the local XP machine:
>>
>> telnet theserver 3306
>>
>> I failed.
>>
>> I guess this is why I can not connect to the server. Could be it
> possible
>> that the Solaris machine deny any request from the PC to the port 3306?
> I
>> can ssh to the server, or using winscp.
>
> There are several possibilities here:
> XP is denying outbound connections to your Solaris machine (quite likely)
> Solaris is denying connections from your XP machine (not likely)
> Firewalls, routers, or proxy servers between the XP and Solaris machines
> are blocking the connection attempt.
>
> If you were able to connect to the MySQL server, your MySQL error would
> say that you "could not authenticate" not "could not connect"
>
>
>>
>> I am really confused here. Is it a mysql issue or the system
> configuration
>> issue on the server?
>
> I think it's a system configuration issue on the XP machine. I believe the
> XP firewall is getting in your way.
>
>>
>> Btw, I can run the same java program from other machine in Solaris
> system
>> to connect the database as the user 'root'. Does this imply that it's
>> administration issue?
>
> Network administration, not MySQL administration (yet).
>
>>
>> Thanks for your kind help and patience.
>>
>> Xiaobo
>>
>>
>
>
> My pleasure!
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> 
>


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

Re: How to add a host so 'root' can connect to the server from it

[sean c peters]

Its been a while since i've dealt much with MySQL permissions, but do you need 
to explicitly state 'localhost' as the machine in some circumstances. 

The other thing i thought of is a guess, as i don't program Java, but have had 
an analogous problem using Perl. It it possible that Java is dealing with the 
passwords using the old password scheme and the server has the passwords in 
the new scheme (or the other way around)?

Just a couple guesses that are probably incorrect.

On Thursday 03 November 2005 16:02, [EMAIL PROTECTED] wrote:
> Answers intermixed. See below
>
> "Xiaobo Chen" <[EMAIL PROTECTED]> wrote on 11/03/2005 04:28:08 PM:
> > Thank so much for the detailed explaination. I do appreciate it.
> >
> > It's more clear now. But I still have a question:
> >
> > I do see 'root' after: select user,host from mysql.user;
> >
> > Then I did this as you said:
> >
> > GRANT SELECT ON mydatabase.* TO [EMAIL PROTECTED] IDENTIFIED BY
>
> 'mypassword';
>
> > then I issue:
> >
> > select * from mysql.user where user='root';
> >
> > I found the select previlege is still 'N'.
> >
> > Besided this, how do I know 'mydatabase' from those tables in 'mysql'
> > database that 'mydatabase' is allowed to be connected by 'root' from the
> > IP. I am confusing here because the 'user' table only give the
>
> association
>
> > betweem 'host' and user 'root' in the Mysql server. But where is the
> > database association?
> >
> > wait a minute, yes, I see. When I issue:
> >
> > select * from mysql.db where user='root';
> >
> > I did see the association and the SELECT_priv is 'Y' there.
>
> That's correct, the `user` table controls GLOBAL permissions. The `db`
> table controls database-specific permissions (there can be multiple
> databases on any server).
>
> > But, in the java program running in the local XP machine still can NOT
> > connect to the database existing in the other Solaris machine!!
> >
> > I tried in the local XP machine:
> >
> > telnet theserver 3306
> >
> > I failed.
> >
> > I guess this is why I can not connect to the server. Could be it
>
> possible
>
> > that the Solaris machine deny any request from the PC to the port 3306?
>
> I
>
> > can ssh to the server, or using winscp.
>
> There are several possibilities here:
> XP is denying outbound connections to your Solaris machine (quite likely)
> Solaris is denying connections from your XP machine (not likely)
> Firewalls, routers, or proxy servers between the XP and Solaris machines
> are blocking the connection attempt.
>
> If you were able to connect to the MySQL server, your MySQL error would
> say that you "could not authenticate" not "could not connect"
>
> > I am really confused here. Is it a mysql issue or the system
>
> configuration
>
> > issue on the server?
>
> I think it's a system configuration issue on the XP machine. I believe the
> XP firewall is getting in your way.
>
> > Btw, I can run the same java program from other machine in Solaris
>
> system
>
> > to connect the database as the user 'root'. Does this imply that it's
> > administration issue?
>
> Network administration, not MySQL administration (yet).
>
> > Thanks for your kind help and patience.
> >
> > Xiaobo
>
> My pleasure!
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> 

-- 
Sean Peters
Senior Programmer, WIREData Inc.
[EMAIL PROTECTED]

"The software required Windows 2000 or better, so i Downloaded Linux"


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

Re: report from two tables

[[EMAIL PROTECTED]]

Don't know how but it works now both ways. Don't ask me how!
:)

Thanks!

-afan



ISC Edwin Cruz wrote:


Try it:
   SELECT o.Trans_No, o.SoldBy, 'NA', sm.LName, sm.FName, sm.Salesperson_No
   FROM v_orders as o, v_salesmen as sm
   WHERE o.Order_Date = '". $report_starts."'
   AND sm.Salesperson_No = o.SoldBy
UNION
   SELECT o.Trans_No, o.SoldBy, 'A', sm.LName, sm.FName, sm.Salesperson_No
   FROM orders as o, v_salesmen as sm
   WHERE o.Order_Date = '". $report_starts."'
   AND o.SoldBy = sm.User_ID
ORDER BY Trans_No DESC
LIMIT 100

It works for me.

Regards!

-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Enviado el: Jueves, 03 de Noviembre de 2005 03:55 p.m.

Para: MySQL List
Asunto: report from two tables


Hi,

I have two table for orders  and I have to create a Report with list of 
orders from both tables for specific day.
I made Reports for each table and they work just fine, but don't know 
how to "put them together":


Query for Order Table 1:
SELECT o.Trans_No, o.SoldBy, sm.LName, sm.FName, sm.User_ID FROM v_orders as
o, v_salesmen as sm WHERE o.Order_Date = '". $report_starts."' AND o.SoldBy
= sm.User_ID ORDER BY sm.LName ASC where Trans_No is transaction no., SoldBy
salesperson's no in table 
"orders", LName, FName and User_ID info from (third) table for 
salespersons (User_ID is salesperson's id - primary key).


Query for Order Table 2:
SELECT o.Trans_No, o.SoldBy, sm.LName, sm.FName, sm.Salesperson_No FROM
orders as o, v_salesmen as sm WHERE Order_Date = '". $report_starts."' AND
o.SoldBy = sm.Salesperson_No ORDER BY sm.LName ASC where Salesperson_No is
info from salespersons table - in this case FK.

I tried with this:

(
   SELECT o.Trans_No, o.SoldBy, 'NA', sm.LName, sm.FName, sm.Salesperson_No
   FROM v_orders as o, v_salesmen as sm
   WHERE o.Order_Date = '". $report_starts."'
   AND sm.Salesperson_No = o.SoldBy
)
UNION
(
   SELECT o.Trans_No, o.SoldBy, 'A', sm.LName, sm.FName, sm.Salesperson_No
   FROM orders as o, v_salesmen as sm
   WHERE o.Order_Date = '". $report_starts."'
   AND o.SoldBy = sm.User_ID
)
ORDER BY Trans_No DESC
LIMIT 100

I'm NOT getting any error but either any result (entry)?

What am I doing wrong?

Thanks for any help.

-afan






 



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

RE: report from two tables

[ISC Edwin Cruz]

Try it:
SELECT o.Trans_No, o.SoldBy, 'NA', sm.LName, sm.FName, sm.Salesperson_No
FROM v_orders as o, v_salesmen as sm
WHERE o.Order_Date = '". $report_starts."'
AND sm.Salesperson_No = o.SoldBy
UNION
SELECT o.Trans_No, o.SoldBy, 'A', sm.LName, sm.FName, sm.Salesperson_No
FROM orders as o, v_salesmen as sm
WHERE o.Order_Date = '". $report_starts."'
AND o.SoldBy = sm.User_ID
ORDER BY Trans_No DESC
LIMIT 100

It works for me.

Regards!

-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Enviado el: Jueves, 03 de Noviembre de 2005 03:55 p.m.
Para: MySQL List
Asunto: report from two tables


Hi,

I have two table for orders  and I have to create a Report with list of 
orders from both tables for specific day.
I made Reports for each table and they work just fine, but don't know 
how to "put them together":

Query for Order Table 1:
SELECT o.Trans_No, o.SoldBy, sm.LName, sm.FName, sm.User_ID FROM v_orders as
o, v_salesmen as sm WHERE o.Order_Date = '". $report_starts."' AND o.SoldBy
= sm.User_ID ORDER BY sm.LName ASC where Trans_No is transaction no., SoldBy
salesperson's no in table 
"orders", LName, FName and User_ID info from (third) table for 
salespersons (User_ID is salesperson's id - primary key).

Query for Order Table 2:
SELECT o.Trans_No, o.SoldBy, sm.LName, sm.FName, sm.Salesperson_No FROM
orders as o, v_salesmen as sm WHERE Order_Date = '". $report_starts."' AND
o.SoldBy = sm.Salesperson_No ORDER BY sm.LName ASC where Salesperson_No is
info from salespersons table - in this case FK.

I tried with this:

(
SELECT o.Trans_No, o.SoldBy, 'NA', sm.LName, sm.FName, sm.Salesperson_No
FROM v_orders as o, v_salesmen as sm
WHERE o.Order_Date = '". $report_starts."'
AND sm.Salesperson_No = o.SoldBy
)
UNION
(
SELECT o.Trans_No, o.SoldBy, 'A', sm.LName, sm.FName, sm.Salesperson_No
FROM orders as o, v_salesmen as sm
WHERE o.Order_Date = '". $report_starts."'
AND o.SoldBy = sm.User_ID
)
ORDER BY Trans_No DESC
LIMIT 100

I'm NOT getting any error but either any result (entry)?

What am I doing wrong?

Thanks for any help.

-afan






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



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

Re: How to add a host so 'root' can connect to the server from it

[SGreen]

Answers intermixed. See below

"Xiaobo Chen" <[EMAIL PROTECTED]> wrote on 11/03/2005 04:28:08 PM:

> Thank so much for the detailed explaination. I do appreciate it.
> 
> It's more clear now. But I still have a question:
> 
> I do see 'root' after: select user,host from mysql.user;
> 
> Then I did this as you said:
> 
> GRANT SELECT ON mydatabase.* TO [EMAIL PROTECTED] IDENTIFIED BY 
'mypassword';
> 
> then I issue:
> 
> select * from mysql.user where user='root';
> 
> I found the select previlege is still 'N'.
> 
> Besided this, how do I know 'mydatabase' from those tables in 'mysql'
> database that 'mydatabase' is allowed to be connected by 'root' from the
> IP. I am confusing here because the 'user' table only give the 
association
> betweem 'host' and user 'root' in the Mysql server. But where is the
> database association?
> 
> wait a minute, yes, I see. When I issue:
> 
> select * from mysql.db where user='root';
> 
> I did see the association and the SELECT_priv is 'Y' there.
> 

That's correct, the `user` table controls GLOBAL permissions. The `db` 
table controls database-specific permissions (there can be multiple 
databases on any server).


> But, in the java program running in the local XP machine still can NOT
> connect to the database existing in the other Solaris machine!!
> 
> I tried in the local XP machine:
> 
> telnet theserver 3306
> 
> I failed.
> 
> I guess this is why I can not connect to the server. Could be it 
possible
> that the Solaris machine deny any request from the PC to the port 3306? 
I
> can ssh to the server, or using winscp.

There are several possibilities here:
XP is denying outbound connections to your Solaris machine (quite likely)
Solaris is denying connections from your XP machine (not likely)
Firewalls, routers, or proxy servers between the XP and Solaris machines 
are blocking the connection attempt.

If you were able to connect to the MySQL server, your MySQL error would 
say that you "could not authenticate" not "could not connect"


> 
> I am really confused here. Is it a mysql issue or the system 
configuration
> issue on the server?

I think it's a system configuration issue on the XP machine. I believe the 
XP firewall is getting in your way.

> 
> Btw, I can run the same java program from other machine in Solaris 
system
> to connect the database as the user 'root'. Does this imply that it's
> administration issue?

Network administration, not MySQL administration (yet).

> 
> Thanks for your kind help and patience.
> 
> Xiaobo
> 
> 


My pleasure!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

report from two tables

[[EMAIL PROTECTED]]

Hi,

I have two table for orders  and I have to create a Report with list of 
orders from both tables for specific day.
I made Reports for each table and they work just fine, but don't know 
how to "put them together":


Query for Order Table 1:
SELECT o.Trans_No, o.SoldBy, sm.LName, sm.FName, sm.User_ID
FROM v_orders as o, v_salesmen as sm
WHERE o.Order_Date = '". $report_starts."'
AND o.SoldBy = sm.User_ID
ORDER BY sm.LName ASC
where Trans_No is transaction no., SoldBy salesperson's no in table 
"orders", LName, FName and User_ID info from (third) table for 
salespersons (User_ID is salesperson's id - primary key).


Query for Order Table 2:
SELECT o.Trans_No, o.SoldBy, sm.LName, sm.FName, sm.Salesperson_No
FROM orders as o, v_salesmen as sm
WHERE Order_Date = '". $report_starts."'
AND o.SoldBy = sm.Salesperson_No
ORDER BY sm.LName ASC
where Salesperson_No is info from salespersons table - in this case FK.

I tried with this:

(
   SELECT o.Trans_No, o.SoldBy, 'NA', sm.LName, sm.FName, sm.Salesperson_No
   FROM v_orders as o, v_salesmen as sm
   WHERE o.Order_Date = '". $report_starts."'
   AND sm.Salesperson_No = o.SoldBy
)
UNION
(
   SELECT o.Trans_No, o.SoldBy, 'A', sm.LName, sm.FName, sm.Salesperson_No
   FROM orders as o, v_salesmen as sm
   WHERE o.Order_Date = '". $report_starts."'
   AND o.SoldBy = sm.User_ID
)
ORDER BY Trans_No DESC
LIMIT 100

I'm NOT getting any error but either any result (entry)?

What am I doing wrong?

Thanks for any help.

-afan






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

Re: How to add a host so 'root' can connect to the server from it

[Xiaobo Chen]

Thank so much for the detailed explaination. I do appreciate it.

It's more clear now. But I still have a question:

I do see 'root' after: select user,host from mysql.user;

Then I did this as you said:

GRANT SELECT ON mydatabase.* TO [EMAIL PROTECTED] IDENTIFIED BY 'mypassword';

then I issue:

select * from mysql.user where user='root';

I found the select previlege is still 'N'.

Besided this, how do I know 'mydatabase' from those tables in 'mysql'
database that 'mydatabase' is allowed to be connected by 'root' from the
IP. I am confusing here because the 'user' table only give the association
betweem 'host' and user 'root' in the Mysql server. But where is the
database association?

wait a minute, yes, I see. When I issue:

select * from mysql.db where user='root';

I did see the association and the SELECT_priv is 'Y' there.

But, in the java program running in the local XP machine still can NOT
connect to the database existing in the other Solaris machine!!

I tried in the local XP machine:

telnet theserver 3306

I failed.

I guess this is why I can not connect to the server. Could be it possible
that the Solaris machine deny any request from the PC to the port 3306? I
can ssh to the server, or using winscp.

I am really confused here. Is it a mysql issue or the system configuration
issue on the server?

Btw, I can run the same java program from other machine in Solaris system
to connect the database as the user 'root'. Does this imply that it's
administration issue?

Thanks for your kind help and patience.

Xiaobo



> "Xiaobo Chen" <[EMAIL PROTECTED]> wrote on 11/03/2005 02:54:16 PM:
>
>> I have a question related to connection control.
>>
>> If I want to connect to one database A in the server from another
>> computer. I am using 'root' and it password. But how shall I add the
> host
>> in which table of which database (mysql?) so that the user 'root' can
>> connect to the database?
>>
>> A little explaination is that, I used 'root' in a java program which use
>> JDBC and tried to connect a database A in the server. And the program is
>> run in a XP machine. In that XP machine, there is no user called 'root'.
>>
>> I read through this link:
>>
>> http://dev.mysql.com/doc/refman/5.0/en/connection-access.html
>>
>> I still didn't know how to do it. Anyone can help me?
>>
>> Xiaobo
>>
>
> You are correct in saying that the XP machine doesn't have a user called
> 'root'. The fact that your database server's operating system (I assume
> you are hosting your database on a LINUX or UNIX server) has a user called
> 'root' is only confusing. The only 'root' you need to worry about is the
> one defined as a user WITHIN MySQL.
>
> run this query
>
> SELECT * from mysql.user;
>
> Can you see a user called 'root' in the results? There may be other users
> listed there, too. MySQL security is based mostly on the values of the
> columns `Host` and `User`. Look at part of a sample `user` table:
>
> localhost>select Host,User from mysql.user;
> +---++
> | Host  | User   |
> +---++
> | % | odbctest   |
> | 192.168.% | sgreen |
> | % | slave  |
> | localhost | ChartReader|
> | localhost | cpwapp2|
> | localhost | dataimporter   |
> | localhost | datareader |
> | localhost | odbctest   |
> | localhost | root   |
> +---++
> 8 rows in set (0.03 sec)
>
> For this server, the user 'root' can only log in from the local machine
> (the computer running the MySQL daemon). Why? That is the only value
> allowed by its `Host` entry. The user 'sgreen' cannot login from the local
> machine. However, that user can try to login from any machine on the
> 192.168.xx.xx subnet. The user 'odbctest' can login to the MySQL server
> from either the local machine or from any outside address.
>
> Here are some valid login combinations:
> --
> Username - location of that user
> --
> sgreen - 192.168.1.17
> odbctest - 192.168.1.17
> root - localhost (from the machine hosting the MySQL server)
>
> Here are some invalid (disallowed) login combinations:
> --
> Username - location of that user
> --
> sgreen - 10.10.1.45
> root - 192.168.1.1
> cpwapp2 - 10.10.1.45
>
> You manage the contents of the `user` table (and a few others, too) with
> the GRANT command and the REVOKE command. If a user attempts to login from
> an address they do not have permission to use (you limit the usable
> addresses with the GRANT statement you used you define the user account
> within MySQL) they will not be able to connect to the MySQL server.
>
> For example,
> *I wanted to create a new user account for the login 'idiotuser'
> *AND only allow this login to select data from any table in the `safety`
> database (and no others)
> *AND their log

Re: How to add a host so 'root' can connect to the server from it

[SGreen]

"Xiaobo Chen" <[EMAIL PROTECTED]> wrote on 11/03/2005 02:54:16 PM:

> I have a question related to connection control.
> 
> If I want to connect to one database A in the server from another
> computer. I am using 'root' and it password. But how shall I add the 
host
> in which table of which database (mysql?) so that the user 'root' can
> connect to the database?
> 
> A little explaination is that, I used 'root' in a java program which use
> JDBC and tried to connect a database A in the server. And the program is
> run in a XP machine. In that XP machine, there is no user called 'root'.
> 
> I read through this link:
> 
> http://dev.mysql.com/doc/refman/5.0/en/connection-access.html
> 
> I still didn't know how to do it. Anyone can help me?
> 
> Xiaobo
> 

You are correct in saying that the XP machine doesn't have a user called 
'root'. The fact that your database server's operating system (I assume 
you are hosting your database on a LINUX or UNIX server) has a user called 
'root' is only confusing. The only 'root' you need to worry about is the 
one defined as a user WITHIN MySQL.

run this query

SELECT * from mysql.user;

Can you see a user called 'root' in the results? There may be other users 
listed there, too. MySQL security is based mostly on the values of the 
columns `Host` and `User`. Look at part of a sample `user` table:

localhost>select Host,User from mysql.user;
+---++
| Host  | User   |
+---++
| % | odbctest   |
| 192.168.% | sgreen |
| % | slave  |
| localhost | ChartReader|
| localhost | cpwapp2|
| localhost | dataimporter   |
| localhost | datareader |
| localhost | odbctest   |
| localhost | root   |
+---++
8 rows in set (0.03 sec)

For this server, the user 'root' can only log in from the local machine 
(the computer running the MySQL daemon). Why? That is the only value 
allowed by its `Host` entry. The user 'sgreen' cannot login from the local 
machine. However, that user can try to login from any machine on the 
192.168.xx.xx subnet. The user 'odbctest' can login to the MySQL server 
from either the local machine or from any outside address. 

Here are some valid login combinations:
--
Username - location of that user 
--
sgreen - 192.168.1.17
odbctest - 192.168.1.17
root - localhost (from the machine hosting the MySQL server)

Here are some invalid (disallowed) login combinations:
--
Username - location of that user 
--
sgreen - 10.10.1.45
root - 192.168.1.1
cpwapp2 - 10.10.1.45

You manage the contents of the `user` table (and a few others, too) with 
the GRANT command and the REVOKE command. If a user attempts to login from 
an address they do not have permission to use (you limit the usable 
addresses with the GRANT statement you used you define the user account 
within MySQL) they will not be able to connect to the MySQL server.

For example, 
*I wanted to create a new user account for the login 'idiotuser' 
*AND only allow this login to select data from any table in the `safety` 
database (and no others) 
*AND their login password is to be 'dunce' 
*AND I only want 'idiotuser' to be able to connect from one machine (IP 
address 192.168.20.2) 

I would use the follwing GRANT statement:

GRANT SELECT ON safety.* TO [EMAIL PROTECTED] IDENTIFIED BY 'dunce';

None of what I just talked about has anything to do with the Operating 
System user 'root'. The permissions for that user belong to the OS, not 
MySQL. You do not use OS permissions to authenticate with a MySQL server. 
You have to use MySQL user accounts to authenticate with a MySQL server.

additional reading:
http://dev.mysql.com/doc/refman/5.0/en/privileges.html
http://dev.mysql.com/doc/refman/5.0/en/grant.html

Let me know if I helped or just made it more confusing...

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

How to add a host so 'root' can connect to the server from it

[Xiaobo Chen]

I have a question related to connection control.

If I want to connect to one database A in the server from another
computer. I am using 'root' and it password. But how shall I add the host
in which table of which database (mysql?) so that the user 'root' can
connect to the database?

A little explaination is that, I used 'root' in a java program which use
JDBC and tried to connect a database A in the server. And the program is
run in a XP machine. In that XP machine, there is no user called 'root'.

I read through this link:

http://dev.mysql.com/doc/refman/5.0/en/connection-access.html

I still didn't know how to do it. Anyone can help me?

Xiaobo


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

Re: MySQL show databases - all db shown

[Michael Stassen]

Paul DuBois wrote:

At 12:59 -0500 11/2/05, Michael Stassen wrote:


BÁRTHÁZI András wrote:




GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 
'user'@'localhost' IDENTIFIED BY PASSWORD 'xx'


GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, 
INDEX, ALTER, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `metazin`.* 
TO 'user'@'localhost'






[EMAIL PROTECTED] has global permission to lock tables in all databases.  
That's enough to allow him/her to see all databases.  Because you can 
only lock a table from which you can SELECT, I expect [EMAIL PROTECTED] 
should only have LOCK TABLES permission for metazin.*.  So, I think 
you need to


  REVOKE LOCK TABLES ON *.* FROM [EMAIL PROTECTED];
  GRANT LOCK TABLES ON metazin.* TO [EMAIL PROTECTED];

Neither FLUSH PRIVILEGES, FLUSH QUERY_CACHE, nor restarting mysqld are 
necessary with GRANT AND REVOKE.  See the manual for more 
.




The user has two global privileges: CREATE TEMPORARY TABLES and LOCK 
TABLES.

Either is enough to allow all databases to be seen, because any global
privilege applies to any database.


Well, some privileges are strictly global, but should not affect SHOW DATABASES. 
 The FILE privilege is one example.  In any case, you are right about "CREATE 
TEMPORARY TABLES ON *.*" causing all dbs to show up.  I should have suggested


  REVOKE CREATE TEMPORARY TABLES, LOCK TABLES ON *.* FROM [EMAIL PROTECTED];
  GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON metazin.* TO [EMAIL PROTECTED];

Probably because temporary tables live in TMPDIR and do not show up in the 
output of SHOW TABLES, I hadn't really thought of temp tables being part of a 
db.  Now I know better, though I still find it a little strange.


On the other hand, I would argue that LOCK TABLES shouldn't affect SHOW 
DATABASES.  LOCK TABLES ON *.* is only partially global, as you may only lock a 
table for which you have the SELECT privilege.  That is, the LOCK TABLES 
privilege changes what I can do with tables I can already access, but it has no 
effect on *which* tables I can access.  Currently, however, LOCK TABLES ON *.* 
does affect the output of SHOW DATABASES.  I think that's slightly strange 
behavior, but perhaps the moral is "Don't grant a privilege globally when 
granting it locally would do."


Michael


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

Access control for mysql database

[Xiaobo Chen]

Hi, all

I am trying to find out how to check which user is connecting to the
server from which host.

I have a database, data_A; I wrote a simple java code to connect this
database as user 'root', then it just waits there for input from the
keyboard. Before I type anything, it should keep connecting to the
database.

But when I type 'select current_user()' in mysql prompt, it always says:
[EMAIL PROTECTED]

Can any one help me figure out this?




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

Re: optimize table on live database

[Paul DuBois]

At 9:41 -0500 11/3/05, [EMAIL PROTECTED] wrote:

Paul,

Thanks for your reply! I have tried using optimize while the table was
"live". However, in the two instances that I have attempted, it generated
an error, and the table could no longer be read. I had to delete the


That should not happen, and should be reported as a bug at
http://bugs.mysql.com.


database and rebuild the table. Can table size be an issue here? I don't
remember the particular tablesize  at that time, but it could easily get
to be around 1 gig or more.

Any suggestions?

Your friendly neighborhood SA,
phiLLip



Paul DuBois <[EMAIL PROTECTED]>
11/03/2005 12:11 AM

To
[EMAIL PROTECTED], mysql@lists.mysql.com
cc

Subject
Re: optimize table on live database






At 14:08 -0500 11/2/05, [EMAIL PROTECTED] wrote:

Hello,

I've been looking for information related to best practice on how to
OPTIMIZE TABLE  with out taking the database offline. I
understand that it is not good to run an optimize while the database is
being used. So what is a good way of handling this?

In my particular application, there are constant inserts going on, and

I'm

doing a delete on older records, and then optimize the table. It's
important to not lose any of those inserts, however I realize that may

not

be possible, so it is acceptable to loose some.


The only methods I've thought of are:

1. revoke the user's insert permission, then optimize the table, and then
grant the permission's back.
2. Rename the table, then optimize the table, and rename it back to the
original
3. stop mysql, use myisamchk to optimize the table, then start mysql

Is there a better way?


You don't have to do any of that.  OPTIMIZE TABLE will block other
clients from modifying the table while it runs.

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

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



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

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

EXCEL Querry

[parkergopal (sent by Nabble.com)]

Hello ALL,

I am basically from the call center industry,
we use customer databses in bulk which consists of telephone numbers, names, 
addresses, city, state and zip codes.,

we have to run them thru against national do not call registry in the usa,

for example i have a column of 4000 phone numbers with me, after scrubbing 
against the dnc registry, i get back results for 3000 numbers which means 1000 
numbers have to be deleted from the list of 4000, how do i do this, can i get 
this done in excel.,

pls help.

regards
Parker
--
Sent from the MySQL - General forum at Nabble.com:
http://www.nabble.com/EXCEL-Querry-t486406.html#a1322601

RE: Always Storing Upper Case Text in a Table

[Malik Kuldeep-LKM041]

Thanks,
I am running MySQL 5.0. So the solution is applicable.
Kuldeep Malik
 

-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED] 
Sent: Thursday, November 03, 2005 10:00 AM
To: Malik Kuldeep-LKM041; mysql@lists.mysql.com
Subject: Re: Always Storing Upper Case Text in a Table

At 10:48 -0500 11/3/05, Malik Kuldeep-LKM041 wrote:
>Hi,
>I have a requirement to always store the UPPER CASE text for the VAR 
>and VARCHAR type columns in a table. Is there a way I can specify it 
>during the CREATE TABLE statement?

Not in the CREATE TABLE statement.

If you have MySQL 5.0, you could create an INSERT BEFORE trigger that
uppercases the relevant column values before storing them.

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

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

Re: Always Storing Upper Case Text in a Table

[SGreen]

"Malik Kuldeep-LKM041" <[EMAIL PROTECTED]> wrote on 11/03/2005 
10:48:21 AM:

> Hi,
> I have a requirement to always store the UPPER CASE text for the VAR and
> VARCHAR type columns in a table. Is there a way I can specify it during
> the CREATE TABLE statement?
> 
> Kuldeep Malik
> 

No.

That is something your data providing applications will need to do for 
you. However, with v5.0, you should be able to write a trigger that will 
upper-case all data inbound to particular tables but you neglected say 
which version you are running so I offer that only as an option. Triggers 
are not defined as part of the CREATE TABLE statement.

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Always Storing Upper Case Text in a Table

[Paul DuBois]

At 10:48 -0500 11/3/05, Malik Kuldeep-LKM041 wrote:

Hi,
I have a requirement to always store the UPPER CASE text for the VAR and
VARCHAR type columns in a table. Is there a way I can specify it during
the CREATE TABLE statement?


Not in the CREATE TABLE statement.

If you have MySQL 5.0, you could create an INSERT BEFORE trigger that
uppercases the relevant column values before storing them.

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

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

Always Storing Upper Case Text in a Table

[Malik Kuldeep-LKM041]

Hi,
I have a requirement to always store the UPPER CASE text for the VAR and
VARCHAR type columns in a table. Is there a way I can specify it during
the CREATE TABLE statement?
 
Kuldeep Malik
 

Re: Edit MySQL through MS Access?

[Stephen Moretti]

Dan Nelson wrote:


In the last episode (Nov 03), Marc Pidoux said:
 


Eh, eh, sorry, stupid question for some of you, I'm sure...

I'm wondering if there is a way to edit a MySQL DB through MS Access
like you can for an MSSQL DB? I want to edit a lot of data, tables
etc... and doing it through phpMyAdmin just isn't very efficient.
   



Sure.  Just install the MySQL ODBC connector and link to the tables
same as you would for MS SQL.  There is a whole section in the manual
detailing this:

http://dev.mysql.com/doc/refman/5.0/en/msaccess.html
 

But you would probably just as well to install one of the many MySQL 
GUIs out there.  I actually quite like MySQLs suite of tools now.  Query 
Browser is still a bit buggy, but its getting better each iteration.


http://dev.mysql.com/ - just check out the links in the box on the right 
hand side.


Stephen


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

Re: Edit MySQL through MS Access?

[Dan Nelson]

In the last episode (Nov 03), Marc Pidoux said:
> Eh, eh, sorry, stupid question for some of you, I'm sure...
> 
> I'm wondering if there is a way to edit a MySQL DB through MS Access
> like you can for an MSSQL DB? I want to edit a lot of data, tables
> etc... and doing it through phpMyAdmin just isn't very efficient.

Sure.  Just install the MySQL ODBC connector and link to the tables
same as you would for MS SQL.  There is a whole section in the manual
detailing this:

http://dev.mysql.com/doc/refman/5.0/en/msaccess.html

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Re: optimize table on live database

[phillip]

Paul,

Thanks for your reply! I have tried using optimize while the table was 
"live". However, in the two instances that I have attempted, it generated 
an error, and the table could no longer be read. I had to delete the 
database and rebuild the table. Can table size be an issue here? I don't 
remember the particular tablesize  at that time, but it could easily get 
to be around 1 gig or more.

Any suggestions?

Your friendly neighborhood SA,
phiLLip



Paul DuBois <[EMAIL PROTECTED]> 
11/03/2005 12:11 AM

To
[EMAIL PROTECTED], mysql@lists.mysql.com
cc

Subject
Re: optimize table on live database






At 14:08 -0500 11/2/05, [EMAIL PROTECTED] wrote:
>Hello,
>
>I've been looking for information related to best practice on how to
>OPTIMIZE TABLE  with out taking the database offline. I
>understand that it is not good to run an optimize while the database is
>being used. So what is a good way of handling this?
>
>In my particular application, there are constant inserts going on, and 
I'm
>doing a delete on older records, and then optimize the table. It's
>important to not lose any of those inserts, however I realize that may 
not
>be possible, so it is acceptable to loose some.
>
>
>The only methods I've thought of are:
>
>1. revoke the user's insert permission, then optimize the table, and then
>grant the permission's back.
>2. Rename the table, then optimize the table, and rename it back to the
>original
>3. stop mysql, use myisamchk to optimize the table, then start mysql
>
>Is there a better way?

You don't have to do any of that.  OPTIMIZE TABLE will block other
clients from modifying the table while it runs.

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

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

Re: LOAD DATA IF?

[Scott Haneda]

on 11/3/05 12:11 AM, John thegimper at [EMAIL PROTECTED] wrote:

> I dont want it to INSERT that row.
> 
> Sample.txt
> name;category;price
> samsung;dvd;60
> siemens;mobile;40
> none;none;0
> 
> Say i dont want to insert rows where category is mobile or price <= 0.
> So in this case only the 'samsung;dvd;60' row would be inserted.

Probably pretty simple to just delete them after youa re done.
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



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

Re: MySQL show databases - all db shown

[Gleb Paharenko]

Hello.



If all your users has 'SHOW DATABASES' privilege, it is normal behavior.

However, probably, it is not you want. You may want to

update the grant tables manually and revoke this privilege from your

users.



BÁRTHÁZI András wrote:

> Hi,

> 

> When I migrated from 3.23 to 4.0 version (if I'm remember well), I think

> I missed to upgrade something, so all my MySQL users are able to see the

> list of the databases on my server. Currently, the MySQL version is

> 5.0.15, i ran the mysql database upgrade script, but it still is a

> problem. What should I do, to hide the databases, and my users just see

> the databases they have rights for to select/etc. from? Or is it the

> normal behaviour?

> 

> Bye,

>   Andras

> 

> 



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

Queue implementation on MySQL

[Cabbar Duzayak]

Hi,

Can you please recommend an open-source, light-weight and more
importantly RELIABLE point-to-point queue implementation, built on
mysql? Something similar to Oracle's Advanced Queue? I don't need
publish/subscribe or a priority-based point-to-point queue, just a
simple one which provides a reliable way of enqueuing and dequeuing
with LIFO...

Thanks...

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

Edit MySQL through MS Access?

[Marc Pidoux]

Eh, eh, sorry, stupid question for some of you, I'm sure...

I'm wondering if there is a way to edit a MySQL DB through MS Access 
like you can for an MSSQL DB? I want to edit a lot of data, tables 
etc... and doing it through phpMyAdmin just isn't very efficient.


Thanks,

Marc


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