Re: forum vs email [was: Re: table-for-column]
- Original Message - From: Jigal van Hemert ji...@xs4all.nl Subject: Re: forum vs email [was: Re: table-for-column] On typo3.org there used to be mailing lists only in a distant past. Later on newsgroups were set up which communicate with the mailing lists (newsgroups are the central source of messages). Rather recently a forum was built on top of the newsgroup data (FUD forum was used). Users on all three message sources can easily communicate with eachother. Hmm. That sounds interesting, I'll have a look. I don't suppose the software is available under a foss license? :-p -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email [was: Re: table-for-column]
- Original Message - From: Johan De Meersman vegiv...@tuxera.be Sent: Wednesday, 10 December, 2014 09:02:45 Subject: Re: forum vs email [was: Re: table-for-column] Hmm. That sounds interesting, I'll have a look. I don't suppose the software is available under a foss license? :-p Hm. Typo3 is a CMS; I take it the integration you're speaking of is specific to their support environment, and not part of the CMS? -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email [was: Re: table-for-column]
Hi, On 10/12/2014 09:02, Johan De Meersman wrote: - Original Message - From: Jigal van Hemert ji...@xs4all.nl Subject: Re: forum vs email [was: Re: table-for-column] On typo3.org there used to be mailing lists only in a distant past. Later on newsgroups were set up which communicate with the mailing lists (newsgroups are the central source of messages). Rather recently a forum was built on top of the newsgroup data (FUD forum was used). Users on all three message sources can easily communicate with eachother. Hmm. That sounds interesting, I'll have a look. I don't suppose the software is available under a foss license? :-p Mailing lists [1] themselves use Mailman [2] (GPL), forum [3] is done with FUD forum [4] (FOSS GPL2), for integration between mailing lists and newsgroups, please contact the TYPO3 server admin team [5]. I'm sure they will tell you all you want to know about this setup. [1] http://lists.typo3.org/cgi-bin/mailman/listinfo [2] http://www.gnu.org/software/mailman/ [3] http://forum.typo3.org/ [4] http://cvs.prohost.org/index.php [5] http://typo3.org/teams/server-team/ -- Met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email [was: Re: table-for-column]
Hi, On 10/12/2014 10:09, Johan De Meersman wrote: Hm. Typo3 is a CMS; I take it the integration you're speaking of is specific to their support environment, and not part of the CMS? Correct, TYPO3 is a CMS (also FOSS GPL2+) and the integration is indeed not part of the CMS. See my other reply for details on the software that was used. -- Met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email [was: Re: table-for-column]
- Original Message - From: Jan Steinman j...@ecoreality.org Subject: Re: forum vs email [was: Re: table-for-column] There actually seem to be a lot of these around. I'm on several that send me email when there are new forum postings. Yes, that bit is pretty standard functionality; but usually they're little more than a notification that something was posted, maybe the first few lines of a post. I want: * The entire post, and as little notification-type content as possible, * headers and subjects so that mail clients that support threading will thread everything from a single forum topic in a mail thread and vice versa, * and, most importantly, the ability to also *reply* through mail and have it appear in the forum thread at the appropriate place in the conversation Those things are what would make it a proper mailing list integration, instead of just another notification tool. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email [was: Re: table-for-column]
Hi, On 05/12/2014 20:54, Jan Steinman wrote: From: Johan De Meersman vegiv...@tuxera.be I've long wanted to - but never quite got around to - write a forum that integrated a mailing list. Bar mail clients that don't handle list threads well, it really doesn't seem such a difficult task. There actually seem to be a lot of these around. I'm on several that send me email when there are new forum postings. On typo3.org there used to be mailing lists only in a distant past. Later on newsgroups were set up which communicate with the mailing lists (newsgroups are the central source of messages). Rather recently a forum was built on top of the newsgroup data (FUD forum was used). Users on all three message sources can easily communicate with eachother. Only some mail clients have difficulty keeping the threading headers in tact, but other than that there are no real issues. -- Met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email [was: Re: table-for-column]
I have been a resident of this list for a very long time. In the early days, this was the only place to get reliable information about what was then a relatively obscure database system. Now, local and online bookstores have shelves full of books, many of them authored by list regulars. We have expert forums which have become more more mature and tens of thousands of example projects readily accessible on github and Google code. And, lest we forget, as the product and the documentation matured, fewer desperate situations arose. The list of not such a critical last resort as it once was. It's true that the list lost a lot of steam after the Oracle acquisition and Monty's rants had a polarizing effect. Since then, it has been low traffic with few threads of much interest. In spite of the rapid rise of NoSql, managed instances of MySQL on a cloud have become a major commodity. The relational model is not dead and reliable implementations will always be in demand. On Sat, 6 Dec 2014 15:53 Jigal van Hemert ji...@xs4all.nl wrote: Hi, On 05/12/2014 20:54, Jan Steinman wrote: From: Johan De Meersman vegiv...@tuxera.be I've long wanted to - but never quite got around to - write a forum that integrated a mailing list. Bar mail clients that don't handle list threads well, it really doesn't seem such a difficult task. There actually seem to be a lot of these around. I'm on several that send me email when there are new forum postings. On typo3.org there used to be mailing lists only in a distant past. Later on newsgroups were set up which communicate with the mailing lists (newsgroups are the central source of messages). Rather recently a forum was built on top of the newsgroup data (FUD forum was used). Users on all three message sources can easily communicate with eachother. Only some mail clients have difficulty keeping the threading headers in tact, but other than that there are no real issues. -- Met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: table-for-column
- Original Message - From: Shawn Green shawn.l.gr...@oracle.com Subject: Re: table-for-column My problem is a lack of time. I can monitor the mailing lists or the forums but rarely both while still doing my regular job of handling the official service requests. I've long wanted to - but never quite got around to - write a forum that integrated a mailing list. Bar mail clients that don't handle list threads well, it really doesn't seem such a difficult task. I never understood why nobody ever did it, as it allows members of a community to interface with it through their preferred means. Perhaps one of those web2.0 types will eventually get around to it :-) -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: table-for-column
On 2014-12-04 9:56 PM, shawn l.green wrote: On 12/1/2014 6:09 AM, Johan De Meersman wrote: - Original Message - From: peter brawley peter.braw...@earthlink.net Subject: Re: table-for-column I wonder if anyone knows why sites like Stack Overflow and those of ours I mentioned are seeing more volume, while this list and all MySQL fora are seeing much, much less. The major benefit I see on StackOverflow and the like, is the rating system on the comments, and to some extent the rating system for users. On the other hand, I find that the signal-to-noise ratio on older media like mailing lists and IRC tends to be much more favourable, presumably because it is where the dinosaurs dwell :-) A lot of new users may only use the MySQL Forums Forum use is radically down also. PB and not even know about this mailing list. I guess this email-based peer-to-peer exchange is slowly disappearing into the background like the old usenet newsgroups, eh? http://forums.mysql.com/ My problem is a lack of time. I can monitor the mailing lists or the forums but rarely both while still doing my regular job of handling the official service requests. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email [was: Re: table-for-column]
From: Johan De Meersman vegiv...@tuxera.be I've long wanted to - but never quite got around to - write a forum that integrated a mailing list. Bar mail clients that don't handle list threads well, it really doesn't seem such a difficult task. There actually seem to be a lot of these around. I'm on several that send me email when there are new forum postings. Here is one: http://www.mobileread.com Based on hints in the html comments, they appear to be using VBulletin (http://www.vbulletin.com/) a fairly common forum package. Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email [was: Re: table-for-column]
2014/12/04 22:56 -0500, shawn l.green I guess this email-based peer-to-peer exchange is slowly disappearing into the background like the old usenet newsgroups, eh? And _I_ like using an off-line e-mail client, and not being bothered by going through a webbrowser--but I suspect that others prefer not to have an e-mail client, and prefer to have the freedom to use small, sophisticated gadgets instead of bigger gadgets that sit on the table, or take most of a lap. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: table-for-column
On 12/1/2014 6:09 AM, Johan De Meersman wrote: - Original Message - From: peter brawley peter.braw...@earthlink.net Subject: Re: table-for-column I wonder if anyone knows why sites like Stack Overflow and those of ours I mentioned are seeing more volume, while this list and all MySQL fora are seeing much, much less. The major benefit I see on StackOverflow and the like, is the rating system on the comments, and to some extent the rating system for users. On the other hand, I find that the signal-to-noise ratio on older media like mailing lists and IRC tends to be much more favourable, presumably because it is where the dinosaurs dwell :-) A lot of new users may only use the MySQL Forums and not even know about this mailing list. I guess this email-based peer-to-peer exchange is slowly disappearing into the background like the old usenet newsgroups, eh? http://forums.mysql.com/ My problem is a lack of time. I can monitor the mailing lists or the forums but rarely both while still doing my regular job of handling the official service requests. -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: table-for-column
- Original Message - From: peter brawley peter.braw...@earthlink.net Subject: Re: table-for-column I wonder if anyone knows why sites like Stack Overflow and those of ours I mentioned are seeing more volume, while this list and all MySQL fora are seeing much, much less. The major benefit I see on StackOverflow and the like, is the rating system on the comments, and to some extent the rating system for users. On the other hand, I find that the signal-to-noise ratio on older media like mailing lists and IRC tends to be much more favourable, presumably because it is where the dinosaurs dwell :-) -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: table-for-column
On 2014-11-27 9:31 AM, h...@tbbs.net wrote: 2014/11/26 20:06 -0600, Peter Brawley Why do you call it a hack, you get outta bed on the wrong side? 2014/11/27 14:08 +0100, Johan De Meersman Doesn't really belong on the list; but I'd love to hear reasonable arguments why that would be a bad thing in and of itself. Why is it that webdevs are so condescending about tables? Excellent question (though not for this list). Re how our MySQL tips aggregator page was written: I think the only relevant MySQL point may be that it's driven by an edge list model of a hierarchy implemented in a pair of MySQL tables. I wonder if anyone knows why sites like Stack Overflow and those of ours I mentioned are seeing more volume, while this list and all MySQL fora are seeing much, much less. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
mysql tables are lost for DDL of alter table .. add column ...
mysql tables are lost for DDL of alter table .. add column ... *1. mysqld's error.log* 110803 3:39:16 InnoDB: Warning: problems renaming 'feel_22/#sql-2635_23d3a8' to 'feel_22/feed_send_1451', 25000 iterations （first 25000，fil0fil.c:: fil_rename_tablespace） InnoDB: Warning: tablespace './feel_22/#sql-2635_23d3a8.ibd' has i/o ops stopped for a long time 24999 （fil0fil.c:: fil_mutex_enter_and_prepare_for_io） 110803 3:39:16 InnoDB: Warning: problems renaming 'feel_22/#sql-2635_23d3a8' to 'feel_22/feed_send_1451', 25001 iterations （over 25000, return FALSE） 110803 3:39:16 [ERROR] Cannot find or open table feel_22/feed_send_1451 from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? or, the table contains indexes that this version of the engine doesn't support. there is another interesting error info: Error 1005: Can't create table 'feel_01.#sql-57f0_25a510' (errno: -1) *2. rename tablespace can be successful only if:* if (node-n_pending 0 || node-n_pending_flushes 0) if (node-modification_counter node-flush_counter) *3. we failed to repeat this bug and failed to locate the real reason. * Here is the information we got: * it's single table space with innodb plugin 1.0.9 and mysql-5.1.48. we failed to repeat this issue. * DDL is done at mid-night, and workload is very very slow(both master and slave suffered with issue but with very very low probability). * for 100G ibd file, fsync is so quickly that retry number is 0 for rename condition waiting, so we don't doult the n_pending_flushes. * all the sql are blocked when 2 retry first hit, but unfornantely no other stack/core info saved. * we suspect the io handlers are out-of-order,io_handler_threads and srv_master_thread maybe all os_event_wait_low. We can not prove the deadlock situation, but if the deadlock is exist in such situation, the patch maybe help us to suffering occasional table losting for DDL --- /tmp/mysql-5.1.48/storage/innodb_plugin/fil/fil0fil.c 2010-06-03 23:50:08.0 +0800 +++ storage/innodb_plugin/fil/fil0fil.c 2011-08-11 00:23:31.0 +++ +0800 @@ -938,8 +938,24 @@ mutex_exit(fil_system-mutex); + +#ifndef UNIV_HOTBACKUP +/* Wake the i/o-handler threads to make sure pending i/o's are + performed */ +os_aio_simulated_wake_handler_threads(); + os_thread_sleep(2); + /* Flush tablespaces so that we can close modified files in the LRU + list */ + +fil_flush_file_spaces(FIL_TABLESPACE); +#else + + os_thread_sleep(2); +#endif + + count2++; goto retry; @@ -2457,6 +2473,11 @@ fputs( to , stderr); ut_print_filename(stderr, new_name); fprintf(stderr, , %lu iterations\n, (ulong) count); + if (node) + fprintf(stderr, node info: n_pending=%lu, n_pending_flushes=%lu +modification_counter=%lu, flush_counter=%lu\n, +node-n_pending, node-n_pending_flushes, node-modification_counter, +node-flush_counter); } mutex_enter(fil_system-mutex); *4. we need yours help to solve the table lost issue*
Re: Alter Table Add Column - How Long to update
On Fri, 2006-10-20 at 09:06 -0700, William R. Mussatto wrote: On Thu, October 19, 2006 18:24, Ow Mun Heng said: Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? Basic process for any change which modifies the structure of the table is to create a temporary table with the new structure, copy the information from the old table table to the new one (modifying as needed to match the new structure), drop the old table and rename the new table to the old tables name. So the time might be realistic. It depends on the hardware you are using and what else is going on on the system. I'm just a bit curious as to why this happens. Looking at the manuals / books etc, it says this is done so that other read processes can still access the DB/table in it's OLD state w/o any hiccups. I'm just not too sold on that idea given that, for eg: a MSSQL server, adds a new column in just secs rather than minutes on MySQL. But anyway, I do understand what is happening right now. Thanks. (So, if I were to want to add a column to a Table which has a couple of million rows, It would take a _looong_ time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update
it says this is done so that other read processes can still access the DB/table in it's OLD state w/o any hiccups. I do not understand so how does the MSSQL DB work when we alter table add column* * 2006/10/22, Ow Mun Heng [EMAIL PROTECTED]: On Fri, 2006-10-20 at 09:06 -0700, William R. Mussatto wrote: On Thu, October 19, 2006 18:24, Ow Mun Heng said: Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? Basic process for any change which modifies the structure of the table is to create a temporary table with the new structure, copy the information from the old table table to the new one (modifying as needed to match the new structure), drop the old table and rename the new table to the old tables name. So the time might be realistic. It depends on the hardware you are using and what else is going on on the system. I'm just a bit curious as to why this happens. Looking at the manuals / books etc, it says this is done so that other read processes can still access the DB/table in it's OLD state w/o any hiccups. I'm just not too sold on that idea given that, for eg: a MSSQL server, adds a new column in just secs rather than minutes on MySQL. But anyway, I do understand what is happening right now. Thanks. (So, if I were to want to add a column to a Table which has a couple of million rows, It would take a _looong_ time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update
On Sun, 2006-10-22 at 21:32 +0800, 黄小聪 wrote: it says this is done so that other read processes can still access the DB/table in it's OLD state w/o any hiccups. I do not understand so how does the MSSQL DB work when we alter table add column Frankly, I don't really know. I tried adding a new column to an existing MSSQL DB and it really just took a few seconds. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update
On Thu, October 19, 2006 18:24, Ow Mun Heng said: Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? I'm using InnoDB by the way Basic process for any change which modifies the structure of the table is to create a temporary table with the new structure, copy the information from the old table table to the new one (modifying as needed to match the new structure), drop the old table and rename the new table to the old tables name. So the time might be realistic. It depends on the hardware you are using and what else is going on on the system. Hope this helps. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alter Table Add Column - How Long to update
Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? I'm using InnoDB by the way -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alter Table Add Column - How Long to update?
Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? Btw, where is this temporary table? I don't see it in the DB. and I don't see it in the default /tmp directory. I'm using InnoDB by the way. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update?
On Wed, 2006-10-18 at 09:29 +0800, Ow Mun Heng wrote: Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? Btw, where is this temporary table? I don't see it in the DB. and I don't see it in the default /tmp directory. This answers some of the questions. http://dev.mysql.com/doc/refman/5.0/en/temporary-files.html I'm using InnoDB by the way. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: calender table - time column?
guess this was too much information for you all! Or too complicated :) Well
this is what we came up with, I would like to know your opinions if you have
any? I can see two flaws in the design for the Time/Date so I really would be
interested in any of your views :)
CREATE TABLE booking (
id varchar(4) NOT NULL default '',
month char(3) NOT NULL default '',
date varchar(4) NOT NULL default '',
day char(3) NOT NULL default '',
year varchar(4) NOT NULL default '',
one varchar(50) NOT NULL default '',
two varchar(50) NOT NULL default '',
three varchar(50) NOT NULL default '',
four varchar(50) NOT NULL default '',
five varchar(50) NOT NULL default '',
six varchar(50) NOT NULL default '',
seven varchar(50) NOT NULL default '',
eight varchar(50) NOT NULL default '',
nine varchar(50) NOT NULL default '',
ten varchar(50) NOT NULL default '',
eleven varchar(50) NOT NULL default '',
twelve varchar(50) NOT NULL default '',
thirteen varchar(50) NOT NULL default '',
fourteen varchar(50) NOT NULL default '',
fithteen varchar(50) NOT NULL default '',
sixteen varchar(50) NOT NULL default '',
seventeen varchar(50) NOT NULL default '',
eighteen varchar(50) NOT NULL default '',
nineteen varchar(50) NOT NULL default '',
twenty varchar(50) NOT NULL default '',
twentone varchar(50) NOT NULL default '',
twentytwo varchar(50) NOT NULL default '',
twentythree varchar(50) NOT NULL default '',
twentyfour varchar(50) NOT NULL default '',
places char(2) NOT NULL default '24'
) TYPE=MyISAM;
#
#
# Table structure for table `flights`
#
CREATE TABLE flights (
day char(3) NOT NULL default '',
flightone varchar(5) NOT NULL default '',
flighttwo varchar(5) NOT NULL default '',
flightthree varchar(5) NOT NULL default '',
flightfour varchar(5) NOT NULL default ''
) TYPE=MyISAM;
#
#
# Table structure for table `guests`
#
CREATE TABLE guests (
surname varchar(50) NOT NULL default '',
firstname varchar(50) NOT NULL default '',
email varchar(50) NOT NULL default '',
phone varchar(50) NOT NULL default '',
date varchar(15) NOT NULL default '',
places char(2) NOT NULL default '',
flight varchar(5) NOT NULL default '',
ref varchar(50) NOT NULL default ''
) TYPE=MyISAM;
INSERT INTO flights VALUES ('Mon', '06:00', '12:00', '15:30', '18:00');
INSERT INTO flights VALUES ('Tue', '01:00', '02:00', '11:00', '23:00');
INSERT INTO flights VALUES ('Wed', '00:00', '14:00', '', '');
INSERT INTO flights VALUES ('Thu', '08:23', '09:16', '17:21', '22:09');
INSERT INTO flights VALUES ('Fri', '03:55', '', '', '');
INSERT INTO flights VALUES ('Sat', '07:22', '13:45', '', '');
INSERT INTO flights VALUES ('Sun', '05:33', '09:40', '23:12', '');
$query = select id,day from booking where month='$month' and date='$day' and
year='$year';
$result = @mysql_db_query ($database, $query);
if ($result){
$dbid = mysql_result ($result, 0, id);
echo $dbid;
echo 'br';
for ($i = $dbid; $i $dbid+5; $i++){
$query2 = select places from booking where id = '$i';
$result2 = @mysql_db_query ($database, $query2);
$dbplaces = mysql_result ($result2, 0, places);
if ($place = $dbplaces){
if ($i==$dbid+4){
$dbday = mysql_result ($result, 0, day);
header(Location:
continue.php?day=$dbdayid=$dbidplaces=$place);
}
}else{
header(Location: booking.php?advice=sorry);
break;
}
}
}
Thanks
Andrew
-Original Message-
From: Andrew [mailto:[EMAIL PROTECTED]
Sent: 01 August 2003 17:26
To: Keith C. Ivey; [EMAIL PROTECTED]
Subject: RE: calender table - time column?
Thanks Keith here it is :)
The booking system will take the format of a form. Customer or travel agent can
select a date of travel. They will also input how many places are needed.
The system will then check that the selected dates are available. At this point
there are two possible responses.
1)
Places are not available: System will bring user back to 1st page and ask them
to choose another date.
2)
Places are available.
System will look at the date of the holiday and check to see if that day is a
Mon, Tue, Wed, etc. Depending on the result a list of possible flights
and costs
will be displayed (pulled from the data base)
User will choose their flight.
At this point the system will move the user to a payment area. Where
all details
are filled out and will be sent for payment and also e-mail sent to Admin with
booking details. The booking system will also now be updated so that
no one else
can book those dates (up to 24 persons rotating).
To deal with the travel agent commission a reference input field for the travel
agent to fill in on the e-mail/payment form. With a description similar to;
If you have a reference code
Re: calender table - time column?
* Andrew guess this was too much information for you all! Or too complicated :) ...or maybe too vague? ;) Well this is what we came up with, I would like to know your opinions if you have any? ok, but be warned, I would have done this _totally_ different... :) I can see two flaws in the design for the Time/Date so I really would be interested in any of your views :) CREATE TABLE booking ( id varchar(4) NOT NULL default '', month char(3) NOT NULL default '', date varchar(4) NOT NULL default '', day char(3) NOT NULL default '', year varchar(4) NOT NULL default '', one varchar(50) NOT NULL default '', two varchar(50) NOT NULL default '', three varchar(50) NOT NULL default '', four varchar(50) NOT NULL default '', five varchar(50) NOT NULL default '', six varchar(50) NOT NULL default '', seven varchar(50) NOT NULL default '', eight varchar(50) NOT NULL default '', nine varchar(50) NOT NULL default '', ten varchar(50) NOT NULL default '', eleven varchar(50) NOT NULL default '', twelve varchar(50) NOT NULL default '', thirteen varchar(50) NOT NULL default '', fourteen varchar(50) NOT NULL default '', fithteen varchar(50) NOT NULL default '', sixteen varchar(50) NOT NULL default '', seventeen varchar(50) NOT NULL default '', eighteen varchar(50) NOT NULL default '', nineteen varchar(50) NOT NULL default '', twenty varchar(50) NOT NULL default '', twentone varchar(50) NOT NULL default '', twentytwo varchar(50) NOT NULL default '', twentythree varchar(50) NOT NULL default '', twentyfour varchar(50) NOT NULL default '', places char(2) NOT NULL default '24' ) TYPE=MyISAM; # You seem to use string column types for everything? You should use the 'correct' column types whenever possible, it will make the database faster and lighter. URL: http://www.mysql.com/doc/en/Column_types.html Also, you should define indexes. In this early stage of your project all needed index are not yet known, but you should at least define primary keys. URL: http://www.mysql.com/doc/en/MySQL_indexes.html URL: http://www.mysql.com/doc/en/CREATE_INDEX.html month, date, day and year can/should be stored in a single column. The above table should imo have been split into three tables: CREATE TABLE booking ( b_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, booking DATETIME); CREATE TABLE person ( p_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50)); This person table is probably the same thing as your 'guests' table below. If so, add the needed columns from that table to the 'person' table, and drop the 'guests' table (or the other way around, if you prefer). CREATE TABLE booking_person ( b_id INT UNSIGNED NOT NULL, p_id INT UNSIGNED NOT NULL, booking_time TIMESTAMP, PRIMARY KEY (b_id,p_id), UNIQUE KEY (p_id,b_id)); The 'booking_time' column is just a suggestion, because it is maintained 'for free': the TIMESTAMP column type is 'magic' and is automatically set to the current time when the record is created or changed. It could be usefull to know _when_ a booking has been made, right? Read about the TIMESTAMP datatype here: URL: http://www.mysql.com/doc/en/DATETIME.html To find how many persons are booked on a flight: SELECT COUNT(*) AS booking_count FROM booking_person NATURAL JOIN booking WHERE booking.booking = '2003-08-10 15:30' NATURAL JOIN can be used in this example because there is only the 'b_id' column name that is common between the two tables. Read about NATURAL JOIN and other joins here: URL: http://www.mysql.com/doc/en/JOIN.html # # Table structure for table `flights` # CREATE TABLE flights ( day char(3) NOT NULL default '', flightone varchar(5) NOT NULL default '', flighttwo varchar(5) NOT NULL default '', flightthree varchar(5) NOT NULL default '', flightfour varchar(5) NOT NULL default '' ) TYPE=MyISAM; # This design will break when there are five or more flights any one day. Also, there is no way to define exceptions: what if the next monday is a national holliday, and some of the flights won't be flying? I guess your design reflects the normal situation: most mondays there are four flights, at 06:00, 12:00, 15:30 and 18:00. You would not want to put a record in the flights table for _every_ monday, but it would be nice to be able to define exceptions, wouldn't it? Furthermore, weekdays seems to be a relevant attribute in this system, usually it is only an output attribute, created by the server during query execution, using DATE_FORMAT(date_column,'%a') or similar. Read about DATE_FORMAT() and a heap of other date time related functions here: URL: http://www.mysql.com/doc/en/Date_and_time_functions.html Consider this design: CREATE TABLE flights ( f_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
RE: calender table - time column?
Hi Roger this was very useful indeed thank you. I would really like to know any other views on this design and implications for the future development of such a system in terms of flexibility Andrew -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: 02 August 2003 16:49 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: calender table - time column? * Andrew guess this was too much information for you all! Or too complicated :) ...or maybe too vague? ;) Well this is what we came up with, I would like to know your opinions if you have any? ok, but be warned, I would have done this _totally_ different... :) I can see two flaws in the design for the Time/Date so I really would be interested in any of your views :) CREATE TABLE booking ( id varchar(4) NOT NULL default '', month char(3) NOT NULL default '', date varchar(4) NOT NULL default '', day char(3) NOT NULL default '', year varchar(4) NOT NULL default '', one varchar(50) NOT NULL default '', two varchar(50) NOT NULL default '', three varchar(50) NOT NULL default '', four varchar(50) NOT NULL default '', five varchar(50) NOT NULL default '', six varchar(50) NOT NULL default '', seven varchar(50) NOT NULL default '', eight varchar(50) NOT NULL default '', nine varchar(50) NOT NULL default '', ten varchar(50) NOT NULL default '', eleven varchar(50) NOT NULL default '', twelve varchar(50) NOT NULL default '', thirteen varchar(50) NOT NULL default '', fourteen varchar(50) NOT NULL default '', fithteen varchar(50) NOT NULL default '', sixteen varchar(50) NOT NULL default '', seventeen varchar(50) NOT NULL default '', eighteen varchar(50) NOT NULL default '', nineteen varchar(50) NOT NULL default '', twenty varchar(50) NOT NULL default '', twentone varchar(50) NOT NULL default '', twentytwo varchar(50) NOT NULL default '', twentythree varchar(50) NOT NULL default '', twentyfour varchar(50) NOT NULL default '', places char(2) NOT NULL default '24' ) TYPE=MyISAM; # You seem to use string column types for everything? You should use the 'correct' column types whenever possible, it will make the database faster and lighter. URL: http://www.mysql.com/doc/en/Column_types.html Also, you should define indexes. In this early stage of your project all needed index are not yet known, but you should at least define primary keys. URL: http://www.mysql.com/doc/en/MySQL_indexes.html URL: http://www.mysql.com/doc/en/CREATE_INDEX.html month, date, day and year can/should be stored in a single column. The above table should imo have been split into three tables: CREATE TABLE booking ( b_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, booking DATETIME); CREATE TABLE person ( p_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50)); This person table is probably the same thing as your 'guests' table below. If so, add the needed columns from that table to the 'person' table, and drop the 'guests' table (or the other way around, if you prefer). CREATE TABLE booking_person ( b_id INT UNSIGNED NOT NULL, p_id INT UNSIGNED NOT NULL, booking_time TIMESTAMP, PRIMARY KEY (b_id,p_id), UNIQUE KEY (p_id,b_id)); The 'booking_time' column is just a suggestion, because it is maintained 'for free': the TIMESTAMP column type is 'magic' and is automatically set to the current time when the record is created or changed. It could be usefull to know _when_ a booking has been made, right? Read about the TIMESTAMP datatype here: URL: http://www.mysql.com/doc/en/DATETIME.html To find how many persons are booked on a flight: SELECT COUNT(*) AS booking_count FROM booking_person NATURAL JOIN booking WHERE booking.booking = '2003-08-10 15:30' NATURAL JOIN can be used in this example because there is only the 'b_id' column name that is common between the two tables. Read about NATURAL JOIN and other joins here: URL: http://www.mysql.com/doc/en/JOIN.html # # Table structure for table `flights` # CREATE TABLE flights ( day char(3) NOT NULL default '', flightone varchar(5) NOT NULL default '', flighttwo varchar(5) NOT NULL default '', flightthree varchar(5) NOT NULL default '', flightfour varchar(5) NOT NULL default '' ) TYPE=MyISAM; # This design will break when there are five or more flights any one day. Also, there is no way to define exceptions: what if the next monday is a national holliday, and some of the flights won't be flying? I guess your design reflects the normal situation: most mondays there are four flights, at 06:00, 12:00, 15:30 and 18:00. You would not want to put a record in the flights table for _every_ monday, but it would be nice to be able to define exceptions, wouldn't it? Furthermore, weekdays seems to be a relevant attribute in this system
RE: calender table - time column?
Hi did anyone reply to this? Andrew I am currently in the process of building a calender/date MySQL table that will be searched for available dates for holiday tours? I want to use a time column as there will be a restriction on places for up to 3 days so this will have to be taken into account. Has anyone done something like this? Cheers Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.504 / Virus Database: 302 - Release Date: 24/07/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: calender table - time column?
On 1 Aug 2003 at 17:08, Andrew wrote: Hi did anyone reply to this? It doesn't look like it. Pretend you don't know anything at all about your project and try reading your message. Would you have any idea what sort of answer was wanted? If you want help, you have to explain exactly what's needed and provide details. Otherwise your message is likely to be ignored. I am currently in the process of building a calender/date MySQL table that will be searched for available dates for holiday tours? I want to use a time column as there will be a restriction on places for up to 3 days so this will have to be taken into account. Has anyone done something like this? Cheers Andrew -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: calender table - time column?
Thanks Keith here it is :) The booking system will take the format of a form. Customer or travel agent can select a date of travel. They will also input how many places are needed. The system will then check that the selected dates are available. At this point there are two possible responses. 1) Places are not available: System will bring user back to 1st page and ask them to choose another date. 2) Places are available. System will look at the date of the holiday and check to see if that day is a Mon, Tue, Wed, etc. Depending on the result a list of possible flights and costs will be displayed (pulled from the data base) User will choose their flight. At this point the system will move the user to a payment area. Where all details are filled out and will be sent for payment and also e-mail sent to Admin with booking details. The booking system will also now be updated so that no one else can book those dates (up to 24 persons rotating). To deal with the travel agent commission a reference input field for the travel agent to fill in on the e-mail/payment form. With a description similar to; If you have a reference code for this holiday, please type it here. (Help) The help will be linked to a pop up with more advice ect. The system will take the given date and check the number of places is available and if so will move to the next day and do it all again. When bookings are written to the table there will be 2 entries for each person each day. Entry one will be a reference number into one of the spare places to identify the user who is in that slot. The second entry will be amendment to the number of places available. This will be based on the itinerary and keep up to date available places and avoid booking where no places are left. Checks will be in place for bogus bookings (payment). This is based on the present itinerary and tour booking operation and may need further amendments to fit in with the live working model. - Ideally what I would like is a DB structure that would work or an idea of the table holding the Time/Date Thank you Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
calender table - time column?
I am currently in the process of building a calender/date MySQL table that will be searched for available dates for holiday tours? I want to use a time column as there will be a restriction on places for up to 3 days so this will have to be taken into account. Has anyone done something like this? Cheers Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copy table with column attributes
Hi all. What's the quickest way to copy a table so that all column attributes such as auto_increment and primary key along with all indexes get copied over? I like using: create table new_table select * from old_table But it doesn't copy any indexes or those special attributes I mentioned over to the new table. I know this can be done using mysqldump but I'm wondering if there's something quicker (less involved). Thanks. David - 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: Copy table with column attributes
At 11:10 -0700 10/15/02, David Yee wrote: Hi all. What's the quickest way to copy a table so that all column attributes such as auto_increment and primary key along with all indexes get copied over? I like using: create table new_table select * from old_table But it doesn't copy any indexes or those special attributes I mentioned over to the new table. I know this can be done using mysqldump but I'm wondering if there's something quicker (less involved). Thanks. mysqldump uses the SHOW CREATE TABLE statement. Hard to use this output in a pure SQL environment, but if you issue it from within a program, you can use the output as you like. David - 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: Copy table with column attributes
I have a table tbl_version_history which has a primary key and an index. Through this query I am able to create a copy tbl_version_history_copy with all the index. Just replace the stuff with yours and probably it will help. create table `tbl_version_history_copy` ( primary key ( FLD_REG_NUM ) ,key `NewIndex1` ( `FLD_REG_NUM`, `FLD_VERSION_CODE`, `FLD_ADD_DATETIME` ) ) select * from `tbl_version_history` Rgds Insane - Original Message - From: David Yee [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 15, 2002 11:40 PM Subject: Copy table with column attributes Hi all. What's the quickest way to copy a table so that all column attributes such as auto_increment and primary key along with all indexes get copied over? I like using: create table new_table select * from old_table But it doesn't copy any indexes or those special attributes I mentioned over to the new table. I know this can be done using mysqldump but I'm wondering if there's something quicker (less involved). Thanks. David - 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 - 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
create tmp table of column list of a table for future use
mysql create table column_list (desc MYTABLE_NAME); ERROR 1064: You have an error in your SQL syntax near 'desc MYTABLE_NAME)' at line 1 Anyone know how to make something like it? - 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: create tmp table of column list of a table for future use
I believe it has to be like this: create table column_list (MYTABLE_NAME DESC); Thanks! -Alex Big Al Behrens E-mail: [EMAIL PROTECTED] Urgent E-mail: [EMAIL PROTECTED] (Please be brief!) Phone: 651-482-8779 Cell: 651-329-4187 Fax: 651-482-1391 ICQ: 3969599 Owner of the 3D-Unlimited Network: http://www.3d-unlimited.com Send News: [EMAIL PROTECTED] - Original Message - From: hong chen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 05, 2002 4:13 PM Subject: create tmp table of column list of a table for future use mysql create table column_list (desc MYTABLE_NAME); ERROR 1064: You have an error in your SQL syntax near 'desc MYTABLE_NAME)' at line 1 Anyone know how to make something like it? - 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 - 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: create tmp table of column list of a table for future use
-Original Message- From: hong chen Sent: Friday, April 05, 2002 5:32 PM To: 'Alex Behrens' Subject: RE: create tmp table of column list of a table for future use No luck. Oracle and Sql Server have system tables to check. mysql desc ATTRIBUTES; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | NAME | varchar(255) | YES | | NULL| | | VALUE | varchar(255) | YES | | NULL| | +---+--+--+-+-+---+ 2 rows in set (0.02 sec) mysql create table column_list ( ATTRIBUTES desc); ERROR 1064: You have an error in your SQL syntax near 'desc)' at line 1 mysql create table column_list (desc ATTRIBUTES); ERROR 1064: You have an error in your SQL syntax near 'desc ATTRIBUTES)' at line 1 mysql select Field from (desc ATTRIBUTES); ERROR 1064: You have an error in your SQL syntax near 'desc ATTRIBUTES)' at line 1 mysql -Original Message- From: Alex Behrens [mailto:[EMAIL PROTECTED]] Sent: Friday, April 05, 2002 5:24 PM To: hong chen; [EMAIL PROTECTED] Subject: Re: create tmp table of column list of a table for future use I believe it has to be like this: create table column_list (MYTABLE_NAME DESC); Thanks! -Alex Big Al Behrens E-mail: [EMAIL PROTECTED] Urgent E-mail: [EMAIL PROTECTED] (Please be brief!) Phone: 651-482-8779 Cell: 651-329-4187 Fax: 651-482-1391 ICQ: 3969599 Owner of the 3D-Unlimited Network: http://www.3d-unlimited.com Send News: [EMAIL PROTECTED] - Original Message - To: [EMAIL PROTECTED] Sent: Friday, April 05, 2002 4:13 PM Subject: create tmp table of column list of a table for future use mysql create table column_list (desc MYTABLE_NAME); ERROR 1064: You have an error in your SQL syntax near 'desc MYTABLE_NAME)' at line 1 Anyone know how to make something like it? - 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 - 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: create tmp table of column list of a table for future use
Are you trying to create a new table with an existing table's structure only ? or with the structure and data? If you just wanna duplicate a table with the data inside it you can use CREATE TABLE .. SELECT ... syntax. More info is at: http://www.mysql.com/doc/C/R/CREATE_TABLE.html IF you just wanna copy the structure, do a SHOW CREATE TABLE on the existing table and copy and paste its CREATE TABLE syntax to create the new one with a different name. See more info at: http://www.mysql.com/doc/S/H/SHOW_CREATE_TABLE.html Gurhan -Original Message- From: hong chen [mailto:[EMAIL PROTECTED]] Sent: Friday, April 05, 2002 5:13 PM To: [EMAIL PROTECTED] Subject: create tmp table of column list of a table for future use mysql create table column_list (desc MYTABLE_NAME); ERROR 1064: You have an error in your SQL syntax near 'desc MYTABLE_NAME)' at line 1 Anyone know how to make something like it? - 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 - 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: create tmp table of column list of a table for future use
-Original Message- From: hong chen Sent: Friday, April 05, 2002 5:50 PM To: 'Gurhan Ozen' Subject: RE: create tmp table of column list of a table for future use I am trying to check if a certain column exists before I try to select it from the table. in Oracle or Sql Server I would: select count(*) from user_tab_columns (or sys_columns) where name='C_NAME'; mysql desc ATTRIBUTES; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | NAME | varchar(255) | YES | | NULL| | | VALUE | varchar(255) | YES | | NULL| | +---+--+--+-+-+---+ 2 rows in set (0.02 sec) mysql select count(*) from (desc ATTRIBUTES) where Field ='ID'; ERROR 1064: You have an error in your SQL syntax near 'desc ATTRIBUTES)' at line 1 thanx. -Original Message- From: Gurhan Ozen [mailto:[EMAIL PROTECTED]] Sent: Friday, April 05, 2002 5:44 PM To: hong chen; [EMAIL PROTECTED] Subject: RE: create tmp table of column list of a table for future use Are you trying to create a new table with an existing table's structure only ? or with the structure and data? If you just wanna duplicate a table with the data inside it you can use CREATE TABLE .. SELECT ... syntax. More info is at: http://www.mysql.com/doc/C/R/CREATE_TABLE.html IF you just wanna copy the structure, do a SHOW CREATE TABLE on the existing table and copy and paste its CREATE TABLE syntax to create the new one with a different name. See more info at: http://www.mysql.com/doc/S/H/SHOW_CREATE_TABLE.html Gurhan -Original Message- Sent: Friday, April 05, 2002 5:13 PM To: [EMAIL PROTECTED] Subject: create tmp table of column list of a table for future use mysql create table column_list (desc MYTABLE_NAME); ERROR 1064: You have an error in your SQL syntax near 'desc MYTABLE_NAME)' at line 1 Anyone know how to make something like it? - 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 - 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: create tmp table of column list of a table for future use
Problem is I'm accessing mysql through scripting against a table that may or may not have ID field, select count(*) from (desc ATTRIBUTES) where Field ='ID'; if count(*) 0 then select ID from ATTRIBUTES; else do something else; end if; -Original Message- From: Gurhan Ozen [mailto:[EMAIL PROTECTED]] Sent: Friday, April 05, 2002 5:57 PM To: hong chen Subject: RE: create tmp table of column list of a table for future use SHOW CREATE TABLE ... syntax will show you whether or not the field name is there.. Gurhan - 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
Table Qualified Column Names
Does mysql jdbc driver support table quailified column names? For example, this seems to work: select id from people But this doesn't: select people.id from people. Thanks. Dave Ford Smart Soft - The Developer Training Company http://www.smart-soft.com - 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
how to create a table with column name 'key'?
I hope you can answer the following question. I am wondering how to create a table with a column named key. I have RTFMed and that didn't help. I'm using 3.23.47-max-debug. The following fails (after selecting a database): mysql create table test (key varchar(255), value varchar(255)); With the following error: ERROR 1064: You have an error in your SQL syntax near 'varchar(255), value varch ar(255))' at line 1 Any suggestions? Many thanks, Dominique __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ - 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 to create a table with column name 'key'?
Dominique, On Sun, Jan 20, 2002 at 12:18:45PM -0800, Dominique Plante wrote: The following fails (after selecting a database): mysql create table test (key varchar(255), value varchar(255)); You should be able to do this by using back-quotes around the column name: create table test (`key` varchar(255), value varchar(255)); You might want to reconsider using reserved names like this because you will need to use the back-quotes in all your queries. Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - 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 to create a table with column name 'key'?
Perhaps you read the wrong part(s) of the manual: http://www.mysql.com/doc/R/e/Reserved_words.html On Sun, 20 Jan 2002 12:18:45 -0800 (PST), Dominique Plante wrote: I hope you can answer the following question. I am wondering how to create a table with a column named key. I have RTFMed and that didn't help. I'm using 3.23.47-max-debug. The following fails (after selecting a database): mysql create table test (key varchar(255), value varchar(255)); With the following error: ERROR 1064: You have an error in your SQL syntax near 'varchar(255), value varch ar(255))' at line 1 Any suggestions? Many thanks, Dominique __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ - 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 - 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: SupportWizard: bug in mysql: ALTER TABLE CHANGE COLUMN A can damage autoincrement column B!
Max writes:
Description:
I am using russian values(cp1251 charset) for ENUM items.
Here is an example how to damage ID field by query to change
F_Category Enum.
How-To-Repeat:
# Create tableA with ID field.
CREATE TABLE tableA (
ID int(11) NOT NULL auto_increment,
F_ReportID bigint(20) NOT NULL default '0',
F_Response_to varchar(99) NOT NULL default '',
F_More_reports mediumtext NOT NULL,
F_Linked_Reports mediumtext NOT NULL,
F_Responses mediumtext NOT NULL,
F_Summary varchar(99) NOT NULL default '',
F_Description mediumtext NOT NULL,
F_Solution mediumtext NOT NULL,
F_Category enum('Bug','Question','Documentation','Enhancement Request','Web
Error','User Error','Unknown') NOT NULL default 'Question',
F_Attached_file mediumtext NOT NULL,
F_Severity enum('Critical','High','Medium','Low') NOT NULL default 'Medium',
F_User varchar(99) NOT NULL default '',
F_EndUser varchar(99) NOT NULL default 'nobody',
F_FullName varchar(99) NOT NULL default '',
F_E_mail varchar(99) NOT NULL default '',
F_Telephone varchar(99) NOT NULL default '',
F_Group varchar(99) NOT NULL default '',
F_Internal_analysis mediumtext NOT NULL,
F_State enum('Open','Assigned','In-progress','In-testing','Closed') NOT NULL
default 'Open',
F_Std_Solution enum('Yes','No') NOT NULL default 'No',
F_Accepted enum('Yes','No') NOT NULL default 'No',
F_Date datetime NOT NULL default '1990-01-01 00:00:00',
F_Thread_owner varchar(99) NOT NULL default '',
F_Creator_E_mail varchar(99) NOT NULL default '',
F_Modified_by varchar(99) NOT NULL default '',
F_Modification_date datetime NOT NULL default '1990-01-01 00:00:00',
F_Route mediumtext NOT NULL,
F_History mediumtext NOT NULL,
F_E_mail_History mediumtext NOT NULL,
F_CorrectSolutionCount bigint(20) NOT NULL default '0',
F_CorrectSolutionCount bigint(20) NOT NULL default '0',
F_AdjustedSolutionCount bigint(20) NOT NULL default '0',
F_Assign_date datetime NOT NULL default '1990-01-01 00:00:00',
UNIQUE KEY ID (ID),
KEY F_ReportID (F_ReportID),
KEY F_Category (F_Category),
KEY F_Severity (F_Severity),
KEY F_User (F_User),
KEY F_State (F_State),
KEY F_Std_Solution (F_Std_Solution),
KEY F_Accepted (F_Accepted),
KEY F_Date (F_Date),
KEY F_Thread_owner (F_Thread_owner),
KEY F_Modified_by (F_Modified_by),
KEY F_Modification_date (F_Modification_date),
KEY F_Assign_date (F_Assign_date),
KEY F_Assigned_to (F_Assigned_to,F_State)
) TYPE=MyISAM;
Hi!
We would very much like to solve a problem, but your test case is
faulty.
First, you have two columns of the same name :
F_CorrectSolutionCount bigint(20) NOT NULL default '0',
F_CorrectSolutionCount bigint(20) NOT NULL default '0',
second , you have a key using non-existent column :
KEY F_Assigned_to (F_Assigned_to,F_State)
Please provide us with a working example.
--
Regards,
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
___/ www.mysql.com
-
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
SupportWizard: bug in mysql: ALTER TABLE CHANGE COLUMN A can damage autoincrement column B!
Description:
I am using russian values(cp1251 charset) for ENUM items.
Here is an example how to damage ID field by query to change
F_Category Enum.
How-To-Repeat:
# Create tableA with ID field.
CREATE TABLE tableA (
ID int(11) NOT NULL auto_increment,
F_ReportID bigint(20) NOT NULL default '0',
F_Response_to varchar(99) NOT NULL default '',
F_More_reports mediumtext NOT NULL,
F_Linked_Reports mediumtext NOT NULL,
F_Responses mediumtext NOT NULL,
F_Summary varchar(99) NOT NULL default '',
F_Description mediumtext NOT NULL,
F_Solution mediumtext NOT NULL,
F_Category enum('Bug','Question','Documentation','Enhancement Request','Web
Error','User Error','Unknown') NOT NULL default 'Question',
F_Attached_file mediumtext NOT NULL,
F_Severity enum('Critical','High','Medium','Low') NOT NULL default 'Medium',
F_User varchar(99) NOT NULL default '',
F_EndUser varchar(99) NOT NULL default 'nobody',
F_FullName varchar(99) NOT NULL default '',
F_E_mail varchar(99) NOT NULL default '',
F_Telephone varchar(99) NOT NULL default '',
F_Group varchar(99) NOT NULL default '',
F_Internal_analysis mediumtext NOT NULL,
F_State enum('Open','Assigned','In-progress','In-testing','Closed') NOT NULL default
'Open',
F_Std_Solution enum('Yes','No') NOT NULL default 'No',
F_Accepted enum('Yes','No') NOT NULL default 'No',
F_Date datetime NOT NULL default '1990-01-01 00:00:00',
F_Thread_owner varchar(99) NOT NULL default '',
F_Creator_E_mail varchar(99) NOT NULL default '',
F_Modified_by varchar(99) NOT NULL default '',
F_Modification_date datetime NOT NULL default '1990-01-01 00:00:00',
F_Route mediumtext NOT NULL,
F_History mediumtext NOT NULL,
F_E_mail_History mediumtext NOT NULL,
F_CorrectSolutionCount bigint(20) NOT NULL default '0',
F_CorrectSolutionCount bigint(20) NOT NULL default '0',
F_AdjustedSolutionCount bigint(20) NOT NULL default '0',
F_Assign_date datetime NOT NULL default '1990-01-01 00:00:00',
UNIQUE KEY ID (ID),
KEY F_ReportID (F_ReportID),
KEY F_Category (F_Category),
KEY F_Severity (F_Severity),
KEY F_User (F_User),
KEY F_State (F_State),
KEY F_Std_Solution (F_Std_Solution),
KEY F_Accepted (F_Accepted),
KEY F_Date (F_Date),
KEY F_Thread_owner (F_Thread_owner),
KEY F_Modified_by (F_Modified_by),
KEY F_Modification_date (F_Modification_date),
KEY F_Assign_date (F_Assign_date),
KEY F_Assigned_to (F_Assigned_to,F_State)
) TYPE=MyISAM;
# It is to see that ID exists in this table
select ID from tableA;
# Try to alter F_Category field
ALTER TABLE tableA CHANGE COLUMN F_Category F_Category ENUM('Bug',
'tmp_sync_1005859699', 'Âîïðîñ ïîëüçîâàòåëÿ', 'Îáùèé âîïðîñ', 'Ðàñøèðåííûé çàïðîñ',
'Îøèáêà íà WEB ñåðâåðå', 'Îøèáêà ïîëüçîâàòåëÿ') NOT NULL DEFAULT 'Bug';
# It is to see what happens with ID
select ID from tableA;
Fix:
none
Submitter-Id: submitter ID
Originator:Max
Organization:
Integral Solutions Corp.
MySQL support: none
Synopsis: autoincrement ID field renamed during changing F_Category ENUM!
Severity: critical
Priority: high
Category: mysql
Class: sw-bug
Release: mysql-3.23.44 (Source distribution)
Environment:
machine, os, target, libraries (multiple lines)
System: Linux linux 2.4.14 #4 SMP 9 03:46:13 MSK 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs
gcc version 2.95.3 20010315 (SuSE)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
-rwxr-xr-x1 root root 1384168 20 07:52 /lib/libc.so.6
-rw-r--r--1 root root 25215580 20 07:28 /usr/lib/libc.a
-rw-r--r--1 root root 178 20 07:28 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/home/isc/mysql
--localstatedir=/usr/home/isc/mysql/data --with-client-ldflags=-all-static
--with-mysqld-ldflags=-all-static --with-berkeley-db --with-innodb --with-libwrap
--with-extra-charset=all --with-charset=cp1251
-
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
mysqldnt 3.23.36 (WIN32) crashed after ALTER TABLE DROP COLUMN
From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject:table modification [ALTER TABLE DROP COLUMN name]; on large table (1.7gb) crashed mysqldnt Description:- attempt to extract a longblob column from a large table with ca. 200.000 records into a separate table (successful) - dropping the blob_column from the large table (success) consecutive access to the database by an odbc-client caused the server to crash. stack_trace (dump.txt) attached How-To-Repeat: bug has been reproduced a 2nd time by dropping another column from the same table. Actually, the original table is not available anymore, because the crash corrupted the indexfile. Further attempts to reproduce the failure on backup-tables in progress. Fix:none so far Synopsis: mysqldnt crashed after ALTER TABLE DROP COLUMN Submitter-Id: ASC Originator: Armin Schöffmann Organization: Aegaeon technologies GmbH MySQL support: licence Severity: critical Priority: Category: mysqld Class: sw-bug Release:mysql-3.23.36 Exectutable:mysqld Environment:Dell Optiplex, Intel PIII, 256mb, nt5sp1(us)ws System: Win2000 Compiler: VC++ 6.0 Architecture: i686 Aegaeon technolgies GmbH Stadtamhof 15 93059 Regensburg Germany fon:+49.941.8107344 fax:+49.941.8107356 www.aegaeon.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
