from:"\[EMAIL PROTECTED\]"

Konsep Pertama Pembenihan Lintah

2008-12-11 Thread [EMAIL PROTECTED]

Assalamualaikum dan Salam Sejahtera buat rakan-rakan pertanian,

Terlebih dahulu saya ingin mohon maaf atas kehadiran  email ini.Hanya sekadar 
untuk berkongsi info kursus sahaja.

Kursus Pembenihan dan Penternakan Lintah Komersial

Konsep Pembenihan Lintah Pertama Di Malaysia

Tarikh : 20  21 Dec 2008 (2hari)
Tempat : MITC Ancasa Hotel - Ayer Keroh Melaka.
Masa : 9.00am - 9.45am Pendaftaran

Yuran : RM350.00 ( Penginapan Hotel dan Makan disediakan)
Deposit : RM100.00

Tarikh notis penyertaan bersama deposit adalah - 12hb Dec 2008.
Penyertaan peserta adalah terhad.

Bayaran deposit CIMB(Bank Bumiputra Commerce) 
Account : CIMB - AMAGRO VERTEAU RESOURCES
No Acc : 0403-0013484-05-1

Selepas membuat pembayaran sila hubungi kami secepat mungkin :-
019-3536828 / 016-9729744

Serta : SMS Nama, No Ic dan No Resit serta - Masa dan Tempat Pembayaran kepada 
019-3536828 / 016-9729744.

Simpan resit untuk semakan ketika pendaftaran kursus dan hantar salinan resit 
pembayaran berserta surat penerimaan kursus dan hantar ke alamat : -

Amagro Resources
Bt 4 Solok Malim ( Belakang TM(Telekom) )
Kampung Bertam Malim,
75250, Melaka.

ataupun email salinan resit serta surat tawaran ke :-
[EMAIL PROTECTED] dan [EMAIL PROTECTED]

Maklumat lanjut sila layari http://amagro.blogspot.com


Terima Kasih diatas kesudian anda membaca email ini.
Sudi-sudikan lah forward email ini kepada kenalan yang berminat.
Harap bertemu anda di Melaka. 


-
Pakej Laman Web + Affliate Program www.website.ws/nasdea79

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

RE: Show Master Status

2008-09-17 Thread [EMAIL PROTECTED]

Hi,

What's in the bin-logs?   (mysqlbinlog )

cheers,
Doug


Original Message:
-
From: David Giragosian [EMAIL PROTECTED]
Date: Wed, 17 Sep 2008 12:38:01 -0500
To: mysql@lists.mysql.com
Subject: Show Master Status


Hurricane Ike has caused our replication set-up to misbehave.

I've stopped the (one and only) application that inserts data into the
master, but show master status's 'position'  field continues to increment.


Have I missed something or is this unexpected behavior?

version 5.0.22 on CentOS 5.

Thanks,

David



mail2web.com  Enhanced email for the mobile individual based on Microsoft®
Exchange - http://link.mail2web.com/Personal/EnhancedEmail



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

Re: counting by countrycode

2008-07-19 Thread [EMAIL PROTECTED]

If your countrycodes are going to be embedded in the callednumber, you
really need a delimiter like a dash "-".  Then you can use some string
functions to do the count.  Ideally, as others suggested, the country
code should be in its on field/column.

Below is the general idea for the SELECT, of course the problem in this
data set is the country code "001":

mysql> SELECT LEFT(callednumber, 4),COUNT(LEFT(callednumber, 4)) FROM
call_log GROUP BY LEFT(callednumber, 4);
+---+--+
| LEFT(callednumber, 4) | COUNT(LEFT(callednumber, 4)) |
+---+--+
| 001X  |1 |
| 0060  |1 |
| 0061  |6 |
| 0063  |2 |
+---+--+

The bottom line is that you really need to use cleaner more discrete data.

CheersCassj




Jerry Schwartz wrote:
> How are you storing phone numbers? I don't think there are leading zeroes in
> country codes, and country codes are not all the same length. Are you
> padding them with leading zeroes to five characters?
>
> If you have padded them so that the first five characters are always the
> country code, then you can simply do
>
> SELECT
>
>> -----Original Message-
>> From: Ron [mailto:[EMAIL PROTECTED]
>> Sent: Friday, July 18, 2008 2:27 AM
>> To: mysql@lists.mysql.com
>> Subject: counting by countrycode
>>
>> 
>> -
>> | customercode | customer name | customerphonenumber | callednumber
>>   | calldate   | callstart | callend  | callduration | callcost |
>> 
>> -
>> | 1116 | Company Name  | Customer NUmber | 0061X
>>   | 2008-07-16 | 15:25:19  | 15:26:00 |1 | 0.229375 |
>> | 1116 | Company Name  | Customer NUmber | 0063X
>>   | 2008-07-16 | 15:25:21  | 15:26:44 |2 |  0.07759 |
>> | 1116 | Company Name  | Customer NUmber | 001XX
>>   | 2008-07-16 | 15:25:24  | 15:25:34 |1 | 0.229375 |
>> | 1116 | Company Name  | Customer NUmber | 0060X
>>   | 2008-07-16 | 15:25:25  | 15:29:33 |5 |  1.14688 |
>> | 1116 | Company Name  | Customer NUmber | 0061X
>>   | 2008-07-16 | 15:25:29  | 15:25:58 |1 | 0.038795 |
>> | 1116 | Company Name  | Customer NUmber | 0061X
>>   | 2008-07-16 | 15:25:29  | 15:25:48 |1 | 0.038795 |
>> | 1116 | Company Name  | Customer NUmber | 0061X
>>   | 2008-07-16 | 15:25:32  | 15:25:54 |1 | 0.038795 |
>> | 1116 | Company Name  | Customer NUmber | 0063X
>>   | 2008-07-16 | 15:25:33  | 15:25:45 |1 | 0.038795 |
>> | 1116 | Company Name  | Customer NUmber | 0061X
>>   | 2008-07-16 | 15:25:35  | 15:26:29 |1 | 0.229375 |
>> | 1116 | Company Name  | Customer NUmber | 0061X
>>   | 2008-07-16 | 15:25:38  | 15:25:53 |1 | 0.038795 |
>> 
>> --
>>
>> Hi,
>>
>> How can i count by countrycode with that table? I kind of need a summary
>>  to count how many calls went to certain country. Thank You. I hope my
>> question is clear.
>>
> [JS] How are you storing phone numbers? I don't think there are leading
> zeroes in country codes, and country codes are not all the same length. Are
> you padding them with leading zeroes to five characters?
>
> If you have padded them so that the first five characters are always the
> country code, then you can simply do
>
> SELECT SUBSTRING(callednumber,1,5) AS countrycode FROM tablename GROUP BY
> countrycode;
>
>> Regards
>> Ron
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>> infoshop.com
>
>
>
>
>


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

Replication error with stored proc and triggers

2007-12-03 Thread [EMAIL PROTECTED]

>Description:

I am using MySQL server as backend database supporting a telecomm platform
providing voice over ip services.

tables involved: acc, cdr, account, location2

Partial processing flow:

1. When a phone call is started. A record will be inserted into table acc
(method=INVITE). This will trigger an insert of a record into table cdr.
2. When the phone call is ended. Another record will be inserted into table
acc (method=BYE). This will trigger an update of the cdr record (endtime and
duration).
3. When table cdr is updated, a trigger will start. If cdr_duration is not
null, the account table will be updated with adjusted balance.

The process flow has been working fine. The database is replicated to
another MySQL server instance running on another machine.

Recently, I am encountering an error that caused the replication to stop.

IN show slave status, I am getting an error message:

 Last_Error: Query caused different errors on master and
slave. Error on master:  'Can't update table '%-.64s' in stored
function/trigger because it is already used by statement which  invoked this
stored function/trigger.' (1442), Error on slave: 'no error' (0). Default
database:  'opnser'. Query: 'insert into acc
(method,from_tag,to_tag,callid,sip_code,sip_reason,time,ani,did,dnis  )
values
('BYE','3405618788-778336','516B0D10-1448','[EMAIL PROTECTED]
hnl.aloha.net','200','OK','2007-12-02
21:14:23','8088646143','818084473408','14378288809164968574')'

Skip counter: 0

Seconds_Behind_Master: NULL

The transaction causing error was completed: cdr record updated, account
balance adjusted.

The only problem is that replication process is frozen.

I have checked the application and it does not look like there is any
deadlock situation.


>How-To-Repeat:

It would be difficult to reproduce the error. The creation of acc record is
the result of a voip software 'openser'. The error condition is random and
unpredictable.


>Fix:

When this happen I need to re-establish the replication by making a copy
from master and do change master on the slave machine.


>Submitter-Id:  
>Originator:Richard Siy
>Organization:
  Quattro Broadband, Inc.
>MySQL support: none
>Synopsis:   Error causing
replication to stop.
>Severity:  <[ non-critical | serious | critical ] (one line)> serious
>Priority:  <[ low | medium | high ] (one line)> high
>Category:  mysql
>Class: <[ sw-bug | doc-bug | change-request | support ] (one line)>
sw-bug
>Release:   mysql-5.0.27-standard (MySQL Community Edition - Standard
(GPL))

>C compiler:gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2)
>C++ compiler:  gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2)
>Environment:

System: Linux qbdbm1.qbnet 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:30:39 EST 2005
i686 athlon i386 GNU/Linux
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake

Compilation info: CC='gcc'
 CFLAGS='-O2 -g -pipe -m32 -march=i386 -mtune=pentium4'  CXX='gcc'
CXXFLAGS='-O2 -g -pipe -m32 -march=i386 -mtune=pentium4'  LDFLAGS=''
ASFLAGS=''
LIBC:
lrwxrwxrwx  1 root root 13 Aug 17  2006 /lib/libc.so.6 -> libc-2.3.4.so
-rwxr-xr-x  1 root root 1439335 Dec 20  2004 /lib/libc-2.3.4.so
Configure command: ./configure '--disable-shared'
'--with-server-suffix=-standard' '--without- embedded-server'
'--with-innodb' '--with-archive-storage-engine' '--without-bench'
'--with-zlib- dir=bundled' '--with-big-tables' '--enable-assembler'
'--enable-local-infile' '--with-mysqld- user=mysql'
'--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-pic'
'--prefix=/' '--with- extra-charsets=complex' '--with-yassl'
'--exec-prefix=/usr' '--libexecdir=/usr/sbin' '-- libdir=/usr/lib'
'--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql'
'-- infodir=/usr/share/info' '--includedir=/usr/include'
'--mandir=/usr/share/man' '--enable-thread-safe- client'
'--with-comment=MySQL Community Edition - Standard (GPL)' '--with-readline'
'CC=gcc' 'CFLAGS= -O2 -g -pipe -m32 -march=i386 -mtune=pentium4'
'CXXFLAGS=-O2 -g -pipe -m32 -march=i386 - mtune=pentium4' 'CXX=gcc'
'LDFLAGS='

Looking forward to hear from you soon.

Thanks,
Richard Siy



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

transaction problem

2007-09-05 Thread [EMAIL PROTECTED]


Hi list,

I am going to change the type of table to InnoDB

Thank!!

Pablo

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

transaction problem

2007-09-05 Thread [EMAIL PROTECTED]


Hi list!
I've a problem with transaction
I initiate a transaction with "start transaction". Soon i execute 
following query:
* update t1 set t=12; and by I complete I execute rollback but when I do 
it I obtain the following thing:
"non-transactional Some changed tables couldn't be rolled back" and 
update I execute myself updating the data

Why do it?
I'm use MySQL 4.1.20 and MyISAM tables

Thanks,

Pablo

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

remove me from this list please

2007-08-29 Thread [EMAIL PROTECTED]

Please remove me from this list or tell me how I can do this procedure...
Thanks...


mail2web.com  Enhanced email for the mobile individual based on Microsoft®
Exchange - http://link.mail2web.com/Personal/EnhancedEmail



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

How to limit word result by query

2007-08-21 Thread [EMAIL PROTECTED]

Hi all,
Is it possible to do query which limiting the result into some words
only? i.e the complete sentence is "I am able to login with the account"
and I just want to view "I am able to login..." Many thanks for any
reply.

Regards,


Willy
-- 
www.sangprabv.web.id
www.binbit.co.id


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

MySQLへログインできない

2007-08-14 Thread [EMAIL PROTECTED]

お世話になります吉田と申します。
早速ですがMySQL(5.0)へログインできず困っております。

Access denied for user 'root'@'localhost' (using password: YES)

再セットアップもかなら色々とやり直してみましたが駄目でした。

起動オプションで

mysqld_safe --user=mysql --skip-grant-tables

等も試してみましたがどうしても接続できませんでした。

FreeBSDのVPSサーバ（NTT.COM）で、root権限付きのサーバなのですが
vinstallコマンドでMySQLをインストールできるはずなのですが
インストールはできてもrootでのアクセスができません。
mysqlというユーザではアクセスできますが、rootのパスワードを
リセットしようとすると権限エラーが出ます。

何かこのような場合のヒントをいただけませんでしょうか。
お手数をおかけいたしますがどうぞよろしくお願いいたします。

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

MySQLへログインできない

2007-08-14 Thread [EMAIL PROTECTED]

お世話になります吉田と申します。
早速ですがMySQL(5.0)へログインできず困っております。

Access denied for user 'root'@'localhost' (using password: YES)

再セットアップもかなら色々とやり直してみましたが駄目でした。

起動オプションで

mysqld_safe --user=mysql --skip-grant-tables

等も試してみましたがどうしても接続できませんでした。

FreeBSDのVPSサーバ（NTT.COM）で、root権限付きのサーバなのですが
vinstallコマンドでMySQLをインストールできるはずなのですが
インストールはできてもrootでのアクセスができません。
mysqlというユーザではアクセスできますが、rootのパスワードを
リセットしようとすると権限エラーが出ます。

何かこのような場合のヒントをいただけませんでしょうか。
お手数をおかけいたしますがどうぞよろしくお願いいたします。

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

Find record between 10 minutes

2007-08-12 Thread [EMAIL PROTECTED]

Hi,
I have a table with records dated 01-01-2007 00:00:00 to 01-02-2007
23:59:59. What I want to do is grab 1 random record in every 10 minutes
between the date. Please help me.

Regards,


Willy
-- 
www.sangprabv.web.id
www.binbit.co.id


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

Re: speeding up a join COUNT

2007-01-25 Thread í <[EMAIL PROTECTED]>


Hi,

try

CREATE INDEX geo_idx ON users(entity_id, user_type);

Filip


Alex Arul napsal(a):

and also an index on users.entity_id (will help the join) should solve your
problem.

Thanks
Alex

On 1/24/07, Brent Baisley <[EMAIL PROTECTED]> wrote:


You should create indexes on the fields you search on most. In this case,
you are searching on the user_type field, so create an
index on that field. Otherwise you need to scan the entire table to find
out which users are of the type you are searching for.


- Original Message -
From: "James Tu" <[EMAIL PROTECTED]>
To: "MySQL List" 
Sent: Tuesday, January 23, 2007 12:04 PM
Subject: speeding up a join COUNT


> I'm performance testing my 'users' table.  It currently has roughly  1M
user records.  The 'geo_entities' table has ~ 250 records.
>
> Here's my query.
>
> SELECT users.entity_id,
geo_entities.entity_name,  geo_entities.short_code, COUNT( 
users.entity_id)

> FROM users, geo_entities
> WHERE users.user_type = 'user'
> AND users.entity_id = geo_entities.id
> GROUP BY entity_id
> LIMIT 0 , 30
>
> It took 51 seconds to execute.
>
> Both tables only have an index on their unique record id.
> Is there a way to speed up this up?
>
> -James
>
>
> --
> 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]







--
Filip Krejci <[EMAIL PROTECTED]>

Why use Windows, since there is a door?

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

Re: MAX() and GROUP BY question

2006-11-11 Thread [EMAIL PROTECTED]

No, this won't work
I actually have a table of support ticket records

record_id
ticket_id
date
name
status
department_id
and other columns

I must select an ordered list of ticket_id where each row is the most recent 
record of gived ticket: max(record_id) for given ticket_id

I currently solved this using two queries: 
First will select a list of max record_id's: SELECT MAX(record_id) GROUP BY 
ticket_id
Then using API I create a comma separated list of record_id's.
Then I select the latest records of each ticket: SELECT * WHERE record_id IN 
()

I just wonder if there is more optimised solution

Thanks a lot

- Original Message 
From: Tim Lucia <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Sent: Saturday, November 11, 2006 3:56:49 PM
Subject: RE: MAX() and GROUP BY question


How about

select ID, X, Y, Z from USERS order by ID desc limit 1


Tim

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, November 10, 2006 5:47 PM
To: mysql@lists.mysql.com
Subject: MAX() and GROUP BY question

Hello everybody
Can you explain me please how to get the entire row where ID is maximum per
given status_id
Mysql 4.0.xx 

for example:

id status_idnamedate
6 3   name0  date0
5 1   name1  date1
7 4   name3  date3
10   3   name2  date2

If I execute 

SELECT MAX(id), status, name, date FROM table_name WHERE status=3 GROUP BY
staus_id
or
SELECT MAX(id), status, name, date GROUP BY staus_id

only id and status_id will be returned correctly, while name and date can
well be from another row
for example: 10 3 name0 date0


Thanks a lot for any suggestions
Yannis



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

MAX() and GROUP BY question

2006-11-10 Thread [EMAIL PROTECTED]

Hello everybody
Can you explain me please how to get the entire row where ID is maximum per 
given status_id
Mysql 4.0.xx 

