Importing dBase II +
Hi, Can someone tell me if it is anyhow possible to import a dBaseIII+ database into MySQL and if so: how? Thank you, Huub -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tracking changes in the database
Andre Matos wrote: I am using InnoDB and replication, is there any issues? Thanks. Andre It depends what you're doing. If you use the binary log, there are no issues - in replication uses the binary log to achieve it's goals. Since the plain-text log is depreciated, you probably should learn to use the binary log. As I noted before, the plain-text log has some issues. I'm not sure what all of them are, but I've encountered issues when using plain-text logs and temporary tables ( eg 2 users create temporary tables - in the plain text log, it looks like the 1 user ). -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innobase sold to Oracle ?
Hi MySQL fans, I just read in the news about Innobase being sold to Oracle. Does anybody know whats going on with Innobase and what impact the selling out to Oracle might have on MySQL ? http://www.infoworld.com/article/05/10/12/HNnewmysql_1.html?source=rssurl=http://www.infoworld.com/article/05/10/12/HNnewmysql_1.html http://www.itmedia.co.jp/news/articles/0510/08/news012.html (ja) Best regards Nils Valentin Tokyo / Japan http://www.be-known-online.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query
Shawn, - Original Message - From: [EMAIL PROTECTED] To: Dušan Pavlica Cc: Michael Stassen ; list mysql Sent: Wednesday, October 12, 2005 4:45 PM Subject: Re: Help with query Dušan Pavlica [EMAIL PROTECTED] wrote on 10/12/2005 10:00:53 AM: Thanks, Michal, for your help. Your query works as I need. I tried to use same syntax as in MS Access, but results are for some reason different for this query. I'm working on application which should be able to connect to MySQL or to MSAccess (users' choice) and I didn't want to write querries for each DB system separately. Now I see that I will have to. Dusan - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Dušan Pavlica [EMAIL PROTECTED] Cc: list mysql mysql@lists.mysql.com Sent: Wednesday, October 12, 2005 2:54 PM Subject: Re: Help with query Dušan Pavlica wrote: Hello, could someone help me please to construct correct query or tell me what I'm doing wrong? snip Kind regards, Dusan Pavlica snip In any case, does this do what you want? SELECT p.Description, p.ID, pp.Product_ID, pc.Code, pp.StartDate FROM products_prices pp JOIN products p ON p.ID = pp.Product_ID LEFT JOIN products_codes pc ON p.ID = pc.Product_ID AND pc.CodeType = 1 WHERE StartDate Now() ORDER BY p.ID, pp.StartDate DESC; Michael I don't think you will need to change anything. MS Access should be able to work with Michael's query just fine. Just because the Query Builder in Access (I despise the SQL that comes out of that tool) always nests its JOINs doesn't mean that MS Access can't use un-nested joins. Give it a shot, you may be surprised. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Unfortunately, I'm afraid that MS Access is not able to work with un-nested joins. I tried Michael's solution also with different changes but Access keeps complaining about syntax error and on MSDN help and in one book about Access I have I saw that they use only syntax with nested joins. I don't know if it's matter but I use ADO for accessing MS Access database. Dusan
Re: Importing dBase II +
Alle 08:21, giovedì 13 ottobre 2005, Huub ha scritto: Hi, Can someone tell me if it is anyhow possible to import a dBaseIII+ database into MySQL and if so: how? On linux there is dbf2mysql http://packages.debian.org/stable/misc/dbf2mysql On windows you could import the tables in m$access and reexport them via odbc to mysql, then alter the tables to respect the original use. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Downgrade privileges on connect
Hi Andrew, all! [EMAIL PROTECTED] wrote: [[...]] but I believe in security in depth and so for read-only users I connect to the database with a SQL user that only has the select privilege, and for read/write users I connect to the database with a user with select,insert,update,delete privileges. Well done! This all works fine, the only reason to change it would be to reduce the number of SQL users an application requires. Then I could create one user with select,insert,update,delete, for example, but when the connection is established a SQL command could be issued requesting a downgrade of privileges to select only. It all comes down to having fewer application passwords to change on a regular basis to stay current with security requirements. Ok, I understand that goal. However, ... I realize that there is probably no way to do that with the current MySQL API, but perhaps it could be a feature request for future releases. ... the SQL way of handling privileges is to associate them with user accounts. I know some DBMSs have roles, but did not yet look into these, I assume this is a way of defining a set of privileges and then provide several users with the same set. There is also the concept of user groups, also doing this. (AIUI, both roles and groups are introduced to manipulate the privilege set only once, but still support distinct users with their own names and passwords - opposite to your goal.) I have never heard of a privilege downgrade while maintaining the user id, so I fear you have little chances in following such a direction. Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innobase sold to Oracle ?
Hi Valentin, all! [EMAIL PROTECTED] wrote: Hi MySQL fans, I just read in the news about Innobase being sold to Oracle. Correct. Does anybody know whats going on with Innobase and what impact the selling out to Oracle might have on MySQL ? What Oracle will do with Innobase is Oracle's matter, not much use in speculating about that IMO. Impact on MySQL: Very little, if any - see here: http://www.mysql.com/news-and-events/news/article_968.html In other words: MySQL has the rights to use, publish, and support InnoDB as a table handler, and will do so. No change for users. Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to test the MySQL Server (windows version) ?
The test system that is included in MySQL Server's Unix source distributions.(/mysql-test/mysql-test-run.sh) We can use the suite to run a test against to the MySQL Server. But these tests only can be run on Unix(linux). If you want to run the tests on Windows, you must use the Cygwin environment, and the server must be compiled under Cygwin environment. Actually, it doesn't test the MySQL Server's Windows version because the Cygwin had make the running environment be equal to the Unix's i think. Is my opinion right? I want to know how to test the MySQL Server's Windows version. Is MySQL AB doesn't provide the test method? Who can give me an answer? _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tracking changes in the database
You can also set a plain logging of mysql activity by enabling this in my.cnf log= /tmp/mysqld.log tmpdir = /tmp/ restart mysql Thanks, At 02:32 PM 10/13/2005, Daniel Kasak wrote: Andre Matos wrote: I am using InnoDB and replication, is there any issues? Thanks. Andre It depends what you're doing. If you use the binary log, there are no issues - in replication uses the binary log to achieve it's goals. Since the plain-text log is depreciated, you probably should learn to use the binary log. As I noted before, the plain-text log has some issues. I'm not sure what all of them are, but I've encountered issues when using plain-text logs and temporary tables ( eg 2 users create temporary tables - in the plain text log, it looks like the 1 user ). -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Ehrwin C. Mina 9/F Tower 2 RCBC Plaza 6819 Ayala Avenue cor. Sen. Gil J. Puyat Avenue Makati City 1200 Philippines Cell (63 918) 930 4383 (63 917) 845 2102 Tel(63 2) 757 2633 Fax(63 2) 757 2633 Email [EMAIL PROTECTED] Webwww.chikka.com This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, you must not copy this message or attachment or disclose the contents to any other person. If you have received this transmission in error, please notify the sender immediately and delete the message and any attachment from your system. Chikka does not accept liability for any omissions or errors in this message which may arise as a result of E-Mail-transmission or for damages resulting from any unauthorized changes of the content of this message and any attachment thereto. Chikka does not guarantee that this message is free of viruses and does not accept liability for any damages caused by any virus transmitted therewith. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
setting date format
Hi Friends,
I want to set the default date format as dd-mon-. How do i do this in
mysql.
I tried this, set global date_format='%d-%m-%y';. The command executed and
when i did show variable it show the new format.
But when i tried the below test, i got wrong results. Can u please help.
mysql create table mytest(doj date);
Query OK, 0 rows affected (0.05 sec)
mysql insert into mytest values('21-01-2005');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql select * from mytest;
++
| doj |
++
| -00-00 |
++
please help.
regards
anandkl
upload images / mp3 more Than 1 MB capacity ---- please help
hi ,
I have crerated a mysql database to store images , mp3 , video files..etc. In
my first stage i stored images as jpg , gif .
But when i try to store little but huge gif files it wont store . I used the
script as follows to upload images,
html
head
titleUpload File To MySQL Database/title
meta http-equiv=Content-Type content=text/html; charset=iso-8859-1
style type=text/css
!--
.box {
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
border: 1px solid #00;
}
--
/style
/head
body
?
if(isset($_POST['upload']))
{
$fileName = $_FILES['userfile']['name'];
$tmpName = $_FILES['userfile']['tmp_name'];
$fileType = $_FILES['userfile']['type'];
$fileSize = $_FILES['userfile']['size'];
$fp = fopen($tmpName, 'r');
$content = fread($fp, $fileSize);
$content = addslashes($content);
fclose($fp);
if(!get_magic_quotes_gpc())
{
$fileName = addslashes($fileName);
}
include 'library/config.php';
include 'library/opendb.php';
$query = INSERT INTO upload (name, type, size, content ) .
VALUES ('$fileName', '$fileType','$fileSize',
'$content');
mysql_query($query) or die('Error, query failed');
include 'library/closedb.php';
echo brFile $fileName uploadedbr;
}
?
form action= method=post enctype=multipart/form-data name=uploadform
table width=350 border=0 cellpadding=1 cellspacing=1 class=box
tr
td width=246input type=hidden name=MAX_FILE_SIZE
value=200input name=userfile type=file class=box id=userfile
/td
td width=80input name=upload type=submit class=box id=upload
value= Upload /td
/tr
/table
/form
/body
/html
56Kb , 75 Kb files are accept to store , but more that 1 MB we cant upload. It
wont give any error messages . But that particular data wont displayed.
so , this is a big problem for me . i couldnt sorted out yet . I used mysqlcc
and sqlYOG as well to upload images ( contents ).
in like this situations how can we work with mp3 and such huge files
please . help me very very urgent
Thanx in advance,
Kane.
-
Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
connection issue
Hi List, When I am trying to connect to mysql 4.0.20 database it is taking very long time when I specified host like Mysql -ux -p -h192.168.3.111 But it is connecting very quickly when I tried like below Mysql -ux -p It is an urgent issue. Please help me. Thanks Anil DBA
Re: setting date format
Ananda Kumar wrote: Hi Friends, I want to set the default date format as dd-mon-. How do i do this in mysql. The short answer: not. http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html lists the variables you can change by the SET GLOBAL or SET SESSION command. 'date_format' is not listed there. Also http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html states: # date_format This variable is not implemented. # datetime_format This variable is not implemented. It's probably listed there for future use. Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compound foreign key(s)
I have the following innoDb tables: country has `id` as PK lang has `id` as PK I want to have a 3rd innoDb table: countrylang with `cid,lang` as compound key with both `cid` and `lang` as foreign (cascade-delete) keys to the master tables. However mysql throws error (Cannot create table..) when I try to define `cid` as a foreign key to country.id country.id and countrytext.cid are both smallint(5) Any help? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upload images / mp3 more Than 1 MB capacity ---- please help
Kane Wilson wrote: But when i try to store little but huge gif files it wont store . First of all, use the method described at http://www.php.net/manual/en/features.file-upload.php for a safe way to handle file uploads. It could be that you run into a server limit which will show up if you use that method. I do think that you exceeded the max_allowed_packet size for MySQL queries which has a default value of 1048576 (=1MB). You can increase this number (must be done in both client and server!!), but it is usually best to store huge files in a file system and not in a database. Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compound foreign key(s)
Hi Guy, Use SHOW INNODB STATUS and check foreign key error at there Good luck Guy Brom wrote: I have the following innoDb tables: country has `id` as PK lang has `id` as PK I want to have a 3rd innoDb table: countrylang with `cid,lang` as compound key with both `cid` and `lang` as foreign (cascade-delete) keys to the master tables. However mysql throws error (Cannot create table..) when I try to define `cid` as a foreign key to country.id country.id and countrytext.cid are both smallint(5) Any help? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible bug in mysql 5.0.13
Peter Brawley [EMAIL PROTECTED] wrote on 10/12/2005 04:27:18
PM:
James,
Both ...
SELECT ...
FROM a, b LEFT JOIN c ON a.x=c.y
and
SELECT ...
FROM a
LEFT JOIN B USING (x )
LEFT JOIN c ON a.x=c.y
work up to and including version 5.0.10, not in 5.0.11, 12 or 13.
http://bugs.mysql.com/bug.php?id=13832 reports...
The two statements below are quite different from one another:
1) SELECT * FROM t1, t2 LEFT JOIN t3 ON t1.a=t3.c
2) SELECT * FROM t1 JOIN t2 LEFT JOIN t3 ON t1.a=t3.c
Statement (1) above will likely continue to give an Unknown
column't1.a' in
'on clause' error, while statement (2) will likely function correctly
at some
point in the future.
This bug report is in reference to statements like statement (2), and
no bug
reports that use a statement like statement (1) are duplicates of this
bug.
The behaviour of (1) above is also verified (http://bugs.mysql.
com/bug.php?id=13551), and that page explains...
This is a change that was made in 5.0.15 [sic] to make MySQL more
compliant with the standard. According to the SQL:2003
from clause ::= FROM table reference list
table reference list ::=
table reference [ { comma table reference }... ]
table reference ::=
table factor
| joined table
joined table ::=
cross join
| qualified join
| natural join
...
Thus when you write
... FROM t1 , t2 LEFT JOIN t3 ON (expr)
it is parsed as
(1) ... FROM t1 , (t2 LEFT JOIN t3 ON (expr))
and not as
(2) ... FROM (t1 , t2) LEFT JOIN t3 ON (expr)
so, from expr you can only refer to columns of t2 and t3 - operands
of the join. Workaround - to put parentheses explicitly as in (2).
Then you can refer to t1 columns from expr.
Unfortunately, this change is not properly documented in the manual,
it will be fixed.
PB
http://www.artfulsoftware.com
-
[EMAIL PROTECTED] wrote:
James Black [EMAIL PROTECTED] wrote on 10/12/2005 02:06:26 PM:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Peter Brawley wrote:
James,
You can reproduce that error by writing ...
SELECT ...
FROM a, b INNER JOIN c ON a.x=c.y
The error goes away if you instead write ...
SELECT ...
FROM b, a INNER JOIN c ON A.x=c.y
so you might try swapping
FROM items i
, nams.netids n
Tried that,now I get:
Unknown column 'n.badge'in 'on clause'
So, whichever order I put them in, I get one of two errors.
It appears that this bug will continue to break for me until it is
fixed in the next version, hopefully.
- --
Love is mutual self-giving that ends in self-recovery. Fulton Sheen
James Black[EMAIL PROTECTED]
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFDTVCiikQgpVn8xrARAqGjAJ9y4/ym15QPZj8KVvbyuIkmTIKMKACfTlP9
cd7w92nB8uhOH2Y1+jAe4MU=
=FOcN
-END PGP SIGNATURE-
What if, instead of using a comma, you use an explicit INNER JOIN? It's
perfectly valid to leave out the ON clause of an INNER JOIN (creating a
Cartesian product). I mention this because you don't seem to have a
term
to use in an ON clause, unless you want to move the term
n.netid='jblack'
from the WHERE clause.
SELECT...
FROM items i
INNER JOIN nams.netids n
INNER JOIN ...
...
Does the problem remain? If it goes away, this would be useful
information
to include in your bug report.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.14/130 - Release Date:
10/12/2005
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.14/130 - Release Date:
10/12/2005
Fantastic response!! Someone give this man a coffe mug or something!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Problem with GRANT not working.
Dear MySQL, A new installation of 4.1.9 on AIX 5. I have a GRANT that is not working. Connecting from foreign server 'pho.com' with IP of '192.168.1.1', I should be able to GRANT using either: mysql GRANT all ON test.* TO test@'192.168.%' IDENTIFIED BY 'test'; mysql GRANT all ON test.* TO test@'%.com' IDENTIFIED BY 'test'; The second version '%.com' allows access. The first version access by IP '192.168.%' does not work. This always has worked on 4.1.9 before. There is nothing in the manual to elaborate on this problem. As far as I can see, this should work perfectly. Would any users be able to suggest a solution to this? Regards, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upload images / mp3 more Than 1 MB capacity ---- please help
thanx for the reply , usually , i uploading jpg and gif files into mysql database , there is no any issue. we can display them correctly. when i try to upload such huge mp3 files and some .jar files into the databse , i used mysqlcc GUI and SQLYOG GUI , from those interfaces also giving troubles. contents wont stored. my requrement is any how keep the contents , in the mysql database, i have high performance machine. so then i'm not worry about perforamnce. please , let me know how could be the way to store such files in a mysql databse ??? please help Jigal van Hemert [EMAIL PROTECTED] wrote: Kane Wilson wrote: But when i try to store little but huge gif files it wont store . First of all, use the method described at http://www.php.net/manual/en/features.file-upload.php for a safe way to handle file uploads. It could be that you run into a server limit which will show up if you use that method. I do think that you exceeded the max_allowed_packet size for MySQL queries which has a default value of 1048576 (=1MB). You can increase this number (must be done in both client and server!!), but it is usually best to store huge files in a file system and not in a database. Kind regards, Jigal. - Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
Mysql with SSL connections
Hi folk! I'm new to mysql and to databases... I'm using Fedora Core 4 with mysql 4.1.11-2 I need some application clients to connect the server using SSL connections. I've created and tested x509 certificates using the way mysql documentation explain, I've modified the /etc/my.cnf [mysqld] and [client] sections with the certificates that are located in /etc/pki (I've made some test with the certificates in /usr/pki too). The fact is that I cant start mysql with ssl support using the /etc/ec/d/init.d/mysql script. But the interesting thing is that if I copy the same script to any different folder it works. If it is in the init.dfolder I always get an SSL connection error. running mysqld, mysqld_safe by hand work perfectly and SSL works too. This is annoying I'll appreciate any help in respect. thanks in advance and best regards... -- Israel Fdez. Cabrera [EMAIL PROTECTED]
Re: Importing dBase II +
Francesco R. wrote: Alle 08:21, giovedì 13 ottobre 2005, Huub ha scritto: Hi, Can someone tell me if it is anyhow possible to import a dBaseIII+ database into MySQL and if so: how? On linux there is dbf2mysql http://packages.debian.org/stable/misc/dbf2mysql On windows you could import the tables in m$access and reexport them via odbc to mysql, then alter the tables to respect the original use. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upload images / mp3 more Than 1 MB capacity ---- please help
AS JIGAL SAID : make sure you are not creating an INSERT statement bigger than the max_allowed_packet setting for both your destination server and your client library (whichever ones you are using). If you attempt to create a packet that is too large, the server will _ignore_ it. Because the packet (SQL statement) is too large, it is considered malformed and any processing of it will be aborted. http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html http://dev.mysql.com/doc/refman/5.0/en/memory-use.html http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Kane Wilson [EMAIL PROTECTED] wrote on 10/13/2005 09:25:24 AM: thanx for the reply , usually , i uploading jpg and gif files into mysql database , there is no any issue. we can display them correctly. when i try to upload such huge mp3 files and some .jar files into the databse , i used mysqlcc GUI and SQLYOG GUI , from those interfaces also giving troubles. contents wont stored. my requrement is any how keep the contents , in the mysql database, i have high performance machine. so then i'm not worry about perforamnce. please , let me know how could be the way to store such files in a mysql databse ??? please help Jigal van Hemert [EMAIL PROTECTED] wrote: Kane Wilson wrote: But when i try to store little but huge gif files it wont store . First of all, use the method described at http://www.php.net/manual/en/features.file-upload.php for a safe way to handle file uploads. It could be that you run into a server limit which will show up if you use that method. I do think that you exceeded the max_allowed_packet size for MySQL queries which has a default value of 1048576 (=1MB). You can increase this number (must be done in both client and server!!), but it is usually best to store huge files in a file system and not in a database. Kind regards, Jigal. - Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
Re: Problem with GRANT not working.
A little more information: If I use --skip-name-resolve then I get an odd error: ERROR 1045 (28000): Access denied for user 'me'@'0.0.0.0' (using password: YES) This seems to suggest that MySQL thinks my client is from '0.0.0.0' and hence cannot match any GRANT syntax I use. Why would MySQL think my client is from '0.0.0.0'? Thanks in advance for any person who can offer a solution to these problems. Ben Clewett wrote: Dear MySQL, A new installation of 4.1.9 on AIX 5. I have a GRANT that is not working. Connecting from foreign server 'pho.com' with IP of '192.168.1.1', I should be able to GRANT using either: mysql GRANT all ON test.* TO test@'192.168.%' IDENTIFIED BY 'test'; mysql GRANT all ON test.* TO test@'%.com' IDENTIFIED BY 'test'; The second version '%.com' allows access. The first version access by IP '192.168.%' does not work. This always has worked on 4.1.9 before. There is nothing in the manual to elaborate on this problem. As far as I can see, this should work perfectly. Would any users be able to suggest a solution to this? Regards, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to test the MySQL Server (windows version) ?
Hi ! lu ming wrote: The test system that is included in MySQL Server's Unix source distributions.(/mysql-test/mysql-test-run.sh) We can use the suite to run a test against to the MySQL Server. But these tests only can be run on Unix(linux). If you want to run the tests on Windows, you must use the Cygwin environment, and the server must be compiled under Cygwin environment. Actually, it doesn't test the MySQL Server's Windows version because the Cygwin had make the running environment be equal to the Unix's i think. Is my opinion right? Well, this is a typical Windows problem (yes, I admit being a Unix guy): Native Windows does not come with a decent portable scripting language. You are right that the MySQL test suite is based on a shell script approach which does not work in (native) Windows. (You might get MinGW, MKS or similar suites to get a shell and try in that environment, but this would be your own personal experiment. If you have looked into that script, you get an idea of the complexity needed, plain batch files will not work for this.) I want to know how to test the MySQL Server's Windows version. Is MySQL AB doesn't provide the test method? Who can give me an answer? MySQL is internally working on replacing the shell script by a set of Perl scripts, which can also be used on Windows (provided you install Perl). However, this work is not yet finished, and several tests will produce different results on Windows from the Unix ones, which the tool considers to be failures - even if it is just the way the result is written,no real database difference. So currently there is no way for you to run the test suite on Windows and get usable results - sorry. We are working on this. Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Mysql with SSL connections
Well I've done more than that I wrote a custom init script with the simplest possible start function containig just the following line: /usr/bin/mysqld_safe and SSL does not work eather... please help... best regards Israel -- Forwarded message -- From: RedRed!com IT Department [EMAIL PROTECTED] Date: Oct 13, 2005 10:16 AM Subject: Re: Mysql with SSL connections To: Israel Fernández Cabrera [EMAIL PROTECTED] Israel Fernández Cabrera wrote: Hi folk! I'm new to mysql and to databases... I'm using Fedora Core 4 with mysql 4.1.11-2 I need some application clients to connect the server using SSL connections. I've created and tested x509 certificates using the way mysql documentation explain, I've modified the /etc/my.cnf [mysqld] and [client] sections with the certificates that are located in /etc/pki (I've made some test with the certificates in /usr/pki too). The fact is that I cant start mysql with ssl support using the /etc/ec/d/init.d/mysql script. But the interesting thing is that if I copy the same script to any different folder it works. If it is in the init.dfolder I always get an SSL connection error. running mysqld, mysqld_safe by hand work perfectly and SSL works too. This is annoying I'll appreciate any help in respect. thanks in advance and best regards... -- Israel Fdez. Cabrera [EMAIL PROTECTED] You might want to check the mysql init.d script to make sure that all of the paths in there are correct. Sean -- Israel Fdez. Cabrera [EMAIL PROTECTED]
Re: Fwd: Mysql with SSL connections
I'm using the default Fedora Core 4 Linux mysql RPM package... I just create x509 certificates and modify the /etc/my.cnf file to use these certificates. The default mysqld init script doesn't work in its normal' location, just moving it to a different folder make it works. MySQL daemon runs by the way, but without SSL support, mysqladmin tests return SSL connection error too... On 10/13/05, RedRed!com IT Department [EMAIL PROTECTED] wrote: Israel Fernández Cabrera wrote: Well I've done more than that I wrote a custom init script with the simplest possible start function containig just the following line: /usr/bin/mysqld_safe and SSL does not work eather... please help... best regards Israel Are you sure your starting the correct binary? When you installed mysql did you install to the default /usr/local or did you specifically tell it to install to /usr ? If you look in the error file in /usr/var or /usr/local/var, does it give you any useful information? It may have a reason for the failure in there. I'm not an expert, but those are some of the steps that I would take to figure this out. Sean -- Israel Fdez. Cabrera [EMAIL PROTECTED]
Re: Innobase sold to Oracle ?
What Oracle will do with Innobase is Oracle's matter, not much use in speculating about that IMO. Sorry, I am going to disagree with that ;-), I am VERY WELL INTERESTED how the environment around Innobase / Innodb will change - and with it any support options and other impacts that might have for our company or any of our customers. I can understand however, if you dont want this to be discussed on this mailing list ;-). Best regards Nils Valentin Tokyo / Japan http://www.be-known-online.com Quoting Joerg Bruehe [EMAIL PROTECTED]: Hi Valentin, all! [EMAIL PROTECTED] wrote: Hi MySQL fans, I just read in the news about Innobase being sold to Oracle. Correct. Does anybody know whats going on with Innobase and what impact the selling out to Oracle might have on MySQL ? What Oracle will do with Innobase is Oracle's matter, not much use in speculating about that IMO. Impact on MySQL: Very little, if any - see here: http://www.mysql.com/news-and-events/news/article_968.html In other words: MySQL has the rights to use, publish, and support InnoDB as a table handler, and will do so. No change for users. Regards, J?rg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.13 lint?
I'm fairly new to MySQL and am getting an error messages like: ERROR 1064 (42000) at line 5: 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 'id int(14) unsigned NOT NULL auto_increment, forename varchar(40) NOT NU' at line 2 neelix hgs 18 % So it doesn't tell me exactly where, or what the nature of the syntax error is (and it can't even tell me it is version 4.1.13 which I know already). It has truncated the second line, so it's not that the rest is missing. My editor's syntax highlighter doesn't show anything awful. This is actually for lines 7 and 8 of the input, the first 4 lines being comments, so the numbering in the output is wrong. Are there any tools (like lint for C) to be more verbose and helpful about this? Thank you, Hugh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connection issue
When you connect without specifing the host, the mysql client will try to connect using the mysql socket file (usually /tmp/mysql.sock or /var/lib/mysql/mysql.sock) and when you specify the IP address it will try to connect using a TCP connection to port 3306. My guess is the you have name lookups turned on. If this is the case mysql will try to resolve the IP address given and probably the 192.168.3.111 IP is not resolving properly. The solution for this is to a add a skip-name-resolve option to your MySQL configuration file. You might also check http://dev.mysql.com/doc/refman/5.0/en/dns.html and the related articles in the MySQL documentation HTH Dobromir Velev On Thursday 13 October 2005 15:38, Anil wrote: Hi List, When I am trying to connect to mysql 4.0.20 database it is taking very long time when I specified host like Mysql -ux -p -h192.168.3.111 But it is connecting very quickly when I tried like below Mysql -ux -p It is an urgent issue. Please help me. Thanks Anil DBA -- Dobromir Velev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does the MySQL mailing list use MySQl to manage the mailing list?
On Thu, Oct 13, 2005 at 02:34:00PM +0900, Dave wrote: I am curious to know if the MySQL mailing list uses MySQL in managing the list and it's archives. The articles are surely archived in a database, and I would assume that if the MySQL developers were to use any database, it would be a MySQL one. What applications are used for management of this list? I like the way it is handled, and I have always been interested in having a mailing list manager that uses MySQL to keep track of members. The lists are managed using ezmlm-idx (http://www.ezmlm.org/). The subscription lists are kept in a MySQL database, the message archives are simply kept in the filesystem, the index of messages used by the news server and web archive are kept in MySQL, and the search index is kept in MySQL. Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.13 lint?
Hugh Sasse [EMAIL PROTECTED] wrote on 13/10/2005 16:27:44: I'm fairly new to MySQL and am getting an error messages like: ERROR 1064 (42000) at line 5: 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 'id int(14) unsigned NOT NULL auto_increment, forename varchar(40) NOT NU' at line 2 neelix hgs 18 % So it doesn't tell me exactly where, or what the nature of the syntax error is (and it can't even tell me it is version 4.1.13 which I know already). It has truncated the second line, so it's not that the rest is missing. My editor's syntax highlighter doesn't show anything awful. This is actually for lines 7 and 8 of the input, the first 4 lines being comments, so the numbering in the output is wrong. Are there any tools (like lint for C) to be more verbose and helpful about this? No, I don;'t think there are any such tools. When you get this sort of message, the error is nearly always *just before* the quoted bit. Which means that you have to get hold of the full command line that you sent and find out what immediately preceded the characters it has given as an error. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Tracking changes in the database
It did not work Daniel. I added this into my.cnf file: [mysqld] log-bin=/var/log/mysql/binary.log and saved the file in two places: /etc/my.cnf /usr/local/mysql/my.cnf Then, I restart the server: sudo /Lybrary/StartupItems/MySQLCOM restart I also did two UPDATE, one INSERT, and one DELETE, but the binary.log file was not created. Note: I am using Mac OS X. Andre -- Andre Matos [EMAIL PROTECTED] -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Thursday, October 13, 2005 2:33 AM To: Andre Matos; mysql@lists.mysql.com Subject: Re: Tracking changes in the database Andre Matos wrote: I am using InnoDB and replication, is there any issues? Thanks. Andre It depends what you're doing. If you use the binary log, there are no issues - in replication uses the binary log to achieve it's goals. Since the plain-text log is depreciated, you probably should learn to use the binary log. As I noted before, the plain-text log has some issues. I'm not sure what all of them are, but I've encountered issues when using plain-text logs and temporary tables ( eg 2 users create temporary tables - in the plain text log, it looks like the 1 user ). -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.13 lint?
On Thu, 13 Oct 2005, [EMAIL PROTECTED] wrote: Hugh Sasse [EMAIL PROTECTED] wrote on 13/10/2005 16:27:44: I'm fairly new to MySQL and am getting an error messages like: ERROR 1064 (42000) at line 5: 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 'id int(14) unsigned NOT NULL auto_increment, forename varchar(40) NOT NU' at line 2 neelix hgs 18 % [...] Are there any tools (like lint for C) to be more verbose and helpful about this? No, I don;'t think there are any such tools. When you get this sort of message, the error is nearly always *just before* the quoted bit. Which means that you have to get hold of the full That's the first line of a create Table: CREATE TABLE IF NOT EXISTS students( id int(14) unsigned NOT NULL auto_increment, forename varchar(40) NOT NULL default '', surname varchar(40) NOT NULL default '', [...] command line that you sent and find out what immediately preceded the characters it has given as an error. It would be helpful if it could spit out expected %s, which would give some more clues I know that parsers are difficult to get right, however Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.13 lint?
Hugh Sasse [EMAIL PROTECTED] wrote on 10/13/2005 11:27:44 AM: I'm fairly new to MySQL and am getting an error messages like: ERROR 1064 (42000) at line 5: 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 'id int(14) unsigned NOT NULL auto_increment, forename varchar(40) NOT NU' at line 2 neelix hgs 18 % So it doesn't tell me exactly where, or what the nature of the syntax error is (and it can't even tell me it is version 4.1.13 which I know already). It has truncated the second line, so it's not that the rest is missing. My editor's syntax highlighter doesn't show anything awful. This is actually for lines 7 and 8 of the input, the first 4 lines being comments, so the numbering in the output is wrong. Are there any tools (like lint for C) to be more verbose and helpful about this? Thank you, Hugh MySQL does not normally use (double quotes) as name identifiers, it uses ` `(backticks). Change all of your to ` to make your syntax correct. That would mean that part of your original statement will look like `id` int(14) unsigned NOT NULL auto_increment, `forename` varchar(40) NOT NULL, In this case line 2 did not refer to the position in the script but to the line within the statement. Your line 1 was something like CREATE TABLE sometablename ( which made your first column definition (the id column) appear on line 2. Make better sense? For more details on ` vs. please read http://dev.mysql.com/doc/refman/4.1/en/legal-names.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MySQL 4.1.13 lint?
Hugh, Agreed that the MySQL error reporter is primitive, and that 'lint for MySQL' would be a smash hit, but if you look up error 1064 you'll find it is a naming error, two of which are visible in your error report--column names enclosed in double quotes. PB http://www.artfulsoftware.com - Hugh Sasse wrote: I'm fairly new to MySQL and am getting an error messages like: ERROR 1064 (42000) at line 5: 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 'id int(14) unsigned NOT NULL auto_increment, forename varchar(40) NOT NU' at line 2 neelix hgs 18 % So it doesn't tell me exactly where, or what the nature of the syntax error is (and it can't even tell me it is version 4.1.13 which I know already). It has truncated the second line, so it's not that the rest is missing. My editor's syntax highlighter doesn't show anything awful. This is actually for lines 7 and 8 of the input, the first 4 lines being comments, so the numbering in the output is wrong. Are there any tools (like lint for C) to be more verbose and helpful about this? Thank you, Hugh -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.14/131 - Release Date: 10/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.13 lint?
On Thu, 13 Oct 2005, [EMAIL PROTECTED] wrote: MySQL does not normally use (double quotes) as name identifiers, it uses ` `(backticks). Change all of your to ` to make your syntax correct. That would mean that part of your original statement will look like Thank you. In this case line 2 did not refer to the position in the script but to but line 5 was at least line 7 in the script, and at most line 4 in the statement. the line within the statement. Your line 1 was something like CREATE TABLE sometablename ( which made your first column definition (the id column) appear on line 2. Make better sense? Yes. Thank you. For more details on ` vs. please read http://dev.mysql.com/doc/refman/4.1/en/legal-names.html Thank you, I'm off there now. Hugh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.13 lint?
On Thu, 13 Oct 2005, Peter Brawley wrote: Hugh, Agreed that the MySQL error reporter is primitive, and that 'lint for MySQL' If I were familiar with the code base I'd happy send patches, but I was hoping improve diagnostics might get nudged up somebody's list by raising it. would be a smash hit, but if you look up error 1064 you'll find it is a naming The first several results returned by google are no help. error, two of which are visible in your error report--column names enclosed in double quotes. I'll probably need to talk to the maintainers of the vim syntax file then, as well, because though that's a MySQL 3 syntax file I expect it was still wrong. Imho it should show white on red as a result, not red on black (string). PB http://www.artfulsoftware.com Thank you, Hugh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy or not so easy GROUP BY
Can anyone please help me with this? I'm just not getting it. - Thanks Ed Reed [EMAIL PROTECTED] 10/6/05 2:50:46 PM Thanks for the reply, Sorry; I'm using 4.1.11 Thanks again. Pooly [EMAIL PROTECTED] 10/6/05 1:51 AM 2005/10/6, Ed Reed [EMAIL PROTECTED] : I'm trying to group some sub categories with a concatenated result. I'm trying to get the max sub for each item per month. I think it should be fairly easy but it is kicking my butt. My data looks like this, +++--+ | month | item | sub | +++--+ | 8 | 5 | NULL | +++--+ | 8 | 4 | a | +++--+ | 8 | 6 | NULL | +++--+ | 8 | 6 | a | +++--+ | 8 | 5 | a | +++--+ | 8 | 4 | b | +++--+ | 9 | 1 | NULL | +++--+ | 9 | 2 | NULL | +++--+ | 9 | 1 | a | +++--+ | 9 | 3 | NULL | +++--+ | 9 | 2 | a | +++--+ | 9 | 1 | b | +++--+ | 9 | 4 | NULL | +++--+ | 9 | 4 | a | +++--+ | 9 | 2 | b | +++--+ | 9 | 1 | c | +++--+ | 10 | 1 | NULL | +++--+ | 10 | 1 | a | +++--+ | 10 | 2 | NULL | +++--+ I'm not having a problem getting a concatenated result but I am having difficulty getting my data grouped correctly. My results should look like this. +---+ | MAX Group | +---+ | 8-4b | +---+ | 8-5a | +---+ | 8-6a | +---+ | 9-1c | +---+ | 9-2b | +---+ | 9-3 | +---+ | 9-4a | +---+ | 10-1a | +---+ | 10-2 | +---+ - Thanks in advance So, Max group by month/item ? http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html and for concat : http://dev.mysql.com/doc/mysql/en/string-functions.html Since we don't know your MySQL version, I can't give you a precise answer. HIMH -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy or not so easy GROUP BY
(response bottom-posted) Ed Reed [EMAIL PROTECTED] wrote on 10/13/2005 12:36:24 PM: Can anyone please help me with this? I'm just not getting it. - Thanks Ed Reed [EMAIL PROTECTED] 10/6/05 2:50:46 PM Thanks for the reply, Sorry; I'm using 4.1.11 Thanks again. Pooly [EMAIL PROTECTED] 10/6/05 1:51 AM 2005/10/6, Ed Reed [EMAIL PROTECTED] : I'm trying to group some sub categories with a concatenated result. I'm trying to get the max sub for each item per month. I think it should be fairly easy but it is kicking my butt. My data looks like this, +++--+ | month | item | sub | +++--+ | 8 | 5 | NULL | +++--+ | 8 | 4 | a | +++--+ | 8 | 6 | NULL | +++--+ | 8 | 6 | a | +++--+ | 8 | 5 | a | +++--+ | 8 | 4 | b | +++--+ | 9 | 1 | NULL | +++--+ | 9 | 2 | NULL | +++--+ | 9 | 1 | a | +++--+ | 9 | 3 | NULL | +++--+ | 9 | 2 | a | +++--+ | 9 | 1 | b | +++--+ | 9 | 4 | NULL | +++--+ | 9 | 4 | a | +++--+ | 9 | 2 | b | +++--+ | 9 | 1 | c | +++--+ | 10 | 1 | NULL | +++--+ | 10 | 1 | a | +++--+ | 10 | 2 | NULL | +++--+ I'm not having a problem getting a concatenated result but I am having difficulty getting my data grouped correctly. My results should look like this. +---+ | MAX Group | +---+ | 8-4b | +---+ | 8-5a | +---+ | 8-6a | +---+ | 9-1c | +---+ | 9-2b | +---+ | 9-3 | +---+ | 9-4a | +---+ | 10-1a | +---+ | 10-2 | +---+ - Thanks in advance So, Max group by month/item ? http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html and for concat : http://dev.mysql.com/doc/mysql/en/string-functions.html Since we don't know your MySQL version, I can't give you a precise answer. HIMH -- Pooly Webzine Rock : http://www.w-fenec.org/ Since you have neglected to post the name of your table or any of your previous attempts, would you be so kind as to try this query with the correct table and column names inserted: SELECT concat(month,'-',item,max(sub)) as 'MAX Group' FROM whatever_your_table_is_called GROUP BY monty, item; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Innobase sold to Oracle ?
[EMAIL PROTECTED] wrote: speculating about that IMO. What Oracle will do with Innobase is Oracle's matter, not much use in Sorry, I am going to disagree with that ;-), I am VERY WELL INTERESTED how the environment around Innobase / Innodb will change - and with it any support options and other impacts that might have for our company or any of our customers. I of course cannot speak for MySQL or any of the other affected parties, but I have been very interested in this issue as we have many high-profile clients who are going to start asking the same kinds of questions very soon. As such, I have tried to be on top of this as much as possible and will share what I believe: Short-term, there is no real impact. MySQL and Innobase have an existing contract that runs (approximately - don't know the exact date) through the end of next year. As such, the existing terms will stay in place through then at the minimum. The only potential impact before then would involve the level of participation in Innobase employees with troubleshooting, patching, and new InnoDB features. However, from what I have heard third-hand, Heikki is continuing to assist as before. All in all, I do not believe any MySQL customers have anything to be concerned with before the end of next year. I agree that at this point in time, it is impossible to tell exactly what Oracle may or may not do with InnoDB. The exact terms of the purchase of Innobase have not been disclosed, so it is unknown if they are going to just acquire the rights to InnoDB and send Innobase employees packing, if they are going to employ the Innobase employees, if they are going to continue active development of InnoDB, etc. Any guesses or statements by anyone other than upper management at Oracle is mere speculation. I can come up with a number of possible scenarios, but none of them result in a dead in the water situation for any MySQL customers. Also, there are a number of things MySQL can do to hedge their bets against any decision Oracle may make regarding the future disposition of InnoDB. My advice is to not worry too much about the Oracle situation for now. Give MySQL some time to work through their options and let the MySQL-Oracle relationship do what it may. I have started working on an internal whitepaper to discuss the situation without all the FUD some journalists and some other DB companies (think ANTs) have started throwing around and to present a list of what I believe to be the most likely outcomes based upon what is publicly known and what options exist for MySQL and Oracle to take in this matter. Depending upon what information may be out by then and on whether or not I can get permission from upper management to release this paper into the open, I would be willing to share my analysis openly. I certainly understand the concerns that people have with this development, but it is truly too early to make a knowledgable call - to do so would be akin to speculating the World Series winner on opening day. Cheers, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL sw for reverse engineering
Thank you all for the posts. Can anyone explain to me how can I connect DBDesigner4 to MySQL 4.x? I've never used MySQL 3.x and I believe there's some kind off trick to connect DBDesigner4 to MySQL 4.x because the program doesn't support the MySQL 4.x Or the ODBC connection solution to MySQL 4.x... Thanks, Bruno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to get a count from this query
SELECT avg(abs(pmd.value) - (mypmd.value))) as diff, pm.mname, pmd.uid FROM pmdata pmd, mnames pm, pmdata mypmd WHERE mypmd.uid= ? AND pmd.pmid=pm.pmid AND mypmd.pmid=pm.pmid AND pmd.uid != mypmd.uid GROUP BY pmd.pmid, pmd.uid ORDER BY pmd.uid What I would like also to return in this query is a COUNT of the number of pmd.uid of each different value so I know how many values I got from uid#1, uid#2 etc. I tried putting COUNT(pmd.uid) as numdelta but it just gave me a 1 in every row. The problem is I am not doing a straight GROUP BY pmd.uid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing GROUP BY
Hi All, I have a query which takes approximately 0.5 seconds to execute , it is as follows SELECT gamename, MAX(score) AS score, COUNT(valid=1) AS played FROM gamesessions AS gamesessions WHERE valid=1 AND sessiontype IN (1,2) GROUP BY gamename; EXPLAIN SELECT gives me this, also there is an index on the columns valid and sessiontype id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE gamesessions ref valid_sess valid_sess 1 const 55003 Using where; Using temporary; Using filesort Can this be optimized to run faster Appreciate your time Kishore Jalleda
Re: How to get a count from this query
Gerald Taylor [EMAIL PROTECTED] wrote on 10/13/2005 12:20:53 PM: SELECT avg(abs(pmd.value) - (mypmd.value))) as diff, pm.mname, pmd.uid FROM pmdata pmd, mnames pm, pmdata mypmd WHERE mypmd.uid= ? AND pmd.pmid=pm.pmid AND mypmd.pmid=pm.pmid AND pmd.uid != mypmd.uid GROUP BY pmd.pmid, pmd.uid ORDER BY pmd.uid What I would like also to return in this query is a COUNT of the number of pmd.uid of each different value so I know how many values I got from uid#1, uid#2 etc. I tried putting COUNT(pmd.uid) as numdelta but it just gave me a 1 in every row. The problem is I am not doing a straight GROUP BY pmd.uid Can you explain, in simple language, the question you are trying to answer with this query? I see an INNER JOIN and a != used together which makes me wonder if you needed to use one of the OUTER JOINs instead. Along with your explanation, please respond with the results of SHOW CREATE TABLE pmdata\G SHOW CREATE TABLE mnames\G Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Optimizing GROUP BY
Kishore Jalleda [EMAIL PROTECTED] wrote on 10/13/2005 02:25:52 PM: Hi All, I have a query which takes approximately 0.5 seconds to execute , it is as follows SELECT gamename, MAX(score) AS score, COUNT(valid=1) AS played FROM gamesessions AS gamesessions WHERE valid=1 AND sessiontype IN (1,2) GROUP BY gamename; EXPLAIN SELECT gives me this, also there is an index on the columns valid and sessiontype id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE gamesessions ref valid_sess valid_sess 1 const 55003 Using where; Using temporary; Using filesort Can this be optimized to run faster Appreciate your time Kishore Jalleda There are several index changes you could make to optimize this particular query. The problem is, if we create too many indexes your INSERT time will begin to tank and your disk space will become scarce. This section of the manual definitely applies to your situation: http://dev.mysql.com/doc/refman/4.1/en/group-by-optimization.html and more generally http://dev.mysql.com/doc/refman/4.1/en/query-speed.html This section of the manual may also be able to help: http://dev.mysql.com/doc/refman/4.1/en/optimizing-database-structure.html in particular, read http://dev.mysql.com/doc/refman/4.1/en/indexes.html and http://dev.mysql.com/doc/refman/4.1/en/multiple-column-indexes.html The problem is, we can make that one query VERY fast at the expense of all sorts of other things. Query optimization is an art, a balancing act. You have to know what you are giving up when you ask for certain performance increases. I just don't have enough information about your overall query patterns to make an informed decision on which optimization will work for this particular query without penalizing other queries in the process. What you have to be able to answer is: How important is the speed of this one query compared to everything else going on in the database as a whole. Nobody can tell you that but you. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Optimizing GROUP BY
That was an excellent reply, I always see you helping so many people, keep the great work going .. Sincerely, Kishore Jalleda On 10/13/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Kishore Jalleda [EMAIL PROTECTED] wrote on 10/13/2005 02:25:52 PM: Hi All, I have a query which takes approximately 0.5 seconds to execute , it is as follows SELECT gamename, MAX(score) AS score, COUNT(valid=1) AS played FROM gamesessions AS gamesessions WHERE valid=1 AND sessiontype IN (1,2) GROUP BY gamename; EXPLAIN SELECT gives me this, also there is an index on the columns valid and sessiontype id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE gamesessions ref valid_sess valid_sess 1 const 55003 Using where; Using temporary; Using filesort Can this be optimized to run faster Appreciate your time Kishore Jalleda There are several index changes you could make to optimize this particular query. The problem is, if we create too many indexes your INSERT time will begin to tank and your disk space will become scarce. This section of the manual definitely applies to your situation: http://dev.mysql.com/doc/refman/4.1/en/group-by-optimization.html and more generally http://dev.mysql.com/doc/refman/4.1/en/query-speed.html This section of the manual may also be able to help: http://dev.mysql.com/doc/refman/4.1/en/optimizing-database-structure.html in particular, read http://dev.mysql.com/doc/refman/4.1/en/indexes.html and http://dev.mysql.com/doc/refman/4.1/en/multiple-column-indexes.html The problem is, we can make that one query VERY fast at the expense of all sorts of other things. Query optimization is an art, a balancing act. You have to know what you are giving up when you ask for certain performance increases. I just don't have enough information about your overall query patterns to make an informed decision on which optimization will work for this particular query without penalizing other queries in the process. What you have to be able to answer is: How important is the speed of this one query compared to everything else going on in the database as a whole. Nobody can tell you that but you. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Innodb open files issue
Hi, I have mysql 4.1.14 on solaris 9 and have just switched to using the innodb_file_per_table option. Previously I was using MyISAM tables and created about 700 tables. The rlim_fd_cur and rlim_fd_max kernel paramteres are set to 256. I'm getting an error from innodb that it is running out of file descriptors. I did not get this when using MyISAM tables. Does the innodb not use the table cache when innodb_file_per_table is set? Where is this documented? thanks, Jo
Looking for better perfomance fulltext database
Hi all, I use MYSQL, it is good, I like it. But when comes to big tables, and fulltext, it is really slow. I have in table up to 100millions rows, and 1 querry fulltext (limit 5) last 5-200 seconds. I use it on FreeBSD, I look for something better, thinking about Firebird or Postgre. Could you give me an advice ? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL sw for reverse engineering
Bruno Cochofel wrote: Thank you all for the posts. Can anyone explain to me how can I connect DBDesigner4 to MySQL 4.x? I've never used MySQL 3.x and I believe there's some kind off trick to connect DBDesigner4 to MySQL 4.x because the program doesn't support the MySQL 4.x Or the ODBC connection solution to MySQL 4.x... Thanks, Bruno I've got it hooked up to 4.0.? and 4.1.?. I don't remember having any problems with it, but it has been a while since I set it up. One database is local to the machine that DBD runs on and the other is on another box. I've got both of the DB servers set up in my DNS server and that's how I've got them listed in the connection settings ( ie server = mysql or mysql2 ). Dunno if that's made a difference or not. I've got DBD 4.0.5.4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb open files issue
Jonathan, http://dev.mysql.com/doc/refman/5.0/en/innodb-start.html innodb_open_files This option is relevant only if you use multiple tablespaces in InnoDB. It specifies the maximum number of .ibd files that InnoDB can keep open at one time. The minimum value is 10. The default is 300. The file descriptors used for .ibd files are for InnoDB only. They are independent of those specified by the --open-files-limit server option, and do not affect the operation of the table cache. That parameter is completely independent of the MySQL table cache. Regards, Heikki Innobase/Oracle - Original Message - From: Jonathan Stockley [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, October 13, 2005 10:49 PM Subject: Innodb open files issue --_=_NextPart_001_01C5D02F.2BE09467 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Hi, I have mysql 4.1.14 on solaris 9 and have just switched to using the innodb_file_per_table option. Previously I was using MyISAM tables and created about 700 tables. The rlim_fd_cur and rlim_fd_max kernel paramteres are set to 256. =20 I'm getting an error from innodb that it is running out of file descriptors. I did not get this when using MyISAM tables. =20 Does the innodb not use the table cache when innodb_file_per_table is set? Where is this documented? =20 thanks, Jo --_=_NextPart_001_01C5D02F.2BE09467-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL sw for reverse engineering
From http://www.mysqlusers.com/msg/37105.html we have:
Reset the password to pre-4.1 style for each user that needs to use a
pre-4.1 client program. This can be done using the SET PASSWORD
statement and the OLD_PASSWORD() function:
mysql SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');
Edward Vermillion wrote:
Bruno Cochofel wrote:
Thank you all for the posts. Can anyone explain to me how can I connect
DBDesigner4 to MySQL 4.x? I've never used MySQL 3.x and I believe
there's some kind off trick to connect DBDesigner4 to MySQL 4.x because
the program doesn't support the MySQL 4.x
Or the ODBC connection solution to MySQL 4.x...
Thanks,
Bruno
I've got it hooked up to 4.0.? and 4.1.?. I don't remember having any
problems with it, but it has been a while since I set it up. One
database is local to the machine that DBD runs on and the other is on
another box. I've got both of the DB servers set up in my DNS server
and that's how I've got them listed in the connection settings ( ie
server = mysql or mysql2 ). Dunno if that's made a difference or not.
I've got DBD 4.0.5.4
Re: MySQL sw for reverse engineering
Bruno Cochofel wrote:
From http://www.mysqlusers.com/msg/37105.html we have:
Reset the password to pre-4.1 style for each user that needs to use a
pre-4.1 client program. This can be done using the SET PASSWORD
statement and the OLD_PASSWORD() function:
mysql SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');
[snip]
That actually looks like something I may have done. I *did* upgrade to
4.0 from 3.?, and may have had to do that to keep it all working at the
time(PHP 4 and all). When I installed 4.1, I just copied the DB files
over to that server so all of that would have gone with it at the time.
Like I said, it was a while back when I installed DBD and even further
back when I updated mysql. I've slept since then. :D
Hope that works for you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innobase sold to Oracle ?
Hi Doug, Thanks for the reply. That is more or less what I was thinking too. (especially the ANT rant ;-) I would be looking forward to read the paper in case its published one day. Best regards Nils Valentin Tokyo / Japan http://www.be-known-online.com Quoting Douglas K. Fischer [EMAIL PROTECTED]: [EMAIL PROTECTED] wrote: speculating about that IMO. What Oracle will do with Innobase is Oracle's matter, not much use in Sorry, I am going to disagree with that ;-), I am VERY WELL INTERESTED how the environment around Innobase / Innodb will change - and with it any support options and other impacts that might have for our company or any of our customers. I of course cannot speak for MySQL or any of the other affected parties, but I have been very interested in this issue as we have many high-profile clients who are going to start asking the same kinds of questions very soon. As such, I have tried to be on top of this as much as possible and will share what I believe: Short-term, there is no real impact. MySQL and Innobase have an existing contract that runs (approximately - don't know the exact date) through the end of next year. As such, the existing terms will stay in place through then at the minimum. The only potential impact before then would involve the level of participation in Innobase employees with troubleshooting, patching, and new InnoDB features. However, from what I have heard third-hand, Heikki is continuing to assist as before. All in all, I do not believe any MySQL customers have anything to be concerned with before the end of next year. I agree that at this point in time, it is impossible to tell exactly what Oracle may or may not do with InnoDB. The exact terms of the purchase of Innobase have not been disclosed, so it is unknown if they are going to just acquire the rights to InnoDB and send Innobase employees packing, if they are going to employ the Innobase employees, if they are going to continue active development of InnoDB, etc. Any guesses or statements by anyone other than upper management at Oracle is mere speculation. I can come up with a number of possible scenarios, but none of them result in a dead in the water situation for any MySQL customers. Also, there are a number of things MySQL can do to hedge their bets against any decision Oracle may make regarding the future disposition of InnoDB. My advice is to not worry too much about the Oracle situation for now. Give MySQL some time to work through their options and let the MySQL-Oracle relationship do what it may. I have started working on an internal whitepaper to discuss the situation without all the FUD some journalists and some other DB companies (think ANTs) have started throwing around and to present a list of what I believe to be the most likely outcomes based upon what is publicly known and what options exist for MySQL and Oracle to take in this matter. Depending upon what information may be out by then and on whether or not I can get permission from upper management to release this paper into the open, I would be willing to share my analysis openly. I certainly understand the concerns that people have with this development, but it is truly too early to make a knowledgable call - to do so would be akin to speculating the World Series winner on opening day. Cheers, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does the MySQL mailing list use MySQl to manage the mailing list? [SOLVED]
The lists are managed using ezmlm-idx (http://www.ezmlm.org/). The subscription lists are kept in a MySQL database, the message archives are simply kept in the filesystem, the index of messages used by the news server and web archive are kept in MySQL, and the search index is kept in MySQL. Thank you. That answer is very concise and helpful. Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upload images / mp3 more Than 1 MB capacity ---- please help
I tend to disagree - at my place of employment, a newspaper, we have hundreds of gigabytes of BLOB data (ad and page layouts digital artwork) stored in SQL databases. Granted we are using Sybase for that and not MySQL but there are a lot of advantages to it - access control, change control and tracking, easy insertion and deletion, and access from any client right through the database driver so you can repurpose content more easily. I have stored smaller amounts of BLOB data in MySQL for side projects in the past, without trouble. Do understand though that storing BLOB data in the db will impact your table optimization and repair times as well as backup/recover times. You might also want to plan ahead and devise a scheme to spread the data among several tables, to allow optimization of just one table a day (for example) and even to allow you to split storage across multiple devices should your database get too large. Dan At 3:09 PM +0200 10/13/05, Jigal van Hemert wrote: Kane Wilson wrote: But when i try to store little but huge gif files it wont store . First of all, use the method described at http://www.php.net/manual/en/features.file-upload.php for a safe way to handle file uploads. It could be that you run into a server limit which will show up if you use that method. I do think that you exceeded the max_allowed_packet size for MySQL queries which has a default value of 1048576 (=1MB). You can increase this number (must be done in both client and server!!), but it is usually best to store huge files in a file system and not in a database. Kind regards, Jigal. -- 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]
Raw devices and MySQL
Hi Listers, Does anybody know if the MyISAM engine (apart from InnoDB) allows the use of raw disk space rather than having cooked files? If not, is this feature likely to be included in a future release? I had a quick scour of the MySQL website but can't seem to find a page with upcoming features. Is there such a beast? Thanks David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax
Re: How to test the MySQL Server (windows version) ?
Hi, joerg Thank you very much! But i want to know more about this problem. I find a perl script about mysql server's test in the mysql-test subdirectory(/mysql-test/mysql-test-run.pl). Is this the test script written by perl what could replace the shell script(/mysql-test/mysql-test-run.sh) in Windows mentioned in your e-mail? Could it be rightly run in windows now? Or it cann't be run due to the unfinished work? Are the perl scripts that mentioned in your e-mail included in the MySQL Server's Unix source distributions(mysql-5.0.12-beta-linux) ? Are the perl test scripts only used in Mysql's internal works now? Best regards luming _ 享用世界上最大的电子邮件系统― MSN Hotmail。 http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Charset issue
Hi, I've installed MySQL 4.1.10 and phpMyAdmin-2.6.4-rc1 on a RHEL4 box. And I found that phpMyAdmin forces the character set of pages utf-8. Though I knew that phpMyAdmin-2.5.7 can work well with gb2312, I want to make phpMyAdmin work well too. Anybody knows that how to make phpMyAdmin work with non-utf8 encoding ? Thanks. -- Best regards, Huisi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
