Re: /var/log/mysql is taking up a lot of diskspace
> this is what logrotate run before and after > > [ -e /var/lock/subsys/mysqld ] && /bin/kill -HUP `cat > /var/run/mysqld/mysqld.pid 2> /dev/null ` || /bin/true > > Assuming paths are the same I would run the above commands. Short of > that I would take down mysql, delete log, then bring mysql back up. Thanks guys. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: /var/log/mysql is taking up a lot of diskspace
ok, cool. I was not sure if I could use logrotate with the mysql logs. Is it safe to delete what I have there at the moment? I need to free up some disk space. On Sat, Jan 31, 2009 at 5:07 PM, Bazooka Joe wrote: > On Fri, Jan 30, 2009 at 7:54 PM, will wrote: >> Hi, >> >> I am running mysql 5.0.32 on debian etch on a VPS. The /var/log/mysql >> directory has grown to 5.8G, over 25% of the diskspace on my server. >> >> Can the contents be deleted saftely? >> >> Can mysql be configured to keep the log directory within, for example, >> 500mb (or whatever is considered reasonable)? >> >> Many Thanks, >> >> Will >> > > most people use logrotate > > <http://linuxcommand.org/man_pages/logrotate8.html> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
/var/log/mysql is taking up a lot of diskspace
Hi, I am running mysql 5.0.32 on debian etch on a VPS. The /var/log/mysql directory has grown to 5.8G, over 25% of the diskspace on my server. Can the contents be deleted saftely? Can mysql be configured to keep the log directory within, for example, 500mb (or whatever is considered reasonable)? Many Thanks, Will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
query kills mysqld
MySQL Ver 8.41 Distrib 5.0.27 Intel Xeon 3.60 GHz 2.0 G of RAM Windows Server 2003 Service Pack 1 The following query will sometimes work and sometimes fail. When it fails it kills the mysqld service and requires it to be restarted. Sometimes if I add additional id's to the IN clause it will fail more often. select distinct(ct.truck_name), (SELECT OID FROM release2_85.cust_positions cp where cp.asset_id = ct.OID order by date_time desc LIMIT 1) as MAXPOSID, (SELECT date_add(date_time, interval -360 minute) FROM release2_85.cust_positions WHERE OID = MAXPOSID) as date_time, (SELECT location1distance / 1000 FROM cust_positions WHERE OID = MAXPOSID) as location1distance, (SELECT location1 FROM release2_85.cust_positions WHERE OID = MAXPOSID) as location1 FROM release2_85.cust_trucks ct where (SELECT OID FROM release2_85.cust_positions cp where cp.asset_id = ct.OID order by date_time desc LIMIT 1) is not null and ct.OID in (-1,1,2,104181,104182,104183) order by truck_name _ William Nordin System Analyst / Programmer QA Technologies [EMAIL PROTECTED] (306) 931-6943 ext.265 NOTICE: This confidential e-mail message is only for the intended recipient(s). If you are not the intended recipient, be advised that disclosing, copying, distributing, or any other use of this message, is strictly prohibited. In such case, please destroy this message and notify the sender.
Re: Check out this Free software I found to document your IT infrastruct
Rob Munsch, I am a member of the Nabble project. This is regarding a bad post by user "itguy321". Just want to explain a few things. Nabble is a free mailing list archive/gateway that works like Gmane. Users can browse, search, and post via Nabble's web interface and the post will be forwarded to the mailing list as an email. A user will need to register with Nabble first, then he will need to subscribe to the mailing list to become a subscriber, only then can he post to that mailing list. You seem to be holding Nabble responsible for this bad post. Is it fair? Is it fair to blacklist Gmail just because a bad user sent a spam? A mailing list usually has a moderation mechanism to deal with bad posts. If you are a mailing list owner, you can simply ban that user. Nabble hates spam as much as you do. We are currently monitoring this user and we will take necessary action just like a mailing list owner will do. Feel free to send me a private email if you have more concerns. Regards, Will L Nabble.com Rob Munsch wrote: > > itguy321 wrote: > >>(garbage snipped) >> >>http://www.ecora.com/ecora/products/documentor.asp >> >> > Full original spam source at end, for the benefit of our CCs. > > Reports sent, omitting the MySQL hosts obviously. Ignoring the bogus > yahoo address, the actual sender and the spamvertised host have been > sent spam reports, as a precursor to blacklisting. > > The sender, meanwhile, is using a "private" listing service to hide > behind; this service is also now contacted about your spamming activity, > sirrah. If they are a respectable service, you will get the boot; if > they are not, i expect they shall shortly be blacklisted as well. > > Have a *great* day. > > Tracking link: > > http://www.nabble.com/check-out-this-free-software-i-found-to-document-your-it-infrastructure-tf2041675.html#a5620241 > No recent reports, no history available > Resolves to 72.21.53.35 > Routing details for 72.21.53.35 > > <http://www.spamcop.net/sc?action=showroute;ip=72.21.53.35;typecodes=17> > [refresh/show] > <http://www.spamcop.net/sc?action=rcache;ip=72.21.53.35> Cached > whois for 72.21.53.35 : [EMAIL PROTECTED] > > > *Domain Name:* ECORA.COM > > *Administrative Contact :* > Bakman, Alex > [EMAIL PROTECTED] > 2 International Drive > Suite 150 > Portsmouth, NH 03801-3149 > US > Phone: (603) 436-1616 > Fax: (603) 436-1604 > > *Technical Contact :* > IT, Ecora > [EMAIL PROTECTED] > 2 International Drive > Portsmouth, NH 03801 > US > Phone: 603-334-3100 > > > - > > Domain Name.. nabble.com > Creation Date 2004-12-28 > Registration Date 2004-12-28 > Expiry Date.. 2006-12-28 > Organisation Name Weizhen Lin > Organisation Address. P O Box 99800 > Organisation Address. > Organisation Address. EmeryVille > Organisation Address. 94662 > Organisation Address. CA > Organisation Address. US > > Admin Name... PrivateRegContact Admin > Admin Address P O Box 99800 > Admin Address > Admin Address EmeryVille > Admin Address 94662 > Admin Address CA > Admin Address US > Admin Email.. [EMAIL PROTECTED] > Admin Phone.. +1.5105952002 > Admin Fax > > Tech Name PrivateRegContact TECH > Tech Address. P O Box 99800 > Tech Address. > Tech Address. EmeryVille > Tech Address. 94662 > Tech Address. CA > Tech Address. US > Tech Email... [EMAIL PROTECTED] > Tech Phone... +1.5105952002 > Tech Fax. > Name Server.. dns1.nabble.com > Name Server.. dns2.nabble.com > > original post source: > 8< - > > Return-path: > <[EMAIL PROTECTED]> > Envelope-to: [EMAIL PROTECTED] > Received: from lists2.mysql.com ([213.136.52.31]:59977 > helo=lists.mysql.com) > by dirtybill.solutionsforprogress.com with smtp (Exim 4.50) > id 1G8MOg-0006lJ-8Z > for [EMAIL PROTECTED]; Wed, 02 Aug 2006 15:28:54 -0400 > Received: (qmail 19122 invoked by uid 510); 2 Aug 2006 19:25:11 - > Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm > List-ID: > Precedence: bulk > List-Help: <mailto:[EMAIL PROTECTED]> > List-Unsubscribe: > <
Re: SELECT all except ... ?
On 09/12/05, Frank Rust <[EMAIL PROTECTED]> wrote: > Is there a possibility to select all columns from a table except one or > two columns? For example I have a table with 30 columns and want all > columns but one column *not*. Do I have to write a very long select > statement with 29 column names that i want to get? If you're using MySQL 5.0 an up you could achieve similar functionality using views. http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-views.html Will. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: String Manipulation
On 09 Dec 2005 10:45:04 +0800, Jeffrey G. Ubalde
> I have been thinking about this for hours, and I cant quite get the
> correct keyword for me to search it in the manual.
>
> for example i have a field that contains string1:string2:string3
> where colon ':' is the delimiter.
>
> How do i create a select statement that only returns string2?
Something like this may be what you're after?
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
mysql> select
substring_index(substring_index('string1:string2:string3',':',2),':',-1);
+--+
| substring_index(substring_index('string1:string2:string3',':',2),':',-1) |
+--+
| string2 |
+------+
1 row in set (0.00 sec)
mysql>
Will.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 4.1.x with php-4.3.x
I recomend the PHP Home Edition Project at http://phphome.sourceforge.net/. It installs PHP, Apache and MySql, all configed to work together on a Windows machine. Version PHP Home 2.3.2 installs PHP v.4.3.3, Apache 2.0 and MySql 4.x (Note the latest version is 2.3.4 and includes PHP 5). I have been using 2.3.2 for several months on several machines and have had no problems at all. HTH, -- Will > -Original Message- > From: Daniel Kasak [mailto:[EMAIL PROTECTED] > Sent: Sunday, June 26, 2005 6:25 PM > To: mysql@lists.mysql.com > Subject: 4.1.x with php-4.3.x > > > I realise that this question may better be asked in a php list, but I > already did that and got *no* answers. > Does anyone know if there are some mysql dlls available for php-4.3.x ( > I'm running 4.3.11) that are compiled against 4.1.x so I don't have to > use the ugly hacks to get the old client to talk to the new server? > I'm not really up to compiling things on Windows. > > -- > Daniel Kasak > IT Developer > NUS Consulting Group > Level 5, 77 Pacific Highway > North Sydney, NSW, Australia 2060 > T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 > email: [EMAIL PROTECTED] > website: http://www.nusconsulting.com.au > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to Version Control a database
I have a project that involves several developers working on their own machines. Each has a local copy of the database on their own machine. Since we have some developers who develop while not connected to the network, we cannot use a common database. How can I version control the database so that changes are not lost or stepped on. Right now, we use mysqldump to dump the database and version the dump file, but this still has problems. Is there a better way? -- Will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slackware10 & mysql
Hey - It's been awhile since I set up mysql on a box (it's one of those things where I teach myself, do it once, then forget it, heh) - but I seem to remember you need to do some things to initialize the databases and set initial privileges before it will work - su to the "mysql" user and run "mysql_install_db" - this command should create the initial tables and give you instructions on where to go from there (the part I don't remember). Will Beers Mohsen Pahlevanzadeh wrote: Dears,I have slackware10.I install it completely. But when i use /etc/rc.d/rc.mysqld start ,I recieve following mesage : mysql ended Please guide me that i can configure mysql in slack. Yours,Mohsen smime.p7s Description: S/MIME Cryptographic Signature
Cygwin Mysql No Response
Hello All, Thank you for reading this email. I have the following Configuration Windows 2000 Server MySQL Win32 Binaries Installed Current Release of Cygwin installed I am trying to access mysql from the bash shell rather than the command line. It was working fine until now, but for some reason I can't issue commands to mysql from the bash shell. I have tried to uninstall and reinstall Cygwin. I have tried to uninstall and reinstall MySQL. But the problem is still there. I can connect from the C: prompt and I can issue a select statement from the BASH shell if I put the -e flag after mysql., but I can not just type mysql and get the standard, Welcome to MySQL blah blah.. example. $ mysql nothing happens. But if I do this $ mysql -e "select * from user" mysql I get rows returned. If anyone can shed some light on this problem, I would appreciate it. I am a newbie at GNU Shells. Thanks +-+-+ | Variable_name | Value | +-+-+ | back_log| 50 | | basedir | C:\mysql\ | | binlog_cache_size | 32768 | | character_set | latin1 | | character_sets | latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | C:\mysql\data\ | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | flush | OFF | | flush_time | 1800 | | have_bdb
Query join issues
Hello, I am trying to figure out a problem I am having with a query involving multiple joins but am not having much luck. I currently have this query working: db1 LEFT JOIN db2 ON db1.files_groupId = db2.group_id LEFT JOIN db3 ON db1.files_userId = db3.user_id Basically the above is taking the group id and user id from db1 and getting their names from the appropriate db. What I would like to be able to do though is join db3 (user) on to db1 again but using a different ON value (a different user id in the same record). Is this possible? I have tried aliasing the db3: db1 LEFT JOIN db2 ON db1.files_groupId = db2.group_id LEFT JOIN db3 AS user1 ON db1.files_userId = user1.user_id LEFT JOIN db3 AS user2 ON db1.files_otherUserId = user2.user_id WHERE ... The above query does not cause a MySQL error, however there are no values passed for the requested labels of db3 e.g user2.user_name. I have tried printing out mysql_fetch_assoc($result) and from what I can see the user db doesn't get joined on again but wipes all results of the other user join. Thanks for any help offered. Will
Weird problem with displaying and retrieving varchar
Hello, I have a table with 3 columns: mysql> desc srv_ref_cities; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | state_id | char(2) | YES | | NULL| | | COUNTYCODE | char(3) | | | | | | CITY | varchar(40) | | | | | ++-+--+-+-+---+ And the problem is when i display a query (two of the columns don't show): mysql> select * from srv_ref_cities where state_id='08'; +--+++ | state_id | COUNTYCODE | CITY | +--+++ || Acres Green || Aguilar if I have just state_id, countycode it displays just fine. I am thinking that maybe the city is unicode but i can't figure this one out. Has anyone else run into this problem or does anyone have any ideas to help me debug this? I have tried versions 4.0.15 and 5 but the same thing happens in each version. thanks, will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
retrieve ignored records from LOAD DATA INFILE IGNORE
Is there anyway to get mySQL to generate a warning or other info when
it ignores a row via LOAD DATA INFILE IGNORE? I'm happy having the
duplicates ignored but ideally would like to log which records were
dupes in a place I can find them again.
--
thanks,
Will
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Time series
>> First: select * from table1 order by field1 asc limit 1 >> Last: select * from table1 order by field1 desc limit 1 > That only returns one number.. what we are really looking for is something And worse: as far as I can tell 3.22.x even if field1 is indexed, ONE of those queries is going to be very slow. The query planner doesn't seem to be smart enough to read the index in reverse for ORDER BY DESC clauses. -- thanks, Will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.1 alpha and mysql_install_db grant tables issue
The mysql_install_db script shipped with 4.1.1-alpha seems to leave
the mysql/* (user,host,etc.) tables owned root.root on my Debian
system; this makes mysqld fail to start after the grant tables are
installed.
It looks like this is because mysql_install_db calls mysqld with
--bootstrap and *without* --user=mysql. mysql_install_db already
takes a --user argument but doesn't use it; perhaps it should be added
to line 208?
--
thanks,
Will
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 100,000,000 row limit?
> I don't believe this. I'm going to write a script to disprove this theory
> right now..
We have a lot more than 100,000,000 more than that in a single MyISAM
table at work:
mysql> select count(*) from probe_result;
+---+
| count(*) |
+---+
| 302045414 |
+---+
1 row in set (0.00 sec)
--
thanks,
Will
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query syntax.
> Select User_Account from Users as a, Devices as b
> WHERE
> a.User_Account = (Select DISTINCT(b.Device_Account) from b.Devices
>WHERE b.Device_Name LIKE 'HP%' )
> I'm running 3.23.49 which I know is not the most current..it was installed
3.x does not support subselects ("select x from (select y from ...)").
You'll need to upgrade to 4.1.
--
thanks,
Will
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlhotcopy as a replication scheme
I've got an application that uses a fairly large (~50MM rows, ~1GB of disk) table of read-only data. The table changes maybe once a month, but when it changes, (almost) EVERY row in the table changes. The app needs to be replicated into several datacenters worldwide using relatively slow backend links. For this reason and others (I need to be able to control when each datacenter picks up updates, etc.) native MySQL replication isn't attractive. I'm considering building a scheme where I insert the data into a table once and ship around a gzipped mysqldump and load it into each datacenter -- this is easy, uses less bandwidth, is easy to control via cron and fits well into the rest of our infrastructure. Then I found mysqlhotcopy. Neato! I've tested, and this seems to work: 1) use mysqlhotcopy to copy the table on the "replication master" 2) gzip the table/index/data files and ship them someplace remote 3) (on the slave) unzip them 4) LOCK TABLES foo WRITE 5) FLUSH TABLE foo 6) copy the unzipped data files over the running mysql data files for the single table I'm intersted in. There's clearly a problem here if the machine crashes during this step, but it can be worked out to just 3 calls to rename(2), which is atomic on a POSIX fs, so that's less an issue than it could be. 7) FLUSH TABLE foo 8) profit! It looks like table foo now contains the new data. It takes a LOT less time than reinserting all the data into the table. Other than "you should really use mysql native replication", does anyone have any comments on whether this is likely to be reliable, or why it's a bad idea? I'm using 3.23.49 (Debian stable); Is FLUSH TABLE likely to change in future versions in a way that will break this? -- thanks, Will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why change in CONCAT_WS
I noticed in the changelog for the upcoming 4.1.1 alpha release that CONCAT_WS is being changed to NOT skip empty strings. I am sure that this must have been done in response to feedback but I wonder if there are others who like me have relied on the skipping of empty strings. Among other things, I use the function to assemble full names (of people) which I store in parsed fields in my database (i.e. title, firstname, middlename, lastname, suffix). In many cases, my middlename and suffix fields are blank and the current version of CONCAT_WS allows me to reassemble the parts without having the possibility of having double spaces, leading spaces, or trailing spaces. Are there others like me who would like to preserve the empty string skipping version of CONCAT_WS. Perhaps the new version of the function could be added under a new name like CONCAT_WS_NOSKIP or perhaps the option to skip blanks could be provided in an argument. Just curious what people think. obligatory keyword for list: mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlimport 'can't get stat of [file]' error
I've unable to load data from files into existing tables. When I give the command: mysqlimport [database] [filename] I keep getting the following error. mysqlimport: Error: Can't get stat of '[filename]' (Errcode: 13), when using table: [table] I get a similar error when trying to use LOAD DATA INFILE from within mysql. The text files all have the extension .table I'm sure that I'm just missing something simple, but I've spent a good amount of time trying to figure this out and haven't been able to. Any advice on what's happening or how to get around this would be really appreciated. Thanks, Will Tyburczy ___ Join Excite! - http://www.excite.com The most personalized portal on the Web! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
follow-up
I'm having this error and am not sure what's causing it. I'm using PHP/MySQL and Apache on Windows XP. I've looked at my server, user, password settings and they seem to be accurate, but the error seems to indicate this is the problem. Any help would be appreciated. ___ PHP Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in c:\phpdev\www\development\phpflash\init.php on line 44 Here's the init.php info // THIS IS THE LOCAL CONFIGURATION $server = "localhost"; $user = "wsj3"; $pass = "wsjJHB"; $dbname = "central_user_db"; /* Active email, Insert, Delete, Update */ $email = true; $onlySelect = false; $onlyFromUrl = false; /* Don't touch */ $conectar_id= mysql_connect($server, $user, $pass); <-- LINE 44 mysql_select_db($dbname, $conectar_id);
trying to figure out error in PHP/MySQL
Hello, I'm new to the group and hoping someone can help me figure out a problem which must be fairly common to new PHP/MySQL users. I have PHP/MySQL/Apache installed on my Windows XP system. I'm using a supplied PHP script for making calls from Actionscript to MySQL. The calls work great on a remote machine, which must have proper user/password etc., but not on my local machine. Here's the error description: SELECT username,password,user_active FROM deckuser WHERE username='asd' LIMIT 0, 1 Warning: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) in c:\phpdev\www\development\phpflash\init.php If anyone can advise me about what I need to change on my system to get this to work I would really appreciate it. Thanks, Will Johnston wsj3.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing up all MySQL DBs
There's also # backup databases for dbname in `echo 'show databases;' | mysql -u$dbuser -p$dbpassword` do echo "Backing up database $dbname..." >> $destdir/backup.log mysqldump -u$dbuser -p$dbpassword $dbname | zip -q9 $destdir/db_$dbname.zip - >> $destdir/backup.log done WILL [EMAIL PROTECTED] - Original Message - > From: "Mike Hillyer" <[EMAIL PROTECTED]> > To: "René Mølsted" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Monday, September 08, 2003 9:02 PM > Subject: RE: Backing up all MySQL DBs > > > > Sure, use the --all-databases option instead of $DBNAME > > http://www.mysql.com/doc/en/mysqldump.html > > > > Regards, > > Mike Hillyer > > www.vbmysql.com > > > > > > > -Original Message- > > > From: René Mølsted [mailto:[EMAIL PROTECTED] > > > Sent: Monday, September 08, 2003 11:51 AM > > > To: [EMAIL PROTECTED] > > > Subject: Backing up all MySQL DBs > > > > > > > > > Hi everybody > > > I'm pretty new to MySQL (and to this list). My problem is I need to get > > > a dump of all databases in seperate files, I know how to do one > > > database to one file and all databases to one file. > > > So far I'm using this command: > > > mysqldump --user "$USERNAME" --password="$PASSWORD" "$DBNAME" | gzip> > > > "$DBNAME"db_"$DATE".sql.gz > > > > > > Is there a way selecting all databases? > > > > > > René Mølsted > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
getting started on a SQL project
Hello, I'm new to SQL and trying to start out with a relatively simple project. I'm hoping someone can give me a few tips on how to accomplish my goal. I want to have a table for registered users reg_user (username, userID, password, email, status) I also want to have a table associated with each user for storing their playing_cards (userID, cards) I want to have a link between the reg_user and the playing_cards tables and also have 'cards' be a dynamic array of playing cards which is updated and retrieved regularly. Can someone give me a simple outline of how I should get started with this? Thanks, Will Johnston wsj3.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Designing Categories
Greets Folks, I cant tell if this is a dumb question or not, so please bear with me if it is... But here goes... You know how when you visit a website and they have "Categories", like in an online store or maybe an auction sites... or maybe categories would be used in a bulletin board system for different types of discussions, or art galleries for different types of galleries... ? I am wondering how the backend logic structures looks to that tablewise... I can handle SQL for relational databasing, but I am trying to understand this more from a design point of view... Thanks, Will _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail - 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
--- Best ISP for MySQL & PHP ???
Gang, I'm looking for an ISP to host a couple of MySQL db's... Will be using PHP as the scripting language... Want to use PhpMyAdmin to administer the db. Are there certain ISP's who specialize in hosting such sites? Thanks for any info. Will - 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
Will PhpMyAdmin run on Linux-Apache-MySQL box ?
I installed PhpMyAdmin on a Windows box using EasyPhp... Will PhpMyAdmin run on a Linux/Apache/MySQL server? (I've got one on my desk up & running) If so which version should I download? (.php files) - Download phpMyAdmin-2.3.3pl1-php.tar.bz2 - Download phpMyAdmin-2.3.3pl1-php.tar.gz - Download phpMyAdmin-2.3.3pl1-php.zip (.php3 files) - Download phpMyAdmin-2.3.3pl1-php3.tar.bz2 - Download phpMyAdmin-2.3.3pl1-php3.tar.gz - Download phpMyAdmin-2.3.3pl1-php3.zip (Yes, I'm a Linux newbie.) Finally, is it a fairly easy install for a Linux newbie? thanks for any help. Will - 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
Will PhpMyAdmin do more than just create a MySQL db ???
Will PhpMyAdmin do more than simply create a MySQL db ? Please cc me directly on any post at [EMAIL PROTECTED] Thanks for any help. Will - 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 query results of a query?
How do you query the table that is the results of a query? Must you ask MySQL to... ...create a temporary table form the results of the first query ...then query that temporary table ...then delete the temp table when you are done? (and if so how do you ask MySQL to create a temp table from the results of a query?) Is there a better and faster way to do this with minimum burden on the web server with the db on it? Thanks for any help. Will - 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: Accessing last_insert_id problem.
Jeff Snoxell wrote: > At 09:46 19/12/02 -0500, you wrote: > >Jeff Snoxell wrote: > > > >>Nope. That doesn't do it either! > >> > >>I go: > >> > >>TRUNCATE TABLE my_table > > > >Are you using InnoDB tables? You'll have to do something akin to ALTER > >TABLE my_table AUTO_INCREMENT=1 ... at least according to Paul ... :) > > No, I'm using MyISAM I believe. > > Jeff If you want to set the ID back to zero, then I assume you are deleteing all of the records in the table. If so, why not simply drop the table and recreate it? Seems to work for me as the session below demonstrates. If you want to do something else, you better ask again so we can answer you real question. clip mysql> create table test ( id int auto_increment, d int, primary key (id) ); Query OK, 0 rows affected (0.00 sec) mysql> insert into test (d) values (2); Query OK, 1 row affected (0.00 sec) mysql> insert into test (d) values (2); Query OK, 1 row affected (0.00 sec) mysql> insert into test (d) values (2); Query OK, 1 row affected (0.00 sec) mysql> select * from test; ++--+ | id | d| ++--+ | 1 |2 | | 2 |2 | | 3 |2 | ++--+ 3 rows in set (0.00 sec) mysql> drop table test; Query OK, 0 rows affected (0.00 sec) mysql> create table test ( id int auto_increment, d int, primary key (id) ); Query OK, 0 rows affected (0.00 sec) mysql> insert into test (d) values (2); Query OK, 1 row affected (0.01 sec) mysql> insert into test (d) values (2); Query OK, 1 row affected (0.00 sec) mysql> insert into test (d) values (2); Query OK, 1 row affected (0.00 sec) mysql> select * from test; ++--+ | id | d| ++--+ | 1 | 2 | | 2 |2 | | 3 |2 | ++--+ 3 rows in set (0.00 sec) mysql> = end clip = -- Will Will Merrell Virtual Assistant [EMAIL PROTECTED] Moreland Business Solutions - Your partner in business. http://www.morelandsolutions.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 get MySQL to list current db's ?
I have two sample MySQL db's running on a local Linux box w/Apache... Once I enter "mysql" from the Linux command line... Is there a way to ask MySQL to list the db's that are currently created? Thanks, Will - 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
Compiling a list of MySQL tools - Please contribute.
I'm just getting into MySQL and have discovered two tools so far... - DBToolsFreeware www.dbtools.com.br/EN/ Manage MySQL & Convert Access & more - Intelligent Converters Shareware www.convert-in.com Converts from Access, Excel & more Please contribute to the list. Thanks, Will - 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 is DBTools Software?
Has anyone used DBTools Software? (http://www.dbtools.com.br/EN/) It is a free utility for managing MySQL & other db's. I also discovered Intelligent Converters at www.convert-in.com Both look good. The first is free and does more than convert Access to MySQL. The second converts more stuff... like Excel to MySQL. All comments encouraged. Thanks, Will - 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 delete MySQL db ?
I have a couple of "test" MySQL data bases on my Linux/Apache box and want to delete them for a clean start... How do I delete all the files associated with the two db's ? Thanks, Will - 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: What are basic db files MySQL creates?
Roger, Are the files that you mentioned stored in the directory for the db ? thanks for the help, Will -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED]] Sent: Friday, December 06, 2002 6:31 AM To: [EMAIL PROTECTED] Cc: Will Standley Subject: Re: What are basic db files MySQL creates? * Will Standley > Question: > > When I create a new db using MySQL... > > What files does MySQL create? None. It creates a new directory only. When you create a table, and the used table handler is MyIsam, three new files are created: *.frm, *.MYI (index) and *.MYD (data). If the table handler is InnoDb, only the *.frm file is created, the indexes and data are stored in innodb tablespace. -- Roger sql - 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
What are basic db files MySQL creates?
Question: When I create a new db using MySQL... What files does MySQL create? I'm asking because a MySQL sample db was created on the machine I am using and they don't seem to be where I think they should be... and I want to search for them... (I looked in root/var/lib/mysql/ and didn't see them... or at lease I did not recognize them.) Thanks, Will - 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 tell MySQL where to store db files ??
1 - When I create a new db using MySQL... Can I tell MySQL where (what path & directory) to store the new db files? And... 2 - To back up the db... can I just copy the files in that directory to a backup device like a CD or floppy? Thanks, Will - 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: Some Basic Questions I can't Find Answers to
Hello Linda, You said ---> The MySQL database I want to run will be on my web host. Since I don't have the server physically in my possession, is the working method to install MySQL to my machine, develop the routines there, then upload them to my server? You may not need to do this. Check your host's services, and see if you can login to your account using a terminal emulator, like putty, for telnet or SSH. Putty is a freebie that works for both telnet and SSH logins. Your host may or may not offer telnet or SSH as a service. Another possibility is that your host might also have a PHPMySQL login panel that will allow you to work everything on the server remotely from your desktop. In this case, you wouldnt even need to use putty (though I like using both the PHPAdminPanel and the terminal emulator). There are even other possible solutions that are a bit more involved. If by any chance you had tables developed in Access, for instance, then you could download and install an ODBC MySQL driver API on your computer that will allow you to login directly to the database to do import/exports on tables right from access. I am sure there are API's like this that would allow you to import/export tables, but your host would have to allow you to form an ODBC conneciton in the first place, so I would check with them. You said --> I have attempted to install to my Windows 2000 pro system. Still fumbling with that. Should I be trying to install the version most like the one on my host's server? He's on Linux Apache. Will that cause a problem? --- That sounds reasonable. But still you'll still need to form an ODBC connection if you go this route. Just a note on importing and exporting if you use a terminal emulator. You can do it manually from the command line, like this: To "export" all the tables in the DB to a directory on your server, you can use: mysqldump -u username -p databasename > filename.sql Here username and database name are your username and database name, and the filename.sql is whatever you want to name the file. Once you have run dump the tables will be in that file in the directory (as a document external to the actual DB itself), and then you can just download them to your desktop via FTP. I think the PHPAdminPanel may also be of assistance for this, though I havent used it. To reload data stored in files, just do this: mysql -u username -p dbname < filename.sql Notice the direction of the arrow. It determines whether the tables are coming or going. I use a little mnemonic thing where if the arrow points TO the database commands, then it is going INTO the Db, but it is point AWAY from the Db commands, then it is coming OUT of the database. You said ---> I don't see anything in either of these books on uploading, which makes me wonder if they're assuming I have access to the actual server. Of course I do, in a limited way, but I don't see any explanations on how to access the MySQL on my virtual "machine" over at the web host's. --- Again, if you can use a terminal emulator, then you'll have to login twice. Once to get into your account, and then a second time to get into the database. If you need more assistance on how to do this, then let me know. HTH, Good luck, Will P.S. Sorry to be so long-winded, but I just got through figuring all this stuff out myself. I know how hard it can be. _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail - 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: Cant Explain Error Message
Thanks... syslexia ducks... Wil --- Hello. I am trying to create a table in one of my databases using textbook code, but it is giving me an error message. Here is how it reads: mysql> create TABLE messages -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, -> forum int not null, -> author char(255) not null, -> subject char(255) not null, -> email char(255) not null, -> date datetime not null, -> ip char(255) not null, -> message text not null, -> thread_id not null -> ) -> ; ERROR 1064: You have an error in your SQL syntax near 'not null )' at line 11 I have tried a few different ways of rewriting the id line, like this for instance: id int not null primary key auto_increment, That's not line 11 though. Line 11 is -> thread_id not null , which is missing a column type. _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail - 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
Cant Explain Error Message
Hello. I am trying to create a table in one of my databases using textbook code, but it is giving me an error message. Here is how it reads: mysql> create TABLE messages -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, -> forum int not null, -> author char(255) not null, -> subject char(255) not null, -> email char(255) not null, -> date datetime not null, -> ip char(255) not null, -> message text not null, -> thread_id not null -> ) -> ; ERROR 1064: You have an error in your SQL syntax near 'not null )' at line 11 I have tried a few different ways of rewriting the id line, like this for instance: id int not null primary key auto_increment, But it still keeps giving the error message. It's odd it's not working because this is a textbook example. Can anyone say what I might be doing wrong? Thanks. Will _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus - 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
$$ Wanted: MySQL & PHP expert assistance
W am looking for some expert assistance using MySQL & PHP on a Linux/Apache based internet site development project. The project shouldn't take a knowledgeable & experienced professional long so you should be able to work it in your schedule without stopping everything forever. Interested parties should reply directly to my email. Ideal candidate will be located in SE USA, but will consider all. Email [EMAIL PROTECTED] thanks. Will Standley NetPV, Inc. - 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
Sorta OT: Perl/MySQL Mail List Module?
Greetings, This may be a bit OT, but does anyone know of a good perl/cgi module that works with DBI (MySQL) to write a mailing list? PLMK, Thanks, Will _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail - 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
newbie stuff: good mysql books?
Greets Folks, I have been wanting to get into data driven sites for the longest time, and I can do some immediate level work in them using perl, but my understanding is still not comprehensive enough for whatever it is I want to do (subconsciously). I sense there is some need to pursue database design better, and an overall book on system's design might be good too. I've read MySQL and Perl for the Web by Paul, and Programming with CGI.pm, and both helped a lot. But there is some sort of a want to work specifically with the database more, especially design for largescale systems development, and I am just wondering what books might be good for this purpose? PLMK, Thanks, Will _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail - 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: Connecting to MySQL on Linux from a Windows machine. -- Resolved !!
Well, after much fussing around with RPM's and searching this list, I have resolved my problem. The clue was the discussion on this list regarding the glibc bug. I upgraded to the 4.0.x RPM's that I got from the MySQL site and after I got all of the dependency problems fixed I was able to start up a MySQL/PHP setup that worked. Now both the website development works and I can use MyCC on the windows machine to administer the database. Thank you to all that considered my problem. --Will - 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: Connecting to MySQL on Linux from a Windows machine.
Thank you, but at this point I am pretty sure that my problem is not a
matter of entries in the user table. When I attempt to telnet into port 3306
the connection is immediately dropped. I don't really know if any connection
is actually established at all, it may be established and immediately
dropped on simply refused from the start. There does not appear to be any
exchange of user names or passwords. I have several variations of my
username and host name in the user table. I believe that one of them should
have worked if that was the problem.
I believe that the problem lies in the mysql configuration. I think that it
is not responding to the TCP/IP port in the way that I need it to. I have
verified that the --skip-network flag is NOT set (set to OFF) so it is not
that simple, but I cannot find any other flag that looks any more promising.
Any other Ideas?
BTW Thanks for the webmin tip, I'll give it a try. Doesn't solve all of my
problem, but it may help.
Thanks,
--Will
> -Original Message-
> From: STIBS [mailto:stibs-pi@;gmx.de]
> Sent: Friday, October 18, 2002 1:35 PM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: RE: Connecting to MySQL on Linux from a Windows machine.
>
>
> Will,
>
> |||)So I still need some more ideas.
>
> I have to learn your problem each time again when I setup a new
> machine every half a year.
>
> I try each time the following hostnames:
>
> localhost (didn't work 4u)
> 127.0.0.1 (IP for localhost)
> hostname (e.g. databaseserver.mydomain.com- what you called the machine)
> 192.168.0.x (Your real network IP, can also be in 10.* or 172.25.*)
> localhost.localdomain (results to same as hostname)
>
> one of them fit's ;o) ... if not I install webmin from
> www.webmin.com and connect via Browser to the machine
> (http://IP.ADD.RE.SS:1 - login as root). In Webmin's server
> section you have a complete webbased MySQL administration. There
> I make all settings and forget about it. It just works after this setup.
>
> STIBS
>
-
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: ERROR 2013: Lost connection to MySQL server during query
Hi, This is the same error that I am getting, except that in my case the
server does not die, I just cant connect from another machine. All I get is
this error, which seems to be because the server is not responding to the
3306 port. or something like that.
I don't know if this is the same problem or if Animesh has a different
problem that generates a similar response.
See the "Connecting to MySQL on Linux from a Windows machine." thread for
more details on my situation.
Thanks,
--Will
> -Original Message-
> From: Animesh [mailto:animesh@;deeproot.co.in]
> Sent: Friday, October 18, 2002 4:53 PM
> To: [EMAIL PROTECTED]
> Subject: ERROR 2013: Lost connection to MySQL server during query
>
>
> Hi All,,
>
> I am getting this error and mysqld is dying out while tring to access a
> particular table in a particular databae while others don't fail..
>
> ERROR 2013: Lost connection to MySQL server during query
>
> Plz Help as early as possible.
> Regards,
> Animesh
>
> [EMAIL PROTECTED]
> DeepRoot Linux Pvt. Ltd.
> Bangalore, India
>
> -
> 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: Connecting to MySQL on Linux from a Windows machine.
Thank you to both of you who responded to my question. I have checked both of these issues and found that neither solve my situation. 1) I have (and had) both a 'will@localhost' and a 'will@"%"' entry in the user table. Is there something else that has to be done to set up the host? 2) According to 'mysqladmin variables' the --skip-networking option is set to OFF. That should mean that Olaf's suggestion is satisfied, right? So I still need some more ideas. Why am I not able to connect to MySQL from another machine? Thanks, -- Will Randy Witt wrote: >Will, > >Do you have a user/host pair for the client in the user table within the >mysql database?? It's not good enough just to have a user, the host has >to be setup too. And Olaf van Zandwijk wrote: > Hi, > > It seems to me that you can't connect to MySQL from an other machine at > all. Did you start MySQL with the --skip-networking option? Try removing > that option from the start-up scripts. > > \Olaf > In response to my Original Post: >Will Merrell wrote: > >I cannot connect from my Windows machine to the MySQL database running on >the Linux server. > >I have tried several different programs and they all report an error that >says "Lost connection to MySQL server during connection". > >I have read all the articles I can find and searched the archives as well as >I can figure out but I am still stumped. > >Here are some pertinent details. > >1) The Server is running Redhat 7.3 and MySQL 3.23.49. It is on my local >network at 192.168.3.2 and there are no firewalls between it and the Windows >machine. (Both are behind the same firewall, and completely open to each >other.) > >2) The Windows machine is running Win98 SE at 192.168.3.x (DHCP). > >3) On the linux machine I can access MySQL just fine. The mysql command >works, and I can serve web pages with PHP/MySQL content. > >4) I can telnet, Samba, xterm, etc. to the linux machine just fine. I can >telnet to port 80 and get a connection although it doesn't do much there. > >5) I cannot telnet to port 3306. When I try, I get an immediate "Host >connection lost." error. > >Any ideas would be most appreciated. > > Thanks, > -- Will - 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
Connecting to MySQL on Linux from a Windows machine.
I cannot connect from my Windows machine to the MySQL database running on the Linux server. I have tried several different programs and they all report an error that says "Lost connection to MySQL server during connection". I have read all the articles I can find and searched the archives as well as I can figure out but I am still stumped. Here are some pertinent details. 1) The Server is running Redhat 7.3 and MySQL 3.23.49. It is on my local network at 192.168.3.2 and there are no firewalls between it and the Windows machine. (Both are behind the same firewall, and completely open to each other.) 2) The Windows machine is running Win98 SE at 192.168.3.x (DHCP). 3) On the linux machine I can access MySQL just fine. The mysql command works, and I can serve web pages with PHP/MySQL content. 4) I can telnet, Samba, xterm, etc. to the linux machine just fine. I can telnet to port 80 and get a connection although it doesn't do much there. 5) I cannot telnet to port 3306. When I try, I get an immediate "Host connection lost." error. Any ideas would be most appreciated. Thanks, -- Will - 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
Index Columns: Night of the Living Newbie
Hello, Without giving me the RTFM, and in 25 words or less... Can someone tell me what an index column is and does for a query? Thanks, Will _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.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
Re: Info on 4.0x release date
sql query Benjamin Pflugmann wrote: >Do you (all) realize you can only complain about this (falling so far >from their prediction), because they happened to give out some >estimate in the first place? > >Sounds only reasonable to me to try to avoid the same situation by not >giving out estimations to begin with. The first time I tried to ride a bike, I fell down and skinned my knee. Following your logic, I should have quit there. I am going to try and make this point one last time... It does not bother me so much that they mispredicted. It bothers me that there wasn't more information available about the cause of the delay. In the absence of being able to say "the product will absolutely positively ship on such and such day," most people are comforted by knowing more about the variables which affect when it will ship. If you've ever been made to sit in a doctor's examining room in your skivvies for an extended period of time -- you know what I mean. When the nurse left you and said the doctor would be right with you, the expectation was set. After thirty minutes shivering on the table, you are angry... not because you suspect the doctor is goofing off, but because you have no way of guessing if it will be 5 or 30 more minutes. If the nurse checked back in periodically and explained that the doctor was dealing with a serious situation and would be with you when he could, you would likely feel better (even if every time she pushed back the ETA for the doctor). If she took the time to add that you look nice in your Scooby Doo underwear, so much the better :) == NOTE: The information in this email is confidential and may be legally privileged. If you are not the intended recipient, you must not read, use or disseminate the information. Although this email and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by Cadwalader, Wickersham & Taft for any loss or damage arising in any way from its use. == - 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 does one successfully use mysql_store_result()?
If I understand you correctly, then you are trying to figure out how to distinguish between an empty result set vs. no result set (i.e. an error if statement was a SELECT). In the case of an error (or a not result set returning statement like INSERT), mysql_store_result will return null. In the case of an empty set, it will return a (non-null) pointer to a set that is empty. Subsequent to this, mysql_fetch_row() will return a null pointer indicating you have reached EOF for the result set. Hope this helps original message text HOW does one use this to confirm a record set? for instance, if mysql_store_result() = 0 then do nothing end if where mysql_store_result() returns an empty set on a select statement. any help obtained would be WONDERFUL!!! sorry, i'm new to this, the syntaxtual information is quite confusing to me. == NOTE: The information in this email is confidential and may be legally privileged. If you are not the intended recipient, you must not read, use or disseminate the information. Although this email and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by Cadwalader, Wickersham & Taft for any loss or damage arising in any way from its use. == - 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: Info on 4.0.x release date
I wrote >> Ironically, the fact that I pay no license fees to MySQL AB is based upon... Dean Ellis wrote >> Ahhh, I see. So, you are _completely irrelevant_. My relevance stems from the fact that I am a very good prospect to a company that has a stated interest in growing their community of satisfied users/customers. My feedback about what is preventing me from signing on the bottom line, to the extent that it may be representative of others like me, is like gold to a growing company (ask anyone who is in marketing or sales). To the extent that you have already been proselytized, you are likely less relevant than I. Dean Ellis wrote >> You are fully entitled to your opinion. Yes I am, Thank you. == NOTE: The information in this email is confidential and may be legally privileged. If you are not the intended recipient, you must not read, use or disseminate the information. Although this email and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by Cadwalader, Wickersham & Taft for any loss or damage arising in any way from its use. == - 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: Info on 4.0.x release date
Joel, you obviously put some thought into his response and I very much appreciate the insight. All in all, I feel you make some good points. I did find a couple of things that I want to rebut. >> Well, as a bystander, I think you've shot a little wide. When you say "bystander", are you referring to yourself of me? If you are referring to me, then I think you are wrong. My ideas and suggestions should stand or fall on their merits -- not whether I attended the online launch party. In fact, the perspective of the uninitiated can, in many cases, prove the most useful. >> Microsoft? Indeed Microsoft, Lotus, Word Perfect, Borland, Oracle and every other major software manufacturer in the world has missed deadlines, stuff happens. I am no stranger to this myself. My point is that every successful software company also spends a serious amount of effort to manage the development process and to communicate with the ultimate beneificiaries of their work. Frankly, based upon the volume and high quality of the MySQL code that I've seen, they must be doing this also. I did not write to criticize them for missing their projected deadline -- my criticism was about my perception that they had failed to communicate adequately with their community about it. >> So they gave you all the details they could. Did they? That was my whole argument. Over the past 6 months I have read no fewer than 10 responses from the team which have, in effect, said "we don't know, quit bugging us." During the same period, they have found time to name the dolphin and get venture funding and write about the booths they are setting up at various trades shows. All of this is important. I strongly support their efforts to be financially successful. I just think that it would be good (and in their economic interest) to more prominently feature news that summarized the development efforts, how they are going, and what the latest thinking is about the timing of milestones. >> are areas where the profit margin is [slim] But all of their more profitable areas are derrived from this one. >> But the analogy does not fit. These guys are cutting new ground. The analogy absolutely fits. They are not cutting new ground! Does their work involve innovation? Absolutely! But they are not building the super-collider or mapping the human genome here. They are writing business software, and that, my friend, is a well-traveled road. >> Otherwise, you'd have gone with Microsoft or Oracle... Actually, you have this backwards. I have been a Microsoft SQL Server developer for years and find it to be an incredibly good tool. I explored MySQL because I like the open source model and I am interested in offering my clients a choice. While I know that many in this group will consider this blastphamous, at this stage, SQL Server is a vastly superior product in many ways. A discussion of why I feel this would require a separate thread all its own, and frankly I am not interested in a pissing match with the MS haters. I use MS products when I feel they are the best solution to my clients business problems. I would like to reccomend and use MySQL in a similar fashion and I feel that when 4.1 is released, I will be able to do so without reservation. I am not so much interested in re-engineering every system I ever wrote to use MySQL. >> [you feel that] since you've spent that money you are entitled Actually, no. My two points are that 1) being free isn't enough, by itself, to make it a good buy -and- 2) their for-pay business model might well benefit from the improved communications I have suggested. >> Have you been working with the alphas and betas, reporting actual bugs, etc.? Have I installed the alphas and played with them? Yes. Have I endeavored to try using them in a production environment? No. The testing you are referring to, I believe, is best suited to users who have existing mysql systems that can be trial-migrated to the new version. I have no such system. Furthermore, the systems that I envision using the product for cannot be developed until a couple of missing (my view) features such as multi-table updates and sub-selects are available. >> the "small" license fee might have been "small" enough to go ahead and pay it Why don't you and everyone else who may read this each send me $1. Its a very small amount, I'm sure you wouldn't even miss it. What? You say that you will not send me a dollar? Why not? Is it because I haven't provided anything of value in exchange? I am in the same boat with MySQL. While thousands of people around the world may find that MySQL is a great fit with their needs (and make no mistake, I think it is a fine product), I have yet to personally come accross a suitable application for it in my work. The product simply is of no use to me,
RE: Info on 4.0.x release date
Obviously you don't agree with me and that is something I readily accept. I do have difficulty with the fact that you clearly spent more time typing your response than considering my points, which were intended to be constructive. >> You got the truth. Would you prefer someone lie to you about it? I will assume that your interest in mysql and participation in this list indicates that you are somehow involved in the process of software development. What is less clear is whether this may be a hobby or your profession. If it is the latter, I would love to know where it is that one can work in this industry where one is not expected to make projections about when project milestones might be met. This is called project management. And while those of us who simply love writing code find such pursuits annoying, they are nonetheless necessary and worthwhile -- accepting this is part of growing up. And while I applaud the honesty of saying "we won't declare it stable until it is stable," surely you can see your way clear that if you were explaining a similar situation to your boss or to your client, they would almost certainly (and justifyably) want you to give them more details. This is called accountability. >> What more information would be helpful, exactly? Do guesses really >> help? Guesses are nice... estimates are better. What is the difference, you ask? The word guess connotes random selection or selection based upon whimsy. Estimates, on the other hand, are approximations based upon some rational methodology. Suppose you take your car into the shop and after looking at it they inform you that it cannot be driven again until a new part is installed and that part will need to be special ordered. Would you not expect them to give you an estimate of how long that will take? Following your logic, they would be justified in telling you that "it will be here when it gets here." Perhaps you would feel better if they said something like "the computer says they have plenty in stock and it usually takes 1-2 days for shipments to arrive." In the later, the parts manager cannot say with absolute certainty when the part will arrive. But short of that certainty, he has given you the next best thing - his best estimate based upon a methodology (based upon past history and his experience). >> But that money isn't paid *to* MySQL AB, so how is that relevant? Ironically, the fact that I pay no license fees to MySQL AB is based upon the fact that I have no production systems that use it and I will not have production systems that use it until the new functionality coming in 4.1 is available. But the fact that I have not paid any money to MySQL AB does not mean that I am not doing things which ultimately improve their bottom line. Though this is my first posting in a while, there was about a three month period when I monitored this list every day and often contributed my knowledge when I thought it could be helpful. I have talked up MySQL to my colleagues. I have suggested ideas for new features. I certainly do not feel anyone need give me a medal for any of this, but my point is that by being yet another active member of the MySQL community, I (with those like me) help advance the product which will inevitably lead to more sales for MySQL AB. My point that licenses are but a small part of total cost is relevant in the sense that just being free isn't good enough to close the deal. Everytime I read one of the smartass responses, I wonder to myself if they feel they can get away with this attitude simply because they are not charging license fees. Maybe this is wrong, I hope so, but I think that they would be well served by if the took more seriously the concerns of their user community (paying and non-paying) because I believe that it will in the end enhance their success. >> But you fail to tell someone how to know if they're the intended >> recipient, don't you? Um, ok, what planet are your from that you do not recognize a standard (as in, appended automatically by my smtp server) disclaimer when you see one. Just curious, do you also bark at the moon? I'll be sure to pass your criticism on to the lawyers. - 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
Info on 4.0.x release date
When will 4.0.x finally be released to beta? The facts as I understand them: - 4.0 went alpha in Nov or Dec of '01 - At the time, Monty and his gang projected stabilization by Jan or Feb - More than 6 months have passed since and the product is not yet stabilized - No meaningful information has been released about what a more realistic date might be - Anytime anyone dares to stick their head up and inquire about this issue, they get their hat handed to them with a glib "when its ready" response It is impossible for me to believe that I am the only one frustrated by this. I spent months evaluating the suitability of MySQL for our systems last summer/fall. At that time I made the determination that it would be quite well suited, but only with the addition of some functionality promised in 4.0 and 4.1. It is not my intention to put the MySQL team on the defensive about this -- I know they are working incredibly hard. I do not, however, feel it is unreasonable for the user community to ask for more information about upcoming releases. I know that you are probably thinking that I don't have the right to be demanding when the software is free. Well guess what, it isn't! Every company that uses MySQL spends money on it (remember, licensing represents only a small fraction of total cost of ownership). Furthermore, MySQL AB is a for-profit venture whose fortunes are tied to the continued spread and use of their flagship open-source product. So... How about cluing us in on what's left to do, what progress has been made and a best-guess as to what the release schedule might look like. I wouldn't worry too much if your dates slip over time (I think you will find a sympathetic group), but just keep us updated so we can adjust our own plans. Thanks Will French == NOTE: The information in this email is confidential and may be legally privileged. If you are not the intended recipient, you must not read, use or disseminate the information. Although this email and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by Cadwalader, Wickersham & Taft for any loss or damage arising in any way from its use. == - 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
NULL ?
hello, Please dont tell me to RTFM on this one (cause I am already doing that), but for clarity's sake... can someone tell me what it means when you use NULL and NOT NULL in a query (specifically CREATE TABLE)? Also, when should I use them? Will K. _ Send and receive Hotmail on your mobile device: http://mobile.msn.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
Learning Data Types
Hi, I am learning about MySQL and how to use the CREATE TABLE command, and I was wondering where I could learn more about MySQL datatypes (cause they seem to be a big part of this command). Will _ Send and receive Hotmail on your mobile device: http://mobile.msn.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
3 Questions
Greets Folks, These questions may sound kinda basic, but here goes... 1.) What if I want to print (on paper) the contents of my MySQL database, then how do I do it? Everything I have done is for the web so far, so that's why I ask. I was thinking I should just use the reports off the web pages, but I dont know if there might be other ways. 2.) This question is more out of curiosity. Suppose I buy a database that has been prepopulated. Then how do I get that info into my existing database? 3.) Where would I look to buy prepopulated databases? Thanks! =) Will _ Chat with friends online, try MSN Messenger: http://messenger.msn.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
RE: Unsigned Int / Signed Int issue when using MyODBC2.5
Solved the problem, here's one for the archives.
Upgrade to the latest version of MDAC (currently 2.7).
.. The production server was still running an old version. Downloading
the MDAC2.7 from microsoft and installing it solved the problems.
If anyone wants me I'll be out the back hitting myself over the head
with a plank of hardwood.
Will Lotto
Systems Administrator
> -Original Message-
> From: Will Lotto [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, 2 July 2002 12:42 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Unsigned Int / Signed Int issue when using MyODBC2.5
>
>
> G'day,
>
> I've got a problem with the ODBC driver for MySQL, in that
> when I use an SQL select statement on a field of type
> UNSIGNED INTEGER, the resulting recordset in asp uses SIGNED
> INTEGER data types, so any data > 2^31 is dropped by asp.
>
> The offending code is as follows:
>
>
> Set sqlCommand = Server.CreateObject("ADODB.Command")
> sqlCommand.ActiveConnection = "MyDatabase"
> sqlCommand.CommandText = "SELECT * FROM " & SQLTable &
> " Where DataDate >= " & StartDate & " and DataDate <= " &
> EndDate & " ORDER BY DataDate"
> Set rs = sqlCommand.Execute
>
> The database design:
>
> mysql> describe SQLTable;
> +--+--+--+-+-+---+
> | Field| Type | Null | Key | Default | Extra |
> +--+--+--+-+-+---+
> | DataDate | int(10) unsigned | YES | | NULL| |
> | DataIn | int(10) unsigned | YES | | NULL| |
> | DataOut | int(10) unsigned | YES | | NULL| |
> | Elapsed | int(10) unsigned | YES | | NULL| |
> +--+--+--+-+-+---+
> 4 rows in set (0.00 sec)
>
>
> Unfortunatly, whenever the data in any of the fields exceeds
> 2,147,483,648 it is dropped from the resulting recordset. ...
> And just to throw a spanner in the works, that code works
> perfactly on our development PC, but not on the production server.
>
> Does anyone have any idea why this would be failing, or know
> a better way to select the code and drop it into a recordset
> (or array) in asp?
>
> Thanks for any help.
>
> Will Lotto
> Systems Administrator
>
>
> -
> 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
Unsigned Int / Signed Int issue when using MyODBC2.5
G'day,
I've got a problem with the ODBC driver for MySQL, in that when I use an
SQL select statement on a field of type UNSIGNED INTEGER, the resulting
recordset in asp uses SIGNED INTEGER data types, so any data > 2^31 is
dropped by asp.
The offending code is as follows:
Set sqlCommand = Server.CreateObject("ADODB.Command")
sqlCommand.ActiveConnection = "MyDatabase"
sqlCommand.CommandText = "SELECT * FROM " & SQLTable & " Where
DataDate >= " & StartDate & " and DataDate <= " & EndDate & " ORDER BY
DataDate"
Set rs = sqlCommand.Execute
The database design:
mysql> describe SQLTable;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| DataDate | int(10) unsigned | YES | | NULL| |
| DataIn | int(10) unsigned | YES | | NULL| |
| DataOut | int(10) unsigned | YES | | NULL| |
| Elapsed | int(10) unsigned | YES | | NULL| |
+--+--+--+-+-+---+
4 rows in set (0.00 sec)
Unfortunatly, whenever the data in any of the fields exceeds
2,147,483,648 it is dropped from the resulting recordset.
... And just to throw a spanner in the works, that code works perfactly
on our development PC, but not on the production server.
Does anyone have any idea why this would be failing, or know a better
way to select the code and drop it into a recordset (or array) in asp?
Thanks for any help.
Will Lotto
Systems Administrator
-
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
Design: General DB Question
Greets Folks, Are there any good online tutorials for DB design in MySQL? I'm not necessarily interested just in the programming per se, but in DB design theory in general. Thanks! =) Will _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - 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
Connection Problem
Greets Folks,
I am trying to get an online database set up and running, but I keep getting
error messages at login.
Here are two sample scripts I have been running (both are from Paul DuBois'
book MySQL and Perl for the Web).
## SCRIPT 1 ##
#! /usr/bin/perl -w
# intro4.pl - generate a Web page using the CGI.pm object-based interface
use strict;
use CGI;
my $cgi = new CGI;
print $cgi->header (),
$cgi->start_html ("My Page Title"),
$cgi->p ("My page body"),
$cgi->end_html ();
exit (0);
## SCRIPT 2 ##
#! /usr/bin/perl -w
# intro6.pl - connect to MySQL, retrieve data, write plain text output
use strict;
use DBI;
use CGI qw(:standard);
my ($dbh, $sth, $count);
$dbh = DBI->connect ("DBI:mysql:host=localhost;database=***",
"**", "***", {PrintError => 0, RaiseError => 1});
$sth = $dbh->prepare ("SELECT name, wins, losses FROM teams");
$sth->execute ();
$count = 0;
while (my @val = $sth->fetchrow_array ())
{
print p (sprintf ("name = %s, wins = %d, losses = %d\n",
$val[0], $val[1], $val[2]));
++$count;
}
print "$count rows total\n";
$sth->finish ();
$dbh->disconnect ();
exit (0);
I have double checked everything. CGI.pm works withother scripts. Thwe
server I know for a fact has MySQL on it, DBI and DBD::mysql. the fiedls
have been uploaded in ASCII format, and I've CHMOD-ed them to 755.
I can't tell what else could be wrong. The only thing I can think of might
be the login command. I had to block out the real user names and passwords
and stuff with asterisks here, but the username and password I use are for
the DBA, an I am assuming that that user has read/write permissions. The
only other thing I can think of might be that the line
DBI:mysql:host=localhost might be different on the actual system than in the
book. Do any of these things sound possible? If so, how can I correct them
or get the right info?
Thanks,
Will
_
Chat with friends online, try MSN Messenger: http://messenger.msn.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
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Seeking Freelancer
Greets Folks, I need help with some too-involved-for-me CGI stuff, and I'd like to work with a freelance programmer/consultant. The consulting will be for a mature themed site, and I am running on a limited budget, so open minds only please. The project is for a paid membership site using Perl/MySQL. The site will also run a number of other CGI programs, namely eCards, bulletin boards, auction and image galleries. Some of the work I can handle myself, but other times I may need direct assistance. Thanks, Will _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx - 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
Scanning Directory/Inserting Image Links to DB
Greets Folks, Can someone recommend a Perl script/technique that will scan a Unix directory of image/text files, and then insert the links into a MySQL database? I used to have a good one, but it got misplaced... Doh! Thanks, Will _ Send and receive Hotmail on your mobile device: http://mobile.msn.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
Select most recent files
Greets Folks, Quick question here... Is there a command I can use to select the most recent files inserted into a MySQL table? Thanks, =) Will _ Chat with friends online, try MSN Messenger: http://messenger.msn.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
SSI's and Databases?
Greets Folks, Does anyone know if it is possible to get an SSI to work in conjunction with a database, like MySQL? I'd like to have a script that will insert a new text file each day along with a timestamp, and I was wondering if it would be possible to use an SSI to draw the info out of that text file for insertion into an html module that would be used in the main cgi document. Please let me know... I don't want to use any chmod 777 commands for this, so I figure inserting the info into a database each day would be the best way to get the job done... Thanks, Will _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.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
RE: Paul DuBois Book
My vote is have the 'mySQL Cookbook' get out the door first, give Paul a quick respite, and then put his nose to the grind stone for 'mySQL version 4'. 8-) -Original Message- From: Anthony W. Marino [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 7:30 AM To: Simon Green; [EMAIL PROTECTED] Subject: Re: Paul DuBois Book On Friday 22 February 2002 05:48 am, Simon Green wrote: > It would be nice to see some thing for V4. > > PS Paul...its only because we liked the first one so much. > > -Original Message- > From: D Bamud [mailto:[EMAIL PROTECTED]] > Sent: 22 February 2002 10:47 > To: [EMAIL PROTECTED] > Subject: Paul DuBois Book > > > When the Next Edition of MySQL By Paul Dubois is coming? > +1 with my vote. > - > 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 - 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
Uploading to MySQL?
Greets Folks, Suppose I wanted to upload a lot of files (1000 or so) to MySQL... sort of like FTP-ing files to another server's directory... Is there a way to get them all into MySQL like that? Uploading to a database seems much different than uploading to a server... Next, suppose I just FTP-ed a bunch of files to a directory on my server, and then I wanted to catalog all the links in a MySQL database table... Is there a way to get all those links into the database without having to manually insert them one at a time? Thanks, Will _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.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 - 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
Data Validation
Greets Folks, Semi-newbie question here. Is there a way to perform data validation (in the SQL language for MySQL) for input I would have coming in off the web (i.e. via HTML/CGI)? I need some tuts, or even just the raw SQL code with some simple install directions. Thanks, =) Will _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.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
Seeking Database/Scripts
Greets Folks, This is my first post here, so I'd just like to begin by saying hello. It seems like a busy list, so I hope to learn a lot and make some good contributions myself. =) But, right now, I have a question that I was wondering if someone could help me with... I need a database and login script to password protect a members only area to a website. I am wondering if there is any prefabbed stuff like that out there that I could either get for free or just buy? Please know that I DON'T want to use .htaccess/.htpasswd because I would like to custom design the login pages. I'd like to see if I could get a database that would also allow me to set cookies to keep users identified at login, "timers" as to when memberships expire, make easy updates as needed. I am working with a Unix server that runs both PHP and Perl in conjunction with a MySQL database. Please let me know if you have any scripts, tutorials or off-the-shelf software you could recommend. Thanks! =) Will K. P.S. Is the Paul DuBois on this list the same Paul DuBois who wrote "MySQL and Perl for the Web"? Just wondering. _ Send and receive Hotmail on your mobile device: http://mobile.msn.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
Corrupted table
I wonder if anyone can assist me with this one. I appear to be getting sporadic corruption of a table that is then unrecoverable. The error I get is as follows: Incorrect information in file: 'user_admin_table.frm' This is a longshot but Has anyone seen this error before? - 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: Can MySQL be run from a CD-ROM?
I went to the link provided to the list archive just to see if I could find anything. I couldn't find anything with "CD", "CD ROM", "CD-ROM", "running mySQL for a CD" and various permutations and combinations thereof. Also did a Google search for this and came up blanked. So, this is not as readily available as it might seem. Now what I did to get MySQL 3.23.41 to run off of a CD with Win32 was this. Downloaded & put the install zip file on CD. Installed mySQL on my machine. Installed mySQL ODBC driver. Ran WinMySQLadmin to make sure that the my.ini file was correct and closed the application. Copied my.ini to a safe place, edited original so that the application related paths pointed to the new location on the CD. If you want to change/update your data, you might want to leave the data paths pointing to a folder on your hard drive. Burnt a CD from the mySQL subdirectory + the my.ini file. Uninstalled mySQL from the hard drive Rebooted. Logged onto e:\mysql\bin and ran mysql from there. Finish using mySQL & log off. Remove CD. Works for me. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jeremy Zawodny Sent: Wednesday, October 31, 2001 10:30 PM To: Carter, Robert L (MN65) Cc: '[EMAIL PROTECTED]' Subject: Re: Can MySQL be run from a CD-ROM? On Mon, Oct 29, 2001 at 09:49:08AM -0600, Carter, Robert L (MN65) wrote: > > Alternatively, is it possible to set up MySQL to be runnable from a > CD-ROM, but using a hard-wired hard drive location in which to find > the database tables? Yes. This was discussed here about a week ago, so it should be easy to find in the archives. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 56 days, processed 1,245,102,049 queries (257/sec. avg) - 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: auto increment problems
> Error: 1062 - Duplicate entry '2147483647' for key 1 That number is the largest number which can be stored in a signed 32-bit (INT) field. As I rather doubt that your table contains > 2 billion rows, I am betting that your auto-numbers are starting at 1. My advice would be to create a new table and use "insert into" to populate all fields but the auto_increment field by specifying a column list. Additionally, you may find that myisamchk may have some feature to deal with this sort of problem but I don't recall. Will > -Original Message- > From: Schmidt, Allen J. [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 04, 2001 1:34 PM > To: MySQL > Subject: auto increment problems > > > I am sure this has been discussed but have not found specifically what I > need. > > I have a MyISAM table with an auto_increment primary key field. It sorta > went nuts and started inserting strange new values. > I decided to follow the stated procedures for resetting an auto_increment > field by deleting and then readding the field. > > ALTER TABLE agents ADD column agent_dbid INT NOT NULL > AUTO_INCREMENT PRIMARY > KEY; > > But this has given a variety of errors. Here is the latest: > > Error: 1062 - Duplicate entry '2147483647' for key 1 > > I can provide other info if it would help but have not been using > MySQL for > long and am sorta stuck. > > Thanks > > Allen > > - > 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: auto increment problems
> Error: 1062 - Duplicate entry '2147483647' for key 1 That number is the largest number which can be stored in a signed 32-bit (INT) field. As I rather doubt that your table contains > 2 billion rows, I am betting that your auto-numbers are starting at 1. My advice would be to create a new table and use "insert into" to populate all fields but the auto_increment field by specifying a column list. Additionally, you may find that myisamchk may have some feature to deal with this sort of problem but I don't recall. Will > -Original Message- > From: Schmidt, Allen J. [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 04, 2001 1:34 PM > To: MySQL > Subject: auto increment problems > > > I am sure this has been discussed but have not found specifically what I > need. > > I have a MyISAM table with an auto_increment primary key field. It sorta > went nuts and started inserting strange new values. > I decided to follow the stated procedures for resetting an auto_increment > field by deleting and then readding the field. > > ALTER TABLE agents ADD column agent_dbid INT NOT NULL > AUTO_INCREMENT PRIMARY > KEY; > > But this has given a variety of errors. Here is the latest: > > Error: 1062 - Duplicate entry '2147483647' for key 1 > > I can provide other info if it would help but have not been using > MySQL for > long and am sorta stuck. > > Thanks > > Allen > > - > 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: Migration to mysql from MS SQL Server
Carl Troein:> Yes, but only if you don't include 8 kilobytes worth of Carl Troein:> redundant irrelevance when you post. Wow, pretty harsh there Carl -- there are some really good decaffeinated brands available now. Although you are correct in pointing out that continuing to carry the entire thread costs some bandwidth and disk space, imho, not having to sift back through scores of old posts to find the original is well worth it. Will > -Original Message- > From: Carl Troein [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 04, 2001 11:59 AM > To: [EMAIL PROTECTED] > Subject: Re: Migration to mysql from MS SQL Server > > > > Harpreet writes: > > > Can we create a php page that will use mssql_connect to connect > to ms sql > > server get the data and then use mysql_connect and connect to > mysql server > > and populate the table. > > > > Is this possible. > > Yes, but only if you don't include 8 kilobytes worth of > redundant irrelevance when you post. > > //C > > -- > Carl Troein - Cmrdan / Istari-PixelMagic - UIN 16353280 > [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ > Amiga user since '89, and damned proud of it too. > > > - > 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: Migration to mysql from MS SQL Server
venu:> If you can make use of MS Access and ODBC in the middle layer in venu:> the conversion, venu:> from MSSQL-->MySQL, then probably you don't need any editing. venu:> Here is the simple venu:> procedure: Yes this is true and probably a superior method for small databases (say, less than 300Mb). But if your database is large (mine is > 6Gb , with a few tables > 1Gb), then the method you outline is excrutiatingly sslllooww. Also keep in mind that MS Access has a hard coded 2Gb limit on database size. Finally I would mention that the script editing chores were, for me, valuable in that they provided me with a chance to review my schema for opportunities to take advantage of some features in MySQL not available in MS SQL (date fieldtypes where MS has only datetime, enum and set types, etc). Will > -Original Message- > From: Venu [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 03, 2001 4:00 PM > To: [EMAIL PROTECTED]; Pavel Hant; [EMAIL PROTECTED] > Subject: RE: Migration to mysql from MS SQL Server > > > Hi !!! > > > -Original Message- > > From: Will French [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, October 03, 2001 12:28 PM > > To: Pavel Hant; [EMAIL PROTECTED] > > Subject: RE: Migration to mysql from MS SQL Server > > > > > > I just did this with a rather large database. Here are the steps that I > > followed: > > > > 1. In MSSQL Enterprise Manager, use "generate scripts" to, > well, generate > > your SQL scripts. (right click on any table and select All|Generate > > Scripts). I decided to break save the index builds until after > the data was > > loaded so I actually created two script files. > > > > 2. Edit the scripts. I used notepad and Excel to modify the > scripts for > > MySql compatibility. This mostly involved using the > ever-popular search and > > replace (e.g. eliminate all the [square] brackets that MS is so fond of, > > dumping the collation sequence stuff, converting my bit fields > to tinyint, > > etc). When the text massaging was more complex, I would cut > and paste it > > into Excel then used worksheet functions like Find() Mid() Left(), etc., > > then paste the section back into notepad. Because my schema is > not terribly > > exotic, I found the field type conversion to be pretty straightforward - > > your experience may be different. > > > > 3. Get the data out of MSSQL. Some people are fond of the > newer DTS (data > > transformation services) but us old timers still prefer BCP > (which, in case > > you don't know is a command line "bulk copy program"). I > created a batch > > file with one line for each table. If you are using trusted > security model, > > a single bcp command would look like this: > > C:\Data> BCP dbname.dbo.tblname out tblname.txt -S > srvrname -T -c -t > > \t -r \n > > If you are using Sql Server authentication, then it would look > like this: > > C:\Data> BCP dbname.dbo.tblname out tblname.txt -S > srvrname -U sa -P > > sapwd -c -t \t -r \n > > The command line options "-c -t \t -r \n" tell it to convert fields to > > character data, terminate fields with a tab character (hex 09) > and terminate > > lines with a carriage-return/linefeed pair (hex 0D 0A). The > line (or row) > > terminator is decieving 'cause it looks like I just asked for a > linefeed but > > it gives you both anyway. > > > > 4. Run your create table script in mysql (i use "\. > scriptname.sql" from > > the mysql prompt. > > > > 5. Load the data into mysql. This one's easy: > > > > mysql > use dbname; > > > > mysql > load data infile 'c:\\data\\tblname.txt' > > > into table tblname > > > lines terminated by '\r\n'; > > > > 6. Assuming you didn't already build your indexes in step 4, > run the script > > to build them now. > > > > > > There are probably a million different ways to do this. This > is the way I > > did it and it worked well for me. Since I have huge amounts of > data, the > > fact that both BCP and "load data infile" are very quick was a deciding > > factor. > > > > Hope this helps. > > > > Will French > > > > > > > > > -Original Message- > > > From: Pavel Hant [mailto:[EMAIL PROTECTED]] > > > Sent: Wednesday, October 03, 2001 2:59 PM > > > To: [EMAIL PROTECTED] > > > Cc: Pavel Hant > >
RE: Migration to mysql from MS SQL Server
I just did this with a rather large database. Here are the steps that I followed: 1. In MSSQL Enterprise Manager, use "generate scripts" to, well, generate your SQL scripts. (right click on any table and select All|Generate Scripts). I decided to break save the index builds until after the data was loaded so I actually created two script files. 2. Edit the scripts. I used notepad and Excel to modify the scripts for MySql compatibility. This mostly involved using the ever-popular search and replace (e.g. eliminate all the [square] brackets that MS is so fond of, dumping the collation sequence stuff, converting my bit fields to tinyint, etc). When the text massaging was more complex, I would cut and paste it into Excel then used worksheet functions like Find() Mid() Left(), etc., then paste the section back into notepad. Because my schema is not terribly exotic, I found the field type conversion to be pretty straightforward - your experience may be different. 3. Get the data out of MSSQL. Some people are fond of the newer DTS (data transformation services) but us old timers still prefer BCP (which, in case you don't know is a command line "bulk copy program"). I created a batch file with one line for each table. If you are using trusted security model, a single bcp command would look like this: C:\Data> BCP dbname.dbo.tblname out tblname.txt -S srvrname -T -c -t \t -r \n If you are using Sql Server authentication, then it would look like this: C:\Data> BCP dbname.dbo.tblname out tblname.txt -S srvrname -U sa -P sapwd -c -t \t -r \n The command line options "-c -t \t -r \n" tell it to convert fields to character data, terminate fields with a tab character (hex 09) and terminate lines with a carriage-return/linefeed pair (hex 0D 0A). The line (or row) terminator is decieving 'cause it looks like I just asked for a linefeed but it gives you both anyway. 4. Run your create table script in mysql (i use "\. scriptname.sql" from the mysql prompt. 5. Load the data into mysql. This one's easy: mysql > use dbname; mysql > load data infile 'c:\\data\\tblname.txt' > into table tblname > lines terminated by '\r\n'; 6. Assuming you didn't already build your indexes in step 4, run the script to build them now. There are probably a million different ways to do this. This is the way I did it and it worked well for me. Since I have huge amounts of data, the fact that both BCP and "load data infile" are very quick was a deciding factor. Hope this helps. Will French > -Original Message- > From: Pavel Hant [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 03, 2001 2:59 PM > To: [EMAIL PROTECTED] > Cc: Pavel Hant > Subject: Migration to mysql from MS SQL Server > > > Hi everybody, > > I am trying to migrate a database from MS SQL Server 2000 to mysql. Does > anybody know any source of information on this matter, or can > anybody share > his/her own experience of such a migration? Suggestions? > > Any help would be appreciated. > > Thanks, > > Pavel > > > - > 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: Is it possible to Rename Database
ravi:> Or, use mysqldump to dump the entire database, edit the CREATE ravi:> DATABASE line ravi:> at the top to the new name and pipe it all back into mysql, then drop the ravi:> old one? That's sort of taking the long way around the barn, isn't it? Will > -Original Message- > From: Ravi Raman [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 03, 2001 6:04 PM > To: Carsten H. Pedersen; Brandon Lewis; [EMAIL PROTECTED] > Subject: RE: Is it possible to Rename Database > > > Or, use mysqldump to dump the entire database, edit the CREATE > DATABASE line > at the top to the new name and pipe it all back into mysql, then drop the > old one? > > -ravi > > -Original Message- > From: Carsten H. Pedersen [mailto:[EMAIL PROTECTED]] > Sent: October 3, 2001 11:50 AM > To: Brandon Lewis; [EMAIL PROTECTED] > Subject: RE: Is it possible to Rename Database > > > > Is it possible to rename a database? > > I would assume that the only way to do is is to > shut down the server, then rename the directory > of the database. > > / Carsten > -- > Carsten H. Pedersen > keeper and maintainer of the bitbybit.dk MySQL FAQ > http://www.bitbybit.dk/mysqlfaq > > > > - > 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 > > - 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 dump only table structure with MySQLDump?
check the command line option -d (or --no-data) > -Original Message- > From: Barry McClure [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 03, 2001 1:45 AM > To: [EMAIL PROTECTED] > Subject: --How to dump only table structure with MySQLDump? > > > I would like to extract the DDL for the database so I can re-run > it elsewhere. > MySQLDump can do that, but it also exports the data. Is there a > way for it > to just export the Create Table statements? > > TIA > > Barry > > - > 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: last_insert_id() bug ?? using INSERT IGNORE
Paul DuBois> INSERT IGNORE can't fully assess whether the record is to be
ignored
Paul DuBois> until the record's contents have been generated.
And why can't it wait until after the ignore/don't ignore assessment before
assigning a new id? Call it a bug... call it a design feature... call it
what ever... it doesn't change the fact that the behavior is
counter-intuitive and could cause problems with some designs (at the very
least, having gaps in the sequence could result in minor performance
degradation due to uneven key distribution).
Will French
> -Original Message-
> From: Paul DuBois [mailto:[EMAIL PROTECTED]]
> Sent: Sunday, September 30, 2001 10:45 PM
> To: marcus davy; [EMAIL PROTECTED]
> Subject: Re: last_insert_id() bug ?? using INSERT IGNORE
>
>
> At 11:44 AM +1200 10/1/01, marcus davy wrote:
> >If you specify the keyword IGNORE in an INSERT, any rows that duplicate
> >an existing PRIMARY or UNIQUE key in the table are ignored and are not
> >inserted.
> >But the last_insert_id() function still appears to increment by one
> >in situations when the query is not adding any new information to
> >the table.
> >
> >This looks like a bug to me can anyone enlighten me to this anomaly?
>
> Why is it a bug?
>
> INSERT IGNORE can't fully assess whether the record is to be ignored
> until the record's contents have been generated.
>
> >
> >I am using 3.23.42-log on red hat 7.1 (also same results on 3.23.40-log).
> >I have searched the list archive for this topic but havent found
> anything yet.
> >
> >
> >mysql commands to test this below
> >
> >CREATE DATABASE test;
> >use test;
> >CREATE TABLE organism (
> > OM varchar(100) NOT NULL unique,
> > OM_ID tinyint(1) NOT NULL auto_increment,
> > PRIMARY KEY (OM_ID)
> > ) TYPE=MyISAM;
> >
> >INSERT IGNORE INTO organism VALUES('foo', NULL);
> >INSERT IGNORE INTO organism VALUES('fodda', NULL);
> >SELECT * FROM organism;
> >
> >#+---+---+
> >#| OM| OM_ID |
> >#+---+---+
> >#| foo | 1 |
> >#| fodda | 2 |
> >#+---+---+
> >#2 rows in set (0.00 sec)
> >
> >SELECT last_insert_id();
> >
> >#+--+
> >#| last_insert_id() |
> >#+--+
> >#|2 |
> >#+--+
> >#1 row in set (0.00 sec)
> >
> >INSERT IGNORE INTO organism VALUES('fodda', NULL);
> >
> >#Query OK, 0 rows affected (0.00 sec)
> ># NO NEW INFORMATION ADDED
> >
> >SELECT * FROM organism;
> >#+---+---+
> >#| OM| OM_ID |
> >#+---+---+
> >#| foo | 1 |
> >#| fodda | 2 |
> >#+---+---+
> >#2 rows in set (0.01 sec)
> >
> >SELECT last_insert_id();
> >
> >#+--+
> >#| last_insert_id() |
> >#+--+
> >#|3 |
> >#+--+
> >#1 row in set (0.01 sec)
> >
> ># last_insert_id has incremented by 1, by adding a further new field,
> ># last_insert_id() will correctly show the last inserted id again
>
>
> --
> Paul DuBois, [EMAIL PROTECTED]
>
> -
> 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: why would LOWER
You didn't mention how large your table is or if the email and cust fields are part of an index. In many situations, the results you are seeing make perfect sense to me. If email is a part of an index then the LOWER function may need to be performed on each value of email in the index before the comparison can be performed. Additionally you may be negating the effect of a hashing algorithm. I can not say with certainty how MySQL does indexed lookups, but many db engines start by narrowing down a search using a hash of the searched for value and looking that up in a hash table for the index. If the character set you are using is case sensitive (and I assume it is or you wouldn't need the LOWER), then the hashing algorithm would also be case sensitive. Will French > -Original Message- > From: Joe Kaiping [mailto:[EMAIL PROTECTED]] > Sent: Sunday, September 30, 2001 4:14 PM > To: [EMAIL PROTECTED] > Subject: why would LOWER > > > > Hi there, > > Is it expected that using the LOWER function should greatly increase query > time? Or is there a MySQL setting that can help speed it up? > Below are the > results of a query against a table that has an index on cols (email,cust). > Using LOWER increases the query time by 10.5 seconds. (eek!) > > I've fixed the scripts that input data into the ind table so that > emails are > now first converted to lower case before being inserted, but was > curious if > doing some data mining to fix the existing emails is the only (probably > best) solution. > > Thanks, > Joe > > SELECT t.id, t.Name, t.city > FROM ind t > WHERE t.cust=1 >AND t.email='[EMAIL PROTECTED]'; > > 1 row in set (0.01 sec) > > SELECT t.id, t.Name, t.city > FROM ind t > WHERE t.cust=1 >AND LOWER(t.email)='[EMAIL PROTECTED]'; > > 1 row in set (10.52 sec) > > > - > 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: ms SQL server 7.0 -- > mysql?
> ms sql server is in our school and since i want to learn mysql, i want > to copy the sammple databases from the school to my house. how do i do > that? from ms sql --> txt --> mysql? The method I outlined is exactly that mssql->txt->mysql. Obviously, since the two machines do not seem to be networkable, I am assuming that you have some means (cd-burner, etc.) of moving data from your windows machine to your linux machine at home. Both the "generate sql scripts" function and bcp create text files which can be copied to the medium of your choice. Will French > -Original Message- > From: --- [mailto:[EMAIL PROTECTED]] > Sent: Sunday, September 30, 2001 1:14 PM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: ms SQL server 7.0 -- > mysql? > > > On Sun, Sep 30, 2001 at 01:00:07PM -0400, Will French wrote: > > I'm not sure what you mean by "stand-alone". If you mean not > connected to a > > network then I don't see how since ms-sql-server only runs on > Windows and > > you have specified that your mysql server is linux. > > > > Unless you have a huge schema (lots of tables and/or lots of columns) I > > would suggest using the "generate sql script" command in sql-server > > enterprise manager and then editing the file as necessary for > compatibility > > with mysql (e.g. changing bit fields to tinyint, etc). Once > you have the > > database created in mysql, I would use a combination of the bcp utility > > (with the "/t" option which creates tab-delimited output) and > the "load data > > infile" command to load it into mysql. > > > > It is possible that someone has created a more automated way of > doing this > > and if so that would be worth checking out. > > > > Will French > > > > > -Original Message- > > > From: --- [mailto:[EMAIL PROTECTED]] > > > Sent: Sunday, September 30, 2001 12:53 PM > > > To: [EMAIL PROTECTED] > > > Subject: ms SQL server 7.0 -- > mysql? > > > > > > > > > > > > how can i transfer a database from sql server to mysql (linux) on a > > > stand-alone ws? > > > -- > > > "When you have eliminated the impossible, > > > whatever remains, however improbable, > > > must be the truth." > > > --Sherlock Holmes _The Sign of Four_ > > > > > > - > > > 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 > > ms sql server is in our school and since i want to learn mysql, i want > to copy the sammple databases from the school to my house. how do i do > that? from ms sql --> txt --> mysql? > > -- > "When you have eliminated the impossible, > whatever remains, however improbable, > must be the truth." > --Sherlock Holmes _The Sign of Four_ > - 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: ms SQL server 7.0 -- > mysql?
I'm not sure what you mean by "stand-alone". If you mean not connected to a network then I don't see how since ms-sql-server only runs on Windows and you have specified that your mysql server is linux. Unless you have a huge schema (lots of tables and/or lots of columns) I would suggest using the "generate sql script" command in sql-server enterprise manager and then editing the file as necessary for compatibility with mysql (e.g. changing bit fields to tinyint, etc). Once you have the database created in mysql, I would use a combination of the bcp utility (with the "/t" option which creates tab-delimited output) and the "load data infile" command to load it into mysql. It is possible that someone has created a more automated way of doing this and if so that would be worth checking out. Will French > -Original Message- > From: --- [mailto:[EMAIL PROTECTED]] > Sent: Sunday, September 30, 2001 12:53 PM > To: [EMAIL PROTECTED] > Subject: ms SQL server 7.0 -- > mysql? > > > > how can i transfer a database from sql server to mysql (linux) on a > stand-alone ws? > -- > "When you have eliminated the impossible, > whatever remains, however improbable, > must be the truth." > --Sherlock Holmes _The Sign of Four_ > > - > 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: Disk-bound joins
Thanks for responding. That's interesting. I will definitely give your sugestion a try. I thought the two syntaxs had the same effect. What does the optimizer do differently? > -Original Message- > From: Andrew Murphy [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 26, 2001 5:12 AM > To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] > Subject: RE: Disk-bound joins > > > Hi, > > Excuse me if this is wrong, but I think its the INNER JOIN which > is slowing > down the query. > > Wouldnt it be better (less memory intensive) to just use: >"FROM zipwork1 as z, pctwork1 as p WHERE z.run_id = p.run_id" > to join the tables if they are 1-1 related. > > > Andrew Murphy > > > -Original Message- > From: Will French [mailto:[EMAIL PROTECTED]] > Sent: 26 September 2001 7:23 am > To: [EMAIL PROTECTED] > Subject: Disk-bound joins > > > Hi all, > > I have two very large tables (4 million recs each) where the records from > each have a 1-to-1 relationship. They share a primary key (int) and there > are no unmatched records in either table (i.e. it is a true 1-to-1). I am > trying to combine a few columns from each of the two tables into > a new third > table. No matter what I do, the select statement process becomes > disk-bound > (low processor utilization despite no competition, tons of disk activity, > excrutiatingly slow). Why is this? Has anyone else encountered this kind > of trouble? > > I have gobs of memory (1.25Gb) and I am using the settings from > my-huge.cnf. > I have a reasonable processor - 733Mhz (not that its doing anything). I > have a ATA-100 IDE hard disk, on a dedicated controller completely devoted > to mysql. I am using mysql 3.23.41 on Win2k. > > My tables are as follows: > > CREATE TABLE zipwork1 ( > runid int not null primary key, > zip char(5), > plus4 char(4), > addrerr tinyint > ); /* This table has 4,014,438 records */ > > CREATE TABLE pctwork1 ( > runid int not null primary key, > cnty char(3) not null, > precinct char(3) not null, > cd tinyint, > sd tinyint, > hd tinyint > ); /* This table also has 4,014,438 records */ > > CREATE TABLE zippctmapwork ( > runid int not null, > zip char(5), > plus4 char(4), > addrerr tinyint, > cnty char(3), > precinct char(3), > cd tinyint, > sd tinyint, > hd tinyint > ); /* This is the table I am trying to populate (starts out empty) */ > > Here is my statement: > INSERT INTO zippctmapwork (runid, zip, plus4, addrerr, cnty, precinct, cd, > sd, hd) > SELECT z.runid, z.zip, z.plus4, z.addrerr, p.cnty, p.precinct, p.cd, p.sd, > p.hd > FROM zipwork1 AS z INNER JOIN pctwork1 as p ON z.runid = p.runid; > > This is taking almost 20 minutes to execute. 20 minutes is great compared > to when I try this on the 2 tables that zipwork1 and pctwork1 are > originated > from (zipwork1 and pctwork1 are subset tables I created to try and speed > this thing up). When I ran it on the original tables, I had to kill it > after 100 minutes and it had not even begun writing to the result > table yet. > > Anyone have any ideas... anyone? > > Thanks in advance for your help > > Will French > > > - > 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
Disk-bound joins
Hi all, I have two very large tables (4 million recs each) where the records from each have a 1-to-1 relationship. They share a primary key (int) and there are no unmatched records in either table (i.e. it is a true 1-to-1). I am trying to combine a few columns from each of the two tables into a new third table. No matter what I do, the select statement process becomes disk-bound (low processor utilization despite no competition, tons of disk activity, excrutiatingly slow). Why is this? Has anyone else encountered this kind of trouble? I have gobs of memory (1.25Gb) and I am using the settings from my-huge.cnf. I have a reasonable processor - 733Mhz (not that its doing anything). I have a ATA-100 IDE hard disk, on a dedicated controller completely devoted to mysql. I am using mysql 3.23.41 on Win2k. My tables are as follows: CREATE TABLE zipwork1 ( runid int not null primary key, zip char(5), plus4 char(4), addrerr tinyint ); /* This table has 4,014,438 records */ CREATE TABLE pctwork1 ( runid int not null primary key, cnty char(3) not null, precinct char(3) not null, cd tinyint, sd tinyint, hd tinyint ); /* This table also has 4,014,438 records */ CREATE TABLE zippctmapwork ( runid int not null, zip char(5), plus4 char(4), addrerr tinyint, cnty char(3), precinct char(3), cd tinyint, sd tinyint, hd tinyint ); /* This is the table I am trying to populate (starts out empty) */ Here is my statement: INSERT INTO zippctmapwork (runid, zip, plus4, addrerr, cnty, precinct, cd, sd, hd) SELECT z.runid, z.zip, z.plus4, z.addrerr, p.cnty, p.precinct, p.cd, p.sd, p.hd FROM zipwork1 AS z INNER JOIN pctwork1 as p ON z.runid = p.runid; This is taking almost 20 minutes to execute. 20 minutes is great compared to when I try this on the 2 tables that zipwork1 and pctwork1 are originated from (zipwork1 and pctwork1 are subset tables I created to try and speed this thing up). When I ran it on the original tables, I had to kill it after 100 minutes and it had not even begun writing to the result table yet. Anyone have any ideas... anyone? Thanks in advance for your help Will French - 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: Inserting Numeric Data with the C API
I searched pretty thoroughly for a way to access mysql data in their native field types (e.g. an int field I wouldn't have to use atoi()/itoa() on) and came up with nothing. If you are processing large amounts of data you may want to do what I did which was to make my program build a load file (and then load it, of course). I found this method had performance advantages. > -Original Message- > From: George Eric R Contr AFSPC/CVYZ > [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, September 25, 2001 3:00 PM > To: [EMAIL PROTECTED] > Subject: Inserting Numeric Data with the C API > > > Hi, > Is there anyway to do this other then copying a representation of the data > into the query string? > This just seems pretty inneffiecient (making an extra copy of the data) & > error prone (getting the representation right when you copy it to the > string). > Thanks > Eric > > - > 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: Update problem
> I think no-one's replied because the answer is really simple Oh I don't know Jay, I've seen some threads 4-5 responses deep to issues far simpler than this. > create a recordset using Select for the records you are interested > in (using your joins), then use that recordset to perform your > UPDATE or DELETE. I understand that you could use REPLACE with this newly created recordset to simulate the UPDATE w/JOIN, but your "it's so simple" response glosses over the details of what to do with the delete. The only way I've conceived is to carry an "isdeleted" field which is set using the replace command, then used in where clause of a DELETE. Of course I may be missing a simpler or better way to do this... which is why I made my original post. > I have to > deal with the same situation Phil describes quite often, but I've never > missed the 'join in update' function. Well I'm happy for you but did you ever stop and consider that what you do with your database may not necessarily be representative of what everyone does? In my situation, one that is certainly not unique, I have tables with 100+ fields and many million rows. The SELECT INTO/REPLACE FROM and SELECT INTO/REPLACE FROM/DELETE WHERE approaches to UPDATE FROM and DELETE FROM are an order of magnitude less efficient. This inefficiency coupled with the need (using myisam) to lock the entire table during updates makes for some not-so-simple design challenges. In closing, I offer a couple rhetorical questions: 1. Who is the bigger idiot... The person who asks a naive question or the person who, upon hearing a question he knows the answer to, assumes the asker is an idiot? 2. Does your condescending attitude win you as many points with your users as it did with me? Will French > -Original Message- > From: Jay Fesco [mailto:[EMAIL PROTECTED]] > Sent: Monday, September 24, 2001 8:30 AM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; > [EMAIL PROTECTED] > Subject: RE: Update problem > > > > > > imho, the fact that mysql does not support joins in UPDATE and DELETE > > statements is by far its greatest weakness. I have posted a couple of > > messages to this group to see what creative work-arounds others > > have used to > > fill this gaping hole in functionality. Alas, no one has felt > > like sharing > > their thoughts on this subject. > > > > Will (and Phil) > > I think no-one's replied because the answer is really simple - create a > recordset using Select for the records you are interested in (using your > joins), then use that recordset to perform your UPDATE or DELETE. > I have to > deal with the same situation Phil describes quite often, but I've never > missed the 'join in update' function. Not that it wouldn't be > nice, but the > workaround is not rocket surgery. > > Jay Fesco > > - 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: Update problem
In answer to your question - your statement does not work becuase mysql does not allow joins in update statements. imho, the fact that mysql does not support joins in UPDATE and DELETE statements is by far its greatest weakness. I have posted a couple of messages to this group to see what creative work-arounds others have used to fill this gaping hole in functionality. Alas, no one has felt like sharing their thoughts on this subject. > -Original Message- > From: Philip Montgomery [mailto:[EMAIL PROTECTED]] > Sent: Sunday, September 23, 2001 10:35 PM > To: [EMAIL PROTECTED] > Subject: Update problem > > > I am having problems getting an update to work correctly. Assume > that I have two tables, employee and office. Employee has two > fields, employee_id and employee_name, and office has three > fields, office_id, office_number, & employee_id. > > I have been using inner join to link the tables together when > pulling information out of the database, but I can't get join to > work with update. > > Say I want to update the office_number for a particular employee. > I would have to get the employee_id from the employee table by > referencing the employee_name in order to update the row with the > corresponding employee_id in the office table. > > Thus far I have been trying statements like this: > > update office inner join employee using (employee_id) set > office_number="XXX" where employee_name="XX"; > > I know that I could work around this via Perl, but I would rather > have mysql do the work. > > Any help would be appreciated, > > Thanks, > > Phil > > > Get 250 color business cards for FREE! > http://businesscards.lycos.com/vp/fastpath/ > > - > 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: Creating Databases
rtfm. Excerpt from mySQL manual. = 6.5.1 CREATE DATABASE Syntax CREATE DATABASE [IF NOT EXISTS] db_name CREATE DATABASE creates a database with the given name. Rules for allowable database names are given in Section 6.1.2 [Legal names], page 358. An error occurs if the database already exists and you didn't specify IF NOT EXISTS. Databases in MySQL are implemented as directories containing files that correspond to tables in the database. Because there are no tables in a database when it is initially created, the CREATE DATABASE statement only creates a directory under the MySQL data directory. You can also create databases with mysqladmin. See Section 4.8 [Client-Side Scripts], page 279. = > -Original Message- > From: Rosyna [mailto:[EMAIL PROTECTED]] > Sent: Sunday, September 23, 2001 8:55 PM > To: [EMAIL PROTECTED] > Subject: Creating Databases > > > Does mySQL not support this syntax? > > CREATE DATABASE newDB ON > ( > NAME = 'newDB', > FILENAME = '~/newDB.mdf', > SIZE = 10MB, > MAXSIZE = 20MB, > FILEGROWTH = 1MB ) > -- > Sincerely, > Rosyna Keller > Technical Support/Holy Knight/Always needs a hug > > Unsanity: Unsane Tools for Insane People > > - > 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: Newbie - PWS & SQL. Some VSQs (Very Silly Questions)
I suggest downloading/installing the MySql ADO library (available from a link off mysql.com downloads). Then you can write ASP pages (using ado objects) to interact with your mysql database. Before my suggestion catches the ire of all the php/linux snobs, I must point out that our friend wants to use PWS (i.e. IIS-Lite). I say when in Redmond do as the Redmondites do... and our friend is definitely in Redmond! I must also point out that since you don't seem to have much invested in PWS, perhaps you might consider whether another web server would better suite your needs. You could, for instance, download Apache and PHP and take a different road altogether. One advantage to this would be that you will certainly find more examples of people using these technologies with mysql than you will ado/asp. In either event, you should definitely look for a less restrictive hosting arrangement. Good luck. Will French > -Original Message- > From: Iain Lang [mailto:[EMAIL PROTECTED]] > Sent: Sunday, September 23, 2001 12:34 PM > To: [EMAIL PROTECTED] > Subject: Newbie - PWS & SQL. Some VSQs (Very Silly Questions) > > > . > Dear List-Members, > > As I am EXTREMELY new to mysql and my connection to my ISP is a > pay-per-minute one, doing the endless testing (koff, kofff > Ok, farffing > around, getting lost, tired and emotional) with mysql can cost some dosh > over time. I've only recently arrived at this work-station machine and > found that the previous operator has Microsoft's Personal Web Server and > mysql on it. She doesn't seem to have *done* anything with them, but > recent magazine articles on mysql have whetted my interest. > So. > Is there a step-by-step guide to installing/linking mysql - or > whatever it > is I have to do - onto the PWS to allow me to experiment locally? I date > from the days of CP/M and DOS 3.2 and command lines, but have no > experience > of Linux/Unix. On the strength of various recommendations I > read, I bought > Mr duBois' book; I commend its jargon-free approach but it > doesn't seem to > mention PWS. > > After reading bits of http://www.mysql.com/articles/ddws/3.html I > looked in > my c:\mysql\bin and there are an *awful* lot of .exe files. When I go to > my hosting site http://www.pay-as-you-host.com/help/howto/sql > and load the > tbl_FILENAMES.php files I've ftp-ed up to it from this machine, > mysql seems > to run all right - it certainly invites me to try all things. > > As there are three hard discs on this machine, "Be there dragons?" on > operating across them - there seem to be some fairly heavy warnings > everywhere on running mysql from anything other than its > installed c:\ drive. > > Round about here is where I run out of intelligent questions (that is, if > the above were intelligent). > > In the usual way of things, after a couple of weeks' use and > experimenting, > I expect I'll cringe at the naive content of this message. > > Yors, > > Iain Lang. >To Keep Well, > You Must Sleep Well! > http://www.divinebedding.co.uk > > > - > 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: Not working mysql in win2000 Prof.
The message you are getting indicates that it cannot find the executable file. You are getting this because you are not putting a space between the executable name "mysqld" and your command-line option "--standalone". Unless you have some reason to not want to run mysql as a service, I reccommend using the mysqladmin program. > -Original Message- > From: umesh kumar chaudhary [mailto:[EMAIL PROTECTED]] > Sent: Sunday, September 23, 2001 12:58 AM > To: [EMAIL PROTECTED] > Subject: Not working mysql in win2000 Prof. > > > Dear Sir, > I have window 2000 Professional Operating System. > Ihad installed PHP, Apache Web Server and MySql.PHP > worksvery good with apache. MySql is installed in by > deafaul directory that is "c:\mysql" but MySql does > not work.I aslso create the file "c:\MY.CNF" with > document of My-example.cnf. > When I give the command in command prompt like: > C:\mysql\bin\mysqld--standalone > it gives the message: > 'mysqld' is not recognized as an internal or external > command. > so, Please help me sir to solve tis type of > problems. > Thank U > umesh > > __ > Do You Yahoo!? > Get email alerts & NEW webcam video instant messaging with Yahoo! > Messenger. http://im.yahoo.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 > > - 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: Backup Data
I have experimented only with replication. All of my experimentation has been on two machines on the same physical network. I quickly determined that replication was not going to be a good solution for what I wanted: An always available inquiry server (free from lock conflicts caused by long running updates). This proved not to work in my tests because it seems that what is logged (and therefore replicated) is only the query which performed the update which takes just as long to run on the replicated server as it did on the master (causing the same blocking problems). This together with the lengthy "known problems with replication" section of the manual and the frequent "replication problem" postings to this group has left me with the impression that this feature is not exactly ready for prime-time. I think your 2-way replication idea is a non-starter. I believe that the manual is very clear that mysql provides one-way only replication. I don't believe that it is even possible to configure a server as both a master and a slave. Even if you could, it seems likely that you would end up with an infinite loop. > -Original Message- > From: Neil Tompkins [mailto:[EMAIL PROTECTED]] > Sent: Saturday, September 22, 2001 1:49 PM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: Backup Data > > > Thanks for your reply. What about replication ? I've looked at this, but > everyone says read the manual. I would like to hear from someone who has > had experience with this and does it work correctly. > > For example has anyone had a database online with a ISP and > updated a local > copy locally with any changes being replicated to the ISPs copy. > Also, has > anyone made changes online and then replicate these changes back to the > local copy e.g two way replication > > Thanks > Neil > > - Original Message - > From: "Will French" <[EMAIL PROTECTED]> > To: "Neil Tompkins" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Saturday, September 22, 2001 6:45 PM > Subject: RE: Backup Data > > > > There are two methods that I commonly use to move data from mySql into > > Access. > > > > The first is to use a myODBC connection and simply import the tables. > Given > > the size of your database (small that is) this would be quite quick > assuming > > that you are able to establish an ODBC connection to your > database accross > > the net (I bet you can). If you haven't already, you should dl and > install > > myODBC. Be warned though, if your database is large (many of my tables > have > > > 1 million records) this method of import can be quite slow (even for > > Access). > > > > The second method is select your files into "OUTFILE"s and then import > them > > into Access using the text import wizard. The select statement > would look > > something like this: > > SELECT * INTO OUTFILE '' > > FROM > > Note: If your table contains binary data, you won't be able to use this > > method. > > > > The second method is generally quicker but has a couple of downsides: > > - It will not create your table definition for you! You will have to > > either: > > 1. pre-define your table and then import into it > > 2. define your table through the import-text wizard (see the advanced > > button) > > 3. import it and then go back and massage the field definitions. > > - For options 2 and 3 be carefull! Access only scans so many rows (50?) > to > > decide what type to assign an import field. This often leads to import > > errors. Also note that Access will always default text fields > to a length > > of 255 which may cause unnecessary overhead if your fields are in fact > > smaller (and most, of course, are). > > > > Because Access' table definition is pretty time consuming to use, I > > sometimes first do a "SHOW COLUMNS" into a text file and then > use EXCEL to > > massage that result set into a CREATE TABLE DDL query (many people don't > > know that Access supports DDL queries). After I have the table > set up, I > > import into that table. > > > > > -Original Message- > > > From: Neil Tompkins [mailto:[EMAIL PROTECTED]] > > > Sent: Saturday, September 22, 2001 1:11 PM > > > To: [EMAIL PROTECTED] > > > Subject: Backup Data > > > > > > > > > Hello, > > > > > > I currently have about 2500 records in a mySQL database that is > > > hosted with > > > my ISP. I am very familar with MS Access. Is it possible that I > &g
RE: Backup Data
There are two methods that I commonly use to move data from mySql into Access. The first is to use a myODBC connection and simply import the tables. Given the size of your database (small that is) this would be quite quick assuming that you are able to establish an ODBC connection to your database accross the net (I bet you can). If you haven't already, you should dl and install myODBC. Be warned though, if your database is large (many of my tables have > 1 million records) this method of import can be quite slow (even for Access). The second method is select your files into "OUTFILE"s and then import them into Access using the text import wizard. The select statement would look something like this: SELECT * INTO OUTFILE '' FROM Note: If your table contains binary data, you won't be able to use this method. The second method is generally quicker but has a couple of downsides: - It will not create your table definition for you! You will have to either: 1. pre-define your table and then import into it 2. define your table through the import-text wizard (see the advanced button) 3. import it and then go back and massage the field definitions. - For options 2 and 3 be carefull! Access only scans so many rows (50?) to decide what type to assign an import field. This often leads to import errors. Also note that Access will always default text fields to a length of 255 which may cause unnecessary overhead if your fields are in fact smaller (and most, of course, are). Because Access' table definition is pretty time consuming to use, I sometimes first do a "SHOW COLUMNS" into a text file and then use EXCEL to massage that result set into a CREATE TABLE DDL query (many people don't know that Access supports DDL queries). After I have the table set up, I import into that table. > -Original Message- > From: Neil Tompkins [mailto:[EMAIL PROTECTED]] > Sent: Saturday, September 22, 2001 1:11 PM > To: [EMAIL PROTECTED] > Subject: Backup Data > > > Hello, > > I currently have about 2500 records in a mySQL database that is > hosted with > my ISP. I am very familar with MS Access. Is it possible that I > can backup > my data into a MS Access database ? Any advice would be very grateful ! > > Thanks > > Neil > www.advertising-online.net > > - > 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: Mysql as backend for email ?!?
Rich, I do recall that there was a mail program that was written in Perl that utilized mySQL as the backend for storing the message. It was reviewed in Linux Journal. I still have the CD on my desk but haven't had a chance to install yet so I cannot give you a personal review, but the write ups in the article were fairly positive. A search of 'Linux Journal' archives may be productive as well as everyone favorite suggestion, Google. HTH When I get home tonight, I'll look for that CD and either(at your discretion) email you the name or email you the rpm if you desire/have the bandwidth for it. Let me know, Will -Original Message- From: Rich Duzenbury [mailto:[EMAIL PROTECTED]] Sent: Friday, September 21, 2001 1:35 PM To: Christopher Bergeron; [EMAIL PROTECTED] Subject: Re: Mysql as backend for email ?!? I'm a little fuzzy on this, but I seem to recall that a .forward file can call a program (perl script?) of your choosing for each mail message. Or, perhaps procmail can do this task? Regards, Rich At 01:05 PM 9/21/01, Christopher Bergeron wrote: >Does anyone know of any applications that insert email into mysql databases? >I can use either Windows or Linux as the client; however, I want mysql >server on the linux box (for stability). I don't like having all of my >email in MSOutlook's proprietary format because in 5 years when I need to >look back at something, I'll need a sql db search, not ms-outlooks little >search algorithm (I get a lot of email daily). > >Anyone have any suggestions or recommendations?? - 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: MySQL Update Logging ???
Actually yes (if I read the documentation correctly - I have not tried this). In MySql Manual section "4.10.5 Replication Options in my.cnf", I find: binlog-do-db=database_name Tells the master it should log updates for the specified database, and exclude all others not explicitly mentioned. (Example: binlog-do-db=some_database) > -Original Message- > From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] > Sent: Friday, September 21, 2001 1:34 AM > To: Christopher Raymond > Cc: MySQL Mailing List > Subject: Re: MySQL Update Logging ??? > > > On Thu, Sep 20, 2001 at 04:25:29PM -0500, Christopher Raymond wrote: > > > > Friends: > > > > My ISP does not enable update logging with their MySQL server. Is it > > possible for them or me to turn on update logging just for MY > database(s) > > ??? > > No. > > Jeremy > > - > 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: Select dates from Table 1 not in Table 2
Try this: CREATE TEMPORARY TABLE tmp_avldate (date datetime, nadate datetime); INSERT INTO tmp_avldate (date, nadate) SELECT DISTINCT schedule.date, nonavailable.date FROMschedule LEFT JOIN nonavailable ON schedule.date = nonavailable.date; SELECT date FROMtmp_avldate WHERE nadate is NULL; DROP TABLE tmp_avldate; Regrettably, the fact that it takes 4 steps may not be as simple as you would like... but with only a max of 200 output records, it should be plenty fast. > -Original Message- > From: Adam Todd [mailto:[EMAIL PROTECTED]] > Sent: Friday, September 21, 2001 12:29 AM > To: [EMAIL PROTECTED] > Subject: Select dates from Table 1 not in Table 2 > > > Hi all, just subscribed. Normally I work these out for myself, but time > and frustration are winning. > > I have two tables: > > SCHEDULE which contains a column called "date" and in that are DATETIME > entries from 2001-09-14 to who cares. > > NONAVAIALBLE which contains users and dates they aren't available in a > column called 'nonavaildate' > > I needed to create 'schedule' because I can't find an easy way to > generate > a list of dates 200 days long without going to external scripts, which is > fine on my unix systems, but I have windows systems too. (sigh) It also > means I've got an indexed reference point that I can use for other things > anyway. > > What I need to do is list dates in ranges, say for example between > 2001-11-01 and 2001-11-10 that have no comparable entries in nonavailable. > > I've tried all kinds of things and given up to ask the wider audience out > there! Any ideas welcome, but it must be done within SQL, preferably as > generic as possible as I have that mixed platform issue. > > My last attempt was: > > SELECT date >FROM schedule, nonavailable > WHERE registrationid=30 #don't worry about that > AND date >= "2001-11-01" and date <= "2001-11-10" > AND nonavaildate >= "2001-11-01" and nonavaildate <= "2001-11-10" > AND date <> nonavailadate > GROUP BY date > ORDER BY date > > It unfortunately dumps a list of dates from 1st to 10th :) > > If I: SELECT count(date), date > > I get a result of dates but the count varies according to which dates are > not in table2 (nonavailable). > > Any suggestions, hints, ideas. The simpler the better. > > > > - > 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: synchronization of database between two mysql servers
Ok, an accidental double posting I can understand... but this is the 4th time in an hour that you have posted the same message... Chill out! > -Original Message- > From: phani krishna [mailto:[EMAIL PROTECTED]] > Sent: Thursday, September 20, 2001 2:42 PM > To: [EMAIL PROTECTED] > Subject: synchronization of database between two mysql servers > > > hi all, > is there any way such that the data(any alteration of > the data like update , insert , delete etc) on server1 > can synchroinize with the data on server2 in MYSQL > database. > > i am having two linux servers with MYSQL running on > both. i want to use one linux server as back up.so i > need to update the 2nd server with the data on the > first server.is there any way. > > IS it possible if i make use of replcation > conce[pt.please guide me how to approach to this > problem.Highly favourable if u suggest me good link > for cleariong my doubt. > please mail me to [EMAIL PROTECTED] or > [EMAIL PROTECTED] > > waiting for the replies. > rgds, > phani > > > = > S.V.PHANI KRISHNA > STESALIT LIMITED > 10,BELVEDERE ROAD, > ALIPORE KOLKATTA - 700027 > PHONE NUMBERS 4498346,4498345,4498342 > MAIL: [EMAIL PROTECTED],[EMAIL PROTECTED] > > > Do You Yahoo!? > Send a newsletter, share photos & files, conduct polls, organize > chat events. Visit http://in.groups.yahoo.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 > > - 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: Semi colons in text fields in update statement
Yes I see your point, Paul. If the bug is in fact in ado or in the oledb
driver for mysql, then I think the solution I outlined below using the
CONCAT and CHAR functions may still apply if only as a workaround to the
presumed ado/oledb bug.
On a related note:
Jay Fesco wrote:
> Paul et al - I think his problem is with quotes, not semicolons. The line
> "Update tblCompany SET CoName='"\;tester"\;' WHERE
> CoID=109" is what
> leads me to believe this.
I don't believe that the quotes are a problem at all. I have used VB/ADO a
lot and I routinely do this (albeit not with the mysql oledb driver). If
you mean the double-quotes surronding the statement - I believe that this
has been cut and pasted from his vb source, the complete line looking
something like this:
dim szSQL as String
szSQL = "UPDATE tblCompany SET CoName='"\;tester"\;' WHERE CoID=109"
If I am correct in this assumption then obviously only what is between the
double quotes would reach the query string buffer. The single quotes need
not be escaped for VB's sake as single-quotes are not significant in VB.
Therefore if he escaped the single quotes, mysql would certainly get
confused because it would not be expecting escape sequences outside of a
quoted literal... right?
I think Paul is probably on the right track in suspecting an ado related
bug - one that probably scans the query string for a semicolon and does not
properly account for its being embeded in a literal string.
> -Original Message-
> From: Paul DuBois [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 20, 2001 2:15 PM
> To: [EMAIL PROTECTED]; johnlucas-Arluna; [EMAIL PROTECTED]
> Subject: RE: Semi colons in text fields in update statement
>
>
> At 2:00 PM -0400 9/20/01, Will French wrote:
> >There probably is a better way but the following should work:
> >
> >"UPDATE tblCompany SET CoName=CONCAT('"', CHAR(59),
> 'tester', CHAR(59),
> >'"', CHAR(59)) WHERE CoID=109"
> >
> >Like you, I believe there probably is an escape sequence for the
> semi-colon
> >but I'll be damned if I can find it documented. Until someone else
> >enlightens us both, hopefully the above solution will see you through.
>
> There is no need for such an escape sequence because semicolons
> in strings are not special. Maybe it's an ADO bug.
>
> >
> >> -Original Message-
> >> From: johnlucas-Arluna [mailto:[EMAIL PROTECTED]]
> >> Sent: Thursday, September 20, 2001 1:45 PM
> >> To: [EMAIL PROTECTED]
> >> Subject: Semi colons in text fields in update statement
> >>
> >>
> >> Hello
> >>
> >> I'm trying to do the following:
> >>
> >> "Update tblCompany SET CoName='"tester"' WHERE CoID=109"
> >>
> >> But getting an error message
> >>
> >> I tried the escape character "\" like
> >>
> >> "Update tblCompany SET CoName='"\;tester"\;' WHERE CoID=109"
> >>
> >> but get the same error
> >>
> >> This happens whether I use ADODB in VB or if I try executing the query
> >> through DBTools 1.0.9
> >>
> >> Can anybody tell me how I can escape the semi-colon please?
> >>
> >> Many thanks for any help
> >>
> > > John
> > >
>
>
> --
> Paul DuBois, [EMAIL PROTECTED]
>
> -
> 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: Semi colons in text fields in update statement
There probably is a better way but the following should work:
"UPDATE tblCompany SET CoName=CONCAT('"', CHAR(59), 'tester', CHAR(59),
'"', CHAR(59)) WHERE CoID=109"
Like you, I believe there probably is an escape sequence for the semi-colon
but I'll be damned if I can find it documented. Until someone else
enlightens us both, hopefully the above solution will see you through.
> -Original Message-
> From: johnlucas-Arluna [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 20, 2001 1:45 PM
> To: [EMAIL PROTECTED]
> Subject: Semi colons in text fields in update statement
>
>
> Hello
>
> I'm trying to do the following:
>
> "Update tblCompany SET CoName='"tester"' WHERE CoID=109"
>
> But getting an error message
>
> I tried the escape character "\" like
>
> "Update tblCompany SET CoName='"\;tester"\;' WHERE CoID=109"
>
> but get the same error
>
> This happens whether I use ADODB in VB or if I try executing the query
> through DBTools 1.0.9
>
> Can anybody tell me how I can escape the semi-colon please?
>
> Many thanks for any help
>
> John
>
>
> -
> 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
