User Preferences?
I'm looking for a good way to store user preferences. The most straightforward way is just to add a column to the Users table for each preference we're looking to store. Downside is that it requires an ALTER TABLE which gets prohibitively expensive as it gets larger, as it's fairly inflexible. I've come up with a few alternatives, and I'm wondering if people have ideas or suggestions, as this has to be a common problem. A quick Google search didn't turn up anything. 1. Store the preferences as a binary blob on the Users table. This blob could be either a blob, or an integer that I use application logic to read/write from, or I could use the SET datatype. 2. Store the preferences in normalized form, with a new table called UserPreferences that has UserId, Setting, Preference and we add a row for each setting of that user. 3. Create a separate table each time we want to add a new setting, UserId, WhateverTheNameOfThePreferenceIs. Anyone have any experience with this, or better suggestions? Thanks, Waynn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
/tmp/mysql.sock dissapears
Hi, I am running mysql 5.0.45 on freebsd 4.11 and for some strange reason /tmp/mysql.sock keeps on disappearing and we are forced to kill -9 mysql and restart it causing db corruptions as there is no other way of telling it to stop once that file has gone. I have tried to find any reason why this happens and there are no errors, no core files, nothing - the file just disappears. Here is the error when trying to login: # mysql -p cache Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) Here is my my.cnf file: [client] port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M #log-bin server-id = 1 # Uncomment the following if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 1 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /var/db/mysql/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /var/db/mysql/ #innodb_log_arch_dir = /var/db/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 16M #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 5M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout The machine in question is a dual xeon with 4gig of ram. Any ideas? Thanks in advance, Ian
Re: joining and grouping
no that won't work, because even though the where excludes *my* vote for a particular candidate, it will include everybody else's vote for the same candidate. the objective is: if *i* voted for john, then john should not be in the final result set even though a million other people voted for john. picture the following list: john (11 votes) paul (8 votes) richard (6 votes) george (4 votes) now, if my vote is among the six votes for richard, then the final list should look like this, even though five other people voted for him: john (11 votes) paul (8 votes) george (4 votes) On 27/02/2008, Phil [EMAIL PROTECTED] wrote: Ok then, so select candidate,count(*) as total from vote where (voter '$me' and vote =1) group by candidate order by total desc; On Wed, Feb 27, 2008 at 9:37 AM, Olav Mørkrid [EMAIL PROTECTED] wrote: hi phil, i forgot to mention one thing. the table also has a column called vote which is either 0 (no vote given) or 1 (vote given). this column is required for other purposes. my favorites: select candidate from vote where voter = '$me' and vote = 1; most popular: select candidate from vote where vote = 1 group by candidate order by count(*) desc; when generating the desired list (most popular minus my favorites) it is important that a candidate is excluded from the result set if *i* voted for him -- even if a million other people voted for him. is this clearer? On 27/02/2008, Phil [EMAIL PROTECTED] wrote: I'm confused as to why you need the subselect at all? As it's all the same table why can't you just use select candidate,count(*) as total from vote where voter '$me' group by candidate order by total desc; On Wed, Feb 27, 2008 at 9:04 AM, Olav Mørkrid [EMAIL PROTECTED] wrote: hello i have a table vote which has the columns voter and candidate. i would like to make a list of the most popular candidates *except* those who are on my favorite list. using a sub-select, it's easy: my favorites: select candidate from vote where voter = '$me'; most popular: select candidate from vote group by candidate order by count(*) desc; sub-select: select candidate from vote where candidate not in (select candidate from vote where voter = '$me') group by candidate order by count(*) desc; however, sub-selects are very slow, so i need to find a speedy way. i'm familiar with joins, but don't know how to use it for this case where grouping is involved. please get in touch if you know how to solve it. thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIKE problem with characters 'å' (norwe gian) and 'a' (mysql bug?)
Dear List,
I get incorrect result when searching for the norwegian character 'å'
using LIKE. I get rows with 'a' in it, and visa versa if I search for
'a', I get results which has 'å' in it in addition to the ones with 'a'.
Example:
CREATE TABLE names (
name VARCHAR(255)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO names VALUES
('Foo'), ('Bar'), ('Båt'), ('Bør'), ('Ære');
Now, searching gives me the following results:
mysql SELECT * FROM names WHERE name LIKE '%å%';
+--+
| name |
+--+
| Bar |
| Båt |
+--+
mysql SELECT * FROM names WHERE name LIKE '%a%';
+--+
| name |
+--+
| Bar |
| Båt |
+--+
Searching for strings with other norwegian characters seams to work:
mysql SELECT * FROM names WHERE name LIKE '%ø%';
+--+
| name |
+--+
| Bør |
+--+
I found that I may use
mysql SELECT * FROM names WHERE LOWER(name) LIKE BINARY LOWER('%å%');
which returns correct results, but this disables me from letting the
user do case sensitive searches.
Am I doing something wrong or stupid? Could this be a MySQL bug?
How do I know this isn't a problem with other utf-8 characters in other
languages?
I've searched in bug reports, but cannot find this exact problem.
Some additional information that might be useful:
mysql SELECT VERSION();
+--+
| VERSION()|
+--+
| 5.0.45-Debian_1ubuntu3.1-log |
+--+
mysql SHOW VARIABLES LIKE '%character%';
+--++
| Variable_name| Value |
+--++
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results| utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--++
Thanks,
Magne Westlie
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Preferences?
Waynn, I've used both schemes 1 and 2 as you describe, and in my experience 2 is the best way to go. It's easy to scale up as you add users and settings, and it's easy to make changes if the meaning of settings should change (i.e. you need to do a backend change to people's settings). #1 is harder to make those kind of back end updates on, and harder for someone troubleshooting to make sense of the data. #3 may not scale well - you would end up having to track too many tables, I think. What I'm doing in my current project is using a data model that has a method for each preference setting, and returns a sensible value by default if the user has no pref set for a given lookup key; otherwise, I return what the user has set. This means adding a method every time I add a preference setting, which on the one hand means adding code - on the other hand, chances are very high that if I am adding the ability for a user to set a preference, I'm already adding code somewhere to ensure that preference has an effect. HTH, Dan On Thu, Feb 28, 2008 at 9:50 AM, Waynn Lue [EMAIL PROTECTED] wrote: I'm looking for a good way to store user preferences. The most straightforward way is just to add a column to the Users table for each preference we're looking to store. Downside is that it requires an ALTER TABLE which gets prohibitively expensive as it gets larger, as it's fairly inflexible. I've come up with a few alternatives, and I'm wondering if people have ideas or suggestions, as this has to be a common problem. A quick Google search didn't turn up anything. 1. Store the preferences as a binary blob on the Users table. This blob could be either a blob, or an integer that I use application logic to read/write from, or I could use the SET datatype. 2. Store the preferences in normalized form, with a new table called UserPreferences that has UserId, Setting, Preference and we add a row for each setting of that user. 3. Create a separate table each time we want to add a new setting, UserId, WhateverTheNameOfThePreferenceIs. Anyone have any experience with this, or better suggestions? Thanks, Waynn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: weird select - version 2
Hi dante, all, [EMAIL PROTECTED] wrote: [[...]] In a nutshell, i need a query intelligent enough to make a query to table_Out, see if theres a match for 'id_tA', if there is one, retrieve field ref, otherwise go look in table_In and retrieve ref from there. Is it too complicated (impossible?) to use only one query? Should i just do it the old style, two queries and a php condition between them? Well, you need two SELECT statements, but you can combine them in one Query using UNION: SELECT ... FROM tableA, table_out WHERE ... UNION SELECT ... FROM tableA, table_In WHERE ... ; I leave the details to you, you should know your conditions best. Also, it is up to you what to do if one row in tableA can be joined to both of table_out and table_In, or to none of them. HTH, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Debugging mysql limits
I'm trying to figure out which limits I'm hitting on some inserts. I have 50 plus tables lets call them A_USER, B_USER, C_USER etc which I daily refresh with updated (and sometimes new) data. I insert the data into a temporary table using LOAD DATA INFILE. This works great and is very fast. Then I do an INSERT INTO A_USER (Select col1,col2,col3...,col 20, 0,0,0,0,0,0,etc etc from A_TEMP) on DUPLICATE KEY UPDATE col1=A_TEMP.col1,col2= etc The sizes in the tables range from 500 entries up to 750,000. two of them in the 200,000 range take 2-3 mins for this to complete, the largest at 750,000 takes over an hour. a sampling of my cnf file is old_passwords=1 max_connections = 50 max_user_connections = 50 table_cache=2000 open_files_limit=4000 log-slow-queries = /var/log/mysql-slow.log long_query_time = 12 log-queries-not-using-indexes thread_cache_size = 100 query_cache_size = 64M key_buffer_size = 512M join_buffer_size = 24M sort_buffer_size = 64M read_buffer_size = 4M tmp_table_size = 64M max_heap_table_size = 64M There is 2Gb Ram in the server which I would gladly increase if I knew I could tweak these settings to fix this? Any ideas what I should do to figure out what is causing it? Regards Phil
Re: /tmp/mysql.sock dissapears
how do you start up? you can start up from scrip. #!/bin/sh id=02 ip=192.168.0.42 sockfile=/tmp/mysql$id.sock user=mysql datdir=/var/db/mysql$id port=3306 /bin/sh /usr/local/bin/mysqld_safe --user=$user --datadir=$datdir --bind-address=$ip --port=$port --sock=$sockfile Ian escribió: Hi, I am running mysql 5.0.45 on freebsd 4.11 and for some strange reason /tmp/mysql.sock keeps on disappearing and we are forced to kill -9 mysql and restart it causing db corruptions as there is no other way of telling it to stop once that file has gone. I have tried to find any reason why this happens and there are no errors, no core files, nothing - the file just disappears. Here is the error when trying to login: # mysql -p cache Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) Here is my my.cnf file: [client] port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M #log-bin server-id = 1 # Uncomment the following if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 1 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /var/db/mysql/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /var/db/mysql/ #innodb_log_arch_dir = /var/db/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 16M #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 5M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout The machine in question is a dual xeon with 4gig of ram. Any ideas? Thanks in advance, Ian -- Ing. Vidal Garza Tirado Depto. Sistemas Aduanet S.A. de C.V. Tel. (867)711-5850 ext. 4346, Fax (867)711-5855. Ave. César López de Lara No. 3603 Int. B Col Jardín. Nuevo Laredo, Tamaulipas, México. -- Este mensaje ha sido analizado por MailScanner en busca de virus y otros contenidos peligrosos, y se considera que está limpio. For all your IT requirements visit: http://www.aduanet.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about reading info from another table.
Hi Everyone, I am attempting to write a PHP application that reads info from a MySQL database, and I'm wondering if I can set up a column in one table that gets it's info from a field in another table automatically? Ie: Table1: field1 field2 field3 Table2: field4 field5 field6 = field1 Does that make sense? Would that be a join? Or maybe a primary key? I'm new to MySQL programming so RTFM's are appreciated as long as M is defined :) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: /tmp/mysql.sock dissapears
Hi,
We use the following sh script to start (its the default one when installed)
cat /usr/local/etc/rc.d/mysql-server.sh
#!/bin/sh
#
# $FreeBSD: ports/databases/mysql50-server/files/mysql-server.sh.in,v
1.32006/03/07 16:25:00 ale Exp $
#
# PROVIDE: mysql
# REQUIRE: LOGIN
# KEYWORD: shutdown
#
# Add the following line to /etc/rc.conf to enable mysql:
# mysql_enable (bool): Set to NO by default.
# Set it to YES to enable MySQL.
# mysql_limits (bool): Set to NO by default.
# Set it to yes to run `limits -e -U mysql`
# just before mysql starts.
# mysql_dbdir (str):Default to /var/db/mysql
# Base database directory.
# mysql_args (str): Custom additional arguments to be passed
# to mysqld_safe (default empty).
#
. /etc/rc.subr
name=mysql
rcvar=`set_rcvar`
load_rc_config $name
: ${mysql_enable=NO}
: ${mysql_limits=NO}
: ${mysql_dbdir=/var/db/mysql}
: ${mysql_args=}
mysql_user=mysql
mysql_limits_args=-e -U ${mysql_user}
pidfile=${mysql_dbdir}/`/bin/hostname`.pid
command=/usr/local/bin/mysqld_safe
command_args=--defaults-extra-file=${mysql_dbdir}/my.cnf
--user=${mysql_user} --datadir=${mysql_dbdir} --pid-file=${pidfile}
${mysql_args} /dev/null
procname=/usr/local/libexec/mysqld
start_precmd=${name}_prestart
mysql_install_db=/usr/local/bin/mysql_install_db
mysql_install_db_args=--ldata=${mysql_dbdir}
mysql_create_auth_tables()
{
eval $mysql_install_db $mysql_install_db_args /dev/null
[ $? -eq 0 ] chown -R ${mysql_user}:${mysql_user} ${mysql_dbdir}
}
mysql_prestart()
{
if [ ! -d ${mysql_dbdir}/mysql/. ]; then
mysql_create_auth_tables || return 1
fi
if checkyesno mysql_limits; then
eval `/usr/bin/limits ${mysql_limits_args}` 2/dev/null
else
return 0
fi
}
run_rc_command $1
In rc.conf we have:
mysql_enable=YES
mysql_args=--myisam-recover=BACKUP,FORCE
Here is the ps of it running:
# ps -axwww |grep mysql
62025 p0 R+ 0:00.00 grep mysql
78519 p0- I 0:00.01 /bin/sh /usr/local/bin/mysqld_safe
--defaults-extra-file=/var/db/mysql/my.cnf --user=mysql
--datadir=/var/db/mysql --pid-file=/var/db/mysql/hostname.pid
--myisam-recover=BACKUP,FORCE
78548 p0- S180:27.77 /usr/local/libexec/mysqld
--defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local
--datadir=/var/db/mysql --user=mysql --pid-file=/var/db/mysql/hostname.pid
--port=3306 --socket=/tmp/mysql.sock --myisam-recover=BACKUP,FORCE
We could try that, but why would it behave differently to the current sh
script?
Thanks
Ian
On Thu, Feb 28, 2008 at 7:43 PM, Vidal Garza [EMAIL PROTECTED] wrote:
how do you start up?
you can start up from scrip.
#!/bin/sh
id=02
ip=192.168.0.42
sockfile=/tmp/mysql$id.sock
user=mysql
datdir=/var/db/mysql$id
port=3306
/bin/sh /usr/local/bin/mysqld_safe --user=$user --datadir=$datdir
--bind-address=$ip --port=$port --sock=$sockfile
Ian escribió:
Hi,
I am running mysql 5.0.45 on freebsd 4.11 and for some strange reason
/tmp/mysql.sock keeps on disappearing and we are forced to kill -9 mysql
and
restart it causing db corruptions as there is no other way of telling it
to
stop once that file has gone. I have tried to find any reason why this
happens and there are no errors, no core files, nothing - the file just
disappears.
Here is the error when trying to login:
# mysql -p cache
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
Here is my my.cnf file:
[client]
port= 3306
socket = /tmp/mysql.sock
[mysqld]
port= 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
#log-bin
server-id = 1
# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 1
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/db/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/db/mysql/
#innodb_log_arch_dir = /var/db/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
Re: Question about reading info from another table.
On Thu, Feb 28, 2008 at 10:59 AM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to write a PHP application that reads info from a MySQL database, and I'm wondering if I can set up a column in one table that gets it's info from a field in another table automatically? Ie: Table1: field1 field2 field3 Table2: field4 field5 field6 = field1 Does that make sense? Would that be a join? Or maybe a primary key? I'm new to MySQL programming so RTFM's are appreciated as long as M is defined :) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] Tip for future questions: Figure out the simplest way to present the question and include the SQL to create the relevant tables. Next explain what you want, any non working sql you have, and lastly give an example result of correct output. Example: So lets say I have two tables: CREATE TABLE `t1` ( `t1_id` int(10) NOT NULL auto_increment, `t1_data` varchar(255) NOT NULL default '', `t2_id` int(10) NOT NULL default '0', PRIMARY KEY (`t1_id`), KEY `t2_id` (`t2_id`) ); CREATE TABLE `t2` ( `t2_id` int(10) NOT NULL auto_increment, `t2_data` varchar(255) NOT NULL default '', PRIMARY KEY (`t2_id`) ); I want to show all the information in t1 and any information in t2 where the t1.t2_id is equal to t2.t2_id. Output should be like: t1_id, t1_data, t2_data *Answer* I really am not sure what you were asking, but take a look at this query for the table structure above. SELECT t1_id, t1_data, t2_data FROM t1 INNER JOIN t2 USING(t2_id) *Better answer* Go buy an introductory book on sql. Read through a couple examples. ( http://www.w3schools.com/sql/default.asp is also very good) From the above question you probably do not know enough to tread water in the very excellent MySQL manual. -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Preferences?
1. Blobs suck. I suggest a serialized array or JSON instead of a BLOB. 2. I have used this before and would love to know what the design pattern is called. This patterns works well, though I would not be surprised to see it called an anti-pattern. Adding fields make the normalized table grow very quickly... 3. I agree with Dan. On Thu, Feb 28, 2008 at 5:25 AM, Dan Buettner [EMAIL PROTECTED] wrote: Waynn, I've used both schemes 1 and 2 as you describe, and in my experience 2 is the best way to go. It's easy to scale up as you add users and settings, and it's easy to make changes if the meaning of settings should change (i.e. you need to do a backend change to people's settings). #1 is harder to make those kind of back end updates on, and harder for someone troubleshooting to make sense of the data. #3 may not scale well - you would end up having to track too many tables, I think. What I'm doing in my current project is using a data model that has a method for each preference setting, and returns a sensible value by default if the user has no pref set for a given lookup key; otherwise, I return what the user has set. This means adding a method every time I add a preference setting, which on the one hand means adding code - on the other hand, chances are very high that if I am adding the ability for a user to set a preference, I'm already adding code somewhere to ensure that preference has an effect. HTH, Dan On Thu, Feb 28, 2008 at 9:50 AM, Waynn Lue [EMAIL PROTECTED] wrote: I'm looking for a good way to store user preferences. The most straightforward way is just to add a column to the Users table for each preference we're looking to store. Downside is that it requires an ALTER TABLE which gets prohibitively expensive as it gets larger, as it's fairly inflexible. I've come up with a few alternatives, and I'm wondering if people have ideas or suggestions, as this has to be a common problem. A quick Google search didn't turn up anything. 1. Store the preferences as a binary blob on the Users table. This blob could be either a blob, or an integer that I use application logic to read/write from, or I could use the SET datatype. 2. Store the preferences in normalized form, with a new table called UserPreferences that has UserId, Setting, Preference and we add a row for each setting of that user. 3. Create a separate table each time we want to add a new setting, UserId, WhateverTheNameOfThePreferenceIs. Anyone have any experience with this, or better suggestions? Thanks, Waynn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: /tmp/mysql.sock dissapears
Its for test.
put the log file on my.cnf and tellus what going on
my.cnf
...
log-error=/var/db/mysql/Server_Error.log
Ian escribió:
Hi,
We use the following sh script to start (its the default one when installed)
cat /usr/local/etc/rc.d/mysql-server.sh
#!/bin/sh
#
# $FreeBSD: ports/databases/mysql50-server/files/mysql-server.sh.in,v
1.32006/03/07 16:25:00 ale Exp $
#
# PROVIDE: mysql
# REQUIRE: LOGIN
# KEYWORD: shutdown
#
# Add the following line to /etc/rc.conf to enable mysql:
# mysql_enable (bool): Set to NO by default.
# Set it to YES to enable MySQL.
# mysql_limits (bool): Set to NO by default.
# Set it to yes to run `limits -e -U mysql`
# just before mysql starts.
# mysql_dbdir (str):Default to /var/db/mysql
# Base database directory.
# mysql_args (str): Custom additional arguments to be passed
# to mysqld_safe (default empty).
#
. /etc/rc.subr
name=mysql
rcvar=`set_rcvar`
load_rc_config $name
: ${mysql_enable=NO}
: ${mysql_limits=NO}
: ${mysql_dbdir=/var/db/mysql}
: ${mysql_args=}
mysql_user=mysql
mysql_limits_args=-e -U ${mysql_user}
pidfile=${mysql_dbdir}/`/bin/hostname`.pid
command=/usr/local/bin/mysqld_safe
command_args=--defaults-extra-file=${mysql_dbdir}/my.cnf
--user=${mysql_user} --datadir=${mysql_dbdir} --pid-file=${pidfile}
${mysql_args} /dev/null
procname=/usr/local/libexec/mysqld
start_precmd=${name}_prestart
mysql_install_db=/usr/local/bin/mysql_install_db
mysql_install_db_args=--ldata=${mysql_dbdir}
mysql_create_auth_tables()
{
eval $mysql_install_db $mysql_install_db_args /dev/null
[ $? -eq 0 ] chown -R ${mysql_user}:${mysql_user} ${mysql_dbdir}
}
mysql_prestart()
{
if [ ! -d ${mysql_dbdir}/mysql/. ]; then
mysql_create_auth_tables || return 1
fi
if checkyesno mysql_limits; then
eval `/usr/bin/limits ${mysql_limits_args}` 2/dev/null
else
return 0
fi
}
run_rc_command $1
In rc.conf we have:
mysql_enable=YES
mysql_args=--myisam-recover=BACKUP,FORCE
Here is the ps of it running:
# ps -axwww |grep mysql
62025 p0 R+ 0:00.00 grep mysql
78519 p0- I 0:00.01 /bin/sh /usr/local/bin/mysqld_safe
--defaults-extra-file=/var/db/mysql/my.cnf --user=mysql
--datadir=/var/db/mysql --pid-file=/var/db/mysql/hostname.pid
--myisam-recover=BACKUP,FORCE
78548 p0- S180:27.77 /usr/local/libexec/mysqld
--defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local
--datadir=/var/db/mysql --user=mysql --pid-file=/var/db/mysql/hostname.pid
--port=3306 --socket=/tmp/mysql.sock --myisam-recover=BACKUP,FORCE
We could try that, but why would it behave differently to the current sh
script?
Thanks
Ian
On Thu, Feb 28, 2008 at 7:43 PM, Vidal Garza [EMAIL PROTECTED] wrote:
how do you start up?
you can start up from scrip.
#!/bin/sh
id=02
ip=192.168.0.42
sockfile=/tmp/mysql$id.sock
user=mysql
datdir=/var/db/mysql$id
port=3306
/bin/sh /usr/local/bin/mysqld_safe --user=$user --datadir=$datdir
--bind-address=$ip --port=$port --sock=$sockfile
Ian escribió:
Hi,
I am running mysql 5.0.45 on freebsd 4.11 and for some strange reason
/tmp/mysql.sock keeps on disappearing and we are forced to kill -9 mysql
and
restart it causing db corruptions as there is no other way of telling it
to
stop once that file has gone. I have tried to find any reason why this
happens and there are no errors, no core files, nothing - the file just
disappears.
Here is the error when trying to login:
# mysql -p cache
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
Here is my my.cnf file:
[client]
port= 3306
socket = /tmp/mysql.sock
[mysqld]
port= 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
#log-bin
server-id = 1
# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 1
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/db/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/db/mysql/
#innodb_log_arch_dir = /var/db/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
Re: Question about reading info from another table.
On Feb 28, 2008, at 1:29 PM, Rob Wultsch wrote: On Thu, Feb 28, 2008 at 10:59 AM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to write a PHP application that reads info from a MySQL database, and I'm wondering if I can set up a column in one table that gets it's info from a field in another table automatically? Ie: Table1: field1 field2 field3 Table2: field4 field5 field6 = field1 Does that make sense? Would that be a join? Or maybe a primary key? I'm new to MySQL programming so RTFM's are appreciated as long as M is defined :) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] Tip for future questions: Figure out the simplest way to present the question and include the SQL to create the relevant tables. Next explain what you want, any non working sql you have, and lastly give an example result of correct output. Hi Rob, I will do this in the future, thank you. And to that end: CREATE TABLE `current` ( `customerName` varchar(30) default NULL, `customerBusiness` varchar(30) default NULL, `loginName` varchar(30) default NULL, `loginPassword` varchar(32) default NULL, `tableName` varchar(20) default NULL, `email` varchar(50) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `adminAll` ( `dispalyTableName` varchar(20) default NULL, `adminLevel` int(10) default NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 What I want, is displayTableName on table adminAll to grab it's info from tableName in current. Does that make more sense? Example: So lets say I have two tables: CREATE TABLE `t1` ( `t1_id` int(10) NOT NULL auto_increment, `t1_data` varchar(255) NOT NULL default '', `t2_id` int(10) NOT NULL default '0', PRIMARY KEY (`t1_id`), KEY `t2_id` (`t2_id`) ); CREATE TABLE `t2` ( `t2_id` int(10) NOT NULL auto_increment, `t2_data` varchar(255) NOT NULL default '', PRIMARY KEY (`t2_id`) ); I want to show all the information in t1 and any information in t2 where the t1.t2_id is equal to t2.t2_id. Output should be like: t1_id, t1_data, t2_data *Answer* I really am not sure what you were asking, but take a look at this query for the table structure above. SELECT t1_id, t1_data, t2_data FROM t1 INNER JOIN t2 USING(t2_id) *Better answer* Go buy an introductory book on sql. Read through a couple examples. ( http://www.w3schools.com/sql/default.asp is also very good) I have been working with MySQL in various degrees for the past few years, I've just never needed to grab info from another table and import it to a different table. My Main area of expertise is in web design (mostly HTML and CSS) and some PHP. From the above question you probably do not know enough to tread water in the very excellent MySQL manual. From my original post: Does that make sense? Would that be a join? Or maybe a primary key? I'm new to MySQL programming so RTFM's are appreciated as long as M is defined :) -- Rob Wultsch -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd Update Question.
http://dev.mysql.com/doc/refman/5.0/en/update.html See multiple table syntax. I have had issues with the syntax (IMHO), and is not available on 3.23 (I am a poor soul that still has to deal 3.23). If you have all the data and you can not figure out the syntax you can alternatively use INSERT... SELECT (and multi table works in 3.23) and recreate the tables. I generally like this approach because it is non destructive in that you still have the old table in case you have an error in your logic somwhere. On Wed, Feb 27, 2008 at 8:09 PM, m i l e s [EMAIL PROTECTED] wrote: Hi, I'm wondering if the following can be done UPDATE tbe_gallery SET tbe_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper WHERE tbe_gallery.gallery_id = tbe_images.img_rel_id AND tbe_images.img_orig_filename = tbe_gsa.gsa_id Let me explain: I have 3 tables and only 1 of them has the correct data which I need to update the other two. The SQL statement above is based upon the following select statement below: SELECT tbe_gsa.gsa_id, tbe_gallery.gallery_id, tbe_gallery.gallery_title, tbe_gallery.gallery_price, tbe_gsa.gsa_sin, tbe_gsa.gsa_paperprice, tbe_gsa.gsa_canvasprice FROM tbe_gsa INNER JOIN tbe_images ON tbe_gsa.gsa_id = tbe_images.img_orig_filename INNER JOIN tbe_gallery ON tbe_images.img_rel_id = tbe_gallery.gallery_id ORDER BY gsa_id ASC This statement works just fine. However the table tbe_gsa contains the necessary column tbe_gsa.gsa_paperprice which has a match field of gsa_id, which matches a field in the images table called tbe_images.img_orig_filename, and the images table contains a match field called tbe_images.img_rel_id, which matches a field in the gallery table called tbe_galery.gallery_id. So my question is how do use the corresponding match fields to update the necessary fields so that... be_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper Is my statement above anywhere close to what it should be Miles. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about reading info from another table.
What you are probably wanting is a join, but how does adminAll relate to current? Generally it is a good idea to have the column that relates the tables (read http://en.wikipedia.org/wiki/Foreign_key if you want to work at a much high level ) have the same column name if possible (IMHO). In the example I sent I had a column in both tables name t2_id. Take a look at http://www.w3schools.com/sql/sql_join.asp for simple joins. The syntax in the first couple examples (without using JOIN) is a good idea to avoid (also IMHO) . The new table you sent is good, but it is more ideal to remove unnecessary columns and make the table /column names generic. On Thu, Feb 28, 2008 at 11:49 AM, Jason Pruim [EMAIL PROTECTED] wrote: On Feb 28, 2008, at 1:29 PM, Rob Wultsch wrote: On Thu, Feb 28, 2008 at 10:59 AM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to write a PHP application that reads info from a MySQL database, and I'm wondering if I can set up a column in one table that gets it's info from a field in another table automatically? Ie: Table1: field1 field2 field3 Table2: field4 field5 field6 = field1 Does that make sense? Would that be a join? Or maybe a primary key? I'm new to MySQL programming so RTFM's are appreciated as long as M is defined :) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] Tip for future questions: Figure out the simplest way to present the question and include the SQL to create the relevant tables. Next explain what you want, any non working sql you have, and lastly give an example result of correct output. Hi Rob, I will do this in the future, thank you. And to that end: CREATE TABLE `current` ( `customerName` varchar(30) default NULL, `customerBusiness` varchar(30) default NULL, `loginName` varchar(30) default NULL, `loginPassword` varchar(32) default NULL, `tableName` varchar(20) default NULL, `email` varchar(50) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `adminAll` ( `dispalyTableName` varchar(20) default NULL, `adminLevel` int(10) default NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 What I want, is displayTableName on table adminAll to grab it's info from tableName in current. Does that make more sense? Example: So lets say I have two tables: CREATE TABLE `t1` ( `t1_id` int(10) NOT NULL auto_increment, `t1_data` varchar(255) NOT NULL default '', `t2_id` int(10) NOT NULL default '0', PRIMARY KEY (`t1_id`), KEY `t2_id` (`t2_id`) ); CREATE TABLE `t2` ( `t2_id` int(10) NOT NULL auto_increment, `t2_data` varchar(255) NOT NULL default '', PRIMARY KEY (`t2_id`) ); I want to show all the information in t1 and any information in t2 where the t1.t2_id is equal to t2.t2_id. Output should be like: t1_id, t1_data, t2_data *Answer* I really am not sure what you were asking, but take a look at this query for the table structure above. SELECT t1_id, t1_data, t2_data FROM t1 INNER JOIN t2 USING(t2_id) *Better answer* Go buy an introductory book on sql. Read through a couple examples. ( http://www.w3schools.com/sql/default.asp is also very good) I have been working with MySQL in various degrees for the past few years, I've just never needed to grab info from another table and import it to a different table. My Main area of expertise is in web design (mostly HTML and CSS) and some PHP. From the above question you probably do not know enough to tread water in the very excellent MySQL manual. From my original post: Does that make sense? Would that be a join? Or maybe a primary key? I'm new to MySQL programming so RTFM's are appreciated as long as M is defined :) -- Rob Wultsch -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about reading info from another table.
On Feb 28, 2008, at 2:06 PM, Rob Wultsch wrote: What you are probably wanting is a join, but how does adminAll relate to current? adminAll will be for the administrators of my program to log into so instead of getting redirected automatically to a certain table (current.tableName in this case) they get a list of available tables in the database (adminAll.displayTableName) so that I don't have to have a separate admin login for each database I setup with my program. Generally it is a good idea to have the column that relates the tables (read http://en.wikipedia.org/wiki/Foreign_key if you want to work at a much high level ) have the same column name if possible (IMHO). the column name won't be an issue since I'm writing it all from scratch :) making it the same name to help improve readability between the tables in the database I'm assuming? In the example I sent I had a column in both tables name t2_id. Take a look at http://www.w3schools.com/sql/sql_join.asp for simple joins. The syntax in the first couple examples (without using JOIN) is a good idea to avoid (also IMHO) . I will look at those as soon as I'm done sending this e-mail! Thank you The new table you sent is good, but it is more ideal to remove unnecessary columns and make the table /column names generic. Can I ask why? So far, everything I have done with MySQL would seem to suggest setting column names so it makes sense what info is stored in it? (IE: First Name would go into FName or firstname or namefirst or something like that) On Thu, Feb 28, 2008 at 11:49 AM, Jason Pruim [EMAIL PROTECTED] wrote: On Feb 28, 2008, at 1:29 PM, Rob Wultsch wrote: On Thu, Feb 28, 2008 at 10:59 AM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to write a PHP application that reads info from a MySQL database, and I'm wondering if I can set up a column in one table that gets it's info from a field in another table automatically? Ie: Table1: field1 field2 field3 Table2: field4 field5 field6 = field1 Does that make sense? Would that be a join? Or maybe a primary key? I'm new to MySQL programming so RTFM's are appreciated as long as M is defined :) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] Tip for future questions: Figure out the simplest way to present the question and include the SQL to create the relevant tables. Next explain what you want, any non working sql you have, and lastly give an example result of correct output. Hi Rob, I will do this in the future, thank you. And to that end: CREATE TABLE `current` ( `customerName` varchar(30) default NULL, `customerBusiness` varchar(30) default NULL, `loginName` varchar(30) default NULL, `loginPassword` varchar(32) default NULL, `tableName` varchar(20) default NULL, `email` varchar(50) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `adminAll` ( `dispalyTableName` varchar(20) default NULL, `adminLevel` int(10) default NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 What I want, is displayTableName on table adminAll to grab it's info from tableName in current. Does that make more sense? Example: So lets say I have two tables: CREATE TABLE `t1` ( `t1_id` int(10) NOT NULL auto_increment, `t1_data` varchar(255) NOT NULL default '', `t2_id` int(10) NOT NULL default '0', PRIMARY KEY (`t1_id`), KEY `t2_id` (`t2_id`) ); CREATE TABLE `t2` ( `t2_id` int(10) NOT NULL auto_increment, `t2_data` varchar(255) NOT NULL default '', PRIMARY KEY (`t2_id`) ); I want to show all the information in t1 and any information in t2 where the t1.t2_id is equal to t2.t2_id. Output should be like: t1_id, t1_data, t2_data *Answer* I really am not sure what you were asking, but take a look at this query for the table structure above. SELECT t1_id, t1_data, t2_data FROM t1 INNER JOIN t2 USING(t2_id) *Better answer* Go buy an introductory book on sql. Read through a couple examples. ( http://www.w3schools.com/sql/default.asp is also very good) I have been working with MySQL in various degrees for the past few years, I've just never needed to grab info from another table and import it to a different table. My Main area of expertise is in web design (mostly HTML and CSS) and some PHP. From the above question you probably do not know enough to tread water in the very excellent MySQL manual. From my original post: Does that make sense? Would that be a join? Or maybe a primary key? I'm new to MySQL programming so RTFM's are appreciated as long as M is defined :) -- Rob Wultsch -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn)
Re: Question about reading info from another table.
I was referering to what you sent into the mysql user list. Descriptive table/columns are ideal in production. When asking for assistance it is ideal to remove extraneous detail. On Thu, Feb 28, 2008 at 12:18 PM, Jason Pruim [EMAIL PROTECTED] wrote: On Feb 28, 2008, at 2:06 PM, Rob Wultsch wrote: What you are probably wanting is a join, but how does adminAll relate to current? adminAll will be for the administrators of my program to log into so instead of getting redirected automatically to a certain table (current.tableName in this case) they get a list of available tables in the database (adminAll.displayTableName) so that I don't have to have a separate admin login for each database I setup with my program. Generally it is a good idea to have the column that relates the tables (read http://en.wikipedia.org/wiki/Foreign_key if you want to work at a much high level ) have the same column name if possible (IMHO). the column name won't be an issue since I'm writing it all from scratch :) making it the same name to help improve readability between the tables in the database I'm assuming? In the example I sent I had a column in both tables name t2_id. Take a look at http://www.w3schools.com/sql/sql_join.asp for simple joins. The syntax in the first couple examples (without using JOIN) is a good idea to avoid (also IMHO) . I will look at those as soon as I'm done sending this e-mail! Thank you The new table you sent is good, but it is more ideal to remove unnecessary columns and make the table /column names generic. Can I ask why? So far, everything I have done with MySQL would seem to suggest setting column names so it makes sense what info is stored in it? (IE: First Name would go into FName or firstname or namefirst or something like that) On Thu, Feb 28, 2008 at 11:49 AM, Jason Pruim [EMAIL PROTECTED] wrote: On Feb 28, 2008, at 1:29 PM, Rob Wultsch wrote: On Thu, Feb 28, 2008 at 10:59 AM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to write a PHP application that reads info from a MySQL database, and I'm wondering if I can set up a column in one table that gets it's info from a field in another table automatically? Ie: Table1: field1 field2 field3 Table2: field4 field5 field6 = field1 Does that make sense? Would that be a join? Or maybe a primary key? I'm new to MySQL programming so RTFM's are appreciated as long as M is defined :) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] Tip for future questions: Figure out the simplest way to present the question and include the SQL to create the relevant tables. Next explain what you want, any non working sql you have, and lastly give an example result of correct output. Hi Rob, I will do this in the future, thank you. And to that end: CREATE TABLE `current` ( `customerName` varchar(30) default NULL, `customerBusiness` varchar(30) default NULL, `loginName` varchar(30) default NULL, `loginPassword` varchar(32) default NULL, `tableName` varchar(20) default NULL, `email` varchar(50) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `adminAll` ( `dispalyTableName` varchar(20) default NULL, `adminLevel` int(10) default NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 What I want, is displayTableName on table adminAll to grab it's info from tableName in current. Does that make more sense? Example: So lets say I have two tables: CREATE TABLE `t1` ( `t1_id` int(10) NOT NULL auto_increment, `t1_data` varchar(255) NOT NULL default '', `t2_id` int(10) NOT NULL default '0', PRIMARY KEY (`t1_id`), KEY `t2_id` (`t2_id`) ); CREATE TABLE `t2` ( `t2_id` int(10) NOT NULL auto_increment, `t2_data` varchar(255) NOT NULL default '', PRIMARY KEY (`t2_id`) ); I want to show all the information in t1 and any information in t2 where the t1.t2_id is equal to t2.t2_id. Output should be like: t1_id, t1_data, t2_data *Answer* I really am not sure what you were asking, but take a look at this query for the table structure above. SELECT t1_id, t1_data, t2_data FROM t1 INNER JOIN t2 USING(t2_id) *Better answer* Go buy an introductory book on sql. Read through a couple examples. ( http://www.w3schools.com/sql/default.asp is also very good) I have been working with MySQL in various degrees for the past few years, I've just never needed to grab info from another table and import it to a different table. My Main area of expertise is in web
Re: Question about reading info from another table.
Jason Pruim wrote: I am attempting to write a PHP application that reads info from a MySQL database, and I'm wondering if I can set up a column in one table that gets it's info from a field in another table automatically? Ie: Table1: field1 field2 field3 Table2: field4 field5 field6 = field1 All the responses have approached this as a join problem. But if you want a field in a table to equal the data in a field in another table (field6 = field1) then perhaps what you really need is a trigger on Table2 that will grab data out of Table1 on insert into Table2, or change data in Table2 on update to Table1. Jim Lyons - Jim Lyons MySQL DBA Hoover's, Inc. 512-380-4780 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about reading info from another table.
If he is not sure what a join or primary key is then I do not think a correct solution for anything he is working on would involve a trigger. In October '07 Baron Schwartz said the following: I'm not an expert on them, but as a side note: I personally don't use triggers in MySQL. I consider them rather poorly implemented in MySQL 5 and I'm afraid of them breaking replication, or doing something else I don't like. When we took a look at them at my employer, we found basic things wrong with them, like this: http://bugs.mysql.com/bug.php?id=19686 Therefore I do not trust them at all, and that's why I don't know a whole lot about how to use them :) Which is a good enough reason for me to not use them. On Thu, Feb 28, 2008 at 1:10 PM, Lyons, Jim [EMAIL PROTECTED] wrote: Jason Pruim wrote: I am attempting to write a PHP application that reads info from a MySQL database, and I'm wondering if I can set up a column in one table that gets it's info from a field in another table automatically? Ie: Table1: field1 field2 field3 Table2: field4 field5 field6 = field1 All the responses have approached this as a join problem. But if you want a field in a table to equal the data in a field in another table (field6 = field1) then perhaps what you really need is a trigger on Table2 that will grab data out of Table1 on insert into Table2, or change data in Table2 on update to Table1. Jim Lyons - Jim Lyons MySQL DBA Hoover's, Inc. 512-380-4780 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: /tmp/mysql.sock dissapears
Does the system in question have some type of /tmp cleaner script that might be removing the socket file? Check /etc/crontab and root's crontab (crontab -l) Steve
Re: /tmp/mysql.sock dissapears
Hi,
Okay, I have added that and will wait and see when it happens again if there
is anything in that log. Just out of interest, does that log show anything
different to the /var/db/mysql/hostnameofunit.err file ?
Cheers
Ian
On Thu, Feb 28, 2008 at 8:44 PM, Vidal Garza [EMAIL PROTECTED] wrote:
Its for test.
put the log file on my.cnf and tellus what going on
my.cnf
...
log-error=/var/db/mysql/Server_Error.log
Ian escribió:
Hi,
We use the following sh script to start (its the default one when
installed)
cat /usr/local/etc/rc.d/mysql-server.sh
#!/bin/sh
#
# $FreeBSD: ports/databases/mysql50-server/files/mysql-server.sh.in,v
1.32006/03/07 16:25:00 ale Exp $
#
# PROVIDE: mysql
# REQUIRE: LOGIN
# KEYWORD: shutdown
#
# Add the following line to /etc/rc.conf to enable mysql:
# mysql_enable (bool): Set to NO by default.
# Set it to YES to enable MySQL.
# mysql_limits (bool): Set to NO by default.
# Set it to yes to run `limits -e -U mysql`
# just before mysql starts.
# mysql_dbdir (str):Default to /var/db/mysql
# Base database directory.
# mysql_args (str): Custom additional arguments to be passed
# to mysqld_safe (default empty).
#
. /etc/rc.subr
name=mysql
rcvar=`set_rcvar`
load_rc_config $name
: ${mysql_enable=NO}
: ${mysql_limits=NO}
: ${mysql_dbdir=/var/db/mysql}
: ${mysql_args=}
mysql_user=mysql
mysql_limits_args=-e -U ${mysql_user}
pidfile=${mysql_dbdir}/`/bin/hostname`.pid
command=/usr/local/bin/mysqld_safe
command_args=--defaults-extra-file=${mysql_dbdir}/my.cnf
--user=${mysql_user} --datadir=${mysql_dbdir} --pid-file=${pidfile}
${mysql_args} /dev/null
procname=/usr/local/libexec/mysqld
start_precmd=${name}_prestart
mysql_install_db=/usr/local/bin/mysql_install_db
mysql_install_db_args=--ldata=${mysql_dbdir}
mysql_create_auth_tables()
{
eval $mysql_install_db $mysql_install_db_args /dev/null
[ $? -eq 0 ] chown -R ${mysql_user}:${mysql_user}
${mysql_dbdir}
}
mysql_prestart()
{
if [ ! -d ${mysql_dbdir}/mysql/. ]; then
mysql_create_auth_tables || return 1
fi
if checkyesno mysql_limits; then
eval `/usr/bin/limits ${mysql_limits_args}` 2/dev/null
else
return 0
fi
}
run_rc_command $1
In rc.conf we have:
mysql_enable=YES
mysql_args=--myisam-recover=BACKUP,FORCE
Here is the ps of it running:
# ps -axwww |grep mysql
62025 p0 R+ 0:00.00 grep mysql
78519 p0- I 0:00.01 /bin/sh /usr/local/bin/mysqld_safe
--defaults-extra-file=/var/db/mysql/my.cnf --user=mysql
--datadir=/var/db/mysql --pid-file=/var/db/mysql/hostname.pid
--myisam-recover=BACKUP,FORCE
78548 p0- S180:27.77 /usr/local/libexec/mysqld
--defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local
--datadir=/var/db/mysql --user=mysql
--pid-file=/var/db/mysql/hostname.pid
--port=3306 --socket=/tmp/mysql.sock --myisam-recover=BACKUP,FORCE
We could try that, but why would it behave differently to the current sh
script?
Thanks
Ian
On Thu, Feb 28, 2008 at 7:43 PM, Vidal Garza [EMAIL PROTECTED] wrote:
how do you start up?
you can start up from scrip.
#!/bin/sh
id=02
ip=192.168.0.42
sockfile=/tmp/mysql$id.sock
user=mysql
datdir=/var/db/mysql$id
port=3306
/bin/sh /usr/local/bin/mysqld_safe --user=$user --datadir=$datdir
--bind-address=$ip --port=$port --sock=$sockfile
Ian escribió:
Hi,
I am running mysql 5.0.45 on freebsd 4.11 and for some strange reason
/tmp/mysql.sock keeps on disappearing and we are forced to kill -9
mysql
and
restart it causing db corruptions as there is no other way of telling
it
to
stop once that file has gone. I have tried to find any reason why this
happens and there are no errors, no core files, nothing - the file
just
disappears.
Here is the error when trying to login:
# mysql -p cache
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)
Here is my my.cnf file:
[client]
port= 3306
socket = /tmp/mysql.sock
[mysqld]
port= 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
#log-bin
server-id = 1
# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 1
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/db/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/db/mysql/
Re: /tmp/mysql.sock dissapears
Hi, No, there is nothing in there cleaning /tmp. Mind you, further investigating there are still some temp files in there from last year - so the directory isnt getting wiped clean. Thanks :) On Fri, Feb 29, 2008 at 3:36 AM, Steve Bernacki [EMAIL PROTECTED] wrote: Does the system in question have some type of /tmp cleaner script that might be removing the socket file? Check /etc/crontab and root's crontab (crontab -l) Steve