for example:

id status_idnamedate
6 3   name0  date0
5 1   name1  date1
7 4   name3  date3
10   3   name2  date2

If I execute 

SELECT MAX(id), status, name, date FROM table_name WHERE status=3 GROUP BY 
staus_id
or
SELECT MAX(id), status, name, date GROUP BY staus_id

only id and status_id will be returned correctly, while name and date can well 
be from another row
for example: 10 3 name0 date0


Thanks a lot for any suggestions
Yannis



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

utf8 importing problem

2006-10-27 Thread [EMAIL PROTECTED]

I use MySQL database with utf8 character set and utf8_czech_ci 
collation. 
It works well on Linux server but when I try to 
export the data and import into the same database but running on XP machine the 
utf8 is 
gone.Instead of a proper coding there are some strange characters.

I used 
mysqldump --default-character-set=utf8 mimi >/home/Result.sql 
to export data to /home/Result.sql file on Linux machine. 
Then I downloaded the file to my XP and here I used 
mysql --default-character-set=utf8 mimi < Result.sql 
to import data. 
Is it correct? 

Any help would be appreciated

L.

--- End of forwarded message ---

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

List-Unsubscribe

2006-08-09 Thread [EMAIL PROTECTED]


List-Unsubscribe



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

myisamchk location for the intermediate files TMD

2006-06-06 Thread [EMAIL PROTECTED]

Hello,

Is it possible to tell myisamchk where the intermediate files (.TMD) must be 
created.
Because my database are big and these not enough space in his directory.

I try with --tmpdir but this doesn't work.

I use myisamchk ver 2.7 and mysql 4.1.11

thank's

Nuno





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

Re: timestamp & not null

2006-04-10 Thread [EMAIL PROTECTED]


  I need to create a "commands" table. A program will periodically check on 
this table whether 
there's a pending command for it to execute or not. Whatever the reason, this 
program might read a command but not acknowledge it's 
execution.
  Other program will check out whether the command timeout has expired or not 
and so act accordingly.
  I guess I can use some sort of integer in order to represent it as a unix 
timestamp, but I would prefer to use a "timestamp".

  Any suggestions?
  Kind regards

Mensaje original
De: [EMAIL PROTECTED]
Recibido: 10/04/2006 11:51
Para: <[EMAIL PROTECTED]>, 
Asunto: Re: timestamp & not null

Hi,

> I created a table and, into it, a timestamp field:
> ... EXPIRES TIMESTAMP NOT NULL, ...
>
> When I issue the command "describe" it shows the field "expires" allows
nulls and defaults to CURRENT_TIMESTAMP. Also, each time I update
> a field other than "expires" in this table, "expires" gets updated to the
current timestamp.
>
> Does anybody know how can I make a timestamp field be "not null"?
> Lots of thanks to you all.

If you want to store date/time values, do not use the TIMESTAMP
datatype.

What is it that you're trying to do?

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com






Prueba el Nuevo Correo Terra; Seguro, Rápido, Fiable.


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

timestamp & not null

2006-04-10 Thread [EMAIL PROTECTED]

Hi!

I created a table and, into it, a timestamp field:
... EXPIRES TIMESTAMP NOT NULL, ...

When I issue the command "describe" it shows the field "expires" allows nulls 
and defaults to CURRENT_TIMESTAMP. Also, each time I update 
a field other than "expires" in this table, "expires" gets updated to the 
current timestamp.

Does anybody know how can I make a timestamp field be "not null"?
Lots of thanks to you all.


Prueba el Nuevo Correo Terra; Seguro, Rápido, Fiable.


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

stored proc/func

2006-04-07 Thread [EMAIL PROTECTED]

Hi!

Can I return a record set from a stored procedure/function in mysql?

Kind regards


Prueba el Nuevo Correo Terra; Seguro, Rápido, Fiable.


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

Re: FreeBSD v6.0 on AMD64-MySQL

2006-02-08 Thread [EMAIL PROTECTED]


je killen wrote:
Is there a version of MySQL for FreeBSD v6.0 AMD64 compatible? I 
haven't seen one explicitly on the MySQL site
and I don't know if it is a good idea to try and compile and install 
from source code for this machine.
Install mysql from FreeBSD ports collection. I'm running MySQL 5.x on 
Opteron machine with FreeBSD 6.0-STABLE.


Ganbold

Thanks
JK





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

database/table size

2006-01-31 Thread [EMAIL PROTECTED]

Hi, there!

I would like to know whether mysql has built-in capabilities/config options in 
order to limid a database size or a table size.
I want a table to grow up to a limit and, when reached, for a new row to be 
inserted the oldest one be deleted. Has mysql got this 
functionality built-in? If not, what other approaches could I use, triggers ...?

Kinf regards.


Prueba el Nuevo Correo Terra; Seguro, Rápido, Fiable.


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

How to start mysql with --old-password

2006-01-27 Thread [EMAIL PROTECTED]

Dear Friends,
I need to start mysql with --old-passwords but i did not know how to do so.
Actually i had mysql installed with someone else. I did not know how have
he installed that.
He have placed an entry in /etc/rc.d/rc.local as
/usr/local/mysql/bin/mysqld_safe & to start mysql when server starts.
Also i have no file as my.cnf .
I have 3 ques:
1)Is it correct ot add that entry into this file
2)Also how to start mysqld with --old-passwords etc.
3)How to add a configuration file.

Pl. do help me with the asnwers.
I shall be very grateful.
--
Regards
Abhishek jain


mail2web - Check your email from the web at
http://mail2web.com/ .



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

Timezone settings

2006-01-18 Thread [EMAIL PROTECTED]

Dear Friends,
I need to do the timezone settings so that now() gives the system
time.Actually first i have installed mysql on a different timezone han
changed the system time zone but perhaps mysql shows the previous time zone
or the default time zone only.
Pl. tell me how to change that .
I shall be very grateful.
--
Regards
Abhishek jain


mail2web - Check your email from the web at
http://mail2web.com/ .



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

RE: OLD_PASSWORD and PASSWORD whats these

2006-01-18 Thread [EMAIL PROTECTED]

Is there a way i can convert the strings converted from old password to
passwords one.So that it become compatible to the passwords one.
--
Abhishek jain

Original Message:
-
From: Ryan Stille [EMAIL PROTECTED]
Date: Wed, 18 Jan 2006 08:03:14 -0600
To: [EMAIL PROTECTED]
Subject: RE: OLD_PASSWORD and PASSWORD whats these


[EMAIL PROTECTED] wrote:
> Dear Friends,
> I had an application built using MySQL where i stored mine
> customers passwords encrypted using password function of
> mysql, now i changed mine servers and do find a new function
> old_password and password. I believe that old_password is equivalent
> to password. Actually i do not want to change mine application and
> change old_password whereever i have password , also i want to have
> all mine existing customers on the new server.So is there a
> way where i could disable this feature of PASSWORD and
> restore old function.
> Pl. help me.
> I shall be very grateful.
> --
> Regards
> Abhishek jain

There is a start up option that is something like --use-old-passwords
that makes MySQL 5.x behave like 4.x as far as passwords are concerned.
Alternatively you can always set your passwords with OLD_PASSWORD()
instead of PASSWORD() and you will have the old type of password.

-Ryan




mail2web - Check your email from the web at
http://mail2web.com/ .



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

OLD_PASSWORD and PASSWORD whats these

2006-01-18 Thread [EMAIL PROTECTED]

Dear Friends,
I had an application built using MySQL where i stored mine customers
passwords encrypted using password function of mysql, now i changed mine
servers and do find a new function old_password and password. I believe
that old_password is equivalent to password.
Actually i do not want to change mine application and change old_password
whereever i have password , also i want to have all mine existing customers
on the new server.So is there a way where i could disable this feature of
PASSWORD and restore old function.
Pl. help me.
I shall be very grateful.
--
Regards
Abhishek jain


mail2web - Check your email from the web at
http://mail2web.com/ .



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

A difficult query- urgent for me

2006-01-15 Thread [EMAIL PROTECTED]

Dear Friends,
I have a problm, try to solve that.
Actually there is a table with columns a and b .
So i want if a contains a particular word than a's value should return else
'b' value should return. And there must be one and only one column
returning. I have mysql 4.x and i think the logic will be like. I know i am
wrong by syntax but ...
SELECT IF(a REGEXP CONCAT('word' , '$'),a,b) from table_name where a =
'anything';

I think i am clear if not do please ask me questions.
I shall be very grateful if any one of you can give me a solutions.
--
Regards
Abhishek jain.


mail2web - Check your email from the web at
http://mail2web.com/ .



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

Getting no of different rows by group by clause

2005-11-22 Thread [EMAIL PROTECTED]

Hi Friends,
I have a table like 

name

MAD
LHR
MAD
LHR
AKL
AWL
AKL
LHR

I want the output as:
LHR 3
AKL 2
AWL 1

etc...

ie the no of entires sorted by their no of appearences.
I cannot do that by select name from tab_name group by name as it will not
give me the no of rows.

Pl. help me to find it. I know it would be simple but i think i am missing
some basic of MySQL.
Thanks
--
Regards
Abhishek Jain


mail2web - Check your email from the web at
http://mail2web.com/ .



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

Re: case insensitive search

2005-11-20 Thread [EMAIL PROTECTED]


Sorry. :)
This is what I thought: I have three entries:
12. afan, pasalic, afan_at_afan_dot_net, 2003-12-18
13. Afan, Pasalic, Pasalic_at_yaoo_dot_com, 2001-01-01
14. AFAN, PASALIC, ammtar_at_gamil_dot_com, 2003-12-18

If I search for *afan* only entry no. 12 will be shown.
If I search for *Afan* only entry no. 13 will be shown.
If I search for *AFAN* only entry no. 14 will be shown.

?!?


Thanks.

-afan



Jasper Bryant-Greene wrote:


[EMAIL PROTECTED] wrote:

but I'm getting case insensitive search?!? it's not the same I enter 
afan or Afan or AFAN!??



Your two statements contradict each other. Either you are getting case 
insensitive search, meaning that it *is* the same if you enter afan, 
Afan or AFAN, or you're getting case *sensitive* search, which means 
it isn't the same if you enter afan, Afan or AFAN.


Which is it?





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

case insensitive search

2005-11-18 Thread [EMAIL PROTECTED]


Hi!

I have this code:

   
   Search:
   

   
   

and this
#searching for salesperson
if(isset($_POST['SubmitSearch']))
{
   $SearchSalesperson = 
mysql_real_escape_string($_POST['SearchSalesperson']);

   $query = my_query("
   SELECT FName, LName
   FROM  Salespersons
   WHERE  FName LIKE '%$SearchSalesperson%' OR LName LIKE 
'%$SearchSalesperson%'

   ORDER BY LName ASC, FName ASC
   ", 0);
  while($result = mysql_fetch_array($query, MYSQL_ASSOC))
  {
  $SALESMEN[] = preg_replace("/$SearchSalesperson/", "style=\"background-color: gold;\">$SearchSalesperson", $result);

  }
}

but I'm getting case insensitive search?!? it's not the same I enter 
afan or Afan or AFAN!??


Any ideas?

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

2005-11-03 Thread [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]

report from two tables

2005-11-03 Thread [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: one product in more categories

2005-10-21 Thread [EMAIL PROTECTED]


Thanks Shawn.
See  comments:

[EMAIL PROTECTED] wrote:

Sorry I didn't get back to you earlier today. I have had a busy day. 
Comments embedded...

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote on 10/20/2005 04:35:30 PM:

 


Could you please tell me should this work?

CREATE TABLE categories (
 cat_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
 cat_name VARCHAR(45) NULL,
 cat_description TEXT NULL,
 cat_parent INTEGER(4) UNSIGNED NULL,
 cat_status ENUM('0','1') NULL,
 PRIMARY KEY(cat_id)
)
TYPE=InnoDB;

   

Good. I would add another INDEX for (cat_parent, cat_id) to speed up 
subcategory listings.


 


CREATE TABLE products (
 prod_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
 prod_no VARCHAR(12) NOT NULL,
 prod_name VARCHAR(45) NOT NULL,
 prod_description TEXT NULL,
 prod_colors TEXT NULL,// since there are tons of colors, defined 
differently by different suppliers, I had to go this way for color 
   


options
 


 prod_includes TEXT NULL,
 prod_catalog VARCHAR(45) NULL,
 prod_status ENUM('0','1') NOT NULL DEFAULT 0,
 prod_supplier VARCHAR(45) NULL,
 prod_start_date DATE NULL,
 prod_end_date DATE NULL,
 PRIMARY KEY(prod_id),
 INDEX products_index1(prod_status),
 INDEX products_index2(prod_start_date, prod_end_date)
)
TYPE=InnoDB;
   



Good again.
if you don't want your application to parse out a list of colors from the 
colors field, you will need a table of just colors and another association 
table between colors and products.
 


This is why I made color columns this way.
Product 07V0128: Neutrals - White(30n)Heathers - Ash (93h), Sport 
Grey (95h)Colors - Gold (24c), Navy (32c), Forest Green (33c), Black 
(36c), Orange (37c), Red (40c), Royal (51c), Lt Blue (69c), Purple 
(81c), Maroon (83c)
Product 07V0205: Neutrals: **White, Natural. Lights: *Ash, Graphite, 
**Light Steel. Darks - **Black, Bluestone, Cardinal, Copper, Daffodil 
Yellow, Denim Blue, Gold, Gold Nugget, Kelly Green, Light Blue, Lime, 
Moss, *Navy, Orange, Pink, Pebble, Pine, Purple, *Red
Product 07V0560: Apricot, Burnt Orange, Butter, Cedar, Celery, Chalky 
Blue, Chalky Mint, Chalky Purple, Charcoal, Cigar, Columbia Blue, 
Crimson, Dandelion, Denim, Dijon, Dorm Green, Forest, Grasshopper, Hot 
Red, Indigo, Kelly, Key Lime, Latte, Lime, Mustard, Nantucke

Since, there is NO RULE for colors, this is a only solution, right?

Remember that the optimizer won't use an index if it thinks that the index 
will return over 30% or so of the records in the table. With only two 
values in it, an index on prod_status (all by itself) will probably never 
have enough cardinality to be useful. Consider using it as part of a 
compound index instead.
 


Looks like I'm doing wrong for a lng time :)
Ok, then. Do I have to Index prod_status or it's not necessary?
Could you please give me more info on "Consider using it as part of a 
compound index instead"?



 

//since one product could be in more then one category, I created 
this assoc. table

CREATE TABLE categories_has_products (
 categories_cat_id INTEGER(8) UNSIGNED NOT NULL,
 products_prod_id INTEGER(8) UNSIGNED NOT NULL,
 PRIMARY KEY(categories_cat_id, products_prod_id),
 INDEX categories_has_products_FKIndex1(categories_cat_id),
 INDEX categories_has_products_FKIndex2(products_prod_id),
 FOREIGN KEY(categories_cat_id)
   REFERENCES categories(cat_id)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,
 FOREIGN KEY(products_prod_id)
   REFERENCES products(prod_id)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION
)
TYPE=InnoDB;
   



OK, your primary key acts as an index for categories_cat_id and for 
(categories_cat_id, products_prod_id). The second index on just 
categories_cat_id is redundant and can be deleted with no side-effects.


 

//table methods represent different kind of imprints on apparels. 
same an apparel with different kind of imprint could have a different 
   


price.
 


CREATE TABLE methods (
 met_id INTEGER(4) UNSIGNED NOT NULL AUTO_INCREMENT,
 met_name VARCHAR(12) NULL,
 PRIMARY KEY(met_id)
)
TYPE=InnoDB;

   


So far, you are doing a good job at normalizing.

 


CREATE TABLE prices (
 price_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
 products_prod_id INTEGER(8) UNSIGNED NOT NULL,
 methods_met_id INTEGER(4) UNSIGNED NOT NULL,
 qty INTEGER(8) UNSIGNED NULL,
 price DECIMAL(10,2) NULL,
 sale_price DECIMAL(10,2) NULL,
 PRIMARY KEY(price_id),
 INDEX prices_index1(qty),
 INDEX prices_FKIndex1(methods_met_id),
 INDEX prices_FKIndex2(products_prod_id),
 FOREIGN KEY(methods_met_id)
   REFERENCES methods(met_id)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,
 FOREIGN KEY(products_prod_id)
   REFERENCES products(prod_id)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION
)
TYPE=InnoDB;


Only thing that bothers me is start and end dates for sale prices:
shirt: available from today until 4/1/2006
price: $7.95
on sale from 11/15/2005 to 1/15/2006
sale

Re: one product in more categories

2005-10-20 Thread [EMAIL PROTECTED]


Somethign like this?

insert into products values (null, 'AP1520', 'Ultra Blend 50/50 Sport 
Shirt', '5.6 ounce, 50% cotton/ 50% polyester fabric. Contoured collar 
and cuffs, double-stitched seams. Clean finished placket with reinforced 
box and 3 woodtone buttons. Sizes M-3X.', 'Neutrals - White(30n) 
Heathers - Ash (93h), Sport Grey (95h) Colors - Gold (24c), Navy (32c), 
Forest Green (33c), Black (36c), Orange (37c), Red (40c), Royal (51c), 
Lt Blue (69c), Purple (81c), Maroon (83c)', 'Price includes 1-color 
screened imprint. Screen charge, add $24.00(g) per color, per location. 
Additional imprint colors add $.42(c) per color per location. 
Embroidered imprint - 1-location, up to 5 thread colors, 5000 stitches. 
Tape charge, add $75.00(g) for 5,000 stitches, over 5,000 stitches add 
$15.00(g) per 1,000 stitches. Run charge over 5,000 stitches, add 
$.45(c), per 1,000 stitches. Add $2.75(c) per 2X - 5X.', 'Katalog_Ime', 
1, 'Vernon', 2005-10-10, 2005-12-31)


INSERT INTO methods VALUES (1, 'Regular');
INSERT INTO methods VALUES (4, 'Emboidered');
INSERT INTO methods VALUES (5, 'Screened');
INSERT INTO methods VALUES (6, 'Screened - dark');

INSERT INTO prices VALUES (null, 'AP1520', 4, 12, '9.07', 0);
INSERT INTO prices VALUES (null, 'AP1520', 4, 24, '8.91', 0);
INSERT INTO prices VALUES (null, 'AP1520', 4, 48, '8.83', 0);
INSERT INTO prices VALUES (null, 'AP1520', 4, 72, '8.75', 0);
INSERT INTO prices VALUES (null, 'AP1520', 4, 144, '7.22', 0);
INSERT INTO prices VALUES (null, 'AP1520', 5, 12, '9.84', 0);
INSERT INTO prices VALUES (null, 'AP1520', 5, 24, '9.66', 0);
INSERT INTO prices VALUES (null, 'AP1520', 5, 48, '9.58', 0);
INSERT INTO prices VALUES (null, 'AP1520', 5, 72, '9.49', 0);
INSERT INTO prices VALUES (null, 'AP1520', 5, 144, '7.93', 0);
INSERT INTO prices VALUES (null, 'AP1520', 6, 12, '10.42', 0);
INSERT INTO prices VALUES (null, 'AP1520', 6, 24, '10.23', 0);
INSERT INTO prices VALUES (null, 'AP1520', 6, 48, '10.14', 0);
INSERT INTO prices VALUES (null, 'AP1520', 6, 72, '10.05', 0);
INSERT INTO prices VALUES (null, 'AP1520', 6, 144, '8.47', 0);

And if I have Embroidered on sale:

UPDATE prices SET sale_price = '7.07' WHERE price_id = $price_id);
UPDATE prices SET sale_price = '6.91' WHERE price_id = $price_id);
UPDATE prices SET sale_price = '6.83' WHERE price_id = $price_id);
UPDATE prices SET sale_price = '6.75' WHERE price_id = $price_id);
UPDATE prices SET sale_price = '5.22' WHERE price_id = $price_id);

