Re: need advice on how to design tables for recurring events
On 10/10/07, Ramsey, Robert L [EMAIL PROTECTED] wrote: I'm looking for a best practices way of creating tables to store both one time and regularly repeating events. These are classes, so for the most part the have a regularly recurring time, but we do have some one off events. (...) The only other way I could think of to do it would be to duplicate the cron format and have a table like this: Name, start_day, start_datetime, stop_day, stop_datetime, 'Class-A', '1,3,5','2007-08-20 08:00:00', '2007-12-05 09:00:00' 'Class-D', '3,4,5','2007-08-20 13:00:00', '2007-12-05 14:00:00' And then parse everything, but that seems resource intensive too. Well that doesn't seem to resource intensive to me, however it depends on what you are displaying later. Knowing the amount of times the class meet would be counting the Mondays (+Wednesdays+Fridays) between start_datetime and stop_datetime, but that shouldn't be too bad. What you should never do though is putting different values into the same field -- it defeats the purpose of a relational database. '1,3,5' is therefore a no-go... You ought to make a column for each day of the week and set it 0 for no class and 1 for class, or something along the lines of that. That will also make it insanely easy to retrieve all classes that meet on any given day: SELECT * FROM classes WHERE monday = 1 AND start_datetime = NOW() AND stop_datetime = NOW(); Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sequence ID generation transaction-safe?
Hi, I am using a sequence table as suggested in http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#id2887015 in order to generate new, unique IDs for a table where I have a primary key spanning multiple columns and still need a unique numeric part without being able to use an auto-increment table. I am using UPDATE translations_seq SET id=LAST_INSERT_ID(id+1) and then I fetch my newest ID with select id from translations_seq. While this method is described in the manual as multi-user safe I was wondering if this was also transaction safe? When two users start a transaction at the same time (and don't commit it yet) will they get different IDs? Because the UPDATE statement will not take place yet until COMMITing it, I am unsure if it will actually hand out different IDs for both of the transactions. Can anyone enlighten me? Thanks Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Convert hex to decimal?
On 11/6/06, Dušan Pavlica [EMAIL PROTECTED] wrote:
How would I convert
13 (false decimal) to 0x13 (Hex) and from there to 19 (decimal)?
conv('column_name', 16, 10)
Thanks, that works!
Fred
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Convert hex to decimal?
Hey, I have a table with a SMALLINT column that -- trough a mistake -- contains values like 57, 13 etc. which in fact are 0x57 and 0x13 (i.e. HEX numbers). How would I convert 13 (false decimal) to 0x13 (Hex) and from there to 19 (decimal)? I tried my luck with UNHEX and CAST but I only got 0 or NULL back respectively. Thanks in advance Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple table inserts
Bruce, why do you want to do that at all? If you need to add values to several tables either at once or (in case of an error) not at all, you should use transactions. Fred On 8/15/06, bruce [EMAIL PROTECTED] wrote: - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 15, 2006 7:56 AM Subject: multiple table inserts hi... the mysql docs don't seem to allow this.. but i'll ask. is there a way to accomplish: insert into cat,dog (cat.x,dog.y) values (1,2) allowing me to essentially insert items into multiple tables simultaneously... thanks -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary tables
On 8/15/06, Dan Buettner [EMAIL PROTECTED] wrote: It would be nice if MySQL would have a more generic 'TEMPORARY TABLES' permission that would allow one to create, insert, delete from and drop temporary tables without having to give up insert/update/delete privileges on the real tables Can't you blacklist the real tables in turn? I.e. assign create, insert, update rights to the new user and then assign table specific rights to him as well, revoking these rights for the individual real tables? That's quite a bit more work and has to be maintained everytime you add a new real table (so it's no real replacement for your suggestion above) but it seems to be a quite secure workaround. Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with select distinct
On Tue, 25 Jan 2005 08:44:45 -0500, Michael Stassen [EMAIL PROTECTED] wrote: I suspect mysql is doing the DISTINCT before the ORDER BY. One of the reasons I avoid DISTINCT when possible. Isn't this supposed to be correct? Ordering has to take place as the very last operation, after any selection and projection, doesn't it? Regards Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations with 4.1.7
On Tue, 30 Nov 2004 19:24:05 +0200, Gleb Paharenko [EMAIL PROTECTED] wrote: The first impression is that you forgot to convert character columns. See: http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html http://dev.mysql.com/doc/mysql/en/Charset-conversion.html Once the Character Sets are set up (everything is utf8 now on my installation), how can it be achieved to convert *ALL* columns in *ALL* tables to the same, new COLLATION value? Changing them by hand would lead to admin's fun for, say, weeks ;) Thanks in advance, Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using IN()
Put the 60K ints into a temporary table. Next join against this temporary table. This join forces the optimizer to translate the range into an eq_ref-one of the fastest type joins. He could also simply use a join to the ID table, provided that the 6 IDs are SELECTable in the current database? Anyway, I support your JOIN suggestion as joins are highly optimized and thus quite fast. Regards Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using IN() clause
On Wed, 17 Nov 2004 21:02:27 -0800 (PST), Mitul Bhammar [EMAIL PROTECTED] wrote: The query is running fine for now. I wanted to know how MySQL interprets and executes this query and can it have problems in future?? Read about MySQL's query optimization here: http://dev.mysql.com/doc/mysql/en/Query_Speed.html The Benchmark() function could also be userful for you. Regards Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy data only from one table to another table
On Wed, 17 Nov 2004 18:37:37 -0700, Jim McAtee [EMAIL PROTECTED] wrote: How would this be done if table_2 already exists? It has an auto_increment field as PK and I want to take all the rows from table_1 and dump them into table_2. The records being copied from table_1 can get new primary keys as there are no foreign key relationships to maintain. I suppose the following (provided the table structure of the both tables are exactly the same): first, make a backup ;) then, delete the primary key column of the table to be imported (for correct new primary key values later) create a table dump. do NOT make use of extended inserts, and be sure not to include the table creation commands. only the data itself is needed. Then import the data into the second table and be happy :) Bye Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql NOT operator
On Sat, 06 Nov 2004 20:34:47 -0800, L a n a [EMAIL PROTECTED] wrote: However, NOT operator gives an error: 3. SELECT data_id from table WHERE keyword = a NOT keyword =b ( returns sql error) AND NOT as well as OR NOT should work, I think. How do you think MySQL would be able to distinguish what you mean with NOT instead? Greetz Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advanced SELECT Syntax Help Needed!
On Fri, 5 Nov 2004 17:49:29 -0500, Rhino [EMAIL PROTECTED] wrote: I'm not sure why you want to use a subquery; if MySQL is anything like DB2, a join usually performs better than a subquery and the optimizer converts a subquery to a join (under the covers) whenever it can anyway. Therefore, how about something like: select id, name, linkname1, linkname2 from main m right outer join links1 l1 on m.id = l1.id right outer join links l2 on m.id = l2.id; Yes, indeed joins usually perform better than subselects. I agree to your suggested query, but I think we ought to use LEFT joins here rather than RIGHT joins because Monique wants to receive any main record regardless of the existence of corresponding links. So the query I would use is: SELECT m.id, l1.linkname1, l2.linkname2 FROM main m LEFT JOIN links1 l1 ON (m.id = l1.id) LEFT JOIN links2 l2 ON (m.id = l2.id); Please let us know if it worked, Monique. Regards Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT... SET - SQL standard?
Is the INSERT ... SET syntax (http://dev.mysql.com/doc/mysql/en/INSERT.html) MySQL specific or does it belong to a SQL (89, 92...?) standard? Thanks in advance Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COUNT Problem
A subselect may help: [...] Don't know ATM if it can be done more easily, but a query like this should probably work. It can be done without a sub-query: [...] That *should* work, barring any typos or ommisions I may have made. I used LEFT JOIN because of personal preference, it can be done other way(s). Ah yes, I see. Nice idea. As JOINs perform better than subselects IIRC, your solution ought to be preferred, then. Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COUNT Problem
On Sun, 17 Oct 2004 19:36:34 +, shaun thornburgh [EMAIL PROTECTED] wrote: A Project will have 1 or more tasks assigned to it. Using the following query, how can I modify it so that I can find out the largest number of tasks assigned to a group of projects. SELECT P.*, T.* FROM Projects P, Tasks T WHERE P.Project_ID = T.Project_ID AND Project_ID 2; A subselect may help: SELECT MAX(rows) FROM (SELECT COUNT(Task_ID) AS rows FROM Tasks GROUP BY Project_ID) AS maxi; Don't know ATM if it can be done more easily, but a query like this should probably work. Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: full text search question
GH schrieb: Laura did this work... inquiring minds want to know :) Laura: Perhaps the - is acting like a Boolean operator. What if you put double quotes around your search phrase: SELECT * FROM metadata WHERE MATCH( type ) AGAINST ( '+XY-11443' IN BOOLEAN MODE ); Or.. the - is possibly supposed to be escaped? Let's take a look at the documentation ;) Bye Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to select last date by current time?
This is my current sql query which catches in desc order for dateposted and list only 10 sql data. select dateposted, filename, description from datafiles where office = 'AC/PA' order by dateposted desc limit 10; This will probably work fine. Anyway, make sure that dateposted has DATE field format. Otherwise sorting will probably take much longer and can sometimes have strange effects ;) Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access hosts wildcard.
[...] how do I just 'allow any host'? [...] maybe it's just '%'? AFAIK, that's it. Greetz Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: iptables and mysql...
bruce wrote: i'm investigating what needs to be done to allow mysql on a server to be used remotely by client machines. each machine is running iptables. so i'm wondering what has to be in the iptables for the machine being used as the mysql server, as well as the client machines that will be communicating with the mysql box... The MySQL standard port is 3306/tcp. So your iptables on the MySQL server are supposed to allow communication on this port for the clients. i have rh8.0 and mysql v11.18-dist3.23.54 huh! Either one is quite old. Think about upgrading - it is a security issue on the one hand - on the other hand, it could prevent a lot of headache using current versions. Anyway, some manual reading could help answering your questions. Greetz Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: iptables and mysql...
bruce wrote: we disagree on this... properly configuring/securing the mysql app is both a linux/mysql issue... and reading the manual is an admin issue *sigh*... do so... http://dev.mysql.com/doc/mysql/en/Starting_server.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying a database
Egor Egorov wrote: How would you go about copying a database? I need to make a copy with all the tables and names the same. I just need to name the database something different. If you use only ISAM/MyISAM table, rename database directory. Otherwise create new database, make a dump of tables, restore them into new database, drop the old one. If you use phpmyadmin for administration purposes, you will find very comfortable functions for those tasks there. Greetings Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question about mysql database backup.
Li, Lenny Yong Bo (Lenny) wrote: Now I am assigned to do the backup of a whole mysql database (version: 3.23.36). I want to make sure whether the following procedures are correct for database backup. 1. mysqldump -F --opt database_name database_name.backup In my opinion, you should generally add the -Q option which quotes table and row names. Otherwise, restoring data can be really hard in case of perhaps only one complicated row name... Regards, Fred -- Condense soup, not books! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transfering from access to MySQL?
Jonas Lindén wrote: Hello, Could someone help me with a tip on how I can convert my old Access DBs to MySQL? Hello Jonas, I managed to do this out of Access by exporting the tables to a MyODBC DSN. Please check the column types later - don't know if they are all converted correctly. Regards, Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Encrypt data
Mike Koponick wrote: The idea is that if someone stole the hard drive or computer, it would be hard for someone to break into the database. It seems to me the best way would be to encrypt the drive space, and use the login to authenticate the encryption space on the hard drive. How about sth like that: http://freshmeat.net/projects/cryptofs/ Nothing mysql-specific though, and I know nothing about its speed or usability in general. But maybe you want to give it a try. Greets Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Encryption Issue
[EMAIL PROTECTED] wrote: I would like to encrypt informaton in MySQL DB using the AES_ENCRYPT function, but what if someone looks into the log files? What ever may be logged by the mysql daemon - I can't believe it loggs passwords or the stored data itself. So why do you think so? bye Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by problem
Sagar C Nannapaneni wrote: ASS1 ASS23 ASS4 ASS10 ASS6 . . when i'm retrieving the data by taking ORDER BY clause it is sorting like this ASS1 ASS10 ASS23 ASS4 ASS6 means its only sorting by the 4 the character. No, it's not sorted by the first four characters but it's sorted lexicographically (string-like). The following might help, but will certainly be quite slow: SELECT ... ORDER BY ABS(SUBSTRING(field, 4)); A better (and faster) solution will probably be indexing the records with a numeric field, as usual. Greetz Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left Outer Join?
Mike Mapsnac wrote: What is the difference between Left Join and Left Outer Join? Thanks If I get it right, there is none. Greetings Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB
Hi there, Patrick Fowler wrote: Do I have to compile from source with the innoDB flag in order to use the innoDB tables or just in stall the RPM? As I am using flawlessly working InnoDB tables on an RPM installation of MySQL, I can say: no need to compile from source for InnoDB use. Fred -- Machines certainly can solve problems, store information, correlate, and play games -- but not with pleasure. -- Leo Rosten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback
Am Fr, den 02.01.2004 schrieb karthikeyan.balasubramanian um 09:28: Is there any alternative way to get back to the old state of the database? The only way I can think of is to dump the respective tables (e.g. every night) and re-import them when needed. Note that this can't be done by mysql automatically because - as Chris just said - COMMIT means to commit /now/ and irreversibly. First step: tell this to your users ;) Greetings Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax error
Hi Asif, Asif Iqbal wrote: I have been pushing my syslogs to the following mysql table However whenever it sees lines with a ' (apostrophe) it complains about SQL syntax You need to escape those reserved characters, i.e. have ' replaced by \' because otherwise mysql will treat the apostrophe as the string delimiting character. Greets Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
