Re: Dumping MySQL result set to a spreadsheet
Neale Banks wrote: Should we presume you haven't noticed the, admittedly not widely publicised, Database Access features of OpenOffice.org? You can use odbc or jdbc (I can only vouch for the latter, on Linux) to connect to the mysql server from within OOo. Should work just fine on your XP box. To get started, open a new spreadseet (or any other kind of document) and select View-Data Sources (or F4). Once you've defined the database connection and the query your original question reduces to dragging the query result and dropping it into the spreadsheet :-) Hmmm, I've never quite been able to get there (00o 1.1.0): I'm able to connect to MySQL (easily) but I get a selection of tables for the wrong database. So close! Any hints? A while back I actually splurged and spent $5.50 on a Perl program someone wrote to do this [http://www.churm.com/konvertr/index.php] and it works just fine - writing to Excel. Very quick, beats point and click. I guess I should have written something like that myself, but it's hard to beat 5 bucks, and I just could not write such a program fast enough to make doing it myself a better deal (and if one writes all the tools along the way, one may lose sight of the goal). Eric Pederson Eric at zomething dot com P.S. I also wanted something to quick summarize my field definitions across the database for review, so I made a little script in Python which produces a HTML summary of the table definitions. I find it helpful documentation. http://www.zomething.com/describe_MySQL.htm You need Python on your machine to run it. http://www.python.org Feedback invited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another loss of mysql
After two years of developing a new system based on MySQL for the company i work at... it turned out to face a failure.. not because the performance.. nor the price... finally the company choose Oracle Application Suite because the Oracle brand it self is a guarantee to bussiness competition i mourn for the dead of my mysql project good bye.. good luck -- Regards Leonardus Setiabudi IT Project Coordinator PT Bina San Prima, www.binasanprima.com http://gtw.binasanprima.com/~leo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help for pdf file
I don't know this related to php subject or mysql subject, but does anybody knows how to create a pdf file from php mysql? Or maybe somebody can show me a url contain about it which is clear. Coz I found some url discussing about it but quite confusing. Thanks in advance ~Elle~
The very first day of march :-) (select (curdate()-1);)
Hi, Look at what happened today : mysql select curdate(); ++ | curdate() | ++ | 2004-03-01 | ++ 1 row in set (0.00 sec) mysql select (curdate()-1); +---+ | (curdate()-1) | +---+ | 20040300 | +---+ 1 row in set (0.00 sec) mysql Until yesterday select (curdate()-1); worked fine. I use MySQL version 4.0.12. Does anybody know something about? Ciao Danilo CONFIDENTIALITY NOTICE This message and its attachments are addressed solely to the persons above and may contain confidential information. If you have received the message in error, be informed that any use of the content hereof is prohibited. Please return it immediately to the sender and delete the message. Should you have any questions, please contact us by replying to [EMAIL PROTECTED] Thank you www.telecomitalia.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help for pdf file
I don't know this related to php subject or mysql subject, but does anybody knows how to create a pdf file from php mysql? http://www.fpdf.org is a free PHP library which can help you produce pdf files. You have to write your own functions for formatting tables, etc. but the actual creation of the pdf file is handled by the library. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help for pdf file
At 17:01 +0700 1-03-2004, Elly Wisata wrote: I don't know this related to php subject or mysql subject, but does anybody knows how to create a pdf file from php mysql? Or maybe somebody can show me a url contain about it which is clear. Coz I found some url discussing about it but quite confusing. Thanks in advance ~Elle~ http://www.fpdf.org/ Santino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The very first day of march :-) (select (curdate()-1);)
Campanella Danilo wrote: Hi, Look at what happened today : mysql select curdate(); ++ | curdate() | ++ | 2004-03-01 | ++ 1 row in set (0.00 sec) mysql select (curdate()-1); +---+ | (curdate()-1) | +---+ | 20040300 | +---+ 1 row in set (0.00 sec) mysql Until yesterday select (curdate()-1); worked fine. Are you sure of that? As far as I know, you have to use a function like date_add() or date_sub() to to date addition / subtraction. What you're doing is turning 2004-03-01 into the number 20040301 and then subtracting 1 from it, giving you 20040300, which is correct. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The very first day of march :-) (select (curdate()-1);)
Your select works fine within the month to the exception of the first day because curdate()-1 is treated as a numeric field (see curdate in http://www.mysql.com/doc/en/Date_and_time_functions.html) The following seems to be what you want to do: mysql select date_format(curdate()- interval 1 day,'%Y%m%d'); +-+ | date_format(curdate()- interval 1 day,'%Y%m%d') | +-+ | 20040229| +-+ 1 row in set (0.00 sec) or, if you want the '-' mysql select date_format(curdate()- interval 1 day,'%Y-%m-%d'); +---+ | date_format(curdate()- interval 1 day,'%Y-%m-%d') | +---+ | 2004-02-29| +---+ 1 row in set (0.00 sec) Bernard On Monday 01 March 2004 05:15, Campanella Danilo wrote: Hi, Look at what happened today : mysql select curdate(); ++ | curdate() | ++ | 2004-03-01 | ++ 1 row in set (0.00 sec) mysql select (curdate()-1); +---+ | (curdate()-1) | +---+ | 20040300 | +---+ 1 row in set (0.00 sec) mysql Until yesterday select (curdate()-1); worked fine. I use MySQL version 4.0.12. Does anybody know something about? Ciao Danilo CONFIDENTIALITY NOTICE This message and its attachments are addressed solely to the persons above and may contain confidential information. If you have received the message in error, be informed that any use of the content hereof is prohibited. Please return it immediately to the sender and delete the message. Should you have any questions, please contact us by replying to [EMAIL PROTECTED] Thank you www.telecomitalia.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hardware devices price-list
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird behaviour
Hello I am using MySQL 4.0.18 and i have come accross weird behaviour. I created the databas and user like this: CREATE DATABASE dev_project; GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,INDEX,ALTER,CREATE TEMPORARY TABLES,LOCK TABLES ON dev_project.* TO [EMAIL PROTECTED] IDENTIFIED BY somepass; FLUSH PRIVILEGES; So, user has all privileges set in mysql.db table (except Grant_priv) an absolutely none set in mysql.user table. So far so good. BUT: when i log into mysql as that fresh user I cat do CREATE DATABASE `dev?project`; CREATE DATABASE `dev-project`; CREATE DATABASE `devaproject`; CREATE DATABASE `devbproject`; CREATE DATABASE `devcproject`; etc... AND! USE devaproject; AND!! CREATE TABLE test (id int not null); INSERT INTO test SET id=1; SELECT * FROM test; ++ | id | ++ | 1 | ++ 1 row in set (0.00 sec) Now am I missing something - is this by design? or it is a bug in MySQL grant system? Best regards, Bostjan Skufca system administrator Domenca d.o.o. Phone: +386 4 5835444 Fax: +386 4 5831999 http://www.domenca.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weekly activity report
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Auto-Confirmation
Thank you for submitting your request to Quest Software Technical Support. We are unable to process your request because our records indicate that you are not registered for Technical Support. To register, logon to our Supportlink web site http://www.quest.com/support , and self-register from the web. Or, you can phone Quest at one of the numbers below to register. Once you have registered, you can submit your request within Supportlink, by email at [EMAIL PROTECTED], by phone at one of the numbers below. Please do not reply to this email. If this is an URGENT matter please contact Quest Technical Support via telephone at one of the numbers listed below. Thank you, Quest Software Technical Support Searchable Knowledgebase and FAQs located at http://www.quest.com/support Quest Software Technical Support - Canada 902.442.5700 Quest Software Technical Support - United Kingdom 44.1628.601007 Quest Software Technical Support - United States 949.754.8000 Original Message From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Mon, 1 Mar 2004 18:32:26 +0530 To: [EMAIL PROTECTED] Subject: Re: Excel file
Re: Weird behaviour
Same for version 3.23.58. On Monday 01 of March 2004 13:47, Bostjan Skufca (at) domenca.com wrote: Hello I am using MySQL 4.0.18 and i have come accross weird behaviour. I created the databas and user like this: CREATE DATABASE dev_project; GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,INDEX,ALTER,CREATE TEMPORARY TABLES,LOCK TABLES ON dev_project.* TO [EMAIL PROTECTED] IDENTIFIED BY somepass; FLUSH PRIVILEGES; So, user has all privileges set in mysql.db table (except Grant_priv) an absolutely none set in mysql.user table. So far so good. BUT: when i log into mysql as that fresh user I cat do CREATE DATABASE `dev?project`; CREATE DATABASE `dev-project`; CREATE DATABASE `devaproject`; CREATE DATABASE `devbproject`; CREATE DATABASE `devcproject`; etc... AND! USE devaproject; AND!! CREATE TABLE test (id int not null); INSERT INTO test SET id=1; SELECT * FROM test; ++ | id | ++ | 1 | ++ 1 row in set (0.00 sec) Now am I missing something - is this by design? or it is a bug in MySQL grant system? Best regards, Bostjan Skufca system administrator Domenca d.o.o. Phone: +386 4 5835444 Fax: +386 4 5831999 http://www.domenca.com -- Z lepimi pozdravi, Bostjan Skufca sistemski administrator Domenca d.o.o. Tel: +386 4 5835444 Fax: +386 4 5831999 http://www.domenca.si -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: unique values across more than one column
Hi, Could you just break out the email information into its own table? Then you could have columns like the following: UserID (foreign key), email (varchar), type (int: 1=primary email, 2=alias email). Then you can just have the email column defined as a unique index and relate the email to the user table using a foreign key. Regards, Colin. -Original Message- From: Anand Buddhdev [mailto:[EMAIL PROTECTED] Sent: 27 February 2004 08:50 To: [EMAIL PROTECTED] Subject: unique values across more than one column Hi everyone, I'm using mysql version 3.23.58, on Fedora core 1 (the default supplied on the system). I have searched the mailing list archives, and google, for my query, but have not yet found an answer. Does anyone know if it's possible to define 2 columns in a table, with a constraint that will ensure that values on both columns are unique? For example, if I have columns a and b, then if I insert value x in column a, then I may not insert value x again in EITHER column a or column b again. I'm trying to develop a structure for a table that will hold a user's primary email address, and an alias, and I'd like to have a column called address and a column called alias, and of course, there must be no address or alias duplication. This allows addition and removal of an address and its alias in one insert, and if the insert fails, then we know there's duplication, and return an error message. I have thought of other ways around this issue, but my ideal solution would be as above. If this is not possible, then I will go back to my other (IMHO less elegant) solutions. -- Anand Buddhdev Celtel International -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Design Advice?
If you are not using 5.0 and don't want to switch to 5.0, then using database view will also work. This link tells how to create views: http://www.mysql.com/documentation/maxdb/6d/117c44d14811d2a97400a0c9449261/content.htm What version does this work for? We're using 4.0.14 and it doesn't like CREATE VIEW in the least... thnx, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dumping MySQL result set to a spreadsheet
SELECT rest of query into OUTFILE some place the mysql user can write to Should get you a tab delimited file there are more options for using different delimiters and field encapsulations, etc. Its in the manual. curtis On Sun, 29 Feb 2004, Joshua Beall wrote: Hi All, I am wondering if anyone can point me to a utility that will let me dump the result of a MySQL query to a file I can open in MS Excel or (preferably) OpenOffice.org Calc. phpMyAdmin lets you do this if you want to dump a SELECT * FROM table, but if I want to fine tune it I cannot (unless I am missing something) MySQL CC lets me save the result of a query to a test file right click-save results, but this format does not quite conform to either Excel or Calc's text format. I could fix it by hand, but before I would do that, I think I would write a PHP script that would generate the right output. But before I spend any time working on doing that, I am wondering if anyone knows of a way to do this already? I am running MySQL 4.0.12-standard on Mandrake 9.2 on the server, and my workstation is an XP Pro box. I use MySQL CC 0.9.1-beta and phpMyAdmin 2.5.0, but am completely willing to upgrade more recent versions of either of these tools, or try a new one. Thanks for any feedback! Sincerely, -Josh p.s. How do I use my newsreader to post to these mailing lists? I see that news.gmane.org seems to carry the mysql lists, but it would not let me post, telling me (even after I was subscribed to the list with the same email address I use in my newsreader): Outlook Express could not post your message. Subject 'Dumping MySQL result set to a spreadsheet', Account: 'news.gmane.org', Server: 'news.gmane.org', Protocol: NNTP, Server Response: '441 You are not allowed to approve postings', Port: 119, Secure(SSL): No, Server Error: 441, Error Number: 0x800CCCA9 -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange bug(?) with Phrase search in BOOLEAN MODE
Sergei, but this is a phrase search. The original query is:
match (keywords) against ('16-bit Touch' IN BOOLEAN MODE)
So shouldn't all words that are actually searched on be present
in a particular order?
Bill
Sergei Golubchik wrote:
Hi!
On Feb 27, Haitao Jiang wrote:
Thanks! That was what I guessed. But how to explain
16-bit Touch doesn't match records with 32-bit
Touch in the keywords? It just returned all the
records with 16-bit Touch, i.e. 16-bit seems does
count.
Because the presense of 16-bit substring is verified as a
post-processing. Similar to
MATCH ... AGAINST ('touch') AND ... LIKE '%16-bit%'
and if you'd have min_word_len=3 the query would be executed
similar to
MATCH ... AGAINST ('+touch +bit') AND ... LIKE '%16-bit%'
that is rows would be selected based on an index lookup for two words,
and as a post-processing a substring search would filter out rows
without 16-bit substring.
Regards,
Sergei
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird behaviour
This is documented behavior http://www.mysql.com/doc/en/GRANT.html: Please note: the `_' and `%' wildcards are allowed when specifying database names in GRANT statements that grant privileges at the global or database levels. This means that if you wish to use for instance a `_' character as part of a database name, you should specify it as `\_' in the GRANT command, to prevent the user from being able to access additional databases matching the wildcard pattern, for example, GRANT ... ON `foo\_bar`.* TO Michael Bostjan Skufca (at) domenca.com wrote: Hello I am using MySQL 4.0.18 and i have come accross weird behaviour. I created the databas and user like this: CREATE DATABASE dev_project; GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,INDEX,ALTER,CREATE TEMPORARY TABLES,LOCK TABLES ON dev_project.* TO [EMAIL PROTECTED] IDENTIFIED BY somepass; FLUSH PRIVILEGES; So, user has all privileges set in mysql.db table (except Grant_priv) an absolutely none set in mysql.user table. So far so good. BUT: when i log into mysql as that fresh user I cat do CREATE DATABASE `dev?project`; CREATE DATABASE `dev-project`; CREATE DATABASE `devaproject`; CREATE DATABASE `devbproject`; CREATE DATABASE `devcproject`; etc... AND! USE devaproject; AND!! CREATE TABLE test (id int not null); INSERT INTO test SET id=1; SELECT * FROM test; ++ | id | ++ | 1 | ++ 1 row in set (0.00 sec) Now am I missing something - is this by design? or it is a bug in MySQL grant system? Best regards, Bostjan Skufca system administrator Domenca d.o.o. Phone: +386 4 5835444 Fax: +386 4 5831999 http://www.domenca.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BOM Select statement
I have a file called STRUCTUR which is typical of any Bill-Of-Materials in an MRP system. The format is similar to this: STRUCTUR.ITEM, STRUCTUR.QTY, STRUCTUR.PARENT_ITEM You define multi-level material lists for items and for components of those items ... Here is an example ITEM; QTY; PARENT_ITEM harddrive, 2, mynewpc modem, 1, mynewpc case, 1, mynewpc monitor, 1, mynewpc screws, 10, case sheetmetal, 4, case powersupply, 1, case capacitor, 10, powersupply transformer, 1, powersupply screws, 5, powersupply What I want is to know how to build a SELECT statement which will return all components of mynewpc and navigate down the bill-of-materials and also return all the components for the case, the powersupply and even the parts on the powersupply. I'm trying to write an indented bill-of-materials with CrystalReports and the SQL is stumping me. Thanks Wesley Baker
Re: Another loss of mysql
There are many companies that develop on MySQL then market the product with Oracle for the same reasons you statethe name. curtis On Mon, 1 Mar 2004, Leo wrote: After two years of developing a new system based on MySQL for the company i work at... it turned out to face a failure.. not because the performance.. nor the price... finally the company choose Oracle Application Suite because the Oracle brand it self is a guarantee to bussiness competition i mourn for the dead of my mysql project good bye.. good luck -- Regards Leonardus Setiabudi IT Project Coordinator PT Bina San Prima, www.binasanprima.com http://gtw.binasanprima.com/~leo -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird behaviour
Excuse my blindness please! Thank you, Bostjan On Monday 01 of March 2004 16:19, Michael Stassen wrote: This is documented behavior http://www.mysql.com/doc/en/GRANT.html: Please note: the `_' and `%' wildcards are allowed when specifying database names in GRANT statements that grant privileges at the global or database levels. This means that if you wish to use for instance a `_' character as part of a database name, you should specify it as `\_' in the GRANT command, to prevent the user from being able to access additional databases matching the wildcard pattern, for example, GRANT ... ON `foo\_bar`.* TO Michael Bostjan Skufca (at) domenca.com wrote: Hello I am using MySQL 4.0.18 and i have come accross weird behaviour. I created the databas and user like this: CREATE DATABASE dev_project; GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,INDEX,ALTER,CREATE TEMPORARY TABLES,LOCK TABLES ON dev_project.* TO [EMAIL PROTECTED] IDENTIFIED BY somepass; FLUSH PRIVILEGES; So, user has all privileges set in mysql.db table (except Grant_priv) an absolutely none set in mysql.user table. So far so good. BUT: when i log into mysql as that fresh user I cat do CREATE DATABASE `dev?project`; CREATE DATABASE `dev-project`; CREATE DATABASE `devaproject`; CREATE DATABASE `devbproject`; CREATE DATABASE `devcproject`; etc... AND! USE devaproject; AND!! CREATE TABLE test (id int not null); INSERT INTO test SET id=1; SELECT * FROM test; ++ | id | ++ | 1 | ++ 1 row in set (0.00 sec) Now am I missing something - is this by design? or it is a bug in MySQL grant system? Best regards, Bostjan Skufca system administrator Domenca d.o.o. Phone: +386 4 5835444 Fax: +386 4 5831999 http://www.domenca.com -- Best regards, Bostjan Skufca system administrator Domenca d.o.o. Phone: +386 4 5835444 Fax: +386 4 5831999 http://www.domenca.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: my.cnf Setup!!
Hello List: I have setup MySQL and it is working fine. I tested connection to MySQL. Now I have gone one step futher and added a password for mysql, as follows: shell ./mysqladmin -u root password mysqlpw Now I want to setup myc.cnf. Our SQL server is a standalone server (RH9, Dual CPU, 1.5GB Memory, RAID1, etc.), so I copied the my-large.cnf to /etc/my.cnf. After looking into my.cnf, I decided to leave it as it is (no change). I do want to add the (1) name of the server and (2) password to the my.cnf. Here I am struck for last couple of days. Finally, here is my question; I want to execute the command: shell mysql -h data -u mysql password=mysqlpw Where (from above command), mysql server is data and password is mysqlpw. What lines do I need to enter in my.cnf for the above command to work? I have tried everything I know! HELP!! KIrti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A problem with access to data source, please help!!!!
Hi, I am trying to configure Open CMS 5.0.0 with mysql. But while Database and and table creation I am getting following error. Could no connect to database via: jdbc:mysql://localhost:3306/ java.sql.SQLException: Server configuration denies access to data = source --- java.lang.NullPointerException I tested mysql with the root password I assigned using mysqladmin -u root -p, so it looks like mysql is setup correctly.However when trying Also I tested the GRANTS of root and I get += + | Grants for [EMAIL PROTECTED] = | += + | GRANT ALL PRIVILEGES ON *.* TO 'root'@'pcepaip30' IDENTIFIED BY PASSWORD '*F65E684A09D85F1DD4574279566B9E738DD597E7' WITH GRANT OPTION | + + 1 row in set (0.00 sec) I need a help, thanks in advance Rafael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange bug(?) with Phrase search in BOOLEAN MODE
Hi!
On Mar 01, William Au wrote:
Sergei, but this is a phrase search. The original query is:
match (keywords) against ('16-bit Touch' IN BOOLEAN MODE)
So shouldn't all words that are actually searched on be present
in a particular order?
Ok, sorry.
If the original query is '16-bit Touch' then it would be executed
functionally similar to
MATCH keywords AGAINST ('Touch' IN BOOLEAN MODE)
AND keywords LIKE '%16-bit Touch%'
Sergei Golubchik wrote:
Hi!
On Feb 27, Haitao Jiang wrote:
Thanks! That was what I guessed. But how to explain
16-bit Touch doesn't match records with 32-bit
Touch in the keywords? It just returned all the
records with 16-bit Touch, i.e. 16-bit seems does
count.
Because the presense of 16-bit substring is verified as a
post-processing. Similar to
MATCH ... AGAINST ('touch') AND ... LIKE '%16-bit%'
and if you'd have min_word_len=3 the query would be executed
similar to
MATCH ... AGAINST ('+touch +bit') AND ... LIKE '%16-bit%'
that is rows would be selected based on an index lookup for two words,
and as a post-processing a substring search would filter out rows
without 16-bit substring.
Regards,
Sergei
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The very first day of march :-) (select (curdate()-1);)
Bernard Clement wrote: Your select works fine within the month to the exception of the first day because curdate()-1 is treated as a numeric field (see curdate in http://www.mysql.com/doc/en/Date_and_time_functions.html) The following seems to be what you want to do: mysql select date_format(curdate()- interval 1 day,'%Y%m%d'); +-+ | date_format(curdate()- interval 1 day,'%Y%m%d') | +-+ | 20040229| +-+ 1 row in set (0.00 sec) or, if you want the '-' mysql select date_format(curdate()- interval 1 day,'%Y-%m-%d'); +---+ | date_format(curdate()- interval 1 day,'%Y-%m-%d') | +---+ | 2004-02-29| +---+ 1 row in set (0.00 sec) You don't really need date_format for the latter, as you've specified the default format. You could just use: mysql select curdate() - interval 1 day; ++ | curdate() - interval 1 day | ++ | 2004-02-29 | ++ 1 row in set (0.00 sec) Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf Setup!!
Kirti S. Bajwa wrote: Hello List: I have setup MySQL and it is working fine. I tested connection to MySQL. Now I have gone one step futher and added a password for mysql, as follows: shell ./mysqladmin -u root password mysqlpw Now I want to setup myc.cnf. Our SQL server is a standalone server (RH9, Dual CPU, 1.5GB Memory, RAID1, etc.), so I copied the my-large.cnf to /etc/my.cnf. After looking into my.cnf, I decided to leave it as it is (no change). I do want to add the (1) name of the server and (2) password to the my.cnf. Here I am struck for last couple of days. For this, you want the .my.cnf file in your home directory. See http://www.mysql.com/doc/en/Option_files.html for details. Finally, here is my question; I want to execute the command: shell mysql -h data -u mysql password=mysqlpw Where (from above command), mysql server is data and password is mysqlpw. What lines do I need to enter in my.cnf for the above command to work? I have tried everything I know! HELP!! This should work without anything special in an option file, assuming that * mysqld server is running on the machine named data. * your hostname lookup will properly resolve data. * user [EMAIL PROTECTED], where client.machine is the name of the computer on which you are running the mysql client, has permission to access the mysqld server on data. If you could provide the exact error message you get when you try this, someone should be aable to diagnose the problem. Finally, I should point out that putting the password on the command line is insecure. It would be better to use shell mysql -h data -u mysql -p in which case you'll be prompted for the password. KIrti Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
When I woke up this morning...
Hi all, I'm a having a stange problem with mysql-nt on win2000. Querys that last night were taking just 2 seconds are now taking 1m 30secs +. I have a table... FieldType Collation NullKey Default Extra Privileges Comment --- - -- -- -- -- --- --- H_ID int(10) NULL PRI 0 select,insert,update,references H_Name varchar(255) latin1_swedish_ci YES MUL (NULL) select,insert,update,references SireID int(10) NULL YES MUL (NULL) select,insert,update,references GSireID int(10) NULL YES (NULL) select,insert,update,references Gender varchar(50) latin1_swedish_ci YES (NULL) select,insert,update,references DOB date NULL -00-00 select,insert,update,references Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed NullIndex_type Comment -- -- --- - --- -- -- -- --- horses 0 PRIMARY 1 H_ID A 40926(NULL) (NULL) BTREE horses 0 h_name 1 H_Name A (NULL)(NULL) (NULL) YES BTREE horses 1 SireID 1 SireID A 2273(NULL) (NULL) YES BTREE It has 40,000 recs. When I run select * from horses, it takes 1m 40secs to get them. anybody run in to this before? I'm considering reinstalling mysql. any toughts? please help Brian Power _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: When I woke up this morning...
Analyze your table. Your cardinality is invalid. Original Message On 3/1/04, 10:22:36 AM, Brian Power [EMAIL PROTECTED] wrote regarding When I woke up this morning...: Hi all, I'm a having a stange problem with mysql-nt on win2000. Querys that last night were taking just 2 seconds are now taking 1m 30secs +. I have a table... FieldType Collation NullKey Default Extra Privileges Comment --- - -- -- -- -- --- --- H_ID int(10) NULL PRI 0 select,insert,update,references H_Name varchar(255) latin1_swedish_ci YES MUL (NULL) select,insert,update,references SireID int(10) NULL YES MUL (NULL) select,insert,update,references GSireID int(10) NULL YES (NULL) select,insert,update,references Gender varchar(50) latin1_swedish_ci YES (NULL) select,insert,update,references DOB date NULL -00-00 select,insert,update,references Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed NullIndex_type Comment -- -- --- - --- -- -- -- --- horses 0 PRIMARY 1 H_ID A 40926(NULL) (NULL) BTREE horses 0 h_name 1 H_Name A (NULL)(NULL) (NULL) YES BTREE horses 1 SireID 1 SireID A 2273(NULL) (NULL) YES BTREE It has 40,000 recs. When I run select * from horses, it takes 1m 40secs to get them. anybody run in to this before? I'm considering reinstalling mysql. any toughts? please help Brian Power _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- 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: Communication link failure - MySQL Server seems to stop
hl said: Hi, What can I do to find the cause / correct the following problem? More often I get the following with Connector/J: error com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.io.EOFException Hlein, Can you post a testcase? Prepared statements are still in a state of flux...The code in 4.1.x from BitKeeper is probably the most stable right now. What happens when you run with MySQL-4.1 from BitKeeper instead of 5.0? -Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: When I woke up this morning...
Is there anything else running on the machine that might have used up a bunch of memory or CPU (check in your Task Manager - see how much free memory you have). Queries will run much faster if the data is cached in memory. If some other process has requested and received most of the memory in your machine, then all the data has to be read from disk. What type of tables? Have you recently added a bunch of data to your tables? Have you tried analyzing them to get the statistics etc back in line with reality? What about a reboot? Win2k is not a great platform for MySQL - I believe it runs under Cygwin, which adds overhead. David. - Original Message - From: Brian Power [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 01, 2004 8:22 AM Subject: When I woke up this morning... Hi all, I'm a having a stange problem with mysql-nt on win2000. Querys that last night were taking just 2 seconds are now taking 1m 30secs +. I have a table... FieldType Collation NullKey Default Extra Privileges Comment --- - -- -- -- -- --- --- H_ID int(10) NULL PRI 0 select,insert,update,references H_Name varchar(255) latin1_swedish_ci YES MUL (NULL) select,insert,update,references SireID int(10) NULL YES MUL (NULL) select,insert,update,references GSireID int(10) NULL YES (NULL) select,insert,update,references Gender varchar(50) latin1_swedish_ci YES (NULL) select,insert,update,references DOB date NULL -00-00 select,insert,update,references Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed NullIndex_type Comment -- -- --- - --- -- -- -- --- horses 0 PRIMARY 1 H_ID A 40926(NULL) (NULL) BTREE horses 0 h_name 1 H_Name A (NULL)(NULL) (NULL) YES BTREE horses 1 SireID 1 SireID A 2273(NULL) (NULL) YES BTREE It has 40,000 recs. When I run select * from horses, it takes 1m 40secs to get them. anybody run in to this before? I'm considering reinstalling mysql. any toughts? please help Brian Power _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- 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: Embeded MySQL for Java ???
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Victor Medina wrote: Hi!! No there isn't Although, you can try bdb(www.sleepycat.com), it comes with a java access class that will let you use bdb in a embeded environment, one thing i must say, is that it does not use jdbc, instead it uses it's own access methods, quite easy btw. GUYS!, it would be really nice to have an embeded access level trough java, think about it! ok? =) It is being planned, however it's not as straightforward as one would think if you still want to have JDBC-like access. There are also issues with Java threads and 'native' threads co-existing on some platforms. Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAQ2qitvXNTca6JD8RAi16AJ9szuvn7N21l6ZUlqHRbLH9ZR9TegCeKxZG zOnh484lj3wT4cAkLK86G18= =73QX -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BUG?
Arithmetic results in a value of zero but mysql is treating the value as 0. This is reproducible in a lot of different ways. Below is a pretty clear example. mysql select version(); +---+ | version() | +---+ | 4.0.17-max-nt | +---+ 1 row in set (0.00 sec) mysql create table t (a decimal(12,2) not null, b decimal(12,2) not null, c decimal(12,2) not null); Query OK, 0 rows affected (0.72 sec) mysql insert into t values (260.22,18.81,279.03); Query OK, 1 row affected (0.00 sec) mysql select a+b-c from t where a+b-c = 0; Empty set (0.00 sec) mysql select a+b-c as y from t having y = 0; Empty set (0.00 sec) mysql select a+b-c as y from t having y 0; +--+ | y| +--+ | 0.00 | +--+ 1 row in set (0.00 sec) mysql select a+b-c from t where a+b-c 0; +---+ | a+b-c | +---+ | 0.00 | +---+ 1 row in set (0.00 sec) Anybody? Thanks, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with 4.0.18
Andrea Riela wrote: Andrea Riela wrote: Could you help me? well, I've tryed with ktrace, see my kdump: http://www.nesys.it/kdump Andrea: Could you ktrace mysqld itself rather than mysqld_safe ? -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimising LIMITs
Hi all, I have what is probably a quite standard question and would love to know how you would all approach this scenario: I have a table in a database that has approx. 190,000 records in it. The table is currently 128MB in size and I'm happy that it is well constructed with no data duplication and sensible indexes. I'm using MySQL 3.28.58. and my question is about querying this volume of data efficiently. The table holds forum threads (several years worth) so a common query running on the table is to bring back the top 50 or 100 threads from a board within the forum. To do this I'm using a LIMIT on my query and for the paging through the data (i.e. the first 100 threads, the next 100, etc) I use the LIMIT n,x syntax. Threads are sorted by date (most recent to the top). This is fine and it works well but I'm concerned it's not the most efficient way to do this because the use of LIMIT is causing the whole table to be scanned each time. Here is a typical (simplified) query: SELECT * FROM thread WHERE thread.status='L' AND thread.boardid=1 ORDER BY created DESC LIMIT 100,50 This takes over 1.02 seconds to process. Running an EXPLAIN on my query shows that it's using one key (boardid), but in the Extra field it shows it is having to use a filesort on the data. 5701 rows were used in order to bring back the final 50 - that's every single thread for this board. What I'm trying to figure out is a more efficient way of selecting a block of 50 or 100 records from any point in my table without MySQL needing to sort/check them all first. One thought I did have was that the Primary Key on my table is called threadid - and I thought that instead of bring back the data in my original query, I could collect nothing but the thread IDs and then use a separate query that does something like: SELECT * FROM thread WHERE threadid IN (...) (where ... = all of the IDs previously selected). Would the fact that threadid is my primary key make the original LIMIT/sort faster? Any thoughts appreciated. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't execute ./bin/mysqld_safe from dir /var/lib
jda wrote: I had MySQL running just a couple days ago, but now it is not. The command and error: # /usr/local/mysql/support-files/mysql.server start Can't execute ./bin/mysqld_safe from dir /var/lib There is mysql directory under /var/lib, but I am not executing from there. If i go to /usr/local/mysql/support-files/ and execute the command mysql.server start from there, the result is the same. There is no log file in /usr/local/mysql/data. Somebody else had this problem (http://lists.mysql.com/mysql/144932) but no solution is there This is an unzip/untar (mysqlcc-0.9.4-linux-glibc22.tar.gz) of a binary installation. I have followed the instructions specified there. The weird part is that it was actually working for a couple days, and then it stopped. I had created a table using mysqlnavigator the day before it stopped working, that's the only difference. I have since removed the original installation and reinstalled it, following the instructions, but the result is the same. I can only assume this is an issue related to permission of access for the user/group mysql/mysql, but I have done everything according to the instructions, as evidenced by the fact that it was working at first. Any suggestions? Try debugging mysql.server by adding a few echo commands to see what it is actually trying to do. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisamchk operation not permitted problem
Rishi Pande wrote: Hello, I have been having trouble with rebuilding a database from backup. The backup consists of the MYD and frm files only. Therefore, I need to rebuild the myi file. I am using MySQL4.0.14 on Mac OS X. I use the myisamchk command to rebuild the database, in the following way shell/path/to/mysql/bin/myisamchk -r -q table_name However, I get an error (45), Operation not permitted when I try doing this. The files are owned by mysql:wheel with permissions 660. The data directory is mounted on a NFS disk which is running on a Alpha Server 4100. The OS is Compaq Digital Unix OSFI V5.1. I have rebuilt several tables before but have never faced this problem. Google did not yield much either. It would be great if someone could tell me what is going on/ what I can do to rebuild the table. Try rebuilding the table in a local directory, and then copy the file to the NFS mounted one. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Type TEXT
Are the TEXT column types padded out to their max length by spaces? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisamchk operation not permitted problem
The problem is the data file is about 25GB. The index file generally turns out to be at least half the size(~15GB)Unfortunately, I don't have 40GB free space on the machine. That's why the NFS server came into picture. I managed to start the table rebuild from a linux box running MySQL4.0.12. Some of the senior folks at the lab think that it may be because lockd and/or statd has issues running over NFS from a Mac OS X box. Anyone have experience with this before? Thanks for all your help. Rishi Try rebuilding the table in a local directory, and then copy the file to the NFS mounted one. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ smime.p7s Description: S/MIME cryptographic signature
Re: BUG?
[EMAIL PROTECTED] wrote: Arithmetic results in a value of zero but mysql is treating the value as 0. This is reproducible in a lot of different ways. Below is a pretty clear example. mysql select version(); +---+ | version() | +---+ | 4.0.17-max-nt | +---+ 1 row in set (0.00 sec) mysql create table t (a decimal(12,2) not null, b decimal(12,2) not null, c decimal(12,2) not null); Query OK, 0 rows affected (0.72 sec) mysql insert into t values (260.22,18.81,279.03); Query OK, 1 row affected (0.00 sec) mysql select a+b-c from t where a+b-c = 0; Empty set (0.00 sec) mysql select a+b-c as y from t having y = 0; Empty set (0.00 sec) mysql select a+b-c as y from t having y 0; +--+ | y| +--+ | 0.00 | +--+ 1 row in set (0.00 sec) mysql select a+b-c from t where a+b-c 0; +---+ | a+b-c | +---+ | 0.00 | +---+ 1 row in set (0.00 sec) Anybody? Isn't this an internal representation error - probably decimal handled as float? mysql select 260.22 + 18.81 - 279.03; +-+ | 260.22 + 18.81 - 279.03 | +-+ | 0.06 | +-+ 1 row in set (0.01 sec) - Cs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld crashes, stack trace doesn't help
Claus wrote: Hi all, we have rh 9.0 (kernel 2.4.20) mysqld 4.0.15 php 4.3.3 apache 1.3.29 and a quite large (10.000 unique visitors/day) server and messageboard (phpbb). every 2 or 3 days the server crashes (mysqld crashes, then all myslqd_safe and httpd processes crashes one after one) there is no stacktrace in the mysql-data-dir/xyz.err logfile. it simply shows a message that the database wasn't shutdown normally after the crash, nothing more. however there is a stacktrace in /var/log/messages of the crashed mysqld. I tried the use stack trace method of the mysql documentation (chapter D.1.4) with the stack trace of /var/log/messages but without success. it only shows some numbers and some ? at every zero offset (like 0x00f ). - is the /var/log/messages stack trace equivalent with the one which should show up at the err logfile of mysqld, so is it proper to make a bug report? - how can we find the problem on the server? there's the option to log every sql statement which might show up the error, but because this would slow down the server immensely we don't want to do this right now. RH 9.0 has done some experimentation with the thread library, and made it the default. I've seen out of memory problems with Resin on it, which I solved by fixing up the symlink on /lib/libpthread.so to point to the normal version instead of the experimental one. I suppose those experimentations would affect MySQL as well. If this is indeed the problem in your case, you can either fix up /lib/libpthread.so to point to something sane, or use a statically linked binary from www.mysql.com - the regular one (non-Max) is statically linked (on x86 Linux), but the Max one is linked dynamically to make UDFs work. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Losing indexes after building from dump.
Toby wrote: I finally figured out that we had been running large databases without indexes working at all. It turns out that if I use mysqldump to dump out a database, then drop/create the database and build from the dump, none of the indexes work. Things slow to a crawl. I found inadvertantly that by issuing any alter table query to a table, the indexes start working again. Is there any reason for this? Sounds like your are using --disable-keys ( or it gets turned on implicitly) when dumping, but alter table enable keys does not happen for some reason when you restore the table. Looks like a bug in mysqldump or the server itself. Upgrade to the latest version, if it still happens, file a bug report, and supplement your restore script with a workaround. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Type TEXT
Hello Jim, Monday, March 1, 2004, 5:44:22 PM, you wrote: JM Are the TEXT column types padded out to their max length by spaces? No, they're variable length and at a maximum of 65KB per row, be thankful of this! A 30 character string will take up 32 bytes for example. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BUG?
More of the same: mysql select 260.22 + 18.81 - 279.03; +-+ | 260.22 + 18.81 - 279.03 | +-+ |0.06 | +-+ 1 row in set (0.11 sec) mysql select 279.03 - 279.03; +-+ | 279.03 - 279.03 | +-+ |0.00 | +-+ 1 row in set (0.00 sec) mysql select 279.03 - (260.22 + 18.81); +---+ | 279.03 - (260.22 + 18.81) | +---+ | -0.05 | +---+ 1 row in set (0.00 sec) mysql select 279.03 - 260.22 - 18.81; +-+ | 279.03 - 260.22 - 18.81 | +-+ | -0.05 | +-+ 1 row in set (0.00 sec) mysql select 279.03 - 260.22; +-+ | 279.03 - 260.22 | +-+ | 18.809995 | +-+ 1 row in set (0.00 sec) On Mon, 2004-03-01 at 13:50, Fagyal, Csongor wrote: [EMAIL PROTECTED] wrote: Arithmetic results in a value of zero but mysql is treating the value as 0. This is reproducible in a lot of different ways. Below is a pretty clear example. mysql select version(); +---+ | version() | +---+ | 4.0.17-max-nt | +---+ 1 row in set (0.00 sec) mysql create table t (a decimal(12,2) not null, b decimal(12,2) not null, c decimal(12,2) not null); Query OK, 0 rows affected (0.72 sec) mysql insert into t values (260.22,18.81,279.03); Query OK, 1 row affected (0.00 sec) mysql select a+b-c from t where a+b-c = 0; Empty set (0.00 sec) mysql select a+b-c as y from t having y = 0; Empty set (0.00 sec) mysql select a+b-c as y from t having y 0; +--+ | y| +--+ | 0.00 | +--+ 1 row in set (0.00 sec) mysql select a+b-c from t where a+b-c 0; +---+ | a+b-c | +---+ | 0.00 | +---+ 1 row in set (0.00 sec) Anybody? Isn't this an internal representation error - probably decimal handled as float? mysql select 260.22 + 18.81 - 279.03; +-+ | 260.22 + 18.81 - 279.03 | +-+ | 0.06 | +-+ 1 row in set (0.01 sec) - Cs. -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Communication link failure - MySQL Server seems to stop
Hi Mark, It is not easy to produce a test case as it happens after some thousands of DB accesses. It also happens with 4.1.1-alpha (from mysql.com) . I did not try the bitkeeper version. But it seems it has something to do with using Server Prepared Statements. It did not happen when I specified useServerPrepStmts=no. Regards Helmut Mark Matthews wrote: hl said: Hi, What can I do to find the cause / correct the following problem? More often I get the following with Connector/J: error com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.io.EOFException Hlein, Can you post a testcase? Prepared statements are still in a state of flux...The code in 4.1.x from BitKeeper is probably the most stable right now. What happens when you run with MySQL-4.1 from BitKeeper instead of 5.0? -Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Privileging
Tucker, Gabriel [EMAIL PROTECTED] wrote: I have a user that is granted all privileges for their database. When = they run a select * on a table in that database, the request fails after = about 1000 records. When I run the same query as root, I have no = problems. Consequently, I believe this is a permissioning problem. Since I have granted this user all privileges on their database, I am = not sure what could be wrong. I appreciate any assistance. 4.0.16 - Unix Check value of SQL_SELECT_LIMIT, SQL_BIG_SELECTS session variables: http://www.mysql.com/doc/en/SET_OPTION.html -- 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]
Re: Type TEXT
Jim McAtee [EMAIL PROTECTED] wrote: Are the TEXT column types padded out to their max length by spaces? No. It's variable-length column type. http://www.mysql.com/doc/en/Storage_requirements.html -- 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]
Why do we need ~ in BOOLEAN MODE?
~ are supposed to adjust the weight of a word to the relevancy score, but they only work in BOOLEAN MODE and BOOLEAN MODE doesn't care about relevancy! In my opinion, they should be only allowed in the regular fulltext search in which we DO compute the relevancy. Am I missing something? Thanks so much! Haitao Jiang __ Do you Yahoo!? Get better spam protection with Yahoo! Mail. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
complicated select statements and out of memory errors
Hi there, I have some rather complicated sql statements that seem to eat up all resources from mysqld. Once I have two of them running at the same time no one can log on any more. Simple queries involving distinct get a out of memory error. We have tried changing the index buffer size from 256M to 2G, no change. We have 7G of memory on an IRIX system with 8 cpus. No replication. Do you have any idea what is happening? Thanks a lot Bernd the sql statements look like this: insert into rule2 select id from text_uniq_bin where (substring(rev_rep,1,1)=1) AND ( substring(replaced,1,1) + substring(replaced,2,1) + substring(replaced,3,1) + substring(replaced,4,1) + substring(replaced,5,1) + substring(replaced,6,1) + substring(replaced,7,1) + substring(replaced,8,1) + substring(replaced,9,1) ) 2 AND (substring(replaced,3,1)=0) AND ( substring(replaced,12,1) + substring(replaced,13,1) + substring(replaced,14,1) + substring(replaced,15,1) + substring(replaced,16,1) + substring(replaced,17,1) + substring(replaced,18,1) + substring(replaced,19,1) ) 5 ; AND insert into rule3 select id from text_uniq_bin where (substring(rev_rep,1,1)=1) AND ( substring(replaced,1,1) + substring(replaced,2,1) + substring(replaced,3,1) + substring(replaced,4,1) + substring(replaced,5,1) + substring(replaced,6,1) + substring(replaced,7,1) + substring(replaced,8,1) + substring(replaced,9,1) ) 2 AND (substring(rev_rep,3,1)=0) AND ( substring(replaced,12,1) + substring(replaced,13,1) + substring(replaced,14,1) + substring(replaced,15,1) + substring(replaced,16,1) + substring(replaced,17,1) + substring(replaced,18,1) + substring(replaced,19,1) ) =5 AND (substring(rev_rep,2,1)=0) AND ( substring(replaced,6,1) + substring(replaced,7,1) + substring(replaced,8,1) + substring(replaced,9,1) + substring(replaced,10,1) + substring(replaced,11,1) + substring(replaced,12,1) + substring(replaced,13,1) + substring(replaced,14,1) + substring(replaced,15,1) + substring(replaced,16,1) + substring(replaced,17,1) + substring(replaced,18,1) + substring(replaced,19,1) ) 7
hot innodb backup options
All, I have a database where the tables are InnoDB. I am working on the backup procedure, and would like to implement a process where the database is backed up without having to shut it down (live, or hot backup). Reading the documentation, it appears the only option I have is to purchase a solution from InnoDB ($500/yr). Is there a free option out there to do this? Thank you, Kevin Williams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Embeded MySQL for Java ???
You can try with a JNI native class. Santino At 10:53 -0600 1-03-2004, Mark Matthews wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Victor Medina wrote: Hi!! No there isn't Although, you can try bdb(www.sleepycat.com), it comes with a java access class that will let you use bdb in a embeded environment, one thing i must say, is that it does not use jdbc, instead it uses it's own access methods, quite easy btw. GUYS!, it would be really nice to have an embeded access level trough java, think about it! ok? =) It is being planned, however it's not as straightforward as one would think if you still want to have JDBC-like access. There are also issues with Java threads and 'native' threads co-existing on some platforms. Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAQ2qitvXNTca6JD8RAi16AJ9szuvn7N21l6ZUlqHRbLH9ZR9TegCeKxZG zOnh484lj3wT4cAkLK86G18= =73QX -END PGP SIGNATURE- -- 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]
Executing REPLACE from IF statement?
Hi,
I was wondering if someone can point out a way to make the below script
update a row.
Query:
SELECT pb.ID, c.mdate, bc.mdate,
IF(DATE_FORMAT(c.mdate,'%m/%d/%Y') = bc.mdate, Same Date, Not Same Date)
FROM product_bench pb, current c, bench_current bc
WHERE pb.ticker=SAP500 and c.ID=pb.ID and bc.ticker=pb.ticker
Result:
97 | 2004-02-21 | 02/21/2004 | Same Date
98 | 2004-02-21 | 02/21/2004 | Same Date
What I would like to do is REPLACE current_profile SELECT * FROM current
where the ID('s) are those returned from the select statement above.
Thanks for your insight and assistance.
Dionysios Pilarinos
**
This message, including any attachments, contains confidential information intended
for a specific individual and purpose, and is protected by law. If you are not the
intended recipient, please contact sender immediately by reply e-mail and destroy all
copies. You are hereby notified that any disclosure, copying, or distribution of this
message, or the taking of any action based on it, is strictly prohibited.
TIAA-CREF
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Type TEXT
- Original Message - From: Richard Davey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 01, 2004 11:02 AM Subject: Re: Type TEXT Hello Jim, Monday, March 1, 2004, 5:44:22 PM, you wrote: JM Are the TEXT column types padded out to their max length by spaces? No, they're variable length and at a maximum of 65KB per row, be thankful of this! A 30 character string will take up 32 bytes for example. Thanks. What (if any) difference is there then be between a varchar(255) and a tinytext column? And what disadvantages are there be in using columns of type text, rather than varchar(n)? Are there performance penalties? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication / Synchronizing DB across different machines
But what happens if there is a break of communication and the same record on both machines is modified? For example, if the sync link is ? down, and I update record #1 on A, while at the same time, another user is modifying record #1 on server B. When the link comes back online, how will the servers be able to synchronize record #1, or in the case that this is impossible (I can't even imagine how I would do that manually - merge? A overwrite B? B overwrite A?), how does MySQL flag it and let me know that there is a discrepancy in the two DBs? From my understanding (please bear in mind I am new to MySQL replication); MySQL Replication does not support conflict resolution. So in the sceanrio wherein you have dual masters actively updating the same data the conflict resolution onus falls on the Application using MySQL process. Also, there is no need for the link between 'A' and 'B' to go down for this issue to surface. The fact that you have a dual-master setup is reason enough for the problem you have explained above to surface. Gowtham __ Do you Yahoo!? Get better spam protection with Yahoo! Mail. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication / Synchronizing DB across different machines
I believe Gowtham's response is accurate. If the link goes down it is going to unclear which update becomes the final one, and it will be possible for one side to contain the row after one update, and the other to contain the row after the other update, thus being out of sync. Mysql has no support for resolving these types of conflicts so you would need to ensure updates are done in a safe manner at the application level. As far as inserts an autoincrement collumns are concerned one way to handle it is to use a unique server id and create a combined key (auto_increment_field, server_id). If all you really need is a failover server and you don't need to do active/active load balancing then you can avoid these type of update issues fairly easily. As for whitepapers, I'm not aware of any good ones, but the replication FAQ in the mysql manual provides a good starting point for this type of 2 way replication setup. John -Original Message- From: Gowtham Jayaram [mailto:[EMAIL PROTECTED] Sent: Monday, March 01, 2004 1:22 PM To: [EMAIL PROTECTED] Subject: Re: Replication / Synchronizing DB across different machines But what happens if there is a break of communication and the same record on both machines is modified? For example, if the sync link is ? down, and I update record #1 on A, while at the same time, another user is modifying record #1 on server B. When the link comes back online, how will the servers be able to synchronize record #1, or in the case that this is impossible (I can't even imagine how I would do that manually - merge? A overwrite B? B overwrite A?), how does MySQL flag it and let me know that there is a discrepancy in the two DBs? From my understanding (please bear in mind I am new to MySQL replication); MySQL Replication does not support conflict resolution. So in the sceanrio wherein you have dual masters actively updating the same data the conflict resolution onus falls on the Application using MySQL process. Also, there is no need for the link between 'A' and 'B' to go down for this issue to surface. The fact that you have a dual-master setup is reason enough for the problem you have explained above to surface. Gowtham __ Do you Yahoo!? Get better spam protection with Yahoo! Mail. http://antispam.yahoo.com/tools -- 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: Executing REPLACE from IF statement?
After some thought, this is what I came up with:
REPLACE current_profile
select c.*
FROM product_bench pb, current c, bench_current bc
WHERE pb.ticker=SAP500 and c.ID=pb.ID and bc.ticker=pb.ticker and
DATE_FORMAT(c.mdate,'%m/%d/%Y') = bc.mdate
Now all I have to do is make this run for each pb.ticker without having to
explicitly identify it in the script.
Feel free to give me suggestions.
-Original Message-
From: Pilarinos, Dionysios
Sent: Monday, March 01, 2004 3:53 PM
To: [EMAIL PROTECTED]
Subject: Executing REPLACE from IF statement?
Hi,
I was wondering if someone can point out a way to make the below script
update a row.
Query:
SELECT pb.ID, c.mdate, bc.mdate,
IF(DATE_FORMAT(c.mdate,'%m/%d/%Y') = bc.mdate, Same Date, Not Same Date)
FROM product_bench pb, current c, bench_current bc
WHERE pb.ticker=SAP500 and c.ID=pb.ID and bc.ticker=pb.ticker
Result:
97 | 2004-02-21 | 02/21/2004 | Same Date
98 | 2004-02-21 | 02/21/2004 | Same Date
What I would like to do is REPLACE current_profile SELECT * FROM current
where the ID('s) are those returned from the select statement above.
Thanks for your insight and assistance.
Dionysios Pilarinos
**
This message, including any attachments, contains confidential information
intended for a specific individual and purpose, and is protected by law. If
you are not the intended recipient, please contact sender immediately by
reply e-mail and destroy all copies. You are hereby notified that any
disclosure, copying, or distribution of this message, or the taking of any
action based on it, is strictly prohibited.
TIAA-CREF
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
**
This message, including any attachments, contains confidential information intended
for a specific individual and purpose, and is protected by law. If you are not the
intended recipient, please contact sender immediately by reply e-mail and destroy all
copies. You are hereby notified that any disclosure, copying, or distribution of this
message, or the taking of any action based on it, is strictly prohibited.
TIAA-CREF
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Executing REPLACE from IF statement?
And to finish it off, I just removed pb.ticker=SAP500 statement.
-Original Message-
From: Pilarinos, Dionysios
Sent: Monday, March 01, 2004 4:56 PM
To: [EMAIL PROTECTED]
Subject: RE: Executing REPLACE from IF statement?
After some thought, this is what I came up with:
REPLACE current_profile
select c.*
FROM product_bench pb, current c, bench_current bc
WHERE pb.ticker=SAP500 and c.ID=pb.ID and bc.ticker=pb.ticker and
DATE_FORMAT(c.mdate,'%m/%d/%Y') = bc.mdate
Now all I have to do is make this run for each pb.ticker without having to
explicitly identify it in the script.
Feel free to give me suggestions.
-Original Message-
From: Pilarinos, Dionysios
Sent: Monday, March 01, 2004 3:53 PM
To: [EMAIL PROTECTED]
Subject: Executing REPLACE from IF statement?
Hi,
I was wondering if someone can point out a way to make the below script
update a row.
Query:
SELECT pb.ID, c.mdate, bc.mdate,
IF(DATE_FORMAT(c.mdate,'%m/%d/%Y') = bc.mdate, Same Date, Not Same Date)
FROM product_bench pb, current c, bench_current bc
WHERE pb.ticker=SAP500 and c.ID=pb.ID and bc.ticker=pb.ticker
Result:
97 | 2004-02-21 | 02/21/2004 | Same Date
98 | 2004-02-21 | 02/21/2004 | Same Date
What I would like to do is REPLACE current_profile SELECT * FROM current
where the ID('s) are those returned from the select statement above.
Thanks for your insight and assistance.
Dionysios Pilarinos
**
This message, including any attachments, contains confidential information
intended for a specific individual and purpose, and is protected by law. If
you are not the intended recipient, please contact sender immediately by
reply e-mail and destroy all copies. You are hereby notified that any
disclosure, copying, or distribution of this message, or the taking of any
action based on it, is strictly prohibited.
TIAA-CREF
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
**
This message, including any attachments, contains confidential information
intended for a specific individual and purpose, and is protected by law. If
you are not the intended recipient, please contact sender immediately by
reply e-mail and destroy all copies. You are hereby notified that any
disclosure, copying, or distribution of this message, or the taking of any
action based on it, is strictly prohibited.
TIAA-CREF
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
**
This message, including any attachments, contains confidential information intended
for a specific individual and purpose, and is protected by law. If you are not the
intended recipient, please contact sender immediately by reply e-mail and destroy all
copies. You are hereby notified that any disclosure, copying, or distribution of this
message, or the taking of any action based on it, is strictly prohibited.
TIAA-CREF
**
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Xserve G5
Has anyone had any experiences running MySQL on a Xserve G5 with Macintosh OSX? We are thinking of purchasing some new hardware to run our MySQL server. The 64-bit architecture is something we would like to take advantage of. Is this good, bad, otherwise? Any comments would be appreciated. Thanks TOM
Re: Best Database
check into innodb and transactions Respectfully, Ligaya Turmelle Paul Higgins [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi everyone, I want to create a table that will consistently have data added and removed from it. Is there anything I should know/consider before doing this? Any information you could provide would be GREATLY appreciated. Thanks, Paul _ Click, drag and drop. My MSN is the simple way to design your homepage. http://click.atdmt.com/AVE/go/onm00200364ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
memory issue: paging, disk activity, table size - was: optimizing a select statement
Thanks for the information. Before I try to further look into the query, I would like to know if there is some memory issue. Its strange since the speed for executing same query differs. Its a Mac G5 with just 1GB ram. I could see lot of pageouts in the top command. The activity monitor shows that there is little free memory (12 -14M) and ~650M of inactive memory and ~250M of active memory. Read on web that the less free memory is the inactive memory will be used. When I run a query (like the one we had discussed) there is only a slight difference in this memory status, where as the disk activity shows active 'data in', 'read in' . Does this means that its out of physical memory and uses swap file? I am not sure how mysql uses memory. Couple of tables I read in the query is more than 4GB but the query uses only two column of those tables and it will not be more than 1 G for sure. I am not sure if I understood correct or not and would like to hear your suggestion. Is there any relation between the table size (no of rows and file size) and the system memory needed to get better performance? i read on web that its better to have ram more than the largest table size. is this true even if the query uses only few columns of big tables? Thanks for your help Karthik. At 07:44 PM 2/29/2004, you wrote: So if this is your query and based on the explain this is what I would recommend. SELECT Distinct (a.id) From table1 a INNER JOIN table1 b USING ( p_id ) INNER JOIN table2 c USING ( p_id ) INNER JOIN table3 d USING ( out_id ) INNER JOIN table4 e ON ( d.name_id = e.name_id ) INNER JOIN table4 f ON ( e.start_id BETWEEN f.left_id AND f.end_id ) WHERE (f.name_id =45 OR f.name_id =56) AND b.id =275 AND a.id != b.id For some reason you are getting the using temporary on table b, I assume based on the explain that you have an index called (id) that only has the id in it. Here's the first problem. You are limiting b, by id. And joining on p_id. So mysql is trying to use the index with the combination of both of them which is combine I assume. So removing distince really won't help with this one much, since your indexes will always have a little problems. INNER JOIN table4 f ON ( e.start_id BETWEEN f.left_id AND f.end_id ) This is probably the second biggest problem, you will always get a range. And ranges will always be slower. Don't really have a solution without actually touching the data. If you really have more than 50 million records this is really bad. a.id != b.id I would recommend trying to rewrite your query and just focus on table a and f. If you can get rid of them returning the extra 2 and 3 rows, I think that would solve your problem. Donny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: hot innodb backup options
this is just my $0.02, but I would think $500/yr is certainly worth it. you'll get a fully supported product, and the money goes to a good cause. jeff Kevin Williams wrote: All, I have a database where the tables are InnoDB. I am working on the backup procedure, and would like to implement a process where the database is backed up without having to shut it down (live, or hot backup). Reading the documentation, it appears the only option I have is to purchase a solution from InnoDB ($500/yr). Is there a free option out there to do this? Thank you, Kevin Williams -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: quot;hotquot; innodb backup options
Anonymous wrote on Mon, 01 March 2004 13:26All, I have a database where the tables are InnoDB. I am working on the backup procedure, and would like to implement a process where the database is backed up without having to shut it down (live, or hot backup). Reading the documentation, it appears the only option I have is to purchase a solution from InnoDB ($500/yr). Is there a free option out there to do this? Thank you, Kevin Williams Well, $500 is not bad for a hot backup solution. That being said you can use mysqldump with the --single-transaction option to achieve the same effect, but it will be slower and (probably) take up more storage space. You should also make sure you are using a recent version of mysql or make sure to disable foreign key checks before doing your restore as mysqldump does not back-up the tables with concern for the integrity constraints. -- Regards, Mike Hillyer Webmaster/Moderator http://www.vbmysql.com Read my blog at: http://www.vbmysql.com/mike/blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UID/PW Question???
During the last week I have asked couple of questions regarding setting up
MySQL have gotten good answers. Since my knowledge of MySQL is in the
beginer level, and I am very close to finishing up working with MySQL, I
have several questions still un answered. I will try asking one question at
a time so I do not confuse anything.
My setup:
T1 to Internet Backbone
|
---
| |
12.21.237.10 12.21.237.11
-- --
| Firewall | | Firewall |
| DNS (NS1)| | DNS (NS2)|
-- --
192.168.21.10 192.168.21.10
| |
---
DMZ |Hub|DMZ
-
| | |
192.168.5.15 192.168.5.16 192.168.5.17
rdx mail mails
| RADIUS | | Mail | | Web/HTTP | | DATA |
| Server | | Server | | Hosting | | MySQL |
192.168.6.15 192.168.6.16 192.168.6.17 192.168.6.18
rdxl maill mailsl datal
| | | |
---|Hub|-
The four servers sit behind a firewall communicate with each other on
private 192.168.6.XX network. Data Server (datal) is a MySQL Master Server
RH9,DUAL CPU, 1.5GB Mem RAID1) it is accessable only by servers in the
DMZ over 192.168.5.XX network. I have file /etc/my.cnf in the data1,
which I copied from my-large.cnf and have made no changed to it.
/etc/hosts table in each server resolves all other servers in the DMZ I
can PING any one of them from any one of them.
RADIUS Server (rdxl) is a client MySQL Server and reads authentication data
from DATA Server (datal). My INTENT is to setup Client MySQL Server (rdxl)
so
that(if possible)it is permanently connected to the MySQL Master Server
(datal)!
-
I use the following command in Client MySQL Server (rdxl) to connect to the
Master MySQL Server:
shell mysql -h datal -u root password=mysqlpw
Where root password (mysqlpw) is setup using mysqladmin command.
Question:
(1) What is the norm UID when connecting from a Client MySQL Server?
I am using root as UID, should I use mysql as UID why? If UID
mysql is used then how do I setup PW for it? Is it by mysqladmin
command or by changing the PW is the User Manager?
(2) I am told not to expose the password. In this situation, where
RADIUS server will be connecting to the Master MySQL server every
few seconds, how would I setup the connection to the datal server?
(2) In Clinet/Master MySQL setup, do I also need to create a /etc/my.cnf
file on the Client MySQL Server?
(3) What changes do I need in /etc/my.cnf file?
-
Thanks.
Kirti
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Type TEXT
Hello Jim, Monday, March 1, 2004, 8:58:51 PM, you wrote: JM Thanks. What (if any) difference is there then be between a varchar(255) and JM a tinytext column? On the surface nothing I believe. Varchar too is a variable length data type. There may well be internal differences in the way MySQL handles them however, but I couldn't tell you what. JM And what disadvantages are there be in using columns of type text, rather JM than varchar(n)? Are there performance penalties? If your data will always fit into 255 characters (or less) then use a varchar, if you always know the exact length then use a char. Use the text range of data types if you need more storage space but still want to be able to run fulltext indexes etc. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re[2]: Type TEXT
If speed is a concern and storage is not, I believe that the fixed length char() will provide a better speed. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Richard Davey [mailto:[EMAIL PROTECTED] Sent: Monday, March 01, 2004 5:32 PM To: Jim McAtee Cc: [EMAIL PROTECTED] Subject: Re[2]: Type TEXT Hello Jim, Monday, March 1, 2004, 8:58:51 PM, you wrote: JM Thanks. What (if any) difference is there then be between a varchar(255) and JM a tinytext column? On the surface nothing I believe. Varchar too is a variable length data type. There may well be internal differences in the way MySQL handles them however, but I couldn't tell you what. JM And what disadvantages are there be in using columns of type text, rather JM than varchar(n)? Are there performance penalties? If your data will always fit into 255 characters (or less) then use a varchar, if you always know the exact length then use a char. Use the text range of data types if you need more storage space but still want to be able to run fulltext indexes etc. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- 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]
More on indexes?
Hi, I've been following the list to get a better grip on MySQL, and have been hearing a lot about indexing. I'm beginning to think I should have indexed some of my tables in the past. Can anyone point me in the right direction as to the purpose of indexes, why they are used, the benefits, and when one should index? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: More on indexes?
Hi Eve: Indexing is use to speed up search in the tables. You can have Primary Key Index to force unique key ( Ex: Client_Cod), or normal index in columns you use to get data in an alternate way(Ex: Client_Name). If you have very little data in your tables, the speed will not notice the speed but as data grows, you start to notice the gain in the searches. You need to read a book on Relational Database. Osvaldo Sommer -Original Message- From: Eve Atley [mailto:[EMAIL PROTECTED] Sent: Monday, March 01, 2004 8:42 PM To: [EMAIL PROTECTED] Subject: More on indexes? Hi, I've been following the list to get a better grip on MySQL, and have been hearing a lot about indexing. I'm beginning to think I should have indexed some of my tables in the past. Can anyone point me in the right direction as to the purpose of indexes, why they are used, the benefits, and when one should index? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.605 / Virus Database: 385 - Release Date: 3/1/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.605 / Virus Database: 385 - Release Date: 3/1/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using mod_auth_mysql with Apache 2
Look in /etc/httpd/conf.d. These config files are included by a line in httpd.conf. Now, find the config file for mod_auth_mysql. At the top, there may be something like IfDefine HAVE_MOD_AUTH_MYSQL ...stuff there... /IfDefine. For some reason, the installed RPM is not defining HAVE_MOD_AUTH_MYSQL, thus it isn't loading. Comment out the IfDefine ... and /IfDefine lines, restart Apache, and that should put you on your way. You didn't say which distribution you were using, but I had that problem with an update to Apache on Mandrake 9.2. Hope that helps!! j- k- On Friday 27 February 2004 10:24 am, James Marcinek wrote: Hello all! I know this isn't an Apache list but I already checked the documentation on Apache.org on trying to get this configured correctly. I have a question about mod_auth_mysql. I installed the rpms for Apache(2.x) and Mysql and I can see the module is in the /etc/httpd/modules dirctory: mod_auth_mysql.so However I do not see this added in the httpd.conf file. I also checked what modules are loaded statically with the httpd -l command, and it wasn't listed. The reference material I have is a bit vague so I'm hoping that someone can help me out as this is the first time I'm setting this up. The material covers Apache 1.x and 2(at the same time) so I'm not sure what I have to do (if anything) I'm assuming that I have to add this module to the Dynamic Shared Object support section of the httpd.conf: auth_mysql_module modules/mod_auth_mysql.so Is this correct? Also, my reference material also indicates I need to add a line to the httpd.conf to give mod_auth_mysql the parameters it needs to connect to MySQL: Auth_MySQL_Info hostname user password Is this right? Where in the httpd.conf should this be placed. Any help would be great! Thanks, James -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Feature request related to COMPRESS and UNCOMPRESS functions
Here is the background: Anyone that is running a huge system like MARC that has millions of uncompressed blob records in huge tables, needs to be able to migrate, in real-time and without down-time, to compressed blobs. Therefore, we need a way to know if a given field is compressed or not. I hear you on that! We did the compression on the application end. When we started compressing all of the blobs in the table were uncompressed except newly added ones. We took advantage of the fact that zlib fails on decompression. So we wrote a function my_decompress() that takes the blob and decompresses it and if it fails just returns the original (assumed to be already decompressed). Works great and decompression gets divided among the webservers which scales better than having MySQL do it. However, you should develop a way to take tables offline. Lack of proper table maintenance can slow things down by a factor of 10 or more (and one of the reasons we can not use InnodDB). -steve-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Xserve G5
Not to start a flame war, but just a question/suggestion... Do you specifically want a Mac (i.e. are you a Mac shop), or are you looking for inexpensive 64 bit? If the latter applies, you might want to look at an AMD64 box running Linux (SuSE and Mandrake are two that come to mind as having 64 bit versions, I'm sure there are others, just haven't kept up to date in that area). Hope that helps. j- k- On Monday 01 March 2004 01:29 pm, Tom O'Neill wrote: Has anyone had any experiences running MySQL on a Xserve G5 with Macintosh OSX? We are thinking of purchasing some new hardware to run our MySQL server. The 64-bit architecture is something we would like to take advantage of. Is this good, bad, otherwise? Any comments would be appreciated. Thanks TOM -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A problem with access to data source, please help!!!!
I had a bunch of problems setting up OpenCMS until I figured it out. OpenCMS tries to connect via the network socket, so the connection to the database may look like it's coming from the.host.name.com. You may need to add the full host name of the machine you are installing on to the privileges table as well. (I'm not 100% sure of that...it's been a long time since played with OpenCMS.) j- k- On Monday 01 March 2004 06:55 am, Rafael Diaz Valdes wrote: Hi, I am trying to configure Open CMS 5.0.0 with mysql. But while Database and and table creation I am getting following error. Could no connect to database via: jdbc:mysql://localhost:3306/ java.sql.SQLException: Server configuration denies access to data = source --- java.lang.NullPointerException I tested mysql with the root password I assigned using mysqladmin -u root -p, so it looks like mysql is setup correctly.However when trying Also I tested the GRANTS of root and I get += + | Grants for [EMAIL PROTECTED] = += + | GRANT ALL PRIVILEGES ON *.* TO 'root'@'pcepaip30' IDENTIFIED BY PASSWORD '*F65E684A09D85F1DD4574279566B9E738DD597E7' WITH GRANT OPTION + + 1 row in set (0.00 sec) I need a help, thanks in advance Rafael -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Xserve G5
The benchmarks look good for opterons and mysql, i just scored a Sun Fire V100, still havent benchmarked that yet though. Not to start a flame war, but just a question/suggestion... Do you specifically want a Mac (i.e. are you a Mac shop), or are you looking for inexpensive 64 bit? If the latter applies, you might want to look at an AMD64 box running Linux (SuSE and Mandrake are two that come to mind as having 64 bit versions, I'm sure there are others, just haven't kept up to date in that area). Hope that helps. j- k- On Monday 01 March 2004 01:29 pm, Tom O'Neill wrote: Has anyone had any experiences running MySQL on a Xserve G5 with Macintosh OSX? We are thinking of purchasing some new hardware to run our MySQL server. The 64-bit architecture is something we would like to take advantage of. Is this good, bad, otherwise? Any comments would be appreciated. Thanks TOM -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- 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]
bookings
I am (trying) to make a booking systems. Currently I have a table with 3 timestamps that record bookingDate, bookingFromDate and bookingToDate I have another table that records 'seasons'. This table contains two timestamps that record seasonStartDate and seasonEndDate also I have the rates as seasonRateWeekly and seasonRateNightly What I need to do, is INSERT INTO bookings the bookingFromDate and bookingToDate, no problem there, but I need to know what rate to charge them. and if the booking dates overlap seasons, the appropriate rate needs to be applied. All replies greatfully recieved, Kevin -- __ (_ \ _) ) | / / _ ) / _ | / ___) / _ ) | | ( (/ / ( ( | |( (___ ( (/ / |_| \) \_||_| \) \) Kevin Waterson Port Macquarie, Australia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 4.1 Stable Enough For Beginner To Use? [SOLVED]
Thanks to all who responded. I think I will attempt to rn MySQL 4.1 for my web sites. -- Yoroshiku! Dave G [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: help for pdf file
Any rpm version? ~Elle~ -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: Monday, March 01, 2004 5:17 PM To: [EMAIL PROTECTED] Subject: Re: help for pdf file I don't know this related to php subject or mysql subject, but does anybody knows how to create a pdf file from php mysql? http://www.fpdf.org is a free PHP library which can help you produce pdf files. You have to write your own functions for formatting tables, etc. but the actual creation of the pdf file is handled by the library. 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]
unable to resolve hostname...
Ok, I am going about this as I normally have umpting times # scripts/mysql_install_db and I get Neither host 'isdesign.isdesigndev.com' and 'localhost' could not be looked up with ./bin/resolveip Please configure the 'hostname' command to return a correct hostname. If you want to solve this at a later stage, restart this script with the --force option I have in /etc/hosts 127.0.0.1 isdesign localhost.localdomain localhost and in /etc/sysconfig/network HOSTNAME=isdesign.isdesigndev.com h? This is on a RedHat 9 server... Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Xserve G5
Has anyone had any experiences running MySQL on a Xserve G5 with Macintosh OSX? We are thinking of purchasing some new hardware to run our MySQL server. The 64-bit architecture is something we would like to take advantage of. Is this good, bad, otherwise? Any comments would be appreciated. I've had great success running it on my 4-year old Mac laptop (using the latest OS), and the Xserve is quite a machine (see #3 at http://www.top500.org/list/2003/11/). I think you'll have to compile MySQL on the Xserve to take advantage of the 64-bit architecture. Then if you are saavy (which I'm not, but I know people who are) Apple has offered a suite of free profiling tools (Shark is a big one) that shows which routines use the most processor time, and will even show you the assembly code and when the processor stalls, why, and what you can do about it. Very slick. See http://developer.apple.com/tools/performance/. - B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlbinlog: unknown command errors
Hi, I'm having problems running queries in my binlog that contain binary data. Apparently the / character is being interpreted as a mysql command and I get errors. The queries must have gone through on the original machine because they're there in the binlog. Specs are: original server: mysqld Ver 4.0.14-standard for pc-linux on i686 update server: mysqld Ver 4.0.18-standard for pc-linux on i686 (Official MySQL-standard binary) This is a problem I've had for a while and I've never really gotten the binlog to work the way it's supposed to. Any help greatly appreciated. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help for pdf file
Hi ~Elle~, No rpm, or any package is needed. It's just plain PHP that's being used in FPDF, no binaries whatsoever. Just download the classes and follow the many examples on the site to start using them. Regards, Jigal. - Original Message - From: Elly Wisata [EMAIL PROTECTED] Any rpm version? ~Elle~ -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: Monday, March 01, 2004 5:17 PM To: [EMAIL PROTECTED] Subject: Re: help for pdf file I don't know this related to php subject or mysql subject, but does anybody knows how to create a pdf file from php mysql? http://www.fpdf.org is a free PHP library which can help you produce pdf files. You have to write your own functions for formatting tables, etc. but the actual creation of the pdf file is handled by the library. 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]
