Re: Licence question
1. Free use for those who are 100% GPL 2. Free use for those who never copy, modify or distribute 3. Commercial use for everyone else OK. But 2nd statement is not taken from GPL. Example: SUSE ships non GPL programs such as StarOffice (not OpenOffice), SUN ships Java etc. in it's Linux... This is not covered by GPL, as I've mentioned before. The only thing I said wrong is: I think that mySQL doesn't have redistribution targeted license. Ivan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query to emulate what mysqldump does
On woensdag 3 december 2003 22:56 Andrew Braithwaite told the
butterflies:
You could try to use the select into {OUTFILE | DUMPFILE}
from tablename where blah=blah...
I think you may be able to do select into local outfile from
blah
Which will put the file on the same server as the MySQL
client is running on...
Cheers,
Andrew
-Original Message-
From: Matt Babineau [mailto:[EMAIL PROTECTED]
Sent: Wednesday 03 December 2003 17:37
To: [EMAIL PROTECTED]
Subject: RE: Query to emulate what mysqldump does
On Wed, 2003-12-03 at 15:22, Jay Blanchard wrote:
[snip]
I thought about that Jay, but the mysql server is not on the
webserver machine. Any other suggestions? [/snip]
phpmyadmin will allow you to connect to the remote MySQL server and
do dumps
What if I don't have phpmyadmin available? :)
What I am trying to do, it setup a simple script to pull down
essentially a backup of their database and write it to a file on my
development machine so when they mess up their data (..and I said
WHEN) I can be a hero and revert them to the last good backup before
they didn't touch a thing.
As I understand, you've got a box of your own. With MySQL installed. Since
the database is on another server as the website, there is a fair chance
that you can just connect to it from your local box.
mysql -u user --host host [--port port] -p
and
mysqldump -u user --host host [--port port] -p databaes [table]
Or, if you insist on doing this through your own little php script, might
wanna try this:
$Tables = mysql_query('SHOW TABLES');
while($Table = mysql_fetch_assoc($Tables)) {
$Rows = mysql_select('SELECT * FROM '.$Table['Tables_in_dbname']);
while($Row = mysql_fetch_assoc($Rows)) {
// Here you've got your tables. For each and every
// table again. Do whatever you want with it...
}
}
(but I'd personally just stick to phpMyAdmin)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error when running mysql for the first time
Hi, I downloadet the mysql 4.1 development tree made a windows source tree, and compiled with VC++6. After running mysql in console I got this error: D:\MySQL\binmysqld-max.exe --console InnoDB: Error: log file group too small for innodb_thread_concurrency 031204 10:33:08InnoDB: Assertion failure in thread 3548 in file C:\Documents and Settings\Twm\Desktop\New Folder\mysql-4.1.1\innobas e\log\log0log.c line 852 InnoDB: Failing assertion: log_calc_max_ages() InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] 031204 10:33:08 mysqld-max.exe: Got signal 11. Aborting! 031204 10:33:08 Aborting 031204 10:33:08 mysqld-max.exe: Shutdown Complete D:\MySQL\bin Is this a bug or am I doing something wrong? Best regards... Torben Meyhoff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.1 Download
Karam Chand [EMAIL PROTECTED] wrote: Are binaries of MySQL 4.1.1 available or we need to compile it from the source. No, binaries are not available yet. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
Once again, three queries, same result, huge speed difference
I posted this few days ago, but with no answer, also posted it to
benchmark list..
Executing this SQL, takes ~5 sec.
select artists.name , cds.title , tracks.title from artists, tracks,
cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and MATCH (artists.name) AGAINST ('madonna')
and MATCH (cds.title) AGAINST ('music')
and MATCH (cds.title) AGAINST ('mix')
and MATCH (cds.title) AGAINST ('2001')
and this, ~40 sec.
select artists.name , cds.title, tracks.title from artists, tracks, cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
and MATCH (cds.title) AGAINST ('music mix 2001' IN BOOLEAN MODE)
and executing this takes less than 1 sec.
select artists.name , cds.title, tracks.title from artists, tracks, cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and artists.name like '%madonna%'
and cds.title like '%music mix 2001%'
Same result but the speed difference is quite a different, why is that ?
This is only on test DB, I didn't try it on real life DB where I have
~14 mil. rows in tracks table.
Regards
Re: Error 2002
jeepcreep [EMAIL PROTECTED] wrote: I did and it is not running. Look into error log file. You can find causes why MySQL server doesn't start. Victoria Reznichenko [EMAIL PROTECTED] wrote:jeepcreep wrote: That is what I did. Check with ps ax | grep mysqld if MySQL server is really running. That's when I get the error 2002 message. Maybe my problem is not with the mysql.sock file but something else. Daniel Kasak wrote:jeepcreep wrote: Just loaded mySQL v4 to LINUX Mandrake v9.2 via RPM. The error 2002 talks of a mysql.sock file. I can't find this file anywhere on my box. Can't figure out why I can't find it as I'm preety sure the install was completed successfully. If I want to rebuild it, though, what's in it? The mysql.sock file only exists while MySQL is running. Maybe you installed MySQL but haven't started it. I don't know how Mandrake handles starting stopping services, but you should have a script: /etc/init.d/mysql ( or something like that ). Try: /etc/init.d/mysql start That's what starts it on mine anyway. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Query Help
On 2 Dec 2003 at 7:20, Greg Jones wrote: snip select l.ltsysid,l.lientraknum, c.name from lientrak as l, customer as c where l.custsysid=c.custsysid and l.ltsysid in (select l2.ltsysid from lientrak as l2 where l2.lientraknum like '2003-%') snip Hi greg Yopur problem is that you are using a subquery - MySQL 4.0.1.5 does not support subqueries - you will need 4.1 for that... A possible workaround (based on your query above - so please tolerate any mistakes :) ) : select l.ltsysid,l.lientraknum, c.name from lientrak as l, customer as c where l.custsysid=c.custsysid and l.lientraknum like '2003-%' Actually, having written the above workaround, I am puzzled as to why you would need the subquery at all :)...unless of course, above workaround is completely wrong, and I have to eat humble pie again. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown)
Double execution of queries?
One of my apps in a test enviroment is showing some strange behaviour.
Up-front-answer: No, there are no loops in this segment of code.
Snippet:
$password = sha1(stripslashes($eval['tpassword']));
$query = INSERT INTO logins (login_parent, login_name, login_password, fname,
lname) values ($id, {$eval['temail']}, '$password', {$eval['fname']},
{$eval['lname']});
doDebug(7, SQL: $query);
$r = $db-query($query);
errorCheck($r, $db);
doDebug is a custom logging command. errorCheck is a wrapper to the PEAR
error checking stuff for DB.
Mysql Logs:
40821 Init DB product
40821 Query INSERT INTO logins (login_parent,
login_name, login_password, fname, lname) values (36, '[EMAIL PROTECTED]',
'ef2a9c2c39232fbe4984787b7a81d2faa0df5d9d', 'Name', 'Name')
40821 Init DB product
40821 Query SELECT last_insert_id()
40821 Init DB product
40821 Query INSERT INTO logins (login_parent,
login_name, login_password, fname, lname) values (36, '[EMAIL PROTECTED]',
'ef2a9c2c39232fbe4984787b7a81d2faa0df5d9d', 'Name', 'Name')
There is no repetition of the last_insert_id() query. Server is 4.1 alpha.
This one boggles my mind, as it was working about a week ago on the same
server, and nothing (and I've double checked) has changed in the
configuration of the DB engine or in the PHP code.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: MySQL 4.1.1 Download
Are binaries of MySQL 4.1.1 available or we need to compile it from the source. No, binaries are not available yet. They will be available very soon, most probably this week (yes, I know today's Thursday :-). Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Once again, three queries, same result, huge speed difference
On Thu, 4 Dec 2003, Uros Kotnik wrote:
I posted this few days ago, but with no answer, also posted it to
benchmark list..
Executing this SQL, takes ~5 sec.
select artists.name , cds.title , tracks.title from artists, tracks,
cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and MATCH (artists.name) AGAINST ('madonna')
and MATCH (cds.title) AGAINST ('music')
and MATCH (cds.title) AGAINST ('mix')
and MATCH (cds.title) AGAINST ('2001')
and this, ~40 sec.
select artists.name , cds.title, tracks.title from artists, tracks, cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
and MATCH (cds.title) AGAINST ('music mix 2001' IN BOOLEAN MODE)
and executing this takes less than 1 sec.
select artists.name , cds.title, tracks.title from artists, tracks, cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and artists.name like '%madonna%'
and cds.title like '%music mix 2001%'
Same result but the speed difference is quite a different, why is that ?
This is only on test DB, I didn't try it on real life DB where I have
~14 mil. rows in tracks table.
Regards
Can you post EXPLAIN SELECT of those queries as well, please?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.1 Download
Are binaries of MySQL 4.1.1 available or we need to compile it from the source. Karam __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql UDF in windows
i find problem while compiling the UDF in windows. the function returns an integer and so i set the type as long long but this datatype is not recognised by VC++ 6.0 and i end up with a compilation error. how can i resolve this and what the prototype for functions that returns integer values. i searched the manual but got no solution. - Download Yahoo! Messenger now for a chance to WIN Robbie Williams Live At Knebworth DVD
Deleting using a table as reference.
Hi All! I have a problem deleting from a table and using another as reference. I use a query like: DELETE tableA FROM tableB WHERE tableA.idB = tableB.id AND tableA.data1=VALUE AND tableB.data2=VALUE (I'm using MySQL 4.0.12 on Red Hat 8 - Note: VALUE are integer values) What I want to do is delete some rows from tableA (which is a detail table for tableB) meeting some criteria (tableA.data1=VALUE) and with some value also in tableB. From the documentation, this is a correct DELETE statement, however, I get the following error: Not unique table/alias: 'tableA' As I understand, this error says that I cannot delete from tableA if I use it in the WHERE clause. But I need to list it in the where clause so that I can do the master/detail relationship. How can I do that? Please note that I use MySQL 4.0.12, so I cannot use the DELETE ... USING ... syntax which were added in 4.0.2 Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql UDF in windows
At 11:45 4/12/2003 +, Prem Soman wrote: Hi, how can i resolve this and what the prototype for functions that returns integer values. i searched the manual but got no solution. In the \sql\udf_example.cpp you find: #ifdef __WIN__ typedef unsigned __int64 ulonglong; /* Microsofts 64 bit types */ typedef __int64 longlong; #else typedef unsigned long long ulonglong; typedef long long longlong; #endif /*__WIN__*/ #else -- Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.547 / Virus Database: 340 - Release Date: 2/12/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Licence question
Thank you for all your response, but my question is very simple : Example : We have company 1 that make's a product that communicate with MySQL server using TCP/IP. This product (company 1) does not use the MySQL client to connect to MySQL server. (Don't ask me how, I don't know) (By the way, this product really exist, that is why I am asking this question). Therefore, if Company 2 has a MySQL server (commercial license) and purchases 100 product from company 1, does company 2 need a 100 MySQL client or driver licenses ??? I believe not (2 reasons) 1 - I paid company 1 for its product. 2 - the product does not use MySQL client to connect to MySQL server. This is what is bugging me, can you help ? thank you, -Original Message- From: Ron Albright [mailto:[EMAIL PROTECTED] Sent: 3 décembre, 2003 18:27 To: [EMAIL PROTECTED] Subject: Re: Licence question At 01:26 PM 12/3/2003, Chuck Gadd [EMAIL PROTECTED] wrote: This is your standard I am not a lawyer type answer, because reading the text of the GPL can be overwhelming, but the way I understand it, if you are shipping MySql with your app, then you've either got to release your app under the GPL, or you've got to buy a commercial Mysql license for each copy of your app that you ship. If you were to simply download and install MySQL at your company office, then write apps for in-house use at your company, then you have no license issues. Your apps would not need to be GPL, and you do not need a Mysql commercial license. This was discussed by a Mysql AB employee during the MySQL training class I took a few weeks ago. This is somewhat ambiguous. From the statements below it would appear to me that you can ship MySQL with an application as long as the your application does not directly link to the MySQL libraries as would be the case if embedded. But mere aggregation seems to apply even if your application starts the database as a separate executable. The last paragraph of the first question seems to allow shipping it along with your application but the last sentence leaves it somewhat open to question. From the GPL FAQ (http://www.gnu.org/licenses/gpl-faq.html): What is the difference between mere aggregation and combining two modules into one program? Mere aggregation of two programs means putting them side by side on the same CD-ROM or hard disk. We use this term in the case where they are separate programs, not parts of a single program. In this case, if one of the programs is covered by the GPL, it has no effect on the other program. Combining two modules means connecting them together so that they form a single larger program. If either part is covered by the GPL, the whole combination must also be released under the GPL--if you can't, or won't, do that, you may not combine them. What constitutes combining two parts into one program? This is a legal question, which ultimately judges will decide. We believe that a proper criterion depends both on the mechanism of communication (exec, pipes, rpc, function calls within a shared address space, etc.) and the semantics of the communication (what kinds of information are interchanged). If the modules are included in the same executable file, they are definitely combined in one program. If modules are designed to run linked together in a shared address space, that almost surely means combining them into one program. By contrast, pipes, sockets and command-line arguments are communication mechanisms normally used between two separate programs. So when they are used for communication, the modules normally are separate programs. But if the semantics of the communication are intimate enough, exchanging complex internal data structures, that too could be a basis to consider the two parts as combined into a larger program. If a program released under the GPL uses plug-ins, what are the requirements for the licenses of a plug-in. It depends on how the program invokes its plug-ins. If the program uses fork and exec to invoke plug-ins, then the plug-ins are separate programs, so the license for the main program makes no requirements for them. If the program dynamically links plug-ins, and they make function calls to each other and share data structures, we believe they form a single program, so plug-ins must be treated as extensions to the main program. This means they must be released under the GPL or a GPL-compatible free software license, and that the terms of the GPL must be followed when those plug-ins are distributed. If the program dynamically links plug-ins, but the communication between them is limited to invoking the `main' function of the plug-in with some options and waiting for it to return, that is a borderline case. Can I use the GPL for a plug-in for a non-free program? If the program uses fork and exec to invoke plug-ins, then the plug-ins are separate programs, so the license for the main program makes no requirements for them.
RE: Once again, three queries, same result, huge speed difference
Another thing that I noticed is :
This query takes less than sec :
SELECT artists.name, cds.title, tracks.title
FROM artists, tracks, cds
WHERE artists.artistid = tracks.artistid AND cds.cdid = tracks.cdid AND
MATCH ( name )
AGAINST ( 'madonna' )
But when I add one more AND it takes more than 15 min.
SELECT artists.name, cds.title, tracks.title
FROM artists, tracks, cds
WHERE artists.artistid = tracks.artistid AND cds.cdid = tracks.cdid AND
MATCH ( name )
AGAINST ( 'madonna' )
AND
MATCH ( cds.title )
AGAINST ( 'music' )
-Original Message-
From: Tobias Asplund [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 11:50
To: Uros Kotnik
Cc: [EMAIL PROTECTED]
Subject: Re: Once again, three queries, same result, huge speed
difference
On Thu, 4 Dec 2003, Uros Kotnik wrote:
I posted this few days ago, but with no answer, also posted it to
benchmark list..
Executing this SQL, takes ~5 sec.
select artists.name , cds.title , tracks.title from artists, tracks,
cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and MATCH (artists.name) AGAINST ('madonna')
and MATCH (cds.title) AGAINST ('music')
and MATCH (cds.title) AGAINST ('mix')
and MATCH (cds.title) AGAINST ('2001')
and this, ~40 sec.
select artists.name , cds.title, tracks.title from artists, tracks,
cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
and MATCH (cds.title) AGAINST ('music mix 2001' IN BOOLEAN MODE)
and executing this takes less than 1 sec.
select artists.name , cds.title, tracks.title from artists, tracks,
cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and artists.name like '%madonna%'
and cds.title like '%music mix 2001%'
Same result but the speed difference is quite a different, why is that
?
This is only on test DB, I didn't try it on real life DB where I have
~14 mil. rows in tracks table.
Regards
Can you post EXPLAIN SELECT of those queries as well, please?
--
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: Licence question
* Stéphane Bischoff Thank you for all your response, but my question is very simple : Example : We have company 1 that make's a product that communicate with MySQL server using TCP/IP. This product (company 1) does not use the MySQL client to connect to MySQL server. (Don't ask me how, I don't know) (By the way, this product really exist, that is why I am asking this question). Therefore, if Company 2 has a MySQL server (commercial license) and purchases 100 product from company 1, does company 2 need a 100 MySQL client or driver licenses ??? I believe not (2 reasons) 1 - I paid company 1 for its product. 2 - the product does not use MySQL client to connect to MySQL server. This is what is bugging me, can you help ? I'm no lawyer either, but I find this quote from the mysql download pages informative and to the point... slightly paraphrased: You need to purchase commercial non-GPL MySQL licenses if you distribute MySQL Software with your non open source software. If company 1 does not distribute MySQL software (C API included), they don't need a licence. Company 2 is the customer in this case, and does not need a licence in any case. (Need as in have to, they may need/want it because of warranty and/or community support issues.) If the product _really_ can connect to the server without client software... I don't know... check this: URL: http://www.mysql.com/products/licensing.html More specifically 3b: If you include one of the MySQL drivers in your non Open Source application (so that your application can run with MySQL), you need a commercial licence for the driver(s) in question. ...so that your application can run with MySQL... it is implied that you can not communicate with the server without a client, and that any client would be considered derived from the GPL'ed MySQL client...? Also note this snippet from the GPL FAQ: * Ron Albright [...] From the GPL FAQ (http://www.gnu.org/licenses/gpl-faq.html): [...] Combining two modules means connecting them together so that they form a single larger program. If either part is covered by the GPL, the whole combination must also be released under the GPL--if you can't, or won't, do that, you may not combine them. [...] By contrast, pipes, sockets and command-line arguments are communication mechanisms normally used between two separate programs. So when they are used for communication, the modules normally are separate programs. But if the semantics of the communication are intimate enough, exchanging complex internal data structures, that too could be a basis to consider the two parts as combined into a larger program. One could argue that the product delivered by company 1 is a combined larger program, depending on what kind of product it is. But again, this would not change the situation for Company 2. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mean/median/mode
From: Robert Citek [mailto:[EMAIL PROTECTED] Hello all, How can I calculate the mean/median/mode from a set of data using SQL? Mean seems to exist as the average (avg): select name, avg(value) from table group by name Is there a way to calculate median and mode with a group by clause? Median: the value at which 50% of the samples are above and below that value. Mode: the most common value For mode, this should work: SELECT COUNT(value) AS mode FROM table GROUP BY value ORDER BY mode DESC LIMIT 1; As for median, it's sort of a hack, but this may do the trick: SELECT FLOOR(COUNT(value)/2) FROM table; SELECT name, value FROM table ORDER BY value ASC LIMIT previous result, 1; Caveat: That'll work if you have an odd # of rows in the table (I have 15 in my test table); I don't know, mathematically, what median should return for a set of data of an even number, actually. The middle two? Or should it pick one? Good luck either way. -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FRM file disappears -- any way to rebuild or recover?
It seems that the .FRM file for one of my largest tables has disappeared. 180 million rows. I am not so much concerned about finding out WHY it was deleted as I am trying to figure out how to recover the table. I've tried making a new table using a similar data structure (the table is only three columns wide) and then using it's .FRM file as a definition. However, I cannot seem to get it quite right. The table is integer, integer, and then a varchar() or enum(). I can usually get the varchar/enum column correct, but the first two ints are giving me hassle. I know I used a combination of different integer types to conserve space (some of the numbers will never be higher than 100K or so). However, I cannot determine *what* integer types I've used. I always use unsigned, but I cannot recall if I used NOT NULL with a default value. Is there any way to solve this without trying the hundreds of permutations of integer definitions? -- R -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql user
i find problem while compiling the UDF in windows. the function returns an integer and so i set the type as long long but this datatype is not recognised by VC++ 6.0 and i end up with a compilation error. how can i resolve this and what the prototype for functions that returns integer values. i searched the manual but got no solution. - Download Yahoo! Messenger now for a chance to WIN Robbie Williams Live At Knebworth DVD
MySQL 4.1.1 has been released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 4.1.1, a new version of the popular Open Source/Free Software database management system, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is the second Alpha development release of the 4.1 tree, adding many new features (see below) and fixing recently discovered bugs. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. As this code is currently labeled Alpha, we do not recommend that this version be used in production environments yet! However, we encourage you to test and evaluate it and, more importantly, report any bugs or observations to our bug tracking database at http://bugs.mysql.com/. Please note, that for us to resolve a bug report, a reproducible test is required. See How to report a bug at http://bugs.mysql.com/how-to-report.php for more details before filing a bug report. We appreciate your support! For a more detailed list of features in MySQL 4.1, please see http://www.mysql.com/doc/en/MySQL_4.1_Nutshell.html News from the ChangeLog: Functionality added or changed: * Added `IGNORE' option for `DELETE' statement. * The MySQL source distribution now also includes the MySQL Internals Manual `internals.texi'. * Added `mysql_set_server_option()' C API client function to allow multiple statement handling in the server to be enabled or disabled. * The `mysql_next_result()' C API function now returns `-1' if there are no more result sets. * Renamed `CLIENT_MULTI_QUERIES' connect option flag to `CLIENT_MULTI_STATEMENTS'. To allow for a transition period, the old option will continue to be recognized for a while. * Require `DEFAULT' before table and database default character set. This enables us to use `ALTER TABLE table_name ... CHARACTER SET=...' to change the character set for all `CHAR', `VARCHAR', and `TEXT' columns in a table. * Added `MATCH ... AGAINST( ... WITH QUERY EXPANSION)' and the `ft_query_expansion_limit' server variable. * Removed unused `ft_max_word_len_for_sort' server variable. * Full-text search now supports multi-byte character sets and the Unicode `utf8' character set. (The Unicode `ucs2' character set is not yet supported.) * Phrase search in `MATCH ... AGAINST ( ... IN BOOLEAN MODE)' no longer matches partial words. * Added aggregate function `BIT_XOR()' for bitwise XOR operations. * Replication over SSL now works. * The `START SLAVE' statement now supports an `UNTIL' clause for specifying that the slave SQL thread should be started but run only until it reaches a given position in the master's binary logs or in the slave's relay logs. * Produce warnings even for single-row `INSERT' statements, not just for multiple-row `INSERT' statements. Previously, it was necessary to set `SQL_WARNINGS=1' to generate warnings for single-row statements. * Added `delimiter' (`\d') command to the `mysql' command-line client for changing the statement delimiter (terminator). The default delimiter is semicolon. * `CHAR', `VARCHAR', and `TEXT' columns now have lengths measured in characters rather than in bytes. The character size depends on the column's character set. This means, for example, that a `CHAR(n)' column for a multi-byte character set will take more storage than before. Similarly, index values on such columns are measured in characters, not bytes. * The `DATABASE()' function now returns `NULL' rather than the empty string if there is no database selected. * Added `--sql-mode=NO_AUTO_VALUE_ON_ZERO' option to suppress the usual behaviour of generating the next sequence number when zero is stored in an `AUTO_INCREMENT' column. With this mode enabled, zero is stored as zero; only storing `NULL' generates a sequence number. * *Warning: Incompatible change!* Client authentication now is based on 41-byte passwords in the `user' table, not 45-byte passwords as in 4.1.0. Any 45-byte passwords created for 4.1.0 must be reset after running the `mysql_fix_privilege_tables' script. * *Warning: Incompatible change!* Renamed the C API `mysql_prepare_result()' function to `mysql_get_metadata()' as the old name was confusing. * Added `DROP USER 'username'@'hostname'' statement to drop an account that has no privileges. * The interface to aggregated UDF functions has changed a bit. You must now declare a `xxx_clear()' function for each aggregate function `XXX()'. * The
Re: Once again, three queries, same result, huge speed difference
You need to take cache into consideration when doing your testing. Both MySQL cache and the OS cache. That means rebooting between each query that you run to clear the database and OS cache. -or- Run each query 3 or 4 times (or 5, or even 10) consecutively and either take the average or the fastest. Doing it this way will make sure that the cache is used equally for all queries. You should also do and EXPLAIN to see how MySQL is executing each query. On Dec 4, 2003, at 5:35 AM, Uros Kotnik wrote: Same result but the speed difference is quite a different, why is that ? This is only on test DB, I didn't try it on real life DB where I have ~14 mil. rows in tracks table. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1.1 has been released
Hi Lenz, Lenz Grimmer wrote: MySQL 4.1.1, a new version of the popular Open Source/Free Software database management system, has been released. It is now available in source and binary form for a number of platforms from our download pages Any chance to offer binaries for PowerPC Linux as well ? (Preferable tgz) Thanks in advance Gunnar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FRM file disappears -- any way to rebuild or recover?
* Robert It seems that the .FRM file for one of my largest tables has disappeared. 180 million rows. I am not so much concerned about finding out WHY it was deleted as I am trying to figure out how to recover the table. I've tried making a new table using a similar data structure (the table is only three columns wide) and then using it's .FRM file as a definition. However, I cannot seem to get it quite right. You are on the right track...: URL: http://www.mysql.com/doc/en/Repair.html Stage 4, Very difficult repair, point 2. The table is integer, integer, and then a varchar() or enum(). I can usually get the varchar/enum column correct, but the first two ints are giving me hassle. Whoops. Then we are beyond Very difficult repair, maybe an extremely difficult repair...? ;) Do you know the max values? Do you know the combined width in the .MYD file for these two columns? (16 = bigint+bigint, 12 = bigint+mediumint ... 2 = tinyint+tinyint) By inspecting the start of the .MYD file you should be able to see the record length... look for repeating byte sequences and known values... could be tricky, but I don't know another way. If the third column is a varchar, you should see the string values, and you will have a variable record length. If it is a enum there will be binary values, 1, 2, 4 or 8 bytes could be used, depending on the number of values in the enum. Note that the string values of enum columns are _only_ stored in the .frm file, the data file only contains an integer poining to the correct string. In other words, you have to reconstruct these strings somehow, if it is an enum. I know I used a combination of different integer types to conserve space (some of the numbers will never be higher than 100K or so). However, I cannot determine *what* integer types I've used. I always use unsigned, but I cannot recall if I used NOT NULL with a default value. When there are only three columns, the NULL flags will not take any space, i.e. the same byte as is used for the deletion flag is also used for the NULL flags. In other words, it does not change the physical record length. Is there any way to solve this without trying the hundreds of permutations of integer definitions? Not so many combinations if you can find the combined width... integers can be 1, 2, 3, 4 or 8 bytes: width permutations 3 1+2,2+1 4 1+3,2+2,3+1 5 1+4,2+3,3+2,4+1 6 2+4,3+3,4+2 7 3+4,4+3 8 4+4 9 1+8,8+1 If the combined width is 5, you must test 4 combinations. Note that all of them will work technically, i.e. you won't get errors, but only one of them will give you the right data in the columns. Good luck! -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
String Functions
Is there any function to strip non numeric characters from a string?
Such that FUNCTION('1-(519)-473-3853') would return '15194733853'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1.1 has been released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Thu, 4 Dec 2003, Boehn, Gunnar von wrote: Any chance to offer binaries for PowerPC Linux as well ? (Preferable tgz) Yes, we plan to offer Linux/PPC binaries in the near future - we're currently working on purchasing a system for that. I have not decided which Linux Distribution to use, though - maybe Yellowdog? Unfortunately my favourite Linux distribution (SuSE) does not support the PPC platform anymore :( Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE/z1T8SVDhKrJykfIRAl3oAJ9btP3tEBIRhw1EP9SkuIJCxM+LegCfSWbd 483z4GYhyEYmm4i5sJ/DvJ8= =FKbY -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New Microsoft Critical Patch
Can we do anything about these? I am getting several ofthem a day. -Original Message-From: Microsoft Corporation Technical Services [mailto:[EMAIL PROTECTED]Sent: Thursday, December 04, 2003 11:07 AMTo: ConsumerSubject: New Microsoft Critical Patch Microsoft All Products| Support| Search| Microsoft.com Guide Microsoft Home Microsoft Consumerthis is the latest version of security update, the "October 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to protect your computer from these vulnerabilities, the most serious of which could allow an attacker to run code on your system. This update includes the functionality of all previously released patches. System requirements Windows 95/98/Me/2000/NT/XP This update applies to MS Internet Explorer, version 4.01 and laterMS Outlook, version 8.00 and laterMS Outlook Express, version 4.01 and later Recommendation Customers should install the patch at the earliest opportunity. How to install Run attached file. Choose Yes on displayed dialog box. How to use You don't need to do anything after installing this item. Microsoft Product Support Services and Knowledge Base articles can be found on the Microsoft Technical Support web site. For security-related information about Microsoft products, please visit the Microsoft Security Advisor web site, or Contact Us. Thank you for using Microsoft products.Please do not reply to this message. It was sent from an unmonitored e-mail address and we are unable to respond to any replies. The names of the actual companies and products mentioned herein are the trademarks of their respective owners. Contact Us | Legal | TRUSTe ©2003 Microsoft Corporation. All rights reserved. Terms of Use | Privacy Statement| Accessibility
RE: Once again, three queries, same result, huge speed difference
Hmmm, if I execute this 3 queries at any time in any order I get the
same execution time.
Yes, explain...
explain select artists.name , cds.title , tracks.title from artists,
tracks, cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and MATCH (artists.name) AGAINST ('madonna')
and MATCH (cds.title) AGAINST ('music')
and MATCH (cds.title) AGAINST ('mix')
and MATCH (cds.title) AGAINST ('2001')
| table | type | possible_keys | key| key_len
| ref | rows | Extra
| artists | fulltext | PRIMARY,name | name | 0
| | 1 | Using where |
| tracks | ref | PRIMARY,artistIndex| artistIndex| 5
| artists.artistId | 27 | Using where |
| cds | fulltext | PRIMARY,fulltext_title | fulltext_title | 0
| |1 | Using where |
explain select artists.name , cds.title, tracks.title from artists,
tracks, cds where artists.artistid = tracks.artistid and cds.cdid =
tracks.cdid
and MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
and MATCH (cds.title) AGAINST ('music mix 2001' IN BOOLEAN MODE)
| table | type | possible_keys | key| key_len
| ref
| rows | Extra |
+-+--+++
-+--
| artists | fulltext | PRIMARY,name | name | 0
|
|1 | Using where |
| tracks | ref | PRIMARY,artistIndex| artistIndex| 5
| artis
ts.artistId | 27 | Using where |
| cds | fulltext | PRIMARY,fulltext_title | fulltext_title | 0
|
|1 | Using where |
explain select artists.name , cds.title, tracks.title from artists,
tracks, cds where artists.artistid = tracks.artistid and cds.cdid =
tracks.cdid
and artists.name like '%madonna%'
and cds.title like '%music mix 2001%'
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+-++-+-+-+--
+-++-+-+-+
| artists | ALL| PRIMARY | NULL|NULL | NULL
| 23806 | Using where |
| tracks | ref| PRIMARY,artistIndex | artistIndex | 5 |
artists.artis
tId |27 | Using where |
| cds | eq_ref | PRIMARY | PRIMARY | 4 |
tracks.cdId
| 1 | Using where |
-Original Message-
From: Brent Baisley [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 16:38
To: Uros Kotnik
Cc: [EMAIL PROTECTED]
Subject: Re: Once again, three queries, same result, huge speed
difference
You need to take cache into consideration when doing your testing. Both
MySQL cache and the OS cache. That means rebooting between each query
that you run to clear the database and OS cache.
-or-
Run each query 3 or 4 times (or 5, or even 10) consecutively and either
take the average or the fastest. Doing it this way will make sure that
the cache is used equally for all queries.
You should also do and EXPLAIN to see how MySQL is executing each query.
On Dec 4, 2003, at 5:35 AM, Uros Kotnik wrote:
Same result but the speed difference is quite a different, why is that
?
This is only on test DB, I didn't try it on real life DB where I have
~14 mil. rows in tracks table.
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: String Functions
Try the Replace function
replace the `-` character with ``
-Original Message-
From: Luc Foisy [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 10:09 AM
To: MYSQL-List (E-mail)
Subject: String Functions
Is there any function to strip non numeric characters from a string?
Such that FUNCTION('1-(519)-473-3853') would return '15194733853'
--
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: FRM file disappears -- any way to rebuild or recover?
Roger,
Thanks for the info!
I am examining the file in a HEX editor right now and have identified the
pattern. The table size divided by the pattern byte length is 8 bytes. The
last byte is always one of three characters ('S', 'B', or 'U') as defined by
the program that feeds this table. So I *know* that part is correct. The
file size in bytes divided by 8 accurately reflects the number of rows in
the table (it is perfectly divisible, no remainder, which is either lucky or
means I used an enum() or char()).
Nonetheless, I've been going through the remaining 7 characters with a
HEX - integer conversion, which I believe may be incorrect.
I do have a pattern in the MYD file:
F1 02 18 00 54 7A ED 01 53 -- the first line of the file
F1 66 17 00 7C 0A 84 01 53-- about 80% into the file
F1 6E 11 00 FC 0E 00 00 53 -- the last line of the file
So it's clear that the first byte is always, at least generally, F1. The
second two bytes represent something. Then there's always hex 00, followed
by three more bytes, then a 01 or 00. Of course, there are 180 million
rows... so I can't be entirely certain, but random sampling of about 1000
rows shows the pattern is true.
I'm having a time deciphering this stuff. The mediumint and int say they use
3 bytes and 4 bytes each, respectively, but using that in the table
definition yields invalid numbers.
Any ideas?
-- Robert
- Original Message -
From: Roger Baklund [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Robert [EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 9:52 AM
Subject: Re: FRM file disappears -- any way to rebuild or recover?
Whoops. Then we are beyond Very difficult repair, maybe an extremely
difficult repair...? ;)
Do you know the max values? Do you know the combined width in the .MYD
file
for these two columns? (16 = bigint+bigint, 12 = bigint+mediumint ... 2 =
tinyint+tinyint)
By inspecting the start of the .MYD file you should be able to see the
record length... look for repeating byte sequences and known values...
could
be tricky, but I don't know another way. If the third column is a varchar,
you should see the string values, and you will have a variable record
length. If it is a enum there will be binary values, 1, 2, 4 or 8 bytes
could be used, depending on the number of values in the enum. Note that
the
string values of enum columns are _only_ stored in the .frm file, the data
file only contains an integer poining to the correct string. In other
words,
you have to reconstruct these strings somehow, if it is an enum.
I know I used a combination of different integer types to conserve space
(some of the numbers will never be higher than 100K or so). However, I
cannot determine *what* integer types I've used. I always use unsigned,
but
I cannot recall if I used NOT NULL with a default value.
When there are only three columns, the NULL flags will not take any space,
i.e. the same byte as is used for the deletion flag is also used for the
NULL flags. In other words, it does not change the physical record length.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.1. - WITH QUERY EXPANSION
Hi, In 4.1.1. new features there is: * Added `MATCH ... AGAINST( ... WITH QUERY EXPANSION)' and the `ft_query_expansion_limit' server variable. What is WITH QUERY EXPANSION? I found no details in manual. mirza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: String Functions
That I can do
REPLACE(REPLACE(REPLACE(REPLACE(str,'(',''),')',''),'-',''),' ','')
Looks kinda horrid to handle removing just four characters ( ) - and space
And if any other characters end up in str, then they are not handled, and my formula
is broken.
So there is no functions that would do what I need?
Anyone have some kind of formula (multiple functions perhaps) that would simulate
what I need, something that would only return numeric characters?
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 11:15 AM
To: Luc Foisy; MYSQL-List (E-mail)
Subject: RE: String Functions
Try the Replace function
replace the `-` character with ``
-Original Message-
From: Luc Foisy [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 10:09 AM
To: MYSQL-List (E-mail)
Subject: String Functions
Is there any function to strip non numeric characters from a string?
Such that FUNCTION('1-(519)-473-3853') would return '15194733853'
--
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]
Install of mysql 4.1.x on RH 9
Dear all, I am positive this has been asked a 1000 times before, but i cannot find it anywhere on the archive. Maybe a good search function there would help :) Ok, here my problem: I am trying to install mysql 4.1.1 on RH 9.0, but get the following errror: [EMAIL PROTECTED] mysql]# rpm -Uvh --force MySQL-client-4.1.1-0.i386.rpm MySQL-devel-4.1.1-0.i386.rpm MySQL-server-4.1.1-0.i386.rpm MySQL-shared-4.1.1-0.i386.rpm warning: MySQL-client-4.1.1-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5error: Failed dependencies: libcrypto.so.0.9.6 is needed by MySQL-shared-4.1.1-0 libssl.so.0.9.6 is needed by MySQL-shared-4.1.1-0 [EMAIL PROTECTED] mysql]# whereis libcrypto libcrypto: /usr/lib/libcrypto.a /usr/lib/libcrypto.so [EMAIL PROTECTED] mysql]# locate libcrypto /usr/lib/libcrypto.a /usr/lib/libcrypto.so /lib/libcrypto.so.4 /lib/libcrypto.so.0.9.7a /lib/libcrypto.so.2 /lib/libcrypto.so.0.9.6b [EMAIL PROTECTED] mysql]# rpm -q openssl openssl-0.9.7a-20 [EMAIL PROTECTED] mysql]# As you can see, libcrypto is installed. Also the openssl package is installed, which contains both libcrypto and libssl. However, the mysql package does not recognise this. Is there a solution for this. compiling the mysql binary and/or installing a libcrypto/libssl rpm I would not really call a solution. Anyone? Thanx /rudy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fulltext search speed issue with SQL_CALC_FOUND_ROWS
I have some_table with 100,000 rows and with an
average of 500 words in some_column of each row. When
i do a fulltext search on this table using a query
such as the following, all of my results are under 0.1
seconds:
SELECT something
FROM some_table
WHERE MATCH (some_column) AGAINST ('some_search_term')
LIMIT 0,10
However, when i add the SQL_CALC_FOUND_ROWS keyword
like in the following query, some queries take longer
than 1 minute:
SELECT SQL_CALC_FOUND_ROWS something
FROM some_table
WHERE MATCH (some_column) AGAINST ('some_search_term')
LIMIT 0,10
How can there be a huge difference in speed if both
queries always return the exact same results?
Thanks,
TK
__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Licence question
Hi Stéphane, I share the comments already expressed by Roger. But would like to add the following: - If you represent company 2 you will not have to purchase the client licenses. - It seems to me it is the sole responsibility of company 1 to secure the legality and the compliance to the GPL licence of their product or alternatively include a non-GPL license in their product. You might want to ask them about this if you feel unsure or have doubts. Jan -Original Message- From: Stéphane Bischoff [mailto:[EMAIL PROTECTED] Sent: Thursday, December 04, 2003 15:31 To: 'Ron Albright' Cc: MySQL (E-mail) Subject: RE: Licence question Thank you for all your response, but my question is very simple : Example : We have company 1 that make's a product that communicate with MySQL server using TCP/IP. This product (company 1) does not use the MySQL client to connect to MySQL server. (Don't ask me how, I don't know) (By the way, this product really exist, that is why I am asking this question). Therefore, if Company 2 has a MySQL server (commercial license) and purchases 100 product from company 1, does company 2 need a 100 MySQL client or driver licenses ??? I believe not (2 reasons) 1 - I paid company 1 for its product. 2 - the product does not use MySQL client to connect to MySQL server. This is what is bugging me, can you help ? thank you, -Original Message- From: Ron Albright [mailto:[EMAIL PROTECTED] Sent: 3 décembre, 2003 18:27 To: [EMAIL PROTECTED] Subject: Re: Licence question At 01:26 PM 12/3/2003, Chuck Gadd [EMAIL PROTECTED] wrote: This is your standard I am not a lawyer type answer, because reading the text of the GPL can be overwhelming, but the way I understand it, if you are shipping MySql with your app, then you've either got to release your app under the GPL, or you've got to buy a commercial Mysql license for each copy of your app that you ship. If you were to simply download and install MySQL at your company office, then write apps for in-house use at your company, then you have no license issues. Your apps would not need to be GPL, and you do not need a Mysql commercial license. This was discussed by a Mysql AB employee during the MySQL training class I took a few weeks ago. This is somewhat ambiguous. From the statements below it would appear to me that you can ship MySQL with an application as long as the your application does not directly link to the MySQL libraries as would be the case if embedded. But mere aggregation seems to apply even if your application starts the database as a separate executable. The last paragraph of the first question seems to allow shipping it along with your application but the last sentence leaves it somewhat open to question. From the GPL FAQ (http://www.gnu.org/licenses/gpl-faq.html): What is the difference between mere aggregation and combining two modules into one program? Mere aggregation of two programs means putting them side by side on the same CD-ROM or hard disk. We use this term in the case where they are separate programs, not parts of a single program. In this case, if one of the programs is covered by the GPL, it has no effect on the other program. Combining two modules means connecting them together so that they form a single larger program. If either part is covered by the GPL, the whole combination must also be released under the GPL--if you can't, or won't, do that, you may not combine them. What constitutes combining two parts into one program? This is a legal question, which ultimately judges will decide. We believe that a proper criterion depends both on the mechanism of communication (exec, pipes, rpc, function calls within a shared address space, etc.) and the semantics of the communication (what kinds of information are interchanged). If the modules are included in the same executable file, they are definitely combined in one program. If modules are designed to run linked together in a shared address space, that almost surely means combining them into one program. By contrast, pipes, sockets and command-line arguments are communication mechanisms normally used between two separate programs. So when they are used for communication, the modules normally are separate programs. But if the semantics of the communication are intimate enough, exchanging complex internal data structures, that too could be a basis to consider the two parts as combined into a larger program. If a program released under the GPL uses plug-ins, what are the requirements for the licenses of a plug-in. It depends on how the program invokes its plug-ins. If the program uses fork and exec to invoke plug-ins, then the plug-ins are separate programs, so the license for the main program makes no requirements for them. If the program dynamically links plug-ins, and they make function calls to each other and share data structures, we believe
Aliases
If you can do this: SELECT table_a_alias.col_name FROM table_a table_a_alias; Why can't you do this: SELECT 1 AS A, A + 1 AS B, B + 1 AS C; Why can't you use column aliases later in the select? When you can use table aliases even before they are defined. thnx, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Install of mysql 4.1.x on RH 9
On Thursday 04 December 2003 16:27, Rudy Metzger wrote: Dear all, I am positive this has been asked a 1000 times before, but i cannot find it anywhere on the archive. Maybe a good search function there would help :) Ok, here my problem: I am trying to install mysql 4.1.1 on RH 9.0, but get the following errror: [EMAIL PROTECTED] mysql]# rpm -Uvh --force MySQL-client-4.1.1-0.i386.rpm MySQL-devel-4.1.1-0.i386.rpm MySQL-server-4.1.1-0.i386.rpm MySQL-shared-4.1.1-0.i386.rpm warning: MySQL-client-4.1.1-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5error: Failed dependencies: libcrypto.so.0.9.6 is needed by MySQL-shared-4.1.1-0 libssl.so.0.9.6 is needed by MySQL-shared-4.1.1-0 --nodeps eliminates the error, and the software runs without issue (so far for me). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
WHERE with CASE colums?
I have a table called 'Journal_Info' containing titles of various journals When searching and listing them I want to remove any preceeding 'The ', 'An ', or 'A ' that occurs in the title and I also want to be able to search it. The case statement as follows seems to work fine: SELECT CASE WHEN title LIKE 'The %' THEN RIGHT( title, length( title ) -4 ) WHEN title LIKE 'A %' THEN RIGHT( title, length( title ) -2 ) WHEN title LIKE 'An %' THEN RIGHT( title, length( title ) -3 ) ELSE title END AS modtitle FROM Journal_Info But if I try a WHERE clause using the columns AS title I get an error. Can anyone help me figure out how to search the CASEed column? Thanks in advance for any help. Scott
Re: RAID Strip size
Actually, you want to try to match the stripe size to your data size. The ideal would be to have a stripe size equal to the size of a record in your database. This way the disk needs only one read or write for each database record. You really don't want to fragment a record. A large stripe size is good if you have large files, like graphic files, that you read in their entirety. It's bad if you are reading small amounts of data, like in a database. For instance, if you set a stripe size of 128K and you need to read 100 records that are not in the same disk sector, the disk ends up retrieving over 12MB of data. If your typical record only contains 2K of data, that's only 200K of data you need out of the 12MB that the disk retrieved. That's a big waste. On the flip side, a stripe size too small will fragment your records and cause excessive disk access. On the other hand, if you are typically doing full table scans, then you are reading most of a large file and a large stripe size would be good. You really need to know your data and how it is accessed in order to set an optimal stripe size. Even then, you need to benchmark to see if what you set if correct, especially since different tables have different data sizes and access patterns. When in doubt, it's usually best to leave the stripe size at the typical default of 4K. The reason for this is that most operating systems track RAM in 4K increments, so there is a one-to-one relation between disk sector size and RAM sector size. At least that's what I learned a few years ago in an IBM class, perhaps RAM is tracked differently now with the extremely large RAM configurations that are now possible. On Dec 3, 2003, at 4:05 PM, trevor%tribenetwork.com wrote: Greetings Mysqlians, Please comment on the validity of my logic: In setting the RAID(10/2disks) strip size everything I read says you must benchmark your particular system. Since that is not an option, my current logic is to have a large strip size (1024) with the reasoning that fewer writes/reads (yet longer writes) will be better in a database which has a large amount of disk access. The disk cache size is 1GB on our disk device but that is not quite enough to hold all the tables which get accessed(written to and read from) frequently. I figure setting a large stripe size is a conservative approach allowing for better scalability. Many Thanks, Trevor -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible benchmark for mySQL?
Hello, I'm in the midst of using mySQL for some genetic information searching based upon the GenBank data from the NCBI, National Center for Biotechnology Information. In doing some testing on using mySQL, and began to wonder if this data set would be of interest as a benchmark for the database? The following information was taken from a recent run at loading in a portion (500k records) of the data. The full data set has almost 30M records so would not likely be pleasant to store and/or distribute. But the data is publicly available and substantial. Please take a look at the timings on some of the activities shown below. Brad Eacker ([EMAIL PROTECTED]) Load in the data (500,000) rows mysql create table gb_locus ( - gbl_id int primary key, - gbl_fileID int, - gbl_locus varchar(20), - gbl_sizeint, - gbl_datedate, - gbl_phylum char(3), - gbl_foffset int - ); Query OK, 0 rows affected (0.00 sec) mysql load data infile '/hda3/beacker/gene/genbank/a' into table gb_locus - fields terminated by ','; Query OK, 50 rows affected (10.58 sec) Records: 50 Deleted: 0 Skipped: 0 Warnings: 0 Storage used: -rw-rw1 mysqlmysql18141068 Dec 3 20:03 gb_locus.MYD -rw-rw1 mysqlmysql 4098048 Dec 3 20:03 gb_locus.MYI Access data: mysql select gbl_phylum, count(*) from gb_locus group by gbl_phylum; ++--+ | gbl_phylum | count(*) | ++--+ | BCT| 210778 | | CON|11472 | | EST| 277750 | ++--+ 3 rows in set (6.83 sec) Raw data: [EMAIL PROTECTED] genbank]$ ls -l a -rw-rw-r--1 beacker beacker 25758542 Dec 3 17:33 a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: String Functions
What programming language are you using? You could write a method to compare
each character and return only those 0-9 as the output and discard the rest.
-Original Message-
From: Luc Foisy [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 10:26 AM
To: Victor Pendleton; MYSQL-List (E-mail)
Subject: RE: String Functions
That I can do
REPLACE(REPLACE(REPLACE(REPLACE(str,'(',''),')',''),'-',''),' ','')
Looks kinda horrid to handle removing just four characters ( ) - and space
And if any other characters end up in str, then they are not handled, and my
formula is broken.
So there is no functions that would do what I need?
Anyone have some kind of formula (multiple functions perhaps) that would
simulate what I need, something that would only return numeric characters?
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 11:15 AM
To: Luc Foisy; MYSQL-List (E-mail)
Subject: RE: String Functions
Try the Replace function
replace the `-` character with ``
-Original Message-
From: Luc Foisy [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 10:09 AM
To: MYSQL-List (E-mail)
Subject: String Functions
Is there any function to strip non numeric characters from a string?
Such that FUNCTION('1-(519)-473-3853') would return '15194733853'
--
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: Licence question
We have company 1 that make's a product that communicate with MySQL server using TCP/IP. This product (company 1) does not use the MySQL client to connect to MySQL server. (Don't ask me how, I don't know) (By the way, this product really exist, that is why I am asking this question). Therefore, if Company 2 has a MySQL server (commercial license) and purchases 100 product from company 1, does company 2 need a 100 MySQL client or driver licenses ??? http://www.mysql.com/products/licensing-examples.html You need a license if you sell a product designed specifically for use with MySQL or that requires the MySQL server to function at all. This is true whether or not you provide MySQL for your client as part of your product distribution. Seems to me that company 1 should have the commercial MySQL license. Company 2 is not selling anything so they should not need a license. Kaarel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FRM file disappears -- any way to rebuild or recover?
Hi! On Dec 04, Reverend Deuce wrote: I'm having a time deciphering this stuff. The mediumint and int say they use 3 bytes and 4 bytes each, respectively, but using that in the table definition yields invalid numbers. Any ideas? As you still have MYI file, you can simply use myisamchk -dvv to get the table definition. It will be not exactly your table definition, as more than one MySQL data type are mapped to one MyISAM data type (e.g. DATE in MySQL is MEDIUMINT in MyISAM), but you'll get back your data. 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]
Openssl support?
I compiled MySQL 4.0.16 --with-openssl --with-vio and when I look at the variables, has_openssl is set to NO. What could have happened to cause this? -Greg G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Once again, three queries, same result, huge speed difference
It's not the order in which you execute the queries, it's how many
time. Execute the first one 5 times, then the second one 5 times, then
the third one 5 times. See if the times are different between each of
the 5 runs for each query.
Also, you could try reordering your query. Perhaps something like
select fields from cds, artists, tracks...
On Dec 4, 2003, at 10:45 AM, Uros Kotnik wrote:
Hmmm, if I execute this 3 queries at any time in any order I get the
same execution time.
Yes, explain...
explain select artists.name , cds.title , tracks.title from artists,
tracks, cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and MATCH (artists.name) AGAINST ('madonna')
and MATCH (cds.title) AGAINST ('music')
and MATCH (cds.title) AGAINST ('mix')
and MATCH (cds.title) AGAINST ('2001')
| table | type | possible_keys | key|
key_len
| ref | rows | Extra
| artists | fulltext | PRIMARY,name | name |
0
| | 1 | Using where |
| tracks | ref | PRIMARY,artistIndex| artistIndex|
5
| artists.artistId | 27 | Using where |
| cds | fulltext | PRIMARY,fulltext_title | fulltext_title |
0
| |1 | Using where |
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FRM file disappears -- any way to rebuild or recover?
* Reverend Deuce aka Robert
I am examining the file in a HEX editor right now and have identified the
pattern. The table size divided by the pattern byte length is 8 bytes. The
last byte is always one of three characters ('S', 'B', or 'U') as
defined by
the program that feeds this table. So I *know* that part is correct. The
file size in bytes divided by 8 accurately reflects the number of rows in
the table (it is perfectly divisible, no remainder, which is
either lucky or means I used an enum() or char()).
I'm a bit confused... I would expect the total size to be devideable by 9,
the record length + 1. Below you show 9 bytes per line of hex dump...
Nonetheless, I've been going through the remaining 7 characters with a
HEX - integer conversion, which I believe may be incorrect.
I do have a pattern in the MYD file:
F1 02 18 00 54 7A ED 01 53 -- the first line of the file
F1 66 17 00 7C 0A 84 01 53-- about 80% into the file
F1 6E 11 00 FC 0E 00 00 53 -- the last line of the file
So it's clear that the first byte is always, at least generally, F1. The
second two bytes represent something. Then there's always hex 00, followed
by three more bytes, then a 01 or 00. Of course, there are 180 million
rows... so I can't be entirely certain, but random sampling of about 1000
rows shows the pattern is true.
The first byte is the deletion/null flag byte. The next three bytes could be
a MEDIUMINT, followed by a INT and finally a CHAR (S in all example rows).
BY experimenting I found that I get F1 in the first column if I do _not_ use
NOT NULL in any of the field definitions.
I'm having a time deciphering this stuff. The mediumint and int
say they use
3 bytes and 4 bytes each, respectively, but using that in the table
definition yields invalid numbers.
What do you mean with 'invalid nunmbers'? Did you try MEDIUMINT+INT, or only
INT+MEDIUMINT? Did you define any column as NOT NULL?
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 : curious privilege problems (grant, use, show databases)
Hi. Upgrading to 4.1.1 from 4.1.0 seems to solve my problem. There is no more (at the moment) curious privileges problems. Everything seem OK. Thanks to MySQL. N.B.: Please se the history of the problem bellow. Christophe. On Tue, 2 Dec 2003, Sergei Golubchik wrote: Hi! On Nov 25, Christophe DIARRA wrote: Hello. MySQL 4.1.0 is not respecting the registered privileges. ... show databases displays an incorrect list of databases. Each user lists a database it should not, and doesn't show all the databases it should. Same thing with 'use database' : the access is denied for some databases for which the user s granted 'all privileges'. Sorry, I don't have any idea so far :( Some things you can do: 1. upgrade to 4.1.1 (should be out very soon) and try if the bug dissapears 2. try to create a complete repeatable test case that I can use to repeat this behaviour and submit it to bugs.mysql.com. Then the bug will be fixed asap. Regards, Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE with CASE colums?
Hi Scott, === When searching and listing them I want to remove any preceeding 'The ', 'An ', or 'A ' that occurs in the title and I also want to be able to search it. The case statement as follows seems to work fine: SELECT CASE WHEN title LIKE 'The %' THEN RIGHT( title, length( title ) -4 ) WHEN title LIKE 'A %' THEN RIGHT( title, length( title ) -2 ) WHEN title LIKE 'An %' THEN RIGHT( title, length( title ) -3 ) ELSE title END AS modtitle FROM Journal_Info But if I try a WHERE clause using the columns AS title I get an error. Can anyone help me figure out how to search the CASEed column? == Are you trying: WHERE modtitle = '... something ... ' ? Cause the WHERE clause only works on columns. If you want to do a match on the result of the CASE, you have to do your case thingy again: WHERE ( case ...yourstuff... end ) = 'something' With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to query
I'm stumped... I have a pretty standard invoice system: create table invoice( InvoiceNum varhcar(16), ClientInfo ) create table line_items( InvoiceNum varchar(16), ProductCode varchar(10), Description varchar(25), Quantity . ) Three is, of course, a one-to-many relation between invoice and line_items. Now, I need to generate reports for a specific client. In the end, for a given month, I have to tell the client 1. Which invoices were for the purchase of blue widgets, only. 2. Which invoices were for the purchase of blue widgets and yellow widgets, together. 3. Which invoices for the purchase of green doodads, only. 4. Which for green doodads along with yellow widgets, together. etc. Out of 1,000 product codes, I'm interested in 10 or so. It's easy enough to write any one of the queries but I can't figure out how to group/order in such a way as to do it all in one query and to order by the 10 or so products I'm interested in. MySQL is 4.0.12. Thanks. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Openssl support?
I compiled MySQL 4.0.16 --with-openssl --with-vio and when I look at the variables, has_openssl is set to NO. What could have happened to cause this? -Greg G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE with CASE colums?
On Thursday, December 04, 2003 6:22 PM CET, Martijn Tonies wrote: Cause the WHERE clause only works on columns. If you want to do a match on the result of the CASE, you have to do your case thingy again: WHERE ( case ...yourstuff... end ) = 'something' Wouldn't HAVING help here? -- Yves Goergen [EMAIL PROTECTED] Please don't CC me (causes double mails) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql 3.23.51 installation errors
I came across the following errors after install I am not able to start server from terminal (only progress with root not user) 1. ERROR: 1062 Duplicate entry 'localhost-root' for key 1 ERROR: 1062 Duplicate entry 'localhost-root' for key 1 2. WARNING: The host 'localhost' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL deamon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! 3. chown: mysql: invalid user name Starting mysqld daemon with databases from /Library/MySQL/var 031203 21:19:05 mysqld ended Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists! Please check that /tmp/mysql.sock' exists! any references or help on these issues? running Darwin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Openssl support?
I realized that I should probably mention that I've got OpenSSL 0.9.7c installed. I'm seeing this on both Solaris and Debian hosts. I compiled MySQL 4.0.16 --with-openssl --with-vio and when I look at the variables, has_openssl is set to NO. What could have happened to cause this? -Greg g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[OT] RE: New Microsoft Critical Patch
Can we do anything about these? I am getting several of them a day. Do delete them WITHOUT having read them as soon as possible! Someone wants to abuse M$ Security issues for him/her and to infect your PC! These mails almost like M$, but only almost. Have close look at the sending address. If it really was from Mc$oft, the sender would have been something like [EMAIL PROTECTED] HTH, Jakob -- +++ GMX - die erste Adresse für Mail, Message, More +++ Neu: Preissenkung für MMS und FreeMMS! http://www.gmx.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Install of mysql 4.1.x on RH 9
Rudy Metzger wrote: Dear all, I am positive this has been asked a 1000 times before, but i cannot find it anywhere on the archive. Maybe a good search function there would help :) snip There is a search function for the archive, though I admit it's hard to see. In the thin blue bar at the top of the archive page is the tiny word Search which is a link to http://lists.mysql.com/search.php. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Error 1053
Mysqlians, The sql_thread on our slave slave server has been stopping with error 1053 ERROR: 1053 Server shutdown in progress 031203 16:05:01 Slave: error 'Server shutdown in progress' on query 'INSERT INTO INTEREST ( ID, PERSON_ID, COMMENT, DATE_CREATED, INTEREST_ID ) VALUES ( 'fffc5074-fe83-4f2e-9d4b-3d6a761c3f60', '49ea2258-3f60-4337-82be-cb15012636be', '', '2003-12-03 16:04:59', '32' )', error_code=1053 I have seen this half a dozen times and one time was in fact the master being restarted. However I have seen this several times without the master being restarted or any error in the master log at all. In addition this error has occurred on relatively low machine loads ( top/1.0 -2.0) and high (4.0-6.0). However the master server does between 1000 - 4000 questions/second. Is this a bug or something about the insert statement or something else. Thanks, Trevor
Re: Out-of-control log file
Seriously, can anyone help me out here? I've searched the list and I've searched around the Internet. I've found other people reporting a similar entry into their log files, but no one answered them either. I've read up on a few things, and I have a feeling that this error is related to some threading bug/error, but I'm not quite sure. Another thing to note, is it seems that the slave server is getting simultaneously issued a 'flush tables' for each entry shown below. Here is a part of a status command: Uptime: 1 day 21 hours 36 min 51 sec Threads: 1 Questions: 2739695 Slow queries: 0 Opens: 1 Flush tables: 1358804 Open tables: 1 Queries per second avg: 16.684 Additionally, I've set up one of my other servers as a slave, and I do not have the same problems as this slav. Same version of mysql (untared from the same file even), so I'm sure that the problem is not starting from the master. The only difference between the two slave servers is the OS. The slave that is 'broken' is on Mac OS X 10.3 Server and the one that works is Mac OS X 10.1 Server. On Dec 01, 2003, at 03:42, Chris Waskowich wrote: One of my server is a slave to another. The slave server's host.err file is getting filled with blocks like this: Status information: Current dir: /usr/local/mysql/var/ Running threads: 0 Stack size: 65536 Current locks: lock: 82268c: key_cache status: blocks used:15 not flushed: 0 w_requests: 0 writes: 0 r_requests: 0 reads: 0 handler status: read_key:0 read_next: 0 read_rnd 0 read_first: 0 write: 0 delete 0 update: 0 Table status: Opened tables: 0 Open tables:0 Open files: 4 Open streams: 0 Alarm status: Active alarms: 1 Max used alarms: 1 Next alarm time: 3600 Does anyone know what this stuff is and how I would stop it from being written? -- 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: New Microsoft Critical Patch
Okay, let me ask a slightly different question. I am getting several of these Microsoft emails every day and I think that they are coming via this list. Is anyone else having similar problems or am I following a red herring? John -Original Message- From: B. van Ouwerkerk [mailto:[EMAIL PROTECTED] Sent: Thursday, December 04, 2003 11:17 AM To: John Griffin Subject: RE: New Microsoft Critical Patch Yes. Stop using email :-) or use procmail to send them to a safe place.. I don't really understand why you send this to a MySQL list.. B. At 11:11 04-12-2003 -0500, John Griffin wrote: Can we do anything about these? I am getting several of them a day. -Original Message- From: Microsoft Corporation Technical Services [mailto:[EMAIL PROTECTED] Sent: Thursday, December 04, 2003 11:07 AM To: Consumer Subject: New Microsoft Critical Patch SNIP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Record Locking
hi all, Can someone show me how to lock a record in mySQL? As far as my understanding goes, only innoDB tables support record locking level. I am currently using mySQL version 3.23 and my tables are in myISAM. Thanks in advance for any assistance. Mike --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New Microsoft Critical Patch
Hi John, I've been getting those fake e-mails for a month, but I joined this mailing list only a week ago, so they definitely did not start coming from this list. Bob -Original Message- From: John Griffin [mailto:[EMAIL PROTECTED] Sent: Thursday, December 04, 2003 11:30 AM To: [EMAIL PROTECTED] Subject: RE: New Microsoft Critical Patch Okay, let me ask a slightly different question. I am getting several of these Microsoft emails every day and I think that they are coming via this list. Is anyone else having similar problems or am I following a red herring? John -Original Message- From: B. van Ouwerkerk [mailto:[EMAIL PROTECTED] Sent: Thursday, December 04, 2003 11:17 AM To: John Griffin Subject: RE: New Microsoft Critical Patch Yes. Stop using email :-) or use procmail to send them to a safe place.. I don't really understand why you send this to a MySQL list.. B. At 11:11 04-12-2003 -0500, John Griffin wrote: Can we do anything about these? I am getting several of them a day. -Original Message- From: Microsoft Corporation Technical Services [mailto:[EMAIL PROTECTED] Sent: Thursday, December 04, 2003 11:07 AM To: Consumer Subject: New Microsoft Critical Patch SNIP -- 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: New Microsoft Critical Patch {OT}
[snip] Okay, let me ask a slightly different question. I am getting several of these Microsoft emails every day and I think that they are coming via this list. Is anyone else having similar problems or am I following a red herring? [/snip] Red-herring. We get several dozens of these each day from different sources. It is general spam. Now, since you have joined a mailing list your e-mail address is archived with posts to the list. Spambots regularly troll archive lists fishing for addy's to place upon the list. So if you never had spam before joining the MySQL list you may be receiving some by virtue of having joined and having your e-mail addy farmed. This is one possibility -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Record Locking
I dont know if you can lock entries in a table but a table you can. More info: http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#Locking_Issues --- Mike Doanh Tran [EMAIL PROTECTED] wrote: hi all, Can someone show me how to lock a record in mySQL? As far as my understanding goes, only innoDB tables support record locking level. I am currently using mySQL version 3.23 and my tables are in myISAM. Thanks in advance for any assistance. Mike --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New Microsoft Critical Patch
I have checked almost every M$ critical patch and other messages with the same content and as far as I can tell none came via this or any other list. Easy enough to find out: look at the headers. If it says: Received: from lists.mysql.com (lists1.mysql.com [213.115.162.31]) List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] Delivered-To: mailing list [EMAIL PROTECTED] Received: (qmail 9683 invoked from network); 4 Dec 2003 18:30:13 - Received-SPF: unknown (domain of sender [EMAIL PROTECTED] does not designate mailers: NOERROR) X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Then you may been looking in the right direction. B. At 13:30 04-12-2003 -0500, John Griffin wrote: Okay, let me ask a slightly different question. I am getting several of these Microsoft emails every day and I think that they are coming via this list. Is anyone else having similar problems or am I following a red herring? John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Record Locking
I dont know if you can lock entries in a table but a table you can. More info: http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#Locking_Issues --- Mike Doanh Tran [EMAIL PROTECTED] wrote: hi all, Can someone show me how to lock a record in mySQL? As far as my understanding goes, only innoDB tables support record locking level. I am currently using mySQL version 3.23 and my tables are in myISAM. Thanks in advance for any assistance. Mike --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New Microsoft Critical Patch
I've been getting these for about a week, sometimes several times a day. This also was before I subscribed to this list. However: we have a mail system that automatically scans e-mails, especially those with attachments. Our system is flagging these up as containing the W32/[EMAIL PROTECTED] virus. So, PLEASE DO NOT ATTEMPT TO INSTALL OR RUN THE ATTACHED SOFTWARE !! Just bin it. Sorry I had to shout. (I use Macs, so it beats me why they keep sending them to me) Cheers, Alex Brown. MRC-T. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do I know what my MySQL server IP is ??
Hi, w do I know what my MySQL server IP is ?? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I know what my MySQL server IP is ??
On Thursday 04 December 2003 03:42 pm, Stéphane Bischoff wrote: Hi, w do I know what my MySQL server IP is ?? thanks How about ifconfig? James Hicks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stopping or aborting a long query
Hello all, How does one stop or abort a query? I was doing some experimenting with MySQL and created a table with 100,000 records. I then did a join like so: create table foo select foo.name, foo.val from foo, foo x, foo y, foo z ; This took a long time. So, I aborted with Ctrl-C (^C) which bumped me out of the mysql client. I then reconnected, issued 'show processlist;', and issued a 'kill ###;' for the process. Is this the way to abort a query? Or is there a more graceful method that aborts the query but keeps me in the mysql client? Have googled and searched the mailing list archives and faq, but nothing so far. Hints for search terms and pointers to a URL are greatly appreciated. Regards, - Robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stopping or aborting a long query
From: Robert Citek [mailto:[EMAIL PROTECTED] How does one stop or abort a query? I was doing some experimenting with MySQL and created a table with 100,000 records. I then did a join like so: create table foo select foo.name, foo.val from foo, foo x, foo y, foo z ; This took a long time. So, I aborted with Ctrl-C (^C) which bumped me out of the mysql client. I then reconnected, issued 'show processlist;', and issued a 'kill ###;' for the process. Is this the way to abort a query? Or is there a more graceful method that aborts the query but keeps me in the mysql client? Have googled and searched the mailing list archives and faq, but nothing so far. Hints for search terms and pointers to a URL are greatly appreciated. When I need to kill a query, I skip the Ctrl-C step and just open another session on the client and kill it from there. The previous window will say something about the MySQL server going away, but can generally reconnect on its own if you give it a line feed. However, I've found that when I kill a query, more often than not it just hangs in the processlist. In fact, we've had queries that hang and prevent other queries from getting to the table for so long that we end up restarting the server to free it up. Ugh. I haven't researched it much, but has anyone else seen this hanging query problem before? It was probably 3.23 at that point (we recently upgraded to 4, but haven't done much to warrant the situation since). The status for the process usually says 'killed' if that helps. -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing a cross-reference table: tips?
I am in in the processing of laying out a database for a consulting firm. My basic structure is like so: placement_candidate (candidates listing) primary key = CandidateID placement_primarytech (skills listing) primary key = PrimaryTechID I have created an intended cross-reference table, placement_candidatetech, which blends in these two tables, with a format like so: CandidateID PositionsID PrimaryTechID Notes 1 0 4 1 0 7 1 0 9 1 0 13 2 0 1 2 0 4 I showed this to my boss who said, I don't think we need that, we're going to have thousands of users, that table will be huge. Granted, 1 user may have 50 skills. Currently, as you see, only 2 users are in the database (candidate 1 with 4 skills and candidate 2 with 2 skills). Is there a more efficent way of handling this? Something else I should be doing? I would have thought this would be a faster way of searching. Is there another way of setting up this cross-reference table? I am no database guru. Eve Atley
Running Multiple Servers
Hi, Has anyone on this list ever been successful in running two MySQL servers at one? I'm assuming they have ;-) I am having some issues and am hoping for some help or pointers to help. When I try to run my second server I get this error message in the log: 031204 11:35:01 mysqld started 031204 11:35:01 Can't start server : Bind on unix socket: Permission denied 031204 11:35:01 Do you already have another mysqld server running on socket: /data/appenv/mysql/mys ql.sock ? 031204 11:35:01 Aborting 031204 11:35:01 /data/appenv/mysql/bin/mysqld: Shutdown Complete 031204 11:35:01 mysqld ended But I don't have another server running on that socket; it's the one I'm trying to start. I do have one running at /data/mysql/mysql.sock though. This is my command to start the second server: sudo /data/appenv/mysql/bin/safe_mysqld --defaults-file=/data/appenv/mysql/data/my.cnf --user=mysql This is the contents of my.cnf I'm using for the second server: [client] port=3307 socket=/data/appenv/mysql/mysql.sock [mysqld] port=3307 datadir=/data/appenv/mysql/data socket=/data/appenv/mysql/mysql.sock [mysql.server] user=mysql basedir=/data/appenv/mysql [safe_mysqld] err-log=/data/appenv/mysql/logs/mysqld.log pid-file=/var/mysqld.pid What do you think the issue is? Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-4.1.1 is released
Hi! The long-awaited MySQL/InnoDB-4.1.1 has been released. It is still labeled as alpha, because there are so many new features and bug fixes in it compared to 4.1.0. IMPORTANT NOTE: if you upgrade to InnoDB-4.1.1, you cannot downgrade any more! That is because earlier versions of InnoDB are not aware of multiple tablespaces. The biggest change for InnoDB in 4.1.1 is that you can now store each table and its indexes into its own file. This feature is called 'multiple tablespaces', because then each table is stored into its own tablespace. You can enable this feature by putting innodb_file_per_table in the [mysqld] section of my.cnf. Then InnoDB stores each table into its own file tablename.ibd in the database directory where the table belongs. This is like MyISAM does, but MyISAM divides the table to a data file tablename.MYD and the index file tablename.MYI. For InnoDB, both the data and the indexes are in the .ibd file. If you remove the line, then InnoDB creates tables in the ibdata files again. The old tables you had in the ibdata files before an upgrade to 4.1.1 remain there, they are not converted into .ibd files. InnoDB always needs the 'system tablespace', .ibd files are not enough. The system tablespace consists of the familiar ibdata files. InnoDB puts there its internal data dictionary and undo logs. You CANNOT FREELY MOVE .ibd files around, like you can MyISAM tables. This is because the table definition is stored in the InnoDB system tablespace, and also because InnoDB must preserve the consistency of transaction id's and log sequence numbers. You can move an .ibd file and the associated table from a database to another (within the same MySQL/InnoDB installation) with the familiar RENAME trick: RENAME TABLE olddatabasename.tablename TO newdatabasename.tablename; If you have a 'clean' backup of an .ibd file taken from the SAME MySQL/InnoDB installation, you can restore it to an InnoDB database with the commands: ALTER TABLE tablename DISCARD TABLESPACE; /* CAUTION: deletes the current .ibd file! */ put the backup .ibd file to the proper place ALTER TABLE tablename IMPORT TABLESPACE; 'Clean' in this context means: 1) There are no uncommitted modifications by transactions in the .ibd file. 2) There are no unmerged insert buffer entries to the .ibd file. 3) Purge has removed all delete-marked index records from the .ibd file. 4) mysqld has flushed all modified pages of the .ibd file from the buffer pool to the file. You can make such a clean backup .ibd file with the following method. 1) Stop all activity from the mysqld server and commit all transactions. 2) Wait that SHOW INNODB STATUS\G shows that there are no active transactions in the database, and the 'main thread' of InnoDB is 'Waiting for server activity'. Then you can take a copy of the .ibd file. Another (non-free) method to make such a clean .ibd file is to 1) Use InnoDB Hot Backup to backup the InnoDB installation. 2) Start a second mysqld server on the backup and let it clean up the .ibd files. It is in the TODO to allow moving clean .ibd files also to another MySQL/InnoDB installation. That requires resetting of trx id's and log sequence numbers in the .ibd file. The changelog for InnoDB: * Multiple tablespaces now available for InnoDB. You can store each InnoDB type table and its indexes into a separate .ibd file into a MySQL database directory, into the same directory where the .frm file is stored. * The MySQL query cache now works for InnoDB tables also if AUTOCOMMIT=0, or the statements are enclosed inside BEGIN ... COMMIT. * Reduced InnoDB memory consumption by a few MB, if one sets the buffer pool size 8 MB. * You can use raw disk partitions also in Windows. * This release contains all InnoDB bug fixes up to MySQL/InnoDB-4.0.16. * Some non-critical known bugs not yet fixed in this release. The fixes will probably come in 4.1.2. * A new my.cnf option innodb_locks_unsafe_for_binlog did not yet make it to 4.1.1. It will remove next-key locking in most cases, at the risk of breaking replication and binlog recovery in some cases. It is useful for eliminating transaction deadlocks. * A new InnoDB Hot Backup version 2.0 which supports multiple tablespaces in 4.1.1 is already ready, but the binaries not yet built. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
automate MSAccess into MySQL
Is there a way to automate an export of a single table from a MS Access DB into a Temp MySQL DB? I would like to make a front end that the client can select the correct MS Access DB and then the correct table and once those are selected the table will be exported into the MySQL DB for my C++ front end to utilize. Does anyone know how to do this? Thanks Warren -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: automate MSAccess into MySQL
Warren wrote: Is there a way to automate an export of a single table from a MS Access DB into a Temp MySQL DB? I would like to make a front end that the client can select the correct MS Access DB and then the correct table and once those are selected the table will be exported into the MySQL DB for my C++ front end to utilize. Does anyone know how to do this? Thanks Warren There are a number of apps and plugins to Access and scripts that do this. Have a look on the MySQL website, under 'contributed' or something like that. Be aware, however, that some of them make 'interesting' decisions about what column types to use. Carefully review their source before using them yourself; even more so if your customers will be the ones using the app. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Large data set load and access
Folks, Just completed an interesting task utilizing mySQL 4.0.16. The database I'm creating is some summary information from the GenBank info from the NCBI. I must say that I am quite impressed by the performance that I am seeing. The data set is pretty substantial, consisting of almost 30M records. Yet it was loaded from the text file in less than 6 minutes. The text file itself is about 1.6GB in size. An aggregation of the data also took just under a minute as shown in the information I've included with this message. I was also impressed by the time to select a particular record without the use of an index on the column being selected upon. I'm sure that would change once I create an index on this column. For information, this test was done on a 1300 MHz RH Linux 7.3 system with 896MB of memory, and WDC ATA drives. Not the top of the line machine, but pretty respectable. Brad Eacker ([EMAIL PROTECTED]) Particulars: mysql create table gb_locus ( - gbl_id int primary key, - gbl_fileID int, - gbl_locus varchar(20), - gbl_sizeint, - gbl_datedate, - gbl_phylum char(3), - gbl_foffset int - ); Query OK, 0 rows affected (0.00 sec) mysql load data infile '/hda3/beacker/gene/genbank/gbl_locus.txt' - into table gb_locus fields terminated by ','; Query OK, 29830869 rows affected (5 min 44.68 sec) Records: 29830869 Deleted: 0 Skipped: 0 Warnings: 0 Input file information: [EMAIL PROTECTED] genbank]$ ls -l gbl_locus.txt -rw-rw-r--1 beacker beacker 1583781135 Dec 4 13:41 gbl_locus.txt Aggregation select: mysql select gbl_phylum, count(*) from gb_locus group by gbl_phylum; ++--+ | gbl_phylum | count(*) | ++--+ | BCT| 210778 | | CON|11472 | | EST| 18836635 | | GSS| 7585521 | | HTC| 148411 | | HTG|68390 | | INV| 186924 | | MAM|52858 | | PAT| 1345394 | | PHG| 2396 | | PLN| 368927 | | PRI| 302997 | | ROD| 115600 | | STS| 257403 | | SYN|10988 | | UNA| 1093 | | VRL| 203738 | | VRT| 121344 | ++--+ 18 rows in set (59.74 sec) [EMAIL PROTECTED] gene]# ls -l gb_locus* -rw-rw1 mysqlmysql8766 Dec 4 13:57 gb_locus.frm -rw-rw1 mysqlmysql1075530216 Dec 4 14:04 gb_locus.MYD -rw-rw1 mysqlmysql244406272 Dec 4 14:04 gb_locus.MYI Single record selections: mysql select * from gb_locus where gbl_id = 1400; +--++---+--+++-+ | gbl_id | gbl_fileID | gbl_locus | gbl_size | gbl_date | gbl_phylum | gbl_foffset | +--++---+--+++-+ | 1400 | 310212 | AL556818 | 1027 | 2003-05-31 | EST|41517291 | +--++---+--+++-+ 1 row in set (0.04 sec) mysql select * from gb_locus where gbl_locus = 'AL556818'; +--++---+--+++-+ | gbl_id | gbl_fileID | gbl_locus | gbl_size | gbl_date | gbl_phylum | gbl_foffset | +--++---+--+++-+ | 1400 | 310212 | AL556818 | 1027 | 2003-05-31 | EST|41517291 | +--++---+--+++-+ 1 row in set (45.69 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS
Hi,
Yes, you would have similar results with any query that uses
SQL_CALC_FOUND_ROWS. That's because MySQL has to see how many rows would
be found without the LIMIT. So in your case, it can't just abort the
query after it finds 10 rows. All rows that match the WHERE need to be
found.
You might want to try your fulltext search IN BOOLEAN MODE to see if
that runs any faster. :-)
Hope that helps.
Matt
- Original Message -
From: [EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 9:13 AM
Subject: fulltext search speed issue with SQL_CALC_FOUND_ROWS
I have some_table with 100,000 rows and with an
average of 500 words in some_column of each row. When
i do a fulltext search on this table using a query
such as the following, all of my results are under 0.1
seconds:
SELECT something
FROM some_table
WHERE MATCH (some_column) AGAINST ('some_search_term')
LIMIT 0,10
However, when i add the SQL_CALC_FOUND_ROWS keyword
like in the following query, some queries take longer
than 1 minute:
SELECT SQL_CALC_FOUND_ROWS something
FROM some_table
WHERE MATCH (some_column) AGAINST ('some_search_term')
LIMIT 0,10
How can there be a huge difference in speed if both
queries always return the exact same results?
Thanks,
TK
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Off Topic: MySQL Icons
Hi all. I'm prettying up my Gnome desktop, and I'm after a MySQL icon for my MySQLCC launcher. Anyone know of some icons? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Off Topic: MySQL Icons
Try google images... It's where I go every time I need to find an image that I don't have on hand. Drew On Thu, 2003-12-04 at 18:01, Daniel Kasak wrote: Hi all. I'm prettying up my Gnome desktop, and I'm after a MySQL icon for my MySQLCC launcher. Anyone know of some icons? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large data set load and access
[EMAIL PROTECTED] wrote: mysql create table gb_locus ( - gbl_id int primary key, - gbl_fileID int, - gbl_locus varchar(20), - gbl_sizeint, - gbl_datedate, - gbl_phylum char(3), - gbl_foffset int - ); At a recent MySql class, I learned that using all fixed length fields instead of variable length fields can improve speed. This is because MySql can skip thru records faster if the records are all a fixed length. So, if you were willing to give up a little storage space, make the gbl_locus field a Char(20) instead of a varchar(20) and see if it speeds things up. I found noticable speed increase in my selects doing this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: automate MSAccess into MySQL
On Thu, 04 Dec 2003 15:00 , Warren [EMAIL PROTECTED] sent: Is there a way to automate an export of a single table from a MS Access DB into a Temp MySQL DB? I would like to make a front end that the client can select the correct MS Access DB and then the correct table and once those are selected the table will be exported into the MySQL DB for my C++ front end to utilize. Does anyone know how to do this? Thanks Warren I have read posts about implimentations of this. You can easily grab the table structure using VBA and then connect to MYSQL to create and import row by row (or even out to a CSV with a text file for the table schema). PHP could do the same, but I do not know if you can get the table structure as easiliy. I guess the main question is, do you want to keep field types, or just quickly push the data over. --- Chris McKeever If you want to reply directly to me, please use cgmckeever--at--prupref---dot---com http://www.prupref.com Prudential Preferred Properties www.prupref.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL vs. MaxDB
Hola, I was wondering if anyone could point me to any articles or URLs that could give me an idea of the differences between MaxDB and MySQL, specifically if one were to use MaxDB instead of MySQL, what disadvantages would come with the advantages (which to me are basically some of the features MySQL doesnt have yet.) ?? Off the top of my head, I assume MySQL would be faster than MaxDB for web- based applications/sites, but thats just an assumption I'd like to find some facts. Thanks, -- Keith Bussey Mana Internet Solutions, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tree-like structure: make it simply
Hello mysql,
I have some table:
/* BEGIN DUMP */
CREATE TABLE `sp_tovar_vid` (
`id` int(11) NOT NULL auto_increment,
`id_tovar_vid` int(11) NOT NULL default '0',
`name` varchar(100) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
UNIQUE KEY `id` (`id`)
);
INSERT INTO `sp_tovar_vid` VALUES (0, 0, 'root', 'root category');
INSERT INTO `sp_tovar_vid` VALUES (7, 0, 'cat.1', '');
INSERT INTO `sp_tovar_vid` VALUES (8, 0, 'cat.2', '');
INSERT INTO `sp_tovar_vid` VALUES (9, 0, 'cat.3', '');
INSERT INTO `sp_tovar_vid` VALUES (10, 0, 'cat.4', '');
INSERT INTO `sp_tovar_vid` VALUES (11, 0, 'cat.5', '');
INSERT INTO `sp_tovar_vid` VALUES (12, 7, 'subcat 1', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (13, 7, 'subcat 2', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (14, 7, 'subcat 3', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (15, 9, 'subcat 1', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (16, 9, 'subcat 2', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (17, 9, 'subcat 3', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (18, 13, 'subsubcat 1', '(to subcat.2
to cat.1)');
/* END DUMP /
_QUESTION 1_: in what way should i run query to restore table with
`id`' like in dump? (`id` is auto_increment)
_QUESTION 2_: how to display table with columns=
('parent_category.name' ,'category.name')?
i tried this:
/***/
SELECT t1.`name`,t2.`name` FROM `sp_tovar_vid` AS t1, `sp_tovar_vid`
AS t2 WHERE t1.`id`=t2.`id_tovar_vid` ORDER BY t1.`name`,t2.`name`;
/***/
How to make it more simply?
_QUESTION 3_: how to display string:
subsubcat 1 - subcat.2 - cat.1
if we have only `id`=18 ?
--
Best regards,
Alex mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tree-like structure: make it simply
I think I can help with questions 1 and 3... see below,
- Original Message -
From: Alex E.Wintermann [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 11:44 PM
Subject: Tree-like structure: make it simply
Hello mysql,
I have some table:
/* BEGIN DUMP */
CREATE TABLE `sp_tovar_vid` (
`id` int(11) NOT NULL auto_increment,
`id_tovar_vid` int(11) NOT NULL default '0',
`name` varchar(100) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
UNIQUE KEY `id` (`id`)
);
INSERT INTO `sp_tovar_vid` VALUES (0, 0, 'root', 'root category');
INSERT INTO `sp_tovar_vid` VALUES (7, 0, 'cat.1', '');
INSERT INTO `sp_tovar_vid` VALUES (8, 0, 'cat.2', '');
INSERT INTO `sp_tovar_vid` VALUES (9, 0, 'cat.3', '');
INSERT INTO `sp_tovar_vid` VALUES (10, 0, 'cat.4', '');
INSERT INTO `sp_tovar_vid` VALUES (11, 0, 'cat.5', '');
INSERT INTO `sp_tovar_vid` VALUES (12, 7, 'subcat 1', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (13, 7, 'subcat 2', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (14, 7, 'subcat 3', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (15, 9, 'subcat 1', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (16, 9, 'subcat 2', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (17, 9, 'subcat 3', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (18, 13, 'subsubcat 1', '(to subcat.2
to cat.1)');
/* END DUMP /
_QUESTION 1_: in what way should i run query to restore table with
`id`' like in dump? (`id` is auto_increment)
either omit the `id` field when re-loading the data, e.g.
INSERT INTO `sp_tovar_vid` (`id_tovar_vid`,`name`,`description`) VALUES (0,
'root', 'root category');
or, replace the `id` values with an empty string when re-loading the table
data, e.g.
INSERT INTO `sp_tovar_vid` VALUES ('', 0, 'cat.1', '');
both techniques prompt mysql to reassign the `id` values.
note that I do not think it is wise/possible to run a query to restore (or
clean up) the auto_increment values without re-loading the table data.
_QUESTION 2_: how to display table with columns=
('parent_category.name' ,'category.name')?
i tried this:
/***/
SELECT t1.`name`,t2.`name` FROM `sp_tovar_vid` AS t1, `sp_tovar_vid`
AS t2 WHERE t1.`id`=t2.`id_tovar_vid` ORDER BY t1.`name`,t2.`name`;
/***/
How to make it more simply?
_QUESTION 3_: how to display string:
subsubcat 1 - subcat.2 - cat.1
if we have only `id`=18 ?
use SELECT IF(id=18, true_expression, false expression) FROM table...
I'm not clear what you mean by subsubcat 1 - subcat.2 - cat.1... minus, or
some sort of string concatenation?
true expression might be field1 - field2, or CONCAT(field1, field2, ...)
--
Best regards,
Alex mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL vs. MaxDB
from http://www.mysql.com/press/release_2003_35.html The MySQL database is a high performance relational database management system that is noted for its speed, stability and ease of use, while MaxDB is certified for SAP applications and includes features such as stored procedures, triggers and views, for the most demanding enterprise use. I know, it's a lazy response. I haven't noticed any performance comparison artiles. - Original Message - From: Keith Bussey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 04, 2003 11:25 PM Subject: MySQL vs. MaxDB Hola, I was wondering if anyone could point me to any articles or URLs that could give me an idea of the differences between MaxDB and MySQL, specifically if one were to use MaxDB instead of MySQL, what disadvantages would come with the advantages (which to me are basically some of the features MySQL doesnt have yet.) ?? Off the top of my head, I assume MySQL would be faster than MaxDB for web- based applications/sites, but thats just an assumption I'd like to find some facts. Thanks, -- Keith Bussey Mana Internet Solutions, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- 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: automate MSAccess into MySQL
Visit my web site and look at data converters Michael Johnson Director BPEnet Humphrey Consulting Limited 13 Austin Friars London EC2N 2JX Tel +44(0)870 922 0247 Fax +44(0)1323 419554 email [EMAIL PROTECTED] URL www.bpenet.net Also in Dublin Luxembourg -Original Message- From: McKeever Chris [mailto:[EMAIL PROTECTED] Sent: 04 December 2003 23:14 To: MySQL Lists; Warren Subject: Re: automate MSAccess into MySQL On Thu, 04 Dec 2003 15:00 , Warren [EMAIL PROTECTED] sent: Is there a way to automate an export of a single table from a MS Access DB into a Temp MySQL DB? I would like to make a front end that the client can select the correct MS Access DB and then the correct table and once those are selected the table will be exported into the MySQL DB for my C++ front end to utilize. Does anyone know how to do this? Thanks Warren I have read posts about implimentations of this. You can easily grab the table structure using VBA and then connect to MYSQL to create and import row by row (or even out to a CSV with a text file for the table schema). PHP could do the same, but I do not know if you can get the table structure as easiliy. I guess the main question is, do you want to keep field types, or just quickly push the data over. --- Chris McKeever If you want to reply directly to me, please use cgmckeever--at--prupref---dot---com http://www.prupref.com Prudential Preferred Properties www.prupref.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Named Pipe crashes on MySQL (4.1.1 alpha) WinXP
Hi Ed, Yeah, I just installed today's 4.1.1-alpha-nt on Win2k SP3 and get the same thing. :-( Sucks, 'cause named pipes are a lot faster for me than TCP/IP. And I was really looking forward to this release. It's just not the same with TCP/IP. :-( Matt - Original Message - Subject: Named Pipe crashes on MySQL (4.1.1 alpha) WinXP Named Pipe crashes on MySQL (4.1.1 alpha) WinXPI have been unable to get named pipes to work on mysqld-nt 4.1.1 Alpha(including today's official release). As soon as I attempt a connect the server crashes. I can't create a debug trace because named pipes aren't enabled during a --debug. I have enable-named-pipe turned on and my client is connecting with hostname of . notation. The last several weeks of bitkeeper source distributions haven't worked for me either. The last time I can confirm it worked for me was a bitkeeper source build I did on Sep 2 2003. Does anyone have an Alpha 4.1.1 working with named pipes on WinXP (DELL 2.6ghz, P4, 640mb ram, ServPack 1)? Thanks, Ed Mierzwa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error when inserting long string from cgi
Hi there, I am attempting to insert a string into a longtext field (from a CGI program using the C API) and I keep getting an error message. The string is not that long actually, 78,069 characters. (Originally this was mediumtext, but I changed it to longtext as I debugged this problem.) The command is: INSERT INTO my_table_name (integer_field, longtext_field) VALUES (24144, x24144(5000)14799:1.00~23534:1.00~...24114:1.00~) The error message is: MySQL error inserting data in save_neighbors_of_favorites: You have an error in your SQL syntax near 'x23973(5000)79397:1.00~92558:1.00~216160:1.00~263178:1.00~27521' at line 1. The error occurs when the string I want to insert has about 2000 or more characters. The error goes away when the string is shorter than that. So this seems to be a problem with the length of the string. I keep thinking that the syntax error is caused because the INSERT command is being truncated because it's too long. I have tried resetting the net_buffer_size and max_allowed_packet variables. Currently, net_buffer_size is set to 1,407,552 and max_allowed_packet is 3,144,704. (Actually, I tried to set both to 3M doing /usr/bin/safe_mysqld -O max_allowed_packet=3145728 -O net_buffer_length=3145728 but end up being the sizes I listed above. Maybe I am doing something wrong here?) I am running MySQL 3.23.58 on Red Hat 8.0 with 128M of memory. I don't think any of the non-integer characters need to be escaped. I have tried escaping the string anyway just in case using mysql_escape_string() (I know I'm supposed to use mysql_real_eascape_string(), but for some reason it causes my program to seg fault) and I get the same error. I would very much appreciate any help or suggestions. Thanks! Rachel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS
Hello,
Thanks for the response.
There is one thing that is not clear however.
Regardless of whether or not I perform the fulltext
search with or without the SQL_CALC_FOUND_ROWS
keyword, the results that I get are exactly the same.
Also, the notion of stopping after the limit is
reached cannot apply in the fulltext search or
otherwise we would only get the first 10 matches but
not the first 10 most relevant matches. This leads
me to believe that the fulltext search must be looking
at all the rows in both cases since it otherwise would
not find the same first 10 most relevant records.
Hence the question why there should be a difference in
time.
Just to check, I also performed a search with a limit
that was greater than the number of rows in my table
and the first 10 records were again the same.
Here are the results:
rows: about 100,000
colums: average of 500 words
--- RUN 1 ---
test run with SQL_CALC_FOUND_ROWS (pc was rebooted)
---
SELECT SQL_CALC_FOUND_ROWS id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
LIMIT 0,10
++
| id |
++
| 53957 |
| 21607 |
| 106369 |
| 1916 |
| 50071 |
| 39942 |
| 99764 |
| 99467 |
| 51820 |
| 19956 |
++
10 rows in set (94.16) sec
EXPLAIN SELECT SQL_CALC_FOUND_ROWS id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
LIMIT 0,10
+---+--+---+--+
| table | type | possible_keys | key |
+---+--+---+--+
| main | fulltext | abstract | abstract |
+---+--+---+--+
-++--+-+
key_len | ref| rows | Extra |
-++--+-+
0 ||1 | Using where |
-++--+-+
SELECT SQL_CALC_FOUND_ROWS id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
LIMIT 0,10;
select FOUND_ROWS()
17501 rows
--- RUN 2 ---
test run without SQL_CALC_FOUND_ROWS (pc was rebooted)
---
SELECT id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
LIMIT 0,10
++
| id |
++
| 53957 |
| 21607 |
| 106369 |
| 1916 |
| 50071 |
| 39942 |
| 99764 |
| 99467 |
| 51820 |
| 19956 |
++
10 rows in set (0.11) sec
EXPLAIN SELECT id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
LIMIT 0,10
+---+--+---+--+
| table | type | possible_keys | key |
+---+--+---+--+
| main | fulltext | abstract | abstract |
+---+--+---+--+
-++--+-+
key_len | ref| rows | Extra |
-++--+-+
0 ||1 | Using where |
-++--+-+
--- RUN 3 ---
test run without SQL_CALC_FOUND_ROWS and with high
limit (pc was rebooted)
---
SELECT id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
limit 10
++
| ppt_id |
++
| 53957 |
| 21607 |
| 106369 |
| 1916 |
| 50071 |
| 39942 |
| 99764 |
| 99467 |
| 51820 |
| 19956 |
...
17501 rows in set (94.22) sec
EXPLAIN SELECT id
FROM main
WHERE MATCH (abstract) AGAINST ('access')
limit 10
+---+--+---+--+
| table | type | possible_keys | key |
+---+--+---+--+
| main | fulltext | abstract | abstract |
+---+--+---+--+
-++--+-+
key_len | ref| rows | Extra |
-++--+-+
0 ||1 | Using where |
-++--+-+
So to summarize the question:
To get the most relavent first 10 results, fulltext
seach must be going through all records with or
without the SQL_CALC_FOUND_ROWS keyword, so why would
there be such a huge difference in time.
Thanks,
TK
--- Matt W [EMAIL PROTECTED] wrote:
Hi,
Yes, you would have similar results with any query
that uses
SQL_CALC_FOUND_ROWS. That's because MySQL has to see
how many rows would
be found without the LIMIT. So in your case, it
can't just abort the
query after it finds 10 rows. All rows that match
the WHERE need to be
found.
You might want to try your fulltext search IN
BOOLEAN MODE to see if
that runs any faster. :-)
Hope that helps.
Matt
- Original Message -
From: [EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 9:13 AM
Subject: fulltext search speed issue with
SQL_CALC_FOUND_ROWS
I have some_table with 100,000 rows and with an
average of 500 words in some_column of each row.
When
i do a fulltext search on this table using a query
such as the following, all of my results are under
0.1
seconds:
SELECT something
FROM some_table
WHERE MATCH (some_column) AGAINST
('some_search_term')
LIMIT 0,10
However, when i add the SQL_CALC_FOUND_ROWS
keyword
like in the following query, some queries take
Re: How to query
I'm stumped... Now, I need to generate reports for a specific client. In the end, for a given month, I have to tell the client 1. Which invoices were for the purchase of blue widgets, only. 2. Which invoices were for the purchase of blue widgets and yellow widgets, together. 3. Which invoices for the purchase of green doodads, only. 4. Which for green doodads along with yellow widgets, together. etc. Out of 1,000 product codes, I'm interested in 10 or so. It's easy enough to write any one of the queries but I can't figure out how to group/order in such a way as to do it all in one query and to order by the 10 or so products I'm interested in. MySQL is 4.0.12. I guess I should be a little more specific. This is actually invoicing for services rendered. There are 1000+ codes for the various services, plus one additional, PREM, for premium processing. What the client needs to know is how many L1 visas were filed, how many with premium processing, how many L1 extensions, how many with premium processing, etc. The very nice and very smart lady who is running this report is using Crystal Reports and neither one of us knows what it's doing under the hood. But, in response to the trouble she is having, she wants to de-normalize the bejeebers out of MY tables by adding 10, or so, columns to the invoice table for each of the codes she's interested in. As you might guess, I'm dragging my heels, kicking and screaming, but that leaves it up to me to solve the problem. Any help would be appreciated. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large data set load and access
So, if you were willing to give up a little storage space, make the gbl_locus field a Char(20) instead of a varchar(20) and see if it speeds things up. I found noticable speed increase in my selects doing this. Thanks for the heads up on this. Unfortunately the only varchar is the gbl_locus field, so I'm not sure how much this would by me for the space. Thanks again, Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lost connection to MySQL server during query - pls help
Hi Guys, I have a problem with Error 2013 - Lost connection to MySQL server during query I'm using mysql 3.23.41 under Linux Mandrake, and mysql 4.0.15 under Win XP Pro. They turn out to have the same error. My problem is: I have a Java program which is actually a thread to send emails, so it keeps running all the time. When it's time to send emails, it will access MySQL database. Based on my wait_timeout in mysql, I think the connection closes after 8 hrs. If it's the time to send emails, and mysql has already closed the connection, the first connection attemp will throw an error Error 2013 - Lost connection to MySQL server during query * Is there a way that I can avoid this error? Or to make the connection keeps open all the the time? Any help / suggestions will be very much appreciated :) Thank you in advance. Rgds, Vanessa
Solved - median (was Re: mean/median/mode)
On Wednesday, December 3, 2003, at 06:27 PM, Robert Citek wrote:
How can I calculate the mean/median/mode from a set of data using SQL?
After a bit of googling, I found this link:
http://mysql.progen.com.tr/doc/en/Group_by_functions.html
and a few answers in the comments towards the bottom.
Below I've included a sample table and the solution I used to calculate
the median.
Regards,
- Robert
-
DROP TABLE IF EXISTS data;
CREATE TABLE data (
name char(1) default NULL,
val int(5) default NULL
) TYPE=MyISAM;
INSERT INTO data VALUES
('a',2), ('a',2), ('a',2), ('a',2), ('a',20),
('b',4), ('b',4), ('b',4), ('b',4), ('b',40);
CREATE TEMPORARY TABLE medians SELECT x.name, x.val
FROM data x, data y
WHERE x.name=y.name
GROUP BY x.name, x.val
HAVING ((COUNT(*)-(SUM(SIGN(1-SIGN(y.val-x.val) = floor((COUNT(*)
+1)/2)) and
((COUNT(*)-(SUM(SIGN(1+SIGN(y.val-x.val) = floor((COUNT(*)
+1)/2));
SELECT name, AVG(val) AS median FROM medians group by name;
Output
+--++
| name | median |
+--++
| a| 2. |
| b| 4. |
+--++
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
