Re: Root
Read http://www.mysql.com/doc/en/Command-line_options.html Start mysqld with the --skip-grant-tables option then change your root password again, then restart mysqld, this time with out the --skip-grant-tables hope that helps. Sherwin T. Ang Tridel Technologies Incorporated PS: you don't have to reinstall mysql for just this. - Original Message - From: FT [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 03, 2003 1:21 PM Subject: Root Hi everyone I am quiet new to DB and MySql I must have made a big mistake by changing the root @ localhost password via phpMyAdmin and now it is impossible to connect from neither the mysql prompt nor phpMyAdmin... Does anyone know how to recover this problem...I did try to reinstall MySql after uninstalling it and rebooted the server but no luck so far... Thanks for the hand Fabrice -- 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: Root
Hi there, First shutdown the running server. mysqladmin shutdown or /etc/init.d/mysql.server stop or /etc/init.d/mysqld stop (depending on your Linux distribution) Then restart the mysql server with option --skip-grant-tables like this. safe_mysqld --skip-grant-tables You can then login without password. And then you reset your password with the update command like this: UPDATE user SET password='' WHERE user='root'; Restart the server again. Set a new password with mysqladmin like this: mysqladmin -u root password xxx mysqladmin -u root flush-privileges (xxx is your new password) That should be it. Mysql has a nice manual by the way where it is all described detailed, comes in very handy to have such a print close, just in case ;-) Best regards Nils Valentin Tokyo/Japan 2003 8 3 14:21FT : Hi everyone I am quiet new to DB and MySql I must have made a big mistake by changing the root @ localhost password via phpMyAdmin and now it is impossible to connect from neither the mysql prompt nor phpMyAdmin... Does anyone know how to recover this problem...I did try to reinstall MySql after uninstalling it and rebooted the server but no luck so far... Thanks for the hand Fabrice -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Root
to mention one step before re-installing anything and skipping user handling of mysql... initially, there's no root passwort set at all. so you came into the server without any changes to pma configuration or any special parameter to the mysql client. did you change that password in the phpmyadmin config.php file to the new one? or changed it to cookie authorization, so that you could enter your password yourself anytime you want to login? and for the mysql command line client: did you use it with the parameter -p to state that you want to use a password? mysql -h localhost -u root -p then mysql asks you for the root password on localhost... just a thought -yves -Ursprüngliche Nachricht- Von: FT [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Sonntag, 3. August 2003 07:21 Betreff: Root Hi everyone I am quiet new to DB and MySql I must have made a big mistake by changing the root @ localhost password via phpMyAdmin and now it is impossible to connect from neither the mysql prompt nor phpMyAdmin... Does anyone know how to recover this problem...I did try to reinstall MySql after uninstalling it and rebooted the server but no luck so far... Thanks for the hand Fabrice -- 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]
ansi characters in Fulltext search
Does newer versions MySQL support words with Scandinavian letters like æ, ø, å, ö, ä etc in full-text searches? The server I'm on runs 3.23.53a. If so, from what version was it supported? And where can I find documentation on it? Can't find anything in the full-text manual.. Best regards, Kevin Østmark [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NON web based client server applications
* KB i am a new-bie, should i even think of using mySQL in the following scenario ? i have a situation in mind where two or three ( sometimes concurrently ) people will be making invoices, on three computers in different locations on lan. mysql is perfect for such a scenario, and you have a number of programming laguages and server platforms to choose between: URL: http://www.mysql.com/doc/en/Clients.html URL: http://www.mysql.com/doc/en/Which_OS.html Because of the lightweight usage you are planning, you could use almost any old machine for a server, take a look in the back of the closet. :) You could also install the mysql server on an existing machine in your LAN, it does not require much resources. what front end tools should i think of using for dataentry clients ? You say NON web based in the subject, but I guess that is because you are planning on running this on LAN only? IMO, using web based clients even for LAN only appplication is a time-saver. You need a server in any client/server configuration, if you don't go for the web server, you will still need some kind of application server. And just to make it clear: the mysql server can not act as an application server, it is only a database server. A web server, on the other hand, more or less is a generic application server (at least if it's 'enabled' with something, like php/perl/python). I would use HTML/PHP for the front-end, and probably Python for any background processing, but that's me. There are many other options. I don't know if you have done any programming before, or even if you are planning to start now. There could be existing tools for easy creation of dataentry forms for non-programmers, but I don't know of any. If you are beginning programming, HTML/PHP is a relatively easy path. (Python too, for that matter.) how can i print invoices from the database, on a specific printer only, when somebody ( with a username and pswd ) gives a command from anywhere on the Lan / intranet. This depends on the client language you choose and the OS and setup of your server. For instance, in a HTML/PHP scenario, you would have a local web server on your lan, and the same server (or a different one) could run the mysql server daemon. The clients connect to the server using web browsers, and after login they can press a 'Print' button. This button triggers a script on the web server, and this script queries the mysql server and formats/prints the invoice to any predefined printer accessible from that local web server. If you do not go for a HTML solution, you will need an application server for this, a server process accepting requests from your clients, querying the mysql server, formatting and printing the invoices. Good luck! :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Restoring from a mysqldump
I have a file produced by mysqldump and I need to restore it. Can someone point me toward the instructions on how to do this? Todd -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restoring from a mysqldump
* Todd Cary I have a file produced by mysqldump and I need to restore it. Can someone point me toward the instructions on how to do this? URL: http://www.mysql.com/doc/en/mysqldump.html Just before the user comments: You can read this back into MySQL with: mysql database backup-file.sql You may need to add host and/or user parameters. See also: URL: http://www.mysql.com/doc/en/mysql.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ansi characters in Fulltext search
Sunday August 03 2003 15:47, =?iso-8859-1?Q?Kevin_=D8stmark?= wrote to All: is Does newer versions MySQL support words with Scandinavian letters is like is , , , , etc in full-text searches? Those have nothing to do with ANSI. KS KARICO Business Services Toronto, ON Canada http://www.karico.ca Get The Pocket Pro now!: http://www.pocketproshop.com ... Eat prune yogurt for that get up and go feeling. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested SELECT statements problem
Thanks for your input! First, I removed the quotation marks into the sub-query in order to fix syntax. Then, I tried to use different alliases from the main query. However, this doesn't work. I mean that mySQL return a syntax error. I checked the server version and it is 3.23.56. As someone else noticed, nested queries are not supported in mySQL until version 4 or so. Therefore, I assume that it would not work for me. Since I'm not the administrator of the mySQL server, I'm not able to upgrade it. So, I'm stucked with version 3.23.56. Now, someone suggested me to replace subqueries with something like this: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html . However, I'm not able to figure out how to change my queries. Any idea? Le dimanche, 3 aoû 2003, à 01:07 Canada/Eastern, Lin Yu a écrit : One problem is that you have quoted your sub-query, which makes it to return a constant string. Another problem I saw in your code is that you used the same aliases for tables in the query and in the sub-query. In such case, the SQL parser would take all of them to refer to the same table, probably the ones in the query. My suggestion would be to use different aliases in the query and sub-query for the same table. That way, in each of your where-clause, the SQL parser will know exactly which table reference you want. Also, be sure that your data is good so that your sub-query indeed returns some records to be matched; or otherwise the query will not return anything. Hope this helps. Lin -Original Message- From: Pascal Délisle [mailto:[EMAIL PROTECTED] Sent: Saturday, August 02, 2003 11:46 PM To: [EMAIL PROTECTED] Subject: Nested SELECT statements problem Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre book.IDLivre AND aut.IDAuteur ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur aut.IDAuteur AND book.IDLivre ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- 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]
Granting privileges
I have installed Red Hat 9 and it installs MySQL. After logging on as root, I did the following: mysql mysql use mysql; mysql grant all - on * - to todd indentified by 'my_password' - with grant option; I then log out of mysql and log out as root. Logged in as "todd" I type the following: mysql -u todd -p I type in my password and I get the following error: Acess denied for [EMAIL PROTECTED] When I was in mysql as root, "select * from user" has "todd" listed. What have I missed? Todd -- inline: NewLogo.gif
encrypt() call on Win32
While I found some information on this subject: http://groups.google.com/groups?q=mysql+win32+encrypthl=enlr=ie=UTF-8oe=UTF-8selm=be3g14%24ulu%241%40FreeBSD.csie.NCTU.edu.twrnum=1 I can't make much sense of it. Is there a *clear* way to have encrypt() call working on mySQL running on Win32? thanks
Slowing brute force attack
Is there an option for mysqld to add a sleep() call after each failed connection request, before reporting to the client (to slow down brute force attack)? I glanced at the manual and there doesn't seem to be one. If there isn't, would it be a good idea? If yes, I could probably submit a patch. This feature should be trivial to implement, I think. -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Granting privileges
You still need to flush the privileges. Changes in permissions are not automatically committed. Also you should add the host to the username. grant all on *.* to [EMAIL PROTECTED] identified by 'some_password' with grant option; flush privileges; Jamie On Sun, 2003-08-03 at 09:52, Todd Cary wrote: I have installed Red Hat 9 and it installs MySQL. After logging on as root, I did the following: mysql mysql use mysql; mysql grant all - on * - to todd indentified by 'my_password' - with grant option; I then log out of mysql and log out as root. Logged in as todd I type the following: mysql -u todd -p I type in my password and I get the following error: Acess denied for [EMAIL PROTECTED] When I was in mysql as root, select * from user has todd listed. What have I missed? Todd -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested SELECT statements problem
Finally, I solved my problem by creating a temporary table that holds the result of the subquery. So, it looks like this: CREATE TABLE livreTemp (IDLivre int(11)); INSERT INTO livreTemp (IDLivre) SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$lastName%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre; SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livreTemp book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre= book.IDLivre AND aut.IDAuteur=ecr.IDAuteur; DROP TABLE livreTemp; The only problem I see is when there are concurrent access to the table livreTemp, e.g. when there are multiple simultaneous requests of that kind. Is there a better way for achieving this under mySQL 3? Le dimanche, 3 aoû 2003, à 12:41 Canada/Eastern, Pascal Délisle a écrit : Thanks for your input! First, I removed the quotation marks into the sub-query in order to fix syntax. Then, I tried to use different alliases from the main query. However, this doesn't work. I mean that mySQL return a syntax error. I checked the server version and it is 3.23.56. As someone else noticed, nested queries are not supported in mySQL until version 4 or so. Therefore, I assume that it would not work for me. Since I'm not the administrator of the mySQL server, I'm not able to upgrade it. So, I'm stucked with version 3.23.56. Now, someone suggested me to replace subqueries with something like this: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html . However, I'm not able to figure out how to change my queries. Any idea? Le dimanche, 3 aoû 2003, à 01:07 Canada/Eastern, Lin Yu a écrit : One problem is that you have quoted your sub-query, which makes it to return a constant string. Another problem I saw in your code is that you used the same aliases for tables in the query and in the sub-query. In such case, the SQL parser would take all of them to refer to the same table, probably the ones in the query. My suggestion would be to use different aliases in the query and sub-query for the same table. That way, in each of your where-clause, the SQL parser will know exactly which table reference you want. Also, be sure that your data is good so that your sub-query indeed returns some records to be matched; or otherwise the query will not return anything. Hope this helps. Lin -Original Message- From: Pascal Délisle [mailto:[EMAIL PROTECTED] Sent: Saturday, August 02, 2003 11:46 PM To: [EMAIL PROTECTED] Subject: Nested SELECT statements problem Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre book.IDLivre AND aut.IDAuteur ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur aut.IDAuteur AND book.IDLivre ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- 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: Adv. Mysql query
Mattias, Your 'blueprint' table looks good. This where itemID corresponds to a item in the master table and the componentID ALSO referes to items in the Master table??? Correct. This allows 'recursive' components. For example, Item 3 contains components 1,3,4 and component 4 is actually made of components 7 8. In your 'blueprint' table this would be shown as: itemID componentID 31 33 34 47 48 Then I could just do a query for all componentIDs relating to the itemID, taken from a query on the master table. Yes, but if components can contain components then you have to do the query RECURSIVELY. This is explained by Jim's post. It is easiest to do in programming code rather than trying to write a select statement to do it all at once. For Example: For item 3: Get all components that make up item 3 Then for each component, see if the components has components... and so on. I also have suppliers or different items/components, I guess it would be wise to solve that many-many relationship In the same way. A new table with itemID - supplierID relations? Yes, often many to many relationships have an intermediate table like this. Regards, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slowing brute force attack
David, Is it possible to cause the client to wait by the server issuing a SLEEP? What if the client just disconnectes and immeadiately reconnects? Say you use a sleep of 2 seconds. If your server can accept 100 connections at once then 1 client can issue 100 login attempts and wait 2 seconds for the result and then do it all again. I don't think there is a way out of this problem. I would be interested in other answers. Regards, Andy. David Garamond wrote: Is there an option for mysqld to add a sleep() call after each failed connection request, before reporting to the client (to slow down brute force attack)? I glanced at the manual and there doesn't seem to be one. If there isn't, would it be a good idea? If yes, I could probably submit a patch. This feature should be trivial to implement, I think. -- dave -- 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: Nested SELECT statements problem
- Original Message - From: Pascal Délisle [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 03, 2003 1:02 PM Subject: Re: Nested SELECT statements problem Finally, I solved my problem by creating a temporary table that holds the result of the subquery. So, it looks like this: CREATE TABLE livreTemp (IDLivre int(11)); INSERT INTO livreTemp (IDLivre) SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$lastName%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre; SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livreTemp book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre= book.IDLivre AND aut.IDAuteur=ecr.IDAuteur; DROP TABLE livreTemp; The only problem I see is when there are concurrent access to the table livreTemp, e.g. when there are multiple simultaneous requests of that kind. Is there a better way for achieving this under mySQL 3? Can you dynamically generate a unique name for your temporary table? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested SELECT statements problem
Since I'm new to mySQL, could you point me how to create temporary tables dynamically? Le dimanche, 3 aoû 2003, à 15:32 Canada/Eastern, Jim McAtee a écrit : - Original Message - From: Pascal Délisle [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 03, 2003 1:02 PM Subject: Re: Nested SELECT statements problem Finally, I solved my problem by creating a temporary table that holds the result of the subquery. So, it looks like this: CREATE TABLE livreTemp (IDLivre int(11)); INSERT INTO livreTemp (IDLivre) SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$lastName%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre; SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livreTemp book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre= book.IDLivre AND aut.IDAuteur=ecr.IDAuteur; DROP TABLE livreTemp; The only problem I see is when there are concurrent access to the table livreTemp, e.g. when there are multiple simultaneous requests of that kind. Is there a better way for achieving this under mySQL 3? Can you dynamically generate a unique name for your temporary table? -- 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: Nested SELECT statements problem
The only problem I see is when there are concurrent access to the table
livreTemp, e.g. when there are multiple simultaneous requests of that
kind. Is there a better way for achieving this under mySQL 3?
Yes, concurrent access would be a problem. I think I have the answer using a
single query (see below).
I used the ObjectStar RDBMS which has temp (TEM) and session (SES) table
types. Both were specific to the client (a private memory as opposed to a
all user memory). This was very useful, but I've found no mention of it in
the mysql documentation.
// the 2 step query that works...
CREATE TABLE livreTemp (IDLivre int(11));
INSERT INTO livreTemp (IDLivre)
SELECT book.IDLivre
FROM livre book, livreEcritPar ecr, auteur aut
WHERE aut.Prenom like '%$firstName%'
AND aut.Nom like '%$lastName%'
AND ecr.IDAuteur = aut.IDAuteur
AND book.IDLivre = ecr.IDLivre;
SELECT book.IDLivre, aut.Prenom, aut.Nom
FROM livreTemp book, livreEcritPar ecr, auteur aut
WHERE ecr.IDLivre= book.IDLivre
AND aut.IDAuteur=ecr.IDAuteur;
// workout what tables and fields are used...
livre book book
IDLivrebook id
livreEcritPar ecrbook written by
IDAuteur author id
IDLivrebook id
auteur aut author
IDAuteur author id
Prenom firstname
NomlastName
// create table sql...
drop table if exists livre;
create table livre (
IDLivre int(11),
title varchar(50),
PRIMARY KEY (IDLivre)
);
drop table if exists livreEcritPar;
create table livreEcritPar (
IDAuteur int(11),
IDLivre int(11)
);
drop table if exists auteur;
create table auteur (
IDAuteur int(11),
Prenom varchar(50),
Nom varchar(50),
PRIMARY KEY (IDAuteur)
);
// create some test data...
insert into livre (IDLivre,title) values('1','howto: MySQL');
insert into livre (IDLivre,title) values('2','howto: PHP');
insert into livre (IDLivre,title) values('3','History of Tayport');
insert into livre (IDLivre,title) values('4','History of Perth');
insert into livre (IDLivre,title) values('5','British Politics v French
Politics');
insert into livreEcritPar (IDAuteur,IDLivre) values('1','1');
insert into livreEcritPar (IDAuteur,IDLivre) values('1','2');
insert into livreEcritPar (IDAuteur,IDLivre) values('1','3');
insert into livreEcritPar (IDAuteur,IDLivre) values('3','4');
insert into auteur (IDAuteur,Prenom,Nom) values('1','Matthew','Gold');
insert into auteur (IDAuteur,Prenom,Nom) values('2','Jim','Smith');
insert into auteur (IDAuteur,Prenom,Nom) values('3','Scott','Another');
// workout what the query has to return...
select all book.id's
from book, book written by, author
where author.first_name like ...
and author.last_name like ...
and book written by.id = author.id
and book.id = book written by.id
select book.id, author.first_name, author.last_name
from temp, book written by, author
where book written by.id = book.id
and author.id = book written by.id
// okay, but it in plain english?
select book.id, author.first_name, author.last_name
where author.first_name like ... and author.last_name like ...
// thoughts...
we don't need to look at the livre (book) table, because book id exists in
the livreEcritPar (book written by) table. now our query is just concerned
with two tables.
livreEcritPar (book written by)
and,
auteur (author)
// solution 1: english version...
select book written by.book id, author.first_name, author.last_name
from author, book written by
where author.first_name like ...
and author.last_name like ...
and book written by.author id = author.author id;
// solution 1: french version...
select livreEcritPar.IDLivre, auteur.Prenom, auteur.Nom
from auteur, livreEcritPar
where auteur.Prenom like 'Matthew%'
and auteur.Nom like '%'
and livreEcritPar.IDAuteur = auteur.IDAuteur;
+-+-+--+
| IDLivre | Prenom | Nom |
+-+-+--+
| 1 | Matthew | Gold |
| 2 | Matthew | Gold |
| 3 | Matthew | Gold |
+-+-+--+
3 rows in set (0.02 sec)
// more thoughts...
what if he actually wanted the book title? we'll need to look at all three
tables
// solution 2: english version...
select book.title, author.first_name, author.last_name
from author, book written by, book
where author.first_name like ...
and author.last_name like ...
and book written by.author id = author.author id
and book.book id = book written by.book id;
// solution 2: french version...
select livre.title, auteur.Prenom, auteur.Nom
from auteur, livreEcritPar, livre
where auteur.Prenom like 'Matthew%'
and auteur.Nom like '%'
and livreEcritPar.IDAuteur = auteur.IDAuteur
and livre.IDLivre = livreEcritPar.IDLivre;
++-+--+
| title | Prenom | Nom |
++-+--+
| howto: MySQL | Matthew | Gold |
| howto: PHP | Matthew | Gold |
| History of Tayport |
Re: Nested SELECT statements problem
In the last episode (Aug 03), Pascal Dlisle said: Finally, I solved my problem by creating a temporary table that holds the result of the subquery. So, it looks like this: ... The only problem I see is when there are concurrent access to the table livreTemp, e.g. when there are multiple simultaneous requests of that kind. Is there a better way for achieving this under mySQL 3? Use the CREATE TEMPORARY TABLE command, which creates tables which are invisible to other client connections and are automatically dropped when the client exits. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
3.23.56 Replication Bug
I know the replication method is different in MySQL 4.0 then MySQL
3.23.x, but I have a bug that causes problems. The following query will
cause MySQL's logic to not properly read any of the following my.cnf
commands on slave servers:
replicate-wild-do-table
replicate-wild-ignore-table
replicate-ignore-table
I am sure it would make any of the match commands fail as well. This
query caused replication to stop on 2 of my slaves because it was in a
database that was not made to be replicated on the slave.
Below is the query:
UPDATE phpbb_search_results
SET search_id = 1929162862, search_array =
'a:7:{s:14:search_results;s:28:521, 577, 592, 593, 597,
598;s:17:total_
match_count;i:6;s:12:split_search;N;s:7:sort_by;i:0;s:8:sort_dir;
s:4:DESC;s:12:show_results;s:6:topics;s:12:return_chars;i:200;}
'
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5''
Below is the error log entry:
030803 16:24:33 Slave: connected to master
'[EMAIL PROTECTED]:3306', replication started in log
'blue-bin.013' at position 72475077
030803 16:24:33 Slave: did not get the expected error running query
from master - expected: 'Duplicate entry '%-.64s' for key %d' (1062),
got
'no error' (0)
030803 16:24:33 Slave: error running query 'UPDATE
phpbb_search_results
SET search_id = 1929162862, search_array =
'a:7:{s:14:search_results;s:28:521, 577, 592, 593, 597,
598;s:17:total_
match_count;i:6;s:12:split_search;N;s:7:sort_by;i:0;s:8:sort_dir;
s:4:DESC;s:12:show_results;s:6:topics;s:12:return_chars;i:200;}
'
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5''
030803 16:24:33 Error running query, slave aborted. Fix the problem,
and re-start the slave thread with mysqladmin start-slave. We stopped
a
t log 'blue-bin.013' position 72475077
030803 16:24:33 Slave thread exiting, replication stopped in log
'blue-bin.013' at position 72475077
Below is the binlog entry, keep in mind, it will ignore all of the
queries after this one, it will just not ignore a query with those
special characters in it:
# at 72475077
#030803 0:35:26 server id 1 Query thread_id=191959
exec_time=0 error_code=1062
use bluecustforum;
SET TIMESTAMP=1059888926;
UPDATE phpbb_search_results
SET search_id = 1929162862, search_array =
'a:7:{s:14:search_results;s:28:521, 577, 592, 593, 597,
598;s:17:total_
match_count;i:6;s:12:split_search;N;s:7:sort_by;i:0;s:8:sort_dir;
s:4:DESC;s:12:show_results;s:6:topics;s:12:return_chars;i:200;}
'
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5';
# at 72475467
#030803 0:35:29 server id 1 Query thread_id=191960
exec_time=0 error_code=0
SET TIMESTAMP=1059888929;
UPDATE phpbb_topics
SET topic_views = topic_views + 1
WHERE topic_id = 18;
# at 72475580
#030803 0:35:34 server id 1 Query thread_id=191961
exec_time=0 error_code=0
SET TIMESTAMP=1059888934;
UPDATE phpbb_sessions
SET session_time =
1059888934, session_page = 9
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5';
# at 72475755
#030803 0:35:34 server id 1 Query thread_id=191961
exec_time=0 error_code=0
SET TIMESTAMP=1059888934;
UPDATE phpbb_users
SET
user_session_time = 1059888934, user_session_page = 9
WHERE user_id =
8;
Below is the version info from mysqlbug
VERSION=3.23.56
COMPILATION_COMMENT=Official MySQL RPM
BUGmysql=[EMAIL PROTECTED]
# This is set by configure
COMP_ENV_INFO=CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium'
CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -feli
de-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
CONFIGURE_LINE=./configure '--disable-shared'
'--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static'
'--without-berkeley-db'
'--without-innodb' '--enable-assembler' '--enable-local-infile'
'--with-mysqld-user=mysql'
'--with-unix-socket-path=/var/lib/mysql/mysql.sock'
'--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr'
'--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share'
'--locals
tatedir=/var/lib/mysql' '--infodir=/usr/share/info'
'--includedir=/usr/include' '--mandir=/usr/share/man'
'--with-comment=Official MySQL RPM' '
CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6
-fno-omit-frame-pointer -felide-constructors
-fno-exceptions
-fno-rtti -mpentium' 'CXX=gcc'
nickg
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
do long delete/update and view progress?
Hi! Bennett == Bennett Haselton [EMAIL PROTECTED] writes: Bennett Is there any way to do a long update/delete on a table such that you can Bennett view the progress as the command runs, so that long before it's over, you Bennett have some idea of what the total running time will be? Bennett The way I did it was to write a perl script that takes the total range of Bennett values for the primary key field in the table, divides that range into Bennett chunks, and then runs the update/delete command on each chunk at a time, Bennett printing out when it's done that chunk. But that's a bit of a kludge. Is Bennett there a built-in way? For the moment this is not possible. We do have plans to add in MySQL 5.x a way for the server to give the client progress notifications for slow commands which could be used for progess bars etc, but this is still at least 3-5 months in the future. -- Michael Widenius [EMAIL PROTECTED] MySQL AB, CTO Helsinki, Finland Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date to Days Query
Mike, Sure, try this: UPDATE table SET days = TO_DAYS(exp_dte) - TO_DAYS(NOW()); As a side note, I recommend you make this calculation in whatever application this DB supports. Otherwise you will continue to need running a script to update the dates. Regards, A$ On Saturday, August 2, 2003, at 06:07 PM, Mike Blezien wrote: Hello, I need to update one of our mysql tables, which has about 60,000 entires and correct the amount of days remain on each data record. An example of one of the data entires is: memiddaysregdate expdate -- 625290 | 5 | 2003-07-15 | 2003-08-16 now the days should be 14 and not 5 days. I have been trying to update the table with a single SQL query but haven't come up with a way to do this.. I'm sure it's something simple but I can't seem to come up with it. What is the best way to accomplish this in a single query to update the entire table so all the days are accurate according to the expdate, and change the days so they are correct ?? thx's -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Web Hosting http://www.justlightening.net Tel: 1(985)902-8484 MSN: [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: Nested SELECT statements problem
What version of MySQL are you using? Regards, A$ On Saturday, August 2, 2003, at 11:45 PM, Pascal Délisle wrote: Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur = ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- 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: Granting privileges
Jamie - Two questions: 1) Does "grant all" give the user the rights to create a database. 2) What is the difference between "on * " and "on *.* " Todd Jamie Krasnoo wrote: You still need to flush the privileges. Changes in permissions are not automatically committed. Also you should add the host to the username. grant all on *.* to [EMAIL PROTECTED] identified by 'some_password' with grant option; flush privileges; Jamie On Sun, 2003-08-03 at 09:52, Todd Cary wrote: I have installed Red Hat 9 and it installs MySQL. After logging on as root, I did the following: mysql mysql use mysql; mysql grant all - on * - to todd indentified by 'my_password' - with grant option; I then log out of mysql and log out as root. Logged in as "todd" I type the following: mysql -u todd -p I type in my password and I get the following error: Acess denied for [EMAIL PROTECTED] When I was in mysql as root, "select * from user" has "todd" listed. What have I missed? Todd -- -- inline: NewLogo.gif
Re: ansi characters in Fulltext search
Forgot to copy the list ;-) Best regards Nils Valentin Tokyo/Japan 2003 8 4 07:45Nils Valentin : Hi Kevin, Correct me if I am wrong, but the sweddish character set is the default setting with mysql ;-). I was supposing that includes the most or all scandinavian characters. Best regards Nils Valentin Tokyo/Japan 2003 8 3 22:47Kevin stmark : Does newer versions MySQL support words with Scandinavian letters like , , , , etc in full-text searches? The server I'm on runs 3.23.53a. If so, from what version was it supported? And where can I find documentation on it? Can't find anything in the full-text manual.. Best regards, Kevin stmark [EMAIL PROTECTED] -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Granting privileges
Jamie - grant all on *.* to [EMAIL PROTECTED] identified by 'some_password' with grant all; flush privileges; That definitely works, but what I am not sure about is now -u todd -h localhost has all "Y" in the privileges; before only the first few had "Y". Why is that? Is that due to the "*.*"? Todd Jamie Krasnoo wrote: You still need to flush the privileges. Changes in permissions are not automatically committed. Also you should add the host to the username. grant all on *.* to [EMAIL PROTECTED] identified by 'some_password' with grant option; flush privileges; Jamie On Sun, 2003-08-03 at 09:52, Todd Cary wrote: I have installed Red Hat 9 and it installs MySQL. After logging on as root, I did the following: mysql mysql use mysql; mysql grant all - on * - to todd indentified by 'my_password' - with grant option; I then log out of mysql and log out as root. Logged in as "todd" I type the following: mysql -u todd -p I type in my password and I get the following error: Acess denied for [EMAIL PROTECTED] When I was in mysql as root, "select * from user" has "todd" listed. What have I missed? Todd -- -- inline: NewLogo.gif
Lowering memory usage?
Hi everyone, I was wondering if there's any way I can lower MySQL's memory useage? It often uses more than 5kb which I consider to much.. I'm guessing I should play with my.cnf or whatever it's called (located at C:/ and windows calls it cardnumber or something)... Thanks in advance... // DvDmanDT MSN: [EMAIL PROTECTED] Mail: [EMAIL PROTECTED]
installation of mysql 4.0.14 on windows xp
Dear Mysql-help, I am new to mysql server and trying to install the mysql server on my windows xp home edition. I have downloaded 4.0.14 zip file on my computer. I can unzip the file and it generates 10-15 files. When I do double click on the setup.exe it loads for 5 seconds and then stops. No window is displayed. No error message is generated. I thought may be I should try some other version. I downloaded older version 4.0.13 and same thing here. I downloaded 3.23.57 and same thing here. I am logged in as administrator. I can install other softwares without any problem. Can you please suggest any thing I should try to install the mysql server. Thanks very much for your help. Ashish _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Cache Issues, Continued
OK, I have a MySQL server running 4.0.12 on a 12 CPU Sun U4500 with 12GB of memory. With the query cache running, this machine would noticeably hiccup (just stop responding to requests) every so often and wouldn't squeeze more then 1000 queries per second or so as a result (heavily mixed OLTP work, with a lot of SELECT's and almost as many UPDATE's). Without the Query Cache (and no other tweaking), I'm now at over 2100 at the same time of day. Is there a problem with the query cache that causes MySQL to be far less scalable then just straight MySQL/InnoDB? Are there any thoughts on this? Is there a resource specifically for high performance/high load MySQL implementation/usage? Dylan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Granting privileges
Hi Todd, 2003 8 4 08:01Todd Cary : Nils - What is the difference between *.* and * ? Hi Todd I just double checked. There is no difference. Both work the same way. I originally thought that the first one wouldn't have worked, but I checked it now. However I believe that *.* is the prober syntax. Does Grant All give the user the rights to create a DB? The below command allows the user todd all privileges (CREATE,INSERT,DELETE, UPDATE etc.) . The with grant option will also allow the user todd to create new users. About the command itself, I just doule checked once more. If you specify only the username then a wildcard is insert for the hostname (%) which allows the user todd to be able to login from anywhere. make sure thats what you really want !! In any other case you could specify the user as '[EMAIL PROTECTED]' to limit it to only the one host. Best regards Nils Valentin Tokyo/Japan Many thanks Todd Nils Valentin wrote: Hi Todd, try this grant all - on *.* - to todd indentified by 'my_password' - with grant option; I believe you forgot the .* wich I added after grant all on... Best regards Nils Valentin Tokyo/Japan 2003 8 4 01:52Todd Cary : I have installed Red Hat 9 and it installs MySQL. After logging on as root, I did the following: mysql mysql use mysql; mysql grant all - on * - to todd indentified by 'my_password' - with grant option; I then log out of mysql and log out as root. Logged in as todd I type the following: mysql -u todd -p I type in my password and I get the following error: Acess denied for [EMAIL PROTECTED] When I was in mysql as root, select * from user has todd listed. What have I missed? Todd -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: installation of mysql 4.0.14 on windows xp
I just got it working by rebooting the computer. Thanks any way Ashish From: Ashish Gupta [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: installation of mysql 4.0.14 on windows xp Date: Sun, 03 Aug 2003 19:30:48 -0500 Dear Mysql-help, I am new to mysql server and trying to install the mysql server on my windows xp home edition. I have downloaded 4.0.14 zip file on my computer. I can unzip the file and it generates 10-15 files. When I do double click on the setup.exe it loads for 5 seconds and then stops. No window is displayed. No error message is generated. I thought may be I should try some other version. I downloaded older version 4.0.13 and same thing here. I downloaded 3.23.57 and same thing here. I am logged in as administrator. I can install other softwares without any problem. Can you please suggest any thing I should try to install the mysql server. Thanks very much for your help. Ashish _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Granting privileges
Hi Todd, 2003 8 4 08:17Todd Cary : Jamie - grant all on *.* to [EMAIL PROTECTED] identified by 'some_password' with grant all; flush privileges; Flush privileges is not necessary here I believe. Only when you update the privileges 'manually' with INSERT, UPDATE,DELETE Best regards Nils Valentin Tokyo/Japan That definitely works, but what I am not sure about is now -u todd -h localhost has all Y in the privileges; before only the first few had Y. Why is that? Is that due to the *.*? Todd Jamie Krasnoo wrote: You still need to flush the privileges. Changes in permissions are not automatically committed. Also you should add the host to the username. grant all on *.* to [EMAIL PROTECTED] identified by 'some_password' with grant option; flush privileges; Jamie On Sun, 2003-08-03 at 09:52, Todd Cary wrote: I have installed Red Hat 9 and it installs MySQL. After logging on as root, I did the following: mysql mysql use mysql; mysql grant all - on * - to todd indentified by 'my_password' - with grant option; I then log out of mysql and log out as root. Logged in as todd I type the following: mysql -u todd -p I type in my password and I get the following error: Acess denied for [EMAIL PROTECTED] When I was in mysql as root, select * from user has todd listed. What have I missed? Todd -- -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Root
Hi FT, 2003 8 4 09:24: Thanks Nils...good to have you around...in Tokyo This worked except for the UPDATE than was not recognized by MySql 3.23.36 but strangely all previous tables were still there and it looks like it is running again.. still worry about this UPDATE command ..but I am back on phpMyAdmin 4.3.0 as root @ localhost How do I change this one or do I need to keep it Well that depends if you use the account local (login on the same machine the server is running ) or not. See the Grant command and the usage of the mysql client for details. Here just a rough sample: mysql GRANT ALL ON *.* TO '[EMAIL PROTECTED]' indentified by 'my_password' with grant option; If the user doesn't need to create new users than you can leave with grant option. Best regards Nils Valentin Tokyo/Japan Thanks - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: FT [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, August 03, 2003 3:11 PM Subject: Re: Root Hi there, First shutdown the running server. mysqladmin shutdown or /etc/init.d/mysql.server stop or /etc/init.d/mysqld stop (depending on your Linux distribution) Then restart the mysql server with option --skip-grant-tables like this. safe_mysqld --skip-grant-tables You can then login without password. And then you reset your password with the update command like this: UPDATE user SET password='' WHERE user='root'; Restart the server again. Set a new password with mysqladmin like this: mysqladmin -u root password xxx mysqladmin -u root flush-privileges (xxx is your new password) That should be it. Mysql has a nice manual by the way where it is all described detailed, comes in very handy to have such a print close, just in case ;-) Best regards Nils Valentin Tokyo/Japan 2003 8 3 14:21FT : Hi everyone I am quiet new to DB and MySql I must have made a big mistake by changing the root @ localhost password via phpMyAdmin and now it is impossible to connect from neither the mysql prompt nor phpMyAdmin... Does anyone know how to recover this problem...I did try to reinstall MySql after uninstalling it and rebooted the server but no luck so far... Thanks for the hand Fabrice -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating Grant Tables on WinXP
Hi, I've installed 4.0.13 on a WinXP system. The service starts and stops just fine, but I cannot connect to the server, nor can I ping the 3306 port. I think the problem may be failure to complete the post-installation processing. The doc says to run installdir\scripts\mysql_install_db. However that file looks like a Unix shell script, not a Windows .bat file (and windows refuses to run it) Am I looking in the wrong place? Is there a Win script elsewhere? Do I have an even more basic problem? Thanks in advance! Regards, Terry Fuller [EMAIL PROTECTED] --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_info not very informative?
What is annoying about this is that if you use mysqlimport to put this data into the table, I get: testdb.testtable: Records: 3 Deleted: 0 Skipped: 0 Warnings: 2 So, where are the warnings coming from? Is there some public API, below the C API layer, that one can use to obtain this information? If I do an insert and lose information, and get absolutely no warning about the problem, and no way to determine that there was an information loss, then I could call that a bug. I keep looking for a mysql_warnings function, or maybe a mysql_really_info, and I am not seeing anything else to work with. One can do the data validation at the application level by re-fetching the data, but isn't this the most complicated and ugly way to deal with the problem? The database obviously has the facts. Hence the warning from mysqlimport. Is there a reason it must keep the information to itself? - ray On Saturday, August 2, 2003, at 5:24AM, Adam Fortuno wrote: Actually, MySQL doesn't normally give overrun cut-off information (best I know). Use MySQL 4.0.x on 10.2.6, and MySQL has always performed that way. Regards, A$ On Friday, August 1, 2003, at 05:37 PM, Ray Kiddy wrote: I am trying to insert data using the C API. Particulars: OS: Mac OS X 10.3 (7A179) MySQL: MySQL 4.1.0-alpha table type: tried both MyISAM and InnoDB I have a table: mysql describe testtable; ++-+---+--+-+- +---+ | Field | Type| Collation | Null | Key | Default | Extra | ++-+---+--+-+- +---+ | _PK| int(11) | binary| | PRI | 0 | | | first | char(3) | latin1_swedish_ci | YES | | NULL| | | second | char(3) | latin1_swedish_ci | YES | | NULL| | | third | char(3) | latin1_swedish_ci | YES | | NULL| | ++-+---+--+-+- +---+ 4 rows in set (0.00 sec) Note that the first, second, and third columns have 3 characters of space. When I do: printf(insert: %s\n, insert); int result = mysql_query(one, insert); printf(info: %s\n, mysql_info(one)); I get: insert: INSERT INTO testtable (_PK,first,second,third) VALUES (1,'AAAXXX','BBB','CCC') info: (null) insert: INSERT INTO testtable (_PK,first,second,third) VALUES (2,'DDD','EEE','FFF') info: (null) insert: INSERT INTO testtable (_PK,first,second,third) VALUES (3,'GGG','HHHXXX','III') info: (null) Note that the value in the first insert, 'AAAXXX', is too long to fit. As is 'HHHXXX' in the third insert. And indeed, I see: mysql select * from testtable; +-+---++---+ | _PK | first | second | third | +-+---++---+ | 1 | AAA | BBB| CCC | | 2 | DDD | EEE| FFF | | 3 | GGG | HHH| III | +-+---++---+ 3 rows in set (0.00 sec) So, why is mysql_info not giving me any information about the data loss that is going on here? Is that not information that might be of interest? Is there some other call I have to make that will prepare for the mysql_info call? The doc does not seem to indicate this, but one never knows. thanx - ray -- 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: Granting privileges
I did some experimenting and here is what I found: grant all - on * - to bugsbunny indentified by 'bugs - with grant option; After putting the above into MySQL as root, I signed on as "todd" with the appropriate PW and tried to get into mysql with mysql -u bugsbunny -p [using bugs as the PW]. I was refused [EMAIL PROTECTED] is refused. I noticed that all of the privileges were set to "N". Then I tried grant all - on *.* - to bugsbunny indentified by 'bugs' - with grant option; I was refused again with the same message however all of the privileges were set to "Y". Lastly, I tried - on *.* - to [EMAIL PROTECTED] indentified by 'bugs' - with grant option; I was able to gain access and all of the privileges were set to "Y". The "%" in the Host column does not appear to work for me, and there is something different about "*" and "*.*". Todd Nils Valentin wrote: Hi Todd, 2003 8 4 08:01Todd Cary : Nils - What is the difference between "*.* " and "* "? Hi Todd I just double checked. There is no difference. Both work the same way. I originally thought that the first one wouldn't have worked, but I checked it now. However I believe that *.* is the prober syntax. Does Grant All give the user the rights to create a DB? The below command allows the user todd all privileges (CREATE,INSERT,DELETE, UPDATE etc.) . The "with grant option" will also allow the user todd to create new users. About the command itself, I just doule checked once more. If you specify only the username then a wildcard is insert for the hostname (%) which allows the user todd to be able to login from anywhere. make sure thats what you really want !! In any other case you could specify the user as '[EMAIL PROTECTED]' to limit it to only the one host. Best regards Nils Valentin Tokyo/Japan Many thanks Todd Nils Valentin wrote: Hi Todd, try this grant all - on *.* - to todd indentified by 'my_password' - with grant option; I believe you forgot the ".* " wich I added after grant all on... Best regards Nils Valentin Tokyo/Japan 2003 8 4 01:52Todd Cary : I have installed Red Hat 9 and it installs MySQL. After logging on as root, I did the following: mysql mysql use mysql; mysql grant all - on * - to todd indentified by 'my_password' - with grant option; I then log out of mysql and log out as root. Logged in as "todd" I type the following: mysql -u todd -p I type in my password and I get the following error: Acess denied for [EMAIL PROTECTED] When I was in mysql as root, "select * from user" has "todd" listed. What have I missed? Todd -- inline: NewLogo.gif
Re: Granting privileges
Hi Todd, Thank you for the reply. I once more created an account. See below the SHOW GRANTS FOR usertest command. I did login without setting a default database. If you use the first command * then you will only be able to login (no other privileges), depending on the mysql version you will be able to do a show databases, but thats it. That is the same like you said before all privileges are set to N. See the example below. 2003 8 4 12:11Todd Cary : I did some experimenting and here is what I found: grant all - on * - to bugsbunny indentified by 'bugs - with grant option; After putting the above into MySQL as root, I signed on as todd with the appropriate PW and tried to get into mysql with mysql -u bugsbunny -p [using bugs as the PW]. I was refused [EMAIL PROTECTED] is refused. I noticed that all of the privileges were set to N. mysql select database(); ++ | database() | ++ || ++ 1 row in set (0.00 sec) mysql grant all on * to usertest identified by 'usertest'; Query OK, 0 rows affected (0.00 sec) show grants for usertest; +--+ | Grants for [EMAIL PROTECTED] | +--+ | GRANT USAGE ON *.* TO 'usertest | | GRANT ALL PRIVILEGES ON `mysql`.* TO 'usertest | | GRANT ALL PRIVILEGES ON `training_db`.* TO 'usertest | +--+ Then I tried grant all - on *.* - to bugsbunny indentified by 'bugs' - with grant option; I was refused again with the same message however all of the privileges were set to Y. Lastly, I tried - on *.* - to [EMAIL PROTECTED] indentified by 'bugs' - with grant option; I was able to gain access and all of the privileges were set to Y. The second command creates the rights which I believe you wanted mysql grant all on *.* to usertest identified by 'usertest'; Query OK, 0 rows affected (0.00 sec) mysql show grants for usertest; +--+ | Grants for [EMAIL PROTECTED] | +--+ | GRANT ALL PRIVILEGES ON *.* TO 'usertest'@'%' IDENTIFIED BY PASSWORD '*eae699f6230d578dc275ddd6c5b37db11db529532a70' | | GRANT ALL PRIVILEGES ON `mysql`.* TO 'usertest'@'%' | | GRANT ALL PRIVILEGES ON `training_db`.* TO 'usertest'@'%' | +--+ 3 rows in set (0.00 sec) See the difference ;-). The first line says it all the other two are only dependent sub privileges. That crresponds to your observation that all privleges are set to Y. In both cases you are able to login, but in case 1 you have very restricted rights. Try SELECT USER() to see which user account mysql is using currently. Then use the SHOW GRANTS FOR command to see which privileges are set. Best regards Nils Valentin The % in the Host column does not appear to work for me, and there is something different about * and *.*. Todd Nils Valentin wrote: Hi Todd, 2003 8 4 08:01Todd Cary : Nils - What is the difference between *.* and * ? Hi Todd I just double checked. There is no difference. Both work the same way. I originally thought that the first one wouldn't have worked, but I checked it now. However I believe that *.* is the prober syntax. Does Grant All give the user the rights to create a DB? The below command allows the user todd all privileges (CREATE,INSERT,DELETE, UPDATE etc.) . The with grant option will also allow the user todd to create new users. About the command itself, I just doule checked once more. If you specify only the username then a wildcard is insert for the hostname (%) which allows the user todd to be able to login from anywhere. make sure thats what you really want !! In any other case you could specify the user as '[EMAIL PROTECTED]' to limit it to only the one host. Best regards Nils Valentin Tokyo/Japan Many thanks Todd Nils Valentin wrote: Hi Todd, try this grant all - on *.* - to todd
Re: Granting privileges
* Todd Cary I did some experimenting and here is what I found: [...] The % in the Host column does not appear to work for me, and there is something different about * and *.*. The manual is a usefull source of information... ;) URL: http://www.mysql.com/doc/en/Adding_users.html URL: http://www.mysql.com/doc/en/Privileges.html URL: http://www.mysql.com/doc/en/GRANT.html The difference between using * and *.* in a GRANT statement, is that * means all tables in the current database, while *.* means all tables in all databases. This is usefull when you set table/column specific privileges. Using special characters like % in user/host names requires a special quoting, like the examples in the manual shows: mysql GRANT ALL PRIVILEGES ON *.* TO monty@'%' - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; Yes, this could have been more clear in the documentation, but it _is_ mentioned under a Please note section on the GRANT page. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Granting privileges
Roger - Thank you for taking the time to clarify that for me. I was using "PHP with MySQL" and they are not clear on the differences I found and you explained. Thanks again Todd Roger Baklund wrote: * Todd Cary I did some experimenting and here is what I found: [...] The "%" in the Host column does not appear to work for me, and there is something different about "*" and "*.*". The manual is a usefull source of information... ;) URL: http://www.mysql.com/doc/en/Adding_users.html URL: http://www.mysql.com/doc/en/Privileges.html URL: http://www.mysql.com/doc/en/GRANT.html The difference between using * and *.* in a GRANT statement, is that * means "all tables in the current database", while *.* means "all tables in all databases". This is usefull when you set table/column specific privileges. Using special characters like "%" in user/host names requires a special quoting, like the examples in the manual shows: mysql GRANT ALL PRIVILEGES ON *.* TO monty@'%' - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; Yes, this could have been more clear in the documentation, but it _is_ mentioned under a "Please note" section on the GRANT page. -- inline: NewLogo.gif
Replication problem
Hi, I'm using MySQL 4.0.13 for both master and slave servers. However I have problem starting slave server. Error log says: 030804 14:06:10 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'backend2-bin.228' at position 43583528 030804 14:11:48 Slave I/O thread exiting, read up to log 'backend2-bin.228', position 64018330 030804 14:11:58 Error in Log_event::read_log_event(): 'Event too big', data_len=540488761,event_type=52 030804 14:11:58 Error reading relay log event: slave SQL thread aborted because of I/O error 030804 14:11:58 Could not parse log event entry, check the master for binlog corruption This may also be a network problem, or just a bug in the master or slave code. 030804 14:11:58 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'backend2-bin.195' position 141630238 I checked binlog with mysqlbinnlog: mysqlbinlog -j 141630238 /var/db/mysql/backend2-bin.195 | head It showed 2 queries and both seems ok, because I tried manually insert those queries into slave db. How can I fix this issue? thanks in advance, Ganbold -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
