Finding gaps in date intervals
Hi, I have a table with this structure: CREATE TABLE dats ( id INT UNSIGNED NOT NULL, id_ref INT UNSIGNED NOT NULL, start DATE NOT NULL, endDATE NOT NULL ) Now I am trying to create a query that will give me all intervals for which the table does not contain an entry grouped by id_ref, e.g. if the table would contain 1, 1, '2000-01-01', '2000-31-12' 2, 1, '2001-02-01', '2002-31-12' I would like to get 1, '2001-01-01', '2001-01-31', as there is a gap between these two entries. Any idea on how to get this? Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Keeping track of db structure changes
> It's really not all that different that source code. For each > application I build, I tend to put the source code in our CVS > repository long with the $foo.sql file(s) to reconstruct the tables. > Just as there's nothing preventing me from deploying a new release of > an application before checking all my changes into CVS, the same is > true of your database structures. Ouch, why haven't I thought of that? I already use CVS and by putting the structure into CVS as well, I can just take the diff. Thanks a lot! Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Keeping track of db structure changes
Hi, is there any way to have MySQL automatically keep track of any changes I do with ALTER TABLE and later spit out the changes again? I use one database (well, actually several, but that's not important here) for development and one database for the production system. Now, whenever I change the db structure, I have to jot it down in a text file and later copy-paste it to the production db. Of course, as anything that is done manually, this can (and already has) lead to problems if the text file is not kept in sync with the changes in the db. So what I'm looking for is something like this: ALTER TABLE tab1 ADD COLUMN acol int; ALTER TABLE tab2 ADD INDEX (anothercol); DISPLAY STRUCTURE CHANGES; RESET CHANGELOG; where of course the last two commands don't exist in MySQL. Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: group by year
> I have a MySQL database containing records from several years. I want to > calculate counts of records for each year and separate values for a couple > of other fields. Each record contains a date-field, date. I tried putting > the function YEAR(date) into a GROUP BY section, but this didnot work. SELECT YEAR(date) AS yr GROUP BY yr Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How can I set a char(2) column to always stay 2 chars?
Hi Dean,
> I have a table with a char(2) and when I send it '02' I want it to stay
> 02,
> but instead it stores 2
>
> How do I make this store as '02'?
Works for me:
mysql> create table t ( a char(2) );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into t values ('02');
Query OK, 1 row affected (0.03 sec)
mysql> select * from t;
+--+
| a|
+--+
| 02 |
+--+
1 row in set (0.00 sec)
Did you add the single ticks around the 02 in the INSERT statement?
Jens
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DRDB vs. MySQL replication
Hi, has anyone used DRDB (http://www.linbit.com/) instead of the standard MySQL replication? DRDB implements a virtual disk mirrored across a local disk and a disk in a remote stand-by server. On first thought, it adds some complexity to setup, but it can also replicate information not written to MySQL but e.g. directly to the filesystem. Any thoughts on this? Jens Vonderheide REDLINK Mediendienste GmbH http://www.redlink.de/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Restoring database from hotcopy
Hi Travis, > I have the database files, now how do I put them back into mysql? mysqlhotcopy simply copies the data files to another directory. To restore them, terminate the mysql server and then copy the files back to the mysql data directory (e.g. /var/lib/mysql/). Note that mysqlhotcopy fails to backup all data if you use table handlers that use different files to store the data (like InnoDB does). Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB: Checking occupied space
Hi, how can I check how much of the InnoDB data files are already taken? Jens (MySQL...) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with a Select
> Message From To > > Exampel 1 2 > > UserID UserName >1 Me >2 You > > Message From To > ExampelMe You SELECT u.Message, u1.UserName AS From, u2.UserName AS To FROM Messages AS m, UsersAS u1, UsersAS u2 WHERE m.From = u1.UserID AND m.To = u2.UserID Using aliases, you can reference the same table multiple times. Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Designing a database to track files
Hi Erik,
> In the "files" table, each row corresponds to a separate file. There's
> all sorts of additional criteria, but the only one that matters to my
> question is the column called "div_id". That's right, the number in
That's exactly what I would recommend you to do. As you wrote, you can
easily add more divisions or change division names without affecting the
data stored in files or modifying the table structure.
> SELECT files.file_name
> FROM files
> WHERE files.file_id = divisions.div_id
> AND divisions.div_name = "division1" ;
That's nearly correct. You could use
SELECT files.file_name
FROM files, divisions
WHERE files.file_id = divisions.div_id
AND divisions.div_name = 'divisions1';
Note that both tables you wish to include in the query need to be written
after the FROM command. This type of query is called a JOIN. You can find
more information on that in the MySQL manual and at
http://www.devshed.com/Server_Side/MySQL/Join. At devshed, they have some
other interesting material on MySQL as well.
Additionally, you should always use single ticks (') around strings. Double
ticks work with MySQL, but since this violates the ANSI SQL standards, you
will run into problems if you ever decide to port your application to
another database system.
Jens
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Storing groups of groups
Hi,
I am planning an authentication system that is structured much like the
system Windows uses:
I have "users" and "groups". Groups can either contain users directly or can
contain other groups (which can again contain groups etc).
Now I am thinking on how to store and efficiently read those definitions. At
first, I thought about the this table structure (leaving out the "users"
table, which at the moment contains nothing but a userid):
CREATE TABLE groups (
id_group INT UNSIGNED NOT NULL,
id_target INT UNSIGNED NOT NULL,
is_group ENUM('yes', 'no')
)
Note that id_target may both reference a user or a group, as determined by
the setting of is_group.
I don't think I can work very efficiently with this "mixed" storage, but I
haven't come up with something better yet.
Also, how would I write a query that gets me all users contained in a given
group, while at the same time dereferencing groups to the user ids contained
in them.
An example to make myself more clear:
- User ids are 1, 2, 3, 4
- Group 1 contains users 1, 2 and 3
- Group 2 contains user 4 and group 1
When I now query for group 2, I want just the user ids, i.e. 1,2,3,4
returned.
Any ideas?
Jens
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL 3.23 Manual - PDF
> If you wouldn't mind, though I think the folks at MySQL.Com > should consider > throwing a copy back up. Maybe I'm just a nervous nelly, but I won't be I agree with that. It wasn't really a problem using a manual for a subrevision higher than the version I used, but to V4, some things changed, so the V4 manual is not really helpful. Perhaps the guys at MySQL could just make an additional Sourceforge project (mysql-doc) and dump the "old" manuals there before removing them from the website. BTW: If you have all the programs available, try a "make manual" in the Docs subdir. Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: UPDATE with values from another table?
> > is it possible to update fields of a table with values taken > from another > > table? Like this (illegal) command would do: > > UPDATE b SET b.v = a.v WHERE b.id = a.id; > > Not yet. > > Multi-table deletes/updates are planned for MySQL 4.0, AFAIK. Then I have to stick to the method I am currently using: Create a new table with the definition from b and use INSERT ... SELECT. Thanks for your answer, Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
UPDATE with values from another table?
Hi, is it possible to update fields of a table with values taken from another table? Like this (illegal) command would do: UPDATE b SET b.v = a.v WHERE b.id = a.id; Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Ordering by given list
Hi, if I use a query like SELECT id FROM table WHERE id IN (7,2,100,65) is it possible to get the results ordered in exactly the same way as the IN list (7,2,100,65)? Mit freundlichen Grüßen, REDLINK Mediendienste Jens Vonderheide, Dipl.-Inform. Telefon: (0171) 8395997 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Primary key ID gets messed up after deleting/adding
> How would you actually overcome that? Wouldn't it be good if > MySQL would be adapted to actually do this for you? I think that not reusing deleted numbers is easier (i.e. more efficient). IIRC, earlier versions of MySQL in fact reused the numbers. There are 2 ways to overcome this: 1) Check if you really need to rely on numbers without any gaps. If you tell us what you want to do, someone on the list may come up with a different approach. 2) If you really need that behaviour, you shouldn't use auto_increment, but write your own functions to get a unique key. I did this once (because I needed to support some RDBMSs without auto_increment). It's not that difficult. Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Primary key ID gets messed up after deleting/adding
> When I first added about 10 records and then deleted a few records and > then added more records it didn't replace the missing records. That's MySQL's usual behaviour. auto_increment only garantees to create unique keys. "Holes" left by deleting data are not filled. Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Need Help with RH 7.0 RPM install
> I have not been able to figure this out, yet. I found the file > /usr/share/mysql/make_binary_distribution and tried a make on it; no > luck. I scoured the documentation on the web and in my own distribution > and have not been able to find out what to do next. See Below. I would > appreciate getting some advise as to where to turn next. On Redhat, you're usually best of when you use the RPM file from the MySQL site. You can install it just like all other RPMs with rpm -ivh The only thing left to do after installation is setting a password for the "root" user. You will get a message after the installation has finished which tells you how to do that. Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: mysql with php4
> Why does it give the old version number ? Have I done something wrong > ? Should I put --with-mysql=/path/to/mysql ? Yes, you should. If you leave out the path, PHP uses a MySQL library shipped with the PHP source code. You should have gotten a warning after the ./configure for PHP, explaining the drawbacks of this. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ./configure help
> err mex: > "configure: error: installation or configuration problem: C > compiler cannot create executables" Take a look at config.log, you will usually find the real cause for the error in there. Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Duplicate of Everything in Table
> So, when I am calling info from this database, I get doubles of > each result. How can I delete just one of each entry, so I am > left with just: Use: DELETE FROM WHERE LIMIT 1 Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: suggestions for implementing heavily used log tables with nightly delete
> I basically have a situation where 300,000+ rows are being inserted into a > log table daily, and also needing to be cleared out based on a > tstamp. What > I've found is that the inserts work fantastic, but when I got to do the > delete, even if it doesn't do anything, it sits there and blocks > everything > on the server for 20-30 minutes. There was quite an interesting article on that subject in the current issue of a German computer magazine called "iX". The biggest problem with deletes seems to be updating the indexes. This has to be done (using the basic DELETE comamnd) for each deleted set. Basically, there are 3 ways to delete: 1) Simply "DELETE WHERE ...": Used with small operations 2) DELETE without indexes: This is done by first deleting the indexes, giving the DELETE command and then recreating the indexes. It is recommended to use this way with tables holding more than 1 data sets, when there are more than 3% of all sets to be deleted. 3) MOVE-RENAME: First, all data sets that are not to be deleted are copied to a temporary table, then the original table is DROPped, and finally the temporary table is renamed to the original one's name. This method is recommended under the following circumstances: Number of data sets | Percentage of sets to be deleted >= 20 | >=80% >= 40 | >=70% >= 70 | >=60% >= 100| >=50% HTH, Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Table Full Error
> I tried to query below, > "select src_ip, byte, packet from table group by src_ip order by > bytes desc limit 10" > > Then DB said, "ERROR 1114: The table 'SQL2997368_0' is full." MySQL tries to create a temporary table to handle your "order by" command. These tables are usually created in /tmp (please correct me if i'm wrong). So check if /tmp is not null and writeable on your server. Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Failover and Replication [HOWTO]
Hi Bogomolnyi, I am really interested in your scripts. I don't have a use for them at this time, but I am planning to use a fallback system myself in the next few month. > 5 pc , and I use it for a web cluster (10 pc) so I use 1 slave > for 2 httpds Do you have a similar scheme for the web server? > For the moment after an server fail , i sync it back using a > simple script , > the watch dog don't do it because i dont want a buggy server coming in and > out > the party . That's totally acceptable. The fallback when a server dies has do be automatically, because it must be fast. Bringing back a server can take some time. Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
