Fwd: Comma Delimited Import Error
On Jun 5, 2004, at 11:40 PM, David Blomstrom wrote: Here are a few rows from the comma delimited file I imported. Do you have a hunch what might be going on here? Thanks. gd,oat,239,,Grenada,nat gp,oat,240,,Guadeloupe,dep ht,oat,241,,Haiti,nat is,oat,242,,Iceland,nat jm,oat,243,,Jamaica,nat Do you see where you have two commas together such as 239,,Grenada If the field that lines up with the field between ,, does not allow NULL, then an error will occur. Either redefine the field to allow NULL, or use regex on that file to convert ,, to ,, or ,0, as appropriate. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
API Changes from 4.0 to 4.1?
I'm a little out of my realm in asking this on behalf of someone else, but here goes... I finally got 4.1.2 working for me on OS X 10.3.4, but have a curious performance snag. If I connect to MySQL 4.1 via any one of three different GUI Control Center type apps, all works fine. If I connect to 4.1 through Lasso (v6.0.6) middleware, queries are taking a minimum of 5x longer to execute compared to using 4.0.x on that exact same box setup. I have tested with only one MySQL version on the machine at a time. I totally rip one out then install the other. Stock install for each (no my.cnf file). I've repeated this swapping process 2x. There is no difference in speed when using GUI clients like CocoaMySQL, MacSQL, and Navicat, so it's not just 4.1.2 having a general problem. In all cases I am connecting using an old style password for compatibility. Ultimately I'm wondering if there have been changes to the API one would use to build a connector to MySQL with. It sure seems to me that the trouble lies with the Lasso connector, but have there been changes to the MySQL API that could explain that? Both the CocoaMySQL and MacSQL apps I use are old as well, yet they have no problem. BlueWorld doesn't want to bother looking into this because 4.1 is still alpha. So, I'm hoping I can get some indicators here that it is likely the Lasso connector needs updated with some hints as to what to look for at the API level. Maybe this is a question better suited to the PlusPlus list? -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New 4.1.2 Startup Trouble
I've installed mysql 3 4 a dozen times or more, but I'm having trouble with 4.1.2 on OS X Server (10.3.4). This is a new install, no previous mysql (never did fire up the built-in mysql on this particular box). The /data/{host}.err file shows the following after a machine reboot which should start mysql up with the /mysqlcom/mysql script 040601 11:50:23 mysqld started 040601 11:50:23 Warning: Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive InnoDB: unable to create ./innodb.status.16182: Permission denied 040601 11:50:23 Can't init databases 040601 11:50:23 Aborting 040601 11:50:23 /usr/local/mysql/bin/mysqld: Shutdown Complete 040601 11:50:23 mysqld ended Trying to use the script manually (which I've done plenty before with 3.x and 4.0) I get this: 040601 11:50:23 mysqld started 040601 11:50:23 Warning: Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive InnoDB: unable to create ./innodb.status.16182: Permission denied 040601 11:50:23 Can't init databases 040601 11:50:23 Aborting 040601 11:50:23 /usr/local/mysql/bin/mysqld: Shutdown Complete 040601 11:50:23 mysqld ended Can't figure out which permission is failing. I've looked at folder and file perms of the startup script and they match those on systems with 4.0 that are working. Same with the /data directory. Baffled. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug? MySQL 4.1.2 and OS X 10.3.4
This was New 4.1.2 Startup Trouble. The new title seemed more appropriate given my testing. Having installation problems with 4.1.2 on OS X 10.3.4. From previous thread: I've installed mysql 3 4 a dozen times or more, but I'm having trouble with 4.1.2 on OS X Server (10.3.4). Maybe it's not a MySQL problem -- I just happened to read: http://www.eweek.com/article2/0,1759,1603685,00.asp? kc=ewnws060104dtx1k0100599 ...about problems with 10.3.4 :-) With every update someone gripes about something. I can't rule it out, but I've been through upgrades since the beginning of time on several machines and never had any trouble. I run very clean systems and use mainstream devices. I run the repair permissions utility as well. Everything else in my system is running fine. Allowing that it could be 10.3.4's problem, I conducted the following installations on lab rat machines I keep: //-- TEST_1a: MySQL 4.1.2 on 10.3.4 Server - G4/DP500 - 1.25Gb RAM, - 10.3.4 Server - built-in MySQL completely removed Brand new installation of 4.1.2 results in some odd permissions problems that appear to escalate the errors. The /data/ directory is being created with a user of system instead of mysql (which all my other installs have). The /mysql/ database is not being built. The folder exists but is empty. This folder also has system instead of mysql as the user which could be preventing the installer script from finishing its tasks? This test was repeated with download files from three different locations. //-- TEST_1b: MySQL 4.0.20 on 10.3.4 Server Removing 4.1.2 from the same machine used in Test_1, I installed 4.0.20 with the same 10.3.4 Server OS. This installation went w/o hitch. Server and client work fine. //-- TEST_2a: MySQL 4.1.2 on 10.3.2 Standard - iMac G3/333 - 384 Mb RAM, - 10.3.2 Standard - all other prior MySQLs removed For more data, I installed 4.1.2 onto an iMac 333 (10.3.2). That installation actually went fine. Forgot to start things up, but the files were installed with proper mysql user permissions, and the /mysql/ database had all its tables. //-- TEST_2b: MySQL 4.1.2 on 10.3.4 Standard I then deleted mysql from the system, upgraded that iMac from 10.3.2 to 10.3.4, ran Repair Permissions, and installed 4.1.2 again. That installation exhibited the exact problems as Test_1 above. The /data/ directory has system as the user, and the /mysql database folder exists (with the user set to system), but there are no table files. //-- TEST_2c: MySQL 4.0.20 on 10.3.4 Standard I removed 4.2.1 and installed 4.0.20 on the iMac w/ 10.3.4 and mysql server and client worked fine. //-- So, - G4/OS X Server: 4.0.20 installs fine, but 4.2.1 does not on 10.3.4 - G3/OS X Standard: 4.2.1 installs fine on 10.3.2, but after 10.3.4 Standard was installed, 4.2.1 did not work, yet 4.0.20 continues to install fine The fact that 4.0.20 will install just fine on 10.3.4 and that 4.2.1 installed fine on 10.3.2 seems to indicate a specific installer problem between 4.2.1 and OS X 10.3.4 (server standard makes no difference). Whether it is the installer or 10.3.4 I can't decipher from this data set. It is worth mentioning that I installed 4.2.1, then set the users to mysql instead of system, then tried to update that installation. The installer reset the user to system on variouis folders, and still wouldn't install all the table files in the mysql database. This was all done with the OS X Installer package. I guess I'll try the non-Installer version and see what happens. Should I copy Bugs on this? -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Short Passwords in 4.1.2
I'm trying to force 4.1.2 to use the old short passwords for now during some experimental stages. The discussion here (specifically the fourth set of bullets): http://dev.mysql.com/doc/mysql/en/Password_hashing.html and, this paragraph: The Password column must be wide enough to hold long hashes (41 bytes). If the column has not been updated and still has the pre-4.1 width of 16 bytes, the server notices that long hashes cannot fit into it and generates only short hashes when a client performs password-changing operations using PASSWORD(), GRANT, or SET PASSWORD. This is the behavior that occurs if you have upgraded to 4.1 but have not yet run the mysql_fix_privilege_tables script to widen the Password column. led me to think that I could modify the user table and set the width of the Password column to varchar(16) and based on the narrow column 4.1.2 would always default to creating the old passwords. However, GRANT statements are creating passwords that start with * so, even though they're chopped off at 16, they're obviously still the new format. - Sidebar: interesting--mysql 4.0 creates host, user, password columns as binary but 4.1.2 does not? - Can 4.1.2 be forced to used old passwords this way? Do I need to modify the startup script to include --old-passwords? -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug? MySQL 4.1.2 and OS X 10.3.4
On Jun 1, 2004, at 5:04 PM, Greg Willits wrote: So, - G4/OS X Server: 4.0.20 installs fine, but 4.2.1 does not on 10.3.4 - G3/OS X Standard: 4.2.1 installs fine on 10.3.2, but after 10.3.4 Standard was installed, 4.2.1 did not work, yet 4.0.20 continues to install fine The fact that 4.0.20 will install just fine on 10.3.4 and that 4.2.1 installed fine on 10.3.2 seems to indicate a specific installer problem between 4.2.1 and OS X 10.3.4 (server standard makes no difference). Whether it is the installer or 10.3.4 I can't decipher from this data set. This was all done with the OS X Installer package. I guess I'll try the non-Installer version and see what happens. Followup: I tried a couple more things with the .dmg installer, and still can't get it to work. I really think there's a problem with the installer. However, I was able to figure out how to use the regular unix-like install sequence with the .tar file, and everything works. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Are Views Supported?
On May 27, 2004, at 11:16 PM, Jake Johnson wrote: I was wondering when or will views be supported? http://dev.mysql.com/doc/mysql/en/ANSI_diff_Views.html -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is MySQL.com using MnoGoSearch for searching its site???
On May 27, 2004, at 10:17 PM, mos wrote: As most people already know, MySQL has FullText indexing built into it, so why is mysql.com using MnoGoSearch? (There is an icon Powered by MnoGoSearch on the search page.) 1) Is there something wrong with MySQL's FullText search for handling a lot of data? 2) Is MnoGoSearch better? 3) Why isn't MySQL using their own Full Text search engine? The two searches are unrelated. MnoGoSearch searches the entire web site like atomz or other such site indexing tools -- it searches the net result of the pages of the site which may contain many static components not contained in a MySQL database that the FullText search would never see. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECTing to Substitute Text for Refc Codes
Got an offline answer, so I'm all set. Gotta love SQL. for posterity... Try: SELECT cn.name, cd1.desc ,cd2.desc FROM contact AS cn, codes AS cd1 codes AS cd2 WHERE cn.city = cd1.code AND cn.state = cd2.code given: CREATE TABLE `codes` ( `id` int(11) NOT NULL auto_increment, `code` varchar(16) default NULL, `desc` varchar(64) default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM; CREATE TABLE `contact` ( `id` int(11) NOT NULL auto_increment, `name` varchar(32) default NULL, `city` varchar(16) default NULL, `state` varchar(16) default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM; -- gw On May 24, 2004, at 5:12 PM, Greg Willits wrote: Struggling with a query structure new to me... Most of my apps are not very complex at all (simple joins, few functions), but I've worked through a few tricky queries. However, this one is stumping me on how to even begin. I can't even decide what kind of query it is. As you can I wasn't even sure what to call it :-( I'll distill it to a structural example. Two tables: first table (contacts) has basic contact stuff (Name, city, State), second table (syscodes) is a arbitrary collection of codes and descriptions (code, desc). The contacts table uses codes from the sysycodes table instead of actual text for stuff like city and state. So, the contacts table might look like: Will Doolittle, 1543, 425 Robin Banks, 1800, 12897 And, the syscodes table is 425, California 1543, Anaheim 1800, Topeka 12897, Kansas In my realm this is a sadistic level of normalization, but I suppose it is pretty common and must be useful at some scale (I'm getting data from a large U.S. county). Either I'm thinking too hard, or really don't get it, but I don't know how to pull those together to get: Will Doolittle, Anaheim, California Robin Banks, Topeka, Kansas So starting with the basics: SELECT cntcName, cntcCity, cntcState FROM contacts, syscodes WHERE cntcID=x AND cntcCity=code AND cntcState=code (which I know is wrong) But how to connect which row from syscodes goes to which cntc field? The only way I can seem to visualize this is as a bunch of substitutions to alias with AS in the select phrase, but I don't see how. Unfortunately, this has to be compatible with 3.23.54. (when 4.1 hits beta then I will likely switch to it for this particular app as I need sub-selects for another task). All clues welcomed. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cannot get into mysql console
On May 25, 2004, at 4:00 AM, Enda McGahern - Ireland wrote: I have just inherited a ticketing system that uses MySql from an employee that just left the company and I am a newbie to mysql. I cannot seem to be able to get into the mysql console to do anything. I have tried commands such as mysql -q etc and no joy. I get a command not found when entering the mysql command. I am logged in as root. mysql is installed on usr/local/mysql. Is there a way to figure out how it was set up and what the parameters would be to get into the console. The previous person may have set it up to log in as mysqladmin or something but how can I find this out. % cd /usr/local/mysql/bin/ % ./mysql -u root -p % [Password:] {enter the user password} root is MySQL's root user, not the OS root user. There may be another user you have nm pw info for. http://dev.mysql.com/doc/mysql/en/Connecting-disconnecting.html http://dev.mysql.com/doc/mysql/en/Default_privileges.html http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECTing to Substitute Text for Refc Codes
Struggling with a query structure new to me... Most of my apps are not very complex at all (simple joins, few functions), but I've worked through a few tricky queries. However, this one is stumping me on how to even begin. I can't even decide what kind of query it is. As you can I wasn't even sure what to call it :-( I'll distill it to a structural example. Two tables: first table (contacts) has basic contact stuff (Name, city, State), second table (syscodes) is a arbitrary collection of codes and descriptions (code, desc). The contacts table uses codes from the sysycodes table instead of actual text for stuff like city and state. So, the contacts table might look like: Will Doolittle, 1543, 425 Robin Banks, 1800, 12897 And, the syscodes table is 425, California 1543, Anaheim 1800, Topeka 12897, Kansas In my realm this is a sadistic level of normalization, but I suppose it is pretty common and must be useful at some scale (I'm getting data from a large U.S. county). Either I'm thinking too hard, or really don't get it, but I don't know how to pull those together to get: Will Doolittle, Anaheim, California Robin Banks, Topeka, Kansas So starting with the basics: SELECT cntcName, cntcCity, cntcState FROM contacts, syscodes WHERE cntcID=x AND cntcCity=code AND cntcState=code (which I know is wrong) But how to connect which row from syscodes goes to which cntc field? The only way I can seem to visualize this is as a bunch of substitutions to alias with AS in the select phrase, but I don't see how. Unfortunately, this has to be compatible with 3.23.54. (when 4.1 hits beta then I will likely switch to it for this particular app as I need sub-selects for another task). All clues welcomed. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installing on OS X without installer
On May 23, 2004, at 3:30 PM, Chris Curnow wrote: I've just downloaded the latest version of MySQL (4.0.20) for OS X. They seem to have omitted the installer - there's no .dmg file to download. The manual only covers installing from the .dmg for OS X. I think you must have just missed it. There's been an installer one for a while now, and on is still listed here for OS X: http://dev.mysql.com/downloads/mysql/4.0.html -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mac Developer Question
On May 22, 2004, at 2:12 AM, John Mistler wrote: In the larger picture, I am wondering if there is a way to avoid all of this. Ideally, I would like for the application to come packaged with MySQL, to install MySQL automatically (not sure what happens if MySQL already exists on the system), and to never ask the user for a password. In fact, to just run MySQL in the background as if it were a part of my application. Any ideas on this? However, in the short term, in order to get it all working on my own computer I will need to get the above language correct. You should be looking into the embedded version of MySQL which is aimed exactly at these purposes. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Encryption
On May 21, 2004, at 1:54 AM, [EMAIL PROTECTED] wrote: What level of Encryption does MySQL have? I cannot find much security information from mysql.com. http://dev.mysql.com/doc/mysql/en/Encryption_functions.html -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Encryption
On May 21, 2004, at 5:19 AM, Victor Medina wrote: | On May 21, 2004, at 1:54 AM, [EMAIL PROTECTED] wrote: | | What level of Encryption does MySQL have? I cannot find much security | information from mysql.com. || | http://dev.mysql.com/doc/mysql/en/Encryption_functions.html | Any level of SSL provided by the underlying OpenSSL implementation of your system http://dev.mysql.com/doc/mysql/en/Secure_connections.html I haven't used it myself, just read about it. Use the search field on that page to find other sections in the docs. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Large sample data sets for testing
Anyone know of some large sample data sets available for free or $100? Large = maybe 1KB per row or so, 1,000,000 - 5,000,000 records or so, something on the order of a couple hundred megabytes. Sample Data = I don't really care what it is (text based though, no need for BLOB), just something to use for testing. Maybe an old news feed archive or something? I'm sure I could cope with just about any common exchange format. Always wanted to test the performance and scaling of some setups I have and things I've written, but I've never located a big (to me) data set to play with. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld sock conflicts
Greg Willits wrote: On May 20, 2004, at 11:08 AM, Sasha Pachev wrote: Greg Willits wrote: I have two mysql apps running on the same machine (OS X 10.3.3). A mysql 3.23.54 on port 14551, and a mysql 4.0.16 on 3306. Each has a config file specifying the port and a unique socket name in /tmp. They have coexisted just peachy for a very long time. Now however, w/o any changes to either MySQL3, MySQL4, or the OS, every time I issue a terminal command to one of the MySQL3 bin apps preceded by the usual cd /x/y/z/bin, the commands are being sent to the /usr/local/mysql bin apps on 3306. If I shut mysqld 3306 down (which closes the sock file), then any commands to mysql 14551 gripes that there is no socket file even though the one it should be using is still available. Do not worry about why it stopped working - it was not supposed to anyway, and if it did, it was pure luck :-) I was lucky for over two years across several machines then. I think that may be my best streak of anything ever. Too bad there was no money involved! ;-) A clean way to solve the problem would be to create small shell scripts called mysql-3 and mysql-4 that will connect to the right instance. I've started that process, though I'm not much of a shell scripter yet. So it's time to dig in or get used to specifying the socket I guess. OK. Well, at least I know. On May 21, 2004, at 12:54 PM, Sasha Pachev wrote: You do not need to be much of a shell scripter - it is just one line for each script: put in /usr/local/bin/mysql-3: start--- #! /bin/sh mysql --socket=/tmp/mysql-3.sock end--- put in /usr/local/bin/mysql-4: --start-- #! /bin/sh mysql --socket=/tmp/mysql-4.sock --end Make sure to exclude the start/end marker lines when you paste, and fix the socket path if I guessed it wrong, if this is not obvious Execute chmod +x /usr/local/bin/mysql-3 chmod +x /usr/local/bin/mysql-4 Sure. I'm all set for the mysql daemons. I did that through aliases. The bigger hassle is all the utilities. I either type ./mysqladmin -u -p --socket-/tmp/mysql.sock version Or I find a way to shell script for something like ./mysqladmin4 -u -p version or for that matter while I'm at it... ./mysqladmin4 version Same goes for dump and some others. I haven't yet looked into the ins outs (err, no pun intended) of writing shell scripts to accomplish that. I can add to my aliases, and I can write simple one liners like that -- just don't know how to interface to inputs, variables, how to deal with pauses for passwords, etc. I'm sure it'll be relatively straight forward once I read up on it. Thanks for trying to help :-) -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HTML in MySQL Tables
On May 21, 2004, at 9:14 PM, David Blomstrom wrote: --- Hassan Schroeder [EMAIL PROTECTED] wrote: If Cowboy State has a semantic meaning, like nickname, you might want to think about either having a field for it or, if it's part of a text segment that doesn't lend itself to that, use a semantic tag for it, like `nicknameCowboy State/nickname, (reinforced ... and then, for display purposes, transform that XML using XSLT into your appropriate output. You mean I can make up a name for a semantic tag, designating every nicknname nickname or every emphasized word emphasize, for example? And is this something that can only be done with XML? I do recall reading that XML is a very flexible language that lets you create your own codes, styles, etc. Am I correct in guessing that the pros prefer XML to HTML or XHTML when working with MySQL? If so, that's another thing for me to learn. I've found XHTML pretty simple, but XML looks a little more complex. What you want to look into is CSS. If you're already working XHTML, then that's great. It's a cleaner, more straightforward, back the intent of HTML before the browser wars polluted it. Use CSS to define how things appear. Honestly, it takes a while to get. Not the syntax and such (generally easy), but more the methods in how to best apply it. Most CSS refc I have found deal with the mechanics of it. It's hard to find a good refc on how to apply. I recommend the book Eric Meyer on CSS -- it's about practical application. You'll need a companion guide for syntax reference. So, you'd (potentially) end up with: span class=nicknameCowboy State/span It's very easy to initially want to do span class=boldred10ptCowboy State/span and that's pretty typically for everyone's first CSS era, but try to get past that level quickly. I finally hit what I call my third era in CSS this past year, and man, does it make web design so much better. Anyway, quite off topic, so, if you have some questions, hit me up offline. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld sock conflicts
On May 20, 2004, at 11:08 AM, Sasha Pachev wrote: Greg Willits wrote: I have two mysql apps running on the same machine (OS X 10.3.3). A mysql 3.23.54 on port 14551, and a mysql 4.0.16 on 3306. Each has a config file specifying the port and a unique socket name in /tmp. They have coexisted just peachy for a very long time. Now however, w/o any changes to either MySQL3, MySQL4, or the OS, every time I issue a terminal command to one of the MySQL3 bin apps preceded by the usual cd /x/y/z/bin, the commands are being sent to the /usr/local/mysql bin apps on 3306. If I shut mysqld 3306 down (which closes the sock file), then any commands to mysql 14551 gripes that there is no socket file even though the one it should be using is still available. Do not worry about why it stopped working - it was not supposed to anyway, and if it did, it was pure luck :-) I was lucky for over two years across several machines then. I think that may be my best streak of anything ever. Too bad there was no money involved! ;-) A clean way to solve the problem would be to create small shell scripts called mysql-3 and mysql-4 that will connect to the right instance. I've started that process, though I'm not much of a shell scripter yet. So it's time to dig in or get used to specifying the socket I guess. OK. Well, at least I know. Thank you. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
On May 20, 2004, at 2:45 PM, [EMAIL PROTECTED] wrote: Another perspective on the subject of BLOB vs. Links. Links are easier to implement and may be an OK way to start. However, a file system is really a crude database, and I emphasize crude. It's not very good at handling high transaction rates, access from multiple machines, or volume. If your application grows quickly and before you know it you have hundreds of folders with thousands of files in each - your file system will slow to a crawl. All the performance, security, and consistancy features developers have worked so hard to put into database engines don't or barely exist in file systems. So - if you go the link approach - you'll be fine for a while, but when you see the directory structure starting to buckle - it might be time to give BLOBs another look. Interesting. Would make sense that scale would affect the perceived and real limits and hassles of one method vs another, and flip-flop strengths weaknesses. Thx. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld sock conflicts
I have two mysql apps running on the same machine (OS X 10.3.3). A mysql 3.23.54 on port 14551, and a mysql 4.0.16 on 3306. Each has a config file specifying the port and a unique socket name in /tmp. They have coexisted just peachy for a very long time. Now however, w/o any changes to either MySQL3, MySQL4, or the OS, every time I issue a terminal command to one of the MySQL3 bin apps preceded by the usual cd /x/y/z/bin, the commands are being sent to the /usr/local/mysql bin apps on 3306. If I shut mysqld 3306 down (which closes the sock file), then any commands to mysql 14551 gripes that there is no socket file even though the one it should be using is still available. Removing and reinstalling both mysql's (now I have 4.0.18) does not fix it. Each does in fact create its own sock file in /tmp, and each mysqld server runs just fine. I can manually specify the --socket for the 14551 bin apps and they'll work, but I've used mysql3 and mysql4 side by side since one of the later 4.0 betas and I've never had to specify the socket when launching any of the mysql3 utils. There has to be some other kind of socket related config file somewhere that has something to do with this? I have no idea what could have changed all of a sudden and on its own. Thanks for any clues. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
On May 19, 2004, at 1:19 PM, David Blomstrom wrote: I'd like to get some feedback on storing images in MySQL databases. The stuff I've read so far suggests that it's fairly difficult to work with images in MySQL, and they also slow down databases. I've also read that there isn't much you can do with BLOB's that you can't do with PHP manipulating images stored in an ordinary folder. So I just wondered if BLOB's are worth my time. For example, I'm working on a database with information about the 50 states. If I have maps of each state, pictures of each state's capital, etc., is there some BLOB feature that I would find really useful? All conventional wisdom I've ever come across for this type of application is that there's no advantage to keeping the image in the db itself. Just keep them as files on the server, store a filename /or location in the db if necessary, and use your middleware to display the images. Its faster, easier to maintain, and easier to backup. IMO, storing images in the db just bloats the file and complicates all the backup issues. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
On May 19, 2004, at 1:19 PM, David Blomstrom wrote: All conventional wisdom I've ever come across for this type of application is that there's no advantage to keeping the image in the db itself. Just keep them as files on the server, store a filename /or location in the db if necessary, and use your middleware to display the images. Its faster, easier to maintain, and easier to backup. IMO, storing images in the db just bloats the file and complicates all the backup issues. On Wed, 19 May 2004, David Blomstrom wrote: That's the advice I wanted to hear; one less thing for me to learn. :) Thanks. On May 19, 2004, at 2:19 PM, jabbott wrote: I have an application where I serve out blobs. I store the PDF files in blob fields for two reasons. 1. I want to make an interface where staff can upload pdf's into the server but not actually give them access to the server. I use my code to maintain the security of who gets to write to what. Middleware should be able to control that. 2. The pdf's are date sensitive press releases and public notices. They must show up on our web site on a certain date and they MUST not be able to be accessed on our site after that time. Having them stored as blobs I use a cfm page to fetch them out of the database and it is easy for me to write the sql to ~WHERE date or date If they were on the file system someone could have bookmarked the location of the pdf and unless I have something written to go in and prune files out, it could still be gotten. Again, middleware ought to be able to control this a few different ways. One is to use a protected file area to which a server side process has privileged access to, then passed the file to the user for download. Your app determines whether the current web visitor can trigger the script that delivers the file. The file can't be bookmarked. Another method is to change the name as the file is delivered, so that the file received by the user doesn't even match the one on the server. Anyway, I'm sure there's legit uses for storing web resources in BLOBs, but so far I've never seen an advantage over use server config and the web app itself (not that I've made any monster apps mind you, all of mine have been simple enough for single or dual server setup). -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB's - General Guidance
On May 19, 2004, at 3:12 PM, David Blomstrom wrote: Suppose I have a field that stores links that look like this: img src=?php echo $seg ?images/states/ak.gif width=100 height=75 / img src=?php echo $seg ?images/states/wy.gif width=100 height=75 / and another field that stores image links that look like this: img src=?php echo $seg ?images/maps/ak.gif width=100 height=75 / img src=?php echo $seg ?images/maps/wy.gif width=100 height=75 / It's better to have a central config file that defines all your paths as variables. No need to embed hard paths in your links either in code or stored in the db. I do mockup with a GUI which creates paths, but then apply a gobal search replace to change the src to variable plus the file name. So, even your images/maps/ component can be a var. then all I have to do is replace $seg with the path to the image folder to display my images. The images don't even have to be the same size, since I have to enter each state's image individually, anyway. If the images are pre-defined standards, then yeah, entering the size in the db is probably as good a way as any if they're different sizes. However, if you have a bucnh of standard image slots to be filled with images of all the same size, then you can set those as PHP vars defined in config file. Another method is to embed the size in the file name, and parse the file name when it is retrieved from the db. Finally, if the images are being uploaded, use a tool like imagemagick to acquire the image properties at that time and store them. Then your sizes can be vars too. And I could even create a field that anticipates a future series of images in an as yet unspecified folder: img src=?php echo $seg ?images/?php echo $WHAT ?/ak.gif width=100 height=75 / If I then create a series of images in a folder named landscapes - or nature/landscapes - then I would use PHP to replace WHAT with nature/landspaces. Yep. That can be done in the db or in a univeral config file your middleware reads depending on what makes the most sense. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld sock fights between two servers
I suspect this is more of a unix question (OS X 10.3.3) than a mysql question, but hopefully someone will tolerate it. I have two mysql apps running on the same machine. A mysql 3.23.x on port 14551 (which is integral to the Lasso middleware server), and a separate mysql 4.0.x on 3306. They have coexisted just peachy for a very long time. Now (w/o any changes to Lasso, MySQL4, or the OS), every time I issue a terminal command to one of Lasso's MySQL bin apps preceded by the usual cd /applications/lassoprofessional6/bla_bla_bla, the commands are being sent to the /usr/local/mysql bin apps on 3306. Removing and reinstalling both mysql's does not fix it. Each does in fact create its own sock file in /tmp. I can specify the --socket for the 14551 server and things work, but I never used to have to do that (going on 3 yrs now). There has to be some kind of socket related config file somewhere that has something to do with this? I have no idea what could have changed all of a sudden and on its own. Thanks for any clues. -- greg willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting Column to SET
I'm converting a database where multiple selections from a valuelist were stored delimited within a single field with a \r. I imported this data into MySQL 3.23.46 and imported the valuelist selections as is into a VARCHAR field large enough to hold all selections. I'm fine storing selection values to the MySQL field using the existing middleware valuelist routines. However, I now find myself in a pickle when it comes to searching that field for combinations of values. The previous database compared each value in my search string with each value as an independent contains. So, if I had red, blue, green, black as selection choices, and a field had red \r green \r black, (spaces are for readability only) I could search for red \r black and get a list of found records. In MySQL I can search with a single selection red and specify a 'contains' operator with my middleware (Lasso Pro 5) and get a list of records. However, I cannot include more than one option in my search string. It's obvious (and understandable) that MySQL is searching with a literal red \r black string, and finds no matches. Questions: 1 - is there a way with some SQL expression to search the existing field and \r delimited data with a string such as red \r black to reproduce the same search I had before? (I know this will not be as fast as SET). 2 - if I convert this column to a SET and define my choices, will MySQL automatically recognize the existing \r delimited values in the fields and properly convert the field data to its own preferred format for SET fields? 2a - do I first have to manually convert all \r instances to commas or something else? 2b - do I have to export the data, redefine the column as SET, then reimport it into this column? 3 - what happens to field data when the SET selections are redefined? The reason behind the previous setup was to allow easy changes to the selection list and isolate the definition of the list from the database. I know SET is faster, but these are really small databases. Thanks. -- Greg Willits -- [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
SET Conversion Questions
A bit long, but consulting docs DuBois aren't yielding solutions apparant to me... I'm converting a database where valuelists were controlled with a custom routine that read values from a text file. Selections of a specific valuelist were saved to a single field in the database, and each selection is delimited within the field with a \r. Hoping to use that same routine with MySQL, I imported this data into MySQL 3.23.46 and imported the valuelist selections as is into a VARCHAR field large enough to hold all selections. I'm fine storing selection values to the MySQL field. However, I now find myself in a pickle when it comes to searching that field for combinations of values. The previous database compared each value in my search string with each value as an independent contains. So, if I had red, blue, green, black as selection choices, and a field had red \r green \r black, (spaces are for readability only) I could search for red \r black and get a list of found records. MySQL doesn't like to do this. I can search with a single selection red and specify a 'contains' operator with my middleware (Lasso Pro 5) andget a list of records. However, I cannot include more than one option in my search string. It's obvious (and understandable) that MySQL searching with a literal red \r black single string, and finds no matches. Questions: 1 - is there a way with some SQL expression to search the existing field and \r delimited data with a string such as red \r black to reproduce the same search I had before? (I know this will not be as fast as SET). 2 - if I convert this column to a SET and define my choices, will MySQL automatically recognize the existing \r delimited values in the fields and properly convert the field data? 2a - do I first have to manually convert all \r instances to commas or something else? 2b - do I have to export the data, redefine the column as SET, then reimport it into this column? 3 - what happens to field data when the SET selections are redefined? The reason behind the previous setup was to allow easy changes to the selection list and isolate the definition of the list from the database. I know SET is faster, but these are really small databases. Thanks. -- Greg Willits -- [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
Re: print database schema
Check out this program. Just wish there was an OS X version :-( http://www.datanamic.com/dezign/index.html -- greg willits -- [EMAIL PROTECTED] From: Steven Wren [EMAIL PROTECTED] Date: Tue, 29 Jan 2002 16:22:12 +1000 (EST) To: Paul DuBois [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: print database schema Hello more of a visual tool. The program I did see had views of all the tables as if you had done a describe table_name for each table, then had lines linking the relationships between tables (similar to what you can view in Access , even though i hate to admit I have used it :) would just be good for management and seeing where you can improve indexes etc... thanks. Regards, ___ Steven Wren [EMAIL PROTECTED] http://www.server101.com Webhosting and E-commerce Solutions Phone : AU (07)38766 101 US 877 7762 101 Fax : +61 7 38763 101 The world is not only stranger than we suppose, it is stranger than we can suppose. -J.B.S. Haldane On Tue, 29 Jan 2002, Paul DuBois wrote: At 14:42 +1000 1/29/02, Steven Wren wrote: Hello I was wondering if anyone knew of a program that would allow you to print out a complete database. I have seen it on a PostGre DB, so I am sure MySQL should have something... You mean mysqldump --no-data db_name ? cheers! Regards, ___ Steven Wren [EMAIL PROTECTED] http://www.server101.com Webhosting and E-commerce Solutions Phone : AU (07)38766 101 US 877 7762 101 Fax : +61 7 38763 101 The world is not only stranger than we suppose, it is stranger than we can suppose. -J.B.S. Haldane - 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
Case for Field Name Alias
Have a design situation where I could use a field name alias to simplify some programming. I'm wondering which of my options would be considered good form. I generated a series of database action shells (as I call them) in a middleware language to standardize a sequence of events surrounding db add, update, and delete actions. These were originally created for a project with a number of simple unrelated tables. Each table's primary key was a field called rcrdNo. Because of this standardized field name, these routines all use rcrdNo explicitly as the PK. Now, moving these routines to a project where there are a number of related tables (my first real SQL project, so its my first crack at xfring these routines) I see where each table's PK would benefit from a unique name to keep things from getting very confused. I see two options: 1) send the unique PK field name as a paramater to my routines (which I have to do for the db_name anyway). Pro: fully flexible routines. Con: more programming steps. 2) create a field name alias of rcrdNo for each PK. Pro: routines work as is, less programming. Con: I dunno, I guess I am asking you guys :-) I suspect aliases are best reserved to solve hairy conflicts when merging sysems or something. But does the simplification of using standardized routines like this also make for good reason to use them? Wanting to have adopt good habits from the beginning Thanks. -- Greg Willits -- [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