Re: Interesting !?!
On Wed 2003-04-02 at 13:32:22 +0300, [EMAIL PROTECTED] wrote: Hello mysql, We make updade of database from 3.23.49 to 4.0.12 Before update we can see host of connections /see Example1/. After update every connections looks like they made from local host /see Example2/, but they did not. Any explanation of effect?!? A bug. It is listed as fixed in the change history for the next (not yet released) version 4.0.13 in the online manual. HTH, Benjamin. -- [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why don't ISPs use v4
Hi. On Mon 2003-03-31 at 21:20:25 +0100, [EMAIL PROTECTED] wrote: Sorry everybody. I assumed that as it had been out for a long time (2 years?) it was stable. It's been 1 1/2 years (Oct 2001). But that was an alpha release. The open development model of MySQL screws numbers a bit. The first beta release was Aug 2002. So in real, it was about 7 month since feature freeze. The release dates are part of http://www.mysql.com/doc/en/News.html For more info on the release designation (alpha, beta, gamme, production), have a look at http://www.mysql.com/doc/en/Which_version.html (especially the second half) HTH, Benjamin. -- [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Embedded MySQL?
Hi. On Thu 2003-03-20 at 08:25:08 +0200, [EMAIL PROTECTED] wrote: Hi, I have a question I hope you can help me with. I try to develop a database in Borlands Delphi or with C++ and Visual Studio 6 (Windows 2000). Probably I choose Delphi. Now I want to use MySQL as a platform for my program. According to http://www.mysql.com/doc/en/libmysqld_overview.html I can run a standalone server in my program. Now I wonder how this works in practise? Ideally I want to call an API in a dll-file to create and maintain the database. http://www.mysql.com/doc/en/libmysqld.html should answer most of your concern. Basically it boils down to an additional call to mysql_server_init() and mysql_server_end() and compiling with libmysqld instead of libmysqlclient. The rest is the same with the client library. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Index on a (homemade) SET?
Hi. On Thu 2003-03-20 at 15:52:25 +0100, [EMAIL PROTECTED] wrote: I use a lot of SET-alike fields in my tables. The fields are used to store properties and such; every bit stands for a specific value. The fields are queries like 'where property_field 14' or 'where property_field 1025' if you look for more than one property. Just your average SET behavoir and very convenient to use. The only problem is these queries are slow. You need to do a tablescan to find the matching records. Making a seperate table where you store an entry per property is another option but even slower (makes a big difference after even a few tens of thousend of rows). Any hint / tips / ideas how to index a set-like field? Maybe you should start by explaining why you don't use SETs? Whatever the reason is, I strongly suspect that it will influence the answer. All what you described works fine with SETs and there is a chance that MySQL will optimize accesses to them better. Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.0.12 startup problem InnoDB related
On Thu 2003-03-20 at 15:59:29 +0100, [EMAIL PROTECTED] wrote: Hi, I was upgrading from 4.0.10 to 4.0.12 when a strange thing happened. I compiled from source on SuSE-8.0 (gcc version 2.95.3, kernel 2.4.18-4GB) and installed and everything was fine. However I noticed that I had debugging compiled in. I went back, took out --with-debug from my configure options and recompiled. The resulting mysqld did no longer start up but quit with the following message in error.log: 030320 09:02:00 mysqld started InnoDB: Error: trx_t size is 416 in ha_innodb.cc but 456 in srv0start.c InnoDB: Check that pthread_mutex_t is defined in the same way in these InnoDB: compilation modules. Cannot continue. 030320 9:02:00 Can't init databases 030320 9:02:00 Aborting How strange; I deleted all InnoDB files in the data directory. Same result. Then I recompiled again adding the option --with-debug and it started ok creating all necessary files. What is wrong here? Such a config change requires to do a full recompile (make distclean or whatever), which you apparently did not. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible Bug: Dropping Trailing White Space
On Thu 2003-03-20 at 14:01:52 -0500, [EMAIL PROTECTED] wrote: I have a table with a column defined as the following. hash CHAR(16) BINARY NOT NULL Most data inserts fine. However, if data has trailing white space (ASCII character 32), it seems to be getting truncated by MySQL during the insert, such that subsequent queries to find the values fail. Full example below. Any Ideas? That is the documented behaviour (http://www.mysql.com/doc/en/CHAR.html) and is an (implementation dependend) feature of SQL. If you don't want it, you had to use VARCHAR instead. But there is a known bug with MySQL (see above and http://www.mysql.com/doc/en/Open_bugs.html). As the bug description implies, use a TEXT type like TINYTEXT instead. HTH, Benjamin. PS: Btw, the BINARY keyword only influences sort behaviour, nothing else. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: converting an existing column to auto increment
Hello. On Mon 2003-03-17 at 18:08:08 -0600, [EMAIL PROTECTED] wrote: I have an table with a column numberINT NOT NULL PRIMARY KEY This table contains many records and there are gaps in the number sequence. I would like to modify this column to use the AUTO INCREMENT feature but I need to preserve the present numbering sequence (which is monotonically increasing) including the holes. It is not clear from the manual or my MySQL book exactly how to do this. Is this possible? if so what is the correct ALTER TABLE command? Yes. First, make a backup of your tables. Although I don't expect any problems: Better safe than sorry. AUTO_INCREMENT columns won't reuse gaps. If you add a new value, it will be at least MAX(value)+1. Could be higher, if you deleted some rows in-between. The command is ALTER TABLE your_table MODIFY number INT NOT NULL AUTO_INCREMENT PRIMARY KEY Done. Note that if your column wouldn't be NOT NULL already and indeed contain some NULL values, I would expect them to be handled like a NULL for the AUTO_INCREMENT in a normal INSERT, i.e. it would get MAX(value)+1 (and so on for all other rows where number=NULL). As I said, this is not an issue in your current case. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: When is MySQL 4.1 going to have its binary download available ?
Hi. On Wed 2003-03-12 at 14:39:26 +0200, [EMAIL PROTECTED] wrote: Hi, I do not know how to compile, but I sure want to try the new features of 4.1. There is no date yet for the first binary release of 4.1. They will publish such an alpha release when they consider it mature enough for general public testing. So, in a way, just wait with testing until they invite by publishing it. HTH, Benjamin. PS: In case you are not aware of it: There is an own section how to get and compile the 4.1 source version: http://www.mysql.com/doc/en/Installing_source_tree.html -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: automatically incrementing an int value
On Wed 2003-03-12 at 11:16:09 -0600, [EMAIL PROTECTED] wrote: At 11:41 -0500 3/12/03, Douglas B. Jones wrote: Hi, I understood replace to only increment n when it matches the name value. In that case, you want a primary key on (name,n) with n being auto_increment. There are 122,111 statements, but when you add up the numbers in the n column, they exceed 122,111. They should I don't know what you're trying to say here. If you *add up* the numbers in the column, of course they will exceed the number of records. I think he expects one row for each name, with the n being the (original) number of rows which had that name. If I am correct, another way to reach the same goal is to simply insert all rows as (without unique key on name, only a normal key) and then do a SELECT name, COUNT(*) FROM virus GROUP BY name; This has the advantage that you, Douglas, can keep timestamps and so on which enables better analysis afterwards. If you are not interested in other data, but only the one-time analysis you presented, why (mis-)use a database at all? Simple do something like $ sort file_with_names | uniq -c in the shell (I presume you have shell access because you used grep before). HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Searching on indexed char field...
On Mon 2003-03-10 at 15:06:05 -0500, [EMAIL PROTECTED] wrote: If you're indexing all 50 characters, index fewer of them. Not that I think you're wrong, but help me understand, please: It seems to me that this would cause the index creation to go faster, but the execution of the SELECT query to, if anything, go slower...? I'm probably wrong, though, but just wondered why. :) A simple example of why it can be faster (can, not is) for selects is obvious, if you have a look at caches for a moment: If you have 10.000.000 rows and an index on a char[50], you have an index of about (50+4)*10.000.000 ~= 540MB. If you limit the index to 10 chars, you'll need 140MB. If you have 256MB, the one may fit into memory, the other may not and therefore requires additional disk reads. Another example would be regarding disk reads: MySQL reads always a whole block (1KB) from the index. With 14 bytes (plus some adminstrative overhead) far more index entries fit into one block than with 54 bytes. I.e. one disk read has a higher chance to fetch the index entry you need next and make the next disk read unnecessary. The situation is actually far more complex - I only wanted to show an example why the seemingly slower configuration can be faster: due to limited system resources, configurations which use available resources wisely can be faster. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problems with GRANT, user, databases
Hi!
On Mon 2003-03-10 at 16:44:40 -0500, [EMAIL PROTECTED] wrote:
[...]
- Given a system user 'junk' I would like to create a master user
'junk' that can have all permissions (including grant?) on all 'junk_*'
databases so that he can create and manage his own databases
- Given a master user 'junk' I would like for that user to be able to
create other users 'junkNN' or or 'junk-*' or whatever so that my good
friend junk is not always acting as root.
I have tried
mysql grant all privileges on junk_* to [EMAIL PROTECTED] identified by 'junk';
ERROR 1064: You have an error in your SQL syntax near '* to [EMAIL PROTECTED]
identified by 'junk'' at line 1
mysql grant all privileges on junk_.* to [EMAIL PROTECTED] identified by 'junk';
Query OK, 0 rows affected (0.24 sec)
and so I guess the way to specify database matching is with pure regexp
notation of '.*' for 'any character any number of times',
No, it does not use REGEXPs at all. The second statement was
interpreted as 'databasename_'.'*', i.e. '.' is the seperator between
database name and table name. Here, '*' is not really a joker
(although it has some of its effects), but simply a placeholder
specific to the GRANT command. That means you have granted user junk
access to all tables within the database 'junk_' (where '_' is a joker
meaning any single char, like '.' in REGEXP).
I am not sure from my mind whether SQL jokers ('%') work within GRANT,
but if they do, the first statement should have looked something like
that:
GRANT ALL PRIVILEGES ON 'junk\\_%'.* TO [EMAIL PROTECTED] IDENTIFIED BY 'junk';
(maybe more or less backslashes needed)
mysql select * from mysql.db where user = 'junk' ;
| localhost | junk_ | junk | Y | Y | Y | Y
| Y | Y | N | Y | Y | Y |
mysql select * from mysql.user where user = 'junk' ;
| localhost | junk | 372b3ff6545565e4 | N | N | N |
N | N | N | N | N | N|
N | N | N | N | N |
[...]
but I can't create a database
mysql create database junk_testing ;
ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'junk_testing'
Aside from the name mismatch, creating databases is a global
privilege, i.e. database privileges are not evaluated for a
not-yet-existing database. Someone correct me, if I am wrong.
bash-2.05a$ mysql -ujunk -pjunk
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql use junk_test ;
ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'junk_test'
Jupp, see above.
Maybe I have to re-grant, too. Grrr...
No, grants are evaluated when they are needed, not before. I.e. a
database/table whatever has not to exist at GRANT-time for the
privileges to work later.
OK, now I'm just mad :-) Let's try granting to this specific database
just to get things going.
bash-2.05a$ mysql -u droot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql grant all privileges on junk_test to junk ;
ERROR 1046: No Database Selected
mysql use mysql ;
Database changed
That should have got you thinking. :-)
Of course, the grant command implicitly changes the mysql tables, but
you don't have to use them in order to use GRANT. GRANT is an
abstract, generic interace to whatever privilege system an RDBMS has.
I bet the error you get is due to 'junk_test' having no database
specifier (junk_test instead of junk_test.* and therefore MySQL is
trying to use junk_test as table name with the default database
currently used. I.e. I guess you just changed access rights for
mysql.junk_test. Try to see what mysql.db contains now...
mysql grant all privileges on junk_test to junk ;
Query OK, 0 rows affected (0.02 sec)
mysql quit
Bye
bash-2.05a$ mysql -ujunk -pjunk
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql use junk_test ;
ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'junk_test'
... and it would explain that error perfectly. Btw, there is a nice
tool, mysqlaccess, which tells you a bit about the privileges a user
has.
Not only am I a little confused about having had to use a database before
Seems you were not confused enough to see. :-)
I could grant privs, but it didn't work anyway!
I give up. Hlp! :-)
I hope the above hints get you going.
Bye,
Benjamin.
--
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Transactions
Hi. On Sun 2003-03-09 at 11:34:33 -0500, [EMAIL PROTECTED] wrote: From what I understand, transactions are a kind of protection that prevents certain commands from executing if certain other conditions haven't been met. Not completely. They can do much more. Another way to look at transactions is to think about them as a possibility to execute several commands with the advantages (and guarantees) of a single statement, that includes as main points: - all or none of the statements are submitted (if an error occurs, all statements are rolled back) - other threads can only see the effects of any of the statements, when the transaction is committed. And this is particularly important for ecommerce, among other things. Do I more or less have that right? What is important for ecommerce are the ACID criteria (that are guarantees: Atomicity, Consistency, Integrity, Durability - you may want to look that term up), which require transactions to be implemented. Some people use the terms transactions in a wrong way by implying that an RDBMS with transaction support automatically complies with the ACID criteria, which is wrong. It just happens that most RDBMS which support the one also comply to the other. The most abused example is that a bank that wants to transfer money from one account to another. To update the balances you would do something like: UPDATE account SET balance=balance-100 WHERE id=2 UPDATE account SET balance=balance+100 WHERE id=1 Without transactions it may happen that you end up with changing only one of the two accounts. And there are a lot of possibilities why this could happen (power outage, error in the update, network problems, whatever). Some of them can be easily checked and worked-around in application code, some of them can only be handled by changing the database design and application logic a lot to accomodate the risks. My main question, tho', is: Does the latest version of MySQL available in production mode, not a beta or less, have this transactions capability yet? MySQL 3.23 is declared stable since Jan 2001, i.e. more than two years, and had transactions support even longer. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: License
Hi. On Thu 2003-03-06 at 12:45:19 +, [EMAIL PROTECTED] wrote: If I develop a software for my company that works with MySQL and this software will be used only inside the company, do I have to purchase commercial license? No, the GPL license will do: The GPL focuses on _distributing_ between _entities_ and puts conditions on that. Anything else you are free to do. As long as you not distributing (one company counts as one entity), you have to do nothing in order to comply with the GPL. Of course, if you find MySQL useful, it would be sensible to consider buying support to give back a bit. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HAVING vs. WHERE
On Wed 2003-03-05 at 11:17:37 -0500, [EMAIL PROTECTED] wrote: In the MySQL reference, it warns against using HAVING for items that should be in a WHERE clause. I'm not sure what items should be in a WHERE clause. Everything except stuff that only works when it's in the HAVING clause. The HAVING clause is applied only after all rows matching the WHERE clause have been fetched, i.e. if you put a condition from the WHERE clause into the HAVING clause, you take away all possibilities for optimizing. [...] This does it: SELECT * FROM Client WHERE left(inetAdr,instr(inetAdr,.)) 224 OR left(inetAdr,instr(inetAdr,.)) 239 and this works as well: SELECT *,left(inetAdr,instr(inetAdr,.)) as ia FROM Client HAVING ia 224 OR ia 239 In this case you won't notice a big difference, because the first query uses an expression on inetAdr and therefore cannot use indexes either. Try hard to have a pure column on one side of the operator, like this: SELECT * FROM Client WHERE inetAdr NOT BETWEEN 224. AND 239. which will happily use an index on inetAdr. and it is a little cleaner, although as I'm going to be doing this in a PHP script, cleanliness isn't all that important. So I guess I have 2 questions: 1] Which should I use? My version. ;-) 2] Is this the easiest way to check for the multicast address? You may want to have a look at the functions INET_NTOA() and INET_ATON(). HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Syntax confusion
On Wed 2003-03-05 at 17:12:23 -0600, [EMAIL PROTECTED] wrote: [...] BA_DATE = '2003-02-25 00:00:00' AND BA_DATE = '2003-02-25 23:59:59' or TO_DAYS(FROM_DAYS(BA_DATE)) = '2003-02-25' Hm. You mean FROM_DAYS(TO_DAYS(BA_DATE)), don't you? BA_DATE is a timestamp, not a number of days. Aside from that, it prevents use of indexes, which is always bad. :-) How about BA_DATE LIKE '2003-02-25%' instead? HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: speeding up simple SELECT statements with the C api?
Hello. On Thu 2003-02-27 at 14:52:56 -0800, [EMAIL PROTECTED] wrote: [...] Anyways, I'm running into a little bit of a performance issue as the old database report writer had direct access to the database through a c library (no sql interface). On some reports there can be as many as 100,000 select statements. With mysql this is ending up with a performance penalty of about 3x the existing time that the current report writer takes. Running Intel's vtune I can see that the select statements (through mysql_query)are taking up around 90% of the run time. [...] Anyways, I'm not sure if there is any kind of change I can make to reduce this sql statement penalty and was hoping someone here could possibly help reduce it. First, let me clarify, that the perceived speed loss is less with the SQL statements per se, but with: build query - send - task switching to server process - read query - parse - optimizer - read data and build result - send result - task switching to client process - read result. That's of course not complete, but you get the idea. (You can avoid some of the latency by running queries in parallel.) IMHO, there is not much you can do about it. You switched from a specialized database interface to one that is intended for general, rational storage. A general approach is always slower than a specialized one, if both are of the same quality. There are some things you can try to get more speed, but when have implemented so much of them that you are at the old speed, you will have a similar specialized solution as you had before. If you say you are fine with the specialized solution, I wonder why you changed to an RDBMS to begin with (you could have taken, e.g. BDB). If you are not, I fear you have to live with some speed loss. That said, I suggest you take a look at (and benchmark for your application): - UNIX sockets, make sure to use them if you can, - your queries: can you combine some of the 100.000 statements? (oh, see you answered that below), - Using MySQL 4.0 to take advantage of the new query cache (you have to enable it explicitly), - threading (client-side), maybe you can run stuff in parallel, - the HANDLER commands which bypass some abstraction layers and - libmysqld, the embedded library, which bypasses the connection overhead. I am sure there is more, but that is what came to mind currently. [...] I have a feeling it's the overhead with every query that's really the problem here and that there really is no fix. That's right. I also can't really combine the sql statements and save the data for later due to the unique format of the reports. But perhaps there are some optimizations I can make to help. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL 4.0.11 is released
Hi. On Wed 2003-02-26 at 18:36:07 +0100, [EMAIL PROTECTED] wrote: [...] As Mark has already stated, this is a typo - it should have read GAMMA instead. Sorry for the confusion. Would you mind to elaborate a bit on the current state of the 4.0 cycle? Since Monty suggested in the 4.0.8 release announcement that 4.0.9 will be declared stable if no major problems arise with 4.0.8, I am a little bit suprised that we are still at gamma with 4.0.11. I understand that 4.0.9 was a quick after-release in order to fix the problem with hostname resolving, which affected a lot of people. I am not aware of a major bug fix which led to 4.0.10, but there were so much small changes. For 4.0.11, I could guess that the change in NULL sorting behaviour is the major thing. So, well, I think my question is, is that really the way I described and the stable release is only a little step away (and only delayed by what could be called bad luck) or is there a more general issue I am missing? As I said, a summary of the current state and your thoughts on next releases would be fine; I don't expect any binding statements. :) TIA, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to query an entire row?
Hello. On Mon 2003-02-24 at 11:28:05 +, [EMAIL PROTECTED] wrote: Hello, In a table like this: ID Item1 char(100) Item2 char(100) . . ItemN char(100) What's the cleanest way to do this mysql query: SELECT * FROM MyTable WHERE AnyColumn LIKE '%mysearch%' Only way I can think to do it is: SELECT * FROM MyTable WHERE (Item1 LIKE '%mysearch%' OR Item2 LIKE '%mysearch%' OR ) Yes, that's it. If you think that's unpretty, you are right. With a normalized design, you usually shouldn't need such a query. In other words, if you find yourself needing to do such queries regularly, you may want to re-evaluate your database design. Depending on the context, a look at FULLTEXT indexes may be helpful, too. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Why is MySQL 4 standard binary 4mb larger than max?
On Tue 2003-02-18 at 01:17:16 -0800, [EMAIL PROTECTED] wrote: Just curious since max has more featured compiled in yet it's quite smaller for download at mysql.com. Because the MySQL-Max package only contains mysqld-max, and you are supposed to install it alongside of the MySQL package, which contains everything else needed, not only the standard mysqld. HTH, Benjamin. PS: Please start a new thread when you start a new subject (i.e. do not use a unrelated message and reply to that). Else, most mail readers will show it belonging to the post you replied to. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InterBase vs. Mysql
Hi. Just a little correction. On Tue 2003-02-18 at 12:44:39 +0100, [EMAIL PROTECTED] wrote: Hi Maciej, I don't know much about Interbase, but MySQL is for some cases a very good choice! Some database engineers would say MySQL isn't a database because it has no transactions by know (in a stable version). MySQL 3.23 has InnoDB support and therefore transactions and is the stable version since for two years now. The only change with 4.0 is that InnoDB is going to be part of the MySQL package instead of MySQL-Max, as it was with 3.23. But think about, if you really need this!!! In versions 4.x (coming soon in a stable version) transactions, subselects etc. would be available! Soon coming as stable is 4.0 (it is already good enough for production use, IMHO), but sub-selects are in 4.1, which is still declared alpha. The main features of 4.0 as listed on http://www.mysql.com/doc/en/News-4.0.x.html are: - a query cache (may vastly improve performance for many apps) - improved FULLTEXT indexing - MERGE table improvements - support for UNION in select - libmysqld, a embedded version of MySQL - more fine grained privileges (with GRANT) - dynamic server variables (change the configuration on the fly) - rewrite of replication with new features - and some other stuff HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InterBase vs. Mysql
On Tue 2003-02-18 at 12:02:59 +, [EMAIL PROTECTED] wrote: [...] This is the main reason why I am looking at PostgreSQL at the moment, although I've not looked much at Interbase, any good? Either that, or fork MySQL into FreeMySQL, as we can do under the GPL, and not charge our selves :) Aehem. There seems to be some misconception here. Either your program is fine with MySQL being GPL or not. If it is (and your forking example would work for you) either by using MySQL in a way that your program is not required to be GPL'ed or by GPL'ing your program, you need no commercial license from MySQL AB either, and you can already distribute your program with MySQL without the need of a fork or whatever. Or your program needs a commercial license, than forking MySQL would not help, because you still have to adhere to the GPL. The only reason MySQL AB can hand out a commercial license is because they are also the Copyright holders, which you aren't even after forking. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InterBase vs. Mysql
Hi. On Tue 2003-02-18 at 11:48:53 +, [EMAIL PROTECTED] wrote: hi Maciej, the only arguments you can get in favour of mysql is that it's free, and some of its SQL statement are faster than interbase. Well, and outstanding support, IMHO. Interbase has the advantage of having views, stored procedures,triggers,and it's crazily fast. Don't forget that interbase is also written by professionnal on borland campus, guyz who completed a university degree and were assessed and were judged good enough to work with borland; whilst the average developpers for mysql are volunteers who are not paid for what they are doing! You seem to have some greater misconception about the development of MySQL. Please don't make such statements when you don't know the background. Although MySQL had some great contributions (in source code or otherwise) from volunteers, it is mainly developed by the staff of professionals of MySQL AB. So the speak about university degrees is pointless (aside from that, why do you think volunteers wouldn't have a degree...). I think you should insist on the free aspect of mysql and it's simplicity of use, because for somebody who knows the 2 databases, features wise, robustness wise and speed wise, interbase is far ahead of mysql. I give you features wise. The other two, robustness and speed wise, I see no indication for. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InterBase vs. Mysql
On Tue 2003-02-18 at 12:57:04 +, [EMAIL PROTECTED] wrote: [...] Most benchmark published actually give credit to mysql when it comes to Mysql Vs Interbase, but as an end user, the best tests are the one i conduct myself in my premises. Therefor when i say that mysql Vs interbase definitely goes to interbase, understand that it's the result of my own experience! Hm. So you take the limited experience of your use case and make general statements from that. Don't get me wrong. I absolutely believe you that InterBase is faster for you in your use cases. But that doesn't mean that it is in general. As you can see from the reactions, it would help if you stated your personal experience as such. Bear in mind that all the benchmark published can give you all the credit possible, if my experience as an enduser proove me otherwise, i'll tend to believe what i can see and proove, not what i can read in an article! I don't see how this is less biased than a benchmark might be. Of course, it is the relevant part for you personally, in accordance with the next statement. So my word to any enduser would be : conduct your own experiences... and see for yourself. Absolutely. Seconded. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InterBase vs. Mysql
On Tue 2003-02-18 at 16:11:11 +, [EMAIL PROTECTED] wrote: Benjamin Pflugmann wrote: [...] There is also some middle ground here. Which is the overlap of the two. No. Either the way you distribute your software is GPL-compliant or not. If it is not, you need a commercial license, if it is, you are fine. Forking the source requires it to be still under the GPL, so the license requirements have not changed. MySQL say that this is an extension of the application, and therefore breaks the GPL, and therefore a licence is needed. Okay, you can argue, if MySQL AB's interpretation of the GPL is correct, but this changes nothing. Even if you fork, they will still own the copyright on a major part of the code and can still sue you, if they like (I do not mean to imply that they would be fast to sue). They are however, the only big GPL user who thinks this way. I note for example the number of companies selling commercial CGI software designed to run on Apache, Wrong example. Apache is not licensed under GPL (it's Apache License). to which no licence is mandatory. Also Sendmail, Neither is sendmail.(it's BSD license). GCC, Have not seen many packages that add on GCC. And derived output of GCC is explicitly excluded from being GPL. other DBMS's, Examples? PostgreSQL is not GPL'ed, InterBase neither (I am not sure if that is still current, but last time I looked it was not). and indeed GNU/Linux it's self. That's a valid reference in that Linus Torvald has indeed said that he does not think kernel modules have to be GPL'ed, but then, Linus is not someone who much cares about such issues. [...] Therefore, I can see no reason why not somebody could fork MySQL into FreeSQL. It would take a few hours at SorceForge, a 'sed' of MySQL into FreeSQL', and a good posting to Slashdot. Keep it 100% GPL without breaking either the wording or the spirit of the document. Remove all reference to copyright material belonging to MySQL. It's either GPL or cpryright, not both. No offense meant, but you seem to have a lack of understanding of how the GPL works. It cannot work without copyright. It is based on it. It sounds as if you base your opinion on hearsay. I suggest to read the GPL FAQ http://www.gnu.org/licenses/gpl-faq.html. Or talk to an lawyer if you need. Then use this without commercial licence... BTW, as to another posting. 'Either accept the GPL or purchase a licence'. I do note another option (apart from forking): Use something else. Is MySQL really that good? I do worry that with arrogant statements like this, this is exactly what people will do, in droves. What's the problem? Do use whatever fits best with your need. The point of the statement is that there is no right to have MySQL without cost. You can have it with without cost, if you abide by the GPL. Or else you can buy it. Or you can use something else. Freedom of choice. Although that may sound arrogant, it is not meant this way. It is meant as being realitistic: The people who put a lot of hard work into making MySQL have chosen the GPL. So you should respect that. One could also see it the other way: it sounds kind of arrogant of people trying to tell MySQL AB how they have to license their software. You are free to choose the product of your choice. MySQL AB is free to choose the license(s) of their choice. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Quick License Question...
Hi. First, I am not a lawyer. On Tue 2003-02-18 at 15:48:00 -0500, [EMAIL PROTECTED] wrote: Quick question about the license issue that I thought of while reading through the Interbase Vs. MySQL threads. If I develop a program that uses MySQL for my company and it is only used for internal use, never repacked and sold/distributed outside the company what type of license aggrement is that under? If you mean whether that complies with the GPL, the answer is yes. The GPL is about distributing and therefore doesn't care about your use case (a company as a single entity with regard to this). In effect, you are using MySQL without license (but only under the fair use clauses of the copyright law, which allow you that). The reason you may do so with MySQL, but not with some other commercial software is simply that MySQL AB gave you the software without asking for money by making the download available (without attaching any conditions). This project would have code that would obviously be 'sensitive' information for the company so Open Source would be out the question, but as this would never be re-distributed am I right in thinking we do not need to buy a license aggrement from MySQL? Yes. I was reading throuhg the manual in the license section and noticed they said it would be 'nice' that if MySQL was helping your enterprise then you should at least buy some support from them. However, I am comfortable enough with MySQL and its use is VERY light weight that it would be pretty silly to buy support from them. Not saying anything against the MySQL team, but they did make the product fairly easy to work with :) Yeah, the idea in that sentence behind buying support is not about having support, but about given some money in order to pay back, if you think that would be the right thing to do. That you also have official support this way is just an added benefit. In other words: it would be just a gift in order to say thanks. (With the thought that you already got a gift from them: free use of MySQL.) HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InterBase vs. Mysql
On Tue 2003-02-18 at 17:06:17 +, [EMAIL PROTECTED] wrote: This will be my last posting. I don't belive I am being constructive and have no wish to instantly be hated by the whole of MySQL. [...] Oh, I do not have anything at all against you. I just tried to correct what I saw as a misconception of yours. If anything of that sounded offensive to you, I apologize, as that was not my intention. Blame it on me not being a native speaker, if you want. Hope you have a nice day, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: REPOST: MySQL 3.23.54 is a buggy, crashing piece of crap.
On Mon 2003-02-17 at 03:38:07 -0600, [EMAIL PROTECTED] wrote: Reply when you have a job, dumbass. I have a system that makes more in a week than you make in a year crashing because of this buggy piece of shit. If a system supposedly making millions a year is unstable you are a fool that you haven't solved it already using your advanced support contract (http://www.mysql.com/support/), that you surely have. I don't give a rats ass what your worthless opinion on my post is. Now fuck off, dumbass. Yeah. That lack of respect is exactly why I refused to read your original post about the problem to the end. As I said, that is a list of volunteers and your attitude surely doesn't help you to get answers. Bye, Benjamin. - Original Message - From: Benjamin Pflugmann [EMAIL PROTECTED] To: Jason Maskell [EMAIL PROTECTED] Sent: Monday, February 17, 2003 3:35 AM Subject: Re: REPOST: MySQL 3.23.54 is a buggy, crashing piece of crap. On Mon 2003-02-17 at 02:40:57 -0600, [EMAIL PROTECTED] wrote: Oh grow up. Exactly the kind of reply I expected from you. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: REPOST: MySQL 3.23.54 is a buggy, crashing piece of crap.
On Mon 2003-02-17 at 12:14:12 +0100, [EMAIL PROTECTED] wrote: [...] Please disregard this mail. It was not supposed to go to the list. (forgot to remove the CC that my mailer is set up to automatically append for mails going to my -mysql address). My apologies about that, Jason. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mail Problems
Hi. On Sat 2003-02-08 at 16:06:03 +, [EMAIL PROTECTED] wrote: [...] Re: InnoDB foreign keys bug Yesterday 12:30:56 am BUG: InnoDB ORDER BY DESC may hang in 4.0.10 Yesterday 12:40:40 am and they arrived 24 hours after being sent! Yes, the list server is lagging behind now for a while (AFAICT it started End of January and got worse since then). Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to build Relay Replication system ?
Hello. On Sat 2003-02-08 at 07:24:17 -0700, [EMAIL PROTECTED] wrote: [...] Do you think the following links may help? * http://www.mysql.com/doc/en/Replication_HOWTO.html * http://www.mysql.com/doc/en/Replication.html * http://www.mysql.com/doc/en/Replication_FAQ.html * http://www.mysql.com/doc/en/Replication_Options.html * http://www.mysql.com/doc/en/Replication_SQL.html This was an automated response to your email 'How to build Relay Replication system ?'. Final search keyword used to query the manual was 'How to build Relay Replication system ?'. Feedbacks, suggestions and complaints about me should be directed to [EMAIL PROTECTED] Is this bot somehow endorsed by the list admin / MySQL AB? Anyhow, could you please stop CC'ing the replies to the list? There is enough mail already without getting a copy triggered by every new question. I am not pleased. Or in other words: There is no additional benefit for subscribers in getting all these automatically generated search results. I am sure that most subscribers know where to look if they want to search the manual. So please, if you must, keep the reply to the original author only. Regards, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: creating update files
Hi. On Mon 2003-02-03 at 10:19:59 +0100, [EMAIL PROTECTED] wrote: [...] That can be made to work with several versions. In other words, it would not check a version number saved in the database, but determine the version by the database structure itself. That would give #2 with the assurance that an update script is only run, if the database structure is as expected, no matter happened to the database in-between (it is fascinating what clients can do to files they are not supposed to even know about ;-). Sound like a good idea, ie that I should use a md5 hash to recognise the structure. and use that as a sort of versioning of the tables... I think the md5 would have to be calculated ona per table basis, since if we introduce some bug-fixes in one module only and it requires a table-update, this would be recognised in a future update... Of course it is up to you, but I wouldn't do it this way. Changing one table but not another could break your application. I would call your bug-fixes a new version and handle it accordingly. the question is then, do select create table TEST work exactly the same on different os:s ie if I have the same table on a windows machine and on a linux machine, does the above query return the exact same result? even eith line endings? or could they return 2 different queries and therefore making the md5 calculating prove worthless? Interesting point. I did not think about this before (have no cross-platform issues here). Well, if you use mysqldump, you get different versions anyhow (because it contains info about server, etc.). On UNIX at least, it is quite easy to accomodate for lines ending. Hm. There is a another problem. Newer versions of mysqldump use SHOW CREATE TABLE, AFAIK, and therefore the dump will look server-dependend (comments for 4.0 features or such). In short, it is not as stable as needed regarding md5sums across either, platforms or MySQL versions. Missed that, as I concentrated on what you asked for, changes in your database. The cross-platform part can be solved relatively easily by either normalizing the dumps before building the checksum (which is not too hard), or by simply having two checksums (the MS Windows and the UNIX one) pointing to the same update-script. For the MySQL versions issue, it should be enough to keep a older 3.23 mysqldump around, and use always the same. This may break somewhen (MySQL 5.0?), but it should give you mid-term stability for the process. or are there other ways to get to the database structure that work the same on different platforms...? Hm. Write your on mysqldump? (That's not as hard as it sounds, because you don't have to start from scratch, but only modify the existing one.) HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: zerofill fields
Hi. On Tue 2003-02-04 at 17:04:39 -, [EMAIL PROTECTED] wrote: I noticed that when you return a zerofill field from a select statement into a server-side language, say PHP or Perl, it will store the number with the zeros included, great no problem. However, if I insert into that table which has a primary key which is set as a zerofill field, when I use the PHP command mysql_insert_id() it returns the primary key value, but without the zeros, is there any way round this apart from writing some code to add the zeros? No, as mysql_insert_id operates on a number, not a string, so there is no way to pass the leading zeros trough. Regards, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Opposite selection...
Hi. On Tue 2003-02-04 at 12:25:08 -0500, [EMAIL PROTECTED] wrote: Ok I'm stumped on what I think should be a somewhat simple query. What I have so far is a list of names that is in a list of projects AND in a the main contact list by doing the following query: SELECT p.name, p.company FROM contacts c, projects p WHERE CONCAT(c.firstName, , c.lastName) = p.name AND c.company = p.company This is good and works correctly, what I need now is the opposite of this. The names that are in the project list but NOT in the contact list. If I had some subqueries this would be a simple NOT IN :) but as I dont (mysql 3.23.55) I'm not sure how to attack this. Well, manual explicitly explains how to cope with the lack of sub-selects: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html So something like SELECT p.name, p.company FROM project p LEFT JOIN contacts c ON c.company = p.company AND CONCAT(c.firstName, , c.lastName) = p.name WHERE c.lastName IS NULL should do the job. This won't be able to use indexes due to the expression (CONCAT) over the right-table columns (this was not true for your original example, because a normal joins allows exchanges the order, a LEFT JOIN doesn't - a sub-select wouldn't help with this, btw). If you are sure that no spaces are in firstname resp. lastname, you can rewrite the condition to enable use of indexes: ON c.company = p.company AND c.firstName = SUBSTRING_INDEX( p.name, ' ', 1 ) AND c.lastName = SUBSTRING_INDEX( p.name, ' ', -1 ) HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: check doesn't seem to work
Hi. First of all, do all of us, including yourself, a favor and upgrade to 4.0.9 (or 4.0.10 which should come out this week). Reporting problems against a 7 months old alpha version is not very, well, productive, if the current release is gamma quality. On Mon 2003-02-03 at 10:04:58 +0100, [EMAIL PROTECTED] wrote: I have a create table command in which the check statement does not seem to do anything. I know it was just there for compatibility in older mySQL versions, but I thought it should be working in version 4.0.2. Isn't it? Where did you get that from? The only 4.0 change entry mentioned CHECK is for 4.0.6 (which you don't have) and explictly tells that it is still not implemented: http://www.mysql.com/doc/en/News-4.0.6.html And the CREATE TABLE section explains the same: http://www.mysql.com/doc/en/CREATE_TABLE.html Regards, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump issue (table named group)
Hello. On Fri 2003-01-31 at 13:45:26 -0600, [EMAIL PROTECTED] wrote: Description: One of our customers had a table named group, admitedly a bad design but still not something that should cause mysqldump to fail hard! Please be more specific. Did mysqldump error out or the re-import of the data? How-To-Repeat: Name a table group and run mysqldump, even with --opt Have you tried --quote-names ? Fix: Quick workaround is to rename the table, but actually, mysqldump should surround columns and tables with `` marks when creating sql code See above, --quote-names is supposed to do that. If it doesn't for you, please provide a repeatable example. Bye, Benjamin. Release: mysql-3.23.54 (Source distribution) System: Linux x.com 2.4.18-19.7.x #1 Thu Dec 12 09:00:42 EST 2002 i686 unknown [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Syntax
On Sat 2003-02-01 at 10:35:46 -, [EMAIL PROTECTED] wrote: Hi Benjamin, Wow, that sure sorted that problem out... I had to rejig it slightly to get it to work, Oops... too much copypaste by me :-) but this is the final working version: Glad it worked out. Bye, Benjamin. SELECT b.id, p.part_code, p.product_type, p.description, po.options, b.price, b.quantity, b.price*b.quantity AS total FROM basket_header bh INNER JOIN basket b ON b.basket_id = bh.basket_id LEFT JOIN products p ON p.prod_id = b.prod_id LEFT JOIN product_options po ON po.po_id = b.op_id WHERE bh.basket_id = 4 GROUP BY b.id, p.part_code, p.product_type, p.description, po.options, b.price, b.quantity, total, bh.basket_id, p.options Many thanks, now I'll work out why I couldn't do that so I can better understand it. [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Writing a database program in GNU C++ using MySQL.
Hi. Accessing MySQL is equally easy in any of the languages (C++, Perl, PHP, Java). So if that is the main point and it's correct that you, Prabu, are already comfortable with C++, stick with it. A reason a lot of people use Perl, PHP or Java is that many use MySQL in conjunction with web pages and these languages come with built-in support or ready packages for writing CGI for creating web pages. So if you do not write CGIs - and it doesn't sound like this - or you already have appropriate C++ libs for CGI and templates, then the question of which, C++ or Perl is better, boils down to a simple language comparison. In other words, since MySQL is easily accessible in both, put that point out of the requirements list and decide on the remaining factors, whatever they may be for you. HTH, Benjamin. On Thu 2003-01-30 at 14:51:08 -0500, [EMAIL PROTECTED] wrote: Accessing a DB via Java is very easy as well.. IMHO PHP and the like scripts are over rated. You'll have much more flexibility with Java. (If you don't have to worry about firewalls...) Since it sounds like you a C++ person you'll pick up on Java quickly, if you don't know it already. If you have to worry about a firewall then you can still use Java with Servlets but there will be an added learning curve. In that case your probably better off using PHP/JSP, etc From: Kamara Eric R-M [EMAIL PROTECTED] Date: 2003/01/30 Thu AM 07:34:58 EST To: Prabu Subroto [EMAIL PROTECTED] CC: [EMAIL PROTECTED] [EMAIL PROTECTED] Subject: Re: Writing a database program in GNU C++ using MySQL. Hi Prabu, From my own experience I'd say that PHP is the best option since it can be compiled with MySQL support and you will find that accessing the database is very easy. Regards, Eric On Wed, 29 Jan 2003, Prabu Subroto wrote: Dear my friends, My boss wants a database application running on linux machine without XWindows. Is it easy to make the connection to MySQL with GNU C++ . Is perl better then GNU C++ in this case? How is Jave ? TAC. [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to update the database through an applet?
Hi. I am sorry, I am not able to follow you. Maybe you should describe in more detail what components are involved and what you want to archieve. (If you simply want to store information you get from a device, I don't understand where the database related problem is - just store the data as you would with any other. If it is not this, I am not able to extract this from the description). Regards, Benjamin. On Thu 2003-01-30 at 19:36:35 -0800, [EMAIL PROTECTED] wrote: PHP works fine to update a mysql database on the server. But I would like to access a device connected to the serial port or a USB port on the client computer through an applet and store this information in the database on the server. I have tried to find an example of such a transaction on the net, unsuccessfully. Has anybody out there experiences with this kind of problem? It could be any solution, such as PHP + javascript, or PHP all by itself. Is there a good publication talking about this issue? Thank you Mario -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question about Table_Options
Hi.
On Fri 2003-01-31 at 10:39:13 +, [EMAIL PROTECTED] wrote:
Hi
When creating a table can you use multiple Table_Options?
Yes. How about simply trying out beforehand?
The O'Reilly Managing Using MySQL show on page 288 a table been created
with two options ...)AUTO_INCREMENT = 1, TYPE=InnoDB;
But the MySQL manual says or:-
table_options:
TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
orAUTO_INCREMENT = #
.
As it does for create_definition, but that does not mean that you
can only define either a column or a key. The fact that matters is
that one is presented as a list [(create_definition,...)] in the
CREATE TABLE statement, the other is not.
So, yes, the manual is incomplete (especially, because it gives no
indication that you have to seperate the options by comma).
HTH,
Benjamin.
--
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySql, PHP and Javascript
Hi. On Fri 2003-01-31 at 08:27:11 +, [EMAIL PROTECTED] wrote: Is it possible to mix javascript and PHP in the same script?? Of course. One (PHP) is run on the server side in order to create a page which is sent to the client (here: the browser). The other (Javascript) may be contained in that page and is executed on the client side. So if you want to use Javascript, you have to write your PHP pages in a way that they output the needed Javascript. Which should be easy, if you kept your algorithms and HTML kind of seperated in PHP. HTH, Benjamin. PS. If you completly mixed printing the HTML into the algorithms, then it isn't going to be easy, but no change to the HTML would be - so you didn't do that, right? I have a number of web based apps written in PHP/MySql and while they are functionally pretty good they are aesthetically garbage. I'll like to pretty up the interfaces with rollovers etc, but haven't got time to learn JS properly especially if I have to completly re-write the functionality. [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.1 binaries
Hi. On Fri 2003-01-31 at 10:05:40 +0100, [EMAIL PROTECTED] wrote: any further news about 4.1 binaries release date? Why the eagerness? 4.1 will be alpha. If you are going to use it seriously (in your development), you want to compile it yourself anyhow, because you will have to recompile it in order to incorporate any fixes for bugs (which hopefully you reported), long before the next release is available. If not, you are only playing around and a few days should not matter. That said, considering the press release is 10 days old, I would have expected to see binaries already. OTOH, it's the first 4.1 release, so it's hard to say which problems they may encounter. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: TIMESTAMP field is updated unintentionally
Hi. On Fri 2003-01-31 at 12:17:42 +0100, [EMAIL PROTECTED] wrote: I was already questioning my sanity, but the problem below is reproduceable: [...] If I do mysql update T_ORDH set STATUS=2 where PK_ID=26272; ERSTELL_DATUM is set to the current date. I know that a timestamp takes the current time, if set it to NULL, but since I'm not touching it, it shouldn't change, should it? [...] The big question: Is it a bug or a feature? A feature, it is described in detail in the section that explains the TIMESTAMP column type: http://www.mysql.com/doc/en/DATETIME.html If you don't want that behaviour, use DATETIME with NOW() instead. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: creating update files
Hi. On Fri 2003-01-31 at 12:32:51 +0100, [EMAIL PROTECTED] wrote: Hi there, I have a little question for you in the mysql community.. I was wondering how persons in this community handles changes to the database when your application that uses the database needs some new tables, columns, etc... I think this always depends on your use case, but since you wanted to know how we handle this... Do you have an application with a sort of upgrade functionallity that when started checks the database for incosistency and when it finds some updates the database? (ie compares the structure of the table in the database to some template stored in the application or as datafiles/scripts) Or do you use some version system where the application instead of looking for inconsistency just look at the version number of the database, compares it to some internal version, and updates the database accordingly? (ie manually creating all the needed updatescripts that needs to run the application and have the application select between these) Or perhaps you do this update of the database manually when intalling a new version? Effectively that is what I do. Using the development database, I write the application and change the database accordingly (and save the statements I use for this). When the program is ready, I write a script with the SQL commands needed to update the database to the new version (most times that involves only slightly correcting the statements I saved beforehand) and test that it results indeed in the wanted table structure. Then I take both, the new program and the SQL script and install them in parallel on the production machine. For me, version 1 would be preferable, but unfourtunally this does not work in every scenario, but i guess that it normally could take care of about 90% off all my changes... the second system would be able to take care of all type of changes, but it requires more labour... Huh? I am not sure why you think #2 is more work. I think an application which tries to make consistency checks and do the correct SQL statements (#1) is much more complex than one that simply executes some script based on a version number (#2). Especially since you probably already have those SQL scripts as side product of your testing (if you are using a GUI, I sincerly hope it is able to log the commands it executes for you. Or use the update log of MySQL). IMHO, the real (potential) drawback of #2 is that it relies on the version number being correct and therefore fragile against change. E.g. my experience shows that emergencies sometimes require to make changes by hand, which invalidates the version, and it being an emergency, the change is high that the version is not changed accordingly. So I think that #1 has the advantage of being adaptive while being a lot of work. I am also not sure, why you think that #1 would be only able to handle 90% of the cases, theoretically it can dump the whole database structure to find out about types and whatever. Hm. Maybe that's why you don't think it is much work - only concentrating on the common cases? so, how do you all manage this? I don't use the first two solutions, because I am around when I do updates anyhow (and I wouldn't want the application to change the production system significantly when I am not around), so I prefer doing the version check myself. Maybe that is, because I only have a handful of machines to take care of. If I had to roll out changes to several hundreds of machines (possibly clients) and couldn't be sure which version is running, I would use a of variation of #2: write a little script additionally to the SQL update-script, which makes a SQL dump of the existing database structure, compares (maybe via MD5 sum to safe space) that with one that I made on the test system and bails out, if they differ. That can be made to work with several versions. In other words, it would not check a version number saved in the database, but determine the version by the database structure itself. That would give #2 with the assurance that an update script is only run, if the database structure is as expected, no matter happened to the database in-between (it is fascinating what clients can do to files they are not supposed to even know about ;-). HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Autoincrement : how does it work / how to reset it
Hi. On Fri 2003-01-31 at 06:48:45 -0800, [EMAIL PROTECTED] wrote: Hi, I have been using autoincrement fields for some time but was wondering how does it work in some special situations. Most of this depends on which MySQL version you use and which table type, unfortunately. OTOH, for the common case all just work fine and relying too much on the edge cases is not a good idea anyhow. Some relevant manual pages are: 1: http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html 2: http://www.mysql.com/doc/en/SEC471.html 3: http://www.mysql.com/doc/en/CREATE_TABLE.html 4: http://www.mysql.com/doc/en/ALTER_TABLE.html Ex. suppose I have an autoincrement field called num and the last one has value of 10. I delete the last on and insert a new one. Which value will it have ? 10 or 11 With the ISAM and BDB table handler, you will get 10 [see 3]. With MyISAM 11 [3]. With InnoDB 11, except if you restart MySQL in-between, then you will get 10 [2,3]. The common part is: You will get a new number, which is at least greater than the current maximum value, but numbers may be reused. If you ignore older table handlers (ISAM,BDB) and ignore server restarts (my last was about 100 days before): You will get a new, never-used-before number that is greater than the current maximum value with MyISAM or InnoDB. I read that if I issue a query delete * from table and delete from table I have diferent results (in regards to the auto increment field). Is this correct ? The difference is whether you specify a WHERE clause (delete * is not correct syntax, AFAIK), i.e. with DELETE FROM table_name (without a WHERE) the sequence starts over (for any table handler) if you are in auto-commit mode. [3] With a WHERE clause, nothing special happens. You can also set the value explicitly by using [see 4] ALTER TABLE table_name AUTO_INCREMENT = # HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: (SQL) Count Distincts
Hi. On Fri 2003-01-31 at 10:44:58 -0500, [EMAIL PROTECTED] wrote: I am trying to get a count of Distinct IP's from my homemade hit-log database (don't ask). The db is MySQL. I'm trying this: SELECT DISTINCT ClientIP, COUNT(*), Month(TimeStamp), DayOfMonth(TimeStamp) FROM RedirectLog WHERE (TimeStamp BETWEEN '2003013000' AND '2003013100') GROUP BY Month(TimeStamp), DayOfMonth(TimeStamp) But it doesn't appear to be doing what I want. The Count per day is the same as it was when I didn't have DISTINCT ClientIP, in there, and I know from looking at the raw data that there are some duplicate ClientIP's in the data. I am not sure what your query is supposed to do, because having ClientIP without a grouping function doesn't make sense (you will get a random ClientIP from each day and the chance is high, that it will be distinct from the others). But from your description it sounds as if you want something like SELECT COUNT(DISTINCT ClientIP) AS amount, MONTH(TimeStamp) AS month, DAYOFMONTH(TimeStamp) AS day FROM RedirectLog WHERETimeStamp BETWEEN 2003013000 AND 2003013100 GROUP BY month, day The relevant part is using DISTINCT with COUNT, the other changes (like the GROUP BY clause) were just cleanups while I was parsing your query. HTH, Benjamin. PS: Removed [EMAIL PROTECTED] from CC, because #1 cross-posting is bad bad bad, and #2 I don't want to post to a list that I don't know. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Syntax
Hi. On Fri 2003-01-31 at 15:46:37 -, [EMAIL PROTECTED] wrote: Hi All, Can anyone help me get this query working in MySQL, this was created using Access, but it doesn't port well for MySQL syntax: SELECT basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, basket.price*basket.quantity AS total FROM (products LEFT JOIN product_options ON products.prod_id=product_options.prod_id) RIGHT JOIN (product_options AS product_options_1 RIGHT JOIN (basket_header INNER JOIN basket ON basket_header.basket_id=basket.basket_id) ON product_options_1.po_id=basket.op_id) ON products.prod_id=basket.prod_id GROUP BY basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, basket.price*basket.quantity, basket_header.basket_id, products.options HAVING (((basket_header.basket_id)=4)); Reformatting for readability that is: SELECT basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, basket.price*basket.quantity AS total FROM ( products LEFT JOIN product_options ON product_options.prod_id = products.prod_id ) RIGHT JOIN ( product_options AS product_options_1 RIGHT JOIN ( basket_header INNER JOIN basket ON basket.basket_id = basket_header.basket_id ) ON product_options_1.po_id = basket.op_id ) ON products.prod_id = basket.prod_id GROUP BY basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, total, basket_header.basket_id, products.options HAVING basket_header.basket_id=4; Here is the error message MySQL reports: ERROR 1064: You have an error in your SQL syntax near '(product_options AS product_options_1 RIGHT JOIN (basket_header INNER JOIN baske' at line 9 Seems MySQL doesn't like the RIGHT JOIN syntax. Any ideas to the correct syntax? Oh, RIGHT JOINs are fine. What it doesn't like are the parenthesis, I think. So simply reordering the joins (and by that replacing RIGHT JOINs with LEFT JOINs were appropriate and vice versa) should do the trick. FROM ( ( basket_header INNER JOIN basket ON basket.basket_id = basket_header.basket_id ) LEFT JOIN product_options AS product_options_1 ON product_options_1.po_id = basket.op_id ) LEFT JOIN ( products LEFT JOIN product_options ON product_options.prod_id = products.prod_id ) ON products.prod_id = basket.prod_id Now, a lot of the parenthesis are redundant. Written this way, it becomes more obvious, that product_options (not product_options_1) is neither referenced by a other table in an ON clause nor used in the select part, so what is the reason to include it to begin with? It's redundant. Additionally, I don't see the reason for the HAVING clause. IMHO the condition would be as good in the WHERE clause (where the optimizer can make better use of it). Aside from that, I prefer table aliases to get rid of the long names, so the end result would look like SELECT basket.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity AS total FROM basket_header bh INNER JOIN basket b ON b.basket_id = bh.basket_id LEFT JOIN products p ON p.prod_id = b.prod_id LEFT JOIN product_options po1 ON po1.po_id = b.op_id HAVING bh.basket_id = 4; GROUP BY b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, total, bh.basket_id, p.options (I did not rename po1 to po in order to avoid confusion.) HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: line breaks
Hi. In which way is this a MySQL related problem? Please choose a more appropriate forum next time. On Fri 2003-01-31 at 18:22:50 +0200, [EMAIL PROTECTED] wrote: Hi, From a web form I am collecting information to a table. On the form there is a textarea element storing to a text field on the mysql table. Since the textarea can hold the enter key, it's stored in to the field also, but when i try to call that cell and display it on the web page, i see that line breaks are ignored. But the sentences must be displayed as they are entered. - what is the stored character code of the enter key in the text field, i am figuring out to find that character and replace it with the br element, for the exact display, Depends on the browser used to enter the text. Usually it's '\n' (i.e. the character with code 10 resp. hex 0x0A) - do you have a shorter way to do this? Not really, except if you are happy with using PRE Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Percentile calculations
Hi. On Fri 2003-01-31 at 17:22:37 -, [EMAIL PROTECTED] wrote: [...] select count(*) from percentile where criteria; Work out 95% or this value. create temporary table percentile (id int unsigned auto_increment primary key, scantime decimal(20,10)); insert into percentile (scantime) select processingtime from emails where same criteria order by processingtime desc limit value from above; select min(scantime) from percentile; If I am not mistaken, you can pick the row in question without a separat table: Basically you limit the query to the first n rows (in descending order) and then pick the one with the lowerst value, which should have been the last in the limited range. So you could pick that row directly, I think: SELECT processingtime FROM emails WHERE blah ORDER BY processingtime DESC LIMIT value,1 Second, why use 95% of the table, if you can do with 5%? In other words, calculate the remainder, sort ascending and limit by the remainder you calculated. The difference should especially in your case, where you copy those rows and can avoid to do so for 90% of the columns. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to insert an Image in a table with libmysqlclient and API C ?
Hi. On Fri 2003-01-31 at 18:56:19 +0100, [EMAIL PROTECTED] wrote: Hi all, my problem is the following, it's possible to insert an image in a table with the libmysqlclient ? Yes. My language is C i'm working with GNU/Linux Bye, Benjamin. PS: In explanation: if you want a more specific answer, you have to provide more details... MySQL doesn't care whether you store text or binary data, so what is the problem? -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Properly shutting down mysql
Hi. On Mon 2003-01-27 at 16:31:48 -0800, [EMAIL PROTECTED] wrote: If I manually shut down mysql, I usaully use the mysqladmin shutdown command. However, I noticed that the red hat way of stopping mysql in its init scripts is by issuing a kill command to the pid. U, is this wise? What about if the tables aren't written to disk? kill only sends the process a request to terminate (in opposite to kill -9 which kills it instantly). MySQL catches this, therefore it is usually save to use kill this way. But mysqladmin is recommended, because in opposite to kill it also works - if you have no way to become root or the user MySQL is running under, but a database account with shutdown privilege, - you are working from a remote computer or - MySQL is running under Windows. Whereas kill has the advantage that you don't have to - have a database account handy or - know about mysqladmin (think admin vs. DBA: every admin knows kill and can expect that any reasonable program written to run as daemon will react gracefully on kill) Isn't the proper way to issue a mysqladmin shutdown? As you see, both kill and mysqladmin shutdown have their advantages. So RedHat probably choses the way they do because they want to avoid the hassle of assuring to have a database account with shutdown privilege and password somewhere. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: smallest dumpfiles in Windows in one step?
On Mon 2003-01-27 at 21:45:54 -0500, [EMAIL PROTECTED] wrote: Stefan -- ...and then Stefan Hinz, iConnect (Berlin) said... % ... % backing up my MySQL databases on a Win2K box at home to a Linux box in ... % hand, I have only 128 MBit upstream from my Win machine, and maybe you % will have a faster network connection. I use: % % c:\mysql\bin mysqldump --all-databases --add-drop-table mysql -h % myoffice -u username -p is wrong, of course, because it will try to direct the output into a file named mysql. | is what is used to pipe between programs. % This will take some time, and it's most probably not the smartest thing % one could do, but it works nicely. Ouch! You're mostly, or perhaps even only, dealing with simple text, so why not take a moment to compress it before you send it over your tiny drinking straw of a 'net pipeline... mysqldump ... | gzip -9 | ssh myoffice gunzip | mysql -u username -p pass That wouldn't work, would it? I am quite sure the pipes are all interpreted by your local shell, so that would effectively mean to send the data via ssh to the remote machine for unzipping and then processing the output that got sent back via a local mysql. ;) Surely you have the cygwin toolkit and ssh for Win, right? And you could use build-in compression of ssh. So presuming a working ssh on the Microsoft Windows machine, something like this hopefully works: mysqldump ... | ssh -C myoffice mysql -u username -p pass % HTH, Ditto :-) ... :-) Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to speed things up in MySQL ?
Hi. On Sun 2003-01-26 at 00:26:32 +0100, [EMAIL PROTECTED] wrote: Here is the table definition as requested. I'm sorry I could take a full dump... Each record consist of approx 600 KB (just as in the Paradox table) so the file would be very big. (200 MB). Well, you should have told us that to begin with. ;) 600KB/record is not that usual. You are querying for ProductionYear but have no usuable index for it. That results in a full table scan, which has to read 200MB from disk, which is not unreasonble to take 13 secs, depending on your hardware. Btw, this also means that you can disregard my comment from the other mail about disk usage. I really did not expect your rows being so large without you mentioning it. Don't know why Paradox is faster. Maybe it can make partial use of one of the combined indexes which contain ProductionYear. Anyhow, creating an index on ProductionYear will do the job, so that the query runs fast. I am not sure if referring to ID in count(ID) is a problem. If so, replace it by count(*) additionally. If you often have to query on non-indexed fields, the usual solution is to split up the table into two: one containing the fields on which you want to query, the other the fields which you only query by primary key (probably most blob fields). And use a join if you want some blob depending on one of the query fields. This way the table size to read for full table scans will be much smaller. Btw, this is not MySQL-specific, but true for any database. It would also get the times with Paradox in the sub-second range. HTH, Benjamin. PS: Why don't you use a (small)int for ProductionYear? CREATE TABLE dvd ( ID int(11) NOT NULL auto_increment, Title varchar(100) default NULL, ProductionYear varchar(4) default NULL, [...] Starring blob, SoundTracks blob, Subtitles blob, SpecialFeatures blob, Comments blob, PlotOutline blob, FullCredits longblob, Cover longblob, [...] PRIMARY KEY (ID), KEY Added (Added), KEY Title (Title,ProductionYear), KEY NorwegianTitle (NorwegianTitle,ProductionYear) ) TYPE=MyISAM; * END [...] mysql select count(id) as Films, ProductionYear from DVD - group by ProductionYear - order by ProductionYear desc; [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to speed things up in MySQL ?
Hi. On Sun 2003-01-26 at 15:22:06 +0100, [EMAIL PROTECTED] wrote: Benjamin Pflugmann [EMAIL PROTECTED] wrote: [...] Why does MySQL needs to scan the Entire Table (all 200 MB) when I only have selected ID ProductionYear in the SELECT statement ?? Because you have a table with variable-width records and there is no way to easily find out where a field of a record is stored without reading all. Paradox BDE only scans/read the selected fields. (otherwise Paradox would use the same amount of time of course). Well, I suggest to stop thinking about low-level properties in the Paradox way. That Paradox can do such scans is a side-effect or their low-level design decisions. Those are almost always trade-offs. I am sure there other use cases where MySQL is faster than Paradox due to the same design decisions. I presume they store blobs in their own tablespace/segment and therefore can treat the rest of the table as fixed length for the purpose of this discussion. It's obvious that this will be slower, when you access these blobs. [...] I added another Index KEY (ProductionYear). Well... It gave me 3 seconds... so still the commands takes more than 10 seconds to complete. That's still much too slow. The query you posted last time, should only use the index file to be resolved. Can you post the result of EXPLAIN for the query? I tested this on Paradox as well... I removed ALL index and tried again... still the same greate speed even without indexes. and I think - this becasue it doesn't need to read/scan all the fields not specified in the SELECT statement. Sounds true. I guess it would be too much to ask the MySQL development team to re-organize MySQL on a file-level *lol* But I think the problem here is that the table consist of 1 big file that way MySQL must to read the entire record so the file pointer is at the beginning of the next record and ready to read it. Correct. In Paradox all Non-Fixed-Length fields such as Blob fields etc. are stored in a separate file... while the primary file only contains pointers to the Dynamic file if you like Ah. As I thought. If that is for all non-fixed-length fields, then Title (VARCHAR) should be such a field and selecting records based on Title is slow on Paradox, too? Or do they waste space by treating it like a fixed CHAR(100) field? So taking a full scan doesn't require it to read/scan through Blob fields unless they are specified within the SELECT statement. Well, as I said last time, the way to archieve full speed with other database engines is to split the table into two, based on size and on whether the field will be used in the WHERE clause. In your case it should be enough to move the biggest fields to a seperate table. Anyhow, creating an index on ProductionYear will do the job, so that the query runs fast. I am not sure if referring to ID in count(ID) is a problem. If so, replace it by count(*) additionally. Tested this... didn't make any difference (even on MySQL nor Paradox). It should. There is something else wrong. Regards, Benjamin. If you often have to query on non-indexed fields, the usual solution is to split up the table into two: one containing the fields on which you want to query, the other the fields which you only query by primary key (probably most blob fields). And use a join if you want some blob depending on one of the query fields. This way the table size to read for full table scans will be much smaller. Btw, this is not MySQL-specific, but true for any database. It would also get the times with Paradox in the sub-second range. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to speed things up in MySQL ?
Hi. On Sat 2003-01-25 at 13:36:01 +0100, [EMAIL PROTECTED] wrote: [...] mysql select count(id) as Films, ProductionYear from DVD - group by ProductionYear - order by ProductionYear desc; [...] 39 rows in set (13.32 sec) mysql *** How is this possible ?? 13.32 seconds ??? Good question. I do not know the answer, but I can tell you that there is something seriously screwed up. It should only take a fraction of a second. When I run a similar query on my database, it displays 0.00 sec, i.e. it took less than 0.01 secs. The same query on a database with 600.000 records takes about 4.7 secs. And this was when I was directly logged on to the Database (via Localhost)! When Using my Paradox table this took 1.5 second through my CGI script over the Intranet. [...] What can I do to speed this up ? As I said, I am not sure. This is not a question of optimizing your database or queries. Your installation of MySQL does not behave normally. Usually, there is no way any operation on 320 records would take more than at most 0.1 seconds or such. PS: The DVD table containing the 320 records is the only table on the server. (except from the MySQL database and the empy Test Database). Both the Paradox Table and the MySQL server is installed on the same Harddrive so harddisk speed shouldn't be a factor. Harddrive speed shouldn't be a factor at all, as 320 records need so few memory that they usually are kept in memory after first access. Appreciate any help to solve the above :-))) Just to be sure to exclude anything I might not have thought of, can you post the result of EXPLAIN for the select above SHOW CREATE TABLE DVD SHOW INDEX FROM DVD SHOW TABLE STATUS LIKE 'DVD' Is there anything running on the server while you do your tests? Regards, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to combine two selects into one ???
On Sat 2003-01-25 at 14:11:06 +0100, [EMAIL PROTECTED] wrote:
[...]
What I want is the combined output of selects like these (example with 2
users) :
SELECT timecode,
sum(cpuusage) AS jfn
FROM process
WHERE systemid = '2'
AND username = 'jfn'
AND timecode now() - INTERVAL 1 DAY
GROUP BY timecode
ORDER BY timecode
SELECT timecode,
sum(cpuusage) AS root
FROM process
WHERE systemid = '2'
AND username = 'root'
AND timecode now() - INTERVAL 1 DAY
GROUP BY timecode
ORDER BY timecode
With combined I mean that I want it in the form timecode, jfn, root.
How do I rewrite this into one select, and can it be done without having
to use features only present in v4, or features that require write
access to the database ???
If I understand you correcty, the following should do what you
want (untested):
SELECT timecode,
SUM(IF(username='jfn',cpuusage,0)),
SUM(IF(username='root',cpuusage,0))
FROM process
WHEREsystemid = 2 AND
timecode now() - INTERVAL 1 DAY AND
username in ('jfn', 'root')
GROUP BY timecode
ORDER BY timecode
Note that it will be somewhat slower than two seperate queries, I
think.
For people who want to test/try the real database, there's a read only
web interface at http://statdb.dassic.com/sql.php
The 2 queries from the example that I want to combine into one produce
useful data, so the question is just to merge them into one, since the
goal is to later, using another interface, ask a PHP script to draw a
graph with a top 5 of CPU usage among users. To do this I need a output
like this :
timecode, user1, user2, user3 etc.
For that application, I do not see why the output of something like
SELECT timecode, user, SUM(cpuusage)
FROM process
WHEREsystemid = 2 AND
timecode now() - INTERVAL 1 DAY AND
username in ('jfn', 'root')
GROUP BY timecode, user
ORDER BY timecode, user
is not enough. That is easily post-processed in the application.
HTH,
Benjamin.
--
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Segmentation fault when scanning mysqld port
On Sat 2003-01-25 at 14:16:35 +0100, [EMAIL PROTECTED] wrote: Hello, i just installed MySQL 3.23.55 on a SuSE Linux 8.1 system (compiled myself), and after firing up mysqld and starting a scan (via nmap; my server has no firewall) from another server, i get the following message: /usr/local/mysql/3.23.55/bin/safe_mysqld: line 280: 1757 Segmentation fault $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 Number of processes running now: 1 mysqld process hanging, pid 1760 - killed 030125 15:06:29 mysqld restarted Hope someone can say something to this. Try a pre-compiled binary? Without testing, I simply presume that such an obvious bug would not stay long. So your binary looks shaky. Okay, I tested against the older 3.23.49 and cannot reproduce this. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Need nulls in my join
Hi. On Sat 2003-01-25 at 14:25:14 -0400, [EMAIL PROTECTED] wrote: On Fri, 2003-01-24 at 20:27, Hoffman, Geoffrey wrote: [...] It's returning only stories in a section that have photos, but I need it to return all the stories in a section whether it has a photo or not. I think the problem is in the WHERE evtphoto.phtusage = 1 -- if there's no photo, then there's no phtusage. [...] You simply have to move that condition from the WHERE clause to the appropriate ON clause (and drop the part you put in to handle the case without photo): SELECT evtstorysection.secid, [...] FROM evtstory LEFT JOIN evtlayoutsum ON evtstory.slytid = evtlayoutsum.lytid LEFT JOIN evtstorysection ON evtstory.styid = evtstorysection.styid LEFT JOIN evtstoryorderON evtstory.styid = evtstoryorder.styid LEFT JOIN evtphoto ON evtstory.styid = evtphoto.styid AND evtphoto.phtusage = 1 WHERE evtstorysection.secid = 3 AND evtstoryorder.secid = 3 AND evtstoryorder.stodate = '2003-01-24' AND evtstory.stypubdate = '2003-01-24' AND evtstory.styexpdate '2003-01-24' ORDER BY evtstoryorder.stoorder HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is this possible?
On Fri 2003-01-24 at 07:59:16 -0600, [EMAIL PROTECTED] wrote: sub-selects are not yet (to the best of my knowledge) supported in MySQL. To be a bit more precise: They are not supported in any stable release of MySQL. They are going to be supported in Version 4.1, which is still declared alpha. Most basic and not-so-basic tests already work fine, AFAICS. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: NOT NULL question
Hi. On Wed 2003-01-22 at 23:07:24 -0800, [EMAIL PROTECTED] wrote: I'm really just currious as to WHAT you would want to see as opposed to NULL? Well, you asking the wrong guy, because I did not need that feature, but I'll try to explain anyhow. They want to see an error instead. It is the same why people use foreign keys and constraints: They want to enforce that only data which complies which certain rules enters the database. And before you are asking: Yes, there are situations where you know that an unknown value (NULL) is neither needed nor wanted for a column. How could you have a field that has no value? What would it mean? As I said, they want an error instead. If you don't know a value for this field, they want that you are not allowed to insert/update that row. NULL is the answer to this. It is recording the absence of something. So, I would say that this is an expected behaviour of any database engine. I see which point you are making and you are correct about it but you are missing their requirements. Bye, Benjamin. [...] On Tue 2003-01-14 at 09:32:02 -0800, [EMAIL PROTECTED] wrote: I'm aware that NULL and are not the same thing.. I would like to prevent the column from accepting values automatically ( with out the presence of a DEFAULT). [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table joins are slow things to deal with. . .
Hello. On Wed 2003-01-22 at 09:13:20 +0100, [EMAIL PROTECTED] wrote: Steve, ([Defendant] Query WITH a join - 8.79 seconds! EXPLAIN SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%); First thing that comes into mind is: You said you indexed the join fields (i.e. CaseNumber), but what about Defendants.Defendant? Okay, you did. Hmm. Next thing would be to check if the appropriate keys were used (CaseNumber, Defendant). Yes, they were used. Next thing would be to check if the optimizer chose a good join type: | Defendants | range | CaseNumber,Defendant | Defendant | 30 | | Cases | eq_ref | CasesNumber,Filed| CasesNumber | 30 | The join types are range and eq_ref, that's fine. The number of examined rows in Defendants seems okay, too: rows | Extra | 82756 | where used | Good check list. :-) Two things I noticed: Using a 30-byte wide index (CaseNumber) is not fastest. Probably it would help to create an additional INT AUTO_INCREMENT with key and join via that. That reduces the amount of data to be read from disk for the index by the factor 4.25 ((30+4)/(4+4)), and also cuts down the time for comparisions and improves relative effictivness of the key cache. Whether this the main reason for the slowness, I don't know, but it cannot hurt to try. Another thing that I would try is to to create a combined index on (Filed,CaseNumber) and (CaseNumber,Filed). Drop the one that does not get used afterwards. This should enable MySQL to resolve the join only using the index: currently it has to load the data records in order to check whether Filed is in range (i.e. it has to load 34,000 records although only 10,500 are returned afterwards that is about 24,000 seeks and reads without need). An index on (Defendend,CaseNumber) will also help. Of course, if you introduce an id (the INT) column as suggested above, use that instead of CaseNumber in the combined indexes. And at last, be sure to run OPTIMIZE and ANALYZE on the tables (of, course, you can also start with that :-). Please check speed after each change and report back. I am very interested to hear whether any of the suggestion had real effect in your case (the EXPLAIN for each would also be nice). Btw, what size is your key_cache? And how many reads/misses does it have? HTH, Benjamin. Okay, I leave this one to the gurus :-/ Not sure that I'd call me like this, but I chose to add my 2 cents anyhow. ;) [...] Maybe I'm dumb for saying this, but sql joins seems expensive to do in terms of performance (yes, I indexed the joined fields). If I do a query search of a 2,600,000 record defendant table WITHOUT a join (SELECT DISTINCT CaseNumber FROM Defendants WHERE Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%;). Performance is generally zippy at .53 seconds (which ends up pulling about 34,000 rows). HOWEVER, once I join this table with the much smaller [Cases] table (about 140,000 rows), performance plummets to 8.79 seconds! Quite a drop! The SQL statement is: 'SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%);'. I get about 10,500 rows returned here, but man, what an increase in time. [...] ([Defendant] Query WITHOUT a join) - .53 seconds. EXPLAIN SELECT DISTINCT CaseNumber FROM Defendants WHERE Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%; ++---+---+---+-+--+- --+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++---+---+---+-+--+- --+-+ | Defendants | range | Defendant | Defendant | 30 | NULL | 82756 | where used; Using temporary | ++---+---+---+-+--+- --+-+ ([Defendant] Query WITH a join - 8.79 seconds! EXPLAIN SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%); +++--+-+-+-- -+---++ | table | type | possible_keys| key | key_len | ref | rows | Extra | +++--+-+-+-- -+---++ | Defendants | range | CaseNumber,Defendant | Defendant | 30 | NULL
Re: Which is the difference?
Hello. On Tue 2003-01-21 at 18:52:06 +0200, [EMAIL PROTECTED] wrote: Hello all, I've tried the following sql queries: mysql create table a(id int unsigned not null auto_increment primary key, name text); Query OK, 0 rows affected (0.01 sec) mysql insert into a values(null, 'one'), (null, 'two'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql select last_insert_id() from a; +--+ | last_insert_id() | +--+ |1 | |1 | +--+ 2 rows in set (0.01 sec) Well, that doesn't really make sense, because LAST_INSERT_ID() is not bound to rows like this. Just use SELECT LAST_INSERT_ID(); without any reference to a table. What you did is like SELECT SIN(5) FROM a; i.e. it will simply return the constant value for each row found. #I've tried a second time: mysql select last_insert_id() from a; The result of LAST_INSERT_ID() does not change from selects (except for the ODBC case below). #I've tried to put a limit clause to see the last inserted ID only once: mysql select last_insert_id() from a limit 1; That's the wrong way to query LAST_INSERT_ID(). See above. #Now I've tried to find the last inserted ID by using where id is null but ... mysql select id from a where id is null; id IS NULL is supported for compatibility with ODBC. Don't use it in your programs, except if you have to. #The first trial was successfully, but the second not: mysql select id from a where id is null; Empty set (0.01 sec) #And from this point on, I get only empty responses. Please tell me why. I presume that is the behaviour ODBC expects. Since it's mainly for ODBC compatibility, don't wonder about it. It would have been made to return random results if that was what ODBC expected. So your question should rather be: why does ODBC expect it this way. I don't know (and I don't care). And BTW, if I insert more records in a single query, how can I find the real last one? Is the only solution counting the number of new entered records, and adding this number to the number returned by the last_insert_id() function? Yes. As http://www.mysql.com/doc/en/Miscellaneous_functions.html explains, LAST_INSERT_ID() returns the first inserted row to make it easier to reproduce the same insert. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Which is the difference?
Hello. On Wed 2003-01-22 at 08:53:23 +0100, [EMAIL PROTECTED] wrote: seems like LAST_INSERT_ID() will not always return the correct value. If you use ANSI-SQL INSERT, the function works fine. If you use MySQL extended INSERT (i.e. with more than one record per insert statement), the function will return the ID of the _first_ record inserted with an extended INSERT. Yes, that is the documented behaviour: http://www.mysql.com/doc/en/Miscellaneous_functions.html Regards, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: New to MySQL/PHP with Rank-Amateur Questions
Hello. On Mon 2003-01-20 at 09:26:18 -0500, [EMAIL PROTECTED] wrote: I'm brand-new to MySQL/PHP and to this list, spending an interesting holiday weekend devoted to trying to start learning the MySQL/PHP/Apached axis. There're three things that had me thinking of just giving it all up. But after a night's sleep I can't deny still wanting to learn how to design and lay out database-driven websites. But three problems prevent me from getting into the meat of some really great tutorials I found ... 1) I have administrator rights on the G3 PowerBook on which I'm doing this learning. But that's not the same as MySQL recognizing me with administrator acces, is it? Correct. Operating system users have nothing in common with users known to MySQL. The default administrator account for MySQL is called root and has an empty password in the default install, usually. How can I make MySQL give me administrator access, so I can have permission to issue CREATE [and ALL other] commands. As it is now, when I try to create a table from the Terminal window--and I've decided I DO want to be able to do this in the Terminal also, not just thru a GUI--an error message appears, saying that access is denied. As other already pointed out, you have to tell MySQL which user you want to log in as. If you don't do so, it will try to use the name with which you authenticated to your OS, which is not what you want in this case. Use somthing like mysqladmin -uroot create your_database 2) If I create a table in phpMyAdmin, how do I plant it in, say, Dreamweaver--or, really, code it into the HTML Dreamweaver creates--so the table can be accessed, as part of a web page, from a browser? You need some in-the-middle application that does the work. HTML is a language to describe pages, not a programming language. MySQL is a database, it knows nothing about webpages. You want to use a programming language to read the data from MySQL and create the HTML from the data you just read. Some well known are Coldfusion, ASP, PHP, and so on. Or you can use a general purpose programming language like Perl, C++, Java. If you have no preference already, I suggest using PHP. 3) I got to the end of November 2002 Macworld magazine's Serve It Up article, to the sidebar A Jump Start. I downloaded MacUser's .sit file for the 'start application' mentioned, unpacked it all, and put its folder in my SItes folder. I called it up thru my browse--using http://localhost/stephent/contacts/index.php--and was presented with a login page. I put in the username MySQL knows me by and a box drops down with 'Select a username' and three options: 'root', the one I'd put in, and a third name I use in my email address stiano. Whichever I choose, the password I use with each of them, specified earlier in MySQL, is put in the second field on the login page. I press the 'Log in' button, and a Netscape alert appears. It says: The information you have entered is to be sent over an unencryoted connection and could easily be read by a third party. Are you sure you want to continue sending this information? and a third line where I can check off to be alerted any time I submit unencrypted info. Even if it might be obvious, just to be sure: This message has nothing to do with your problem, but will appear any time you fill in a formular. It is just a note to make you aware that the data transmission is kind of unsafe. Plus two buttons. If I choose the default 'Continue', the process begins again, the box dropping with the three choices of user name. If it simply appears again, it means either the username or the password you entered is invalid. The only way to get out of this maddening circle is to choose 'Cancel', which of course just leaves me sitting on the login page without being logged in, and with the sample database nowhere to be seen. I think the problem is that you try the authentication data for MySQL, but you are accessing a web page. It is rather unusual that a web page would require the password for the database directly, except for pages that are meant to work on the database layout (like phpMyAdmin). Have a look if the article you mentioned doesn't list some test account or something like that. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: is there a floating point type bigger than double?
Hi. On Mon 2003-01-20 at 15:05:46 -0700, [EMAIL PROTECTED] wrote: We need to store numbers that exceed the precision of the DOUBLE datatype. The mysql manual makes no mention of a size bigger than DOUBLE. Storing the number as a string is not preferable because of the extra space a character string would take up. One thought is to store two integers: the digits to the right and left of the decimal place. With INT that gives you 10 digits before and 10 after the decimal point. Note that double is considered to have a precision of 15-16 digits. But I would BIGINT instead and just define how to calculate down to the decimal point (either by dividing by 10^10 or 2^32). The question is: is there an easier way to do this that would take up less space than a DECIMAL column... No, the question is: what do you want to do with these numbers? :-) Do you want to run any operation than simply storing and retrieving these numbers? If not, you can simply use CHAR(16) or something like that and store the binary representation of the number. I presume that you have something like that... it wouldn't make much sense to store high precision number, if you use double within your application. If you want to use database functions like AVG() or SUM(), this does not work, of course. perhaps something like a long double column. No there is no native column type that directly stores such numbers. Have I exhausted the available options? I got the feeling that I am going to have to go with the int*2 solution but I decided I would check with the 'experts' first. For a more specific answer you need to tell us what those numbers are and what you are going to do with them (what operations you need to run on them). HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Quick question.
Hi. On Mon 2003-01-20 at 16:51:53 -0700, [EMAIL PROTECTED] wrote: Just a quick question. I am just starting to learn MySQL. I am on a windows XP pro computer but want to create scripts using php and cgi for all platforms. If I create a database using MySQL on windows, is the database transportable to Unix and Linux? Generally yes. There are some minor issues (like case sensitivity) which you should be aware of to create a truly portable database. But even if you ignore these issues it is easy enough (though maybe time consuming) to fix them later. Have a look at http://www.mysql.com/doc/en/Windows_vs_Unix.html which mentions all caveats, AFAICS. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: g++ and mySQL]
Hi.
On Thu 2003-01-16 at 13:54:14 +0200, [EMAIL PROTECTED] wrote:
Hi,
If i understand well you wish to concatenate different strings.
Are many options but the most handy solution is if you use sprintf.
Well, in C++ you would rather use stringstream, because it has better
type and bounds checking. Something like (untested):
#include sstream
[...]
std::ostringstream query;
query insert into table-name values (1,
' my_escape(aaa) ',
' my_escape(bbb) ',
' my_escape(ccc) ')
mysql_query( connection, query.str().c_str() );
(where my_escape is some function calling mysql_real_escape_string())
Or alternatively, use the mysqlcpp, the C++-API (which I am not so
fond of).
HTH,
Benjamin.
[...]
I am trying to connect mySQL thru g++. I could connect the db and
execute a query. But I want to get a value from key board and pass the
same inside the query.
ie,
This is the query I am passing.
mysql_query(connection,insert into table-name values
('1','aaa','bbb','ccc'));
But I want to read the values for aaa, bbb, ccc and pass into the
query. How can I do it. I am a just a beginner in C/C++
--
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: NOT NULL question
Hello. On Tue 2003-01-14 at 09:32:02 -0800, [EMAIL PROTECTED] wrote: I'm aware that NULL and are not the same thing.. I would like to prevent the column from accepting values automatically ( with out the presence of a DEFAULT). [...] The problem is: You have a DEFAULT, you just don't know it. ;-) Do a SHOW CREATE TABLE stuff, and you'll see what I mean. That is a known deficiency, as Eric told you: It's a known behaviour and is described in the MySQL manual: http://www.mysql.com/doc/en/Bugs.html You can change it if you compile MySQL server with -DDONT_USE_DEFAULT_FIELDS option. But in this case you can't use default values at all. http://www.mysql.com/doc/en/configure_options.html describes -DDONT_USE_DEFAULT_FIELDS. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MS SQL vs MySQL
Hi. As Jeremy said, most info can be found by reading the archives or the manual. On Tue 2003-01-14 at 11:41:16 +0200, [EMAIL PROTECTED] wrote: I've asked on another list which database they recommend among MySQL and MS SQL, and ... possibly PostgreSQL. Most of that list members answered me that they recommend MS SQL because it has much more features. The simple question is: do you need the features? If so, you should probably go with MS SQL or Oracle. If not, why should you (waste your money)? Can you tell me which are the most important differences between MS SQL and MySQL? I am interested in the differences in the following areas: - the speed All benchmark are lies (they show what they are supposed to measure, not what your requirements are). That said, you may want to have a look at e.g. http://www.mysql.com/information/benchmarks.html http://www.eweek.com/article2/0,3959,293,00.asp and MySQL AB's summary of the latter: http://www.mysql.com/eweek/index.html - the max size of a database, the max size of a table, etc. Database and table size are almost unlimited. (8 Million TB for tables), but practically limited by the underlying OS: http://www.mysql.com/doc/en/Table_size.html - the things that can be done in MySQL but can't be done in MS SQL I don't know about the MS SQL side, but here is some of the MySQL side: http://www.mysql.com/doc/en/Compatibility.html http://www.mysql.com/doc/en/Extensions_to_ANSI.html (particularly) Additional MySQL features are: - you have the source and can tweak behaviour, if you want or need - outstanding support on this list and by MySQL AB - quick turn-around time: although no guarantee, experience shows, if you happen to find a critical bug, chances are high that you have a patch the next day. - The things that can be done in MS SQL but not in MySQL Again the MySQL side of things: http://www.mysql.com/doc/en/Differences_from_ANSI.html - How easy is to access a database from Perl I don't know about MS SQL, but I presume it can be accessed via DBI the same way as MySQL, so the answer is probably: it is the same for both. There are quite some interesting, more insightful posts in the list archive about that, but I am a bit too lazy to dig them up. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: --- How to query results of a query?
Hello. Please start a new thread instead of replying to an existing one. Or else, your message will be sorted with the original thread for people with decent mail readers. On Wed 2003-01-15 at 14:42:05 -0500, [EMAIL PROTECTED] wrote: How do you query the table that is the results of a query? By a sub-query. Since MySQL supports sub-queries only since version 4.1 (alpha), you have to work around this limitiation. The general answer can be found in the manual: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html Must you ask MySQL to... ...create a temporary table form the results of the first query ...then query that temporary table ...then delete the temp table when you are done? That is one possible solution (also mentioned in the manual page I cited). The third step optional if you use the TEMPORARY keyword with the table, because it will be deleted automatically when the conncetion is closed. (and if so how do you ask MySQL to create a temp table from the results of a query?) See http://www.mysql.com/doc/en/example-Maximum-column-group-row.html, which shows a work-around to a query which typically needs a sub-select. Is there a better and faster way to do this with minimum burden on the web server with the db on it? No. Btw, in the cases where you cannot rewrite a sub-select into a join, most often an RDBMS will so the equivalent of a temporary table. So there is not much loss, except for the additional transfer and parsing of the queries. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select the next name
Hi. On Tue 2003-01-14 at 06:02:10 +, [EMAIL PROTECTED] wrote: SELECT id,first,last FROM names ORDER BY id LIMIT 2,1; Work? Limits to one result, start at second offset. (I may have the 2,1 in the wrong order though) Ok my gut would say that this would not give the result I want unless the id's are sorted by last name, and given inserts and such I can see that would not be the case. But I sense the glimmer of an approach in this query... The problem is that you want to retrieve a row depended on the order of name and that tables in SQL are (unsorted) sets. ORDER BY applies only after the result set has been determined and cannot be used directly to influence what the result will be (only how it looks like). One solution could be something like SELECT id, first, last FROM names WHERE last '$previouslast' AND first 'previousfirst' ORDER BY last, first LIMIT 1 That is: Take all results that come after the previous name, sort them and then take only the first. The problem with it is that it only works fine as long as (last,first) is unique. If not, you will end up looping on the same (last,first) pair forever. One way to solve is to pass a 'skip' value, which tells you how often you already encountered this (last,first) pair and skip that many rows. You would start with skip=0 and reset skip to 1 whenever you encounter a different (last,first) pair. In this case you would use something like ... LIMIT $skip, 1 HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select the next name
On Tue 2003-01-14 at 09:22:40 -0500, [EMAIL PROTECTED] wrote:
One solution could be something like
SELECT id, first, last
FROM names
WHERE last '$previouslast' AND first 'previousfirst'
ORDER BY last, first
LIMIT 1
[...]
I don't think it would loop forever, since you search by '', not
'=', so any identical names would be skipped. Either way, though,
last,first, must be unique.
Correct. Same underlying problem, but wrong description (by me).
Indeed I meant =, although my description was not correct for that
either: I meant =, but wrote and so some thoughts to both made
it into my post. Thanks for pointing this out.
A fix to this might be to order by id after this, since id is
unique
SELECT id, first, last
FROM names
WHERE last 'previouslast' AND first 'previousfirst' AND
id'$previousid'
ORDER BY last, first, id
LIMIT 1
Thus sorting through any identical names as well. (How many Bob Smiths can
there be?)
That was one of the ideas I pondered about originally, but that assumes
that id has a relation to the alphabetical order, which not the case
normally, i.e. assume (sorted by (last,first,id)):
id last first
10 Bar Matt
1 Foo Tom
You'll first get Bar, Matt (10) and then try to select the next
record. But the condition id'$previousid' prevents you from getting
Foo, Tom (1). You see?
In principle that is exactly the problem you try to solve below,
extended to id.
However, this brings out a problem in using AND to combine the elements. by
saying WHERE last '$previouslast' AND first 'previousfirst'
, we're falsly stating that the first name _must_ increase along with the
last name, where in reality the first name may decrease, so long as the last
name increases. Follow?
Yes. Another mistake of mine. I shouldn't answer mail when I am in a
hurry. I am sorry about the confusion. The usual condition clause for
such a requirement is of course:
last 'previouslast' OR ( last='$prevlast' AND first '$prevfirst' )
This does not take duplicates into account yet and doesn't scale well
with (all versions of) MySQL due to the OR clause.
[...]
So, whats the best way to rewrite this? Perhaps a Concat?
SELECT id, first, last
FROM names
WHERE CONCAT(last,first,id)CONCAT('$previouslast', '$previousfirst',
'$previousid')
ORDER BY last, first, id
LIMIT 1
I think that would work, feel free to correct me!
Nice idea, but it has several problems. id is usually a number and
alphanumerical comparisons don't work well with them:
FooTom9 FooTom100
would be true, which is not what we want. Another point is that
CONCAT() on the column part prevents using of indexes. And simply
concatenating strings for sorting could have unwanted side-effects.
Unprobable, but possible. This should work better:
CONCAT(last,'|',first,'|',LPAD(id,11,'0'))
CONCAT('$prevlast|$prevfirst|',LPAD($previd,11,'0')
It solves at least the most problematic parts reasonbly (but still
cannot use indexes). A similar alternative, which uses the seperate OR
suggested above, could be
CONCAT(last,'|',first) '$prevlast|$prevfirst') OR
( CONCAT(last,'|',first) = '$prevlast|$prevfirst' AND id$previd )
In order to use have a chance to use an index I suggest using
something like
SELECT id, last, first
FROM names
WHERE last '$prevlast' OR
(last = '$prevlast' AND first '$prevfirst') OR
(last = '$prevlast' AND first = '$prevfirst' AND id $previd)
ORDER BY last, first, id
LIMIT 1
which is almost what I had in mind originally (using a $skip variable
instead of $previd). One can hope that MySQL notices that it can use
an single index, because all OR parts contain the same column, 'last'.
Well, to get away a bit from the complex discussion: With such a need
it is often easier to do part of the processing in an application.
Therefore start with querying the list of id's sorted as one needs
(supposing the list will not get too big)
SELECT id FROM names ORDER BY last, first, id
and pick the id which comes after $previd in the result set and now
query for the new row by id:
SELECT id, last, first FROM names WHERE id=$pickedid
The same could be archieved with a TEMPORARY table (which doesn't make
sense - the SQL solution above - except if the order involves even
more columns or a more complicated expression):
CREATE TEMPORARY TABLE picknext
(num INT AUTO_INCREMENT PRIMARY KEY, id INT, UNIQUE(id))
SELECT NULL, id FROM names ORDER BY last, first, id
[that is one statement!]
SELECT id, last, first
FROM names
WHERE id IN (
SELECT id FROM picknext WHERE num IN (
SELECT num+1 FROM picknext WHERE id=$previd))
Or without sub-select:
SELECT n.id, n.last, n.first
FROM picknext p
INNER JOIN picknext tmp ON tmp.num = p.num+1
INNER JOIN names n ON n.id = tmp.id
WHERE p.id = $previd
If $previd is not known, the command
Re: Deleting from one table blocks other tables?
Hi. On Sun 2003-01-12 at 22:01:37 -0500, [EMAIL PROTECTED] wrote: On Sun, Jan 12, 2003 at 08:12:35PM -0700, Rodney Broom wrote: I'm trying to delete 5 million rows... [...] If I was deleting things regularly, I'd have to delete maybe a couple hundred thousand rows every day. [...] When I say blocked, I mean e.g. another process tries to SELECT from another table in the database, but it takes way too long. If you are absolutely sure that the other queries don't related to the deleting query at all, it means they are slow, because your disks are too stressed. Any suggestions on how I can delete those rows without causing a lot of downtime? As http://www.mysql.com/doc/en/DELETE.html suggests, you can use LIMIT with DELETE in order to restrict the time a DELETE needs by running it in batches. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: What's the point of SET fields?
Hello. On Sun 2003-01-12 at 11:18:08 +, [EMAIL PROTECTED] wrote: I've setup a mysql table field which is a SET type, but it seems to be completely pointless. I can't see why I should use a SET type rather than a string for the following reasons: 1. I appear to be able to store whatever I want in there. MySQL doesn't enforce the rule that what I stick in there must be items from the set I defined when I defined my field. Correct. In general MySQL doesn't enforce field types for any value. But if you choose a completely invalid value, MySQL will store an marker (here: empty string) instead, e.g. it's similar for the date type. The reasoning for this behaviour is mainly speed. But you oversee an important (depending on use case) fact: SETs are saved as bit-fields, and therefore use much less disk space than the equivalent as string. 2. What I do a SELECT statement the only way I can interrogate records to determine if a certain set value is present is to do a WHERE MySet LIKE '%MySetMember%' which is fairly useless if set items contain other set items in their name (ie if I have set items A,AB,ABC then obviously my LIKE query searching for an A will have issues). Or am I missing something? Yes. The function FIND_IN_SET(). It solves the problems you describe and additionally is a lot faster for the SET type because it uses bit fields instead of strings comparisons. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Maintaining a UNIQUE INDEX in a MERGED Table
Hello. On Wed 2003-01-01 at 15:38:23 -0500, [EMAIL PROTECTED] wrote: If I am reading the documentation correctly, MySQL will not maintain a UNIQUE INDEX across the tables that make-up a merged table. Correct. Although it is called a UNIQUE index, the MERGE table doesn't hold it's own index and uniqueness is only guaranteed on sub-table basis (presumed you have the corresponding UNIQUE index on the sub-tables). Does anyone have any tips for maintaining a UNIQUE INDEX across tables that make up a merged table? No. You have to check uniqueness yourself. Or use a procedure that assures you uniqueness, if possible. Is there an easier way than running a SELECT statement on all of the sub tables and then inserting the item if all tables return 0 found? Do the SELECT on the MERGE table instead? HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ensuring an instructor, client, or room isn't double-booked
David, On Tue 2002-12-31 at 06:47:38 -0500, [EMAIL PROTECTED] wrote: [...] Oh, I see... I've been thinking about that, too; I currently use a datetime field, but all I really need is a date and an hour. I figure with one combined field I can match against -YY-ZZ% but maybe two fields would make it easier to pull out the time column... well, using one field looks fine to me. Both approaches have their advantages and disadvantages and unless you see a particular problem (sounds familiar? ;-), just go with the simpler solution (one column). [...] Now I presume that I will want a unique index for (instructor,timeslot) and a[nother] unique index for (client,timeslot). I can't do a unique index on the place because some rooms are booked for groups (I'm planning to create 12 records for a 12-seat class when it's defined, and then I'll update those records until there are no more with empty client values as I add students). Do I want two unique indexes like that[...]? Correct. [...] You may. If you know anyone who wants to buy it for me for a belated Christmas present, you can suggest that, too :-) :-) [...] % http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:77486 % http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:115591 Thanks; I'll read them next. [Actually I just popped out and read them.] Oh, I get it; an index is a fast BTree pointer to the data; it's a hash table! Cool. Yes, that's what I want, all right. Just a minor clarification: it's *like* a hash table in its *purpose*. Actually, MySQL even supports real hashes for in-memory tables (TYPE=HEAP), so I thought it's worth pointing out. [...] Yeah. I won't have that few; I'll have about 80 per week (6a thru 7p on six days) for every week from now until the end of time (hey, I can hope that I'll be making money from this software for that long!). So in the beginning I'll have fewer time slots, but by the end of the first year that will very probably have flipped... Good grief; what do you do in that case? Depends (familiar, isn't it? ;-). From what I heard until yet, I assume that MySQL will easily handle your database, so having a sub-perfect index should be fine. So go long-term, since with fewer rows the index doesn't matter that much. Well, I suppose it's an opportunity to charge for an annual tuneup ;-) [...] Of course. ;-) If it is anything like the projects I know, you will correcting your application to client's wishes long before a year has passed. HTH and a HNY ;) Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ensuring an instructor, client, or room isn't double-booked
Hi David. On Sun 2002-12-29 at 16:21:56 -0500, [EMAIL PROTECTED] wrote: ...and then Benjamin Pflugmann said... [...] No; everything is one hour. Two hours is two bookings. Ah. Good. That makes the case easier and is exactly the case my last answer was about. [...] % know beforehand when the class starting at x o'clock will end? And % saving a number for the time slot would also work? Saving a number? I don't know what you mean here... Not important anymore. But for completeness: You could do as with school classes. 9-10 o'clock is 1st, 10-11 is 2nd and so on. So you could theoretically save date 2003-02-10 class 1 instead of date 2003-02-10 time 09:00:00. Not that my suggestion would be better necessarily, it just served to make it less ambiguous what we are talking about. Seemed it failed. ;) [...] % In the case of time slots, you could create a unique index on % (client,time) and (instr,time) and skip the selects. Simply insert and % check for an duplicate key error. Hmmm... OK; avoiding duplicates is the end goal and so I should probably learn more about this apparently simple approach. Yes, the main purpose of UNIQUE indexes is to guarantee uniqueness in a column (or a set of columns). So that's the way to go. Um, what next? :-) :-) This is probably in the mysql doc, so a pointer would be appreciated but know that I will [try to] look up multi-column indexes and see how they deal with duplicates. http://www.mysql.com/doc/en/Multiple-column_indexes.html Although the manual covers a lot of stuff which is not strictly MySQL-related, it is not well suited to be an introduction into the world of relational databases. May I suggest Paul DuBois' book? It provides a lot of insight in such issues. [...] I figured I'd have to check every record to see if the datetime and client (or instructor) matched what I have already, and a few years from now when that table is long it seems like that would take a while (seconds? minutes? i dunno) to query... Maybe not at all on such a simple table (very short fixed-length fields, and not very many of them), and maybe not with the indexes (hmmm... indices?) you mention above. Well, that is what indexes are all about. Searching for 1 record in 1 billion with indexes can be as fast as a full table scan for 1 in 30 records. A more complex answer from some ealier posts of mine (still simplified): http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:77486 http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:115591 [...] % If not: Whether an index (client,time) or (time,client) is better % depends on your data (few or a lot of client, etc). If you don't have I don't know exactly, but we're probably looking at a total of 3k or 4k clients with some .5k actively making frequent bookings, and up to a dozen instructors (per installation, which for starters will be a little PII-266 with only 64M of RAM and a 5G IDE disk but will probably settle on a cheapo PIII-800 with 128M and a 10G IDE disk at each studio). Is that a few or a lot? It depends on how many time slots you have. The general answer is that you want the column with the highest cardinality first. But also the storage size matters and so on. That's what etc was about. ;-) Usually I use the trial and error method I mentioned below, myself. But sometimes it is so obvious that you don't have to. Like if you had only 40 time slots (only planning a week ;). The client should come first. % I you are not sure, simply build all of them and look which one get % used. Then dropped the other ones again. The same applies for instr. That's a safe approach that should get the questions answered :-) Jupp. [...] I don't even know; see my newbie comment above :-) I thought that a temporary table helps to speed up specific queries because the grouping is already done and then you just select from that, but I dunno :-) That is correct. *If* you need grouping. And then, as you said, only for some specific queries. But you don't (need grouping). You are right, that you have kind of groups in the table. But the term grouping is about when you want to retrieve some fact based on groups. E.g. if you want to know the next class each client attends, similar to this: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html But I think the overall answer is: Don't optimize early. You are right, that you need a clean design to get reasonable speed. I suggest you first care about that and think about specific optimizations (like using temporary tables) only stumble upon a specific bottleneck. It sounds to me as if you are worrying about non-issues currently. But, of course, a basic understanding of what is going on never hurts. HTH, and yes, HAND, too, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com
Re: altering data structure
Hi again. On Sun 2002-12-29 at 15:21:33 -0500, [EMAIL PROTECTED] wrote: ...and then Benjamin Pflugmann said... [...] % Of course, that's only possible if you can live with being % MySQL-specific. Hmmm... so it's a mysql thing and not a SQL thing. Well, I'd like to avoid that, even though I like mysql... Well, setting up a database (or updating it) is always very vendor-specific, isn't it? Besides, I have yet to see production-level SQL that works on several databases without change (except for stuff that is developed that way). The problem is that the SQL standard is too unspecific in some areas and the vendors have to fill in the details and it becomes a PITA to work without the vendor-specific features. IMHO, the most common example is the lack of sequence support. AUTO_INCREMENT is MySQL-specific. To be portable you have to simulate sequences yourself, which is possible, but you won't do except if you *know* you will need to run on a different RDBMS later. More info here: http://www.mysql.com/doc/en/Compatibility.html Thanks HAND HH Hehe. I wondered about the non-abbriviation in your first mail already. ;-) HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: indexing a blob
Hi. On Mon 2002-12-30 at 16:53:08 -0500, [EMAIL PROTECTED] wrote: [...] hashsum tinyblob not null , # hash of the card: have we seen this one? index (hashsum) # for quick lookups Whenever I try this with the index, I get ERROR 1170 at line 49: BLOB column 'hash' used in key specification without a key length From http://www.mysql.com/doc/en/CREATE_TABLE.html: * With col_name(length) syntax, you can specify an index that uses only a part of a CHAR or VARCHAR column. This can make the index file much smaller. See section 5.4.4 Column Indexes. * Only the MyISAM table type supports indexing on BLOB and TEXT columns. When putting an index on a BLOB or TEXT column you MUST always specify the length of the index: CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10))); I don't know where the key length needs to be specified; I tried hashsum tinyblob(255) not null That is the column definition, but it was about key length, so better look at the key definition: hashsum tinyblob not null , index (hashsum(255)) If you got confused by col_name(length) from the docu, it references: index_col_name: col_name [(length)] on the same page. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: An Idea
Hello. On Sun 2002-12-29 at 11:26:01 +0100, [EMAIL PROTECTED] wrote: Hello, I had a problem few days ago. I'm doing my questbook, and I were thinking what would hapen if I delete some row. Now I know, nothing. I had one column ID (auto_increment) in my table. I wanted it to be one by one even after deleting, so I changed it by myself. But then (after deleting the last ID was 17, and before 32), next ID was 33, not 18. Is there any function, which can change it? If not, mayby you'll try to do something like that. It's right, I can do it by myself not using auto_increment, and giving the ID number MAX(ID)+1, but if there is such function it would be realy fine. What you describe was the behaviour in older MySQL versions and it has been changed because primary keys should never be reused. Never. If you need it to have no holes, you are abusing the primary key for something which it is not intended for (visible entry numbering?). So, yes, you have to either implement it yourself, or, what I would recommend, have a seperate column for it or calculate it in your application, whatever makes most sense for your use. HTH, Benjamin. PS: AFAIK, InnoDB still has the old behaviour. Anyhow, it will change soon enough. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Storing a SHA1 checksum
On Sun 2002-12-29 at 05:28:57 -0500, [EMAIL PROTECTED] wrote: sql, table I'm storing a SHA1 checksum as varchar(20) binary in my application. After running a test, it seems MySQL will strip trailing spaces from a varchar column, even if it is binary! Yes, the BINARY keyword only influences how comparisons are done (mainly case-sensivity, but also umlauts, etc...). Stripping space from VARCHAR is a known deficiency: http://www.mysql.com/doc/en/Bugs.html It also mentions, that the TEXT/BLOB types are save from it. That means if the last character of my SHA1 checksum happens to be a space, MySQL will corrupt it. What should I do? It seems I can: 1. Use blob instead of varchar. Problem: blob type is slower. Is that really a problem? Did you measure it? If so, I would be intersted in the results. Advantage: Other application programmers do not need to be aware of the hack. After MySQL is fixed, the source doesn't contain redundant code. 2. Make my application pad the checksum out to 20 spaces. Problem: Increases my code complexity a bit. Advantage: Doesn't affect performance (noticeably). The DBA doesn't need to be aware of the hack. 3. Wait for MySQL to fix the strip trailing spaces bug. Problem: That doesn't provide an immediate solution. 4. Append a non-space at the end, and ignore it on retrieval Problem: Same as 2. Although 2. looks like the prettier solution, 4. makes easier to spot the problem, if the additional handling is forgotten in new code. Well, what you should do? It depends on what you need. It's a trade-off and no one except you can answer what your priorities are. If, for example, you have many applications / programmers who access this stuff, 1. is least intrusive. OTOH, if it is used only in one place, perhaps in a well-encapsulated object, 2. is the least intrusive change. And someone (that includes yourself in 1 year) looking at your SQL dump wouldn't know why you have chosen a BLOB, while you can have a neat comment in the source about it. Since any of the solutions involves only minor changes, I would not bother to waste time on the decision. Simply go with one and rewrite if it really turns out to become a problem later (which I don't believe). HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: altering data structure
On Sat 2002-12-28 at 05:05:45 -0500, [EMAIL PROTECTED] wrote: ...and then Benjamin Pflugmann said... [...] % If you choose to go the SQL route, have a look at TEMPORARY TABLES, % CREATE TABLE ... SELECT and INSERT ... SELECT. Don't forget that you You mean a CREATE TABLE command that has SELECT as part of it, and an INSERT command that has SELECT as part of it? Yes and no. Yes, it is a INSERT with SELECT. No, if I consider what you write below. I did not mean sub-selects or derived tables. Actually I wanted to know how to do the latter but didn't think it could be done! Briefly: create table ccards ( # ID number id smallint not null default 0 auto_increment primary key , ... hash tinyblob # hash of the card: have we seen this one before? ) ; create table clientcards ( # ID number id smallint not null default 0 auto_increment primary key , client smallint , # references client.id card smallint , # references ccards.id (but must be disconnected) type smallint , # references ccardtypes.id ### need this here? hash tinyblob # references ccards.hash (but must be disconnected) ) ; ... insert into ccards (type,name,number,expdate) values ( '1' , 'david thorburn-gundlach' , '1234 5678 9abc def0' , '2003-06-00') ; update ccards set hash = md5(number) where id = last_insert_id() ; insert into clientcards values ( '' , '1' , '1' , '1' , select ccards.hash where ccards.id = last_insert_id() ) ; That would be something like a derived table, which are only supported since v4.1 (not considering the fact that the FROM clause is missing :-). The INSERT ... SELECT which I referred to is a special syntax which is supported since quite a while (in v3.23 for sure): http://www.mysql.com/doc/en/INSERT_SELECT.html http://www.mysql.com/doc/en/ANSI_diff_SELECT_INTO_TABLE.html In your case it would be something like INSERT INTO clientcards SELECT '', 1, 1, 1, ccards.hash FROM ccard WHERE ccards.id = LAST_INSERT_ID() And you have variables. If the above wouldn't work you could write: SELECT @card_hash := hash FROM ccard WHERE ccards.id = LAST_INSERT_ID(); INSERT INTO clientcards VALUES ( '' , 1, 1, 1, @card_hash ); Of course, that's only possible if you can live with being MySQL-specific. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ensuring an instructor, client, or room isn't double-booked
Hi. On Sat 2002-12-28 at 08:15:58 -0500, [EMAIL PROTECTED] wrote: [...] create table schedule ( # ID number id smallint not null default 0 auto_increment primary key , client smallint , # references client.id class smallint , # references classtypes.id place smallint , # references places.id instr smallint , # references personnel.id time datetime # when Don't you need some kind of duration? Or are the times fixed and you know beforehand when the class starting at x o'clock will end? And saving a number for the time slot would also work? ) ; I could, for every insert, check select * from schedule where client = '1' and time = '...' ; to make sure the client isn't being booked twice, or select * from schedule where instr = '1' and time = '...' ; to make sure the instructor isn't double-booked, but In the case of time slots, you could create a unique index on (client,time) and (instr,time) and skip the selects. Simply insert and check for an duplicate key error. that seems like it would get awfully slow as the table grows... I wonder why you think so. Maybe I do not understand your concern correctly, but with the right indexes this shouldn't be slow. Do I miss something? If not: Whether an index (client,time) or (time,client) is better depends on your data (few or a lot of client, etc). If you don't have time slots, even (client) or (time) would help, although not as much. I you are not sure, simply build all of them and look which one get used. Then dropped the other ones again. The same applies for instr. Is this the sort of thing where a temporary table is advised, or should I define a reverse table that shows each instructor and his/her bookings, or what? I am not sure how a temporary table would improve things. So we have some kind of misunderstanding. Could you post how you would use temporary tables here? Then I could easier tell, how I would do the same. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL InnoDB
Hello.
On Sun 2002-12-29 at 21:40:22 +0300, [EMAIL PROTECTED] wrote:
tell me please where i need write my question about InnoDB in MySQL ?
This mailing list is fine for questions about InnoDB. But your
question is not a MySQL problem, but a misunderstanding about PHP and
Web pages in general.
[...]
But if i try use every Insert at defferent page (php)
Like
Page.php - has FORM ACTION=p1.php
p1.php - has
SET COMMIT;
INSERT 1
FORM ACTION=p2 ?
{go to p2.php}
INSERT 1
ROLLBACK;
- THIS IS NOT WORKED !
WHY ?
Because HTTP is a stateless protocol. After p1.php has been processed,
the connection to the MySQL server is closed (by PHP) and the
transaction rolled back automatically. p2.php gets *nothing* of the
state of p1.php, except for what you transfer as GET or POST
parameters (or Cookies).
It would make no difference if you stopped and restarted the Web
server in between your two page accesses. For more details, please
look up a PHP tutorial. I am sure they explain this behaviour.
HTH,
Benjamin.
PS: And no, persistent connections cannot be (mis-)used to get over
that restriction. They only solve a performance issue.
--
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Moving a database accross a platform
Hi. On Fri 2002-12-27 at 17:19:26 -0800, [EMAIL PROTECTED] wrote: I looked through the documentation, but I couldn't find anything relevant to this, so here goes... I have MySQL with databases running on a Solaris machine, and MySQL with different databases running on an Irix machine. I want to integrate some of the databases from the Solaris machine into the Irix machine (as the Solaris machine will no longer be used).. Is there any simple way to do this? Look up mysqldump and mysql in the manual. You want to do something like this: targethost$ mysqladmin create new_database targethost$ mysqldump -h oldhost some_database | mysql new_database HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: page translation
On Fri 2002-12-27 at 23:45:25 -0800, [EMAIL PROTECTED] wrote: for some reason i have a page in asp just showing code and no actual visual data. any ideas ? Ask in a Microsoft / ASP related forum? I do not see how your question has anything to do with MySQL, so it's off-topic here. This list gets more than enough traffic already. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4 billion record + 6 Gigabytes text fie
Hello. On Wed 2002-12-25 at 20:06:22 -0800, [EMAIL PROTECTED] wrote: My company is currently upgrade our company'database from PICK, an old database system to mysql.. Our company database got 1 table where the record is more than 40 million records, and the other table also got about i million record each.. what i found is i only can use the simple sql query to select the data from database and can't use the query like left join,equi-join and even a global variable also can make our server(2G RAM, 40G hardisk, Pentium 4) take a long time to respond..maybe this is a limitation of mysql i think..can't store such huge data.. No, 40 million records is no particular problem for MySQL. Most probably some indexes are missing. You need to be far more specific. Which version of MySQL do you use? (SELECT VERSION()) Which OS do you use? Which version? What is (one of) the slow query(ies)? What shows EXPLAIN for that query? What does a long time mean, in seconds? And so on. Provide anything else you think could matter. Please post the result of SHOW TABLE STATUS and SHOW CREATE TABLE for the relevant tables. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Hiding the password
Hello. On Thu 2002-12-26 at 09:26:09 -0500, [EMAIL PROTECTED] wrote: i would try using php to have you page connect to the mysql database.. The code gets parsed first then is loaded into the browser...so the user pass for the database is never seen.. i would use something like: $db = mysql_connect(localhost, mysql-user, mysql-user-password); mysql_select_db(whatever-database-name,$db); Huh? How does this differ from the original problem with Perl? The script has to be world-readable in order to allow the web server account to read it in[1] and therefore anyone with shell access or access to write CGI scripts can read it. Bye, Benjamin [1] in the scenary presented by the original poster. [...] On Wed 2002-12-25 at 13:15:58 +0200, [EMAIL PROTECTED] wrote: Hi all, I want to make a CGI program in Perl that queries a MySQL database, and the problem is that I need to write the password for the database in the program and this password can be seen by any user that has an account on that server. I need to gave 755 permissions to CGI scripts because they need to be executed by the web server account, and not by my account. Do you have any tips for hiding the password, Not really. Whereever you put it, the web server account has be able to access it, so the problem stays. Even if you could arrange that only the web server account can read it (e.g. by changing the owner of a file containing the password), every user with permission to create CGI scripts can still write a script to read the data. [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is this a MySQL error?
Hello. On Wed 2002-12-25 at 11:47:22 +0200, [EMAIL PROTECTED] wrote: Hi all, I gave the following command in the default MySQL client: select from_days(365); The result is -00-00 instead of 0001-00-00. If I gave the following command: select from_days(366); It gives the result 0001-01-01. If I give numbers less than 356, the result is 0 for the year, the month, and the day. Something's wrong I guess. Yes, you use FROM_DAYS() for unsupported values. The manual explains that it is not intended for dates before 1582. http://www.mysql.com/doc/en/Date_and_time_functions.html Additionally, it returns a DATE value, for which such low values are not supported, either, but only for 1000-01-01 and later. http://www.mysql.com/doc/en/DATETIME.html So the result for the value you tried is undefined, even if it looks sometimes as if you get some reasonable result. And even if the result for an unsupported value indeed would be correct, you are not guaranteed that a new MySQL version will return the same, because it is explicitly undefined and therefore considered an implementation specific random value. The latter page also explains why you get no error message, if this is what you wondered about: The MySQL server only performs basic checking on the validity of a date [...]. Please note that this still allows you to store invalid dates such as 2002-04-31. It allows web applications to store data from a form without further checking. To ensure a date is valid, perform a check in your application. It also suggests that you should get -00-00 for all years before 1000, which is obviously not the case. That is a minor documention glitch (see above, you should not rely on MySQL to validate ranges). HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Hiding the password
Hello. On Wed 2002-12-25 at 13:15:58 +0200, [EMAIL PROTECTED] wrote: Hi all, I want to make a CGI program in Perl that queries a MySQL database, and the problem is that I need to write the password for the database in the program and this password can be seen by any user that has an account on that server. I need to gave 755 permissions to CGI scripts because they need to be executed by the web server account, and not by my account. Do you have any tips for hiding the password, Not really. Whereever you put it, the web server account has be able to access it, so the problem stays. Even if you could arrange that only the web server account can read it (e.g. by changing the owner of a file containing the password), every user with permission to create CGI scripts can still write a script to read the data. or accessing MySQL from CGI scripts is not secure at all? Well, it is as secure as the server is set up. E.g. one can set up Apache so that it executes CGIs as the user to whom the script belongs. I know this has its own problems... it was only intended as example that it is a question of the server configuration. The best way is always a compromise and depends on how the server is used. If the server configuration is not in your hands, I don't there is much you can do, except asking the admin which way she suggests. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Update NOT NULL Field with NULL!
Hello. On Thu 2002-12-26 at 00:09:55 +0100, [EMAIL PROTECTED] wrote: Hi there, I'm using MySql 3.23.?? with Connector/J on Win32. My Problem is that the database lets me UPDATE NOT NULL Fields with NULL-Values. This is not a special Java Problem! For example: The Field Name in tbl_Names is declared NOT NULL. UPDATE tbl_Names SET Name = NULL WHERE ID = 23; ...works fine, but... INSERT INTO tbl_Names (Name) VALUES (NULL); ...throws an NOT NULL Exception!!! The Field Name is declared NOT NULL. This occurs during both, Java-Executes and manual Command Line Entry! Is there a way to configure mysql to throw an error while trying to update NOT NULL Fields with NULL??? Is that a bug that will be fixed someday? This is listed as known misbehaviour: http://www.mysql.com/doc/en/Bugs.html The short, general answer is: do not rely on MySQL to do input validation for you. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Return every Nth row in a result set
Hello. On Sun 2002-12-22 at 08:56:43 -0500, [EMAIL PROTECTED] wrote: I really don't want to do this client side (I'd have to execute approximately 10 queries for every page load just for this small task). Selecting the entire table into a temp table to number the rows also seems rather inefficient. I was reading in a book at Barnes and Noble yesterday which said to use a query that looked something like this: SELECT a.id FROM documents as a, documents as b WHERE a.id = b.id GROUP BY a.id HAVING MOD(a.id,:n); I'm nearly positive that that isn't exactly what it said, but it was something like that. If anyone can come up with a way to do this without a temporary table and only one or two queries (using 3.x or 4.0) that'd be great. Thanks for the help guys. Well, the solution is already in there: they suggest using a HAVING clause to reduce the rows after the complete result set has been determined. And to use MOD(id, number) to select which rows to keep. MOD(id,10) will return 0 for multiples of 10. So, if you want every 10th rows, you would use SELECT * FROM your_table WHERE some_condition HAVING NOT MOD(id,10) If you still encounter problems, please elaborate. And include a real example of what you tried. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: thread_cache . what is it ? (no newbie question) .
Hi. On Fri 2002-12-20 at 10:13:57 +0200, [EMAIL PROTECTED] wrote: Hi . Simple but still tricky question : what mean 'thread' in mysql's server point of view ? I've set the thread_cache_size=30 in my.cnf . I use Mysql with Php and my aplication's behaviour is like this : connect - select update insert select etc.. disconnect; This(above) is a thread that mysql caches ? If so , i don't think i can benefit from thread cache because my threads are very different from one to another :) . The thread cache does not care, what a thread had executed. It is about saving the destroy/create cycle, when your app disconnects and the next one connects. So it is about re-using an existing thread for the next connection, instead of creating a new one for each new connection. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Recover deleted records
Hello. On Wed 2002-12-18 at 14:49:12 +, [EMAIL PROTECTED] wrote: Hi, I posted this a few days ago, but haven't seen a reply as yet, and I'm getting quite desperate now!! You probably got no reply because there is no real solution to your problem. I've managed to delete all the records in a table, well haven't we all done it sometime!, and was wondering whether it's possible to recover them. I notice that the .MYD and .MYI files still appear to be showing the original data. You are right, the data is still there (some data is lost, if dynamic row storage is used). But there is no tool ready to recover the data. You are expected to have backups. Recovering the data by hand will be a *lot* of work. Before doing anything else, make sure to create a backup of the files, in case anything else goes wrong. If you need the data desperately enough to invest in the recovering, you may want to consider contacting the MySQL people directly and ask if they are able to help. If you have update-logs enabled, you could re-run the queries to recreate the tables. Regards, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Severe performace problem linking tables with mysql
Hi. On Wed 2002-12-18 at 18:40:04 +, [EMAIL PROTECTED] wrote: PLATFORM: 3.23.52-max-nt with Windows 2000 professional (default table type) I have discovered a performace issue when joining several tables together. The performance is extremely poor when performing select queries using the WHERE clause and joining the tables with the pk_media_id = fk_media_id etc... I guess this is what people might think about when considering using mysql. With SQL 2000 Windows Server, and ACCESS, there was no loss in performace. 4 of my tables are cross reference tables as such: Access? You aren't serious, are you? If you really experience Microsoft Access being faster than MySQL, you are doing something *seriously* wrong. Probably keys missing or not used for whatever reason. Please post the result of EXPLAIN for the query in question and the result of SHOW INDEX. Also SHOW CREATE TABLE, if posting that information is OK with you. And whatever information you consider to be of interest. Regards, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Converting many tables into MyISAM
Hello.
For changing one table, the recommended way is
ALTER TABLE foo TYPE=MYISAM;
There is no built-in way to change several tables at once. I regulary
have the need to apply the same command to some tables. If you use
some UNIX shell, you can do something like this:
mysql your_db -t -e SHOW TABLE STATUS | awk '/ ISAM / {print $2}'
That will give a list of tables of type ISAM. When you are sure that
you get the tables you want, change the awk command to
... | awk '/ ISAM / {print ALTER TABLE $2 TYPE=MYISAM;}'
When you are satisfied with the output (and maybe tested one line by
copypaste), just append a call to the command line client after awk:
... | mysql your_db
So the complete command line would be:
mysql your_db -t -e SHOW TABLE STATUS \
| awk '/ ISAM / {print ALTER TABLE $2 TYPE=MYISAM;}' \
| mysql your_db
HTH,
Benjamin.
On Wed 2002-12-18 at 18:58:04 -, [EMAIL PROTECTED] wrote:
I have about 300 database tables that are mostly ISAM and some MyISAM
format. I would like to move them all into MyISAM - what's the
easiest/quickest way? I am running 3.23.54.
I was thinking of doing mysqldump, then using a search/replace in the file
CREATE TABLE .. TYPE=, then recreating the database files using mysql, but
it seems a bit heavyhanded to me ;)
[...]
--
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Displaying output from MySQL
Hi. On Thu 2002-12-12 at 02:25:51 -0500, [EMAIL PROTECTED] wrote: Hi, Not sure if this is a PHP of a MySQL question, so I am sending it to both groups. Usually it is preferred that you send it only to the list which is more appropriate first (toss a coin, if you must ;-) and only if you get no satisfying answer, try the other one. Cross-posting is usually frowned upon. Basically I have a list of numbers with two decimal places in the MySQL database, but I only want to display some of them with the decimal points. i.e. 70 (not 70.00) 87 51.5 46.75 12 29 5.5 -1 45 I know it's probably a weird request, but any thoughts on how one would do this either through PHP or MySQL. You can use TRIM for that purpose: SELECT TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM column)) ... HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql Hogging ram?
Hello. On Wed 2002-12-11 at 05:14:09 -0800, [EMAIL PROTECTED] wrote: Hello: I am having a strange problem that it appears that no one else is having. I am running Slackware Linux with a 2.4.18 kernel, and MySQL 3.23.52-log. It is running on a dual CPU system with 860M ram. The problem is that mysql seems to reserve a lot of ram reguardless of what mysql is actually doing. Here is a top output [...] 10252 mysql 90 270M 260M sleep 2:10 0.01% 0.00% mysqld [...] Why is mysql size showing as 270M? There is nothing going on that looks like it is useing lot's of ram. Is this something to worry about? [...] This is a snip of my /etc/my.cnf [...] set-variable= key_buffer=256M Here you are allowing MySQL to use up to 256MB as cache for indexes. Well, it is actually doing as you told it to do. As the cache is meant to keep index pages between queries, there is no sense in freeing those pages in between. It is what makes queries fast by avoiding disk accesses for locating the records to load. [...] These problems are causing me serious pain as whenever a large process starts my load avg jumps throught the roof. I run radius off of this database, and when the load jumps users fail to authenticate. Any help/sugestions with this matter would be great. 256MB for the key_cache on a 860MB system looks completely sane to me, maybe even small, depending what else runs on the machine. But if that memory usage makes problems, you should decrease it, of course. But remember that the price you pay is a performance loss for MySQL. How much, that depends on how efficient the key cache is with the new size. On the other hand, I wonder what claims the other 590MB of your system? HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Odd Slowness in 4.0.5a with binlog
Hello. On Sat 2002-12-07 at 10:14:57 -0800, [EMAIL PROTECTED] wrote: I have the binlog files stored to a separate drive (actually a partition on a drive other than the raid array for the tables). When this partition becomes full, mysql's load average goes from 1.5 to 25 and stays there until more space is available on the partition that holds the binlog. This isn't the biggest deal in the world, since I should make sure there is enough space, but why is this happening? It never happened with 3.23.x. Although it used to cause a segfault in 4.0.2. (MySQL 4.0.5 actually did segfault, but a while after it ran out of space and had 0 processes running. How it had 0 processes running is beyond me...) Well, the following does not completely describe what you observe, but might be a beginning: When MySQL encounters a disk-full condition, the thread in question complains in the error log, simply sleeps and checks in regular intervals if space has been freed. Soon, there will be another thread which needs to access the same tables/blocks/rows, that the first one did and still may lock, because it did not finish yet. On you go, with one thread after another. Of course, threads which process queries, which do not touch the stuff blocked by the first thread or the binlog itself, will run fine. But as you can imagine, the number of threads blocked will grow with time and the number of threads to do other stuff will decrease until all threads are blocked. I am not sure why you observe this load (AFAIK, blocked threads should not change the load), but it explains well, why 0 processes are running. They are all waiting. HTH, Benjamin. PS: Of course, that was only a general description. In real, details may be more complex. For example, I could bet that writing on the binlog is protected by a mutex. Therefore only the first thread waits due to the disk full condition. Others which want to write to binlog are waiting on the mutex instead. But anyhow, you get the idea. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Single process hanging
Hello. On Sat 2002-12-07 at 13:35:03 -0500, [EMAIL PROTECTED] wrote: I found that kill -9 seems to be the only way to kill the hung process. I'll see if I can find another way. OK. Sorry about my over-reaction, but it was not obvious from your mail that nothing else worked. Rather a bit too cautious than too less. [...] Release: mysql-3.23.46 (Source distribution) Environment: machine, os, target, libraries (multiple lines) System: Linux groupstudy.com 2.2.20 #1 Wed Dec 12 12:11:07 EST 2001 i586 unknown Architecture: i586 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Aug 10 2000 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x1 root root 4106572 Oct 10 19:55 /lib/libc-2.1.3.so -rw-r--r--1 root root 20336836 Oct 10 19:55 /usr/lib/libc.a -rw-r--r--1 root root 178 Oct 10 19:55 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql-3.23.46 --localstatedir=/home/httpd/mysql --with-mysqld-user=nobody Perl: This is perl, version 5.005_03 built for i386-linux Well, I am not too familiar with possible build problems, so I hope someone else will jump in here. Although I wondered if -felide-constructors -fno-exceptions -fno-rtti was missing (that is unfortunatly not obvious from the output). Anyhow. Can you try a precompiled binary? I understand that a lot of stability problems can be solved this way. HTH, Benjamin. [...] an active website. On occasion mysql seems to stop responding. When I log into the server one of the MySQL processes is consuming a substantial amount of CPU resources (i.e. it is on the top of the list when viewing the top command.). [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Compile error on HPUX
Hello. As a side note, did you try the precompiled binary for HP-UX already? http://www.mysql.com/downloads/mysql-3.23.html On Mon 2002-12-09 at 08:22:44 -0800, [EMAIL PROTECTED] wrote: Benjamin, Thanks for you information, now I tried to use gcc Which version? compile: CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure You left out the info from mysqlbug again. Although I doubt that this has to do with the error you see below, you do not seem to honor the notes about building from the manual: http://www.mysql.com/doc/en/HP-UX_10.20.html http://www.mysql.com/doc/en/HP-UX_11.x.html The configure line above looks incomplete. Configure has no problem. But When I run make, I got: gcc -O3 -DDBUG_OFF -DHAVE_BROKEN_PREAD -DDONT_USE_FINITE -DHAVE_BROKEN_GETPASS -DNO_FCNTL_NONBLOCK -DDO_NOT_REMOVE_THREAD_WRAPPERS -DHAVE_BROKEN_PTHREAD_COND_TIMEDWAIT -o hp_test2 hp_test2.o libheap.a ../mysys/libmysys.a ../dbug/libdbug.a ../strings/libmystrings.a -lpthread -lcrypt -lnsl -lm -lpthread Making all in sql Making all in share No suffix list. ../../extra/comp_err czech/errmsg.sys Usage: ../../extra/comp_err [-?] [-I] [-V] fromfile[s] tofile *** Error exit code 255 I am sorry, but I am not able to help here. As I said, I am not familiar with HP-UX (an neither with build problems, because I never had any). HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
