Re: Replication question
On 24/07/2013 19:52, Rick James wrote: 4) 3 tables from the slaves are to be replicated back to the master NO. However, consider Percona XtraDb Cluster or MariaDB+Galera. They allow multiple writable masters. But they won't let you be so selective about tables not being replicated. Here are the gotchas for Galera usage: http://mysql.rjweb.org/doc.php/galera If you can live with them (plus replicating everything), it may be best for you. Ok thanks Rick for confirming my initial gut feelings about this...! Will have to implement a manual process to push the required data back to the master. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Replication question
I have been asked to set up multiple database replication which I have done before for simple cases however there are some nuances with this instance that add some complexity and I'd like to hear your collective expertise on this proposed scenario:- 1) Single master database 2) n (probably 3 to start with) number of slave databases 3) All but 5 tables (123 tables in total) are to be replicated from the master to all the slaves 4) 3 tables from the slaves are to be replicated back to the master It is mainly item 4) that concerns me - the primary ID's are almost certain to collide unless I seed the auto increment ID to partition the IDs into separate ranges or does MySQL handle this issue? There are some foreign keys on one of the 3 slave to master tables but they are pointing at some extremely static tables that are very unlikely to change. Is the above a feasible implementation...? Thanks in advance for any advice/pointers! Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: (real) silly question about variables...
On 04/10/2012 15:52, MAS! wrote: Hi I know there'd be a reason, but I can't understand that.. mysql select @valore:=rand(), @valore, @valore:=ciao, @valore; +---+---+-+-+ | @valore:=rand() | @valore | @valore:=ciao | @valore | +---+---+-+-+ | 0.483624490428366 | 0.483624490428366 | ciao| 0 | +---+---+-+-+ 1 row in set (0.00 sec) mysql select @valore:=rand(), @valore, @valore:=ciao, @valore; +---+---+-+-+ | @valore:=rand() | @valore | @valore:=ciao | @valore | +---+---+-+-+ | 0.747058809499311 | 0.747058809499311 | ciao| ciao| +---+---+-+-+ 1 row in set (0.00 sec) why in the first execution the latest value is 0 and not 'ciao'? and why in the first 2 columns the variables seems works as expected!? what version of MySQL are you running? I get this:- Server version: 5.5.17-log MySQL Community Server (GPL) mysql select @valore:=rand(), @valore, @valore:=ciao, @valore; +++-+-+ | @valore:=rand()| @valore| @valore:=ciao | @valore | +++-+-+ | 0.8187706152151997 | 0.8187706152151997 | ciao| ciao| +++-+-+ Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Having trouble with SQL query
Hi Nitin Thanks - I tried that and got 0 rows... I have spent more time on describing my problem -- see below hopefully this will make the issue more clear... Rich I have a MySQL database with a menu table and a product table. - The products are linked to the menus in a one-to-many relationship i.e. each product can be linked to more than one menu - The menus are nested in a parent child relationship - Some menus may contain no products The desire is that when a user clicks on a menu entry then all products linked to that menu - there may be none - will get displayed as well as all products linked to any child menus of the menu clicked on ... So say we have a menu like this:- Motor cycles - Sports bikes - Italian - Ducati Motor cycles - Sports bikes - Italian - Moto Guzzi Motor cycles - Sports bikes - British - Triumph Motor cycles - Tourers - British - Triumph Motor cycles - Tourers - American - Harley-Davidson . etc etc Clicking on 'Sports bikes' will show all products linked to 'Sports bikes' itself as well as all products linked to ALL menus below 'Sports bikes', clicking on 'Harley-Davidson' will just show products for that entry only. Below are 'describe table' for the 2 main tables in question NB there is a 3rd table that holds product descriptions which I won't show as I don't think it is relevant here:- CREATE TABLE `menu` ( `menuid` int(11) unsigned NOT NULL AUTO_INCREMENT, `parent_menuid` int(11) unsigned NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`menuid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 CREATE TABLE `menu_product` ( `menuid` int(11) unsigned NOT NULL, `productid` int(11) unsigned NOT NULL, PRIMARY KEY (`menuid`,`productid`), KEY `prodidx` (`productid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 For the sake of this question I will simplify it and say there is only 2 levels of nesting i.e. root level and 1 level below that... this is the query I came up with:- SELECT DISTINCT p.productid, pd.name FROM menu_product as p INNER JOIN menu as m ON (m.menuid = p.menuid) INNER JOIN product_description as pd ON (pd.productid = p.productid) LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected menu may itself be a child menu... WHERE (m.name = 'name obtained from user's click' OR p.productid IN (SELECT p2.productid from menu_product AS p2 WHERE p2.menuid = m2.menuid) Anyway when I run the above query it returns far too many entries from menus that are totally unrelated... I have been staring too hard at this for too long - I am sure it will be a forehead slapper! I hope I have explained this sufficiently and I TYIA for any guidance Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Having trouble with SQL query
I have a MySQL database with a menu table and a product table linked to the menus *(each product can be linked to more than menu row)* and the menus are nested. The query is that when a user clicks on a menu entry then all products linked to that entry *(there may be none)* will get displayed as well as all products linked to child menus... below are describe tables for the 2 main tables in question (there is a 3rd table that holds product descriptions which I won't show as I don't think it is relevant) CREATE TABLE `menu` ( `menuid` int(11) unsigned NOT NULL AUTO_INCREMENT, `parent_menuid` int(11) unsigned NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`menuid`) ) ENGINE=MyISAM AUTO_INCREMENT=225 DEFAULT CHARSET=utf8 CREATE TABLE `menu_product` ( `menuid` int(11) unsigned NOT NULL, `productid` int(11) unsigned NOT NULL, PRIMARY KEY (`menuid`,`productid`), KEY `prodidx` (`productid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 Anyway for the sake of this question lets say there is only 2 levels of nesting so a parent menu can only have children so no grandkids+ this is the query I came up with:- SELECT DISTINCT p.productid, pd.name FROM menu_product as p INNER JOIN menu as m ON (m.menuid = p.menuid) INNER JOIN product_description as pd ON (pd.productid = p.productid) LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected menu may itself be a child menu... WHERE (m.name = 'name obtained from user's click' OR p.productid IN (SELECT p2.productid from menu_product AS p2 WHERE p2.menuid = m2.menuid) Anyway the above query returns many many entries from menus that are totally unrelated... I have been staring too hard at this for too long - I am sure it will be a forehead slapper! I hope I have explained this sufficiently and I TYIA for any guidance Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL v5.5.12 on Mac OSX - Server won't start...
I have installed MySQL v5.5.12 Community Server on Mac OS X v10.5.8 When I try and start the server I get these messages before it aborts... 110514 17:16:14 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 110514 17:16:16 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead. 110514 17:16:17 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive 110514 17:16:17 [Note] Plugin 'FEDERATED' is disabled. /usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist 110514 17:16:17 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 110514 17:16:17 InnoDB: The InnoDB memory heap is disabled 110514 17:16:17 InnoDB: Mutexes and rw_locks use GCC atomic builtins 110514 17:16:17 InnoDB: Compressed tables use zlib 1.2.3 110514 17:16:17 InnoDB: Initializing buffer pool, size = 128.0M 110514 17:16:17 InnoDB: Completed initialization of buffer pool 110514 17:16:17 InnoDB: highest supported file format is Barracuda. 110514 17:16:19 InnoDB: Waiting for the background threads to start 110514 17:16:20 InnoDB: 1.1.6 started; log sequence number 1595675 110514 17:16:20 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'default-character-set=utf8' 110514 17:16:20 [ERROR] Aborting 110514 17:16:20 InnoDB: Starting shutdown... 110514 17:16:21 InnoDB: Shutdown completed; log sequence number 1595675 110514 17:16:21 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 110514 17:16:21 mysqld_safe mysqld from pid file /usr/local/mysql/data/x.local.pid ended Anybody point me towards where I can get this issue sorted... Googling didnt turn up much ... TIA Rich
RE: permission or config error
Melinda
Is there a default socket entry in your php.ini (mysql.default_socket) that
is pointing to /tmp?
Rich
-Original Message-
From: Melinda Taylor [mailto:[EMAIL PROTECTED]]
Sent: 25 January 2003 00:25
To: [EMAIL PROTECTED]
Subject: permission or config error
Hello,
Probably a question that has been asked a million times before, I searched
google and it
has been brought up before but there didn't seem to be that many solutions
and alot fo the
time it would just Start working.
Anyway,I have just set up my mysql server
Server version: 3.23.54
Protocol version: 10
Connection: Localhost via UNIX socket
UNIX socket:/var/lib/mysql/mysql.sock
and wrote a little php test script to access the database (not php is
configured properly and works
when it is just plain php but as soon as it (tries to) connect to the
database all fails).
?
$connection=mysql_connect(localhost,melinda,mypasswd);
$connection= mysql_connect(localhost,melinda,mypassswd) or
die(Couldn't connect);
if($connection)
{
$msg=success;
}
?
? echo $msg; ?
The message I get when I connect is:
Warning: Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (2) in /home/melinda/public_html/test-sql.php on line 2
Warning: MySQL Connection Failed: Can't connect to local MySQL server
through socket '/tmp/mysql.sock' (2) in
/home/melinda/public_html/test-sql.php on line 2
Warning: Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (2) in /home/melinda/public_html/test-sql.php on line 3
Warning: MySQL Connection Failed: Can't connect to local MySQL server
through socket '/tmp/mysql.sock' (2) in
/home/melinda/public_html/test-sql.php on line 3
Couldn't connect
I am still trying to understand all the info on access but I did setup the
user melinda as follows:
mysql use mysql
Database changed
mysql insert into user (host, user, password) values('localhost',
'melinda',password('mypasswd'));
- then flushed privileges
mysqladmin -u root -p flush-privileges
and I can login to mysql as the user melinda.
Is the error message above due the fact that I have not enabled some other
permission or it simply due to the fact that
the socket is in /var/lib/mysql/mysql.sock not in /tmp/ and if so how do I
tell it that the socket file is in /var/lib
Many Thanks,
Melinda
-
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: Displaying news across several pages
when 2 arguments are specified for LIMIT then the first argument is the offset and the second is the max number of rows e.g. below select * from mytable limit 10,10 select * from mytable limit 20,10 Rich -Original Message- From: Pag [mailto:[EMAIL PROTECTED]] Sent: 25 January 2003 05:10 To: Roger Davis; [EMAIL PROTECTED] Subject: Displaying news across several pages Hi, I am a beginner in this MySQL thing, and i am trying to figure out how i can split, for example, some news articles i already have in a table, into several pages. On the site i am building, i get the 10 first news and display them. I use SELECT FROM NEWS ... ORDER BY DATE DESC LIMIT 10. Is there any mysql command to select the next 10 items, for example? something like LIMIT from 10 to 20 hehe, i know it sounds weird. :-) Thanks. Pag - 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 write a scheduled job for MySQL
Hi Akash If using *nix investigate using the cron scheduler via crontab/at to execute your SQL script at regular intervals. If you are running on NT/Win2K then investigate using the task scheduler instead. HTH Rich -Original Message- From: Akash [mailto:[EMAIL PROTECTED]] Sent: 20 January 2003 11:52 To: [EMAIL PROTECTED] Subject: how to write a scheduled job for MySQL Hi, I want to periodically delete some records from a table. Is there any mechanism by which I can schedule a job, which will execute a particular query at periodic intervals ? MySQL currently doesn't support stored procedures and triggers... but I guess, there must be some way around. If anyone has ever faced this problem, please let me know. Thanks Akash Agarwal Senior Member Technical Staff, Intersolutions Pvt Ltd, NOIDA, INDIA (www.lotusinterworks.com) (www.alacre.com) - 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
