mysql_fix_privilege_tables doesn't fix privilege tables [50 character or so descriptive subject here (for reference)]
Description: When I start up mysqld (4.1.0-alpha), I get the message in the error log: mysql.user table is not updated to new password format: Disabling new password usage until mysql_fix_privilege_tables is run I *HAVE* run mysql_fix_privilege_tables. At least 4 times. I have a whole bunch of privilege columns including super_priv. Every time I run it it corrupts the (select-only web server) users I created since upgrading (from 3.23.56 to 4.1.0-alpha) by handing out privileges I don't want these users to have, like CREATE TEMPORARY TABLES. What's the point of SHOW PRIVILEGES? There are a lot of privilege columns in mysql.user that don't show in SHOW PRIVILEGES, yet I can grant them and display them in SHOW GRANTS FOR ... . I thought it was to keep the list of privileges in sync with what the server actually supports? How-To-Repeat: Start mysqld. Get warning message. Run mysql_fix_privilege_tables. Repeat ad nauseum. Fix: Is the mysql_fix_privilege_tables that came with 4.1.0-alpha really up to date? Submitter-Id: submitter ID Originator:Gordon Burditt Organization: MySQL support: none [none | licence | email support | extended email support ] Synopsis: mysql_fix_privilege_tables doesn't fix the privilege tables Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-4.1.0-alpha (FreeBSD port: mysql-server-4.1.0_1) Server: /usr/local/bin/mysqladmin Ver 8.40 Distrib 4.1.0-alpha, for portbld-freebsd4.7 on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.1.0-alpha-log Protocol version10 Connection mysql.burditt.org via TCP/IP TCP port3306 Uptime: 2 hours 25 min 6 sec Threads: 1 Questions: 385428 Slow queries: 7 Opens: 35 Flush tables: 1 Open tables: 29 Queries per second avg: 44.272 C compiler:2.95.4 C++ compiler: 2.95.4 Environment: System: FreeBSD hammy.lonestar.org 4.7-RELEASE FreeBSD 4.7-RELEASE #1: Wed Feb 19 01:29:17 CST 2003 [EMAIL PROTECTED]:/scratch5/i386-obj/usr/src/sys/HAMMY i386 Some paths: /usr/local/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.4 20020320 [FreeBSD] Compilation info: CC='cc' CFLAGS='-O -pipe -O3 -fno-omit-frame-pointer' CXX='cc' CXXFLAGS='-O -pipe -O3 -fno-omit-frame-pointer -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' ASFLAGS='' LIBC: -r--r--r-- 1 root wheel 1218496 Dec 3 2002 /usr/lib/libc.a lrwxrwxr-x 1 root wheel 9 Dec 3 2002 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 574916 Dec 3 2002 /usr/lib/libc.so.4 Configure command: ./configure '--localstatedir=/var/db/mysql' '--without-debug' '--without-readline' '--without-libedit' '--without-bench' '--without-extra-tools' '--with-libwrap' '--with-mysqlfs' '--with-vio' '--with-low-memory' '--with-comment=FreeBSD port: mysql-server-4.1.0_1' '--enable-thread-safe-client' '--enable-assembler' '--with-berkeley-db' '--with-openssl' '--prefix=/usr/local' '--build=i386-portbld-freebsd4.7' 'CFLAGS=-O -pipe -O3 -fno-omit-frame-pointer' 'CXX=cc' 'build_alias=i386-portbld-freebsd4.7' 'CC=cc' 'CXXFLAGS=-O -pipe -O3 -fno-omit-frame-pointer -felide-constructors -fno-rtti -fno-exceptions' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use LAST_INSERT_ID() or mysql_insert_id() ?
I know this is an old query, but still, problems . . . I've tried these both, but something is amiss - probably my interpretation. Every time I try to use LAST_INSERT_ID() I get a complaint that the query has a problem. E.G., $qry=$qry.;select LAST_INSERT_ID(); will blow up on me every time USE ONE QUERY AT A TIME. Since last_insert_id() depends on the connection involved, no query done on a different connection between your insert and getting last_insert_id() will affect the result you get. I've evaluated $qry, and it works just fine until I append the piece that should let me recover the auto increment value that was created during the insert process. One query at a time. Gordon L. Burditt - 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: help me out here guys... you gotta have a primary key
So, one of my associates has made a linking table (some people also call it intersection table, cross tab table, but i believe that the propper way to model a many to many relationship is via a linking table). in the linking table, there is no primary key defined. Let us suppose you wish to define a many-to-many relationship between people and telephones (and it IS a many-to-many relationship: most people have at least a home and a work phone (and some also have a home cellphone and a work cellphone), and most families do not have a separate phone for each family member). You have a 'people' table with names, birth dates, employee number, etc. The primary key is the employee number (id int not null auto_increment). (Hint: Social Security numbers are NOT guaranteed unique, even if everyone relevant has one.) You have a 'telephone' table with a telephone number, type (landline or cellular), owner (company or someone else), location, and an artificial primary key (id int not null auto_increment). Why not use the telephone number as primary key? Some extensions, such as the one in the lobby for guests, or the ones in elevators, can't take incoming calls and don't HAVE numbers in the conventional sense. So how do you represent the relationship? Add another table (linking table is a reasonable description) containing two columns, the primary key of the person and the primary key of the telephone. So what is a suitable primary key for THIS table? It's a key on both columns. The same person can't have the same telephone more than once, so requiring uniqueness prevents multiple identical rows that don't make any sense. Should the key be (person_id, telephone_id) or (telephone_id, person_id)? This depends on which index you anticipate the SQL query to need most often. Now, to those who like bringing up the foreign keys as some sort of alternative: I don't believe it. Having foreign keys gets you referential integrity. It does NOT eliminate the need for the linking table, nor does it change what's in the table, to represent a many-to-many relationship. I challenge anyone to demonstrate otherwise. I believe that every table must have a primary key. It is absolutely essential, otherwise you'll get tons of problems including redundancy, and inconsistency. Yes, you could record the relationship between me and my work telephone several times, if the primary key didn't prevent that. I don't believe you have to have a SINGLE-FIELD primary key on every table. However, my associate believes that our coding will ensure that such problems will be avoided and that it's okay for a table to have no primary key defined. I totally disagree. Even if our code is perfect, a primary key must be defined. So, am i correct in being concerned, or am i just being close minded? If, i'm totally wrong, in what situations is it a good idea, okay, or benificial to not have a primary key defined for a table? I can't think of a good reason to not have a primary key. Gordon L. Burditt - 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 user priviliges
I Have a question relating to setting up users for our customers on a shared server environment. I can not find this answer in the documentation so I am trying here. Is it standard practice to give the user of the database (site owner) one set of permissions which I figure should be: select, insert, update,delete, create, drop On database to user@localhost I would include alter and index in this list. You can accomplish the equivalent of alter with drop and create but alter can be a LOT easier to work with. Alter can be used to subvert table and column privileges but here it seems that only per-database privileges are used. I would also include references if it actually did anything. Essentially, the owner of a database gets all the per-database privileges on his own database. You would probably NOT want to give privileges such as reload, shutdown, process, and file, as these aren't limited to one database. and create one for them to put into their php pages that call the database from the website (which would be the one their visitors are using)? like an IUSER_ but for mysql? If so, what should the bare minimum permissions be for this username. I think insert, update, delete and select, but I am not sure. The bare minimum is: select . Some pages read the database only and make no changes in it. Normal privileges for a web page that uses the data but does not alter the schema (no temporary tables) would be: select, insert, update, delete, on one specific database. You might not want to grant delete if the changed data is handled like a log and you don't want the web page ever deleting previously logged data. If you don't want the site owner constantly asking for changes in the setup, you'd probably give the web-page login the delete privilege. Gordon L. Burditt - 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: Deep Nesting (sql)
The tables will be used to enable users to enter comments concerning what they believe happens at individual conjunctions. For example, in astrology, let's say one enters a comment of what happens when the Moon enters Scorpio. These comments can then be called by other users and rated with an accreditation system similar to Amazon's rating of books (and those who rate them). The problem occurs with granulation. Let's say, instead, that our above example is when the Moon enters Scorpio while the Sun is in the fifth degree of Aries and Mars is in the twentieth degree of Gemini while the natal chart reflects that this nativity has an ascendant at the seventh degree of Cancer, etc. Indeed, it's actually far more involved than that, because I'd like to cross-reference other metaphysical systems (Tzolkin, I Ching, etc.) several of which have nativities as well as transient elements. I can see tables literally in the thousands (or much higher) with no way to avoid this proliferation. Any suggestions? Ok, here's one way I see to organize this: First, create a table with all the possible single events (e.g moon enters Scorpio) (is varchar(100) enough for the description?) and an artificially created event ID (id int not null auto_increment primary key). Next, create a table for all the combinations (it is probably more appropriate here to create entries for combinations actually USED, not all possible combinations, as a table for all possible combinations may rapidly exceed the amount of disk storage ever manufactured). I am not sure what you'd need here beyond just a combination ID, but there might be some kind of composite rating of the event. Next, create a table to relate events to combinations (a many-to-many relationship). This combination happens when this event, that event, and a third event happens. This table has two columns, event ID and combination ID, and lists the events that make up a combination. For example, if a combination C happens when three events, X, Y, and Z happen, then you'd have three rows: Event IDCombination ID X C Y C Z C The combination (event ID, combination ID) should be unique. Now, create a table for the people making comments. This would include a commenter ID (primary key), some kind of text name for that person, and possibly a password they use to make comments under their name. This might include an email address and billing information. Next, create a table for the comments. This contains a combination ID for what they are commenting on, the commenter ID, and the comment made. Ok, that's 5 tables. I can't see the number of tables growing even if astrology suddenly discovered 27 more planets and 5 new signs of the zodiac. The contents of the tables would get bigger, but there wouldn't be more of them. I'm not that familiar with Amazon's rating system so I can't comment on what additional tables are needed for that. You'd need a SQL query with a multi-way join to get the description of the combination, the commenter name, and the comment made, but this shouldn't be hard or slow with appropriate indexes on the tables. Gordon L. Burditt - 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 query for next unique ID
Does anybody know if there is a function to retrieve the next unique ID number for a record to be made in the future? I believe you can reliably allocate sequence numbers from a sequence table (a table used only to dole out sequence numbers) like this: create table sequence ( seq int not null auto_increment primary key); (depending on intended use, you may need to change the size of the integer type, or use an unsigned type). When you wish to get a sequence number, issue these three queries: #1: insert into sequence values (null); #2: select last_insert_id(); Fetch the single-row result from this and put it in variable $s, which gets substituted into the following query before sending it: #3: delete from sequence where seq = $s; Now use $s as your sequence number to insert into another table. Note that this still works if several clients try this sequence of queries on different connections, WITHOUT transactions and WITHOUT any explicit locking between the queries, but with arbitrary interleaving between queries by different clients. Query #1 establishes the sequence number (implicit locking during that query prevents handing out duplicate numbers). Query #2 fetches it, and since last_insert_id() is connection-specific, nothing another connection does will affect the result. Query #3 cleans up the sequence table. Since only one client was handed the sequence number for this row, only one client will try to delete it. Normally the table will only have entries for clients executing between query #1 and query #3, or clients that bombed between query #1 and query #3 (thus, during a lull in activity, the steady state of the sequence table should be empty). Gordon L. Burditt - 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 send multiple SQL statements using C API mysql_query?
I would like to send multiple SQL statements using the C API mysql_query. I have a large string with 20 SQL statements. When I call mysql_query with that string, only the first one is processed. Is there a way to do what I'm doing without separating the statements into individual calls to mysql_query? I beleive this is not possible. If it were, it would give lots of people many hours of headache. Imagine a badly written script, where you can escape out from the original query, like update articles set author='$author' If you can make several statements with one query, you could make $author = whatever'; drop database It's STILL dangerous even without being able to insert a separate query. Granted, with a select the attacker could probably only dump your entire database, using something like $author = whatever' or 1 If you have a MySQL-driven web page and putting special characters like single quotes into an input field can draw SQL errors, you've got a BIG problem, unless you really don't care about having your site and/or database hacked (In which case I'd prefer you take it down, as I don't want SPAM relayed through your site showing up in my mailbox.) Quote your input properly (as with mysql_escape_string()) or validate it before feeding it to MySQL. Also, be very careful about allowing stuff INTO your database which will be blatted out unchecked into a web page. It's easy to insert malicious Javascript or an offensive banner ad into even a moderately long text field, like one intended for a Subject: line. Gordon L. Burditt - 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: OT: Securing username and password in script file
I did not mean for this to be an off-topic PHP post, what I was noodling about here was a mySQL means to provide a more secure access for scripting languages like Perl, Python and PHP - which end up with insecure username and password config files all over the Internet. There's a problem here: you need to have whatever information is needed to access MySQL (or any other database) in those files. If someone else on the same machine can get that information, he can also access the database. It doesn't matter if this information is encrypted for transmission - every client knows how to do that. I don't know what this mechanism is - I'm not even sure I can think it through at the moment - but something like checking a server variable like http_server, or maybe even http_document_root and only allowing the login if the script was being run from the appropriate location. Perhaps limiting a login for a specific username only from a specific document_root? There is no known method of sucking down client-side information (like UNIX user name/id, environment variables, current directory, etc.) from the server end of an Internet-domain socket. The protocols just won't do it. The client end has to supply this information. This means that it can lie about it. You might get a UNIX userid out of UNIX-domain sockets (and all you will get if you're running mod_php is the userid of the web server), but you won't get other information like virtual host name, document root, or other such information, and this depends on the MySQL server and the web server being on the same machine (often undesirable for security and/or performance reasons). Having a tamper-proof client linked in with a hostile program and retaining tamper-proof-ness is very, very difficult, especially if the client is written in C. It is less hard for a scripting language like PHP (but all bets are off if the attacker is also allowed to use C - a problem with using secure clients is that even one insecure client can ruin all the security). Having the client be open-source makes the problem even worse: it would be rather easy to change where it gets the information it sends the server and compile a hacked client which destroys security in the shared-webserver scenario. Besides, there are plenty of times I want to access MySQL from a machine that doesn't even HAVE a web server. The problem with checking for username@localhost - which is what most installations do, at least through phpMyAdmin on CPANEL hosts - is that once your username and password are available, you are vulnerable from any other shared host on the same server. Their host is also localhost. Your script has to have the information necessary to access the database (this is not unique to MySQL). If other virtual hosts are able to get this information, you're screwed. Adding one more piece of information that can be obtained just as easily as the other things doesn't help the situation any. Is there a way to see the value of localhost from within mySQL? What good is 127.0.0.1 (or /tmp/mysql.sock) going to do? You could get the client to tell the server the virtual host name (perhaps tack it onto the end of the user name), but that won't prevent someone else from passing bogus information. Gordon L. Burditt - 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