Does it make a sense?

-afan


Peter Brawley wrote:


afan,

The model is progressing. The test for how well it works is what 
happens when you ask it to represent all presently known types of 
products. What happens when you hae to apply two 'methods' to one 
product?


PB

-

[EMAIL PROTECTED] wrote:


Could you please tell me should this work?

CREATE TABLE categories (
 cat_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
 cat_name VARCHAR(45) NULL,
 cat_description TEXT NULL,
 cat_parent INTEGER(4) UNSIGNED NULL,
 cat_status ENUM('0','1') NULL,
 PRIMARY KEY(cat_id)
)
TYPE=InnoDB;


CREATE TABLE products (
 prod_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
 prod_no VARCHAR(12) NOT NULL,
 prod_name VARCHAR(45) NOT NULL,
 prod_description TEXT NULL,
 prod_colors TEXT NULL,// since there are tons of colors, defined 
differently by different suppliers, I had to go this way for color 
options

 prod_includes TEXT NULL,
 prod_catalog VARCHAR(45) NULL,
 prod_status ENUM('0','1') NOT NULL DEFAULT 0,
 prod_supplier VARCHAR(45) NULL,
 prod_start_date DATE NULL,
 prod_end_date DATE NULL,
 PRIMARY KEY(prod_id),
 INDEX products_index1(prod_status),
 INDEX products_index2(prod_start_date, prod_end_date)
)
TYPE=InnoDB;


//since one product could be in more then one category, I created 
this assoc. table

CREATE TABLE categories_has_products (
 categories_cat_id INTEGER(8) UNSIGNED NOT NULL,
 products_prod_id INTEGER(8) UNSIGNED NOT NULL,
 PRIMARY KEY(categories_cat_id, products_prod_id),
 INDEX categories_has_products_FKIndex1(categories_cat_id),
 INDEX categories_has_products_FKIndex2(products_prod_id),
 FOREIGN KEY(categories_cat_id)
   REFERENCES categories(cat_id)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,
 FOREIGN KEY(products_prod_id)
   REFERENCES products(prod_id)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION
)
TYPE=InnoDB;


//table methods represent different kind of imprints on apparels. 
same an apparel with different kind of imprint

Re: one product in more categories

2005-10-20 Thread [EMAIL PROTECTED]


Could you please tell me should this work?

CREATE TABLE categories (
 cat_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
 cat_name VARCHAR(45) NULL,
 cat_description TEXT NULL,
 cat_parent INTEGER(4) UNSIGNED NULL,
 cat_status ENUM('0','1') NULL,
 PRIMARY KEY(cat_id)
)
TYPE=InnoDB;


CREATE TABLE products (
 prod_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
 prod_no VARCHAR(12) NOT NULL,
 prod_name VARCHAR(45) NOT NULL,
 prod_description TEXT NULL,
 prod_colors TEXT NULL,// since there are tons of colors, defined 
differently by different suppliers, I had to go this way for color options

 prod_includes TEXT NULL,
 prod_catalog VARCHAR(45) NULL,
 prod_status ENUM('0','1') NOT NULL DEFAULT 0,
 prod_supplier VARCHAR(45) NULL,
 prod_start_date DATE NULL,
 prod_end_date DATE NULL,
 PRIMARY KEY(prod_id),
 INDEX products_index1(prod_status),
 INDEX products_index2(prod_start_date, prod_end_date)
)
TYPE=InnoDB;


//since one product could be in more then one category, I created 
this assoc. table

CREATE TABLE categories_has_products (
 categories_cat_id INTEGER(8) UNSIGNED NOT NULL,
 products_prod_id INTEGER(8) UNSIGNED NOT NULL,
 PRIMARY KEY(categories_cat_id, products_prod_id),
 INDEX categories_has_products_FKIndex1(categories_cat_id),
 INDEX categories_has_products_FKIndex2(products_prod_id),
 FOREIGN KEY(categories_cat_id)
   REFERENCES categories(cat_id)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,
 FOREIGN KEY(products_prod_id)
   REFERENCES products(prod_id)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION
)
TYPE=InnoDB;


//table methods represent different kind of imprints on apparels. 
same an apparel with different kind of imprint could have a different price.

CREATE TABLE methods (
 met_id INTEGER(4) UNSIGNED NOT NULL AUTO_INCREMENT,
 met_name VARCHAR(12) NULL,
 PRIMARY KEY(met_id)
)
TYPE=InnoDB;



CREATE TABLE prices (
 price_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
 products_prod_id INTEGER(8) UNSIGNED NOT NULL,
 methods_met_id INTEGER(4) UNSIGNED NOT NULL,
 qty INTEGER(8) UNSIGNED NULL,
 price DECIMAL(10,2) NULL,
 sale_price DECIMAL(10,2) NULL,
 PRIMARY KEY(price_id),
 INDEX prices_index1(qty),
 INDEX prices_FKIndex1(methods_met_id),
 INDEX prices_FKIndex2(products_prod_id),
 FOREIGN KEY(methods_met_id)
   REFERENCES methods(met_id)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,
 FOREIGN KEY(products_prod_id)
   REFERENCES products(prod_id)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION
)
TYPE=InnoDB;


Only thing that bothers me is start and end dates for sale prices:
shirt: available from today until 4/1/2006
price: $7.95
on sale from 11/15/2005 to 1/15/2006
sale price: $5.95

Right now, with "my" solution, administrator has to turn on/off "on 
sale". I need to automate this by start and end dates for sale.

Any ideas?

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: one product in more categories

2005-10-19 Thread [EMAIL PROTECTED]

No. It doesn't work. First, I found one error: there are two columns for 
same thing in ac_products ac_products_product_id and product_id.

Second, ac_extended_prices table doesn't fit with multiple solutions

:(




[EMAIL PROTECTED] wrote:


Thanks guys for really detailed answers.

After your emails I talked to project supervisor and found that there 
is "some changes" in the project:
(i) do you know in advance all the kinds of price extensions that can 
come up?

- I hope I know them now :(

(ii) do you want the price rules to be (a) in the database or (b) in 
the app?
(iii) if the answer to (ii) is (a), do you want the rules in stored 
procedures, or in tables which application code must parse?
- Those two I really didn't get. If you thought on this: there is no 
rule in making prices for different number of items in pack. next 
price is NOT for x% lower or for $x lower. There is no rule. If you 
were thinking on something else please explain. Thanks.


(iv) does the app need to track price history (e.g. so it can recreate 
a price computation from six months ago)?
- This would be actually more online catalog where visitor/customer 
will create an inquiry. And we don't need to track a purchase history 
in this case.


But, Peter's 2nd part is actually "touching" the change in the 
project: product can have more then 2 prices. E.g. if you select shirt 
with your logo embroidered - it's one price. If your logo will be 
screened on the shirt - other price. And then if the shirt is on sale 
- 2 more prices Total 4 different prices have to be shown on catalog.

The solution:

CREATE TABLE pricemodtypes (
pricemodtype_id INT AUTO_INCREMENT PRIMARY KEY,
name CHAR(20)
)

CREATE TABLE extended_prices (
epid INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
pricemodtype_id INT NOT NULL,
qty_up_to SMALLINT NOT NULL,
begindate DATE NOT NULL,
enddate DATE NOT NULL,
price_per DECIMAL(10,2) NOT NULL,
price_per_mod DECIMAL(10,2) NULL
);

will be fine?

Actually, there is what I have for the moment for my DB:

categories and subcategories:
CREATE TABLE ac_categories (
 cat_id INT(6) NOT NULL AUTO_INCREMENT,
 cat_name VARCHAR(45) NULL,
 cat_description TEXT NULL,
 cat_parent INTEGER(4) UNSIGNED NULL,
 cat_status ENUM('0','1') NULL DEFAULT 0,
 PRIMARY KEY(cat_id),
 INDEX ac_categories_index1(cat_status)
);

CREATE TABLE ac_products (
 product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
 product_no VARCHAR(12) NULL,
 product_name VARCHAR(45) NULL,
 product_description TEXT NULL,
 product_colors TEXT NULL,   // since there is hundreds of different 
colors and color combination, we will have colors as description
 product_includes TEXT NULL,   // shows what is includes in price (e.g 
how many colors for logo and how much costs additional color)
 product_catalog VARCHAR(45) NULL, // products are in groups of 
catalogs - for internal use
 product_status ENUM('0','1') NULL, // is product available (visible 
at front end)

 product_supplier VARCHAR(45) NULL,
 product_start_date DATE NULL,
 product_exp_date DATE NULL,
 PRIMARY KEY(product_id),
 INDEX ac_products_index1(product_start_date, product_exp_date),
 INDEX ac_products_index2(product_status),
);


since, one product can be in more than one category:
CREATE TABLE ac_products_has_ac_categories (
 ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
 ac_categories_cat_id INT(6) NOT NULL,
 PRIMARY KEY(ac_products_product_id, ac_categories_cat_id),
 INDEX ac_products_has_ac_categories_FKIndex1(ac_products_product_id),
 INDEX ac_products_has_ac_categories_FKIndex2(ac_categories_cat_id)
);

CREATE TABLE ac_extended_prices (
 epid INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
 ac_pricemodtypes_pricemodtype_id INT(8) NOT NULL,
 ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
 product_id INTEGER(8) UNSIGNED NULL,
 pricemodtype_id INTEGER(8) UNSIGNED NULL,
 qty_up_to INTEGER(8) UNSIGNED NULL,
 begindate DATE NULL,
 enddate DATE NULL,
 price_per DECIMAL(10,2) NOT NULL,
 price_per_mod DECIMAL(10,2) NULL,
 PRIMARY KEY(epid),
 INDEX ac_extended_prices_index_date(begindate, enddate),
 INDEX ac_extended_prices_index_qty(qty_up_to),
 INDEX ac_extended_prices_FKIndex1(ac_products_product_id),
 INDEX ac_extended_prices_FKIndex2(ac_pricemodtypes_pricemodtype_id)
);

CREATE TABLE ac_pricemodtypes (
 pricemodtype_id INT(8) NOT NULL AUTO_INCREMENT,
 name CHAR(40) NULL,
 PRIMARY KEY(pricemodtype_id)
);



Your opinion?

Thanks for help.

-afan



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

Re: one product in more categories

2005-10-19 Thread [EMAIL PROTECTED]

ables:


1. It will be best to type the primary & foreign keys identically--all 
unsigned, or all not.


2. To avoid rounding errors, use DECIMAL rather than FLOAT for money 
columns.


Before you model extended price computations, you have to ask & answer 
crucial questions:
(i) do you know in advance all the kinds of price extensions that can 
come up?
(ii) do you want the price rules to be (a) in the database or (b) in 
the app?
(iii) if the answer to (ii) is (a), do you want the rules in stored 
procedures, or in tables which application code must parse?
(iv) does the app need to track price history (eg so it can recreate a 
price computation from six months ago)?


Suppose the answers are those that most conventionally apply: only qty 
and sale will ever come up, the rules will be in the app, and you can 
leave history to the backups. Then you can take a very simple, 
semi-normalised approach (leaving out some details):


CREATE TABLE extended_prices (
 epid INT AUTO_INCREMENT PRIMARY KEY,
 product_id INT NOT NULL,
 qty_up_to SMALLINT NOT NULL,
 price_per DECIMAL(10,2) NOT NULL,
 price_per_sale DECIMAL(10,2) NULL
);

which permits you to enter whatever (qty cutoffs, price, sale) combos 
are desired for any desired products, and find them for any product 
with a very simple query. There is a risk, though: in six months the 
client may find that new price extensions are needed, and/or that she 
needs history after all.


Now, add the wrinkles that other possible, but presently unidentified 
price extensions (eg 'special promotions', 'coupons', &c) will be 
required, and that history must be tracked. Now you need at least, 
again normalising only partly...


CREATE TABLE pricemodtypes (
 pricemodtype_id INT AUTO_INCREMENT PRIMARY KEY,
 name CHAR(20)
)

CREATE TABLE extended_prices (
 epid INT AUTO_INCREMENT PRIMARY KEY,
 product_id INT NOT NULL,
 pricemodtype_id INT NOT NULL,
 qty_up_to SMALLINT NOT NULL,
 begindate DATE NOT NULL,
 enddate DATE NOT NULL,
 price_per DECIMAL(10,2) NOT NULL,
 price_per_mod DECIMAL(10,2) NULL
);

the query to retrieve all the prices for a product is more complicated 
but still straightforward.


Of course wrinkles multiply as if conjured by a Sorcerer's Apprentice. 
Perhaps we should pause here for a breath. Is this the info you need?


PB





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

Re: one product in more categories

2005-10-18 Thread [EMAIL PROTECTED]

For the same project (below) I have problem with building table for 
product prices.

In "regular" online store, price is usually part of the products table.
But, I need a solution for multiple prices. E.g.
QTY -2550   100   200
Price -   $1.59   $1.39   $1.19   $0.99

Also, if product is On Sale I need to be shown both prices: regular and 
sale price

QTY -2550   100   200
Price -$1.59   $1.39   $1.19   $0.99
Sale - $0.99   $0.99   $0.99   $0.99

What would be structure of "Quantity" and "Price" tables?

My guess:

CREATE TABLE ac_quantities (
 quantity_id INT(8) NOT NULL AUTO_INCREMENT,
 quantity INTEGER(6) NOT NULL,
 product_id INTEGER(8) NOT NULL,
 PRIMARY KEY(quantity_id),
 INDEX ac_quantities_index1(quantity)
);


CREATE TABLE ac_prices (
 price_id INT(8) NOT NULL AUTO_INCREMENT,
 price FLOAT(10,2) NOT NULL,
 product_id INTEGER(8) NOT NULL,
 product_type ENUM('regular','sale') NOT NULL DEFAULT 'regular',
 PRIMARY KEY(price_id),
 INDEX ac_prices_index1(price)
);



How "close" am I?
:)




[EMAIL PROTECTED] wrote:


"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote on 10/18/2005 01:50:20 PM:

 


Hi to all!

I have tables products and categories

CREATE TABLE categories (
 cat_id INT(6) NOT NULL AUTO_INCREMENT,
 cat_name VARCHAR(45) NULL,
 cat_description TEXT NULL,
 cat_parent INTEGER(4) UNSIGNED NULL,
 cat_status ENUM(0,1) NULL DEFAULT 0,
 PRIMARY KEY(cat_id),
 INDEX ac_categories_index1(cat_status)
);

CREATE TABLE products (
 product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
 product_no VARCHAR(12) NULL,
 product_name VARCHAR(45) NULL,
 product_description TEXT NULL,
 product_colors TEXT NULL,
 product_includes TEXT NULL,
 product_catalog VARCHAR(45) NULL,
 product_status ENUM(0,1) NULL,
 product_supplier VARCHAR(45) NULL,
 product_start_date DATE NULL,
 product_exp_date DATE NULL,
 product_on_sale ENUM(0,1) NULL,
 PRIMARY KEY(product_id),
 INDEX ac_products_index1(product_start_date, product_exp_date),
 INDEX ac_products_index2(product_status),
 INDEX ac_products_index_onsale(product_on_sale)
);

Since one product can be in more then one category, is it correct to 
create thirs table with those info?


