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]
Re: need advice on how to design tables for recurring events
I've been working with Drupal for some time, and there is a module that allows you to create event-based content with a repeating schedule. The schema that it uses may be of some help to you. CREATE TABLE IF NOT EXISTS event_repeat ( rid int(10) unsigned NOT NULL default '0', repeat_data longtext NOT NULL, repeat_RRULE longtext NOT NULL, repeat_COUNT_remaining int(4) NOT NULL default '-1', repeat_start int(10) unsigned NOT NULL default '0', repeat_end int(10) unsigned NOT NULL default '0', repeat_last_rendered int(10) unsigned NOT NULL default '0', PRIMARY KEY (rid) ); CREATE TABLE IF NOT EXISTS event ( rid int(10) unsigned NOT NULL default '0', nid int(10) unsigned NOT NULL default '0', repeat_edited int(2) unsigned NOT NULL default '0', UNIQUE KEY nid (nid), KEY rid (rid) ); CREATE TABLE IF NOT EXISTS event_repeat_calendar_map ( day_stamp char(15) NOT NULL default '', date_stamp int(10) unsigned NOT NULL default '0', day_of_week char(2) NOT NULL default '', day_in_month char(3) NOT NULL default '', day_in_month_R char(4) NOT NULL default '', month_day char(2) NOT NULL default '', month_day_R char(3) NOT NULL default '', month char(2) NOT NULL default '', year_day char(3) NOT NULL default '', year_day_R char(4) NOT NULL default '', week_number char(2) NOT NULL default '', week_number_R char(3) NOT NULL default '', PRIMARY KEY (date_stamp), KEY day_of_week (day_of_week), KEY day_in_month (day_in_month), KEY day_in_month_R (day_in_month_R), KEY month_day (month_day), KEY month_day_R (month_day_R), KEY month (month), KEY year_day (year_day), KEY year_day_R (year_day_R), KEY week_number (week_number), KEY week_number_R (week_number_R) ); HTH -Erich- Frederic Wenzel wrote: 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]
Re: secure host and user name for non static ip address
My take on this never have no password.. At no time any data you have someone else will also want therefore running a open query with nopasword on the system for a couple of hours will get access to your system.. Allways use passwords, If the users dont want passwords then you dont need any membership.. And accept someone will run away with your data. If you are worried about your password or access use ssl across the net, because you cannot trust everyone between you and your site. Also what happens if it goes across a wireless link and all someone is doing is listening.. Regards Michael On 10/10/07, Stephen Sunderlin [EMAIL PROTECTED] wrote: QUESTION: What are the most secure permissions settings for administrator access to connect to my server without using a static IP address? MY ISP changes my DSL ip address almost daily so when I log on to MySQL Administrator with 'myusername'@'currentipaddress' using password 'mypassword' I have grant permission to the new ip address. I also have and account: 'username'@'%' with no password with SELECT, INSERT, UPDATE and DELETE privileges only for general users for this membership site. Are there any security issues with this? Any input or direction for informed reading on the issue would be appreciated. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unbiased opinion needed on access control procedure
Dont trust anything, The more secure you can be the better, What happens if you have not patched a single server (maybe you are testing) then someone gets into it and then has unrestricted access to all the DB's.. Never trust your own users even, If the data is worth money to you it is worth more to your competitors since they dont have to do the leg work to get the data, first into that nice database.. On 10/10/07, Matt Juszczak [EMAIL PROTECTED] wrote: Hi all, I'm looking to get an unbiased opinion of two possible methods for controlling access to database servers. Here are the specs: * 6 database servers, all firewalled off to the outside world, but accessible to each other on port 3306 unrestricted * 10 web servers total, but only half of those need to connect. Which option? A) GRANT replication slave ON *.* TO [EMAIL PROTECTED]; GRANT replication slave ON *.* TO [EMAIL PROTECTED]; GRANT replication slave ON *.* TO [EMAIL PROTECTED]; GRANT SELECT ON database.* TO [EMAIL PROTECTED]; GRANT SELECT ON database.* TO [EMAIL PROTECTED]; B) GRANT REPLICATION SLAVE ON *.* TO ruser; GRANT SELECT on database.* TO otheruser; A: Pro: Granular control per host, although the permissions are always identical, we still block the webservers that don't need access. Con: Hard to manage, 40 webservers = 40 users with the same permissions, 10 slaves = 10 users with the same permissions B: Pro: Simple to manage. One user allows access from anywhere on the internal network (since the servers are firewalled off to the outside) Con: Any server on the internal network can connect as that specific user to the database. Could cause problems with old code, etc. if passwords aren't changed. Which option do you use in your setup? Which do you see more fit? Thanks, Matt -- 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]
sequential numbering in Auto_Increment Field across two tables
I have two tables. TableA is current data. TableB (created with 'Create Table A like Table B' ) is an archive where deleted data is inserted from Table A before being deleted from table B with INSERT INTO TableA Select * , Null, NOW (), 'ACTION', 'USER' from TableA. The null column in TableB is a primary, Not Null AUTO_INCREMENT field. The issue is that the first auto increment number in the primary key of TableA is the next highest value of the AUTO_INCREMENT field of tableA instead of what I would have suspected was 1. Is this normal. Does it matter. And will this create conflict with other quires using LAST_INSERT_ID() Function? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: need advice on how to design tables for recurring events
This and Frederic Wenzel's suggestions were exactly what I was looking for. Thanks! Bob -Original Message- From: Erich C. Beyrent [mailto:[EMAIL PROTECTED] Sent: Thursday, October 11, 2007 6:10 AM To: mysql@lists.mysql.com Subject: Re: need advice on how to design tables for recurring events I've been working with Drupal for some time, and there is a module that allows you to create event-based content with a repeating schedule. The schema that it uses may be of some help to you. CREATE TABLE IF NOT EXISTS event_repeat ( rid int(10) unsigned NOT NULL default '0', repeat_data longtext NOT NULL, repeat_RRULE longtext NOT NULL, repeat_COUNT_remaining int(4) NOT NULL default '-1', repeat_start int(10) unsigned NOT NULL default '0', repeat_end int(10) unsigned NOT NULL default '0', repeat_last_rendered int(10) unsigned NOT NULL default '0', PRIMARY KEY (rid) ); CREATE TABLE IF NOT EXISTS event ( rid int(10) unsigned NOT NULL default '0', nid int(10) unsigned NOT NULL default '0', repeat_edited int(2) unsigned NOT NULL default '0', UNIQUE KEY nid (nid), KEY rid (rid) ); CREATE TABLE IF NOT EXISTS event_repeat_calendar_map ( day_stamp char(15) NOT NULL default '', date_stamp int(10) unsigned NOT NULL default '0', day_of_week char(2) NOT NULL default '', day_in_month char(3) NOT NULL default '', day_in_month_R char(4) NOT NULL default '', month_day char(2) NOT NULL default '', month_day_R char(3) NOT NULL default '', month char(2) NOT NULL default '', year_day char(3) NOT NULL default '', year_day_R char(4) NOT NULL default '', week_number char(2) NOT NULL default '', week_number_R char(3) NOT NULL default '', PRIMARY KEY (date_stamp), KEY day_of_week (day_of_week), KEY day_in_month (day_in_month), KEY day_in_month_R (day_in_month_R), KEY month_day (month_day), KEY month_day_R (month_day_R), KEY month (month), KEY year_day (year_day), KEY year_day_R (year_day_R), KEY week_number (week_number), KEY week_number_R (week_number_R) ); HTH -Erich- Frederic Wenzel wrote: 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/mysql?unsub=robert- [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trigger
Hi, I have a problem with a trigger which should conver a unix timestamp to a MySQL date datatype. The trigger works if the column is varchar, but when the column is date type, it write the date of 1969-31-12. Any ideas? DROP TABLE IF EXISTS `visitas`; CREATE TABLE `visitas` ( `id` int(11) NOT NULL auto_increment, `date` varchar(25) default NULL, `elapsed` int default NULL, `src_ip` varchar(15) default NULL, `result_code` varchar(25) default NULL, `http_status` TINYINT default NULL, `bytes` int default NULL, `request` varchar(50) default NULL, `authname` varchar(10) default NULL, `type` varchar(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; DELIMITER ;; /*!50003 SET SESSION SQL_MODE=STRICT_TRANS_TABLES */;; /*!50003 CREATE */ /*!50017 [EMAIL PROTECTED] */ /*!50003 TRIGGER `unix2normaltime` BEFORE INSERT ON `visitas` FOR EACH ROW begin set New.date=date(from_unixtime(New.date)); end */;; When
open_files_limit problem.
I am trying to change this value and it doesn't seem to work. Looking at the processes I have: mysql 21752 0.0 0.1 1652 1092 p3 I 3:50PM 0:00.01 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysql/nsccmadb2.campus.nscc.ca.pid mysql 21770 0.0 1.3 58188 26168 p3 S 3:50PM 0:00.21 /usr/local/libexec/mysqld --defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --pid-file=/var/db/mysql/nsccmadb2.campus.nscc.ca.pid Within /var/db/mysql/my.cnf I have: [mysqld_safe] open_files_limit = 32768 [mysqld] open_files_limit = 32768 But when I try something like mysql show variables like '%files%';: I get: open_files_limit 11095 Is there another variable that needs to be adjusted to bump this up? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger
Michael, im converting the unixtime to normal time with from_unixtime. So after i did the convertion i write the result to the table. It works ok when i write to a varchar column, but not to a date column - Michael Dykman [EMAIL PROTECTED] escribió: That is a common symptom of some conversion error... A Unix timestamp of '0' in fact always resolved to that specific date. In some earlier, buggier versions of MySQL, negative values would be inteterpreted as seconds-before-the-unix-epoch... now they all are treated as invalid, and hence, guive up the date you see. the thing here is, if your column type is Date and you try to pass it a unix timestamp as in UPDATE visitas SET `date` = `1192109927' you will get an unpredictable result, because that string is not a date. You are trying to transport 2 distinct type (Date and timestamp) in the same typed variable, and that suimply is not going to work. IF your app is supplying the time as a timestamp, have it update a seperate int field, then use the trigger to grab that value and translate it for your Date field. Caveat: timestamps have a short lifespan.. they can not represent dates reliable before Jan 1, 1970.. if you only need it to stamp 'current' events, then go ahead, but if you are trying to track anything historic (like birth dates), they fall apart as soon as a 37-year-old signs up. BTW: you might want to adjust your schema so that you don't use any MySQL keywords as column names (or table names or any other user object)... the example that pops out at me is your field 'Date', which, not surprisingly, is a keyword. this can only lead to greif sooner or later. - michael dykman On 10/11/07, Patricio A. Bruna [EMAIL PROTECTED] wrote: Hi, I have a problem with a trigger which should conver a unix timestamp to a MySQL date datatype. The trigger works if the column is varchar, but when the column is date type, it write the date of 1969-31-12. Any ideas? DROP TABLE IF EXISTS `visitas`; CREATE TABLE `visitas` ( `id` int(11) NOT NULL auto_increment, `date` varchar(25) default NULL, `elapsed` int default NULL, `src_ip` varchar(15) default NULL, `result_code` varchar(25) default NULL, `http_status` TINYINT default NULL, `bytes` int default NULL, `request` varchar(50) default NULL, `authname` varchar(10) default NULL, `type` varchar(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; DELIMITER ;; /*!50003 SET SESSION SQL_MODE=STRICT_TRANS_TABLES */;; /*!50003 CREATE */ /*!50017 [EMAIL PROTECTED] */ /*!50003 TRIGGER `unix2normaltime` BEFORE INSERT ON `visitas` FOR EACH ROW begin set New.date=date(from_unixtime(New.date)); end */;; When -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: secure host and user name for non static ip address
QUESTION: What are the most secure permissions settings for administrator access to connect to my server without using a static IP address? Assuming a unix server (or even a windows server running SSH), use an SSH tunnel, and then connect to 127.0.0.1:whatever_port_you_chose. You get the extra benefit of the traffic being encrypted, plus you don't have to worry about changing the permissions every $x hours when your IP changes. -Doug -- Douglas Phillips Programmer / Database Engineer Cybergroup, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
