Re: Printing
I think that offering some sort of feedback other than result sets would be nice for debugging. Peter Brawley wrote: I hope this isn't a silly question, or something covered in a FAQ. . . but is there any reason to not have at least some primitive print formatting commands in MySQL? Or am I missing something blindingly obvious? I think the idea is that RDBMS ought to stick to what is necessary for RDBMS management and leave the rest to application programs. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforcing Data Format
I'd go the other way and use a trigger to validate the data, and roll back the transaction if it isn't in valid format (assuming you are using transactions). This way, ANY app that puts data in that field gets the validation. An error in an application or typing by someone who has direct table access will leave you with bunk data, which is worse IMO than having to figure out the code for the trigger. You have to keep in mind that I generally spend half my day fixing broken data though :) Mogens Melander wrote: On Thu, May 3, 2007 12:56, Tim Milstead wrote: Hello, Is it possible to enforce data formatting in fields using something like a regular expression? varchar is great but does not stop someone putting in the wrong reference number. I suspect the answer is no, you have to do it at a higher level. What aprroach would people suggest? Perhaps another table with two fields - field_name and field_regex? And, what would you have mysql do if a user failed to enter fx. valid date ? Maybe you'd be better off, handling input validation in the user-interface. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using replace with New Lines
I always go with REPLACE(REPLACE(STRING, '\n', ''), '\r', ''), since depending on where your data came from there may be one or the other, or both. Although if there is a shorthand/more efficient way I'd love to hear it. [EMAIL PROTECTED] wrote: Ok.. I found the problem.. I needed to add a \r.. but now that opens up the question can I have a Multi replace search in replace? Example can I do something like this in MySQL REPLACE (STRING,'\r' or '\n',' ') REPLACE(DESCRIPTION,'\r\n',' ') Wishing you the best you know you deserve, __ Hello Group, Looking to use this REPLACE to strip multi line data out of my return data. I am having problems getting the \n to work correctly, any ideas? REPLACE(DESCRIPTION,\n,' ') SELECT rpad(CASE WHEN DESCRIPTION IS NULL THEN '' ELSE REPLACE(DESCRIPTION,'\n',' ') END,80,' ') as var FROM hardware; Wishing you the best you know you deserve, __ Lucas Heuman CM Web Developer SRA International, Inc. FAA, WJHTC/Bldg 300, 2nd Fl., H33 Atlantic City Int'l Airport, NJ 08405 Phone 609.485.5401 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SPAM ?
http://lists.mysql.com/[EMAIL PROTECTED] that link ought to help you. Jacques Brignon wrote: Hi, I keep receiving this message by several dozens each day, how can this be stopped? Regards Jacques -Message d'origine- De : Jerry Schwartz [mailto:[EMAIL PROTECTED] Envoyé : jeudi 22 mars 2007 17:53 À : [EMAIL PROTECTED]; 'James Tu' Cc : 'MySQL List' Objet : RE: Finding a record in a result set I don't think that will work. If there are 1,000 records that qualify but none for Joe, then it will return 1,001. If Joe is in record 1 of the retrieved record set, and there are 999 other people who match the WHERE clause, then it will retrieve 1,000. Am I missing something? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, March 22, 2007 12:33 PM To: James Tu Cc: MySQL List Subject: Re: Finding a record in a result set I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) If 'Joe' is a unique name LOL... SELECT 1 + COUNT(*) FROM employees WHERE name 'Joe' AND state = 'MA' AND hiredate datevalue; PB James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date: 3/22/2007 7:44 AM -- 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]
Re: leading the technological bandwagon
You should send that one to The Daily WTF... http://thedailywtf.com/ Jay Blanchard wrote: [snip] I recently last week Had and experience with an Very small Company, where as they had around 15 Machines all hooked Star topology and a central iis ASP Web server that only showed the date, and a few small utilities when addressed To it, now there entire Operation was based upon Excel, everything not joking I mean everything, was a file share to an Folder (around 90 Folders) in which they had probably 200 + excel sheets in each one u name something u need to do in business I guarantee there was a excel sheet for it, repetitive sheets for Different Operations, all Sheets linked to one another thru references in excel, excel97 Mind u, I was there on a Consulting call, to inform them of a better way to update / Operate there business They ended up saying they liked there Current way better it makes more sence to them and Only wanted me to link 10 more sheets to what they already had and add a few more file shares, of Course I refused this Project and walked out Laughing my ass off in the car [/snip] Seriously? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grant privs to multiple tables at once?
You can use the INFORMATION_SCHEMA.TABLES view to generate the GRANT
statements for you. Write a query along these lines:
SELECT CONCAT('GRANT SELECT ON test.', TABLE_NAME, ' to ''foouser'';')
FROM INFORMATION_SCHEMA.TABLES
WHERETABLE_SCHEMA = 'test'
AND TABLE_NAME LIKE 'foo_%'
Then run it, copy the results, and run those results as a query or
script. You can of course get as crazy as you want with this, for
example if you do this for many users maybe write a stored procedure
that takes a parameter for the username and can therefore be used as a
tool whenever you need it.
It isn't a syntax you asked for, but it is a nice trick that works.
David Felio wrote:
Assume database 'biggie' with 15 tables, 10 of which start with 'foo_'.
I want the user 'foouser' to have access only to those tables that begin
with 'foo_'.
I'm hoping that I am just being blind because I don't see anything in
the manual or in the MySQL book on granting to multiple tables at once
and the * wildcard appears to only work by itself, not when appended to
a string (i.e. I can't do grant select on biggie.foo_* to
'foouser'...). I've tried multiple variations of wildcards, to no avail.
Please tell me I'm not going to have to explicitly grant privs to each
table. There are actually several set of tables for a total of several
hundred tables to which I will need to apply permissions.
Thanks.
David
--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]
Re: Outputting text in a
There apparently isn't a PRINT or RAISERROR function like there is in SQL Server, I've asked about this before. I created a stored procedure to dump debug text into a table (which is timestamped) and another to output the rows in there in a SELECT. It isn't the same but it's something. Dan Buettner wrote: Sure - in your sql script, put in SELECT text to the screen; Dan On 8/23/06, Jesse [EMAIL PROTECTED] wrote: Is it possible to output text to the screen from a .sql script? If so, how? Thanks, Jesse -- 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]
Re: PRINT statement?
Microsoft did not invent the concept of outputting a user-defined line of text; I'm not going to research this but I doubt if they invented using the word PRINT for it either. Also, EVERY vendor includes extensions to the standard (I just happen to know the keywords for the T-SQL ones since that is what I get paid for). I'm not here to discuss people's religious beliefs, I'm here to learn what MySQL can and can not do. Peter Brawley wrote: Stephen Cook wrote: There are such things as extensions to the standard, and many languages besides BASIC that have the ability to output a character string. No need to be snippy. The preference expressed is to that SQL not be bowdlerised into Microsoftese. PB - I will look into the --silent option, thanks! Peter Brawley wrote: Stephen Cook wrote: I appreciate it but SELECT isn't quite what I want. It adds an extra 4 to 6 lines to the output (drawing the table, headers, row counts, etc). PRINT simply outputs whatever comes after it: PRINT is not a SQL command. The mysql client (fortunately) does not speak Basic. To minimise output in the mysql client, have a look at the -s --silent option. PB - PRINT 'hey you!' would show: hey you! Not a big deal I suppose but it makes for a lot more scrolling around. I've started just dumping the comments (i.e. '') into a table with a timestamp, so I can review it afterwards. Its a close second. Rhino wrote: Thanks, Quentin, for the documentation. Assuming that the Transact-SQL Help file is using various terms in the same way as MySQL does, particularly string expression and function, I think we will find that the SQL SELECT will do all of the things that Stephen has come to expect from the PRINT statement in MS SQL Server. I've just put together an SQL Script that I think demonstrates that SELECT can do mostl of the same things as the PRINT statement. Here is the script, which works perfectly in MySQL 4.0.15: = select === S C R I P T B E G I N S === as ; select CONNECT TO DATABASE as Action; use tmp; select DROP/CREATE TABLE as Action; drop table if exists users; create table if not exists users (user_id smallint not null, user_fname char(20) not null, user_lname char(20) not null, user_birthdate date not null, user_education_years int not null, primary key(user_id)); select POPULATE TABLE AND DISPLAY CONTENTS as Action; insert into users values (1, 'Alan', 'Adams', '1970-04-08', 15), (2, 'Bill', 'Baker', '1964-02-01', 18), (3, 'Cass', 'Cooke', '1981-12-04', 12), (4, 'Dina', 'Davis', '1944-06-06', 19), (5, 'Earl', 'Edger', '1990-08-02', 17); select * from users; select SET AND DISPLAY SCRIPT VARIABLES as Action; set @minimum_education_years = 16; set @birthdate_of_youngest_legal_worker = date_sub(curdate(), interval 16 year); select as Variable, as Value UNION select minimum_education_years=, @minimum_education_years UNION select birthdate_of_youngest_legal_worker=, @birthdate_of_youngest_legal_worker; select as Variable, as Value UNION select minimum_education_years=, @minimum_education_years UNION select birthdate_of_youngest_legal_worker=, @birthdate_of_youngest_legal_worker; select EXECUTE QUERIES THAT USE SCRIPT VARIABLES as Action; select concat(Get users who have more than , @minimum_education_years, years of education) as Query; select * from users where user_education_years = @minimum_education_years; select concat(Get users who are old enough to work, i.e. were born before , @birthdate_of_youngest_legal_worker) as Query; select * from users where user_birthdate = @legal_to_work; select DISPLAY FUNCTION RESULTS as Action; select as Function, as Value UNION select curdate()=, curdate() UNION select now()=, now() UNION select Firstname+Lastname=, concat(user_fname, ' ', user_lname) from users where user_id = 1; select === S C R I P T E N D S === as ; = and this is the output of the script: = +---+ | | +---+ | === S C R I P T B E G I N S === | +---+ 1 row in set (0.00 sec) +-+ | Action | +-+ | CONNECT TO DATABASE | +-+ 1 row in set (0.00 sec) Database changed +---+ | Action| +---+ | DROP/CREATE TABLE | +---+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +-+ | Action | +-+ | POPULATE TABLE
Re: PRINT statement?
There are such things as extensions to the standard, and many languages besides BASIC that have the ability to output a character string. No need to be snippy. I will look into the --silent option, thanks! Peter Brawley wrote: Stephen Cook wrote: I appreciate it but SELECT isn't quite what I want. It adds an extra 4 to 6 lines to the output (drawing the table, headers, row counts, etc). PRINT simply outputs whatever comes after it: PRINT is not a SQL command. The mysql client (fortunately) does not speak Basic. To minimise output in the mysql client, have a look at the -s --silent option. PB - PRINT 'hey you!' would show: hey you! Not a big deal I suppose but it makes for a lot more scrolling around. I've started just dumping the comments (i.e. '') into a table with a timestamp, so I can review it afterwards. Its a close second. Rhino wrote: Thanks, Quentin, for the documentation. Assuming that the Transact-SQL Help file is using various terms in the same way as MySQL does, particularly string expression and function, I think we will find that the SQL SELECT will do all of the things that Stephen has come to expect from the PRINT statement in MS SQL Server. I've just put together an SQL Script that I think demonstrates that SELECT can do mostl of the same things as the PRINT statement. Here is the script, which works perfectly in MySQL 4.0.15: = select === S C R I P T B E G I N S === as ; select CONNECT TO DATABASE as Action; use tmp; select DROP/CREATE TABLE as Action; drop table if exists users; create table if not exists users (user_id smallint not null, user_fname char(20) not null, user_lname char(20) not null, user_birthdate date not null, user_education_years int not null, primary key(user_id)); select POPULATE TABLE AND DISPLAY CONTENTS as Action; insert into users values (1, 'Alan', 'Adams', '1970-04-08', 15), (2, 'Bill', 'Baker', '1964-02-01', 18), (3, 'Cass', 'Cooke', '1981-12-04', 12), (4, 'Dina', 'Davis', '1944-06-06', 19), (5, 'Earl', 'Edger', '1990-08-02', 17); select * from users; select SET AND DISPLAY SCRIPT VARIABLES as Action; set @minimum_education_years = 16; set @birthdate_of_youngest_legal_worker = date_sub(curdate(), interval 16 year); select as Variable, as Value UNION select minimum_education_years=, @minimum_education_years UNION select birthdate_of_youngest_legal_worker=, @birthdate_of_youngest_legal_worker; select as Variable, as Value UNION select minimum_education_years=, @minimum_education_years UNION select birthdate_of_youngest_legal_worker=, @birthdate_of_youngest_legal_worker; select EXECUTE QUERIES THAT USE SCRIPT VARIABLES as Action; select concat(Get users who have more than , @minimum_education_years, years of education) as Query; select * from users where user_education_years = @minimum_education_years; select concat(Get users who are old enough to work, i.e. were born before , @birthdate_of_youngest_legal_worker) as Query; select * from users where user_birthdate = @legal_to_work; select DISPLAY FUNCTION RESULTS as Action; select as Function, as Value UNION select curdate()=, curdate() UNION select now()=, now() UNION select Firstname+Lastname=, concat(user_fname, ' ', user_lname) from users where user_id = 1; select === S C R I P T E N D S === as ; = and this is the output of the script: = +---+ | | +---+ | === S C R I P T B E G I N S === | +---+ 1 row in set (0.00 sec) +-+ | Action | +-+ | CONNECT TO DATABASE | +-+ 1 row in set (0.00 sec) Database changed +---+ | Action| +---+ | DROP/CREATE TABLE | +---+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +-+ | Action | +-+ | POPULATE TABLE AND DISPLAY CONTENTS | +-+ 1 row in set (0.00 sec) Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 +-++++--+ | user_id | user_fname | user_lname | user_birthdate | user_education_years | +-++++--+ | 1 | Alan | Adams | 1970-04-08 | 15 | | 2 | Bill | Baker | 1964-02-01 | 18 | | 3
Re: PRINT statement?
the value shown for the second variable name is shown as birthdate_of_youngest_le and the VALUE of that variable is shown only as 1990, NOT the correct value, which is 1990-05-11. The danger is that it is not obvious that the value of the variable has been truncated. When I first encountered this, I thought I'd written the date_sub() function incorrectly and messed around with it for awhile before I discovered the truncation problem. Therefore, my technique is to always use the first SELECT to set the column names for the result set AND to control the width of the result set columns. -- Okay then, aside from the issue of string expressions, which I'm not sure about yet, I think we can see that SELECT can do everything else that the PRINT command supports. -- Rhino - Original Message - From: Quentin Bennett [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; Stephen Cook [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Wednesday, May 10, 2006 11:59 PM Subject: RE: PRINT statement? From Transact-SQL Help file: PRINT Returns a user-defined message to the client. Syntax PRINT 'any ASCII text' | @local_variable | @@FUNCTION | string_expr Arguments 'any ASCII text' Is a string of text. @local_variable Is a variable of any valid character data type. @local_variable must be char or varchar, or be able to be implicitly converted to those data types. @@FUNCTION Is a function that returns string results. @@FUNCTION must be char or varchar, or be able to be implicitly converted to those data types. string_expr Is an expression that returns a string. Can include concatenated literal values and variables. The message string can be up to 8,000 characters long; any characters after 8,000 are truncated. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Thursday, 11 May 2006 3:51 p.m. To: Stephen Cook Cc: MySQL List Subject: Re: PRINT statement? I am not familiar with the PRINT command so I don't know what it does. I played with MS SQL Server once for a couple of days a few years back and that is the only contact I've ever had with SQL Server. If you can tell me what PRINT does, in detail, maybe I can suggest another alternative. -- Rhino - Original Message - From: Stephen Cook [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Wednesday, May 10, 2006 8:09 PM Subject: Re: PRINT statement? I've started using the SELECT with no other clauses but I am still curious about a PRINT-like command. It is for SQL scripts. Rhino wrote: - Original Message - From: Stephen Cook [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Sunday, May 07, 2006 3:53 AM Subject: PRINT statement? Is there a statement similar to PRINT in T-SQL (MicroSoft SQL Server)? It would be handy to debug some scripts. If you're talking about a script that is running SQL, you can simply use the SELECT statement without any FROM, WHERE, ORDER BY, GROUP BY or HAVING clauses. For example: select Creating Foo table as Action; will produce the following output: +--+ | Action | +--+ | Creating Foo table | +--+ 1 row in set (0.00 sec) If you're talking about an OS script, you can use OS commands to display things. For example, I have some BASH scripts on our Linux server so I can use the BASH echo command, like this: #!/bin/bash report_date=`/bin/date` echo Report Date: $report_date; to produce this output: Report Date: Sun May 7 09:42:57 EDT 2006 -- Rhino -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PRINT statement?
I've started using the SELECT with no other clauses but I am still curious about a PRINT-like command. It is for SQL scripts. Rhino wrote: - Original Message - From: Stephen Cook [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Sunday, May 07, 2006 3:53 AM Subject: PRINT statement? Is there a statement similar to PRINT in T-SQL (MicroSoft SQL Server)? It would be handy to debug some scripts. If you're talking about a script that is running SQL, you can simply use the SELECT statement without any FROM, WHERE, ORDER BY, GROUP BY or HAVING clauses. For example: select Creating Foo table as Action; will produce the following output: +--+ | Action | +--+ | Creating Foo table | +--+ 1 row in set (0.00 sec) If you're talking about an OS script, you can use OS commands to display things. For example, I have some BASH scripts on our Linux server so I can use the BASH echo command, like this: #!/bin/bash report_date=`/bin/date` echo Report Date: $report_date; to produce this output: Report Date: Sun May 7 09:42:57 EDT 2006 -- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PRINT statement?
Is there a statement similar to PRINT in T-SQL (MicroSoft SQL Server)? It would be handy to debug some scripts. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
A commonly used example (at least at my last 3 jobs) would be a table of demographics for people (whether they be employees, clients, whatever). You can have one table and allow NULLs for some of the fields (id, LastName, FirstName, MiddleInitial, Title, NameSuffix, AddressLine1, AddressLine2, City, State, Zip, Phone1, Phone2, Fax, Pager), or you can have a separate table for each of the nullable fields (I count 8 in addition to the base demographics table). I am not a master DBA but it seems kind of brain damaged to insist on using 9 tables left joined together every time I need a line of demographics. Martijn Tonies wrote: Hello, (please read all before replying) Basics of database design: store what you know. Given that NULLs basically means the absence of a value (eg: unknown), you shouldn't be storing NULLs. Nonsense!! That's a bold statement ... That's simply wrong. A null means unknown or not applicable and is a Ah, so now things become clear, a NULL actually can mean two things? So much for clarity then... Come on; that's not fair. Unknown and not applicable are more like different senses of the same thing, not two opposite things. Read the literature on how to design databases. What you do is storing true propositions. That is, each attribute defines a certain true proposition. For example: Employee Martijn has Employee# 14. You should be able to derive these sentences from every row. Putting a NULL or N/A in there fails to meet this requirement. Perhaps my theory is a bit rusty but I have never heard of this true propositions business before. Do you have a citation where I can read about this? I would certainly recommend this one: http://www.amazon.com/exec/obidos/ASIN/0321197844/databasede095-20/103-0880452-0854264?creative=327641camp=14573adid=0VQ1KDVJ56MV7389RPEQlink_code=as1 Frankly, I'm skeptical about your interpretation of this idea. While you clearly don't want false information in a database, it isn't false to say that student such-and-such's exam mark or such-and-such an employee's termination date is unknown or not applicable. It _would_ be false to store a grade of 0 for a student who didn't take the exam because it implies that he got every question wrong when in fact he didn't write the test at all; Agreed. that would be an example of 0 having two contradicatory meanings. Given a phobia about nulls, it is a reasonable design to put students who don't take the test in a separate table but it's not the ONLY way to handle the situation: a null to indicate a student who didn't take the test is perfectly reasonable too. Disagreed. Let me explain further: In this case, a null means (according to you and in your design) that the student did not take the test. It could also mean that we lots it's grade for now, and that we want to fill it in later, as in: unknown, which is what NULL also means (your own words). In another case, null would be the employee isn't terminated yet, but could be in the future. That's the problems with nulls - there's no actual defined meaning. You can never say: This table holds test results, a row will mean: Student with StudentID 123 made test #12 and scored a 4 Cause you can have rows that can mean this: Student with StudentID 123 made test #12 and scored a yet unknown score or Student with StudentID 123 made test #12 and did not make the test or Student with StudentID 123 and test #12 doesn't apply to him In short: NULL is ambigious. perfectly valid value to use in many, many situations. Two classic examples: Let me first state that there's a difference between storing NULLs and handling NULLs in your result. Yes, of course there is: so what? You said NULLs should never be _stored_; that's what I'm responding to. Using NULLs is not very _difficult_ anyway, just a bit tedious because it is another case to handle. There's no point in storing what you don't know. But you _do_ know something: that the student didn't take the exam. That is a piece of true knowledge all by itself. But that's not what the rows in the table would mean - see above. In the relational model, the idea is to store true propositions - that is, each row means the same thing, but with different attributes. See above. So, let's debunk these two classic examples... Please note that I did not say that you MUST use NULLs, just that they were perfectly valid to use in a design. Depends on who you're asking ... A few years ago, I was working at a company that had developed an application with an Oracle database with about 400 tables, nothing too large. Plenty of tables had NULLs, not because of missing business data, but rather used internally in the system. I'm not sure what you mean by that phrase used internally in the system. Missing business data, eg: no middle name. Some people would put in a NULL in there as well, while NULL can also mean: I do not KNOW this
DROP DATABASE doesn't actually drop the database?
I am scripting out the creation of a database so I can make changes and then run the script to generate a clean copy (by running it in MySQL Query Browser). The script DROPs all the tables, then CREATEs them again along with all the indices and whatnot. However, if I run the script after having run it once (if I close the Query Browser and then open it again and reload the script later), I get error 1061 Duplicate key name on all of the indices. So I figured I missed something, and I'll just DROP the whole database and then run the script. I add a DROP DATABASE databasename; and a CREATE DATABASE databasename; at the start of my script and try to run it again. Now I get errors 1008 Can't drop database 'databasename'; database doesn't exist and 1007 Can't create database 'databasename'; database exists, followed by the index errors above. If I drop the database from MySQL Administrator (or the command-line client), and then run the script again I get the same errors. The database and all of the scripted objects are created (properly as far as I can tell), but why would I get these errors? I am using the latest version of MySQL (5.0.19) with InnoDB as the storage engine on Windows 2000 Professional SP4, but I have the same problem with 5.0.17. If there is any other information you need I'd be happy to supply it.
Re: DROP DATABASE doesn't actually drop the database?
Thanks for your comments! I ran my entire script (DROP DATABASE and all) with the command-line client, and got no errors. Perhaps there is something with the Query Browser that is causing this problem. I added the IF EXISTS and IF NOT EXISTS in appropriate places (although I can see the database there beforehand, and see it disappear in MySQL Administrator, it shouldn't be necessary in this case because I know what is there). Of course, I still get the duplicate index errors in Query Browser, but I suspect it is that program and not the database server that is causing this. The reason I am scripting this manually is because I am trying to learn and practice MySQL (I come from a MS SQL Server background), and this database is very small (3 or 4 tables, a view and 2 stored procedures so far). It is not difficult to make a change and re-run the script. When I get anything good (and of decent size) going I'll start using the other options; right now I'm just playing with a throwaway database to get a feel for the syntax. On 3/13/06, Andreas Krüger [EMAIL PROTECTED] wrote: Stephen, the behavior of MySQL server sounds bizarre, as to what information you give us. For the DROP DATABASE and DROP TABLE statements, there is an option that prevents an error, if database or table don' t exist: DROP DATABASE* IF EXISTS *db_name DROP TABLE* IF EXISTS *tbl_name You might also want to have a look at the 5.0 manual: http://dev.mysql.com/doc/refman/5.0/en/drop-database.html http://dev.mysql.com/doc/refman/5.0/en/drop-table.html I am further astonished that you do script files manually for re-creating databases and tables.* Why don' t you use mysqldump for dumping a database and its tables mysql for loading the dumped information?* You can dump a database by mysqldump db_name db_name.sql There are many handy options to mysqldump as --add-drop-table and -all that you should consider to use You then can manually update the dump files in a text editor. mysql db_name db_name.sql recreates the dumped data with all tables. Andy Stephen Cook wrote: I am scripting out the creation of a database so I can make changes and then run the script to generate a clean copy (by running it in MySQL Query Browser). The script DROPs all the tables, then CREATEs them again along with all the indices and whatnot. However, if I run the script after having run it once (if I close the Query Browser and then open it again and reload the script later), I get error 1061 Duplicate key name on all of the indices. So I figured I missed something, and I'll just DROP the whole database and then run the script. I add a DROP DATABASE databasename; and a CREATE DATABASE databasename; at the start of my script and try to run it again. Now I get errors 1008 Can't drop database 'databasename'; database doesn't exist and 1007 Can't create database 'databasename'; database exists, followed by the index errors above. If I drop the database from MySQL Administrator (or the command-line client), and then run the script again I get the same errors. The database and all of the scripted objects are created (properly as far as I can tell), but why would I get these errors? I am using the latest version of MySQL (5.0.19) with InnoDB as the storage engine on Windows 2000 Professional SP4, but I have the same problem with 5.0.17. If there is any other information you need I'd be happy to supply it.
Re: Strange behavior with integer unsigned type...
Maybe it is because I am a programmer, but (unsigned) 0 - 1 = 4294967295. What's the big deal? Gleb Paharenko wrote: Hello. That seems like a bug: http://bugs.mysql.com/bug.php?id=14543 Marko Domanovic wrote: mysql 5.0.15-standard UPDATE table SET fieldname = fieldname-1 when the fieldname is 0 gives me 4294967295 fieldname is integer(10) unsigned... maybe it would be more logical the expression to evaluate as 0, insted 2^32 .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] SPAM
I got one from mysql@lists.mysql.com, and one from [EMAIL PROTECTED] And my filters remove anything that isn't from one of the mailing lists I subscribe to. Daniel Kasak wrote: Stephen Cook wrote: What's the deal with SPAM on the list? I don't see any. Maybe my spam filter is better than yours? What exactly are you asking anyway? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[OT] SPAM
What's the deal with SPAM on the list? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot start MySQL under Win2000
You must not install 5.0 over 4.1, put it somewhere else. Also, instead of naming the service MySQL both times, call it something else (i.e. MySQL41 and MySQL50). If you use the Windows installer distribution, it is one of the options; if you are doing it by hand then you already know how to name the services whatever you like. HTH. Octavian Rasnita wrote: Hi, Is it possible to install 2 versions of MySQL on the same computer? I want to have MySQL 4.1 and 5.0 installed, and use only one of them at a time. I have installed MySQL 5.0 over MySQL 4.1 and now MySQL 5 works fine, however, I cannot start MySQL 4.1 although I have stopped MySQL 5 first. When I try to: net start mysql (where mysql is the name of MySQL 4.1 service) or tried to start the service from the Windows management console (Services), the following error appears: Could not start the MySQL service on Local Computer. Error 1067: The process terminated unexpectedly. Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows - logging into MySQL
His answer was correct, and somewhat politer than RTFM. As for finding your own damn answers, see again Mr. Green's original reply. Beauford wrote: Sorry, but I don't take kindly to idiots. I don't care if he's gods gift to MySQL. If there isn't enough info for him to give an informed answer then he should either ask for more or shut up. I'm just simply trying to resolve a problem, and flip answers aren't needed or appropriate. I don't send emails to the list at the first sign of problem, this is probably the first in over a year and a half. When I do however, it is because the manual or other attempts have not provided me the answers I need. In any event I will move on and find my answers elsewhere. -Original Message- From: J.R. Bullington [mailto:[EMAIL PROTECTED] Sent: November 30, 2005 6:37 PM To: mysql@lists.mysql.com Subject: RE: Windows - logging into MySQL You need to be careful what you write. I have found that Shawn is one of the best guys on this list. His answers are 99% right on target (1% because not enough information was given) and can help on every version of db out there. Don't start a flame war because your feelings were hurt. Grow up. J.R. -Original Message- From: Beauford [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 30, 2005 6:05 PM To: 'Logan, David (SST - Adelaide)' Cc: mysql@lists.mysql.com Subject: RE: Windows - logging into MySQL There are always idots like Shawn Green that give BS answers just for the sake of responding, and for me it just reflects badly on the group as a whole. There are many others that help, but when I open my email and this is the first response I see, it just gets under my skin. FYI. I have posted some information in another response as to the problems with later versions etc. -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: November 30, 2005 5:57 PM To: Beauford; [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: Windows - logging into MySQL Sorry Beauford, The url that Shawn gave you is valid for the version you quoted. There hasn't been much change and they work just as well. This is also the earliest documentation that now exists on the website. What is the bad name this list has gained? I've found it helpful on numerous occasions. I think a reconsideration of your opinions would result in a change of heart. The documentation states the defaults that the mysql client program would accept, it also details how to set users and passwords exactly as you requested. These all work on 3.23.58 (I have this version installed at a customer site as we write). Perhaps if you detail the problems you had installing the later versions, somebody may be able to help. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Beauford [mailto:[EMAIL PROTECTED] Sent: Thursday, 1 December 2005 9:21 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: Windows - logging into MySQL This is why lists like this get a bad name. First off, these instructions are for 4.1. Secondly. What makes you think I haven't read the instructions or searched extensively on Google etc. Either give a proper answer or just shut your mouth. Never mind anyone responding, I'll find my own damn answers. Thanks for the help. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: November 30, 2005 12:16 AM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Windows - logging into MySQL Beauford [EMAIL PROTECTED] wrote on 11/29/2005 11:31:24 PM: I just installed MySQL 3.23 (the only one I could get to work) and I find that I can log into the server just by typing mysql at the DOS prompt. Ho do make it so you have to use a user name and password to get in. Thanks Running on Win2k Advance Server. You could try following the installation instructions in the manual http://dev.mysql.com/doc/refman/4.1/en/windows-installation.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- 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]
Re: MySQL vs XML
MySQL is a relational database. XML is a text file. the biggest difference is that MySQL will let you organize, sort, match/link (joins), and otherwise manipulate the data you have. XML is just text with tags in a heirarchy; anything other than reading it in a text editor will take programming on your part. IMHO, XML is extremely overrated. it has its uses of course, but it is waay overused these days because of the buzz. David Blomstrom wrote: I've been gathering data for an animal kingdom database for quite some time and am now trying to figure out how to organize and display it. So far, I have a table that lists every order, suborder, family, subfamily, genus and species of mammal in a child-parent relationship, like this: NAME | PARENT Carnivora | Mammalia Canidae | Carnivora Canis | Canidae lupus (the wolf) | Canis I also broke that table into separate tables listing only orders, families, genera, species, etc., which I can then display via joins. I haven't yet figured out which methid is going to work best. I think I'd like to make a content management system, possibly modeled after Wikipedia, though I'm also looking at the Tree of Life website at http://tolweb.org/tree/phylogeny.html They use a recursive array technique called Edge Representation, which is discussed about halfway down this page: http://www.phyloinformatics.org/pdf/7.pdf Another possible guide is the Animal Diversity Web - http://animaldiversity.ummz.umich.edu/site/about/technology/index.html - which uses something called Mousetrap and TaxonDB. This is all new and very confusing to me. Making things even more confusing, I read that XML can be used in lieu of databases, and at least one reference seems to suggest that it's the superior choice. So, before I get in any deeper, I'd like to ask about the differences between XML and MySQL. What are the pros and cons, and which would be better for an animal kingdom database? Or could I use both at the same time? I'm new to XML, too, but it looks like it might not be too complex. But it's hard to envision how this all fits together. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which Engine to use...
it is because of the dollar sign that it works so much quicker. Scott Hamm wrote: I'm now trying to learn engines in MySQL. When I migrated from M$ SQL to MySQL to learn the migration process and executed the following: SELECT * FROM QA LEFT JOIN Batch ON Batch.QAID=QA.ID LEFT JOIN QAErrors ON QAErrors.ID=Batch.QEID WHERE QA.ID http://QA.ID BETWEEN '106805' AND '107179' ORDER BY QA.ID http://QA.ID; M$ SQL executed and brought up result in 2 seconds where MySQL took 801 seconds and where Batch datalength is around 18.5 MB, QAErrors is around 464KB and QA is around 3.5MB Which engine should I use and should I apply to all these tables or? Batch/QAErrors/QA is most frequent used in database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing huge amount of binary data
Per Jessen wrote: 3. Again, as you can understand, I want to minimize the cost here. If you don't think I can use mysql, do you think Microsoft SQL server is good enough for this task? I don't think so, no. what are you basing this on? SQL Server is a truly great database package, don't let some foolish bias blind you to the fact that it is professional grade software. i like MySQL as much as the next guy, but its being open source does not mean you should ignore the facts. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: offer a solution ?
depending on what you use this information for, you might want to keep two tables, one with the current information, and one of the history. it would keep the current one relatively small, but allow for looking up historical data if necessary. nephish wrote: Hey there, i have been messing around with MySQL for a little bit now. I have a question about how i might could do something. i am writing a database to track what a bunch of electric monitors are doing. the status of the monitor changes almost daily. i need access to each monitor, when it changed, and i also need to track its history. Easy enough. but if i update a row in a table, i loose the old info. So i kinda cannot create a table referenced by a key of monitor number... i think that the easiest way, would be to create a seperate table for each monitor... but there are almost a thousand monitors... will that become a nightmare ? can MySQL handle that kind of thing? a thousand tables in one database ? i have to be able to access each change in its history. how should i set this up? thanks for any suggestions. shawn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