CREATE TABLE ac_products_categories (
 pc_id INTEGER(6) UNSIGNED NOT NULL AUTO_INCREMENT,
 ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
 p_id INTEGER(6) UNSIGNED NULL,
 c_id INTEGER(6) UNSIGNED NULL,
 PRIMARY KEY(pc_id),
 INDEX pc_index(p_id, c_id),
);

Or, there is better solution?

Thanks!

-afan


   



I think you may have just one too many columns. I can understand c_id and 
p_id. Those point to categories.cat_id and products.product_id. I 
understand creating a column to identify the association (pc_id). But what 
is the column ac_products_product_id for?


I would have probably defined it this way

CREATE TABLE ac_products_categories (
   p_id int unsigned not null,
   c_id int unsigned not null,
   PRIMARY KEY(p_id, c_id),
   INDEX(c_id, p_id)
);

The PK ensures that each product can only be associated with any category 
only once. The other key makes reverse lookups blindingly fast (if you 
know the category and want a list of all of the products). Forward lookups 
are covered by the PK.


I didn't identify the association with it's own column because there is 
nothing else this association carries with it (no other data ABOUT the 
association). If you wanted to add something like who assigned this 
product to this category or what date it was added, then I may have left 
it in. 

To answer your bigger question, "YES!"  This is a proper way of creating a 
many-to-many relationship in MySQL. Good job!


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: one product in more categories

2005-10-18 Thread [EMAIL PROTECTED]


Oops! My bad! :)

original name for categories and products are  ac_categories and 
ac_products. but I wanted to "simplify" and deleted "ac_" part - but not 
on all places. And my "simplified" query become "mess" query. Sorry.


But, you got a point. And I got the answer.

And "ac_products_categories" table doesn't have any other association. 
It doesn't matter who, when, why added a prodcut to particular category.



Thanks Shawn.




[EMAIL PROTECTED] wrote:


"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote on 10/18/2005 01:50:20 PM:

 


Hi to all!

I have tables products and categories

CREATE TABLE categories (
 cat_id INT(6) NOT NULL AUTO_INCREMENT,
 cat_name VARCHAR(45) NULL,
 cat_description TEXT NULL,
 cat_parent INTEGER(4) UNSIGNED NULL,
 cat_status ENUM(0,1) NULL DEFAULT 0,
 PRIMARY KEY(cat_id),
 INDEX ac_categories_index1(cat_status)
);

CREATE TABLE products (
 product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
 product_no VARCHAR(12) NULL,
 product_name VARCHAR(45) NULL,
 product_description TEXT NULL,
 product_colors TEXT NULL,
 product_includes TEXT NULL,
 product_catalog VARCHAR(45) NULL,
 product_status ENUM(0,1) NULL,
 product_supplier VARCHAR(45) NULL,
 product_start_date DATE NULL,
 product_exp_date DATE NULL,
 product_on_sale ENUM(0,1) NULL,
 PRIMARY KEY(product_id),
 INDEX ac_products_index1(product_start_date, product_exp_date),
 INDEX ac_products_index2(product_status),
 INDEX ac_products_index_onsale(product_on_sale)
);

Since one product can be in more then one category, is it correct to 
create thirs table with those info?


CREATE TABLE ac_products_categories (
 pc_id INTEGER(6) UNSIGNED NOT NULL AUTO_INCREMENT,
 ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
 p_id INTEGER(6) UNSIGNED NULL,
 c_id INTEGER(6) UNSIGNED NULL,
 PRIMARY KEY(pc_id),
 INDEX pc_index(p_id, c_id),
);

Or, there is better solution?

Thanks!

-afan


   



I think you may have just one too many columns. I can understand c_id and 
p_id. Those point to categories.cat_id and products.product_id. I 
understand creating a column to identify the association (pc_id). But what 
is the column ac_products_product_id for?


I would have probably defined it this way

CREATE TABLE ac_products_categories (
   p_id int unsigned not null,
   c_id int unsigned not null,
   PRIMARY KEY(p_id, c_id),
   INDEX(c_id, p_id)
);

The PK ensures that each product can only be associated with any category 
only once. The other key makes reverse lookups blindingly fast (if you 
know the category and want a list of all of the products). Forward lookups 
are covered by the PK.


I didn't identify the association with it's own column because there is 
nothing else this association carries with it (no other data ABOUT the 
association). If you wanted to add something like who assigned this 
product to this category or what date it was added, then I may have left 
it in. 

To answer your bigger question, "YES!"  This is a proper way of creating a 
many-to-many relationship in MySQL. Good job!


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]

one product in more categories

2005-10-18 Thread [EMAIL PROTECTED]


Hi to all!

I have tables products and categories

CREATE TABLE categories (
 cat_id INT(6) NOT NULL AUTO_INCREMENT,
 cat_name VARCHAR(45) NULL,
 cat_description TEXT NULL,
 cat_parent INTEGER(4) UNSIGNED NULL,
 cat_status ENUM(0,1) NULL DEFAULT 0,
 PRIMARY KEY(cat_id),
 INDEX ac_categories_index1(cat_status)
);

CREATE TABLE products (
 product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
 product_no VARCHAR(12) NULL,
 product_name VARCHAR(45) NULL,
 product_description TEXT NULL,
 product_colors TEXT NULL,
 product_includes TEXT NULL,
 product_catalog VARCHAR(45) NULL,
 product_status ENUM(0,1) NULL,
 product_supplier VARCHAR(45) NULL,
 product_start_date DATE NULL,
 product_exp_date DATE NULL,
 product_on_sale ENUM(0,1) NULL,
 PRIMARY KEY(product_id),
 INDEX ac_products_index1(product_start_date, product_exp_date),
 INDEX ac_products_index2(product_status),
 INDEX ac_products_index_onsale(product_on_sale)
);

Since one product can be in more then one category, is it correct to 
create thirs table with those info?


CREATE TABLE ac_products_categories (
 pc_id INTEGER(6) UNSIGNED NOT NULL AUTO_INCREMENT,
 ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
 p_id INTEGER(6) UNSIGNED NULL,
 c_id INTEGER(6) UNSIGNED NULL,
 PRIMARY KEY(pc_id),
 INDEX pc_index(p_id, c_id),
);

Or, there is better solution?

Thanks!

-afan




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

Problems with backup and special characters

2005-08-09 Thread [EMAIL PROTECTED]

Restoring a backup of a very large database, I've received an error 
message by the server (4.13) about an sql syntax error. I've done the 
backup with mysql administrator and I've used the same procedure for 
months without problems.
I've discovered that problem is into a blob 
field that store long descriptive text. That filed, is populated 
automatically with an import procedure from an Oracle db where, when a 
phrase is too long, it continues on another record. In this field, 
often is stored phrases contained between quotation marks ("). However 
the problem is present only when the phrase begin into a record with 
quotation marks and reach the end into the next record BUT with the 
ONLY quotation marks.
How can I solve this problem if I cannot modify 
the insert sql statement? I need an expedient that works only for the 
backup and restore procedures.
Thanks a lot
Stefano

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

create database outside /mysql/data/

2005-08-08 Thread l&#x27;[EMAIL PROTECTED]

I installed Mysql on windows and found out that the documentation does not 
talk about installing the database in another directory or partition of the 
disk.


Is it possible in Windows?
I have little space left on the partition where progrxxxFiles/mysql/ is.

THanks
Laurie 



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

Re: telnet localhost 3306 Connection refused

2005-07-07 Thread l&#x27;[EMAIL PROTECTED]


Have you checked that the user failing to connect has the right privileges?
Check the user table of mysql database to see if that user has a select 
privilege in that table.


Laurie

At 01:01 AM 7/7/2005, Daevid Vincent wrote:

What is causing me to have this problem in "mysql  Ver 12.22 Distrib 4.0.24,
for pc-linux-gnu (i686)". I am running shorewall, but that shouldn't affect
localhost should it? My firewall, web, and mySQL server are the same
machine.

# telnet localhost 3306
Trying 127.0.0.1...
telnet: Unable to connect to remote host: Connection refused

I've tried to comment, uncomment and change to * this 'bind-address' line in
/etc/mysql/my.cnf
# keep secure by default!
#bind-address= 127.0.0.1
#bind-address= *
port= 3306

Of course, I can't connect from any of the other IP addresses that my mySQL
server is assigned either:

# telnet 192.168.1.1 3306
Trying 192.168.1.1...
telnet: Unable to connect to remote host: Connection refused

# telnet 10.10.10.1 3306
Trying 10.10.10.1...
telnet: Unable to connect to remote host: Connection refused


# ifconfig
eth1  Link encap:Ethernet
  inet addr:192.168.1.1  Bcast:192.168.1.255  Mask:255.255.255.0
loLink encap:Local Loopback
  inet addr:127.0.0.1  Mask:255.0.0.0
wlan0 Link encap:Ethernet
  inet addr:10.10.10.1  Bcast:10.255.255.255  Mask:255.255.255.0


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

GRANT and mysql.user table

2005-07-04 Thread l&#x27;[EMAIL PROTECTED]


I tried as root having GRANT ALL PRIVILEGES with GRANT OPTION
to change the privilege of a user:
GRANT ALL ON mydb.*  TO myUser;

The result of this statement is that the query is OK and 0 rows are affected.
WHen I look at the mysql table holding the grants: user, the myUser row is 
unchanged.


What could possibly be preventing the system from changing the GRANT of myUser?
Is the mysql.db table part of the granting?

thanks in advance for your help.
laurie 



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

innodb design question

2005-06-15 Thread [EMAIL PROTECTED]


Greetings list,

In an analysis I was sent, there is 1 table with a simple set of rules, e.g.
a_table (id, day, max_hours, min_hours, min_days, start_time, stop_time, 
max_attendants, ...)

No biggie there.

The problem is the user has to be able to define exceptions to those rules.
These exceptions will at one time be a quantitative value, i.e. a 
minimum or maximum number of attendants for a specific day, at other 
times it will concern a start_time that has to be met at least X times a 
month, etc.


I've more or less broken the table structure for this down to a couple 
of tables, like this:

table_case_quantity
table_case_actions
table_case_day
table_case_time
...
I 'concatenated' (foreign keys) related tables together into tables that 
specify a certain condition. At this time, there are 3 kinds of 
exception tables.

In other words, I've managed to normalize this, no problem there.

And now for the question :)
When looking up exceptions, I would like it if there is just 1 table 
'exceptions', that looks like this:
exceptions (id, exception_type, 
id_of_the_exception_in_the_table_of_that_exception_type), i.e. while 
keeping a foreign constraint.

In bashed up SQL syntax, I guess what I'd like to do is
FOREIGN KEY (exception_type) REFERENCES table_of_the_exception_type   
(<--- notice no id, just the table)

FOREIGN KEY (id_of_the_...) REFERENCES table_of_the_exception_type(id)

I'm thinking this has come up earlier, the question is how to do it.

The other option is querying the db for every exception-type-table, 
which I'd prefer not doing.



TIA,

Stijn Verholen


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

import data into table with existing data fails

2005-06-14 Thread l&#x27;[EMAIL PROTECTED]



I imported data into a table "mytable"  into a databse "mydb" with existing 
data.

mysqlimport -L mydb data.txt

that operation failed.
I interrupt it.

It seems that I imported data with the same indexes (duplicate entries)

I tried: delete from mytable;
that seems to fail too

What happens when you interrupt such operation: unstable state (indexes, 
references to other keys ...)


What can I do to repair?
Should I drop the table, create it again and fill it up with my data?
Or is there more to do than that?

Thank you for your suggestions
Laurie


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

Re: Problems importing dump 4.0 > 4.1

2005-06-03 Thread [EMAIL PROTECTED]

Hi,

Thanks for your reply.

What do you mean by "right character set" ?
The default character set for ver. 4.1 is latin1, right ? This set should
support all western european languages !

Regarding --default-character-set command in chapter 10 is written that a way to
change settings is to recompile...
Is there another less painful way to do this ?

Thanks in advance

Roberto Jobet


> Hello.
>
> Specify the right character set with --default-character-set command
> line option for mysql (if you're using it for reading dump). Chapter 10
> of the manual could be helpful. See:
>   http://dev.mysql.com/doc/mysql/en/charset.html
>
>
>
>
> "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> > I'm trying to import a db dump made on ver. 4.0, on a new 4.1 vers=
> > ion.
> >
> > All accented characters (french, spanish) are replaced by a '?'=0D
> > =
> >
> > What it's the right way to import it ?
> >
> >
> > Thanks in advance
> >
> > Robert=
> > o Jobet
> > =0A=0A=0A=0A=
> > =0ANavighi a 4 MEGA e i primi 3 mesi sono GRATIS. =0AScegli Liber=
> > o Adsl Flat senza limiti su http://www.libero.it=0A
> >
> >
>
>
> --
> 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]
>
> 



________
6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero!
Scaricalo su INTERNET GRATIS 6X http://www.libero.it



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

Problems importing dump 4.0 > 4.1

2005-06-02 Thread [EMAIL PROTECTED]

Hi,

I'm trying to import a db dump made on ver. 4.0, on a new 4.1 version.

All accented characters (french, spanish) are replaced by a '?'

What it's the right way to import it ?


Thanks in advance

Roberto Jobet





Navighi a 4 MEGA e i primi 3 mesi sono GRATIS. 
Scegli Libero Adsl Flat senza limiti su http://www.libero.it



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

Re: Import dump (4.0 > 4.1) and collation problem

2005-06-02 Thread [EMAIL PROTECTED]

Hi,

I don't understand very well, what should I do...

Does anyone has a step-by-step instructions on how to import a ver. 4.0 DB dump
into a 4.1 version ?

Thanks for any help

Roberto Jobet



>
> look at automatic character set conversion
> http://dev.mysql.com/doc/mysql/en/charset-collation-charset.html
>
> mathias
>
> Selon [EMAIL PROTECTED]:
>
> > Salut,
> > i don't see what doesn't work ! Where characters are replaced by ?, since 
> > you
> > say that insert,select and import work fine ? is it in the export ?
> >
> > see http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html for migration.
> >
> >
> > Mathias
> >
> > Selon "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>:
> >
> > > Voilà,
> > >
> > > Everything works fine: insert, select and importing. I have always the 
> > > same
> > > text:
> > >
> > > 'Ceci est un test en Français'
> > >
> > > So what could be the problem ? and how to recover foreign characters ?
> > >
> > > Thanks
> > >
> > > Roberto Jobet
> > >
> > >
> > > > i mean :
> > > > if you launch :
> > > > into into table (toto) values ('Ceci est un test en Français');
> > > > then :
> > > > select toto from table;
> > > >
> > > > can you read it correctly ? And if the same row is imported, is there 
> > > > any
> > > > difference ?
> > > >
> > > > A bientôt
> > > > Mathias
> > > >
> > > >
> > > > Selon "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>:
> > > >
> > > > > Salut,
> > > > >
> > > > > je vois que tu es français, j'aimerai bien continuer en français mais
> > il
> > > vaut
> > > > > mieux en anglais si jamais il y avait quelqu'un avec le même problème
> > ;-)
> > > > >
> > > > >
> > > > > So what I'm trying to achieve is to have UTF-8 databases in order to
> > > support
> > > > > multilingual content.
> > > > >
> > > > > But first I have to understand how to recover foreign characters in my
> > > DB.
> > > > >
> > > > > What do you mean by: "try some inserts to see if it is an import
> > problem
> > > " or
> > > > > "just the client display" ?
> > > > >
> > > > > Merci d'avance
> > > > >
> > > > > Roberto Jobet
> > > > >
> > > > >
> > > > > > hi,
> > > > > > your config supports european characters :
> > > > > >
> > > > > > mysql> select * from tst;
> > > > > > +--+---+
> > > > > > | a| txt   |
> > > > > > +--+-----------+
> > > > > > |1 | Ceci est un test en Français  |
> > > > > > |1 | se facilitará el conocimiento de la evolución del |
> > > > > > +--+---+
> > > > > > 2 rows in set (0.00 sec)
> > > > > >
> > > > > > mysql> show variables like '%coll%';
> > > > > > +--+---+
> > > > > > | Variable_name| Value |
> > > > > > +--+---+
> > > > > > | collation_connection | latin1_swedish_ci |
> > > > > > | collation_database   | latin1_swedish_ci |
> > > > > > | collation_server | latin1_swedish_ci |
> > > > > > +--+---+
> > > > > > 3 rows in set (0.00 sec)
> > > > > >
> > > > > >
> > > > > > have you tried some inserts to see if it is an import problem, or
> > just
> > > the
> > > > > > client display ?
> > > > > >
> > > > > > Mathias
> > > > > >
> > > > > > Selon "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > >

Re: Import dump (4.0 > 4.1) and collation problem

2005-06-01 Thread [EMAIL PROTECTED]

Voilà,

Everything works fine: insert, select and importing. I have always the same 
text:

'Ceci est un test en Français'

So what could be the problem ? and how to recover foreign characters ?

Thanks

Roberto Jobet


