Re: MySQL 5.0 and Stored Procedures
When you install development test releases (pre-alpha) you are pretty much on your own... Unless any documentation was included with the download, the only other thing around is the blurb on mysql.com http://www.mysql.com/downloads/mysql-5.0.html On Wed, 2003-10-15 at 10:55, Josh Wilson wrote: > I have just recently compiled Mysql 5.0 onto my redhat linux box, and > have been trying to set up some store procedures, but have yet to be > able to figure out how. Can anyone help me on this on how to go about > creating a stored proc in Mysql 5.0?? > > Any help or suggestions would be great. -- Woody In a world without boundaries why do we need Gates and Windows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding time elapsed
SELECT UNIX_TIMESTAMP(timestamp2)-UNIX_TIMESTAMP(timestamp1) as difference from table; gives you the time in seconds between the 2 SELECT sec_to_time(UNIX_TIMESTAMP(timestamp2)-UNIX_TIMESTAMP(timestamp1)) as difference from table; gives you the time in Days+HH:MM type format see for additional functions to vary up what you are trying to get http://www.mysql.com/doc/en/Date_and_time_functions.html On Fri, 2003-10-10 at 12:05, Jeff McKeon wrote: > I have two columns in a table, both timestamp(14). The first one holds > a start date and the second one holds an end date. Is there any built > in mysql function to subtrack timestamp1 from timestamp2 and get the > elapsed time between? > > Thanks, > > Jeff > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Woody In a world without boundaries why do we need Gates and Windows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID, mirror OR replication?
On Mon, 2003-10-06 at 14:21, Richard Reina wrote: > I am wanting to protect myself against future potential hard drive > failures on my DB server running version 3.23.49a. Should I try and set > up a RAID, a mirror or would the best solution be to set up MySQL > replication. Any suggestions would be greatly appreciated. > Richard, if you have the resources available I would suggest doing both RAID and Replication. RAID 5 maximizes your disk space, while making your system pretty fault tolerant. (this of course assumes Hot Swappable SCSI Drives). The replication gives you the added level of fault tolerance, plus on a busy server DB reads can be offloaded to the replicant freeing up resources on the Master. Don't know how familiar you are with RAID so this is a breakdown of the most common options. RAID0 - disk Stripeing (very fast reads but one drive fails and everything is lost). Absolutely no fault tolerance. But an option for a Replicant. RAID1 - disk mirroring (Duplicate copy of everything on another harddrive - the problem is that you have to duplicate your drives. If you have a 80GB disk, you need 2 of them, but you still only use 80GB. RAID0+1 - disk striping w/ Mirroring, you have 2 RAID0 volumes of identical size that mirror to each other. You get the speed of RAID0, and the fault tolerance of RAID1. If you have 2 80GB disk striped, now you need 4 80GB disks and you only get space of 2 of the 80GBs. RAID5 - In my opinion the best choice. You maximize available space, since its (N-1) * Drive capacity. Meaning The number of drives - 1 is your capacity. The equivilent of 1 drive is used to store parity information. If one drive fails, the RAID Controller can autocorrect the missing information on the fly so your system slows down, but stays available. You remove the bad drive, put a new one in, and the new drive gets rebuilt and in a few hours you are back to full steam. You build a raid set with 4 80GB drives, your available capacity would be 240GB (4 Drives - 1 for parity) * 80GB. > Richard > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Woody In a world without boundaries why do we need Gates and Windows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can i make mysql to print date and time automatically?
On Wed, 2003-10-01 at 10:09, Emilio Ruben Estevez wrote: > Hi, im develping an application, and was wondering how can i make mysql get > time and date from pc and print it automatically in the time field and date > field so the user dont have to worry about entering the coorect time and > date. Is this posible, ive created a databse with fields hour(time) and > Date(date) like type but i dont know how to do the mysql to get time and > date and print it! > > Any hints? Heres a hint...type "date" into the search field on www.mysql.com instead of asking the good people here to hold your hand. Yes I am a little irritated at people who don't even make the simplest of efforts to help themselves. Stepping down...and apologizing for the attitude to the good and helpful people here. -- Woody In a world without boundaries why do we need Gates and Windows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unix date problems
While I don't know for sure, my guess is that it would have something to
do with 32 bit as the magic number, but also...being that this won't
become a problem until
mysql> select from_unixtime(2147483647);
+---+
| from_unixtime(2147483647) |
+---+
| 2038-01-18 21:14:07 |
+---+
1 row in set (0.00 sec)
And the 64bit processors and OS's are going to start coming out soon...
As for your other issues...mine doesn't quite wrap like yours does.
mysql> select from_unixtime(2147483648);
+---+
| from_unixtime(2147483648) |
+---+
| 1901-12-13 14:45:52 |
+---+
1 row in set (0.00 sec)
and if we look at the start of time...
mysql> select from_unixtime(0);
+-+
| from_unixtime(0)|
+-+
| 1969-12-31 18:00:00 |
+-+
1 row in set (0.00 sec)
This was done in 3.23.56-log on RH7.3
On Thu, 2003-08-07 at 09:27, Andy Jackman wrote:
> Hi,
> 1) I was investigating the unix_timestamp routine in mysql (version
> 3.23.46-nt) and for some reason the unix epoch (1-1-1970) was returned
> with a value of -3600. Then, as you can see, it suddenly corrected
> itself. I can't replicate this error, but it caused me to shudder.
> Attached is the output from the command line client.
> 2) The function from_unixtime(n) wraps on my server after 2^31 seconds.
> e.g. select from_unixtime(2147483648) returns "1900-01-00 00:00:00". I
> suspect this is caused by the underlying time_t size that mysql was
> compiled with. Is this so? Are any versions of mysql compiled with a
> larger time_t?
> Regards,
> Andy
>
> +---+
> | from_unixtime(2147483648) |
> +---+
> | 1900-01-00 00:00:00 |
> +---+
> 1 row in set (0.00 sec)
>
>
> mysql> select unix_timestamp();
> +--+
> | unix_timestamp() |
> +--+
> | 1060262715 |
> +--+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:00:00');
> +---+
> | unix_timestamp('1970-01-01 00:00:00') |
> +---+
> | -3600 |
> +---+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:00:01');
> +---+
> | unix_timestamp('1970-01-01 00:00:01') |
> +---+
> | -3599 |
> +---+
> 1 row in set (0.00 sec)
>
> mysql> select from_unixtime(0);
> +-+
> | from_unixtime(0)|
> +-+
> | 1970-01-01 00:00:00 |
> +-+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:00:00');
> +---+
> | unix_timestamp('1970-01-01 00:00:00') |
> +---+
> | -3600 |
> +---+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:01:00');
> +---+
> | unix_timestamp('1970-01-01 00:01:00') |
> +---+
> |60 |
> +---+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:02:00');
> +---+
> | unix_timestamp('1970-01-01 00:02:00') |
> +---+
> | 120 |
> +---+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:00:10');
> +---+
> | unix_timestamp('1970-01-01 00:00:10') |
> +---+
> |10 |
> +---+
> 1 row in set (0.02 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:00:09');
> +---+
> | unix_timestamp('1970-01-01 00:00:09') |
> +---+
> | 9 |
> +---+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:00:08');
> +---+
> | unix_timestamp('1970-01-01 00:00:08') |
> +---+
> | 8 |
> +---+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:00:07');
> +---+
> | unix_timestamp('1970-01-01 00:00:07') |
> +---+
> | 7 |
> +---+
> 1 row in set (
INDEX'd fields...any drawbacks?
I have a database, about 12 fields per record and roughly 100,000 records per month. I would do the following query: select count(*), type from TABLENAME where field0='23-23-23' group by type; the query would run an average of about 2secs. Not bad until I am running the same query about 1400 times all with slightly different field0 values. I added an index for field0...with: alter table TABLENAME add index (field0); Now those same queries run about an average of 1/10th of a sec!! My question is, is there a negative effect by indexing this field? Obviously you woudn't index all fields...but could you? Thanks, -- Woody In a world without boundaries why do we need Gates and Windows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT TOP
SELECT h.historyid, h.searchstring, h.serverid, s.shortname FROM history h INNER JOIN servers s ON h.serverid = s.serverid WHERE h.employeeid = 9 ORDER BY h.historyid DESC limit 20; ^ You probably mean LIMIT to limit. On Mon, 2003-07-14 at 17:12, Jim McAtee wrote: > What's wrong with the following query? The application used to use Access via > ODBC, now running MySQL 3.23.xx, MyISAM table, accessed via MyODBC 3.51.01. > > // Return last 20 searches > SELECT TOP 20 h.historyid, h.searchstring, h.serverid, s.shortname > FROM history h > INNER JOIN servers s ON h.serverid = s.serverid > WHERE h.employeeid = 9 > ORDER BY h.historyid DESC > > Works fine once I remove the "TOP 20" from the query. If this isn't supported, > is there an equivalent? > > Thanks, > Jim > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Woody In a world without boundaries why do we need Gates and Windows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: Fw: check
On Mon, 2003-07-14 at 17:16, Alex82 wrote: > I can't use itfor example i have to create a column that accept values > between 1 to 120...i can't enumerate all of themor i have to accept a > value different from NULL if an other column has a certain values, otherwise > the first must be NULL, it's impossible to use enum to do this! > - Original Message - > From: "Michael Brunson" <[EMAIL PROTECTED]> > To: "Alex82" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Tuesday, July 15, 2003 12:13 AM > Subject: Re: Fw: check > > > Use an ENUM() > Then your best option would be to code the limits into your application. -- Woody In a world without boundaries why do we need Gates and Windows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication question...
I am in the process of setting up replication for my mysql servers, and had a couple of questions I wanted to clarify. One of the slaves will be located in an offsite hotsite, and another local. The hotsite slave will only be used to mirror the data, in the event of a failure or accident at the office, the data is safe in the offsite location. The local slave will server 2 purposes, and here is where my question comes in. Currently I shutdown the mysql server once/night for backups. When I get the replication in place I would like to do the backups on the local slave so the master remains up and available 24x7. So 2-3 times per day the slave would go down run a quick backup and get back to its slave duties. When the server goes down for backup, it will pick back up where it was when it comes back online correct? I read in the archives that the replication is near instantanous depending on network connection. Would it be beneficial for the master and slave to have an additional network card and the replication be done over a cross-over cable and therefore out-of-band with the general network traffic? What kind of traffic volume is generated with replication, our database has a pretty steady read/update volume throughout the day and its pretty much 50/50 read/write. I do plan to offload some of the reads (such as for daily reports and informational sites to the slave. Hopefully this isn't too long, and clear. -- Woody In a world without boundaries why do we need Gates and Windows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
