Re: arbitrary ORDER BY
In article [EMAIL PROTECTED], Steve Lefevre [EMAIL PROTECTED] writes: For posterity, this is how I solved the problem: To create an abitrary for the ORDER BY clause, create a field like this: SELECT FIELD( field, arbitrary sort string 2, arbitrary sort string 3, arbitrary sort string 1 ) as sort_order FROM table ORDER BY sort_order It's not necessary to SELECT something in order to ORDER BY it. Just do SELECT anything_else FROM TABLE ORDER BY FIELD(field, arbitrary sort string 2, arbitrary sort string 3, arbitrary sort string 1 ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird MySQL Connection Issues
If you are running MySQL on Windows, then I'm wondering whether you are having a problem with running out of available ports, for clients to connect to MySQL on. This may be your problem: From the manual, 2.3.16. MySQL on Windows Compared to MySQL on Unix *** MySQL for Windows has proven itself to be very stable. The Windows version of MySQL has the same features as the corresponding Unix version, with the following exceptions: Limited number of ports Windows systems have about 4,000 ports available for client connections, and after a connection on a port closes, it takes two to four minutes before the port can be reused. In situations where clients connect to and disconnect from the server at a high rate, it is possible for all available ports to be used up before closed ports become available again. If this happens, the MySQL server appears to be unresponsive even though it is running. Note that ports may be used by other applications running on the machine as well, in which case the number of ports available to MySQL is lower. For more information, see http://support.microsoft.com/default.aspx?scid=kb;en-us;196271. *** Keith In theory, theory and practice are the same; In practice they are not. On Sun, 12 Feb 2006, Aaron Axelsen wrote: To: mysql@lists.mysql.com From: Aaron Axelsen [EMAIL PROTECTED] Subject: Weird MySQL Connection Issues I have been experiencing some weird MySQL connection issues lately. Twice now in that last couple weeks, there have been times where some mysql applications are working, and others are not working. Both times the mysql connection limit was rather high. A simple mysql restart has fixed the problem both times. There are a few forums using phpbb running on this server which get heavy access, which is most likely the cause of the problem. The version of mysql running is: 4.1.14 Is there a known issue like this with alot of mysql connections? Does anyone have any related ideas or suggestions? Thanks! -- Aaron Axelsen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: Database Workbench 2.8.1 released!
Ladies, gentlemen, Upscene Productions is proud to announce the next version of the popular database development tool: Database Workbench 2.8.1 has been released today! Download a trial at: http://www.upscene.com What's new?: http://www.upscene.com/products/dbw/whatsnew.htm Full list of features and fixes: http://www.upscene.com/news/20060213.htm Database Workbench supports: - Borland InterBase ( 4.x - 7.x ) - Firebird ( 1.x, 2.0 ) - MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, MSDE 1 2, SQL Express ) - MySQL 4, 4.1, 5.0 - Oracle Database ( 8i, 9i, 10g ) If you experience any problems with this new version, don't hestitate and either go to the website and send a support email or email directly to [EMAIL PROTECTED] New - Microsoft SQL 2005 support - MySQL 5 support - Two-way Visual Query Builder - Increased Oracle support - New SQL Insight - Create INSERT script from ODBC datasource - TIFF support in BLOB Editor Enhancements - Code/SQL Editor enhancements - More complte Schema Compare/Migration - Automatic image-type recognition in BLOB Editor - many user interface improvements - MySQL explain support in SQL Editor Thank you for your support, Martijn Tonies Database Workbench - the database developer tool for professionals Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird MySQL Connection Issues
We are running mysql on a Debian system, so we shouldn't have any of those windows problems. It's really quite confusing, there were no mysql errors when we noticed the problem. The only guess we currently have is to wait until it happens again, and make sure we dump the stats to further investigate. Any other suggestions are welcome, thanks! -- Aaron [EMAIL PROTECTED] wrote: If you are running MySQL on Windows, then I'm wondering whether you are having a problem with running out of available ports, for clients to connect to MySQL on. This may be your problem: From the manual, 2.3.16. MySQL on Windows Compared to MySQL on Unix *** MySQL for Windows has proven itself to be very stable. The Windows version of MySQL has the same features as the corresponding Unix version, with the following exceptions: Limited number of ports Windows systems have about 4,000 ports available for client connections, and after a connection on a port closes, it takes two to four minutes before the port can be reused. In situations where clients connect to and disconnect from the server at a high rate, it is possible for all available ports to be used up before closed ports become available again. If this happens, the MySQL server appears to be unresponsive even though it is running. Note that ports may be used by other applications running on the machine as well, in which case the number of ports available to MySQL is lower. For more information, see http://support.microsoft.com/default.aspx?scid=kb;en-us;196271. *** Keith In theory, theory and practice are the same; In practice they are not. On Sun, 12 Feb 2006, Aaron Axelsen wrote: To: mysql@lists.mysql.com From: Aaron Axelsen [EMAIL PROTECTED] Subject: Weird MySQL Connection Issues I have been experiencing some weird MySQL connection issues lately. Twice now in that last couple weeks, there have been times where some mysql applications are working, and others are not working. Both times the mysql connection limit was rather high. A simple mysql restart has fixed the problem both times. There are a few forums using phpbb running on this server which get heavy access, which is most likely the cause of the problem. The version of mysql running is: 4.1.14 Is there a known issue like this with alot of mysql connections? Does anyone have any related ideas or suggestions? Thanks! -- Aaron Axelsen [EMAIL PROTECTED] -- Aaron Axelsen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Any help with resetting the administrative password using 'mysqld _safe' w/ the --init-file option
Has anyone run into a problem with setting the root user password in mysql using mysqld_safe with the '--init-file' option that contains the new password for startup? What I'm seeing after I issue the kill command for the 'host.pid' file and then restarting 'mysqld_safe --init-file', is a short pause, and then an error message that mysqld is unable to start - i.e. 060213 13:28:35 mysqld ended My mysql version is 4.0.20 running on a Solaris 9 system. If there are any additional patches or workarounds that are required, I'd appreciate any advice or tips that you could pass along to me for those that have tried to create the root password in this way. http://dev.mysql.com/doc/refman/4.1/en/resetting-permissions.html Thanks in advance, Matt Skarlatos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting min, max
Hi, I have a table with the following fields: symbol, date_time, price, volume I need to extract a list which the following values from this table, for each hour (in date_time field): - symbol - min(price) - max(price) - price where date_time is the earliest for that certain hour. - price where the date_time is the last from that hour. - The sum of volume from that hour. I have tried to get the list of symbols, then get each hourly period and calculate those 6 values for each period, but there are many symbols and very many periods, and it takes very very much time. Is there a more intelligent way of getting those values in another way than symbol by symbol and period by period? Thank you very much. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting min, max
- Original Message - From: Octavian Rasnita [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 13, 2006 9:07 AM Subject: selecting min, max Hi, I have a table with the following fields: symbol, date_time, price, volume I need to extract a list which the following values from this table, for each hour (in date_time field): - symbol - min(price) - max(price) - price where date_time is the earliest for that certain hour. - price where the date_time is the last from that hour. - The sum of volume from that hour. I have tried to get the list of symbols, then get each hourly period and calculate those 6 values for each period, but there are many symbols and very many periods, and it takes very very much time. Is there a more intelligent way of getting those values in another way than symbol by symbol and period by period? It's hard to answer your question since you haven't given us any examples of the SQL you've already tried. You haven't told us which version of MySQL you are using, either. That makes a big difference since newer versions offer many more SQL capabilities like views and subqueries that could really help you. You certainly shouldn't have to write separate queries for each different symbol that you are using! Have you looked at the GROUP BY clause? If you haven't, I think you'll find that it is _very_ helpful. A query like: select symbol, max(price) as Maximum_Price, min(price) as Minimum_Price from mytable group by symbol should show you a single row for each symbol that you have in your table. Each row will contain the maximum and minimum prices for that symbol. Of course you will still need to add the time logic to that example so that rows for each hour are grouped together as well. But I can't do that without seeing a full definition of the table and a few sample rows so that I can really understand the data. Something like that should get you everything you want in just one query. I don't know how it will perform but if it doesn't perform well, you should be able to improve the performance dramatically by creating appropriate indexes on the data. I can't advise you on the construction of indexes in MySQL - I don't know enough about how MySQL uses indexes - but others on this mailing list are very experienced in this area and should be able to guide you. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 13/02/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to duplicate a database at home, possible encoding problem
MySQL List, With the off list help of a member of this community, I have solved the issue of accessing my database data from within PHP. It was an issue related to PHP's register_globals setting. However, the encoding issue remains. I've taken the text that pertains only to that, and reproduced it here in hopes that someone can give me some advice which will enable me to transport my utf8 encoded data from my hosting service to my home machine. I can take the .sql file that I have exported from my hosting service, open it in OpenOffice Write as a text encoded file, and verify that it is encoded in utf-8. Most of the Japanese text shows up readable. Some of it, however, shows up as coded numbers (I'm not sure what the term is when utf displays this way): #12513;#12540;#12531;#12539; When I import the .sql file into MySQL, I can look at it in phpMyAdmin and see that the text that displayed correctly as Japanese in OpenOffice still displays correctly as Japanese. The text that was in number form is also still in number form when viewed through phpMyAdmin. In short, phpMyAdmin sees it after import the same way that OpenOffice did before import. But, then when I view a PHP file in FireFox, and it accesses the database that way, the situation changes. The text that is encoded as numbers displays as correct Japanese. The text that displays as actual Japanese text in OpenOffice and phpMyAdmin now displays as quesiton marks. Again, just to be clear, all Japanese characters and all database data display correctly when viewed from the hosting service. I hope someone can shed some light on this. Thank you. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FROM_UNIXTIME()
Hi Ben, all!
[EMAIL PROTECTED] wrote:
Hi,
I have two different servers each running mysql. One is a SuSE and the other
a FreeBSD system.
mysql --version returns:
mysql Ver 12.22 Distrib 4.0.26, for portbld-freebsd5.4 (i386)
mysql Ver 12.22 Distrib 4.0.18, for suse-linux (i686)
On the SuSE system the following command returns this:
mysql SELECT FROM_UNIXTIME(-100);
+-+
| FROM_UNIXTIME(-100) |
+-+
| 1969-12-20 11:13:20 |
+-+
On the FreeBSD system the same command returns:
mysql SELECT FROM_UNIXTIME(-100);
+-+
| FROM_UNIXTIME(-100) |
+-+
| NULL|
+-+
Obviously the SuSE system seems to be able to use negative values for a unix
timestamp.
I am wondering, how I can get the FreeBSD system to work the same way. Can
you help me out?
This is probably no issue of the SuSE vs. FreeBSD binary but rather one
of 4.0.18 vs. 4.0.26.
Scanning the Changes in release 4.0.x sections from 4.0.26 down to
4.0.18, I came across this one for 4.0.23:
Fixed bug which caused FROM_UNIXTIME() function to return wrong
result if the argument was too big. (Bug #6439)
Looking at the associated code change, it seems that this disables
negative values of Unix timestamps.
The Unix type time_t (used to hold timestamps, coded as values since
the epoch which is Jan 1, 1970, 00:00:00 UTC) may be unsigned, and
negative values are not defined for it (but not excluded either).
(Checked with X/Open 1988, and IEEE / Open Group 2001)
So IMO using negative Unix timestamp values is outside the
specification, and anything may happen.
The 4.0.18 behavior you report seems a bit inconsistent anyway, because
already in that version the test func_time should contain these lines:
select unix_timestamp('1969-12-01 19:00:01');# command
unix_timestamp('1969-12-01 19:00:01')# result header
0# result value
So this shows that dates prior to the epoch will not be converted to a
Unix timestamp (which would have to be negative).
If you need to handle values before the epoch, you should use a
different data type to store them.
Regards,
Jörg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple many-to-many SELECT
Peter Brawley [EMAIL PROTECTED] wrote on 02/12/2006 03:40:52 PM: Jessica, Your first query, with inner joins, fails with the error message Unknown column 'movies.id' in 'on clause because the query inside parentheses in ... ... FROM movies INNER JOIN (director INNER JOIN director_movies ON director_movies.director_id = director.id AND director_movies.movie_id=movies.id) ... references a table, movies, which is referenced only _outside_ the parenthesis. Notice too that your construct table1 INNER JOIN (joined_table_ref) has not ON or USING clause, so it calls for a _cross_join_ between table1 and joined_table_ref! I expect you didn't intend this, but rather wanted ... INNER JOIN ( director INNER JOIN director_movies ON director_movies.director_id = director.id ) ON director_movies.movie_id=movies.id ... When we fix that problem, and the same problem in the other join clauses, we get ... SELECT * FROM movies LEFT JOIN (director INNER JOIN director_movies ON director_movies.director_id = director.id ) ON director_movies.movie_id=movies.id LEFT JOIN (country INNER JOIN country_movies ON country_movies.country_id = country.id ) ON country_movies.movie_id=movies.id LEFT JOIN (producer INNER JOIN producer_movies ON producer_movies.producer_id = producer.id ) ON producer_movies.movie_id=movies.id WHERE movies.id = 123 which throws no syntax error. Is this the query you want? Peter Brawley http://www.artfulsoftware,com - Jessica Yazbek wrote: Wow, ok, I totally messed up my example. I actually have 8 related tables (+8 relating tables), so I decided to try to simplify and only show two - director and producer. However, I left country in the select example. So.. please also consider the following tables in my database: country | CREATE TABLE `country` ( `id` int(11) NOT NULL auto_increment, `country_of_origin` text NOT NULL, PRIMARY KEY (`country_of_origin`(100)), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | country_movies | CREATE TABLE `country_movies` ( `movie_id` int(11) NOT NULL default '0', `country_id` int(11) NOT NULL default '0' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Maybe I should send this to the list...grr! Thanks for the help so far! Jessica On Feb 12, 2006, at 11:54 AM, Peter Brawley wrote: table country_movies? P. Jessica Yazbek wrote: By request, here are the create statements for my tables: movies | CREATE TABLE `movies` ( `id` int(11) NOT NULL default '0', `catalog_description` text, `title` text, `website_url` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 director | CREATE TABLE `director` ( `id` int(11) NOT NULL auto_increment, `director_first_name` text NOT NULL, `director_last_name` text NOT NULL, PRIMARY KEY (`director_first_name`(100),`director_last_name`(100)), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 producer | CREATE TABLE `producer` ( `id` int(11) NOT NULL auto_increment, `producer_first_name` text NOT NULL, `producer_last_name` text NOT NULL, PRIMARY KEY (`producer_first_name`(100),`producer_last_name`(100)), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 director_movies | CREATE TABLE `director_movies` ( `movie_id` int(11) NOT NULL default '0', `director_id` int(11) NOT NULL default '0' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 producer_movies | CREATE TABLE `producer_movies` ( `movie_id` int(11) NOT NULL default '0', `producer_id` int(11) NOT NULL default '0' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Again, I'm trying to select from movies,director, and producer, using only one SELECT statement. Director and Producer are related to movies via the tables director_movies and producer_movies. Thanks again! Jessica On Feb 12, 2006, at 11:05 AM, Peter Brawley wrote: Jessica, To figure out what's causing your error, I for one would need the CREATE statements. PB Jessica Yazbek wrote: Hello, I apologize if this is a common question; I have been working with it and googling for days, and can't seem to find anyone who has been trying to do the same thing that I am. Maybe I'm using the wrong keywords. In any event, I am desperate for help. Here is my problem: I have a database with several tables related on a many-to-many basis. Here is a simplified description: TABLE: movies +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | id | int(11) | | PRI | 0 | | | catalog_description | text| YES | | NULL| | | title | text| YES | | NULL
Re: Unable to duplicate a database at home, possible encoding problem
Hi, I'm not very good at encoding either, but from what I understood, the client specifies the encoding expected from the database,if you do a show variables like '%char%', you will have a line character_set_client. In your case this is probably set to latin_1. If this is the case, mysql is sending the result declared as latin1 encoded. The 'numbers' (hexadecimals value of your characters) are ok, but the utf8 encoded ones can't be represented properly thus the question marks. If this is the case you could try adding SET NAMES 'UTF8' before your queries. detailed info here: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html I think the second user comment may also be helpful. hth, melanie From: Dave M G [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: Unable to duplicate a database at home, possible encoding problem Date: Tue, 14 Feb 2006 00:06:42 +0900 MySQL List, With the off list help of a member of this community, I have solved the issue of accessing my database data from within PHP. It was an issue related to PHP's register_globals setting. However, the encoding issue remains. I've taken the text that pertains only to that, and reproduced it here in hopes that someone can give me some advice which will enable me to transport my utf8 encoded data from my hosting service to my home machine. I can take the .sql file that I have exported from my hosting service, open it in OpenOffice Write as a text encoded file, and verify that it is encoded in utf-8. Most of the Japanese text shows up readable. Some of it, however, shows up as coded numbers (I'm not sure what the term is when utf displays this way): #12513;#12540;#12531;#12539; When I import the .sql file into MySQL, I can look at it in phpMyAdmin and see that the text that displayed correctly as Japanese in OpenOffice still displays correctly as Japanese. The text that was in number form is also still in number form when viewed through phpMyAdmin. In short, phpMyAdmin sees it after import the same way that OpenOffice did before import. But, then when I view a PHP file in FireFox, and it accesses the database that way, the situation changes. The text that is encoded as numbers displays as correct Japanese. The text that displays as actual Japanese text in OpenOffice and phpMyAdmin now displays as quesiton marks. Again, just to be clear, all Japanese characters and all database data display correctly when viewed from the hosting service. I hope someone can shed some light on this. Thank you. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ The new MSN Search Toolbar now includes Desktop search! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple many-to-many SELECT
oops! I hit SEND before I was done. (BAD FINGERS!) SELECT * FROM movies LEFT JOIN director_movies ON director_movies.movie_id=movies.id LEFT JOIN director ON director_movies.director_id = director.id LEFT JOIN country_movies ON country_movies.movie_id=movies.id LEFT JOIN country ON country_movies.country_id = country.id LEFT JOIN producer_movies ON producer_movies.movie_id=movies.id LEFT JOIN producer ON producer_movies.producer_id = producer.id WHERE movies.id = 123 Now this may not be as fast as doing this in stages. First stage you collect all of the secondary id's into a temporary table. Second stage you hit the master tables and resolve the secondary id's into their actual values. Once you get above 7 or so JOINs per query, performance tends to degrade. CREATE TEMPORARY TABLE tmpMiddle SELECT movies.id, director_movies.director_id, country_movies.country_id, producer_movies.producer_id FROM movies LEFT JOIN director_movies ON director_movies.movie_id=movies.id LEFT JOIN country_movies ON country_movies.movie_id=movies.id LEFT JOIN producer_movies ON producer_movies.movie_id=movies.id WHERE movies.id = 123 ; SELECT * FROM tmpMiddle LEFT JOIN movies ON movies.id = tmpMiddle.id LEFT JOIN director ON tmpMiddle.director_id = director.id LEFT JOIN country ON tmpMiddle.country_id = country.id LEFT JOIN producer ON tmpMiddle.producer_id = producer.id; DROP TEMPORARY TABLE tmpMiddle; This reduces the # of joins from 8 to 4 and based on your initial description you will probably have more than this. Again, this is just another way to look at the issue. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: selecting min, max
Octavian, I need to extract a list which the following values from this table, for each hour (in date_time field): - symbol - min(price) - max(price) - price where date_time is the earliest for that certain hour. - price where the date_time is the last from that hour. - The sum of volume from that hour. Groupwise aggregates again. Very often required asked about. See http://www.artfulsoftware.com/queries.php#18 and http://www.artfulsoftware.com/queries.php#19 for two typical answers. PB Octavian Rasnita wrote: Hi, I have a table with the following fields: symbol, date_time, price, volume I need to extract a list which the following values from this table, for each hour (in date_time field): - symbol - min(price) - max(price) - price where date_time is the earliest for that certain hour. - price where the date_time is the last from that hour. - The sum of volume from that hour. I have tried to get the list of symbols, then get each hourly period and calculate those 6 values for each period, but there are many symbols and very many periods, and it takes very very much time. Is there a more intelligent way of getting those values in another way than symbol by symbol and period by period? Thank you very much. Teddy -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 2/13/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie wants to load a couple of tables and join them
Basically, I'm new to mysql (or to any database for that matter). I have an old version installed on my linux machine. I thought, as a learning exercise I'd take 2 files (tab separated tables) load them into mysql and then merge or join them. So what are the steps? The first thing I tried was to create a database with mysqladmin create MACARP and the error I get is CREATE DATABASE failed; error: 'Access denied for user: '@localhost' to database 'MACARP'' A similar attempt to create a user ended similarly. Can I get some hints? === Al -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie wants to load a couple of tables and join them
Al Sparks [EMAIL PROTECTED] wrote on 13/02/2006 16:11:49: Basically, I'm new to mysql (or to any database for that matter). I have an old version installed on my linux machine. I thought, as a learning exercise I'd take 2 files (tab separated tables) load them into mysql and then merge or join them. So what are the steps? The first thing I tried was to create a database with mysqladmin create MACARP and the error I get is CREATE DATABASE failed; error: 'Access denied for user: '@localhost' to database 'MACARP'' A similar attempt to create a user ended similarly. Can I get some hints? When your system was installed, it was installed with security turned on (which is definitely a wise thing to do). MySQL security is a bit like linux secutiry, in that there is a user called root who is usually omnipotent, and other users with lesser rights, and you cannot do anything unless yuou have the appropriate rights. However, it is not the same as linux security - your linux user name and your MySQL user name are different entities, not the same unless you choose to make them so. I would reccommend that you try and find out the root password for your system from whoever installed it. If not, and you think that no data on the system is valid, de-intall MySQL, remove the data directory (which also contaisn the security data), and re-install. Alternatively, the MySQL installation usually sets up a database imaginatively named test with wide rights, so that you could run you experiments within database test. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems Starting MySQL
Anyone have any ideas on this one? Many thanks. RobL On Friday 10 February 2006 15:07, Rob Lacey wrote: Running as root. RobL On Friday 10 February 2006 14:55, sheeri kritzer wrote: Are you running the startup script as root, or is it setuid? -Sheeri On 2/10/06, Rob Lacey [EMAIL PROTECTED] wrote: Wondering if anyone can shed some light on a problem I am experiencing. I am running MySQL 4.1 on a Red Hat Enterprise Linux ES release 4 (Nahant Update 2) machine. I have been trying to get the default /etc/init.d/mysqld script to restart the server but I keep running into the same problems. The MySQL server itself is running at the moment and has a number of live databases on it. 060209 18:40:43 mysqld started 060209 18:40:43 [Warning] Can't create test file /var/lib/mysql/thirdeye.lower-test /usr/libexec/mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13) 060209 18:40:43 [ERROR] Aborting 060209 18:40:43 [Note] /usr/libexec/mysqld: Shutdown complete 060209 18:40:43 mysqld ended I can't see where the thirdeye.lower-test file creation is coming into this at all. Since as far as can see the startup script doesn't seem to call any script to create test databases. Is this part of mysqld_safe? I can also see that /var/lib/mysql is already owned by mysql.mysql with 755 permissions. Indeed the RedHat startup script makes this so. Therefore the permissions error seems extremely odd as they seem fine to me. I can start the server using the following directly on the command line, which is what the startup script ultimately runs anyway. /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file= /var/run/mysqld/mysqld.pid /dev/null 21 Can anyone give me something further to go on? Many thanks. Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting min, max
Hi, From: Rhino [EMAIL PROTECTED] ... I need to extract a list which the following values from this table, for each hour (in date_time field): - symbol - min(price) - max(price) - price where date_time is the earliest for that certain hour. - price where the date_time is the last from that hour. - The sum of volume from that hour. I have tried to get the list of symbols, then get each hourly period and calculate those 6 values for each period, but there are many symbols and very many periods, and it takes very very much time. Is there a more intelligent way of getting those values in another way than symbol by symbol and period by period? It's hard to answer your question since you haven't given us any examples of the SQL you've already tried. You haven't told us which version of MySQL you are using, either. That makes a big difference since newer versions offer many more SQL capabilities like views and subqueries that could really help you. You certainly shouldn't have to write separate queries for each different symbol that you are using! Here is the table definition. The table is simple, but what I want is complicated: CREATE TABLE `tickers` ( `symbol` varchar(20) NOT NULL, `last_volume` bigint(20) unsigned default NULL, `last_price` decimal(20,4) unsigned default NULL, `last_update` datetime default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I want to get a list of values for more periods of time, 5 minutes, 15 minutes, and hourly. I need to get: symbol date_format(last_update, '%Y-%m-%d') as date date_format(last_update, '%H:%i:%s') as time min(last_price) as low (The min value of last_price for that period) max(last_price) as high (the max price from that period) last_price as open (where last_update=min(last_update) from that period) last_price as close (where last_update=max(last_update) from that period) The result data should look something like: Symbol,data,time,low,high,open,close simb1,2006-02-08,10:15:00,1000,1200,1050,1150 simb1,2006-02-08,10:30:00,1100,1150,1150,1150 simb1,2006-02-08,10:45:00,1000,1200,1050,1150 simb1,2006-02-08,11:00:00,1050,1200,1050,1150 simb1,2006-02-08,11:15:00,1000,1200,1050,1150 ... then here follow the rest of records for simb1 and for other symbols. You may see that the first time is 10:15:00, the next time is 10:30:00, the next is 10:45, so the period of time is 15 minutes. The first low is the lowest price between 10:15:00 and 10:30:00 and the high is the highest price in that period. The first open value is the last_price of the first trade from that period and the close price is the last_price of the latest trade from that period. I don't know if MySQL can create a query that can get those values fast enough. Thank you very much. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting min, max
PS, I have forgotten to tell that I am using MySQL 5. Thank you. Teddy - Original Message - From: Octavian Rasnita [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, February 13, 2006 10:00 PM Subject: Re: selecting min, max Hi, From: Rhino [EMAIL PROTECTED] ... I need to extract a list which the following values from this table, for each hour (in date_time field): - symbol - min(price) - max(price) - price where date_time is the earliest for that certain hour. - price where the date_time is the last from that hour. - The sum of volume from that hour. I have tried to get the list of symbols, then get each hourly period and calculate those 6 values for each period, but there are many symbols and very many periods, and it takes very very much time. Is there a more intelligent way of getting those values in another way than symbol by symbol and period by period? It's hard to answer your question since you haven't given us any examples of the SQL you've already tried. You haven't told us which version of MySQL you are using, either. That makes a big difference since newer versions offer many more SQL capabilities like views and subqueries that could really help you. You certainly shouldn't have to write separate queries for each different symbol that you are using! Here is the table definition. The table is simple, but what I want is complicated: CREATE TABLE `tickers` ( `symbol` varchar(20) NOT NULL, `last_volume` bigint(20) unsigned default NULL, `last_price` decimal(20,4) unsigned default NULL, `last_update` datetime default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I want to get a list of values for more periods of time, 5 minutes, 15 minutes, and hourly. I need to get: symbol date_format(last_update, '%Y-%m-%d') as date date_format(last_update, '%H:%i:%s') as time min(last_price) as low (The min value of last_price for that period) max(last_price) as high (the max price from that period) last_price as open (where last_update=min(last_update) from that period) last_price as close (where last_update=max(last_update) from that period) The result data should look something like: Symbol,data,time,low,high,open,close simb1,2006-02-08,10:15:00,1000,1200,1050,1150 simb1,2006-02-08,10:30:00,1100,1150,1150,1150 simb1,2006-02-08,10:45:00,1000,1200,1050,1150 simb1,2006-02-08,11:00:00,1050,1200,1050,1150 simb1,2006-02-08,11:15:00,1000,1200,1050,1150 ... then here follow the rest of records for simb1 and for other symbols. You may see that the first time is 10:15:00, the next time is 10:30:00, the next is 10:45, so the period of time is 15 minutes. The first low is the lowest price between 10:15:00 and 10:30:00 and the high is the highest price in that period. The first open value is the last_price of the first trade from that period and the close price is the last_price of the latest trade from that period. I don't know if MySQL can create a query that can get those values fast enough. Thank you very much. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 2/13/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random ID's for existing data
Brian Menke [EMAIL PROTECTED] wrote on 02/12/2006 09:20:32 PM: I have a table with about 2000 entries with names and other data. Ultimately I'm going to make a database backed web site with this and I want to provide existing users with random id's that they will use for their passwords. I was wondering how to take the existing data and add random id's to it. This web site will be using PHP as the programming language so it would be nice if I could use the same mechanism to add the random id's automatically when I add new users via a web form. Thanks for any ideas on how to approach this! -Brian Menke One simple method could be to add another column to your existing users table and fill it with a hashed/encrypted copy of the plain-old auto_increment value already on the table: ALTER TABLE userstable ADD idhash varchar(25); UPDATE userstable set idhash = OLD_PASSWORD(id); It's not pretty, but it's quick. RTFM for more details on the available encryption/data hashing functions. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Database Replication
All, I have been tasked with setting up DR between two different sites. Is there a favorite tool or GUI that someone could recommend for this task? Regards, Alan L. Fisher -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installation Issue
I have been noticing following message during mysql startup.the installation was fine. 060213 16:40:58 mysqld started 060213 16:40:59 InnoDB: Started; log sequence number 0 43655 060213 16:40:59 [ERROR] Can't start server : Bind on unix socket: Permission denied 060213 16:40:59 [ERROR] Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? 060213 16:40:59 [ERROR] Aborting 060213 16:40:59 InnoDB: Starting shutdown... 060213 16:41:01 InnoDB: Shutdown completed; log sequence number 0 43655 060213 16:41:01 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 060213 16:41:01 mysqld ended Here is my my.cnf file details [client] # socket = /var/run/mysqld/mysqld.sock socket = /usr/local/mysql/mysql.sock [mysqld] datadir=/usr/local/mysql/data/ port =3306 socket=/var/lib/mysql/mysql.sock user=mysql [mysql.server] #user=mysql basedir=/usr/local/mysql [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Please sugegst . thx +ravi - Brings words and photos together (easily) with PhotoMail - it's free and works with Yahoo! Mail.
Re: selecting min, max
I've just spent the last couple of hours trying various queries to get the result you want. I'm afraid I didn't find an answer for you. I think I understand exactly what you want but I couldn't figure out how to write the query you need. Unfortunately, I'm using 4.0.15 and I don't even have subquery support in that version. I might be able to solve the problem in DB2 SQL, which I know better, but that won't help you because MySQL doesn't appear to support the techniques I would use in DB2, like nested table expressions and common table expressions. Therefore, I think you should look very carefully at the two suggestions that Peter Brawley gave you earlier in the day. They look promising and I think you might be able to solve the problem that way. Sorry I couldn't help more. -- Rhino - Original Message - From: Octavian Rasnita [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED]; Rhino [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, February 13, 2006 3:16 PM Subject: Re: selecting min, max PS, I have forgotten to tell that I am using MySQL 5. Thank you. Teddy - Original Message - From: Octavian Rasnita [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, February 13, 2006 10:00 PM Subject: Re: selecting min, max Hi, From: Rhino [EMAIL PROTECTED] ... I need to extract a list which the following values from this table, for each hour (in date_time field): - symbol - min(price) - max(price) - price where date_time is the earliest for that certain hour. - price where the date_time is the last from that hour. - The sum of volume from that hour. I have tried to get the list of symbols, then get each hourly period and calculate those 6 values for each period, but there are many symbols and very many periods, and it takes very very much time. Is there a more intelligent way of getting those values in another way than symbol by symbol and period by period? It's hard to answer your question since you haven't given us any examples of the SQL you've already tried. You haven't told us which version of MySQL you are using, either. That makes a big difference since newer versions offer many more SQL capabilities like views and subqueries that could really help you. You certainly shouldn't have to write separate queries for each different symbol that you are using! Here is the table definition. The table is simple, but what I want is complicated: CREATE TABLE `tickers` ( `symbol` varchar(20) NOT NULL, `last_volume` bigint(20) unsigned default NULL, `last_price` decimal(20,4) unsigned default NULL, `last_update` datetime default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I want to get a list of values for more periods of time, 5 minutes, 15 minutes, and hourly. I need to get: symbol date_format(last_update, '%Y-%m-%d') as date date_format(last_update, '%H:%i:%s') as time min(last_price) as low (The min value of last_price for that period) max(last_price) as high (the max price from that period) last_price as open (where last_update=min(last_update) from that period) last_price as close (where last_update=max(last_update) from that period) The result data should look something like: Symbol,data,time,low,high,open,close simb1,2006-02-08,10:15:00,1000,1200,1050,1150 simb1,2006-02-08,10:30:00,1100,1150,1150,1150 simb1,2006-02-08,10:45:00,1000,1200,1050,1150 simb1,2006-02-08,11:00:00,1050,1200,1050,1150 simb1,2006-02-08,11:15:00,1000,1200,1050,1150 ... then here follow the rest of records for simb1 and for other symbols. You may see that the first time is 10:15:00, the next time is 10:30:00, the next is 10:45, so the period of time is 15 minutes. The first low is the lowest price between 10:15:00 and 10:30:00 and the high is the highest price in that period. The first open value is the last_price of the first trade from that period and the close price is the last_price of the latest trade from that period. I don't know if MySQL can create a query that can get those values fast enough. Thank you very much. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 2/13/2006 -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 13/02/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 13/02/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation Issue
On Monday 13 February 2006 23:03, Ravi Kumar wrote: I have been noticing following message during mysql startup.the installation was fine. 060213 16:40:58 mysqld started 060213 16:40:59 InnoDB: Started; log sequence number 0 43655 060213 16:40:59 [ERROR] Can't start server : Bind on unix socket: Permission denied 060213 16:40:59 [ERROR] Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? 060213 16:40:59 [ERROR] Aborting 060213 16:40:59 InnoDB: Starting shutdown... 060213 16:41:01 InnoDB: Shutdown completed; log sequence number 0 43655 060213 16:41:01 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 060213 16:41:01 mysqld ended Here is my my.cnf file details [client] # socket = /var/run/mysqld/mysqld.sock socket = /usr/local/mysql/mysql.sock [mysqld] datadir=/usr/local/mysql/data/ port =3306 socket=/var/lib/mysql/mysql.sock user=mysql [mysql.server] #user=mysql basedir=/usr/local/mysql [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Please sugegst . thx +ravi [ERROR] Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ? 060213 Well? Do you? -- Peter M. Groen Open Systems Development Klipperwerf 12 2317 DZ Leiden T : +31-(0)71-5216317 M : +31-(0)6-29563390 E : [EMAIL PROTECTED] Skype : peter_m_groen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General DB Design Question - How to avoid redundancy in table relationships
These are the tables in question: RFQ (Request for Quote) Part Inventory Inventory items ALWAYS have a partID. RFQ items ALWAYS have a partID. However, sometimes, RFQ items have an inventoryID as well. Now, we have a redundancy problem. Because, in those instances when the RFQ has an inventoryID, the partID should be derived from the inventoryID. If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. We don't want to have both the inventoryID and the partID in the RFQ table, because it opens up data integrity issues. ie, what if the RFQ item shows inventoryID 2, and partID 1...but inventoryID 2 is associated to partID 2. Now which partID is correct? They can't both be right. I'm sure this type of problem is run up against all the time, and I'm wondering what the best practice methodology is from experienced DBA's. This was a simple example; however, we are running into the problem system wide. For example, a quote table has an OPTIONAL RFQ ID, and a mandatory contactID. The RFQ table has a mandatory contactID. If the quote table has an RFQID, we want to derive the contactID from the RFQID. If the quote has NO RFQID, then we need to store the contactID directly in the quote table. In those instances where there IS an RFQID in the quote table, we end up storing the contactID twice. Once in the quote table, and once in the association between the RFQ/Contact table. Same problem as above: integrity and poor overall design. Thanks for your advice.
Re: Local 'replication'
To my knowledge the only way you can do this is by running 2 daemons on the same host. You can easily do this with the 'mysqld_multi' script that comes with mysql. Atle - Flying Crocodile Inc, Unix Systems Administrator On Mon, 13 Feb 2006, Rob Gormley wrote: Short of running two MySQL instances on the same server... What I would like to do is have a table which exists on disk in InnoDB format, and a Memory table which clients make requests for. Lest that sound more convuluted than it really is, the situation is thus: Limited (financial) resources client, heavy load on DB server. In order to offset some of the load, we are able to push some load to another machine. This data needs to be made available as fast as possible, hence the memory table, but the local InnoDB table is to lighten the load on DB restart... We don't want to deluge the primary server with the synchronisation process... So it was figured that if there's a local disk based table, that can be synced fairly quickly, and the memory table can populate off of that, with no hit to the primary server. Or am I making things way more complicated than they need be? Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql query browser
I was just running some queries to get an approximate time that they will run, and i saw this in the bottom left corner: 6 rows fetched in 0.0167s (0.4119s) What do the two numbers mean, and why are they so different? I couldn't find this in the help documentation. thanks. -- http://www.douglassdavis.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General DB Design Question - How to avoid redundancy in table relationships
Scott, I'm sure this type of problem is run up against all the time, and I'm wondering what the best practice methodology is from experienced DBA's. It looks like the kind of problem database schemas are meant to _avoid_. >From your description it seems you have ... part ( partID PRIMARY KEY ) inventory ( inventoryID PRIMARY KEY, partID FOREIGN KEY REFERENCES part.partID ) RFQ ( rfqID PRIMARY KEY??? (I assume), partID FOREIGN KEY references part.partID, inventoryID NULL LOOKS UP inventory.inventoryID ) according to which ... (i) a RFQ item can reference a partID which is not in inventory, (ii) even if a RFQ partID is in inventory, it may show up in RFQ paired with a different invcentoryID, but (iii) if [ii] occurs, it indicates an error which is plumb crazy--if [ii] is an error, the schema should disallow it. The business rules embedded in this schema contain a contradiction. If it were my project, I'd conclude that it's time to sit down with the client. But perhaps we need more info? PB Scott Klarenbach wrote: These are the tables in question: RFQ (Request for Quote) Part Inventory Inventory items ALWAYS have a partID. RFQ items ALWAYS have a partID. However, sometimes, RFQ items have an inventoryID as well. Now, we have a redundancy problem. Because, in those instances when the RFQ has an inventoryID, the partID should be derived from the inventoryID. If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. We don't want to have both the inventoryID and the partID in the RFQ table, because it opens up data integrity issues. ie, what if the RFQ item shows inventoryID 2, and partID 1...but inventoryID 2 is associated to partID 2. Now which partID is correct? They can't both be right. I'm sure this type of problem is run up against all the time, and I'm wondering what the best practice methodology is from experienced DBA's. This was a simple example; however, we are running into the problem system wide. For example, a quote table has an OPTIONAL RFQ ID, and a mandatory contactID. The RFQ table has a mandatory contactID. If the quote table has an RFQID, we want to derive the contactID from the RFQID. If the quote has NO RFQID, then we need to store the contactID directly in the quote table. In those instances where there IS an RFQID in the quote table, we end up storing the contactID twice. Once in the quote table, and once in the association between the RFQ/Contact table. Same problem as above: integrity and poor overall design. Thanks for your advice. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 2/13/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 2/13/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.1.6-alpha has been released
Hi, MySQL 5.1.6-alpha, a new version of the popular Open Source Database Management System, has been released. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a new alpha development release, adding new features and fixing recently discovered bugs. NOTE: This Alpha release, as any other pre-production release, should not be installed on ``production'' level systems or systems with critical data. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. We welcome and appreciate your feedback! News from the ChangeLog: Functionality added or changed: * Packaging changes: MySQL 5.1.6 introduces some changes to distribution packaging: + Distributions include both a mysqld optimized server and mysqld-debug debugging server. There is no separate debug distribution. + There is no longer a mysqld-max server. + Server binaries are no longer stripped, except for RPM distributions. + Binary distributions for Unix and Unix-like systems no longer include safe_mysqld as a link to mysqld_safe. safe_mysqld has been deprecated since MySQL 4.0 and is now removed. * Events: MySQL 5.1.6 introduces the Events Scheduler, which allows one to schedule SQL statements for execution at predetermined times. Events can be transient (one-time-only) or recurrent at regular intervals, and may execute queries and statements permitted in stored routines, including compound statements. + The CREATE EVENT statement creates events. Users must have the EVENT privilege to create events. + Events can be altered after creation (with ALTER EVENT) and dropped when no longer needed (with DROP EVENT). + For more information, see http://dev.mysql.com/doc/refman/5.1/en/events.html. * INCOMPATIBLE CHANGE: This release introduced the TRIGGER privilege. Previously, the SUPER privilege was needed to create or drop triggers. Now those operations require the TRIGGER privilege. This is a security improvement because you no longer need to grant users the SUPER privilege to enable them to create triggers. However, the requirement that the account named in a trigger's DEFINER clause must have the SUPER privilege has changed to a requirement for the TRIGGER privilege. After upgrading, be sure to update your grant tables as described in Section 5.5, mysql_fix_privilege_tables --- Upgrade MySQL System Tables. This process will assign the TRIGGER privilege to all accounts that had the SUPER privilege. (After updating, you might also consider whether any of those accounts no longer need SUPER.) If you fail to update the grant tables, triggers may fail when activated. (Bug#9142 (http://bugs.mysql.com/9142)) * INCOMPATIBLE CHANGE: Due to a change in the naming scheme for partitioning and subpartitioning files, it is not possible for the server to read partitioned tables created in previous MySQL versions. A suggested workaround is (1) to create a non- partitioned table with the same table schema using a standard CREATE TABLE statement (that is, with no partitioning clauses) and then (2) to issue a SELECT INTO to copy the data into the non-partitioned table before the upgrade; following the upgrade, you can partition the new table using ALTER TABLE ... PARTITION BY Alternatively, you can dump the table using mysqldump prior to upgrading and reload it afterwards with LOAD DATA. In either case, you should drop the pre-5.1.6 partitioned tables before upgrading to 5.1.6 or later. (Bug#13437 (http://bugs.mysql.com/13437)) Important: If any partitioned tables that were created prior to MySQL 5.1.6 are present following an upgrade to MySQL 5.1.6 or later, it is also not possible to read from the INFORMATION_SCHEMA.PARTITIONS table, nor will you be able to drop those tables or the database or databases in which they are located. In this event, you must: (1) shut down mysqld; (2) manually delete the table, partition, and (if any) subpartition files; and then (3) restart the MySQL Server. (Bug#16695 (http://bugs.mysql.com/16695)) * INCOMPATIBLE CHANGE: Words with apostrophes are now matched in a FULLTEXT search against non-apostrophe words (for example, a search for Jerry will match against the term Jerry's). Users upgrading to this
Re: Installation Issue
On Tuesday 14 February 2006 00:28, Ravi Kumar wrote: Permission denied 060213 I assume you are starting MySQL NOT as root.. Has the user write permissions on the directory and the socketfile? -- Peter M. Groen Open Systems Development Klipperwerf 12 2317 DZ Leiden T : +31-(0)71-5216317 M : +31-(0)6-29563390 E : [EMAIL PROTECTED] Skype : peter_m_groen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting min, max
hi, I think the following link would be some help for you! http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html Leo Huang 2006/2/14, Octavian Rasnita [EMAIL PROTECTED]: Hi, From: Rhino [EMAIL PROTECTED] ... I need to extract a list which the following values from this table, for each hour (in date_time field): - symbol - min(price) - max(price) - price where date_time is the earliest for that certain hour. - price where the date_time is the last from that hour. - The sum of volume from that hour. I have tried to get the list of symbols, then get each hourly period and calculate those 6 values for each period, but there are many symbols and very many periods, and it takes very very much time. Is there a more intelligent way of getting those values in another way than symbol by symbol and period by period? It's hard to answer your question since you haven't given us any examples of the SQL you've already tried. You haven't told us which version of MySQL you are using, either. That makes a big difference since newer versions offer many more SQL capabilities like views and subqueries that could really help you. You certainly shouldn't have to write separate queries for each different symbol that you are using! Here is the table definition. The table is simple, but what I want is complicated: CREATE TABLE `tickers` ( `symbol` varchar(20) NOT NULL, `last_volume` bigint(20) unsigned default NULL, `last_price` decimal(20,4) unsigned default NULL, `last_update` datetime default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I want to get a list of values for more periods of time, 5 minutes, 15 minutes, and hourly. I need to get: symbol date_format(last_update, '%Y-%m-%d') as date date_format(last_update, '%H:%i:%s') as time min(last_price) as low (The min value of last_price for that period) max(last_price) as high (the max price from that period) last_price as open (where last_update=min(last_update) from that period) last_price as close (where last_update=max(last_update) from that period) The result data should look something like: Symbol,data,time,low,high,open,close simb1,2006-02-08,10:15:00,1000,1200,1050,1150 simb1,2006-02-08,10:30:00,1100,1150,1150,1150 simb1,2006-02-08,10:45:00,1000,1200,1050,1150 simb1,2006-02-08,11:00:00,1050,1200,1050,1150 simb1,2006-02-08,11:15:00,1000,1200,1050,1150 ... then here follow the rest of records for simb1 and for other symbols. You may see that the first time is 10:15:00, the next time is 10:30:00, the next is 10:45, so the period of time is 15 minutes. The first low is the lowest price between 10:15:00 and 10:30:00 and the high is the highest price in that period. The first open value is the last_price of the first trade from that period and the close price is the last_price of the latest trade from that period. I don't know if MySQL can create a query that can get those values fast enough. Thank you very much. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