> i mean :
> if you launch :
> into into table (toto) values ('Ceci est un test en Français');
> then :
> select toto from table;
>
> can you read it correctly ? And if the same row is imported, is there any
> difference ?
>
> A bientôt
> Mathias
>
>
> Selon "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>:
>
> > Salut,
> >
> > je vois que tu es français, j'aimerai bien continuer en français mais il 
> > vaut
> > mieux en anglais si jamais il y avait quelqu'un avec le même problème ;-)
> >
> >
> > So what I'm trying to achieve is to have UTF-8 databases in order to support
> > multilingual content.
> >
> > But first I have to understand how to recover foreign characters in my DB.
> >
> > What do you mean by: "try some inserts to see if it is an import problem " 
> > or
> > "just the client display" ?
> >
> > Merci d'avance
> >
> > Roberto Jobet
> >
> >
> > > hi,
> > > your config supports european characters :
> > >
> > > mysql> select * from tst;
> > > +--+---+
> > > | a| txt   |
> > > +--+---+
> > > |1 | Ceci est un test en Français  |
> > > |1 | se facilitará el conocimiento de la evolución del |
> > > +--+---+
> > > 2 rows in set (0.00 sec)
> > >
> > > mysql> show variables like '%coll%';
> > > +--+---+
> > > | Variable_name| Value |
> > > +--+---+
> > > | collation_connection | latin1_swedish_ci |
> > > | collation_database   | latin1_swedish_ci |
> > > | collation_server | latin1_swedish_ci |
> > > +--+---+
> > > 3 rows in set (0.00 sec)
> > >
> > >
> > > have you tried some inserts to see if it is an import problem, or just the
> > > client display ?
> > >
> > > Mathias
> > >
> > > Selon "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>:
> > >
> > > > Hi,
> > > >
> > > > I recently upgraded from 4.0 to 4.1 because of the multilingual support.
> > > >
> > > > But importing the DB dump into the new 4.1 version all foreign 
> > > > characters
> > > > (french, spanish, portoguese) have been replaced by ? character...
> > > >
> > > > I've been trying to understand the new "collation" feature but i seems
> > quite
> > > > difficult to setup.
> > > >
> > > > Anybody has already faced this problem ?
> > > >
> > > > Here's my current configuration:
> > > >
> > > > 1) my.cnf (only the mysqld section)
> > > >
> > > > [mysqld]
> > > > user= mysql
> > > > pid-file= /var/run/mysqld/mysqld.pid
> > > > socket  = /var/run/mysqld/mysqld.sock
> > > > port= 3306
> > > > # Both location gets rotated by the cronjob.
> > > > #log= /var/log/mysql.log
> > > > log = /var/log/mysql/mysql.log
> > > > basedir = /usr
> > > > datadir = /db
> > > > tmpdir  = /tmp
> > > > language= /usr/share/mysql/english
> > > >
> > > > 2) Output show variables like '%char%';
> > > >
> > > > character_set_client utf8
> > > > character_set_connection utf8
> > > > character_set_database latin1
> > > > character_set_results utf8
> > > > character_set_server latin1
> > > > character_set_system utf8
> > > > character_sets_dir /usr/share/mysql/charsets/
> > > >
> > > > 3) Out put show variables like '%coll%';
> > > >
> > > > collation_connection utf8_general_ci
> > > > collation_database latin1_swedish_ci
> > > > collation_server latin1_swedish_ci
> > > >
> > > > Thanks for any help
> > > >
> > > > Regards
> > > >
> > > > Roberto Jobet
> >
> >
> >
> >
> > 
> > 6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero!
> > Scaricalo su INTERNET GRATIS 6X http://www.libero.it
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
>
> 




6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero!
Scaricalo su INTERNET GRATIS 6X http://www.libero.it



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

Re: Import dump (4.0 > 4.1) and collation problem

2005-06-01 Thread [EMAIL PROTECTED]

Salut,

je vois que tu es français, j'aimerai bien continuer en français mais il vaut
mieux en anglais si jamais il y avait quelqu'un avec le même problème ;-)


So what I'm trying to achieve is to have UTF-8 databases in order to support
multilingual content.

But first I have to understand how to recover foreign characters in my DB.

What do you mean by: "try some inserts to see if it is an import problem " or
"just the client display" ?

Merci d'avance

Roberto Jobet


> hi,
> your config supports european characters :
>
> mysql> select * from tst;
> +--+---+
> | a| txt   |
> +--+---+
> |1 | Ceci est un test en Français  |
> |1 | se facilitará el conocimiento de la evolución del |
> +--+---+
> 2 rows in set (0.00 sec)
>
> mysql> show variables like '%coll%';
> +--+---+
> | Variable_name| Value |
> +--+---+
> | collation_connection | latin1_swedish_ci |
> | collation_database   | latin1_swedish_ci |
> | collation_server | latin1_swedish_ci |
> +--+---+
> 3 rows in set (0.00 sec)
>
>
> have you tried some inserts to see if it is an import problem, or just the
> client display ?
>
> Mathias
>
> Selon "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>:
>
> > Hi,
> >
> > I recently upgraded from 4.0 to 4.1 because of the multilingual support.
> >
> > But importing the DB dump into the new 4.1 version all foreign characters
> > (french, spanish, portoguese) have been replaced by ? character...
> >
> > I've been trying to understand the new "collation" feature but i seems quite
> > difficult to setup.
> >
> > Anybody has already faced this problem ?
> >
> > Here's my current configuration:
> >
> > 1) my.cnf (only the mysqld section)
> >
> > [mysqld]
> > user= mysql
> > pid-file= /var/run/mysqld/mysqld.pid
> > socket  = /var/run/mysqld/mysqld.sock
> > port= 3306
> > # Both location gets rotated by the cronjob.
> > #log= /var/log/mysql.log
> > log = /var/log/mysql/mysql.log
> > basedir = /usr
> > datadir = /db
> > tmpdir  = /tmp
> > language= /usr/share/mysql/english
> >
> > 2) Output show variables like '%char%';
> >
> > character_set_client utf8
> > character_set_connection utf8
> > character_set_database latin1
> > character_set_results utf8
> > character_set_server latin1
> > character_set_system utf8
> > character_sets_dir /usr/share/mysql/charsets/
> >
> > 3) Out put show variables like '%coll%';
> >
> > collation_connection utf8_general_ci
> > collation_database latin1_swedish_ci
> > collation_server latin1_swedish_ci
> >
> > Thanks for any help
> >
> > Regards
> >
> > Roberto Jobet





6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero!
Scaricalo su INTERNET GRATIS 6X http://www.libero.it



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

Import dump (4.0 > 4.1) and collation problem

2005-06-01 Thread [EMAIL PROTECTED]

Hi,

I recently upgraded from 4.0 to 4.1 because of the multilingual support.

But importing the DB dump into the new 4.1 version all foreign characters
(french, spanish, portoguese) have been replaced by ? character...

I've been trying to understand the new "collation" feature but i seems quite
difficult to setup.

Anybody has already faced this problem ?

Here's my current configuration:

1) my.cnf (only the mysqld section)

[mysqld]
user= mysql
pid-file= /var/run/mysqld/mysqld.pid
socket  = /var/run/mysqld/mysqld.sock
port= 3306
# Both location gets rotated by the cronjob.
#log= /var/log/mysql.log
log = /var/log/mysql/mysql.log
basedir = /usr
datadir = /db
tmpdir  = /tmp
language= /usr/share/mysql/english

2) Output show variables like '%char%';

character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/

3) Out put show variables like '%coll%';

collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci

Thanks for any help

Regards

Roberto Jobet




6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero!
Scaricalo su INTERNET GRATIS 6X http://www.libero.it



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

select count(*) table

2005-05-13 Thread [EMAIL PROTECTED]

I have a curious situation I was hoping someone could shed some light on.
mysql> select count(*) table;
+---+
| table |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
mysql> select count(*) from table;
+--+
| count(*) |
+--+
|25965 |
+--+
1 row in set (0.00 sec)
I found it curious that the first query didn't return an error with 
there being no 'from', and even more curious that it returned a 0.  Is 
the first query actually legit, and if so, what does the 0 mean?

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

mysqldump appears to generate invalid code.

2005-05-01 Thread [EMAIL PROTECTED]

I created a mysqldump with --quote-names option, but I get this 
error:C:\xampp\xampp\mysql\bin>mysql -uroot db2 < test5.txtERROR 1005 at 
line 54: Can't create table '.\db2\shift.frm' (errno: 
150)C:\xampp\xampp\mysql\bin>Line 54 is the CREATE TABLE... line of the 
following bit of SQL from the file, and the original database created on the 
same server works fine.How is it that mysqldump can successfully create the sql 
file, but the code it has generated fails to create a new database.  Can any 
one say what might be wrong with the foreign key format (err 150) or 
mysqldump.exe
I have MySQL 4.0.18 and mysqldump  Ver 9.10 Distrib 4.0.17, for Win95/Win98 
(i32) as supplied to me.Andrew HCREATE TABLE `shift` (  `staff` char(20) NOT 
NULL default '',  `shift` char(1) NOT NULL default '',  `client` char(20) 
default NULL,  `role` char(1) default NULL,  `on` date NOT NULL default 
'-00-00',  PRIMARY KEY  (`staff`,`shift`,`on`),  KEY `staff` 
(`staff`,`role`),  KEY `shift` (`shift`),  KEY `client` (`client`),  KEY `role` 
(`role`),  CONSTRAINT `shift_ibfk_1` FOREIGN KEY (`staff`, `role`) REFERENCES 
`staffrole` (`uniquename`, `role`),  CONSTRAINT `shift_ibfk_2` FOREIGN KEY 
(`shift`) REFERENCES `shifttype` (`shift`),  CONSTRAINT `shift_ibfk_3` FOREIGN 
KEY (`client`) REFERENCES `client` (`uniquename`),  CONSTRAINT `shift_ibfk_4` 
FOREIGN KEY (`staff`) REFERENCES `staff` (`uniquename`),  CONSTRAINT 
`shift_ibfk_5` FOREIGN KEY (`role`) REFERENCES `roletype` (`role`)) TYPE=InnoDB;

Fw: mysqldump generates invalid code

2005-04-30 Thread [EMAIL PROTECTED]


Thank you for your help.  I really appreciate it.

I created the dump with --quote-names option, but this does not solve the 
problem unfortunately.  I still get error:

C:\xampp\xampp\mysql\bin>mysql -uroot db2 < test5.txt
ERROR 1005 at line 54: Can't create table '.\db2\shift.frm' (errno: 150)
C:\xampp\xampp\mysql\bin>

Line 54 is the CREATE TABLE... line of the following bit, and the original 
database 
works fine.

I want to upgarde, but unfortuantely my employer refuses to allow upgrade or 
to buy a fully supported database server.  Is there perhaps a known defect 
recorded for this, which I could use as leverage.

Andrew H

CREATE TABLE `shift` (
  `staff` char(20) NOT NULL default '',
  `shift` char(1) NOT NULL default '',
  `client` char(20) default NULL,
  `role` char(1) default NULL,
  `on` date NOT NULL default '-00-00',
  PRIMARY KEY  (`staff`,`shift`,`on`),
  KEY `staff` (`staff`,`role`),
  KEY `shift` (`shift`),
  KEY `client` (`client`),
  KEY `role` (`role`),
  CONSTRAINT `shift_ibfk_1` FOREIGN KEY (`staff`, `role`) REFERENCES 
`staffrole` 
(`uniquename`, `role`),
  CONSTRAINT `shift_ibfk_2` FOREIGN KEY (`shift`) REFERENCES `shifttype` 
(`shift`),
  CONSTRAINT `shift_ibfk_3` FOREIGN KEY (`client`) REFERENCES `client` 
(`uniquename`),
  CONSTRAINT `shift_ibfk_4` FOREIGN KEY (`staff`) REFERENCES `staff` 
(`uniquename`),
  CONSTRAINT `shift_ibfk_5` FOREIGN KEY (`role`) REFERENCES `roletype` 
(`role`)
) TYPE=InnoDB;


> Original Message
> From: [EMAIL PROTECTED]
> Date: 30/04/2005 13:52
> To: mysql@lists.mysql.com
> Subj: Re: mysqldump generates invalid code
> 
> Hello.
> 
> Make the dump using --quote-names option for mysqldump. Does it solve 
> the problem? You have an old MySQL version. I strongly recommend you 
to
> upgrade.
> 
> 
> 
> 
> >I backup a db using mysqldump, but when I come to execute the sql file
> >on an empty db I
> >get this
> >C:\xampp\xampp\mysql\bin>mysql -uroot db2 < test3.txtERROR 1005 at
> >line 54:
> >Can't create table '.\db2\shift.frm' (errno: 150)
> >C:\xampp\xampp\mysql\bin>
> >I have tried to fix the foreign key constraint (error 150), but to no
> >avail, I cannot see
> >what is wrong with it nd anyway if I created this in the same mysql,
> >then why doesn't it
> >generate code that is valid?
> >Here's the create that it fails on:
> >CREATE TABLE shift (  staff char(20) NOT NULL default '',  shift char(1)
> >NOT NULL default
> >'',  client char(20) default NULL,  role char(1) default NULL,  on date
> >NOT NULL default
> >'-00-00',  PRIMARY KEY  (staff,shift,on),  KEY staff (staff,role),
> >KEY shift
> >(shift),  KEY client (client),  KEY role (role),  CONSTRAINT
> >`shift_ibfk_1` FOREIGN KEY
> >(`staff`, `role`) REFERENCES `staffrole` (`uniquename`, `role`),
> >CONSTRAINT
> >`shift_ibfk_2` FOREIGN KEY (`shift`) REFERENCES `shifttype` (`shift`),
> >CONSTRAINT
> >`shift_ibfk_3` FOREIGN KEY (`client`) REFERENCES `client`
> >(`uniquename`),  CONSTRAINT
> >`shift_ibfk_4` FOREIGN KEY (`staff`) REFERENCES `staff` (`uniquename`),
> >CONSTRAINT
> >`shift_ibfk_5` FOREIGN KEY (`role`) REFERENCES `roletype` (`role`))
> >TYPE=InnoDB;
> >I've tried back ticks on all the `on` column references, but still get
> >same error.
> >I have MySQL 4.0.18 and the mysqldump is :mysqldump  Ver 9.10 Distrib
> >4.0.17, for
> >Win95/Win98 Is this the problem?  If so where do I get the right version
> >of mysqldump? 
> >Later ones may not be bkwds compat.
> >Can any-one help?
> >Andrew H
> >
> >"[EMAIL PROTECTED]" <[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]
net
> 



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

mysqldump generates invalid code

2005-04-29 Thread [EMAIL PROTECTED]

I backup a db using mysqldump, but when I come to execute the sql file on an 
empty db I get this
C:\xampp\xampp\mysql\bin>mysql -uroot db2 < test3.txtERROR 1005 at line 
54: Can't create table '.\db2\shift.frm' (errno: 150)
C:\xampp\xampp\mysql\bin>
I have tried to fix the foreign key constraint (error 150), but to no avail, I 
cannot see what is wrong with it nd anyway if I created this in the same mysql, 
then why doesn't it generate code that is valid?
Here's the create that it fails on:
CREATE TABLE shift (  staff char(20) NOT NULL default '',  shift char(1) NOT 
NULL default '',  client char(20) default NULL,  role char(1) default NULL,  on 
date NOT NULL default '-00-00',  PRIMARY KEY  (staff,shift,on),  KEY staff 
(staff,role),  KEY shift (shift),  KEY client (client),  KEY role (role),  
CONSTRAINT `shift_ibfk_1` FOREIGN KEY (`staff`, `role`) REFERENCES `staffrole` 
(`uniquename`, `role`),  CONSTRAINT `shift_ibfk_2` FOREIGN KEY (`shift`) 
REFERENCES `shifttype` (`shift`),  CONSTRAINT `shift_ibfk_3` FOREIGN KEY 
(`client`) REFERENCES `client` (`uniquename`),  CONSTRAINT `shift_ibfk_4` 
FOREIGN KEY (`staff`) REFERENCES `staff` (`uniquename`),  CONSTRAINT 
`shift_ibfk_5` FOREIGN KEY (`role`) REFERENCES `roletype` (`role`)) TYPE=InnoDB;
I've tried back ticks on all the `on` column references, but still get same 
error.
I have MySQL 4.0.18 and the mysqldump is :mysqldump  Ver 9.10 Distrib 4.0.17, 
for Win95/Win98 Is this the problem?  If so where do I get the right version of 
mysqldump?  Later ones may not be bkwds compat.
Can any-one help?
Andrew H

RE: Newbie Q - Re: Copying databases

2005-04-29 Thread [EMAIL PROTECTED]

