MySQL InnoDB table row access
Hi, I want to access data from an InnoDB table. I know that I can do this using the corresponding handler and ha_rnd_next() or ha_index_next(). My problem is that the original MySQL code is outperforming my implementation even on simple projection queries, even though I use the same functions. The entry point for my own execution is in the sql_parce.cc file in the function int mysql_execute_command(THD *thd). ~line 2600 res= execute_sqlcom_select(thd, all_tables); database: http://dev.mysql.com/doc/employee/en/index.html query: select title from titles; my code: while (!tables->table->file->ha_rnd_next(tables->table->record[0])) { result->send_data(thd->lex->select_lex.item_list); } handler->ha_index_or_rnd_end(); return; result is of the type select_send To evaluate the query, using this simple loop, takes around 20 percent longer than the original MySQL code. I have used a debugger to step through the code, but the code being executed seems to be the same. That's why I think MySQL is doing some kind of optimization on the table but I can't figure out where. I have also tried to let my code run later in the evaluation process of MySQL but the result stays the same. Best regards, Tobias Krueger
Re: Join with OR-condition and Indexes
Thanks for your answers. I found out, that MySQL behaves very strange in this situation. I think this is a bug or important missing feature. I would like to see how other DBMS behave in this situation, which I would think is a common problem - whenever you want to join one column of a table with several columns of another table. MySQL uses an index_merge when I query for one specific athlete: SELECT COUNT(*) FROM matches m WHERE (m.team1_player_id = 808884 OR m.team1_partner_id = 808884 OR m.team2_player_id = 808884 OR m.team2_partner_id = 808884) id select_type table typepossible_keys key key_len ref rowsExtra 1 SIMPLE m index_merge team1_player_id_idx,team1_partner_id_idx,team2_player_id_idx,team2_partner_i d_idx team1_player_id_idx,team1_partner_id_idx,team2_player_id_idx,team2_partner_i d_idx 5,5,5,5 NULL153 Using union(team1_player_id_idx,team1_partner_id_idx,team2_player_id_idx,team2_par tner_id_idx); Using where But it doesn't use the index merge when joining like I originally tried, although logically it should be able to use it, shouldn't it? Of course, adding USE INDEX FOR JOIN (team1_player_id_idx, team1_partner_id_idx, team2_player_id_idx, team2_partner_id_idx) cannot convince MySQL to use the indexes. SELECT a.id, COUNT(*) FROM athletes a FROM athletes a LEFT JOIN matches m USE INDEX FOR JOIN (team1_player_id_idx, team1_partner_id_idx, team2_player_id_idx, team2_partner_id_idx) ON ( m.team1_player_id = a.id OR m.team1_partner_id = a.id OR m.team2_player_id = a.id OR m.team2_partner_id = a.id ) WHERE a.gender = 'female' GROUP BY a.id Then Michael Dykman said, MySQL is restricted to one index per table per query. So I thought, maybe I can help MySQL when I add a compound index for all players. So it could use the one index to resolve the join. ALTER TABLE `matches` ADD INDEX `players_idx` ( `team1_player_id` , `team1_partner_id` , `team2_player_id` , `team2_partner_id` ) ; Explain now gives me: id select_type table typepossible_keys key key_len ref rowsExtra 1 SIMPLE a ref gender_index_idxgender_index_idx 1 const 2193Using where; Using temporary; Using filesort 1 SIMPLE m index team1_player_id_idx,team1_partner_id_idx,team2_pla... players_idx 20 NULL46664 Using index Hm, what does this mean? It uses the index (players_idx) but still evaluates all 46664 rows? This does not make sense to me. Anyways, the query is still extremely slow (2400 rows in 3 min 5 sec). But with IGNORE INDEX (players_idx) the query seems to be infinite (over 15 min). So now it seems that UNIONS are the only options. But they are also much slower than it should be. You version took 10 seconds. My modified version with UNION ALL instead of DISTINCT takes 5.2 seconds - only showing athletes with min. 1 match. SELECT *, COUNT(am.id) AS number_matches FROM ( SELECT a.id AS athlete_id, a.first_name, a.last_name, a.gender, m.* FROM athletes a INNER JOIN matches m ON (m.team1_player_id = a.id) UNION ALL SELECT a.id AS athlete_id, a.first_name, a.last_name, a.gender, m.* FROM athletes a INNER JOIN matches m ON (m.team1_partner_id = a.id) UNION ALL SELECT a.id AS athlete_id, a.first_name, a.last_name, a.gender, m.* FROM athletes a INNER JOIN matches m ON (m.team2_player_id = a.id) UNION ALL SELECT a.id AS athlete_id, a.first_name, a.last_name, a.gender, m.* FROM athletes a INNER JOIN matches m ON (m.team2_partner_id = a.id) ) AS am GROUP BY am.athlete_id ORDER BY number_matches DESC But then I could also use the idea I had in the first post, which still needs 3.8 seconds. SELECT *, COUNT(am.id) AS number_matches FROM ( ( SELECT team1_player_id AS player_id, teammatch_id, match_type, team1_score, team2_score, team1_points, team2_points, no_fight FROM matches WHERE team1_player_id IS NOT NULL # reduced the query from 4.1 to 3.8 sec ) UNION ALL ( SELECT team1_partner_id, teammatch_id, match_type, team1_score, team2_score, team1_points, team2_points, no_fight FROM matches WHERE team1_partner_id IS NOT NULL ) UNION ALL ( SELECT team2_player_id, teammatch_id, match_type, team1_score, team2_score, team1_points, team2_points, no_fight FROM matches WHERE team2_player_id IS NOT NULL ) UNION ALL ( SELECT team2_partner_id, teammatch_id, match_type, team1_score, team2_score, team1_points, team2_points, no_fight FROM matches WHERE team2_partner_id IS NOT NULL ) ) AS am INNER JOIN athletes a ON (am.player_id = a.id) GROUP BY a.id ORDER BY number_matches DESC Changing the database schema doesn't seem to be usefull. Regarding my schema is already normalized and over-normalization generally decreases performances. I guess I would then run into other problems. Greetings Tobias -Ursprüngliche Nachricht- Von: Mi
Join with OR-condition and Indexes
Hello, I'm working on an application for my bachelor thesis. I'm having a performance problem with a SQL-Query in MySQL5. I hoped someone can easily enlighten me in this issue. The schema: CREATE TABLE IF NOT EXISTS `athletes` ( `id` int(11) NOT NULL AUTO_INCREMENT, `last_name` varchar(20) NOT NULL, `first_name` varchar(20) NOT NULL, `gender` enum('male','female') NOT NULL, `birthday` date NOT NULL, `country` char(2) NOT NULL, `club_id` int(11) NOT NULL, `is_active` tinyint(1) NOT NULL, PRIMARY KEY (`id`), KEY `gender_index_idx` (`gender`), KEY `is_active_index_idx` (`is_active`), KEY `club_id_idx` (`club_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Sportler bzw Mitglieder des Verbandes'; -- CREATE TABLE IF NOT EXISTS `matches` ( `id` int(11) NOT NULL AUTO_INCREMENT, `teammatch_id` int(11) NOT NULL, `match_type` varchar(10) NOT NULL, `team1_player_id` int(11) DEFAULT NULL, `team1_partner_id` int(11) DEFAULT NULL, `team2_player_id` int(11) DEFAULT NULL, `team2_partner_id` int(11) DEFAULT NULL, `team1_score` tinyint(3) unsigned DEFAULT NULL, `team2_score` tinyint(3) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `teammatch_id_idx` (`teammatch_id`), KEY `team1_player_id_idx` (`team1_player_id`), KEY `team1_partner_id_idx` (`team1_partner_id`), KEY `team2_player_id_idx` (`team2_player_id`), KEY `team2_partner_id_idx` (`team2_partner_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Spiele zwischen zwei oder vier Sportlern (Einzel und Doppel)' AUTO_INCREMENT=46665 ; I want to get all matches for each athlete and calculate statistics such as number of matches etc. The basic very simplified query is like SELECT a.id, COUNT(*) FROM athletes a LEFT JOIN matches m ON ( m.team1_player_id = a.id OR m.team1_partner_id = a.id OR m.team2_player_id = a.id OR m.team2_partner_id = a.id ) WHERE a.gender = 'female' GROUP BY a.id Now the problem is, that mysql uses a full table scan to retrieve the matches for an athlete, so the execution takes many seconds or even worse. An athlete can be referenced in any of the m.team1_player_id OR m.team1_partner_id OR m.team2_player_id OR m.team2_partner_id. (That allows doubles matches.) Why is an full table scan necessary although there is an index on each of these fields? So an index exists for each OR-part of the join condition... Here the execution plan: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE a ref gender_index_idx gender_index_idx 1 const 2193 Using where; Using temporary; Using filesort 1 SIMPLE m ALL team1_player_id_idx,team1_partner_id_idx,team2_pla... NULL NULL NULL 46664 Joining on each fields like the following is very fast and uses the index but of course doesn't give me the expected result. FROM athletes a LEFT JOIN matches m ON (a.id = m.team1_player_id) LEFT JOIN matches m2 ON (a.id = m2.team2_player_id) Maybe I need to do a workaround using a UNION? But this doesn't help either: (It takes 76 seconds) FROM athletes a LEFT JOIN ( ( SELECT team1_player_id AS player_id, teammatch_id, match_type, team1_score, team2_score, team1_points, team2_points, no_fight FROM matches ) UNION ( SELECT team1_partner_id, teammatch_id, match_type, team1_score, team2_score, team1_points, team2_points, no_fight FROM matches ) UNION ( SELECT team2_player_id, teammatch_id, match_type, team1_score, team2_score, team1_points, team2_points, no_fight FROM matches ) UNION ( SELECT team2_partner_id, teammatch_id, match_type, team1_score, team2_score, team1_points, team2_points, no_fight FROM matches ) ) m ON (a.id = m.player_id) I hope someone can help me with this. Thanks in advance. Regards Tobias
Question about triggers
Hy there, I'm planing to to build a small "partial replication" on MySQL 5.0 using the Spread Toolkit and the Message API for MySQL. Therefore I'll create a trigger (on insert) which fetches the row and sends it to the message group XY. I was wondering if there is a way to tell MySQL in an SQL statement to not fire the trigger, for example if a message from Server B arrives on A - and contrariwise - I don't want the trigger to be launched to avoid loops and such. I actually couldn't find anything about this topic. So, my question: is it possible at all? Regards and thanks in advance, Toby -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Bulk INSERT performance question
List, I am bulk inserting a huge amount of data into a MyISAM table (a wikipedia page dump). Before I issued SOURCE filename.sql; I did an ALTER TABLE page DISABLE KEYS; LOCK TABLES page WRITE; The dump consists of about 1,200 bulk INSERT statements with roughly 12,000 tuples each. For the first hour or so it performed pretty good, taking about 5 seconds for each INSERT and using all the CPU it could get. But a while later time consumption increased up to 10 minutes. In the same time, mysqld is barely using any CPU (less than 2%), there is no significant I/O going on, and there is still unused memory. Thoughts? Thanks a lot -- Tobi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory limit?
On Wed, 9 Feb 2005, Batara Kesuma wrote: > Hi, > > I try to install MySQL 4.1.9 (official RPM from mysql.com). My machine > is running linux 2.6.9, and it has 4GB of RAM. The problem is MySQL > won't start if I set innodb_buffer_pool_size to >= 2GB. Here is my > ulimit. Are you trying this on a 32-bit cpu machine? > > [EMAIL PROTECTED] mysql]# ulimit -a > core file size (blocks, -c) 0 > data seg size (kbytes, -d) unlimited > file size (blocks, -f) unlimited > pending signals (-i) 1024 > max locked memory (kbytes, -l) 32 > max memory size (kbytes, -m) unlimited > open files (-n) 1024 > pipe size(512 bytes, -p) 8 > POSIX message queues (bytes, -q) 819200 > stack size (kbytes, -s) 10240 > cpu time (seconds, -t) unlimited > max user processes (-u) 63484 > virtual memory (kbytes, -v) unlimited > file locks (-x) unlimited > > Here is the error message: > > 050209 20:41:18 mysqld started > 050209 20:41:18 [Warning] Asked for 196608 thread stack, but got 126976 > 050209 20:41:18 InnoDB: Fatal error: cannot allocate 2147500032 bytes > of > InnoDB: memory with malloc! Total allocated memory > InnoDB: by InnoDB 34049176 bytes. Operating system errno: 12 > InnoDB: Cannot continue operation! > InnoDB: Check if you should increase the swap file or > InnoDB: ulimits of your operating system. > InnoDB: On FreeBSD check you have compiled the OS with > InnoDB: a big enough maximum process size. > InnoDB: Fatal error: cannot allocate the memory for the buffer pool > 050209 20:41:18 [ERROR] Can't init databases > 050209 20:41:18 [ERROR] Aborting > > > I have also checked the archive and it looks like other people also > having similar problem, but I couldn't find the answer. Is there any > memory limit when it is run on linux? What can I do so MySQL can run > with >= 2GB RAM? Thank you in advance. > > Regards, > --bk > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB crash and runaway rollback - help pls
On Tue, 8 Feb 2005, Heikki Tuuri wrote: > You should upgrade to 4.1.9. That version commits ALTER TABLE at every 10 > 000 rows, and a runaway rollback can no longer happen. This is very nice! Are there any plans for the same with INSERT ... SELECT -type statements? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REPLACE INTO //add or update?
On Mon, 7 Feb 2005, Bjorn van der Neut wrote: > Hello Everyone, > > Can someone tell me If you can find out if the replace into function has > done an insert or an update? It actually never does an update, it always INSERTs. It either does an insert or delete(s) followed by an insert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump unusable, bugged?
On Tue, 1 Feb 2005, matt_lists wrote: > >> now we have this added on a few tables in the dump > >> > >> DATA DIRECTORY='E:\mysql\data\campbell\' INDEX > >> DIRECTORY='E:\mysql\data\campbell\' > > > > DATA DIRECTORY='E:\\mysql\\data\\campbell\\' INDEX > > DIRECTORY='E:\\mysql\\data\\campbell\\' > > > Anybody know? > > Is there some option I'm not setting which causes the data directory and > index directory to not be correct? > > Why are there not hundreds of people with this problem, I would think it > affects anyone on 4.x running on a windows machine, I hope they dont > learn the hard way when a restore fails to work! Noone has found a way to reproduce it with 100% certainity. If you have a way to always reproduce, feel free to re-open my bug at: http://bugs.mysql.com/bug.php?id=6660 (Or send me the test-case and I can do it). I haven't gotten the bug approved since I can't reproduce it (but it does happen quite often, just haven't been able to find out why, not even by replaying binary logs). cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query problem
Hi! I have a problem with a query. I have a given amount of values (id:s) (for example 1,2,6,12,77,78,79,122,123,124), these are related to a table, "pref", and belongs to a specific category, "pref_cat"-table. So, 1,2,6,12 are connected to id 1 in "pref_cat", 77,78,79 to id 2 and 122,123,124 to id 3 in "pref_cat". Table: "pref" | id| bigint(20) unsigned | | user_id | bigint(20) unsigned | | category_id | bigint(20) unsigned | | option_id | bigint(20) unsigned | Each user have X nr of rows in this table. From the given id:s I want match them with this table in the way that one id (option_id) from the given values in each category_id (ex 1,2 and 3) should be in this table. So, if a user X have id 1, 77, 122 in this table he should be a match. Below I have query that doesn't work, the reason it doesn't work is because it tries to match to the same id in "pref". SELECT DISTINCT u.id FROM user u INNER JOIN pref p ON p.user_id = u.id AND p.option_id IN ( 1,2,6,12 ) AND p.option_id IN ( 77,78,79) AND p.option_id IN ( 122,123,124 ) What I rather want to do is to match all p.option_id:s for the specific user with the ones in the "IN". For example if a user have the id: 1, 77 AND 122 I want to to a match like this: SELECT DISTINCT u.id FROM user u INNER JOIN pref p ON p.user_id = u.id AND "if any of (1,77,122) exist IN (1,2,6,12)" AND "if any of (1,77,122) exist IN (77,78,79)" AND "if any of (1,77,122) exist IN (122,123,124 )" Is this in some way possible to do or does someone have an other solution in mind? (Maybe I should tell you that I'm using mySQL 4.1) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump unusable, bugged?
On Thu, 27 Jan 2005, matt_lists wrote: > Having repeatable problems doing restores, 4.1.8 and 4.1.9 both do the > same error > > Is there some setting I'm missing? > > mysqldump -u username-pPassword --all-databases --quote-names > > /intranet/backup/backup.sql > > > E:\intranet\backup>mysql -u xotech -pDaredevil22 -f < backup.sql > ERROR at line 2153: Unknown command '\m'. > ERROR at line 2153: Unknown command '\m'. > ERROR at line 2153: Unknown command '\m'. > mysql: Out of memory (Needed 626767192 bytes) > mysql: Out of memory (Needed 626763096 bytes) > > E:\intranet\backup> I've seen this quite a few times, but never been able to reproduce it properly. I'm assuming you're running on Windows, correct? I'd search your dump-file for "DATA DIRECTORY" the problem on Windows is that it uses \ instead of / in the path names there, which makes it use it as an escape character. Since dATA/INDEX DIRECTORY in CREATE TABLE is ignored on Windows when importing anyways, it's safe to remove those clauses. If you can find a way to reproduce it, feel free to add it to my old bug report about this at: http://bugs.mysql.com/bug.php?id=6660 cheers, > > > -- Tobias Asplund <[EMAIL PROTECTED]> Trainer and Consultant, Sweden MySQL AB http://www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: .MYD, .MYI files don't exist... but queries WORK??????
On Wed, 3 Nov 2004, Anders Green wrote: > Tobias Asplund wrote: > >If you installed MySQL 4.1.7 on Windows with the new installer > > Yes I did. > > >it will automatically use InnoDB tables as the default > > Ah ha. Thanks. :) > > > That just leaves this: > > > > So now my question "Where does InnoDB data get stored?" You'll probably see some files in your data\ directory under your MySQL directory named something like 'ibdata1'. InnoDB stores all its data in a tablespace, so there won't (by default) be more than the .frm file per table (as of 4.1 you can override this with --innodb-file-per-table) You can read more about this here: http://dev.mysql.com/doc/mysql/en/InnoDB.html > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTERSECT in mysql
On Wed, 3 Nov 2004, Chaitra Yale wrote: > ...how can union be the same as intersect..iam trying to get the names > of comapnies that are in both queries.for example the first query > gives me companies A, B AND C and the second query gives A , B..i want > the intersect of these 2 queriesso i get companies A and B...if i > did a union i will get A, B and C Could use the union in a subquery together with a HAVING COUNT(*) on the outside. SELECT * FROM ( SELECT DISTINCT col1 FROM t1 WHERE... UNION ALL SELECT DISTINCT col1 FROM t1 WHERE... ) AS tbl GROUP BY tbl.col1 HAVING COUNT(*) = 2 Or something... not sure how well that would performe, however. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: .MYD, .MYI files don't exist... but queries WORK??????
If you installed MySQL 4.1.7 on Windows with the new installer, it will automatically use InnoDB tables as the default unless you specify different. (This is different from earlier versions), so the error was probably not in the Query Browser, but in the server settings in this case. On Wed, 3 Nov 2004, Anders Green wrote: > Sequence of events: > -had an MS Access db > -converted it to MySQL 4.1.7 with a utility > -everything worked > -using MySQL Query Browser 1.0.1, added three tables > -everything worked > -comes time to deploy to another machine for testing, > copy directory of database over to other machine > -any queries involving the three new tables fail: > mysql> select * from admin; > ERROR 1016 (HY000): Can't open file: 'admin.InnoDB' (errno: 1) > > Now, the first thing to do is go looking for files. > What I find shocks and amazes me! There are missing > files! *grin* > > For each of the three new tables, there is a .frm > file, but no .myd or myi file. Okay. transfer > aborted or something I'll go back to the first > development machine and copy them over. > > WAIT A SECOND! They don't exist on the first > development machine either! And yet, queries > on the first development machine are working, > and those files are nowhere to be found on the > entire drive. > > Ok, I was about to add some info about the > table types... and with the query browser > on the first machine, I saw that they were > all InnoDB instead of MyISAM. Changing those > to MyISAM created the files. Copied those > over to the other machine, restarted the > server there, and they work. > > So now my question (which might be "Why does > the query browser not select MyISAM as the > table type since it describes it as "default">", > or might be "why is my junk not working?") > is "Where does InnoDB data get stored?" > > That is, in what file and what location? > Cause now I'm curious. :) > > Cheers, > Anders > +===+ > |Anders Green Email: [EMAIL PROTECTED] | > | Home: 919.303.0218 | > |Off Road Rally Racing Team: http://LinaRacing.com/ | > +===+ > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More on MySQL server is taking all my hardrive space
On Thu, 21 Oct 2004, C.F. Scheidecker Antunes wrote: > Hello, > > On a further inspection and by reading Paul DuBois' I guess m y system > has to many bin logs. How can I get ride of them? Assuming you don't need them for replication or point-in-time recovery, you can use the PURGE command. http://dev.mysql.com/doc/mysql/en/PURGE_MASTER_LOGS.html If you do not need the binary logs for anything, and you're not running replication you can remove all of them and restart at binlog.01 with the command RESET MASTER (I'd suggest using the PURGE, though). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB deadlock problem
On Wed, 6 Oct 2004, David Edwards wrote: > I've got a deadlock problem using InnoDB tables (...) > Transaction 1: > START TRANSACTION; > DELETE FROM results WHERE id_job = 25920; > INSERT INTO results(result,id_job) VALUES (31.461937,25920); > COMMIT; > > Transaction 2: > START TRANSACTION; > DELETE FROM results WHERE id_job = 25919; > INSERT INTO results(result,id_job) VALUES (25.388607,25919),(22.650234,25919); > COMMIT; I think this manual page might explain what's happening: http://dev.mysql.com/doc/mysql/en/InnoDB_Next-key_locking.html -- Tobias Asplund <[EMAIL PROTECTED]> Trainer and Consultant, Sweden MySQL AB http://www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT on duplicate UPDATE?
On Tue, 14 Sep 2004, Yves Goergen wrote: > Hi, > I can vaguely remember there was something like "INSERT... on duplicate > key UPDATE..." in MySQL, but the documentation search is almost as > useful as I'm used to - it cannot tell me anything about this. Can you > please? How does this work, what's the syntax? Is this ANSI-SQL standard > (or compatible to other DBMS)? Or is it even documented? It's covered on the INSERT page: http://dev.mysql.com/doc/mysql/en/INSERT.html "If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and already contains the value 1, the following two statements have identical effect: mysql> INSERT INTO table (a,b,c) VALUES (1,2,3) -> ON DUPLICATE KEY UPDATE c=c+1; mysql> UPDATE table SET c=c+1 WHERE a=1; Note: If column b is unique too, the INSERT would be equivalent to this UPDATE statement instead: mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1; If a=1 OR b=2 matches several rows, only one row is updated! In general, you should try to avoid using the ON DUPLICATE KEY clause on tables with multiple UNIQUE keys. As of MySQL 4.1.1, you can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT part of the INSERT ... UPDATE statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted if no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise. Example: mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b); That statement is identical to the following two statements: mysql> INSERT INTO table (a,b,c) VALUES (1,2,3) -> ON DUPLICATE KEY UPDATE c=3; mysql> INSERT INTO table (a,b,c) VALUES (4,5,6) -> ON DUPLICATE KEY UPDATE c=9; When you use ON DUPLICATE KEY UPDATE, the DELAYED option is ignored. " -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext doesn't seem to find certain text
On Tue, 14 Sep 2004, joe mcguckin wrote: > If I perform a fulltext search for 'foo', it won't match text like > 'foo, inc'. In addition to the other suggestions, make sure our ft_min_word_len isn't more than 2, because it won't index words shorter than that many characters. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL speed
> Can anyone tell me what's going on? Are there MySQL parameters that can > improve things? To know if you need to tune something, you can send us the copy of SHOW STATUS; and SHOW VARIABLES; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Field Type Problem
On Tue, 14 Sep 2004, Sheraz wrote: > when I try following: > > CREATE TABLE `MyTable` ( > `SNumber` char(32) NOT NULL, > `UserID` char(32) NOT NULL default '0', > PRIMARY KEY (`SNumber`) > ) TYPE=MyISAM; > > It created the exactly correct table, But when i add > any varchar field in the above sql..then generated > table is like it has all the fields of type Varchar This behaviour is documented (although slightly annoying) here: http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave I/O thread dies, fatal error 1236
On Tue, 7 Sep 2004, matt ryan wrote: > Still have not got this fixed, I'm all out of idea's, the slave has been > reloaded again today I forgot, did you have multiple slaves on multiple machines? If so, do they have identical hardware/drivers? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky Date Query
On Tue, 7 Sep 2004, Lee Denny wrote: > Hello, > > I need to perform a select that compares two dates > > I need to return all records that haven't had date_2 set after a given > number of days since date_1. > ... WHERE date_2 < date_1 + INTERVAL X DAY Where X is the number of days. Assuming this is what you asked, giving more information is always good. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't start server: Bind on TCP/IP port: Address already in use
Hi! I have restarted the Linux server. Typing >mysqld result: 040607 11:32:47 Can't start server: Bind on TCP/IP port: Address already in use 040607 11:32:47 Do you already have another mysqld server running on port: 3306 ? 040607 11:32:47 Aborting 040607 11:32:47 mysqld: Shutdown Complete This isn't correct I think, as >ps yields: PID TTY TIME CMD 9855 pts/100:00:00 bash 10363 pts/100:00:00 ps No sign of another mysql daemon running there. What to do? It used to work flawlessly. Am I hacked? Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 2026: SSL connection error
Hello! I'm using MySQL 4.0.18 on a SuSE 9.0 machine. I compiled it with ./configure \ --with-vio \ --with-openssl \ --without-debug \ --without-bench \ --enable-assembler I've created the certs & keys with the following commands: openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/cacert.pem -config $DIR/openssl.cnf openssl req -new -keyout $DIR/server-key.pem -out $DIR/server-req.pem -days 730 -config $DIR/openssl.cnf openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem openssl ca -policy policy_anything -out $DIR/server-cert.pem -config $DIR/openssl.cnf -infiles $DIR/server-req.pem openssl req -new -keyout $DIR/client-key.pem -out $DIR/client-req.pem -days 730 -config $DIR/openssl.cnf openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem openssl ca -policy policy_anything -out $DIR/client-cert.pem -config $DIR/openssl.cnf -infiles $DIR/client-req.pem My /etc/my.cnf looks like this: --- /etc/my.cnf [client] ssl-ca=/etc/mysql/cacert.pem ssl-cert=/etc/mysql/client-cert.pem ssl-key=/etc/mysql/client-key.pem socket=/dat1/srv/chroot/mysqld/tmp/mysql.sock [mysqld] port=3306 user=mysql safe-show-database skip-symlink safe-user-create log=/var/log/mysql/mysql.log log-slow-queries=/var/log/mysql/slow_queries.log ssl ssl-ca=/etc/mysql/cacert.pem ssl-cert=/etc/mysql/server-cert.pem ssl-key=/etc/mysql/server-key.pem set-variable=local-infile=0 -- EOF SSL connections worked fine for a cuple of hours, but then I get this SSL error: ERROR 2026: SSL connection error When I delete the certs & keys and creating them again, all works fine for some time. But then the same error occurs. What did I wrong??? It seems, that the certs & keys are vaild only for some hours. Maybe someone could help me. Thanks. Tobias Gunkel Germany -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to change a Bit of a SET Value
Hi all, I use a SET Value to keep track of my Replication Process on the Main-Daemon. So for every Daemon, how collects Data from a Branch, a have a Flags as a SET ('daemon_on', 'slave_working', ...) Now I wont to toggle only one Bit and lave the rest at it is. Example: Now I would like to toggle Bit 1 (slave_working) without to check first if Bit 0 (daemon_on) is set or not. (I know the Slave can't work if the daemon is off, but there are more Options) Is there a way to set/reset only one Bit in a SET Value. I couldn't find something useful in the Docs. Maybe I can link the Update Values with an XOR and a Mask, but how must I do it in MySQL? Up to now a haven’t figured it out how to UPDATE a SET Value with a "binary Value". I use only the "Strings" or the "Decimal Value". Thanks if someone is a little bit more familiar with SET Values and posts a short comment. MfG, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY WITH NO PRINT
On Fri, 26 Mar 2004, Seena Blace wrote: > Select group,hostname,details from table1 order by group; > > I want the output should be like > Group hostname details > aa abababa > abababababab > anannanananna > bbllololololool Hi Seena, Try this: SELECT IF(@a != group, @a := group, '') AS group, hostname, details FROM table1 ORDER BY group Cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld_multi
Date: Wed, 14 Apr 2004 13:26:10 -0400 To: [EMAIL PROTECTED] From: "Pingouin Team" <[EMAIL PROTECTED]> Subject: mysqld_multi Message-Id: <[EMAIL PROTECTED]> Hi, I need help. I have my old mysql server running in my web server and I can't stop it for make the test of the new version (3.28 to 4.0). I have installed with compilation of the source code and configure my socket, var and port. I have make a config file with the --example: [mysqld_multi] mysqld = /home/benoit/mysql/bin/safe_mysqld mysqladmin = /home/benoit/mysql/bin/mysqladmin user = multi_admin password = multipass [mysqld2] socket = /home/benoit/tmp/mysql.sock port = 3310 pid-file = /home/benoit/mysql/var/pingouin.pid datadir = /home/benoit/mysql/var language = /home/benoit/mysql/share/mysql/english And I start my MySQL with ./mysqld_multi --config-file=/home/benoit/mysql/etc/my.cnf start 2 but when I execute that command line MySQL start and stop. In the log, I can see just that: mysqld_multi log file version 2.2; run: Wed Apr 14 13:01:02 2004 Starting MySQL servers Starting mysqld daemon with databases from /home/benoit/mysql/var 040414 13:01:05 mysqld ended Why the server do not stay online? Thank you, Benoit Tremblay -- NerdzTeam [EMAIL PROTECTED] ### Servus, to know what stops your MySQL Daemon you must look in your Log-File of this one. I don't know where it is in your system but it helps to add a separate one in [mysqld2] err-log = /var/log/mysql2d/mysqld.log and then look for the real problem (probably permissions ;-) But maybe you should wait an moment, because I have with this mysqld_multi a problem who can give you some trouble, too! As soon as I start the second Daemon the Socket of the first (main) Daemon disappears!! You can connect over TCP, but by default it tries the Socket at localhost. I have MySQL 5.0 running and maybe you haven't got this problem, but think about it. I posted about my Problem to the List ("mysqld_multi kills socket from previous Daemon" from 2004-04-07), but up to now I haven't got an answer and still don't know what’s going on with my Sockets! (Has someone an Idea ??) Best regards, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld_multi kills socket from previous Daemon
Hello all, I would like to run diverent MySQL-Daemons on the same Linux machine, so I use mysqld_multi to start the different processes. The Config looks right to me, I have different Database Directorys, Pid-Files, Ports, IDs usw. and different Socket-Files and all over it seems to work. But! as soon as I start the sekond Daemon my Socket-'File' from the first one, who ist started at the boot time (like RedHat Config), disappears! Both Daemons are running still fine, but the first one without a Socket. I have to connect over the TCP Port. The same happens if i stop the first Daemon and then start it with mysqld_multi. The Socket is here, untill I start the next Daemon with mysqld_multi. Has anybody a Idee where I can look and find a way to keep my Sockets ??? Before I say good by, I would like to apologies about the bat sentens construction and the writing mistakes in English! Best regards, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What exactly is happening with this table create?
On Mon, 15 Mar 2004, Boyd E. Hemphill wrote: > Using a tool to generate a data model I go the following statement: > > Create table StateN ( > StateId Int NOT NULL AUTO_INCREMENT, > StateNm Char(50) NOT NULL DEFAULT '', > StateCd Char(7) NOT NULL DEFAULT '', > SortInt Int NOT NULL DEFAULT 0, > UNIQUE (StateId), > UNIQUE (StateCd), > Primary Key (StateId), > UNIQUE Index ak_State (StateCd) > ) TYPE = InnoDB > ROW_FORMAT = Default; > > The last four statements seem to be redundant in that the > UNIQUE(StateId) is a proper subset of the Primary Key(StateId) statement > and is StateCd to its index. All four statements aren't redundant, only two of them are. > My question is, what is happening in terms of the objects I am creating? > That is: Am I creating for indexes or two? Are they the pk and ak that > I want (last two statements) or is the server only creating the first > two? All four indexes are created in this case. You have one primary key on the StateId column, one unique index on the StateId column called StateId (<-- redundant) one unique index on the StateCd column called StateCd and one unique index on the StateCd column called ak_State. The primary key should be removed if you want StateId to contain NULL values, but most likely you will want to remove the unique index on this column (since a primary key in itself is unique). Then you will remove one of the two indexes on the StateCd column. for example: DROP INDEX StateId ON StateN; DROP INDEX StateCd ON StateN; That would leave you with the Primary Key on StateId and a unique index called ak_State on the StateCd column. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect URGENT!
On Sun, 8 Feb 2004 [EMAIL PROTECTED] wrote: > the only way I can connect to the server is typing > > mysql -h localhost -u root > make sure you don't have a line "skip-networking" in your /etc/my.cnf cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with wildcards in host field .
On Fri, 6 Feb 2004, Alvaro Avello wrote: > the questions is , the wildcard in host field doesn't > involve localhost o a machine host's ? Which kind of permissions we > have to put in host fields to have a mobility and not to be afraid to > move our servers for an emergency ? When the host field is localhost, it is checked against when connecting through a local socket, when not it's checked when connecting through TCP/IP. You can force a TCP/IP socket over a Unix socket when connecting from the local computer by instead of connecting to localhost in your application connect to 127.0.0.1 or the computer's IP. This however will slightly decrease performance, since a Unix socket is faster. A fix if you have to emergency move the server could be to just change the fields from localhost to '%'. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0.0-alpha: can't create column 'Found'
On Fri, 30 Jan 2004 [EMAIL PROTECTED] wrote: > >Description: > When creating a table, mysql 5.0.0-alpha (binary package) > does not accept column name 'Found'. > > >How-To-Repeat: > create table log (Found INT UNSIGNED NOT NULL); > FOUND is a reserved word. Use within backticks, ie `Found` http://www.mysql.com/doc/en/Reserved_words.html cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on sub-selects
On Wed, 28 Jan 2004, Deven Phillips wrote: > Hello, > > There is a web site associated with the web-radio. Users of the web site > can rate songs which are contained in the database. The rating system > works such that users can rate songs from +3 to -2. Now, what I would > like to accomplish is to create a query that allows me to randomly > select a song from the database to add to the queue while taking into > account the ratings. For example: > > There are 2400 songs listed > One song has been rated three times as follows: > +3 > +1 > -1 > I would like that song to have a 3/2400 chance of being selected for the > queue. You can easily come into a situation where this isn't feasible. I assume the 3 in 3/2400 is the sum of the votes. Imagine you have 2 songs and 10 users. 5 users vote 3 for one of the songs and 5 users vote 2 for the same song. Then you'd have 25/2 chance of having that song selected. > Secondly, I need the database to store who voted for waht so that users > cannot continually vote +3 over and over for their favorite song. That is easily accomplished, just use a table like: CREATE TABLE votes ( userid INT UNSIGNED NOT NULL, songid INT NOT NULL, vote TINYINT NOT NULL, PRIMARY KEY (userid, songid) ) Then when your web application inserts or updates a vote you can use REPLACE INTO votes (userid, songid, vote) VALUES(1, 1, 2); That will take care of the problem of someone voting for their favorite song more than once. One suggestion (doesn't have the percentages that you specify above, but that is easily implementable) could be to use the votes table above and the songs table: CREATE TABLE songs ( songid INT UNSIGNED NOT NULL AUTO_INCREMENT, artistid INT UNSIGNED NOT NULL, songname char(75) NOT NULL, PRIMARY KEY (songid) ) Then you could use a query like: SELECT songname, RAND() * (AVG(votes.vote) + 10) AS rnd FROM songs JOIN votes ON votes.songid = songs.songid GROUP BY songs.songid ORDER BY rnd DESC LIMIT 1 This will generate random numbers between 0 and 8-13 depending on what the average vote for the song is, you can change the + 10 to any number to weigh them differently (although, if you use 0-2 votes with low averages might never be played since their values will always be negative). Might give you some new ideas if nothing else cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored proc containing subquery crashes mysqld-nt.exe
On Fri, 23 Jan 2004, Rob Kemmer wrote: > Hi, MySQL Team! > > I've downloaded and successfully installed v5.0 win2k, and am happily using stored > procs, but seem to be encountering problems with subqueries in stored procs. When I > run a stored proc containing a subquery, the first pass works, but a second > consecutive call to the proc crashes the server. > > Other than that, I haven't encountered any other problems. This sounds very similiar to bug #2460: http://bugs.mysql.com/bug.php?id=2460 cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function in Where clause
On Wed, 21 Jan 2004, Yves Arsenault wrote: > Thanks for your response, > > I'm using ColdFusion along side of MySQL to server dynamic content to some > websites. > > When I run the page that this code is in, I get an SQL syntax error: > Syntax error or access violation: You have an error in your SQL syntax near > '(theDate) = '31' and month(theDate) = '9' and year(theDate) = '2003'' > > The right values are displayed from ColdFusion server, but I keep getting > the syntax error. In 3.23 you have to use DAYOFMONTH(), DAY() isn't available til 4.1.1 cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ignore Replication Temp Tables
On Tue, 20 Jan 2004, Todd Burke wrote: > Is there any way to disable replication of all temp tables using > replicate-ignore-table or some other means? The names of the temp tables > are generated randomly by a script. Thanks If you could have all temporary tables starting with "tmp" or something like that (since the script generates the names it should be easy just to tag something at the start or end that makes them stand out), then you can use: (from manual: http://www.mysql.com/doc/en/Replication_Options.html) --replicate-wild-ignore-table=db_name.table_name Tells the slave thread to not replicate a query where any table matches the given wildcard pattern. To specify more than one table to ignore, use the directive multiple times, once for each table. This will work for cross-database updates. Please read the notes that follow this option list. Example: --replicate-wild-ignore-table=foo%.bar% will not do updates to tables in databases that start with foo and whose table names start with bar. Note that if you do --replicate-wild-ignore-table=foo%.% then the rule will be propagated to CREATE DATABASE and DROP DATABASE, that is, these two statements will not be replicated if the database name matches the database pattern (foo% here) (this magic is triggered by % being the table pattern). Escaping wildcard characters _ and %: see notes in the description of replicate-wild-do-table just above. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Increment value
On Tue, 20 Jan 2004, Mike Mapsnac wrote: > In the table value login_count is int(4). For example if value login_count > equal to 3 and each time user login I want to increment by one. > > update customer set login_count='how?" where id=12121212; SET login_count = login_count + 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped on a query
On Tue, 20 Jan 2004, Chris Boget wrote: > > > The data I'm working with looks like this: > > > table1.columnA = '1;3;4;6;8;9;12;13;14;15'; > > > table2.columnA = '3'; > > > table2.columnB = 'this'; > > > I need to write a query that will do something along these lines: > > > SELECT * FROM table1, table2 WHERE > > > table2.columnB = 'this' > > > AND > > > table1.columnA LIKE CONCAT( '%', table2.columnA, '%' ); > > AND > > INSTR(table1.columnA, table2.columnA) > > Well, the only problem with going this route is that if table1.columnA 's value > was, instead, '1,4,6,8,9,13,14,15', that row would still match (when it really > shouldn't) because of the '13' within the string. The '3' from table2.columnA > is part of the table1.columnA string. Oops INSTR(a, CONCAT(',', b, ',')) OR INSTR(a, CONCAT(b, ',')) = 1 OR INSTR(a, CONCAT(',', b)) = CHAR_LENGTH(a) - CHAR_LENGTH(b) might work better, sorry about that (replace a and b with appropriate columns). The first line checks if ,column, exists, which will be in all cases when it's not either first or last. The second line checks if it exists first in the commaseparated list, and the last line checks if it exists last. There's probably an easier way to do this, but since I submitted a faulty reply I should atleast make up for it ;) cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped on a query
On Tue, 20 Jan 2004, Chris Boget wrote: > The data I'm working with looks like this: > > table1.columnA = '1;3;4;6;8;9;12;13;14;15'; > > table2.columnA = '3'; > table2.columnB = 'this'; > > I need to write a query that will do something along these lines: > > SELECT * FROM table1, table2 WHERE > table2.columnB = 'this' > AND > table1.columnA LIKE CONCAT( '%', table2.columnA, '%' ); > AND INSTR(table1.columnA, table2.columnA) You can find more info about the INSTR function at: http://www.mysql.com/doc/en/String_functions.html#IDX1189 cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reading securely the value of an autoincrement field used as PK
On Tue, 20 Jan 2004, Marco Paci wrote: > Since the process of inserting a new record and reading its PK field > value is a two step process implemented by: > 1) insert into tablename (columnnames) values() > 2) select last_insert_id() > ,and since because of the architecture of my application I cannot > prevent that 2 concurrent insertions won't be done http://www.mysql.com/doc/en/LOCK_TABLES.html What will happen is: |Write-lock on table |Insert a new record | Write-lock on table (waiting...) | |Read the value of PK field |Unlocking table(s) | Write-lock gotten | Insert new record | Read the value of PK field | Unlock table(s) | | | v TimeThread 1 Thread 2 Axis cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Expressions
On Mon, 19 Jan 2004, Ian O'Rourke wrote: > Regarding the following query: > > SELECT ID,Author,DATE_FORMAT(EntryDate,'%d %m > %y'),SectionID,Title,Summary,Content FROM articles > ORDER BY EntryDate > DESC LIMIT 10 > > Okay, I've looked in the manually up and down, as I know how to do it in > Access, but I can't find it. I want to set an expression so I can give the > Date_Format function a handy name - so it returns the name of the column as > ArticleDate, for instance. > > I'm missing something simple :) SELECT ID,Author,DATE_FORMAT(EntryDate,'%d %m %y') AS something, SectionID, Title, Summary, Content FROM articles ORDER BY EntryDate DESC LIMIT 10 (The AS isn't mandatory, you can just state the alias if you want, the AS can be good for readability, however) cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table info
On Mon, 19 Jan 2004, Mike Mapsnac wrote: > I think that main disadvantage of this command is thah it works for the > database but not for specific table. So if a database has 200 tables, find > result for specific table is not an easy task. SHOW TABLE STATUS LIKE 'tablename'\G cheers, Tobias > > > > > >From: Victoria Reznichenko <[EMAIL PROTECTED]> > >To: [EMAIL PROTECTED] > >Subject: Re: table info > >Date: Mon, 19 Jan 2004 14:10:37 +0200 > > > >"Mike Mapsnac" <[EMAIL PROTECTED]> wrote: > > > > > Is it possible to find out when the table was created? How to get > >information about the table? > > > >SHOW TABLE STATUS command provides info about table: > > http://www.mysql.com/doc/en/SHOW_TABLE_STATUS.html > > > > > >-- > >For technical support contracts, goto https://order.mysql.com/?ref=ensita > >This email is sponsored by Ensita.net http://www.ensita.net/ > >__ ___ ___ __ > > / |/ /_ __/ __/ __ \/ /Victoria Reznichenko > > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > >/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net > ><___/ www.mysql.com > > > > > > > > > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > _ > Rethink your business approach for the new year with the helpful tips here. > http://special.msn.com/bcentral/prep04.armx > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ibdata1
On Sun, 18 Jan 2004, Hassan Shaikh wrote: > Hi, > > How do I resize (shrink & expand) InnoDB file? > You can read about the various InnoDB specific startup options here: http://www.mysql.com/doc/en/InnoDB_start.html cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table info
On Sat, 17 Jan 2004, Mike Mapsnac wrote: > Is it possible to find out when the table was created? How to get information about > the table? > You can use the unix command `stat`, the last of the 4 dates will be the creation date (in the order of Last Accessed, Last Modified, Last Inode modification, Birth Date of the file). Ex. [EMAIL PROTECTED]:/var/mysql/world# stat City.frm 1042 50097 -rw-rw 1 mysql mysql 230984 8702 "Dec 21 17:00:23 2003" "Dec 21 17:00:23 2003" "Dec 21 17:00:23 2003" "Dec 21 17:00:23 2003" 16384 20 City.frm In this case it shows that the table was created Dec 21. stat -f %B will give you only this data in a unixtime format, if that would suit you better. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dynamic no of columns
On Sun, 18 Jan 2004, Nitin Mehta wrote: > I need to select a column's value as different columns and other fields grouped by > those values. > > I want to select distinct values of reseller column as individual columns and other > data grouped on these values. Since the approach of tables usually are that the columns are more static than the number of rows, and the result you're striving for is that the aggregations are more static than the number of resellers, wouldn't it make more sense to use one row per reseller and one column per grouping? You could easily accomplish this with the SELECT ... CASE ... control flow function as documented at: http://www.mysql.com/doc/en/Control_flow_functions.html cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: File_priv syntax?
On Fri, 16 Jan 2004, Jough P wrote: > Greetings all, I'm trying to grant a user the file privilege and am > getting error messages. Here's my SQL statement > > mysql> GRANT file ON bs.table1 TO [EMAIL PROTECTED] IDENTIFIED BY > 'password123'; > > It gets the following error: > > ERROR 1144: Illegal GRANT/REVOKE command. Please consult the manual > which privileges can be used. the FILE privilege is a global privilege, and as such needs to be in the *.* context. It does not work on a db-level. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data loses connection
On Thu, 15 Jan 2004, Jamie Murray wrote: > Hi Guys, > after waiting about a minute I get > > ERROR 2013 (HY000): Lost connection to MySQL server during query > See if changing any of the SHOW VARIABLES LIKE 'net%timeout'; helps. Not sure why the crash popup comes up, however. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie - connect error
On Fri, 16 Jan 2004, tait sanders wrote: > yep I've already done this. > everything I do comes back with the same error: > ERROR 2002: Can't connect to local MySQL server through socket > '/tmp/mysql.sock' (2) > > I even deleted the mysql.sock and used 'mysql_config --socket' to > recreate it. > this produces a new mysql.sock but then trying to start mysql produces > the error of before. > > can i uninstall mysql rather than deleting it. I've tried the 'rpm' > utility but it's not on my os. > > thanks heaps for all your help. You can see if MySQL is actually running with ps. If it is running you can see what socket it's using with `netstat -a | grep mysql` You can try to connect through tcp/ip instead of the socket by using -h 127.0.0.1 at the commandline. If the server isn't up, the info should be in the error log why it doesn't like to start. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: setting a variable
On Wed, 14 Jan 2004, Ugo Bellavance wrote: > mysql 4.0.17 on redhat 9 or debian 3.0 > > mysql> show variables like 'log'; > +---+---+ > | Variable_name | Value | > +---+---+ > | log | ON| > +---+---+ > 1 row in set (0.00 sec) > > mysql> set global log=on; > ERROR 1193: Unknown system variable 'log' > > What am I doing wrong? Currently you cannot start logging while the server is running. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why: mysqldump and mysqlimport?
On Tue, 13 Jan 2004, EP wrote: > I am wondering: > > I can see the MySQL data files for my various databases. > > What technically prevents me from simply copying those files and using copies > - to move my database to another file structure or server > - to back-up my current db Copying will not work across certain versions, will also not work between system archtechtures, port to other RDBMS or if you do not have physical access to the files. For the cases above mysqldump works fine. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL_NO_CACHE
On Tue, 13 Jan 2004, Priyanka Gupta wrote: > Hi, > > I am trying to do some performance analysis by trying different indexing > schemes and testing how long it takes. To get consistent results, I would > like to use something like SQL_NO_CACHE. However, the mysqld version that I > have installed does not seem to support it..its 4.0.16 > > Could anyone suggest any other way of achieving this. > What does SHOW VARIABLES LIKE 'query_cache_type'; show? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.1.1a
On Mon, 12 Jan 2004, Viktor wrote: > Hello mysql, > > Table-level privileges do not work at all... (on Windows) Works fine for me: 4.1.1a-alpha-max-nt:tmp >GRANT SELECT ON tmp.tmp TO [EMAIL PROTECTED] IDENTIFIED BY 'aaa'; Query OK, 0 rows affected (0.18 sec) 4.1.1a-alpha-max-nt:tmp >\q Bye C:\mysql\4.1-tree\bin>.\mysql -u tmp -p -P 3308 Enter password: *** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.1.1a-alpha-max-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 4.1.1a-alpha-max-nt:(none) >SELECT * FROM tmp.tmp; +--+ | a| +--+ |1 | +--+ 1 row in set (0.42 sec) 4.1.1a-alpha-max-nt:(none) >SELECT * FROM tmp.meep; ERROR 1142 (42000): select command denied to user: 'tmp'@'localhost' for table 'meep' 4.1.1a-alpha-max-nt:(none) > cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange ORDER BY question
On Mon, 12 Jan 2004, Lewis, Jason wrote: > Okay I have a field in my db called MemberLevel in this field you can be one of 5 > levels. > > Platinum > Gold > Silver > Paying > Non-Paying > > > now my question is, how would I ORDER BY MemberLevel and get it to come out in the > above order? I have been racking my brains for a week on this one and any help will > be appreciated. > Can think of 3 options offhand. 1. ENUM() and order by enumcol, this will order them in the order you specify them in the ENUM('col1', ...) order. 2. Break it out into another table andgive them ids in the order they should be sorted 3. use a CASE statement: SELECT col, CASE col WHEN 'Platinum' THEN 1 WHEN 'Gold' THEN 2 ... END AS sortby ORDER BY sortby cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: my.ini file for two instances of MySql - need help
> Hello, > > What do I need to add to the my.ini file? Can anyone post a typical setup > of this as an example? I install with mysqld-nt-max --install servicename servicename in those cases are MysQL40, MySQL41 and MySQL50 Those are the relevant rows: [mysqld] [mysql40] basedir = C:/mysql/4.0-tree/ datadir = C:/mysql/4.0-tree/data port= 3307 [mysql41] basedir = C:/mysql/4.1-tree datadir = C:/mysql/4.1-tree/data port= 3308 [mysql50] basedir = C:/mysql/5.0-tree datadir = C:/mysql/5.0-tree/data port= 3309 cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup my database
On Mon, 12 Jan 2004, Alaios wrote: > Hi there. Do u know how can i backup my database? > (create && insert) http://www.mysql.com/doc/en/Backup.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: field reference question
On Mon, 12 Jan 2004, Ed Lazor wrote: > Is there an abstract way to refer to field names in a result set? > select ID, Name, Price from Products order by field(1) > where "field(1) would be the abstract way of referring to the specific field to sort > on. ORDER BY # Where # is the number of the column counting from left to right starting at 1. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Resetting Auto_Increment
On Mon, 12 Jan 2004, Hassan Shaikh wrote: > Hi, > The following does not work for InnoDB tables. The manual says "The next > AUTO_INCREMENT value you want to set for your table (MyISAM). " > ALTER TABLE AUTO_INCREMENT = ; > > Any suggestions for InnoDB? Insert a row with a custom value, then delete it, the next value insrted will be value+1 of the value you juse inserted. Example below: [EMAIL PROTECTED]:tmp > CREATE TABLE ai (num INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARy KEY) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) [EMAIL PROTECTED]:tmp > INSERT INTO ai VALUES (NULL), (NULL), (NULL); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 [EMAIL PROTECTED]:tmp > SELECT * FROM ai; +-+ | num | +-+ | 1 | | 2 | | 3 | +-+ 3 rows in set (0.00 sec) [EMAIL PROTECTED]:tmp > INSERT INTO ai VALUES(10); Query OK, 1 row affected (0.00 sec) [EMAIL PROTECTED]:tmp > DELETE FROM ai WHERE num=10; Query OK, 1 row affected (0.01 sec) [EMAIL PROTECTED]:tmp > INSERT INTO ai VALUES(NULL); Query OK, 1 row affected (0.00 sec) [EMAIL PROTECTED]:tmp > SELECT * FROM ai; +-+ | num | +-+ | 1 | | 2 | | 3 | | 11 | +-+ 4 rows in set (0.00 sec) [EMAIL PROTECTED]:tmp > \t -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reference to a command that I can not find AND Foriegn Key information
On Tue, 6 Jan 2004, Luc Foisy wrote: > There was a user comment under the Foriegn Key section of the documentation reading: > To restore from a mysqldump file that uses foreign keys: > > mysql> SET FOREIGN_KEY_CHECKS = 0; > mysql> SOURCE your_dump_file; > mysql> SET FOREIGN_KEY_CHECKS = 1; > > The command I am looking for is the call SOURCE. Where is this in the documentation? http://www.mysql.com/doc/en/Batch_Commands.html > Also, will this FOREIGN_KEY_CHECKS ignore the definitions while creating the tables, > as in not create them, or just merely not check for its consitancy? Just doesn't check if there's a valid value in the parent table that it refers to and will not give an error. > Is any of the above limited to the InnoDB type? Both -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1 Production Release
On Tue, 6 Jan 2004, Knepley, Jim wrote: > Any chance that there's a quarterly strategic roadmap published > somewhere? > > I have projects that sometimes depend on a feature in the next rev' or > some such, and I need to plan out for my organization... Difficult to > answer my boss when the dependencies are released "when they're ready." > This is a snip from the 5.0.0 release message: "THe MySQL 4.1 branch seams to be relatively stable and we will, if we don't find any new unexpected hard bugs that will require new design decisions, make a beta release of 4.1 in January followed by a gamma release ASAP." cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Current server charset?
On Tue, 6 Jan 2004, Martijn Tonies wrote: > Hi, > > In MySQL 4.1, is there a function to know what the > current default server-wide characterset is? You can find all those with: SHOW VARIABLES LIKE 'character_set%' cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 Production Release
On Mon, 5 Jan 2004, Allen Weeks wrote: > Hi All, > > Just a quick question, does anyone have a good estimate of when ver 4.1 will > go production. When known bugs are fixed. You can read up on MySQL's release policy here: http://www.mysql.com/doc/en/Release_philosophy.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pulling numbers out of a column for a query
On Mon, 5 Jan 2004, Hal Vaughan wrote: > I've found an odd problem in queries. > > I have a lot of data regarding vehicle speeds in a survey. All the data is in > the form: xx/yy, for example 43/55 means that vehicle was clocked at 43 miles > per hour in a 55 miles per hour zone. 80/55 means we have a serious speed > demon, going 80 in a 55 zone. > > I need to be able to pull out data depending on speeds in relation to the > speed limit, for example, find out how many are less than 55/55 or 45/45, or > how many are more than 5 miles over (like 61/55 or 32/25). mysql> SELECT SUBSTRING_INDEX(speed, '/', 1) AS clocked, -> SUBSTRING_INDEX(speed, '/', -1) AS zone, ... -> WHERE SUBSTRING_INDEX(speed, '/', 1) > SUBSTRING_INDEX(speed, '/', -1) AS zone That is for finding all the speeders, for example. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL certification
On Mon, 5 Jan 2004, Douglas Sims wrote: > The test was a bit harder than I anticipated. I should have paid more > attention to column types and database name, among other things. But I > did pass - at least, the preliminary report said pass, but also said > that the exam will be reviewed and "If you have met the passing score, > you will receive your official certificate by mail." This puzzles me, > because this exam isn't beta any more? It's the Professional exam that is in beta. Once you say you're done reviewing you should get a PASS or FAILED on the screen imediately. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get counts of col=value with an GROUP BY clause?
On Mon, 5 Jan 2004, Greg Owen wrote: > I tried (you can laugh here) to do it this way, but failed miserably: > > mysql> select class,count(questnum),count(difficulty='0'), >count(difficulty='1'),count(difficulty='2'), >count(in_use='0'),count(in_use='1') from Questions >group by class; > You can use SELECT class, COUNT(questnum), SUM(IF(difficulty = 0, 1, 0)), SUM(IF(difficulty = 1, 1, 0)), ... cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Method for Learning mysql
On Mon, 5 Jan 2004, Marc Dver wrote: > Based on the collective experiences of the members of this group, what > are the best methods for learning mysql, both from the perspective of > certification and of learning enough to excel in the production > environment? My interests include both the specifics of mysql and the > general concepts behind the use and administration of databases in > general. I really like Paul DuBois' book; MySQL The Core certification is a pretty good way to test yourself, and quite a few I've talked to that has taken it said that just by cramming for the actual certification exam they learned lots of new things and little tips. As for general concepts C.J Date's 'An Introduction to Database Systems' is by many considered the bible for relational databases. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert: auto increment field
This bug was fixed to 4.0.17, it happened when you inserted a negative value manually into an auto_increment column for more info see: http://bugs.mysql.com/bug.php?id=1366 On Mon, 5 Jan 2004, Donald Henson wrote: > Please post your table schema. As to why rather than zero, I'll > have to defer to the experts. > > Don Henson > > On Sun, 2004-01-04 at 16:52, Mike Mapsnac wrote: > > I use both metods and they works But when I make another insert I receive a > > message > > ERROR: 1062 Duplicate entry '2147483647' for key 1. > > > > Why id (primaty , and auto_increment) start from 2147483647 and not from 0 > > or 1 > > > > Thanks cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: struggling newbie - datetime or timestamp problem
On Mon, 5 Jan 2004, Matthew Stuart wrote: > I am trying to create a couple of columns (one createddate and one > updateddate) in a MySQL table that are DATETIME or TIMESTAMP values, > but I am having trouble understanding how it works. I want both columns > to auto add a date and time when a record is first inserted, but only > the updateddate column to update when the record is updated. I would use a DATETIME for the created date, and a TIMESTAMP for the changed. The reason being that the creation date is only inserted once, and it won't change, and changed using TIMESTAMP will automaticly update when the record is changed, so no complex application logic here (well, maybe not very complex, but I won't have to think about it, it's MAGIC!). > Could someone give me the code for these two columns please? This is > what I have: > > CREATE TABLE all_articles ( > id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, > createddate DATETIME DEFAULT "-00-00 00:00:00" > updateddate DATETIME DEFAULT "-00-00 00:00:00" > article TEXT > ); CREATE TABLE all_articles ( id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, createddate DATETIME DEFAULT '-00-00 00:00:00', updateddate TIMESTAMP, article TEXT ); Should work. > Obviously these aren't going to auto add/update because I am not stating > a NOW() or NULL value anywhere, but I thought this would be the cleanest > way to give it to you. The way the zeros, hyphens and colons are typed > is how I would like to store my dates if at all possible. The only issue here is that when you insert a new article you need to set the createdate column to NOW(). ie, INSERT INTO all_articles (createddate, article) VALUES (NOW(), 'Article text goes here'); cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replicating Table Schema
On Fri, 2 Jan 2004, Roger Baklund wrote: > * Gohaku > > I was just curious if there's a shorthand way of replicating a Table > > Schema. > > I use the following to create a new Table with the same schema. > > >create table new_table ( select * from table); > > >delete from new_table; > > You can avoid the DELETE by specifying a non-true WHERE clause: > > CREATE TABLE new_table SELECT * FROM table WHERE 1=0 > > http://www.mysql.com/doc/en/CREATE_TABLE.html > > Do, however, note that this does not copy the index-structure, nor attributes like auto_increment. You can use CREATE TABLE table_copy LIKE table_original Or if you want the whole create statement just to store can use mysqldump from outside the client or use SHOW CREATE TALE table_original -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change from loop to single query
On Fri, 2 Jan 2004, Jonathan Villa wrote: > I have a loop which is similar to the following: > > while(array contains elements) { > UPDATE users SET status = no WHERE name = array[i] > } > great, it works but the query runs many times. I want to make only one > call to the database and have all the elements in the array be included > in the query UPDATE users SET status = no WHERE name IN('name1', 'name2', 'name3', ..., 'namen') you might still have to loop to remake your array to a commaseparated list of strings, but it will still be boatloads more efficient. If you have several hundred thousands or more of names and get an error, try upping your max_allowed_packet variable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Roles
On Fri, 2 Jan 2004, Caroline Jen wrote: > In case that a user has multiple roles; for example, > John Dole is both author and editor, > > 1. I should have two rows for John Dole? > >John Dole author >John Dole editor > >or. I should have only one row and use comma ',' to > >separate the roles? > >John Dole author, editor > > 2. How do I create the table for the second case (see > below)? > > create table user_roles ( > user_name varchar(15) not null, > role_name varchar(15) not null, varchar(15) null > ); > If the roles will not be very dynamic and could be hardcoded you might be able to use the SET datatype which is described here: http://www.mysql.com/doc/en/SET.html If you will add/update/change/delete roles often, then you should go for one of the other methods suggested instead, but if the roles are static, this might work better for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Time series
http://www.mysql.com/doc/en/example-Maximum-column-group-row.html might help you do what you're looking for. On Thu, 1 Jan 2004, Schulman, Michael wrote: > As far as I know min(price) and max(price) will return the lowest and higest > price, not the first and last in the group. Again I know first and last > break the paradaigm of SQL's bucket mentality but it is crucial to doing > timeseries analysis. And timeseries aggregation as the query I gave is > trying to do. > > Thanks again, > Mike > > -Original Message- > From: Fredrick Bartlett [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 01, 2004 10:12 PM > To: Schulman, Michael; [EMAIL PROTECTED] > Subject: Re: Time series > > > Is Hour a DateTime? If so, will this work... > > SELECT ticker, DATE_FORMAT(Hour,'%H' ), min(price), max(price) > from pricedata > order by DATE_FORMAT(Hour,'%H' ) > group by DATE_FORMAT(Hour,'%H' ) > > > - Original Message - > From: "Schulman, Michael" <[EMAIL PROTECTED]> > To: "'Fredrick Bartlett'" <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]> > Sent: Thursday, January 01, 2004 6:59 PM > Subject: RE: Time series > > > > That only returns one number.. what we are really looking for is something > > like > > > > SELECT ticker, hour, first(price), last(price) from pricedata group by > hour > > > > Sorry for the confusion. > > > > Thanks, > > Mike > > > > -Original Message- > > From: Fredrick Bartlett [mailto:[EMAIL PROTECTED] > > Sent: Thursday, January 01, 2004 9:57 PM > > To: Schulman, Michael; [EMAIL PROTECTED] > > Subject: Re: Time series > > > > > > Hmmm... > > First: select * from table1 order by field1 asc limit 1 > > Last: select * from table1 order by field1 desc limit 1 > > > > > > - Original Message - > > From: "Schulman, Michael" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Thursday, January 01, 2004 6:47 PM > > Subject: Time series > > > > > > > Hi, > > > > > > I work for a large financial instituition. We are currently evaluating > > > databases to store intraday stock data. These are large tables with 40 > > > million rows per day. We've done some initial testing with MySQL and > have > > > been extremely impressed with its speed and ease of use. I know that it > > > goes agains the SQL standard but adding a FIRST,LAST aggregate function > > > along with some other time series functions would allow mysql to compete > > > with just about any timeseries database, and open up mysql to a huge > > market > > > of financial firms. I know my firm would most likely purchase it. > Has > > > anyone developed anyhting like this as an add on? > > > > > > Thanks, > > > Mike > > > > > > > -- > > > > > This message is intended only for the personal and confidential use of > the > > > designated recipient(s) named above. If you are not the intended > > recipient of > > > this message you are hereby notified that any review, dissemination, > > > distribution or copying of this message is strictly prohibited. This > > > communication is for information purposes only and should not be > regarded > > as > > > an offer to sell or as a solicitation of an offer to buy any financial > > > product, an official confirmation of any transaction, or as an official > > > statement of Lehman Brothers. Email transmission cannot be guaranteed > to > > be > > > secure or error-free. Therefore, we do not represent that this > > information is > > > complete or accurate and it should not be relied upon as such. All > > > information is subject to change without notice. > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > -- > > > This message is intended only for the personal and confidential use of the > > designated recipient(s) named above. If you are not the intended > recipient of > > this message you are hereby notified that any review, dissemination, > > distribution or copying of this message is strictly prohibited. This > > communication is for information purposes only and should not be regarded > as > > an offer to sell or as a solicitation of an offer to buy any financial > > product, an official confirmation of any transaction, or as an official > > statement of Lehman Brothers. Email transmission cannot be guaranteed to > be > > secure or error-free. Therefore, we do not represent that this > information is > > complete or accurate and it should not be relied upon as such. All > > information is subject to change without notice. > > > > > -- > This message is intended only for the personal and confidential use of the > designated recipient(s) named above. If you are not the intended recipient of >
Re: One Slave Many Master
On Mon, 29 Dec 2003, Leo wrote: > I know someone already ask this, > and the answer generally 'NO YOU CANT' :b > > but, is there any work around so i can make a backup server (slave), > from many other server (master) through replication? You could run a server instance per database replicated and use the same data-dir (be careful of conflicting writes, however, you probably want to enable-external-locking, especially if they share any data). > oh, one more thing, can the replication run both way? > what ever happened on machine A, replicated on machine B and vice versa.. That works fine, just be aware of the problems that can arise if this is not taken into consideration since the replication is asynchronous read the Q: What issues should I be aware of when setting up two-way replication? part at http://www.mysql.com/doc/en/Replication_FAQ.html > > thanks... > > -- > Regards > Leonardus Setiabudi > IT Project Coordinator > PT Bina San Prima, www.binasanprima.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Question
On Mon, 22 Dec 2003, Jeff McKeon wrote: > Is it possible to have 2 database on one server replicating from the > same Master server? yes. > In other words. DB01 is the Master on System01, > System02 has DB01_rep1 and DB01_rep2, each with their own replication > from DB01. Shouldn't be a problem. > I need to do this to have a development copy of DB01 on System02. I > have production software that pulls data > from DB01_rep1 and I need to set up DB01_rep2 to do some development > work. > > When I copy the DB01 snapshot into a new database (DB01_rep2) on > System02 and then log into that database and do a "Show slave status" it > shows me the slave status information that was set up for DB01_rep1. Just make sure that you're not running against the same logfiles/data, so you can have different relay-logs and master.info files. > Is replication System dependant or database dependant? Neither, it's server instance dependant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can't start the server
Can the user the mysqld process run as (usually the mysql user) read/write the /var/lib/data directory without problems? Does your /etc/my.cnf file contain a datadir = /var/lib/mysql or is it pointing somewhere else? Did adding skip-innodb to your my.cnf solve anything? On Fri, 19 Dec 2003, landon kelsey wrote: > I run safe_mysqld out of /usr with ./bin/safe_mysqld & > > /var/log/mysqld.log > > 868 031219 20:52:52 mysqld started > 869 Cannot initialize InnoDB as 'innodb_data_file_path' is not set. > 870 If you do not want to use transactional InnoDB tables, add a line > 871 skip-innodb > 872 to the [mysqld] section of init parameters in your my.cnf > 873 or my.ini. If you want to use InnoDB tables, add to the [mysqld] > 874 section, for example, > 875 innodb_data_file_path = ibdata1:10M:autoextend > 876 But to get good performance you should adjust for your hardware > 877 the InnoDB startup options listed in section 2 at > 878 http://www.innodb.com/ibman.html > 879 031219 20:52:52 /usr/libexec/mysqld: Can't find file: > './mysql/host.frm' (errno: 13) > 880 031219 20:52:52 /usr/libexec/mysqld: Normal shutdown > 881 > 882 031219 20:52:52 mysqld ended > > I've even tried moving host.frm around. > > The manual says that mysql_install_db may not have run or to try > > ./bin/safe_mysqld & > > I've tried all combinations of the above. > > ps -ef | grep -i sql shows nothing so the server is NOT running > > mysql does not work > ERROR 2002: Can't connect to local MySQL server through socket > '/var/lib/mysql/mysql.sock' (2) > > .. PREVIOUS HISTORY > (1) performed a standard install of Redhat Linux 9 months ago > (I found mysql 3.23.52 installed) > I executed mysql_install_db successfully (manual says it is safe to > rerun) > installing all prepared tables > 031219 0:44:16 /usr/libexec/mysqld: Shutdown Complete > > > To start mysqld at boot time you have to copy > support-files/mysql.server > to the right place for your system > > PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! > This is done with: > /usr/bin/mysqladmin -u root password 'new-password' > /usr/bin/mysqladmin -u root -h landon password 'new-password' > See the manual for more instructions. > > NOTE: If you are upgrading from a MySQL <= 3.22.10 you should run > the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be > able to use the new GRANT command! > > You can start the MySQL daemon with: > cd /usr ; /usr/bin/safe_mysqld & > > You can test the MySQL daemon with the benchmarks in the 'sql-bench' > directory: > cd sql-bench ; run-all-tests > > Please report any problems with the /usr/bin/mysqlbug script! > > The latest information about MySQL is available on the web at > http://www.mysql.com > Support MySQL by buying support/licenses at https://order.mysql.com > > > (2) safe_mysqld & produces > landon (root) /usr/bin [311]>safe_mysqld & > [1] 3247 > landon (root) /usr/bin [312]>Starting mysqld daemon with databases > from > /var/lib/mysql > 031219 00:25:56 mysqld ended > > [1]+ Donesafe_mysqld > > (3) tried debug > landon (root) /usr/bin [316]>!305 > cat /var/lib/mysql/xxx.log > /usr/libexec/mysqld, Version: 3.23.52-log, started with: > Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock > Time Id CommandArgument > > (4) here is what mysql.sock looks like > landon (root) /usr/bin [303]>lsa /var/lib/mysql > total 20 > drwxr-xr-x4 mysqlmysql4096 Dec 18 23:42 ./ > drwxr-xr-x 29 root root 4096 Jul 8 18:31 ../ > drwx--2 root root 4096 Dec 18 21:21 mysql/ > srwxrwxrwx1 mysqlmysql 0 Dec 18 23:42 mysql.sock= > drwx--2 root root 4096 Dec 18 21:21 test/ > -rw-rw1 mysqlmysql 156 Dec 18 22:52 xxx.log > > (5) also tried ./bin/safe_mysqld --user=mysql & > same result > > (6) tried > safe_mysqld --debug & > couldn't find the mysqld.trace file > > (7) mysql doesn't work > ERROR 2002: Can't connect to local MySQL server through socket > '/var/lib/mysql/mysql.sock' (2) > > _ > Grab our best dial-up Internet access offer: 6 months @$9.95/month. > http://join.msn.com/?page=dept/dialup > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto increment using even numbers
On Fri, 19 Dec 2003, K Q-B wrote: > I am creating a table and would like to use auto > increment, but I would like one column to increment in > only odd numbers 1,3,5... and another column of the > same table to increment in even numbers 2,4,6... > > Is it possible to do this? Not without a little tinkering. You can use a separate table for just the counters, when you need a new id generated, you can insert a value into it, then when inserting into the real table you can use INSERT INTO table SET id_col = LAST_INSERT_ID() * 2 ... (or INSERT INTO table (id_col, ...) VALUES (LAST_INSERT_ID() * 2, ...); ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to get value of autoincriment after inserting NULL?
On Mon, 15 Dec 2003, Paul Fine wrote: > If I have a table like with a column being the PK for the table and being an > Auto Increment value, what is the best way to return this value to my > script? If you insert a row LAST_INSERT_ID() will return the primary key value in this setup. The other way is if you're not inserting a record, but just want to know the next value that will be used. SHOW TABLE STATUS LIKE ''; > It is possible that additional rows may have been added during the > small wait. It is possible another value is inserted in between the query for the next number and an insert afterwards. To stop this from happening you can place a read-lock on the table, this won't allow any other processes to insert rows until you release the lock. LOCK TABLE READ; Get Auto-increment value Do your stuff... UNLOCK TABLES; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting data
On Mon, 15 Dec 2003, Roberts, Mark (Tulsa) wrote: > I have an order taking system where the tables are store in a MySql database. I need > to develop a select statement to output all new orders to a .csv formatted file. > > Is this possible to do in MySql. I would try looking this up, however, I am not even > sure what to look for in the documentation. Any help that I could get to lead me in > the right direction would be appreciated. Thanks. > > Mark Roberts > > http://www.mysql.com/doc/en/SELECT.html Check out the bit about INTO OUTFILE. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
> Sven Köhler wrote: > > >> I set the isolation level to READ_REPEATABLE and use mysqldump | > >> bzip2 to get the result. I've tested the restore and it's fine! > > > > > > So how does mysqldump handle binary data? > > > > If it does embed the data into the SQL-statement somehow, that's crap, > > since SQL-Statements are limited in length. On Tue, 16 Dec 2003, Chris Nolan wrote: > Are they? Shoving in rows that are several meg in size didn't pose any > problems. The restore procedure looked like this: > > bunzip2 dumpfile | mysql -u db_grunt -p projectdb > > May I ask where the limitation you mentioned is documented? Maybe the > situations we were using it in didn't come close to the limit. > > Regards, > > Chris This is limited by the max_packet_size variable. In 3.23.x it's limited to 16Mb, in 4.0+ it is limited by 2Gb or the amount of physical memory the machine has, whichever is less. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE..
On Mon, 15 Dec 2003, Graham Little wrote: doing selective quoting below. > LOAD DATA INFILE "D:\mysql\sql\CountryData.txt" > INTO TABLE cou (id, country); See how you try to load from a file into the columns id and country in the cou table? > The table the data is being inserted into is: > > +--+-+--+-+-+---+ > | Field| Type| Null | Key | Default | Extra | > +--+-+--+-+-+---+ > | cou_id | int(11) | | PRI | 0 | | > | cou_name | text| | | | | > +--+-+--+-+-+---+ > And those columns doesn't exist. > I am getting the following error message and i am not > sure why? > > Error 1054: Unknown column 'id' in 'field list' > > > Any help would be appreciated. Hope that helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SEQUENCES
On Mon, 15 Dec 2003, Peter Lovatt wrote: > Try > > Insert INTO `table` ( `inc_field` ) values (10) > > the auto inc field will then generate the next sequential numbers > > HTH > > Peter Or just use ALTER TABLE table AUTO_INCREMENT=10 That way you don't have to enter a record just to set the AUTO_INCREMENT value. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting MySql Port
On Wed, 10 Dec 2003, Gavin Dimmock wrote: > Hi All, > > I want to change the default port on mysql server from 3306 to 5 (for > example). The server is NT. Has anyone done this before? > > Any help really appreciated, > Can either modify the service to start with the --port parameter or add in your my.cnf (or my.ini, whichever you're using) the line port = 5 under the [mysqld] section. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication/binary log
On Tue, 9 Dec 2003, Mayuran Yogarajah wrote: > Diana Soares wrote: > > >Use "PURGE {MASTER|BINARY} LOGS TO 'log_name'" instead of "RESET > >MASTER". > >>From the manual: > > > >" > >Deletes all the binary logs listed in the log index that are strictly > >prior to the specified log or date. The logs also are removed from this > >list recorded in the log index file, so that the given log now becomes > >the first. > >(...) > >You must first check all the slaves with SHOW SLAVE STATUS to see which > >log they are reading, then do a listing of the logs on the master with > >SHOW MASTER LOGS, find the earliest log among all the slaves (if all the > >slaves are up to date, this will be the last log on the list), backup > >all the logs you are about to delete (optional) and purge up to the > >target log. > >" > >http://www.mysql.com/doc/en/PURGE_MASTER_LOGS.html > > > > > > > > That worked quite nicely, thank you :) Is there some reason why > MySQL keeps these log files ? Why wouldn't it delete them as a > new one got created? > > thanks > You can also use those to restore a database that is lost somehow. Just run all the binary logs and pipe them into mysql from the last "snapshot" you have taken a backup from. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: >= not working?
You have a few ways to do this. What's happening here is that you do a comparison in a string context, which means that it will sort according to the ascii values, and 1 comes before 8. To sort the way you want you need to specify to MySQL that you want to do it in a numeric context. You have two simple ways to do this. Add 0 to the string number to force it into numeric mode. AND squarefeet >= '$squarefeet' + 0 use the CAST() function, it's described more at: http://www.mysql.com/doc/en/Cast_Functions.html On Mon, 8 Dec 2003, Ed Curtis wrote: > > I've got an entry in a table where the value is 875. If I run a query on > that table with the clause AND sqaurefeet >= '$squarefeet' and $squarefeet > has a value of say 1000 the row with the squarefeer value of 875 will > appear. The column is of type varchar(10) and using the binary flag. > > What am I doing wrong? > > Thanks, > > Ed > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Once again, three queries, same result, huge speed difference
On Thu, 4 Dec 2003, Uros Kotnik wrote: > I posted this few days ago, but with no answer, also posted it to > benchmark list.. > > Executing this SQL, takes ~5 sec. > > select artists.name , cds.title , tracks.title from artists, tracks, > cds > where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid > and MATCH (artists.name) AGAINST ('madonna') > and MATCH (cds.title) AGAINST ('music') > and MATCH (cds.title) AGAINST ('mix') > and MATCH (cds.title) AGAINST ('2001') > > and this, ~40 sec. > > select artists.name , cds.title, tracks.title from artists, tracks, cds > where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid > and MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE) > and MATCH (cds.title) AGAINST ('"music mix 2001"' IN BOOLEAN MODE) > > and executing this takes less than 1 sec. > > select artists.name , cds.title, tracks.title from artists, tracks, cds > where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid > and artists.name like '%madonna%' > and cds.title like '%music mix 2001%' > > > Same result but the speed difference is quite a different, why is that ? > > This is only on test DB, I didn't try it on real life DB where I have > ~14 mil. rows in tracks table. > > > Regards > Can you post EXPLAIN SELECT of those queries as well, please? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can't find FULLTEXT index
If you do a fulltext search on multiple columns at once, there must be a combined fulltext index on this exact set of columns. Just having an index on them individually will not work. On Wed, 3 Dec 2003, Mirza wrote: > Hi, > > I have error 1191 "can't find fulltext index matching the column list", > but I am certain that all columns in the list are present in my Fulltext > index. Does anybody else had this problem? I am using 4.1.0. > > thanks, > Mirza > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to 'customize' GROUP BY?
I think that http://www.mysql.com/doc/en/example-Maximum-column-group-row.html covers your problem here. You either have to solve it with Temporary tables, the MAX-Concat trick (in the url above) or a subquery (which will be more inefficient than the other two options). On Thu, 20 Nov 2003, Yves Goergen wrote: > Hi again... > yet another question to this list that maybe someone can easily answer me... > > When I do a GROUP BY on a column of my query, I'll get one random row from > the entire group. But how can I tell MySQL to, i.e., give me the row with > the highest value of another column or so? I mean something like > > SELECT id, grp FROM tbl GROUP BY grp ORDER BY id > > but with the ORDER BY relating to the GROUP... I don't know how to express > this in SQL since it doesn't seem to be possible? > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Missing mysql.sock
On Sat, 8 Nov 2003, Michael Satterwhite wrote: > This has got to be a common question, but I'd really appreciate a little help. > > I recently reinstalled my Linux (SuSE 8.2). I *KNOW* I don't have a cron run > that deletes this. > > When I try to start mysql, I get the message > > "Can't connect to local MySQL server through socket > '/var/lib/mysql/mysql.sock' (2)" > > Using locate, I see that, indeed, it is not there. I'm using MySQL 4.1.0 > > I've tried running mysqld_safe. It tries to start the mysqld, then I > immediately get the message that mysqld ended. It doesn't start. > > How do I get it going again? If your server is not running (check with a `ps -aux` call) then check your error logs. If the server IS running however, it's likely that your client tries to connect through /var/lib/mysql/mysql.sock while the server listens somewhere else. To find out if that is the case, run `netstat -u | grep "mysql.sock"` and see where the server is listening. If the server is listening on, for example /tmp/mysql.sock , then you can make your client use that by editing your ~/.my.cnf (or /etc/my.cnf, or any of the other locations possible) with the row socket = /tmp/mysql.sock under the [client] group (or change the server's by entering the same row with the other path under the [mysqld] group). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on SELECT support
MySQL doesn't support TOP, however, there's a LIMIT syntax for MySQL that roughly does the same thing: http://www.mysql.com/doc/en/SELECT.html for a brief explanation. In your example what you are looking for is: SELECT DateCreatedField FROM my_table ORDER BY DateCreatedField DESC LIMIT 10; On Fri, 17 Oct 2003, Dale Hans wrote: > Hi, > > I am trying to select the first 10 rows of a table by the date the > record was created. I have a date field in the table and I tried using > the TOP syntax of SELECT, but I keep getting syntax error. > > SELECT TOP 10 DateCreatedField > FROM my_table > ORDER BY DateCreatedField DESCENDING > > Does MySQL not support TOP? > > If it does, can somebody tell me what is wrong with that syntax? > > If it doesn't, can someone please tell how I can do it without getting > ALL records and extracting the first 10 programmatically? > > Thanks, > Dale Hans > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql max
It depends on your filesystem's and OS's max-size of a file in its filesystem. HOWEVER, there are a few ways you can get around this. You can use InnoDB tables, or read about RAID types here: http://www.mysql.com/doc/en/CREATE_TABLE.html (almost at the bottom of the page). You can also use MERGE tables: http://www.mysql.com/doc/en/MERGE.html On Fri, 17 Oct 2003, nm wrote: > Hi > > what's the max table size we can use with mysql-max version? > > Suggestions? > > Thanks. > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: natural sorting
If you have 4.0.2 or later you can use the CAST() function, if you have earlier you should be able to emulate it with the BINARY keyword for the ORDER BY clause, examples below: [EMAIL PROTECTED]:tmp > CREATE table sort ( -> num int -> ); Query OK, 0 rows affected (0.00 sec) [EMAIL PROTECTED]:tmp > INSERT sort (num) VALUES (1), (2), (3), (10), (11); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 [EMAIL PROTECTED]:tmp > SELECT num FROM sort ORDER BY num; +--+ | num | +--+ |1 | |2 | |3 | | 10 | | 11 | +--+ 5 rows in set (0.00 sec) [EMAIL PROTECTED]:tmp > SELECT num FROM sort ORDER BY BINARY num; +--+ | num | +--+ |1 | | 10 | | 11 | |2 | |3 | +--+ 5 rows in set (0.00 sec) [EMAIL PROTECTED]:tmp > SELECT num FROM sort ORDER BY CAST(num AS CHAR); +--+ | num | +--+ |1 | | 10 | | 11 | |2 | |3 | +--+ 5 rows in set (0.00 sec) On Thu, 16 Oct 2003, Michael Winston wrote: > Is there any way to do "natural sorting" in MySQL? That is: > Chapter 1 > Chapter 2 > Chapter 10 > > instead of > > Chapter 1 > Chapter 10 > Chapter 2 > > ? > > If not, are there any plans for this feature in future releases? > Would be pretty helpful... > > Michael > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Command line fails "mysql mydb < mufile.sql"
http://www.mysql.com/doc/en/Option_files.html Check the example halfway down the page, it shows how you can supply a password through your my.cnf file. > I can log in as -u administrator when I use the -p option. I need to > make it work > from command line because I am planning to put this command in > a script to make it automatic to insert records. > > Nestor :-) > > Nestor A. Florez > > > >>> Tobias Asplund <[EMAIL PROTECTED]> 10/15/2003 12:02:21 PM >>> > > > c:\>mysql mydb < myfile.sql > > ERROR 1044: Access denied for user: '@localhost' to database 'mydb' > > try mysql -u administrator mydb < myfile.sql > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repairing a large table takes weeks!
About two weeks ago I received "The table Worklist is full" error. Since what type of table? MyISAM? how big is 'full'? "If you don't specify any of the above options, the maximum size for a table will be 4G (or 2G if your operating systems only supports 2G tables)." i dont know how big files can be on NTFS/WinXP. -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Suboptimal index-usage with FULLTEXT-search
column is used in the where-clause, The MySQL optimizer will ALWAYS choose the FULLTEXT-index since it is assumed that this will match only 1 row (always 1 row). I understand that it is very hard to do a correct analysis and estimation of rows for a BOOLEAN FULLTEXT-search, but to assume that it will always return 1 row is not at all optimal in my opinion! If MySQL instead assumed - say 1000 rows - we would get far better results in most situations. Then we would use another index if it would result in less rows than 1000 (not so many in my large table). If that assumption would be incorrect, the worst thing that could happen would be that another index (with less that 1000 possible rows) would be used instead even though the fulltext-index would have been better. But that's not so bad - we would scan maximum 999 rows with the use of another index - no problem! ...not compared to my 100.000 rows scans that's the result of picking a fulltext-index when it gives a lot of matches... The best solution would of course be that MySQL could estimate the number of possible rows from the boolean fulltest-search. I guess that this is impossible. The next best thing would be to be able to manually configure the minimum estimated number of rows from a boolean search as a hint to the MySQL optimizer... Right now this estimation is always 1, I'd like to set it higher!! Best regards, Tobias Lind -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE sales.customer_id = 10 AND sales.sale_id IS NULL This query should return no rows, because if you retrieve rows where sales.sale_id is NULL, customer_id for these rows also will be NULL, not 10. you are wrong, or do you know the table-structure? sales.sale_id can be NULL while customer_id can be 10 ! -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SubQueries and IN
Andy Hall wrote: Hi, I have just started using MySQL from MSSQL 7. I need to port the following into MySQL from an existing (working) query on MSSQL Server: SELECT product_id, name, description FROM products WHERE product_id NOT IN (SELECT product_id FROM sales WHERE customer_id = 10) i.e. get all the products that a particular customer has not already bought This errors, and I have since read that the MySQL "IN" does not allow sub-queries, but also seen examples of it done. Is it only supported in a later version? We are running v. 3.23.3. subqueries requieres 4.x try SELECT product_id, name, description FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE NOT sales.customer_id = 10 I have also tried: SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE sales.customer_id = 10 AND sales.sale_id IS NULL This does not return any records as it seems to ignoring the LEFT JOIN part when I stick on the "WHERE sales.customer_id = 10". (pretty sure this query would work in MS-SQL) this seems a bit different then this before but should work, does SELECT * FROM sales WHERE sales.customer_id = 10 AND sales.sale_id IS NULL return any results? There must be a way to do this, but I dont seem to be able to put my finger on it and I would appreciate any help! -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: It is secure to access MySQL thru internet?
What do you mean with secure connection, ssh? How to configure this on MySql? - Original Message - From: "Fortuno, Adam" <[EMAIL PROTECTED]> To: "'Flavio Tobias'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 29, 2003 2:17 PM Subject: RE: It is secure to access MySQL thru internet? > Sure. If you use a secured connection. > > A$ > > -Original Message- > From: Flavio Tobias [mailto:[EMAIL PROTECTED] > Sent: Friday, August 29, 2003 1:11 PM > To: [EMAIL PROTECTED] > Subject: It is secure to access MySQL thru internet? > > > I need to access a database thru internet. It is secure to do this using > MySql? > > > Thanks > Flavio Tobias > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
It is secure to access MySQL thru internet?
I need to access a database thru internet. It is secure to do this using MySql? Thanks Flavio Tobias
Re: Query excution time
If you want a simple solution for windows, check RSSmyperfmon www.rss-software.de/en > MTop (MySQL Top) is a really nice little utility: > http://mtop.sourceforge.net/ > > Shows what queries are running, and elapsed time. > > Daivd > - Original Message - > From: "Prem Soman" <[EMAIL PROTECTED]> > To: "mysql groups" <[EMAIL PROTECTED]> > Sent: Saturday, June 21, 2003 12:02 AM > Subject: Query excution time > > > > hi ! > > > > what is the best way to find query excution time in > > MySql > > > > plz help me! > > > > > > Want to chat instantly with your online friends? Get the FREE Yahoo! > > Messenger http://uk.messenger.yahoo.com/ > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ssh problem with mysql_real_connect - repost
I think I need a MySQL code guru... I am using the 4.0.12 client library on a Win2k host to connect to a 4.0.13 linux server. If I connect directly to the server using mysql_real_connect, everything runs fine. However, I want to use SSH tunneling. So I set up a SSHv2 tunneling connection from localhost:3306 to server:3306. If I use the command line, i.e. mysql -u foo -pbar -h localhost, I can connect to the remote server via SSH. The same is, if I use tools like SQLyog. But in my program which call the mysql C API, the program freezes. I enable the dbug, here is the output. The program hangs at the last line... Any ideas how to avoid this problem? Thanks in advance, Tobias >my_init | >my_win_init | libmysql_init my_malloc | my: Size: 544 MyFlags: 48 | exit: ptr: 4d144c8 mysql_real_connect | enter: host: 127.0.0.1 db: rssvertrieb user: rss | info: Server name: '127.0.0.1'. Named Pipe: MySQL | error: host: '127.0.0.1' socket: '' named_pipe: 0 have_tcpip: 1 | info: Server name: '127.0.0.1'. TCP sock: 3306 | >vio_new | | enter: sd=1140 | | >my_malloc | | | my: Size: 84 MyFlags: 16 | | | exit: ptr: 4d124b0 | | vio_reset | | | enter: type=1 sd=1140 localhost=0 | | my_net_init | | >my_malloc | | | my: Size: 8199 MyFlags: 16 | | | exit: ptr: 4d14720 | | vio_fastsend | | | exit: 0 | | vio_keepalive | | enter: sd=1140, set_keep_alive=1 | vio_is_blocking | | exit: 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
strange SSH/libmysql.dll problem - this time with dbug infos
I think I need a MySQL code guru... I am using the 4.0.12 client library on a Win2k host to connect to a 4.0.13 linux server. If I connect directly to the server using mysql_real_connect, everything runs fine. However, I want to use SSH tunneling. So I set up a SSHv2 tunneling connection from localhost:3306 to server:3306. If I use the command line, i.e. mysql -u foo -pbar, I can connect to the remote server via SSH. The same is, if I use tools like SQLyog. I enable the dbug, here is the output. The program hangs at the last line... >my_init | >my_win_init | libmysql_init my_malloc | my: Size: 544 MyFlags: 48 | exit: ptr: 4d144c8 mysql_real_connect | enter: host: 127.0.0.1 db: rssvertrieb user: rss | info: Server name: '127.0.0.1'. Named Pipe: MySQL | error: host: '127.0.0.1' socket: '' named_pipe: 0 have_tcpip: 1 | info: Server name: '127.0.0.1'. TCP sock: 3306 | >vio_new | | enter: sd=1140 | | >my_malloc | | | my: Size: 84 MyFlags: 16 | | | exit: ptr: 4d124b0 | | vio_reset | | | enter: type=1 sd=1140 localhost=0 | | my_net_init | | >my_malloc | | | my: Size: 8199 MyFlags: 16 | | | exit: ptr: 4d14720 | | vio_fastsend | | | exit: 0 | | vio_keepalive | | enter: sd=1140, set_keep_alive=1 | vio_is_blocking | | exit: 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]