Question on replication terminology
Hi I'm new to replication and looking through some docs on how to use it. Could someone please tell me if the following terms mean the same thing or, if not, what is the difference: master-master replication dual-master replication bidirectional replication TIA -BT
Help with formatting of 1:n relationship
Hello all I have a database with a 1:n relationship as follows: Item (1) fld1 fld2 ... Item_info (n) - fld1 fld2 ... My client wants me to export these linked records into an Excel file in the following format. item.fld1 item.fld2 ... item_info.fld1 item_info.fld2 ... item_info.fld1 item_info.fld2 .. ie. each item and all the records that make up its info together in a single Excel row. I'm using an excel library that accepts a SELECT as input and generates an XLS file with the records as output. I'd like to use this where possible. However I don't know if it's possible to write a SELECT that compresses a 1:n relationship into a single row. Is this possible, and if yes, could someone show me how? Or could you suggest another way in which I could achieve the above required output? Thanks, Vikram -- Operator: So what do you need? Besides a miracle. Neo: Guns. Lots of guns. -- The Matrix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with formatting of 1:n relationship
Hi Jerry Thanks for your input on this! I'm using an excel library that accepts a SELECT as input and generates an XLS file with the records as output. I'd like to use this where possible. However I don't know if it's possible to write a SELECT that compresses a 1:n relationship into a single row. Is this possible, and if yes, could someone show me how? Or could you suggest another way in which I could achieve the above required output? Does whatever tool you are using have any place where you can manipulate the data between the SELECT and the creation of the XLS? If not,I think you need a user-defined function for this, or perhaps you can do it with a user-defined procedure. Unfortunately the tool doesn't let me manipulate the data. It simply reads the result set and pops each field into a separate column in the XLS. So any formatting I do has to be part of the SELECT. I did consider a procedure but the problem is that the client is still using MySQL 4.x, which afaik doesn't support stored procedures. An upgrade is not something they can do at this point, as they're using s shared host so the server isn't really under their control. Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with formatting of 1:n relationship
Hi, I did consider a procedure but the problem is that the client is still using MySQL 4.x, which afaik doesn't support stored procedures. An upgrade is not something they can do at this point, as they're using s shared host so the server isn't really under their control. I think what you're really looking for is a pivot table or crosstab report in SQL itself, right? I have used pivot tables before but I'm far from an expert on them. I wasn't able to understand how I could apply a pivot table structure to this problem. Can you give me some more details on what you had in mind? Thanks, Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Tips for optimizing stored procedures
Hi all I am new to stored procedures and have just started using them. Is there any Web site you could suggest which discusses common optimization tips for MySQL SPs? Vikram -- Operator: So what do you need? Besides a miracle. Neo: Guns. Lots of guns. -- The Matrix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Question on default database for stored functions
Hi According to the MySQL manual, By default, a routine is associated with the default database.When the routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates) However, when I tried accessing a stored routine from another database, I received an error. Output below: mysql USE test mysql DELIMITER // mysql CREATE FUNCTION get_area(radius INT) - RETURNS FLOAT - BEGIN - RETURN PI() * radius * radius; - END - // Query OK, 0 rows affected (0.13 sec) mysql DELIMITER ; mysql USE test2 Database changed mysql select get_area(11); ERROR 1305 (42000): FUNCTION test2.get_area does not exist Can someone tell me what I'm doing wrong? Thanks. Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Question on returning multiple rows from a stored procedure into a session variable
Hi Is there a way to write a stored procedure that returns a result set containing multiple rows? More specifically, I'm trying to return the multi-row result set as an OUT parameter, which I can then access via a session variable. If this is possible, could someone direct me to the appropriate documentation or post an example. Thanks. Vikram -- Operator: So what do you need? Besides a miracle. Neo: Guns. Lots of guns. -- The Matrix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Need help with permissions between host, db and user tables
Hi,
I'm trying to customize permissions for a particular database and have
run into an odd problem. I'm hoping someone on this list can suggest
what I'm doing wrong. The setup is as follows:
Database: property
User '[EMAIL PROTECTED]' should be able to run SELECTs on this database
User '[EMAIL PROTECTED]' should be able to run SELECTs and INSERTs
on this database
I read in the MySQL manual that between the 'db' and 'host' tables, if
the 'db.host' field is empty, MySQL will look up the 'host' table for a
list of allowed hosts and set privileges as the intersection of the two
tables. http://dev.mysql.com/doc/refman/5.1/en/request-access.html
So I ran the following queries:
insert into user (host, user, password) values ('%.example.com', 'bill',
'');
insert into db (host, db, user, select_priv, insert_priv) values ('',
'property', 'bill', 'Y', 'Y');
insert into host (host, db, select_priv, insert_priv) values
('home.example.com', 'property', 'y', 'n');
insert into host (host, db, select_priv, insert_priv) values
('office.example.com', 'property', 'y', 'y');
flush privileges;
Per my understanding of what the manual page says, this should
accomplish what I am trying to do. However, when I log in using the
specified user/host combination (from either host), I cannot see the
'property' database. Also, SHOW GRANTS does not display the privileges
set above.
When I do the same thing using the GRANT command, it works. On
investigation, I see this is because it is inserting a separate record
for each host into the 'db' table, and not writing anything to the
'host' table.
So my question is: why does the 'if empty db.host field/look up in host
table' method suggested in the manual not work? What step am I missing?
Thanks in advance for your help. I'd appreciate it if you could reply to
me directly as well as the list.
Vikram
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help needed with MySQL C API-based client (segfault)
Hello,
I need to write a simple C client for a project. I am using the MySQL C
API. Attached is the code. It occassionally segfaults with no visible
pattern. Could someone help me figure out why? Or any other comments on the
code to help me make it better?
/* client.c */
#include stdio.h
#include mysql.h
int main()
{
/* declare
structures and variables */
char query[255];
int i, j, count;
MYSQL mysql;
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL_FIELD
*field;
/* initialize MYSQL structure */
mysql_init(mysql);
/* connect to database */
if
(!(mysql_real_connect(mysql, NULL, root, , db1, 0, NULL, 0)))
{
fprintf(stderr, Error in connection: %s\n,
mysql_error(mysql));
}
for( ;; )
{
printf(query? );
gets(query);
if (strcmp(query,exit)
== 0)
{
break;
}
/* execute query
*/
/* if error, display error message */
/* else check the type of
query and handle appropriately */
if (mysql_query(mysql, query) != 0)
{
fprintf(stderr, Error in query: %s\n, mysql_error(mysql));
}
else
{
if (result = mysql_store_result(mysql))
{
/* SELECT
query */
/* retrieve result set */
int numRecords =
mysql_num_rows(result);
int numFields = mysql_num_fields(result);
for (i = 0; i numRecords; i++)
{
row =
mysql_fetch_row(result);
for (j = 0; j numFields; j++)
{
//field= mysql_fetch_field(result);
fprintf(stdout, %s, row[j]);
j != (numFields-1) ? printf(, ) : printf(\n);
}
}
fprintf(stdout, ** Query successful, %d rows retrieved **\n,
numRecords);
}
else
{
if (mysql_field_count(mysql) == 0)
{
/* non-SELECT query */
fprintf(stdout, ** Query successful, %d
rows affected **\n, mysql_affected_rows(mysql));
}
else
{
fprintf(stderr, Error in reading result set: %s\n,
mysql_error(mysql));
}
}
}
/* clean up */
mysql_free_result(result);
}
mysql_close(mysql);
}
--
I wouldn't recommend sex, drugs, and insanity for everyone, but it works
for me.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using a foreign character set in MYSQL
Hello all. I am working with MySQL 4.0. I have a requirement to create a data-driven Web page to display Chinese text from a MySQL table. I'm completely new to this, can someone tell me exactly what I need to do to make this happen? 1. For example, how do I insert the Chinese text from my source (a Word doc) into a MySQL table without corrupting it? When I try copying and pasting it into the mysql client command-line, the data gets trashed. 2. Once it's in, how do I get it back out into my application without corrupting it? I'm using PHP 4.3 for the Web site. 3. If I need to make changes to the data from the command-line client, how can I do it, especially if the query involves using a Chinese-language string? For example, update langdata set menutitle='SOME_MENU_TITLE_IN_CHINESE' where menutitle='SOME_OLD_MENU_TITLE_IN_CHINESE' Looked at the online manual but am sorry to say it didn't really help much. I tried starting the server with --character-set=big5 but it didn't seem to make much difference... Thanks! Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using a foreign character set in MYSQL
When I try copying and pasting it into the mysql client command-line, the data gets trashed. Not necessarily. May just be that the command-line window doesn't know to display Chinese unless you tell it to. What OS are you working on? Working on Windoze with a telnet window open to a Linux box (which has the MySQL client/server) Thanks, Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary/foreign keys and indexes
Hello all, I am working on a book on MySQL, and I need some assistance with deciding where some concepts should be dealt with. 1. I have dealt (briefly) with primary keys and indexes in a chapter called MySQL DML. I have not discussed foreign keys in this chapter. 2. I would like to discuss foreign keys and indexes in detail further down in the book. I am confused as to how to break up this information. Is it better to deal with primary keys, foreign keys and indexes all at once, in a single chapter, or break it up into two, one dealing with primary/foreign keys and the other with indexes. The issue is confounded further (at least for me) by the fact that mySQL seems to require an index on both primary and foreign keys, and the manual suggests KEY as a synonym for INDEX. Are the concepts of key and index distinct or not? Any thoughts would be most welcome :) Thanks, Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with DELETE and a subquery
Hey, Thanks for the help. I dont think this is possible, because MySQL will not let you delete from the same table you are reading. Is there an alternative way to do this using a subquery, you think? ignore both previous posts. Both don't work as wanted. I just realized that and I will come back to you after I created the tables and made it sure. Sorry for the confusion. Best regards Nils Valentin Tokyo/Japan 2003年 7月 25日 金曜日 14:42、Nils Valentin さんは書きました: Hi Vikram, just read the post once more. I made a mistake. You want to delete the clients with no branches you said, so the command should look like mysql delete from clients where cid = (select clients.cid from clients left join branches using (cid) WHERE ISNULL(clients.cid); Note that cid itself is ambigous, because in both tables. Best regards Nils Valentin 2003年 7月 25日 金曜日 14:31、Nils Valentin さんは書きました: Hi Vikram, NULL is a special data type and requires special procedures. Try this: mysql delete from clients where cid = (select clients.cid from clients left join branches using (cid) WHERE ISNULL(bid); Please make NO SPACE betwen ISNULL and (bid) as otherwise wit will give you an syntax error. Hope that ends the problems you had. Best regards Nils Valentin Tokyo/Japan 2003年 7月 25日 金曜日 12:58、Vikram Vaswani さんは書きました: Hi all, I have the following two tables: mysql SELECT * FROM clients; +-+-+ | cid | cname | +-+-+ | 101 | JV Real Estate | | 102 | ABC Talent Agency | | 103 | DMW Trading | | 104 | Rabbit Foods Inc| | 110 | Sharp Eyes Detective Agency | +-+-+ 5 rows in set (0.00 sec) mysql SELECT * FROM branches; +--+-++--+ | bid | cid | bdesc | bloc | +--+-++--+ | 1011 | 101 | Corporate HQ | CA | | 1012 | 101 | Accounting Department | NY | | 1013 | 101 | Customer Grievances Department | KA | | 1041 | 104 | Branch Office (East) | MA | | 1042 | 104 | Branch Office (West) | CA | | 1101 | 110 | Head Office| CA | | 1031 | 103 | N Region HO| ME | | 1032 | 103 | NE Region HO | CT | | 1033 | 103 | NW Region HO | NY | +--+-++--+ 9 rows in set (0.01 sec) I need to delete all clients with no branches. I need to use a subquery to do this. Given these constraints, I came up with the following: mysql delete from clients where cid = (select clients.cid from clients left join branches using (cid) WHERE bid is null); MySQL says: ERROR 1093: You can't specify target table 'clients' for update in FROM clause Does any one know why I am getting this error (MySQL 4.1)? Can you help me rewrite this operation *using a subquery only*? TIA, Vikram -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- 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]
Help with DELETE and a subquery
Hi all, I have the following two tables: mysql SELECT * FROM clients; +-+-+ | cid | cname | +-+-+ | 101 | JV Real Estate | | 102 | ABC Talent Agency | | 103 | DMW Trading | | 104 | Rabbit Foods Inc| | 110 | Sharp Eyes Detective Agency | +-+-+ 5 rows in set (0.00 sec) mysql SELECT * FROM branches; +--+-++--+ | bid | cid | bdesc | bloc | +--+-++--+ | 1011 | 101 | Corporate HQ | CA | | 1012 | 101 | Accounting Department | NY | | 1013 | 101 | Customer Grievances Department | KA | | 1041 | 104 | Branch Office (East) | MA | | 1042 | 104 | Branch Office (West) | CA | | 1101 | 110 | Head Office| CA | | 1031 | 103 | N Region HO| ME | | 1032 | 103 | NE Region HO | CT | | 1033 | 103 | NW Region HO | NY | +--+-++--+ 9 rows in set (0.01 sec) I need to delete all clients with no branches. I need to use a subquery to do this. Given these constraints, I came up with the following: mysql delete from clients where cid = (select clients.cid from clients left join branches using (cid) WHERE bid is null); MySQL says: ERROR 1093: You can't specify target table 'clients' for update in FROM clause Does any one know why I am getting this error (MySQL 4.1)? Can you help me rewrite this operation *using a subquery only*? TIA, Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Updating a record with MAX(value)
Hi all, Is there a way to update the record with maximum value in a table with another value? I am trying this: mysql update services set sfee = 1 where sfee = max(sfee); Query OK, 0 rows affected (0.06 sec) Rows matched: 0 Changed: 0 Warnings: 0 But it doesn't make any changes to the data. Any ideas why? TIA, Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting columns into a single row
Hi guys, I need some help with this. I have the following 3 tables. +--+---+ | uid | name | +--+---+ | 100 | sue | | 102 | harry | | 104 | louis | | 107 | sam | | 110 | james | | 111 | mark | | 112 | rita | +--+---+ +--+---+ | gid | name | +--+---+ | 501 | authors | | 502 | actors| | 503 | musicians | | 504 | chefs | +--+---+ +--+--+ | uid | gid | +--+--+ | 11 | 502 | | 107 | 502 | | 100 | 503 | | 110 | 501 | | 112 | 501 | | 100 | 501 | | 102 | 501 | | 104 | 502 | | 100 | 502 | +--+--+ I'm looking for the following output: 1. Group members group name users authors sue,harry,james,mark actors ..., musicians ..., chefs NULL 2. User memberships user name groups user1 group1,group3, etc user 2 group2, group3, etc and so on Any idea how to do this? TIA, Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on choosing a MySQL API
Hi, The MySQL API is available in a number of languages - PHP, Perl, C, Java, etc. I was wondering if anyone here had any thoughts on the decision criteria to be kept in mind when choosing which language to use when programming with the MySQL API? A brief list of these would be very helpful to me. Or if you could point me to a link, that would be great too! TIA, Vikram - 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
C API
Hi, Does anyone know which version of MySQL first exposed an API for developers? Is there a changelog somewhere that might have this info? I'm interested in seeing how the API has evolved over time. TIA, Vikram -- Luke: I don't believe it! Yoda: That, is why you fail. --Star Wars: The Empire Strikes Back - 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 book TOC
Jeremy - the first one ;) Any comments on the TOC? Vikram At 02:46 PM 2/8/03 -0800, Jeremy Zawodny wrote: On Fri, Feb 07, 2003 at 03:20:47PM +0500, Vikram Vaswani wrote: My name is Vikram Vaswani, and I'm currently working on the outline for a MySQL reference book. This is supposed to be a comprehensive reference to MySQL 4, covering all aspects of the software, including the new transaction/subquery features. Call me biased, but that and this... Since I'm not that experienced with MySQL Don't seem like the right combination. Did you mean not that experienced writing about MySQL? Or are you really new to MySQL and trying to write a book about it? Jeremy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL book TOC
Hi Nasser, Usage?? well whoever goes to buy your book, must have some ideas in head for the usage.. I don't think you should waste trees on telling people things that don't help them. One Question: What makes this book different from the MySQL manual? Well, it's not supposed to replace the manual by any means - it's just supposed to offer novice-intermediate users a starting point to MySQL usage Another Question: Who is your intended audience? Novice to intermediate users A suggestion: Think of substantial examples How about performance? and Performance tuning? I am planning to cover this, but in the latter sections of the book. Fundamentally, my problem is with the Section 2 - I am not sure what level of detail to go to in this section. Any assistance you or other list members could provide would be very helpful :) Thanks for your response, Vikram - 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
MySQL book TOC
Hello all, My name is Vikram Vaswani, and I'm currently working on the outline for a MySQL reference book. This is supposed to be a comprehensive reference to MySQL 4, covering all aspects of the software, including the new transaction/subquery features. I have put together a draft table of contents (TOC), but am a little hesitant about some aspects of it. I have a nagging feeling that I'm missing out on a lot of things here, and also that it might bs structured better. Specifically, I was hoping that this list would help me in evaluating the structure and content of Section 2 below. Since I'm not that experienced with MySQL, I thought I'd open this up to the list and see if you guys could help me figure out what's bugging me :) I'd appreciate as much feedback as possible, since my aim here is to produce something that would hopefully be useful to you in your usage of MySQL (and also stand out from the crowd of other MySQL books in the market) Thanks! I look forward to hearing back. Vikram Vaswani -- TOC Section 1: Installation 1.Introduction to MySQL What is MySQL? History Features Future development 2. Installing Linux Windows Section 2: Usage 3.Introduction to Databases and SQL What is SQL? SQL variants SQL features SQL syntax A sample SQL session 4.MySQL Data Types Basic data types String and string variants Integer and integer variants Date and time types Boolean types Null type Advanced data types Enum (choice) type Binary object type 5.MySQL Syntax (Working With Databases) - CREATE, DROP, SHOW Understanding how databases are stored on the file system Creating a database Viewing available databases Deleting a database Do I need to add info on the InnoDB/Berkelet/MyISAM types here? And how/when to select each? 6.MySQL Syntax (Working With Tables) - DESCRIBE, SELECT, INSERT, UPDATE, DELETE, APPEND, joins Understanding how tables are stored on the file system MySQL table types Creating a table Viewing available tables Creating table columns Inserting records Viewing table records Editing (updating) table records Joining tables Erasing records Altering columns Erasing columns Erasing tables What have I missed here? 7.MySQL Syntax (Working With Tables - Advanced) - Indices, transactions and sub-queries Advanced joins Sub-queries Indices Transactions Stored procedures Do you think I need a separate chapter for each of the above? What have I missed here? Section 3: Administration Section 4: Development -- -- Luke: I don't believe it! Yoda: That, is why you fail. --Star Wars: The Empire Strikes Back - 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
Difference between MySQL versions
Hi there, Can someone point me to amn (exhaustive) list of features in MySQL Max that are not present in MySQL Standard? Or any other documentation that lists the differences between the two? Thanks in advance, Vikram -- Luke: I don't believe it! Yoda: That, is why you fail. --Star Wars: The Empire Strikes Back - 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: Problem compiling 4.0.9-gamma on Windows
Hi all, I get this error when compiling MySQL 4.0.9-gamma on Windows .\debug\Strings.obj LINK : fatal error LNK1181: cannot open input file .\debug\Strings.obj Error executing link.exe. Am using VC++ 6.0 Any help you can offer would be appreciated. Thanks! Vikram - 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