Yes I had thought I should use tools to copy, but I have had problems with 
mysqldump.  I successfully created a dump file, but when I execute this file on 
an empty database I got:
C:\xampp\xampp\mysql\bin>mysql -uroot db2 < test1.txtERROR 1064 at line 
54: You have an error in your SQL syntax.  Check the manual that corresponds to 
your MySQL server version for the right syntax to use near 'on date NOT NULL 
default '-00-00',  PRIMARY KEY  (staff,shrefering I presume to 
CREATE TABLE shift (  staff char(20) NOT NULL default '',  shift char(1) NOT 
NULL default '',  client char(20) default NULL,  role char(1) default NULL,  on 
date NOT NULL default '-00-00',  PRIMARY KEY  (staff,shift,on),... etc
Could it be that the version of mysqldump that has been installed is not in 
step with the server or are there some issues with SQL generated by mysqldump.  
I have MySQL 4.0.18 and the mysqldump is :
mysqldump  Ver 9.10 Distrib 4.0.17, for Win95/Win98 (i32)By Igor Romanenko, 
Monty, Jani & Sinisa
This is not an installation I have control over.  Soryy to be a pain, any ideas?
Andrew H

Newbie Q - Re: Copying databases

2005-04-29 Thread [EMAIL PROTECTED]

I am having to operating MySQL 4.0.18, I had heared that I could copy a db 
folder out of the mysql/data folder to the same folder on a different machine 
running its own local host server and that server would have a copy of the 
database.  HOWEVER, o some occasions this seemed to work Ok, and on others I 
could use db, show tables etc, but on select I got a failure (which 
unfortunately I have not copied sorry) something to do with finding? 
.innodb.
Now as far as I know both servers were at the same version and both had innodb 
enabled and I THNK I quit both clients before copying.
Is this a valid way to copy a db or only sometimes.  Should I really always use 
mysqldump.  Although I am suspecting given the amount of data this may make for 
a v large sql file?
Can some-one advise this newbie,  thanks,
Andrew H

Unix time as year in select query -nooby

2005-04-13 Thread [EMAIL PROTECTED]

MySql vers 4.0.20
A table "noticeboard" has three test entries.
A unix time from date("U") is stored in the field "published" of type, 
bigint20.

I am not able to workout how to select  the year.  Assuming that the 
value from date("U") can be treated as a unixtime value, my latest 
unsuccessful effort is 

$yearslist = mysql_query("SELECT published, 
FROM_UNIXTIME(UNIX_TIMESTAMP(published),'%Y') AS year FROM noticeboard") 
or die("Cannot get list of years." . mysql_error());

$counttotal = mysql_num_rows($yearslist);
echo "Rows found $counttotal";
while ($myrow = mysql_fetch_array($yearslist)) {
if (ISSET($myrow['published'])){$published=$myrow['published'];}
$yearpub=date("Y",$published);
if (ISSET($myrow['year'])){
$year=$myrow['year'];}
else {echo "Year is not set";}
echo "Year $year but value was $published.  Year value should be 
$yearpub";

}
The result is
Rows found 3
Year 1969 but value was 1083923875. Year value should be 2004
Year 1969 but value was 1113300220. Year value should be 2005
Year 1969 but value was 1113351870. Year value should be 2005
Louise

Re: FW: GWAVA Sender Notification (Spam)

2005-04-08 Thread l&#x27;[EMAIL PROTECTED]

Yes I received the same email about being a spammer.
Somebody on the list told me that our email headers get rewritten and 
sometimes you get this email about being a spammer.

Laurie
At 02:29 PM 4/8/2005, Keith Ivey wrote:
[EMAIL PROTECTED] wrote:
This is what I call WAY OVER REACTING.
*This member turned my email into the spam report immediately without 
thinking.  Now I am recieving these.*
I doubt that there's any connection.  It's impossible to tell for sure, 
but the message was probably rejected because of the capital letters and 
multiple exclamation points in the subject line -- which means the person 
who posted the original complaining message probably got the same sort of 
bounce.

--
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
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]

WARNING!!!! abuser on this list?

2005-04-08 Thread l&#x27;[EMAIL PROTECTED]

When I opened the  message send by:
[EMAIL PROTECTED]>
subject: Boolean searches
a pop up box appeared stating
"Connect to 70.84.29.164"
Web host manager
Username 
password:

Has anybody encountered this problem when you cliked on his email
Should we FLAME that guy?
In the mean time I am sending an email to [EMAIL PROTECTED] owning that 
IP address.

Here is the headers of his email:
Received: (qmail 7362 invoked by uid 109); 8 Apr 2005 17:13:25 -
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
List-ID: 
Precedence: bulk
List-Help: <mailto:[EMAIL PROTECTED]>
List-Unsubscribe: <mailto:[EMAIL PROTECTED]>
List-Post: <mailto:mysql@lists.mysql.com>
List-Archive: http://lists.mysql.com/mysql/182353
Delivered-To: mailing list mysql@lists.mysql.com
Received: (qmail 7312 invoked from network); 8 Apr 2005 17:13:24 -
Received-SPF: pass (lists.mysql.com: local policy)
Reply-To: <[EMAIL PROTECTED]>
From: <[EMAIL PROTECTED]>
To: "Mysql" 
Subject: Boolean searches
Date: Fri, 8 Apr 2005 11:13:13 -0600
Message-ID: <[EMAIL PROTECTED]>
MIME-Version: 1.0
Content-Type: multipart/related;
boundary="=_NextPart_000_0098_01C53C2B.F4751020"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0)
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-PopBeforeSMTPSenders: 
[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],gunmuse,[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],redmoon,[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED]
X-AntiAbuse: This header was added to track abuse, please include it with 
any abuse report
X-AntiAbuse: Primary Hostname - pistol.gunmuse.us
X-AntiAbuse: Original Domain - lists.mysql.com
X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12]
X-AntiAbuse: Sender Address Domain - gunmuse.com
X-Source:
X-Source-Args:
X-Source-Dir:
X-Virus-Checked: Checked
X-Spam-Checker-Version: SpamAssassin 3.0.2 (2004-11-16) on c.spam
X-Spam-Status: No, score=1.1 required=5.0 tests=DNS_FROM_AHBL_RHSBL,
HTML_MESSAGE,HTML_TAG_EXIST_TBODY,NORMAL_HTTP_TO_IP,NO_REAL_NAME,
WEIRD_PORT autolearn=disabled version=3.0.2
X-Spam-Level: *


thanks
Laurie

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

Re: FULLTEXT index on InnoDB tables (looking for a workaround)

2005-01-28 Thread [EMAIL PROTECTED]

Thanks for the extremely useful answer.

Some comments, questions here below.

On Friday 28 Jan 2005 16:38, you wrote:
> No, only the BLOB/TEXT columns need to be moved to MyISAM.

Yes, I thought of doing so. The drawback is that you de-normalise the 
database. Is that correct?

There are 2 alternatives we should then consider:
- extract the two important columns, put them in a separate MyISAM table, 
index the separate table, operate searches only on the separate table. Like 
having a "view", but permanent.
- separate the original table, in two tables (columns which need to be 
indexed, columns which do not need to be indexed), operate the search only on 
the table with the relevant columns. I do not like it too much, because it 
spoils the structure of the database.

From a logical point of view, the former is better. Furthermore, there is 
rollback. (By the way, how do you solve the rollback problem?)

The latter consumes less disk space, performance wise is better.

Do you see any other drawback / advantage?


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

MySQL 4.1.7 Bug?

2004-12-02 Thread [EMAIL PROTECTED]


Hi,

We have done the following test, which boils down to a simple connect and then 
a select * from accounts; (accounts=MyISAM, 3 records, 3 columns).

Clientside: A stable win32 app capable of using any libmysql.dll
Serverside: 4.1.7-nt win32 MySQL service (stable download), with vanilla setup
Location: Client and Server running on same win32 machine (XP/SP2)
Communication: TCP/IP
Username/Password: root/ (full privileges)

Test A: 
1. Setup client with any 3.23.x-4.1.0 libmysql.dll (as provided with 
distribution)
2. Start MySQL
3. Connect & Select
4. Result = OK
5. Disconnect, now use any >4.1.1 libmysql.dll
6. Connect & Select
7. Result = columnnames screwy, i.e. contains illegible characters
[8. Optionally switch back and forth between dlls, result is the same, i.e. 
connecting with any 4.1.1+ dll gives odd results while anything <4.1.0 works 
100%)
9. Shutdown MySQL

Test B (Inverse sequence): 
1. Setup client with >4.1.1 libmysql.dll (as provided with distribution)
2. Restart MySQL
3. Connect & Select
4. Result = OK
5. Disconnect, now use 3.23.x-4.1.0 libmysql.dll
6. Connect & Select
7. Result = columnnames screwy, i.e. contains illegible characters
[8. Optionally switch back and forth between dlls, result is the same, i.e. 
connecting with any <4.1.0 dll gives odd results while anything 4.1.1+ works 
100%)
9. Shutdown MySQL


Conclusion: MySQL 4.1.7 sets itself up for either an old or new style 
connection (i.e. CLIENT_PROTOCOL_41 in client/server capabilities) depending on 
the version of the first client which connects to it after it initially fires 
up. After which the other type (i.e. old or new) can't communicate with it 
properly.  Initial connections are fine, but pulling any results, result in 
weird columnnames.

We have done exactly the same tests (i.e. A and B) with other versions of 
MySQL, ex. 3.23.42, 4.0.20, 4.1.4 and even 5.0.0a all of which returned the 
correct results independent of the client version connecting first after it 
starts up, and always returns the correct results and columnames.  In all tests 
the client app was exactly the same, no different settings, only the MySQL AB 
client dll was exchanged.

We would appreciate any feedback as to if this is in fact a bug, or some MySQL 
setting causing this behavior or if this is something specific to our MySQL 
4.1.7 installation (i.e. if no one else experience these symptoms with their 
4.1.7 MySQL).

Regards.


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

Which Filesystem to choose?

2004-10-18 Thread [EMAIL PROTECTED]

Hi,

I'm trying to set up a new Opteron-Based MySQL-Server.
The only thing I'm unsure about is which filesystem to choose..

ext3? ReiserFS? XFS?
What's your experience?

Thanks 

chris

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

Replication & Index

2004-10-11 Thread [EMAIL PROTECTED]

Hi,

just wanted to know if I can set other indexes on one of my replication 
servers that are not on the master server?
This would be a great performance benefit if I use this server for 
administrative purpose..

Thanks,

chris

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

Re: i cant connect to mysql from other machine

2004-07-20 Thread [EMAIL PROTECTED]

(excuse for my english)

thx for your interest Lachan..

the problem was the attribute bind_address at the my.cnf

:)

thanks
d2clon


On Tuesday 20 July 2004 02:59, Lachlan Mulcahy wrote:
> d2,
>
> By the looks of it the client machine you are trying to connect from can
> resolve the hostname of your 'linuxready' machine perfecetly fine... and
> your host (according to nmap) is running the MySQL server daemon listening
> on TCP port 3306. I would suggest the problem lies at the network level.
>
> It may be possible that your distribution by default sets up a firewall
> that blocks out external MySQL access (this is no an uncommon default).
>
> What linux distribution are you running on the host (linuxready)?
>
> Also what kind of network lies between the two machines. Are they simply
> two machines plugging into the same switch/hub? Are they connected via
> crossover cable?
>
> The connection refused message suggests a firewall somewhere between the
> two machines, I would guess it's most likely running on your host and you
> don't know it.
>
> Lachlan
>
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, 20 July 2004 2:18 AM
> To: Victor Pendleton
> Cc: '[EMAIL PROTECTED] '; '[EMAIL PROTECTED] '
> Subject: Re: i cant connect to mysql from other machine
>
>
> (first: excuse for my english)
>
> On Monday 19 July 2004 15:03, Victor Pendleton wrote:
> > Can you telnet port 3306 on linuxready from the remote machine?
>
> nop.. :/ .. i cant do telnet to linuxready 3306 ¡
> -
> [EMAIL PROTECTED] $ telnet linuxready 3306
> Trying 10.205.171.16...
> telnet: Unable to connect to remote host: Connection refused
> -
>
>
> but i have visibility to linuxready.. i can open a ssh session, or ftp
> session..
>
> i have not configure any firewall.
>
> why i cant do telnet to linuxready 3306?
> i have opened the port:
> --
> linuxready root # nmap localhost
>
> Starting nmap 3.50 ( http://www.insecure.org/nmap/ ) at 2004-07-19 18:24
> CEST
> Interesting ports on localhost (127.0.0.1):
> (The 1654 ports scanned but not shown below are in state: closed)
> PORT STATE SERVICE
> 22/tcp   open  ssh
> 80/tcp   open  http
> 3306/tcp open  mysql
> 8009/tcp open  ajp13
> 8080/tcp open  http-proxy
> ---
>
> thanks
> d2clon
>
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > To: [EMAIL PROTECTED]
> > Sent: 7/19/04 4:27 AM
> > Subject: i cant connect to mysql from other machine
> >
> > hello people..
> >
> > i have a curious problem with mysql.
> > i configure root user acount to enable access from other machines but i
> > cant
> > connect..
> >
> > my user table is:
> > mysql> select * from user;
> > +---+--+--+-+-+-
> > +-+-+---+-+-
> > --+--+---++-+---
> > -++
> >
> > | Host  | User | Password | Select_priv | Insert_priv |
> >
> > Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv |
> > Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv
> >
> > Index_priv | Alter_priv |
> > +---+--+--+-+-+-
> > +-+-+---+-+-
> > --+--+---++-+---
> > -++
> >
> > | localhost | root | 1595fd346d9734fe | Y   | Y   | Y
> > |
> > | Y   | Y   | Y | Y   | Y |
> >
> > Y
> >
> > | Y | Y  | Y   | Y  | Y  |
> > | % | root | 1595fd346d9734fe | Y   | Y   | Y
> > |
> > | Y   | Y   | Y | Y   | Y |
> >
> > Y
> >
> > | Y | N  | Y   | Y  | Y  |
> >
> > +---+--+--+-+-+-
> > +-+-+---+-+-
> > --+--+---++-+---
> > -+----+
> > 2 rows in set (0.00 sec)
>

Re: i cant connect to mysql from other machine (OK¡¡)

2004-07-19 Thread [EMAIL PROTECTED]

On Monday 19 July 2004 15:17, Giulio wrote:
> I believe you shoul comment out the bind_address option on config
> file...
>

:) 

ok¡

all is right now¡

a lot of thanks

> Hope this helps,
>
>Giulio
>

d2clon


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

Re: i cant connect to mysql from other machine

2004-07-19 Thread [EMAIL PROTECTED]

(first: excuse for my english)

On Monday 19 July 2004 15:03, Victor Pendleton wrote:
> Can you telnet port 3306 on linuxready from the remote machine?

nop.. :/ .. i cant do telnet to linuxready 3306 ¡
-
[EMAIL PROTECTED] $ telnet linuxready 3306
Trying 10.205.171.16...
telnet: Unable to connect to remote host: Connection refused
-


but i have visibility to linuxready.. i can open a ssh session, or ftp 
session.. 

i have not configure any firewall.

why i cant do telnet to linuxready 3306?
i have opened the port:
--
linuxready root # nmap localhost

Starting nmap 3.50 ( http://www.insecure.org/nmap/ ) at 2004-07-19 18:24 CEST
Interesting ports on localhost (127.0.0.1):
(The 1654 ports scanned but not shown below are in state: closed)
PORT STATE SERVICE
22/tcp   open  ssh
80/tcp   open  http
3306/tcp open  mysql
8009/tcp open  ajp13
8080/tcp open  http-proxy
---

thanks
d2clon




>
> -Original Message-
> From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]
> Sent: 7/19/04 4:27 AM
> Subject: i cant connect to mysql from other machine
>
> hello people..
>
> i have a curious problem with mysql.
> i configure root user acount to enable access from other machines but i
> cant
> connect..
>
> my user table is:
> mysql> select * from user;
> +---+--+--+-+-+-
> +-+-+---+-+-
> --+--+---++-+---
> -++
>
> | Host  | User | Password | Select_priv | Insert_priv |
>
> Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv |
> Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv
>
> Index_priv | Alter_priv |
> +---+--+--+-+-+-
> +-+-+---+-+-
> --+--+---++-+---
> -++
>
> | localhost | root | 1595fd346d9734fe | Y   | Y   | Y
> |
> | Y   | Y   | Y | Y   | Y |
>
> Y
>
> | Y | Y  | Y   | Y  | Y  |
> | % | root | 1595fd346d9734fe | Y   | Y   | Y
> |
> | Y   | Y   | Y | Y   | Y |
>
> Y
>
> | Y | N  | Y   | Y  | Y  |
>
> +---+--+--+-+-+-
> +-+-+---+-+-
> --+--+---++-+---
> -++
> 2 rows in set (0.00 sec)
>
>
> i execute the flush privileges sentence.
>
> i try to connect from another machine and this is the error:
> $ mysql --host linuxready -uroot -p
> Enter password:
> ERROR 2003: Can't connect to MySQL server on 'linuxready' (111)
>
>
> Maybe is it possible that I have to configure some attribute in my.cnf?
>
> thanks in advance
> d2clon


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

i cant connect to mysql from other machine

2004-07-19 Thread [EMAIL PROTECTED]

hello people.. 

i have a curious problem with mysql.
i configure root user acount to enable access from other machines but i cant 
connect.. 

my user table is:
mysql> select * from user;
+---+--+--+-+-+-+-+-+---+-+---+--+---++-+++
| Host  | User | Password | Select_priv | Insert_priv | 
Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | 
Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | 
Index_priv | Alter_priv |
+---+--+--+-+-+-+-+-+---+-+---+--+---++-+++
| localhost | root | 1595fd346d9734fe | Y   | Y   | Y   
| Y   | Y   | Y | Y   | Y | Y
| Y | Y  | Y   | Y  | Y  |
| % | root | 1595fd346d9734fe | Y   | Y   | Y   
| Y   | Y   | Y | Y   | Y | Y
| Y | N  | Y   | Y  | Y  |
+---+--+--+-+-+-+-+-+---+-+---+--+---++-+++
2 rows in set (0.00 sec)


i execute the flush privileges sentence.

i try to connect from another machine and this is the error:
$ mysql --host linuxready -uroot -p
Enter password: 
ERROR 2003: Can't connect to MySQL server on 'linuxready' (111)


Maybe is it possible that I have to configure some attribute in my.cnf?

thanks in advance
d2clon


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

how to write a query to return records has the closest date to end of month

2004-07-02 Thread [EMAIL PROTECTED]


Hi, guys

I would like to write a query to return all records which has the closest date to the 
end of month. (Assume that date will be 6/30/2004)

| id | product_id |  price | snapshot_date |
++++---+
|  1 |  1 |  99.95 |2004-06-23 |
|  2 |  1 |  99.74 |2004-06-27 | *
|  3 |  2 | 101.52 |2004-06-25 |
|  4 |  2 | 101.85 |2004-07-01 | *
|  5 |  3 | 100.00 |2004-06-29 |
|  6 |  3 | 100.50 |2004-07-01 | *
++++---+

*if there is a tie, it always pick the one after the end of month 
The 2, 4, 6 entries shall be return by the query.

Is this possible to write this in pure sql?

Thanks in advance.

Lam


The best thing to hit the Internet in years - NetZero HiSpeed!
Surf the Web up to FIVE TIMES FASTER!
Only $14.95/ month -visit www.netzero.com to sign up today!

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

error replicating from mysql3.23.36 to mysql4.0.18

2004-06-28 Thread [EMAIL PROTECTED]

(excuse for my english)

all proccess is ok.. the replication starts.. but, a few minutes later, mysql 
shows me this error:
Last_error: Error 'Table 'eshablar_new.contadorZs' doesn't exist' on query 
'UPDATE contadorZs set indCont = indCont + 1 where idUser = 2394'. Default 
database: 'eshablar_new'

the problem is that the 'contadorZs' table has been created by mysqldump as 
'contadorzs' name.. and all querys that reference this table as 'contadorZs' 
chrash...

in the mysql3.23.36 the table names are non-case-sensitive .. and in 
mysql4.0.18 the names of tables are case-sensitive.. 

this is getting me into a lot of troubles..

for example:

look at this in a 3.23.36 version:

=0
mysql> create table pepePepe( id int );
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
++
| Tables_in_test |
++
| pepepepe   |
++
1 row in set (0.00 sec)
=0

look that the name wich i create the table is with capital 'P'.. but the 'show 
tables' show me with non-capital 'p' .. 

now .. the same in a mysql 4.0.18:

=0
mysql> create table pepePepe( id int );
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
++
| Tables_in_test |
++
| pepePepe   |
++
1 row in set (0.00 sec)
=0

then.. i dont know.. i dont know if i have a unsolutionable problem.. or 
something like that.. 

i only wanted to comment this.. :D

thanks
d2clon


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

Re: innodb and backup

2004-06-25 Thread [EMAIL PROTECTED]


i have been reading..
and i have found 3 ways to have a copy of mysql master datas for initilize the 
mysql slave 
( always having in mind innodb compatibility )

1) 
FLUSH TABLES WITH READ LOCK
see the 'SHOW MASTER STATUS' sentence
shutdown master database
copy all data, log and .frm files
RESTART SERVER

2) 
FLUSH TABLES WITH READ LOCK
see the 'SHOW MASTER STATUS' sentence
shutdown master database
use 'mysqldump ...' command
RESTART SERVER

3)
use 'LOAD DATA FROM MASTER' sentence
( i havent studied this option )


i prefer option number 2..

but my question is:
is it possible to do a 'mysqldump ..' ( remember innodb ) without shutdowning 
the server?

thanks
d2clon


On Thursday 24 June 2004 15:22, Kieran Kelleher wrote:
> The simplest thing to do is set up a replication slave and run a script
> every night that shuts down the MySQL slave, performs the backup, and
> then restarts MySQL. We have a dedicated XServe running MySQL and we
> use another XServe whose primary function is filesharing to also act as
> a replication slave. Each night a system cron script stops MySQL, backs
> up the slave XServe, and then restarts MySQL
>
> On Jun 24, 2004, at 7:01 AM, [EMAIL PROTECTED] wrote:
> > thx thx :D
> >
> > thats work right.
> >
> > the only problem is that mysqldump dont write the 'set
> > FOREIGN_KEY_CHECKS'
> > sentences until version 4.1.1:
> >
> > "To make it easier to reload dump files for tables that have foreign
> > key
> > relationships, mysqldump automatically includes a statement in the dump
> > output to set FOREIGN_KEY_CHECKS to 0 as of MySQL 4.1.1."
> >
> > :).. but dont worry i write the sentences by hand.
> >
> > thx
> > d2clon
> >
> > On Thursday 24 June 2004 11:44, Egor Egorov wrote:
> >> "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> >>> i have read multiple websites and posts.. and this mail list archive
> >>> but i have not found the answer to the question:
> >>>
> >>> how is it possible to do a backup of a innodb table?
> >>>
> >>>
> >>>
> >>> i have read: "try to use mysqldump.." but this does not work right
> >>> i have read: "try to use mysqldump --single-transaction ..." but i
> >>> dont
> >>> see any difference.. and doesnt work right too
> >>> i have read: "use this: http://www.innodb.com/order.php .." but it
> >>> isnt
> >>> free :/
> >>>
> >>>
> >>> the problem is not how to do the backup.. but recovering from it..
> >>> because it complains about the foreign keys.
> >>
> >> How to restore tables that have foreign key relationships is
> >> described at:
> >>http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
> >>
> >> You should set FOREIGN_KEY_CHECKS to 0 before restoring tables.
> >>
> >>> and then i think:
> >>> how is it possible that i cant do backup, and recover it, of my
> >>> innodb
> >>> tables?
> >>
> >> --
> >> For technical support contracts, goto
> >> https://order.mysql.com/?ref=ensita
> >> This email is sponsored by Ensita.net http://www.ensita.net/
> >>__  ___ ___   __
> >>   /  |/  /_ __/ __/ __ \/ /Egor Egorov
> >>  / /|_/ / // /\ \/ /_/ / /__   [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]


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

Re: innodb and backup

2004-06-24 Thread [EMAIL PROTECTED]

that is just what we want to do..

but it isnt "The simplest thing to do" as you say 

:P

i am studying this:
http://dev.mysql.com/doc/mysql/en/Replication_HOWTO.html

thx another time
d2clon

On Thursday 24 June 2004 15:22, Kieran Kelleher wrote:
> The simplest thing to do is set up a replication slave and run a script
> every night that shuts down the MySQL slave, performs the backup, and
> then restarts MySQL. We have a dedicated XServe running MySQL and we
> use another XServe whose primary function is filesharing to also act as
> a replication slave. Each night a system cron script stops MySQL, backs
> up the slave XServe, and then restarts MySQL
>
> On Jun 24, 2004, at 7:01 AM, [EMAIL PROTECTED] wrote:
> > thx thx :D
> >
> > thats work right.
> >
> > the only problem is that mysqldump dont write the 'set
> > FOREIGN_KEY_CHECKS'
> > sentences until version 4.1.1:
> >
> > "To make it easier to reload dump files for tables that have foreign
> > key
> > relationships, mysqldump automatically includes a statement in the dump
> > output to set FOREIGN_KEY_CHECKS to 0 as of MySQL 4.1.1."
> >
> > :).. but dont worry i write the sentences by hand.
> >
> > thx
> > d2clon
> >
> > On Thursday 24 June 2004 11:44, Egor Egorov wrote:
> >> "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> >>> i have read multiple websites and posts.. and this mail list archive
> >>> but i have not found the answer to the question:
> >>>
> >>> how is it possible to do a backup of a innodb table?
> >>>
> >>>
> >>>
> >>> i have read: "try to use mysqldump.." but this does not work right
> >>> i have read: "try to use mysqldump --single-transaction ..." but i
> >>> dont
> >>> see any difference.. and doesnt work right too
> >>> i have read: "use this: http://www.innodb.com/order.php .." but it
> >>> isnt
> >>> free :/
> >>>
> >>>
> >>> the problem is not how to do the backup.. but recovering from it..
> >>> because it complains about the foreign keys.
> >>
> >> How to restore tables that have foreign key relationships is
> >> described at:
> >>http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
> >>
> >> You should set FOREIGN_KEY_CHECKS to 0 before restoring tables.
> >>
> >>> and then i think:
> >>> how is it possible that i cant do backup, and recover it, of my
> >>> innodb
> >>> tables?
> >>
> >> --
> >> For technical support contracts, goto
> >> https://order.mysql.com/?ref=ensita
> >> This email is sponsored by Ensita.net http://www.ensita.net/
> >>__  ___ ___   __
> >>   /  |/  /_ __/ __/ __ \/ /Egor Egorov
> >>  / /|_/ / // /\ \/ /_/ / /__   [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]


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

Re: innodb and backup

2004-06-24 Thread [EMAIL PROTECTED]


thx thx :D

thats work right.

the only problem is that mysqldump dont write the 'set FOREIGN_KEY_CHECKS' 
sentences until version 4.1.1:

"To make it easier to reload dump files for tables that have foreign key 
relationships, mysqldump automatically includes a statement in the dump 
output to set FOREIGN_KEY_CHECKS to 0 as of MySQL 4.1.1."

:).. but dont worry i write the sentences by hand.

thx
d2clon


On Thursday 24 June 2004 11:44, Egor Egorov wrote:
> "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> > i have read multiple websites and posts.. and this mail list archive
> > but i have not found the answer to the question:
> >
> > how is it possible to do a backup of a innodb table?
> >
> >
> >
> > i have read: "try to use mysqldump.." but this does not work right
> > i have read: "try to use mysqldump --single-transaction ..." but i dont
> > see any difference.. and doesnt work right too
> > i have read: "use this: http://www.innodb.com/order.php .." but it isnt
> > free :/
> >
> >
> > the problem is not how to do the backup.. but recovering from it..
> > because it complains about the foreign keys.
>
> How to restore tables that have foreign key relationships is described at:
>   http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
>
> You should set FOREIGN_KEY_CHECKS to 0 before restoring tables.
>
> > and then i think:
> > how is it possible that i cant do backup, and recover it, of my innodb
> > tables?
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.net http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Egor Egorov
>  / /|_/ / // /\ \/ /_/ / /__   [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]

innodb and backup

2004-06-24 Thread [EMAIL PROTECTED]

( first.. sorry for my english )


hello people..

i have read multiple websites and posts.. and this mail list archive 
but i have not found the answer to the question:

how is it possible to do a backup of a innodb table?



i have read: "try to use mysqldump.." but this does not work right
i have read: "try to use mysqldump --single-transaction ..." but i dont see 
any difference.. and doesnt work right too
i have read: "use this: http://www.innodb.com/order.php .." but it isnt 
free :/


the problem is not how to do the backup.. but recovering from it.. because it 
complains about the foreign keys. 

and then i think:
how is it possible that i cant do backup, and recover it, of my innodb tables?

thanks for the suggestions
d2clon


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

one on one joins

2004-06-23 Thread [EMAIL PROTECTED]

Hi list, 

I have a problem concerning two tables. Basically, I need a strict 
"one on one join". 

Simplyfied, the problem is as follows:

I need a check on two tables:

T1: containing four records, with the value of field id being 1, 2, 3, 4
T2, same structure, containing the records 1, 2, 3, 5 for field id.


I want to delete anything in table T1 which is not in T2. That is 
simple: 

delete t1 from t1 a left join t2 b
using(id)
  where
b.id is not null;

However, Now my problem:

the problem is, that the id in both tables can contain duplicates.

When the tables have the following recs:

T1: field id: 1,1,2,3,4
T2: field id: 1,2,3,5


I want the delete command to delete ONE 1, the 2 and the 3, thus 
resulting in a table T1 having left only two recrods, with id 1 and 4. 


However, the delete matches both 1-records of T1 to the single 1-
record of T2, so both of them are deleted.

So, summarizing: I need a sort of "one on one join", which joins 
only one single 1 in table T1 to a single 1 in table T2, and when 
there's no 1 record left in T2, the other 1 in T1 should be 
unmatched.

How can I do this?

Or if this is not possible, does somebody know some smart 
workaround trick?

Thanks, rinke

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

Re: INTO OUTFILE error

2004-06-02 Thread [EMAIL PROTECTED]

There's got ot be something else wrong.  I've got the exact same problem and
I have done a chmod 666 on the directory I am trying to write to with no
luck.  I am running v4.0.15

/T

on 6/1/04 7:06, Egor Egorov at [EMAIL PROTECTED] wrote:

> John Mistler <[EMAIL PROTECTED]> wrote:
>> Would there be any obvious reasons for its inability to access the desktop?
>> I have used that desktop directory many times before in UNIX.
>> 
> 
> Check permissions on that dir. It must be writeable by the user you run mysqld
> as ('mysql' by default).
> 
>> 
>> on 6/1/04 12:49 AM, Egor Egorov at [EMAIL PROTECTED] wrote:
>> 
>>> John Mistler <[EMAIL PROTECTED]> wrote:
>>>> Can anyone guess why I am getting this error:
>>>> 
>>>> "ERROR 1: Can't create/write to file
>>>> '/Users/johnmistler/Desktop/TestFile.txt' (ERRCODE 13)"
>>>> 
>>>> when using this statement:
>>>> 
>>>> mysql> SELECT * INTO OUTFILE '/Users/johnmistler/Desktop/TestFile.txt' FROM
>>>> theTable WHERE column1 != 'thisString';
>>>> 
>>>> ?  I am logging in as root for the test.
>>>> 
>>> 
>>> It means that MySQL server can not access to the /Users/johnmistler/Desktop/
>>> directory.
>>> 
>>> 
>> 
>> 
> 
> 



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

Error on Outfile

2004-06-01 Thread [EMAIL PROTECTED]

input:
select firstname,middlename,lastname,county,state,dob,ssn into outfile
'/Users/user/temp/test.csv' fields terminated by ',' lines terminated by
'\n' from iffinet.subjects where client_id='1' and ostatus='complete';

output:
ERROR 1: Can't create/write to file '/Users/timbest/test.csv' (Errcode: 13)

I¹ve granted full rights to anyone who accesses /Users/user/temp.  Any ideas
as to what I can do to fix this?

VR/Tim
Best IT
cell: 504-231-1084
fax: 206-338-6162
[EMAIL PROTECTED]
http://www.best-it.biz

[Re: Re: set wait_timeout]

2004-05-21 Thread [EMAIL PROTECTED]

> If you check wait_timeout value using mysql command-line client, it
> takes value for 'wait_timeout' from 'interactive_timeout' variable
> which is 28800 by default.
thanks, now i see.
this behaviour is rather strange, isn't it? why are there two different 
variables then? i thought that wait_timeout is for non-interactive and 
interactive_timeout for interactive clients.

when i run:
mysql -e 'show variables;'
then i get right value (=30), as i set it on startup.
when i start mysql command line client and run:
show variables;
then i get value for interactive_timeout (it's default =28800).
} this is what you pointed out.
but when i run:
mysql -e 'set wait_timeout=26600;'
mysql -e 'show variables;'
then i get old values:
wait_timeout=30
interactive_timeout=28800
this behaviour is quite odd and i don't see a way how to set 
wait_timeout for non-interactive clients from interactive session.
(i suppose that mysql command line client is non-interactive when it 
runs with the "-e" parameter)

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

set wait_timeout

2004-05-20 Thread [EMAIL PROTECTED]

hi,
i have problem with automatic setting of variable wait_timeout.
mysql version 4.0.20 (and 4.0.18) on slackware linux.
i've tried to set it in several ways:
1) in startup script as a parameter to mysql_safe:
-O wait_timeout=30
2a) in configuration file /etc/my.cnf, section [mysqld]:
set-variable=wait_timeout=30
2b) in configuration file /etc/my.cnf, section [mysqld]:
wait_timeout=30
3) from the shell (i've planed to place it into the startup script after 
mysqld_safe start):
mysql -e 'set wait_timeout=30'

nothing worked, i got no error message and the value of wait_timeout 
stood default: 28800.
when i run 'set wait_timeout=30;' as privileged user in interactive 
mysql session (running 'mysql'), the value changes as i wish.
what should i do to set the variable at startup?

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

mysqld-threads

2004-05-04 Thread [EMAIL PROTECTED]

Hi folks!

I have diffrent linux-machines running with the great mysqld.

But on some machines I have 20 running threads on some other only 2
threads.

If only 2 threads are running is that not bad for fast sql-results?
I have about 500 v-hosts and many sql-queries on my machine.

Does somebody know how I can add more "stand-by" threads for my mysqlds
that mysqld become faster?

Viele Gruesse,
Peter.
-- 
www: http://peter.tux.hm
www: http://tux.hm - Linux- und BSD-UserGroup im Weserbergland
gpg: http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&search=0x690A1AC2


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

Converting from MS SQL Server to MySQL

2004-04-15 Thread [EMAIL PROTECTED]

I am sure conversion questions are asked a lot, I need to move what is a MS
SQL Server database to a MySQL database, both table stuctures, data, and a
web application with a very large number of queries.

I am looking for any advice on this undertaking, things to be mindful of and
such, and if anyone knows a good site that explains more on this topic,
links would also be very appreciated.

My ultimate goal is to port it successfully so none of the queries crash
later on.

Thanks in advance



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

privileges

2004-04-10 Thread [EMAIL PROTECTED]

hi

I have redhat 7.2 running on my server with plesk
i forgot mysql password for user root
so i reset it by following way

services mysqld stop
safe_mysqld --skip-grant-tables &
mysqladmin -u root fulsh-privileges password newpassword

services mysqld restart.

now i can logon to mysql by root user

but i can not create and modify databases
it gives an error access denied for user [EMAIL PROTECTED]

i do not know how to recover root privileges

please help me






mail2web - Check your email from the web at
http://mail2web.com/ .



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

privileges

2004-04-10 Thread [EMAIL PROTECTED]




mail2web - Check your email from the web at
http://mail2web.com/ .



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

RE: group by & order by rand() problem

2004-03-31 Thread [EMAIL PROTECTED]

> Try seeding your rand.

Tried. It doesn't work. The select shows always the same records but
in different order:

SELECT * FROM banners GROUP BY user_id ORDER BY RAND();

first call
++-+---+
| id | user_id | title |
++-+---+
|  1 |   1 | first banner  |
|  3 |   2 | third banner  |
++-+---+

second call
++-+---+
| id | user_id | title |
++-+---+
|  3 |   2 | third banner  |
|  1 |   1 | first banner  |
++-+---+

etc...

Alex

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

group by & order by rand() problem

2004-03-31 Thread [EMAIL PROTECTED]

I have this table:

mysql> select * from banners;
++-+---+
| id | user_id | title |
++-+---+
|  1 |   1 | first banner  |
|  2 |   1 | second banner |
|  3 |   2 | third banner  |
|  4 |   2 | forth banner  |
|  5 |   2 | fifth banner  |
++-+---+

I would like to show a random banner for each user,
something like this:

first call
++-+---+
| id | user_id | title |
++-+---+
|  1 |   1 | first banner  |
|  3 |   2 | third banner  |
++-+---+

second call
++-+---+
| id | user_id | title |
++-+---+
|  2 |   1 | second banner |
|  4 |   2 | forth banner  |
++-+---+

etc...

I have tried with following query but the banner
doesn't change while multiple calls:

SELECT * FROM banners GROUP BY user_id ORDER BY RAND();

Can anyone help me?

Thanks in advance,

Alex

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

cleaning up large file ibdata1??

2004-02-20 Thread [EMAIL PROTECTED]

Hi List, 

I'm having quite a large data file ibdata1 in \mysql\data. The file 
occupies over 500 MB, while all of my databases at the moment 
are fairly small (recently, i deleted some old databases).

I wonder how it is possible that this file gets so large, compared to 
the space the rest of my databases take. Does it, for example, 
contain lots of old data from databases since long deleted??

And I wonder if I can clean it up one way or another; it takes quite 
some disk space.

thanks, rinke

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

Re: Can't connect to MySQL server on 'localhost' (10061)

2004-01-29 Thread [EMAIL PROTECTED]

Your password is wrong.  You probably need to read the manual that is in 
the docs directory.  There are some issues about passwords with 4.1.1.  
I don't have time to walk you through  the entire setup, but will be 
glad to help with simple questions.

Since you can connect on the server, the install should be fine.  There 
is no setup program.  You just unzip it to your hard drive. 
Use mysqlCC or some other tool on the server to change users/passwords.  
This is in the mysql.user table.  Make sure that the host column  is set 
to % and not just localhost also.

mysql test -h localhost -u username -ppasswd
	- Access denied using PW (YES)

--I would delte the my.ini file(don't know if 4.1.1 even uses that)>I don't use one.
--the reason you can connect from localhost is because a password is not set.
--search for all issues regarding passwords with 4.1.1(docs,readme's,release 
notes/known issues)
hope this helps

brian

[EMAIL PROTECTED] wrote:

OK.  Thanks. Late now, and laptop is off. Client is the mysql.exe
program.
I have to copy the mysql.exe to the laptop (or cd to the server machine
and execute from it. Leave the space out.
Right now, just fooling around from the server machine:

mysql test -h localhost 
	- works fine. 
mysql test -h localhost -u username
	- works fine
mysql test -h localhost -u username -ppasswd
	- Access denied using PW (YES)

I use the pw that is in the my.ini

Using any of the above fails to allow connection using the laptop--but
I'll try again tomorrow.
I suspect I did something wrong in the install, though everything seems
to work from the host machine.
I unzipped the the 4.1.1 catalog in winzip, read the readme that said
"run setup".  There was no setup so I simply unzipped to the \temp
directory. I ran mysqladmin, mysql, the mysqlcc (control center) and all
worked. It did not install like a 'regular' program (put itself in the
add software list in control panel), so I simply moved all the files to
a mysql directory and everything continued to work. Had to be too
simple.
Well, I'll let you know if connecting from the laptop works.

chris
    
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 28, 2004 6:40 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: Can't connect to MySQL server on 'localhost' (10061)

 

I've executed the mysql -h ip -u user -p pw
   

No space after -p.  I know it's weird, but it's the only way it will
work. So, use this: mysql -h ip -u user -ppw
 

With the result that I get asked for a PW and then told I can't 
connect.
   

You shouldn't get asked for PW if you use the above syntax correctly.

 

But, I don't have the 4.1.1 client.  Where is it?
   

If you downloaded mysql4.1.1 to your laptop, then you have it.
Otherwise: http://www.mysql.com/downloads/mysql-4.1.html
 

There is no '...client' in the bin directory.
   

By client, I mean the mysql file in the bin dir.
If you set your system path, you won't need to actually be in the bin
dir.
Otherwise you need to cd to that dir.  So the entire proc is:

1.Open command prompt.

2.cd C:\mysql\bin

3.mysql -h ip -u user -ppw

 

--
This mail composed and sent using Mozilla Thunderbird.
(http://www.mozilla.org/projects/thunderbird/)
-It's a brave, GNU world!(sorry Bill)--
http://www.linux.org
OpenSource is aimed at skilled users, not the 3l33T3 war3z crowd.
(1f U sp3ll l1k3 th1s, gu3ss wh1ch U R)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't connect to MySQL server on 'localhost' (10061)

2004-01-28 Thread [EMAIL PROTECTED]

I assume that you know the the server's ip and then try:
from DOS:
mysql -h ipaddressofserver -u user1 -ppassword1
--type this as is with your server's ip

Also you need to be running 4.1.1 client on your laptop.

N. Chris Frost wrote:

With several others, I too have this problem.

MySQL 4.1.1 installed without a problem. The sqlAdmin is running. I can
run the control center and/or mysql to do sql things.
This, though, is all from the server (a PC running Win ME).  I want to
connect from my laptop.
What do I have to do? Executing telnet to the PC won't work (probably
need the a telnet server). 

When I move to the shared directory on the PC and try to execute mysql,
I get the error.
Thanks for an assistance.

N. Chris Frost
 


--

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

Re: Can't connect to MySQL server on 'localhost' (10061)

2004-01-28 Thread [EMAIL PROTECTED]

I've executed the mysql -h ip -u user -p pw
No space after -p.  I know it's weird, but it's the only way it will work.
So, use this: mysql -h ip -u user -ppw
With the result that I get asked for a PW and then told I can't connect.
You shouldn't get asked for PW if you use the above syntax correctly.

But, I don't have the 4.1.1 client.  Where is it?
If you downloaded mysql4.1.1 to your laptop, then you have it. Otherwise:
http://www.mysql.com/downloads/mysql-4.1.html
There is no '...client' in the bin directory.
By client, I mean the mysql file in the bin dir.
If you set your system path, you won't need to actually be in the bin dir.
Otherwise you need to cd to that dir.  So the entire proc is:

1.Open command prompt.

2.cd C:\mysql\bin

3.mysql -h ip -u user -ppw

--

This mail composed and sent using Mozilla Thunderbird.
(http://www.mozilla.org/projects/thunderbird/)
-It's a brave, GNU world!(sorry Bill)-
 >>>>http://www.linux.org
OpenSource is aimed at skilled users, not the 3l33T3 war3z crowd.
(1f U sp3ll l1k3 th1s, gu3ss wh1ch U R)
:

Thanks for the reply. I've executed the mysql -h ip -u user -p pw

With the result that I get asked for a PW and then told I can't connect.

But, I don't have the 4.1.1 client.  Where is it? There is no
'...client' in the bin directory.
Thanks,

Chris

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 28, 2004 4:15 PM
To: N. Chris Frost
Cc: [EMAIL PROTECTED]
Subject: Re: Can't connect to MySQL server on 'localhost' (10061)

I assume that you know the the server's ip and then try:
from DOS:
mysql -h ipaddressofserver -u user1 -ppassword1
--type this as is with your server's ip

Also you need to be running 4.1.1 client on your laptop.

N. Chris Frost wrote:

 

With several others, I too have this problem.

MySQL 4.1.1 installed without a problem. The sqlAdmin is running. I 
can run the control center and/or mysql to do sql things.

This, though, is all from the server (a PC running Win ME).  I want to
   

 

connect from my laptop.

What do I have to do? Executing telnet to the PC won't work (probably 
need the a telnet server).

When I move to the shared directory on the PC and try to execute 
mysql, I get the error.

Thanks for an assistance.

N. Chris Frost



   

--

 

--
This mail composed and sent using Mozilla Thunderbird.
(http://www.mozilla.org/projects/thunderbird/)
-It's a brave, GNU world!(sorry Bill)--
http://www.linux.org
OpenSource is aimed at skilled users, not the 3l33T3 war3z crowd.
(1f U sp3ll l1k3 th1s, gu3ss wh1ch U R)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Database Can't Be Dropped

2004-01-22 Thread [EMAIL PROTECTED]

Quite some time ago, I started doing a book of exercises. I got some work,
put it down, and now I need to start over. So I start up MySQL, do a "DROP
DATABASE [name];". And I'm answered by:

ERROR 1008: Can't drop database '[name]'. Database doesn't exist"

So I figure, okay, then I can CREATE it, right? This time I'm answered by:

ERROR 1006: Can't create database '[name]]. (errno: 17)"

What's going on? Shouldn't be one or the other work?

Thank you.

Steve Tiano


mail2web - Check your email from the web at
http://mail2web.com/ .



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

Redhat Alternatives for MySQL

2004-01-21 Thread [EMAIL PROTECTED]

Hello,
 
For some time we've been running MySQL (from 4.0.1 to 4.0.17)  on Redhat (v. 7.2 & 
9.0). We use our linux server for one thing: MySQL server.
 
We are far from linux experts, so Redhat made sense, particularly the ease of 
installation and the bugfixes via up2date. Now that Redhat is ending support, we need 
an alternative that doesn't cost $349+ per year. We never need formal tech support; we 
only need access to reliable security bugfixes/updates.
 
Fedora seems out of the question because of the frequent upgrade cycles.
 
Can anyone recommend a linux vendor that 1) offers an easy bugfix/update system and 2) 
is affordable for a small business with limited linux expertise. We want a reliable 
solution for our MySQL server(s) as we grow.
 
I realize this request may be a little off-topic, but since the health, security and 
future of our database server is in question, we felt we needed input from other MySQL 
users.
 
Thanks!



-
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes

Re: mysqld without LinuxThreads

2004-01-09 Thread [EMAIL PROTECTED]

Thanks a lot for your kind and amusing explanation!
I will have a look at MySQL thread layer AND of course wait for kernel 
2.6..

Chris Nolan wrote:

On Thu, 2004-01-08 at 21:10, [EMAIL PROTECTED] wrote:
 

Andy Bakun wrote:

   

On Wed, 2004-01-07 at 10:38, [EMAIL PROTECTED] wrote:

 

Hi all,
does anyone know if it's possible to compile MySQL under Linux so that
mysqld doesn't rely upon LinuxThreads, but makes direct call to fork()
or clone() system calls instead?
  

   

As far as I know and can tell from reading docs, Linux's pthread
implementation DOES use clone to create threads.  In most cases, pthread
is a wrapper around whatever threading services the host system
provides.
 

I would like that MySQL avoid calling pthread_create function, which is 
the wrapper around clone() provided by pthreads. I'm looking for a way 
to exploit clone() directly, or to deploy processes instead of threads 
through the fork call. 

   



 

I'm looking for a way to avoid the exploitation of pthreads under
Linux.
  

   

Why wouldn't you want to use an industry standard threading model?

 

Just for testing purposes: I am trying to investigate the possible 
benefits that would come from the use of MySQL with the Migshm patch for 
OpenMosix, which enables threads migration, but which doesn't support 
pthreads presently (pthread_create call).

Any suggestions?
Thanks!
   

You've got a few options if I recall correctly:

1. MySQL's lower layers have a POSIX layer that takes care of things
like thread usage. You might want to look there.
2. Forget it. MySQL's architecture is designed to leverage threads and
the shared address space they require. The only thing LinuxThreads does
in addition to calling clone() when creating new "threads" involves some
minor management by the helper thread.
3. Wait for a while. Linux 2.6 includes (as does the RedHat 9.0 and
ES/WS/AS 3.0 kernels) NPTL - the Native POSIX Threads for Linux
implementation which is superior in many ways and does not use clone()
at all. As you'll no longer want to use clone() for threaded apps (trust
me on this), I'm betting that all interested parties will start
complaining in the relevant mailing lists.
4. (Dare I say it) Pick another RDBMS that uses a process-per-connection
model like that evil Oracle contraption or that funky-lookin'
elephant-promoting feature-heavy bohemeth PostgreSQL.
5. Use FoxPro, realise how lucky you are and manufacture some
contentment in running MySQL on a single machine. :-)
Hope this helps / amuses!

Regards,

Chris

 



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

Getting Garbage

2004-01-08 Thread [EMAIL PROTECTED]

I have a very strange problem with mysql version 4.1.1 . I have a table 
in utf-8 character set. When I insert the following into one of the 
columes that is TEXT:

گسترده جناح 
اصلاح طلب 
درون حاکميت 
از رد گسترده 
صلاحيت 
نامزدهای 
اين جناح از 
سوی شورای 
نگهبان 
محسوب

I get the same thing back, but when I insert more of the same kind of 
data (which is text in a different language I have copied from a 
browser), I get bad data when I fetch the data. Here is part of what I 
get back which is not good:

?kR\x08?kR\x087;۷ 
د&?^\x00\x00\x00\x00\x00\x00\x00d\x00\x00\x00\x00\x00\x00\x00(?\x0f?3\x02\rwww.bbc.co.uk\x05utf-8Q\x00http://www.bbc.co.uk/persian/iran/story/2004/01/040107_bt-jb-qualifications.shtml?\x04?kR\x08?kR\x087;۷; 
د&?^\x00\x00\x00\x00\x00\x00\x00d\x00\x00\x00\x00\x00\x00\x00(?\x0f?3\x02\rwww.bbc.co.uk\x05utf-8Q

Anybody knows what is going on?

Thanks,

-Jalil

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

Re: mysqld without LinuxThreads

2004-01-08 Thread [EMAIL PROTECTED]



Andy Bakun wrote:

On Wed, 2004-01-07 at 10:38, [EMAIL PROTECTED] wrote:
 

Hi all,
does anyone know if it's possible to compile MySQL under Linux so that
mysqld doesn't rely upon LinuxThreads, but makes direct call to fork()
or clone() system calls instead?
   

As far as I know and can tell from reading docs, Linux's pthread
implementation DOES use clone to create threads.  In most cases, pthread
is a wrapper around whatever threading services the host system
provides.
I would like that MySQL avoid calling pthread_create function, which is 
the wrapper around clone() provided by pthreads. I'm looking for a way 
to exploit clone() directly, or to deploy processes instead of threads 
through the fork call. 

 

I'm looking for a way to avoid the exploitation of pthreads under
Linux.
   

Why wouldn't you want to use an industry standard threading model?
 

Just for testing purposes: I am trying to investigate the possible 
benefits that would come from the use of MySQL with the Migshm patch for 
OpenMosix, which enables threads migration, but which doesn't support 
pthreads presently (pthread_create call).

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

mysqld without LinuxThreads

2004-01-07 Thread [EMAIL PROTECTED]

Hi all,
does anyone know if it's possible to compile MySQL under Linux so that
mysqld doesn't rely upon LinuxThreads, but makes direct call to fork()
or clone() system calls instead?
I'm looking for a way to avoid the exploitation of pthreads under
Linux.
Is this feature provided by MySQL?
Many Thanks
Fabio


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

Unicode support in 4.0.17

2004-01-05 Thread [EMAIL PROTECTED]

I finally upgraded to 4.0.17 to be able to use the support for Unicode 
but I have problems running any of the examples I have seen to use th 
'CHARACTER SET' option when creating tables. For example, even the 
following doesn't work:

CREATE DATABASE db_name
  CHARACTER SET latin1 COLLATE latin1_swedish_ci;
Am I doing anything wrong? How should I create tables (or columns)
using UTF8 encoding?
By the way, is there a location to look at the manual for 4.0 version?

Thanks,

-Jalil



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

4.0.17-0.i386.rpm installation hangs

2004-01-05 Thread [EMAIL PROTECTED]

I am trying to install the 4.0.17-0.i386.rpm server package. I first 
installed the MySQL-shared-compat-4.0.17-0.i386.rpm which went fine. 
Then I tried the MySQL-client-4.0.17-0.i386.rpm which gave me a 
segmentation fault. Now, I am running:

rpm -Uvh MySQL-server-4.0.17-0.i386.rpm

Which seems to hang forever. I use the same "rpm -Uvh" for all packages.

I was running version 3.23.56 initially on RedHat.

Any idea what is going on and what I should do now?

Regards,

-Jalil

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

General Question on Upgrading

2004-01-04 Thread [EMAIL PROTECTED]

I got version 3.23.56 of mysql with my redhat installation as an rpm 
package and now want to upgrade to the latest 4 version. My question is: 
how I can upgrade my current installation myself without going through 
the rpm package installer?

Thanks,

-Jalil

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

Thinking About Upgrading MySQL, PHP, and phpmyAdmin

2004-01-03 Thread [EMAIL PROTECTED]

I'm on a Macintosh G3 PowerBook with OS 10.2.1. PHP is version 4.3.0;
MySQL, 3.23.53; and phpmyAdmin, 2.4.0.

What are the latest available post-beta, stable, fully-baked versions? Is
upgrading as big a project as deleting old versions and then reinstalling
newer? Or are there instructs for upgrading over the old?

Thank you.

Stephen Tiano


mail2web - Check your email from the web at
http://mail2web.com/ .



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

1 2 3 >

1 - 100 of 262 matches

Mail list logo