Re: MySQL and Delphi

[Remo Tex]

[EMAIL PROTECTED] wrote:

Hi,

Anyone out there using MySQL and Delphi together?

Would love to exchange experiences.

Regards,

John
John Barrington
[EMAIL PROTECTED]
+27 11 6489876


You'd better try Borland's newsgroups like:
borland.public.delphi.database.dbexpress
 etc. - that's where the problems usually are ;-)
HTH

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: searching in an 'AND' style of query

[starmonkey]

Quoting Chris <[EMAIL PROTECTED]>:

SELECT content.name
FROM content as c
INNER JOIN content_categories as cg
 ON(c.id = cg.content_id)
INNER JOIN categories as g
 ON(cg.category_id = g.id)
WHERE g.id IN (3,5,9)
GROUP BY c.id
HAVING 3 = COUNT(*)


Thanks, Chris - that did the trick alright! I use PHP to make the query 
dynamic, and now I can "drill down" into my content by selecting 
categories in an AND fashion.


cheers!
sm

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: error 1016 : cant open ibd file even though it exists

[Rithish Saralaya]

Hello.

Yes. Maybe a mystery that will remain unsolved for some time; however,
hopefully will get solved.

I deleted all the related files, dropped the database, and recreated it
again freshly from a backup. It was needed to try out some migration of data
across tables, and hence the data was not 'that' critical. I shudder at the
thought of this happening on the live server... I would have ended up having
a rocket lighted behind me...  ;o)

On another note, what gets written into the ibdata1 file, wrt a
database/table? Can't I write/update something into it to rectify any
dangling references it has to any db/table?

Regards,
Rithish.


-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
Sent: Monday, February 27, 2006 1:54 PM
To: mysql@lists.mysql.com
Subject: Re: error 1016 : cant open ibd file even though it exists


Rithish,

- Original Message -
From: ""Rithish Saralaya"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Monday, February 27, 2006 7:06 AM
Subject: RE: error 1016 : cant open ibd file even though it exists


> Heikki.
>
> I found the ibdata1 and ib_log files located in only '/var/lib/mysql'
> which
> is defined as the mysql home folder.
>
> The last time my.cnf was editied was on the 9th of Dec, and the database
> was
> created somewhere in the month of January. So the database would have been
> created with the same configurations.
>
> Thankfully, I have a dump of the original database. Looks like I have to
> restore all of the 35 GB of data again.

then this remains a mystery. InnoDB never deletes ibdata files or
ib_logfiles by itself.

> Regards,
> Rithish.


Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: error 1016 : cant open ibd file even though it exists

[Rithish Saralaya]

Hello Keith.

The power outage was known before-hand, and the server was shutdown before
the outage happened. The server was brought up once the power returned. So
no UPs intervention happened here.

Regards,
Rithish.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Saturday, February 25, 2006 8:13 PM
To: mysql@lists.mysql.com
Subject: RE: error 1016 : cant open ibd file even though it exists



I do not use Innodb tables at the moment, so all this is
pure speculation.

Is/was the server connected to a UPS when the power failure
happened?

If so, did the UPS function properly and do you have any UPS
logs to match against the mysql error log?

Things to check for would be the time the UPS took over from
the mains supply?

If there was no UPS in operation, were your InnoDB tables
corrupted when the power failure occured.

Is it possible for the InnoDB tables to have become so
corrupted that mysql had no alternative but to re-construct
the tables?

What are your config settings for mysql at boot up time?
Do you have any directives to force checking of InnoDB
tables at boot-time, and if so what are they please?

As soon as you mentioned 'power failure' the words 'table
corruption' sprang to my mind.

Also, are there any mysql transaction logs (*.log) you can
check, to see what was happening when the power outage
occured?

Keith

In theory, theory and practice are the same;
In practice they are not.

On Sat, 25 Feb 2006, Rithish Saralaya wrote:

> To: [EMAIL PROTECTED]
> From: Rithish Saralaya <[EMAIL PROTECTED]>
> Subject: RE: error 1016 : cant open ibd file even though it exists
>
> Hello David.
>
> There was supposed to be a power outage in our office that day. So the
> server was shut down. Finally when the power was back, the machine was
> plugged on. That's all. No file system change. Nothing.
>
> Regards,
> Rithish.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: searching in an 'AND' style of query

[Chris]

starmonkey wrote:

q: I want to pull a distinct list of "content" that have a "category" 
(we have a content_categories table linking content ids with category 
ids), BUT I want it to be able to work from multiple categories in an 
AND fashion.


the table structure is, roughly:

content:
 id
 name
 blahblahblah

content_categories:
 content_id
 category_id

categories:
 id
 name
 blahblahblah

Eg:

"give me all content with categories animal (id 3) AND vegetable (id 
5) AND mineral (id 9)"


This could be done something like this, there *might* be a better way, 
but this seems pretty clean to me:


SELECT content.name
FROM content as c
INNER JOIN content_categories as cg
 ON(c.id = cg.content_id)
INNER JOIN categories as g
 ON(cg.category_id = g.id)
WHERE g.id IN (3,5,9)
GROUP BY c.id
HAVING 3 = COUNT(*)


I didn't test this, but the idea is to group the content by content name 
and count how many of the 3,5,9 categories it has.


Anything that matches all three, provided there are no dulicate rows 
incontent_categories, should have a COUNT(*) of 3 rows.



If it's an OR process, it's easy -

SELECT DISTINCT blah blah ... AND c.catid in (10,9,23,11)

- but that's effectively an OR search, which is not what I want.

note: please cc me on list replies, cause I'm on the digest!

thanks,
sm




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Multi Master Replication

[alexj]

On Mon, 27 Feb 2006, Marvin Wright wrote:


Hi,

I'm having a few problems at the moment with replication.
We are in the process of migrating a cache to 2 new database machines.

As the data is a cache its very large is refreshed constantly.

Currently we have 2 machines, 1 master and 1 slave.

I am trying to set up this configuration but I just can not get it to
work.

Master  -->  Slave1  -->  Slave2  -->  Slave3

Master has id 1 and bin-log enabled

Slave1 has id 2 and bin-log enabled but also a do-bin-log=Cache

Slave2 has id 3 and bin-log enabled

Slave3 has id 4

The process is working between Master, Slave1 and Slave2 but for some
reason Slave2 is not writing bin-log information.
So Slave3 is sitting empty and not getting any data because of no data
in Slave2's bin-log.


for slaves to log data from the master  into the binlog you should set 
log-slave-updates=on

Thanx
Alex

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Multi Master Replication

[Ravi Prasad LR]

Marvin,
 > The process is working between Master, Slave1 and Slave2 but 
>for some reason Slave2 is not writing bin-log information.
> So Slave3 is sitting empty and not getting any data because of no data
> in Slave2's bin-log.  
   I guess u don't have 'log-slave-updates' flag enabled also along 
with bin-log in Slave1 and Slave2 in order to have the updates  from master 
by slave sql-thread to be logged in their bin-log.  
   
Have a look here
http://dev.mysql.com/doc/refman/5.0/en/replication-options.html

--Ravi

On Monday 27 February 2006 19:18, Marvin Wright wrote:
> Hi,
>
> I'm having a few problems at the moment with replication.
> We are in the process of migrating a cache to 2 new database machines.
>
> As the data is a cache its very large is refreshed constantly.
>
> Currently we have 2 machines, 1 master and 1 slave.
>
> I am trying to set up this configuration but I just can not get it to
> work.
>
> Master  -->  Slave1  -->  Slave2  -->  Slave3
>
> Master has id 1 and bin-log enabled
>
> Slave1 has id 2 and bin-log enabled but also a do-bin-log=Cache
>
> Slave2 has id 3 and bin-log enabled
>
> Slave3 has id 4
>
> The process is working between Master, Slave1 and Slave2 but for some
> reason Slave2 is not writing bin-log information.
> So Slave3 is sitting empty and not getting any data because of no data
> in Slave2's bin-log.
>
> The idea is to get this process working correctly, then the
> Slave2->Slave1 link broken and Slave2 will become the new Master.
>
> Any idea why this could be a problem ?
>
> All mysql versions are 4.1.12 and running on linux.
>
> Many Thanks
>
> Marvin Wright
> Flights Developer
> Lastminute.com
> [EMAIL PROTECTED]
>
>
>
>
> **
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> the system manager.
>
> This footnote also confirms that this email message has been swept by
> MIMEsweeper for the presence of computer viruses.
>
> www.mimesweeper.com
> **

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: bind-address by name under 5.0.18

[James Long]

> Hi,
> 
> > Thank you very much!  I'm willing to help test if you can provide me a diff 
> > of your changes.
> 
> Well, that was easy.  I checked the code, and it turns out that the 
> functionality is already there.  I just tested it on my laptop (running 
> 5.0.13) and it does indeed work.
> 
> I did:
> 
> * echo "10.0.0.1 foo" >> /etc/hosts
> * ifconfig lo0 alias 10.0.0.1
> * add to /etc/my.cnf:
>[mysqld]
>bind-address=foo
> * restart mysqld
> * mysql -h 10.0.0.1  <-- confirm connection or error from mysqld
> * mysql -h 127.0.0.1 <-- confirm failure to connect at all
> 
> I didn't know that this worked.  Learn something new every day.
> 
> Are you having a problem with it?  Does it not work for you?


Sort of.  If I run the command manually, it does.

But my script uses su to run the server as an unprivileged user.

What happens if you:

su -m mysql -c 'sh -c "/usr/local/bin/mysqld_safe  
--defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql 
--pid-file=/var/db/mysql/www.example.com.pid --bind-address=localhost > 
/dev/null &"'

I'm also hopeful to get multi-IP support, so that I can have the 
server listen on two NICs or what-have-you, such as:

[mysqld]
bind_address=foo bar localhost

I haven't tried that yet, so perhaps it is already functional as 
well, I'll try to get to testing that soon.

And with other DBs, I can set 'sql.example.com' to resolve to N
IPs (N=4 in this example):

10.0.0.10
10.0.1.10
10.0.2.10
10.0.3.10

Then I can have N separate hosts share the same start-up script,
because the SQL server binds only to those IPs which are local.  Since
each of the N servers has only one of those IPs locally, each
server automatically binds to the correct IP.  And if the IPs ever
change, I just update DNS, and there's no need to do any maintenance
on the start-up scripts.  These IPs would not necessarily be in the 
same subnet and in fact, are CNAMEs, such as sql.example.com
defined as:

sql.example.com.IN  CNAME dallas-sql.example.com
sql.example.com.IN  CNAME cleveland-sql.example.com
sql.example.com.IN  CNAME portland-sql.example.com
sql.example.com.IN  CNAME sydney-sql.example.com



I am grateful to be making progress.  Thank you for your assistance.


Jim


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

searching in an 'AND' style of query

[starmonkey]

q: I want to pull a distinct list of "content" that have a "category" 
(we have a content_categories table linking content ids with category 
ids), BUT I want it to be able to work from multiple categories in an 
AND fashion.


the table structure is, roughly:

content:
 id
 name
 blahblahblah

content_categories:
 content_id
 category_id

categories:
 id
 name
 blahblahblah

Eg:

"give me all content with categories animal (id 3) AND vegetable (id 5) 
AND mineral (id 9)"


If it's an OR process, it's easy -

SELECT DISTINCT blah blah ... AND c.catid in (10,9,23,11)

- but that's effectively an OR search, which is not what I want.

note: please cc me on list replies, cause I'm on the digest!

thanks,
sm

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: bind-address by name under 5.0.18

[Jeremy Cole]

Hi,

Thank you very much!  I'm willing to help test if you can provide me a diff 
of your changes.


Well, that was easy.  I checked the code, and it turns out that the 
functionality is already there.  I just tested it on my laptop (running 
5.0.13) and it does indeed work.


I did:

* echo "10.0.0.1 foo" >> /etc/hosts
* ifconfig lo0 alias 10.0.0.1
* add to /etc/my.cnf:
  [mysqld]
  bind-address=foo
* restart mysqld
* mysql -h 10.0.0.1  <-- confirm connection or error from mysqld
* mysql -h 127.0.0.1 <-- confirm failure to connect at all

I didn't know that this worked.  Learn something new every day.

Are you having a problem with it?  Does it not work for you?

Regards,

Jeremy

--
Jeremy Cole
MySQL Geek, Yahoo! Inc.
Desk: 408 349 5104

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: bind-address by name under 5.0.18

[James Long]

> Hi James,
> 
> >>> I would like to be able to bind to a host name, rather than
> >>> an IP number.  IP numbers come and go, and are beyond the
> >>> control of anyone who doesn't have their own direct allocation.  
> >>> But since I own my domain, a host name is more permanent.
> >> Adding this functionality is fairly trivial, with the caveat that it 
> >> will have to use the first address returned by gethostbyname(), so if 
> >> you have multiple IPs assigned to a hostname (for e.g. round-robin DNS) 
> >> it will be "interesting". :)
> > 
> > Thanks for your reply.  Just for my edification, how come only one IP?
> 
> Add another 10 lines and you should be able to make it bind to all IPs 
> returned.  Currently bind-address supports only one IP to bind to.
> 
> This is a reasonable request, I'll take a crack at it.
> 
> Regards,
> 
> Jeremy

Thank you very much!  I'm willing to help test if you can provide me a diff 
of your changes.

Jim

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: bind-address by name under 5.0.18

[Jeremy Cole]

Hi James,


I would like to be able to bind to a host name, rather than
an IP number.  IP numbers come and go, and are beyond the
control of anyone who doesn't have their own direct allocation.  
But since I own my domain, a host name is more permanent.


Adding this functionality is fairly trivial, with the caveat that it 
will have to use the first address returned by gethostbyname(), so if 
you have multiple IPs assigned to a hostname (for e.g. round-robin DNS) 
it will be "interesting". :)


This is probably a 5-10 line patch, including error checking.

Regards,

Jeremy

--
Jeremy Cole
MySQL Geek, Yahoo! Inc.
Desk: 408 349 5104

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Elementary replication

[Rob Gormley]

The curse of the impatient and deadlined sysadmin:

060227 19:22:28 [ERROR] The slave I/O thread stops because master and
slave have different values for the COLLATION_SERVER global variable.
The values must be equal for replication to work

Change default charset, all works. Sorry for the noise. ;)

Rob

> -Original Message-
> From: Rob Gormley [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, 28 February 2006 12:36 PM
> To: mysql@lists.mysql.com
> Subject: Elementary replication
> 
> Hi,
> 
> Not having a lot of luck with replication.
> 
> Have 2 4.1x boxes
> 
> Slave is configured, via my.cnf, to only replicate three tables:
> 
> replicate-do-table = db.table1
> replicate-do-table = db.table2
> replicate-do-table = db.table3
> 
> SHOW SLAVE STATUS; (sanitised) output is as follows:
> 
> mysql> show slave status\G
> *** 1. row ***
>  Slave_IO_State: 
> Master_Host: db0.xxx
> Master_User: xxx
> Master_Port: 3306
>   Connect_Retry: 60
> Master_Log_File: mysql-bin.001305
> Read_Master_Log_Pos: 10165261
>  Relay_Log_File: db2-relay-bin.02
>   Relay_Log_Pos: 4
>   Relay_Master_Log_File: mysql-bin.001305
>Slave_IO_Running: No
>   Slave_SQL_Running: Yes
> Replicate_Do_DB: 
> Replicate_Ignore_DB: 
>  Replicate_Do_Table: db.table1,db.table2,db.table3
>  Replicate_Ignore_Table: 
> Replicate_Wild_Do_Table: 
> Replicate_Wild_Ignore_Table: 
>  Last_Errno: 0
>  Last_Error: 
>Skip_Counter: 0
> Exec_Master_Log_Pos: 10165261
> Relay_Log_Space: 4
> Until_Condition: None
>  Until_Log_File: 
>   Until_Log_Pos: 0
>  Master_SSL_Allowed: No
>  Master_SSL_CA_File: 
>  Master_SSL_CA_Path: 
> Master_SSL_Cert: 
>   Master_SSL_Cipher: 
>  Master_SSL_Key: 
>   Seconds_Behind_Master: NULL
> 1 row in set (0.00 sec)
> 
> 
> Master position was set with "CHANGE MASTER TO 
> MASTER_LOG_FILE='mysql-bin.001305', MASTER_LOG_POS = 10165261"
>  to deal with restoring a mysqldump, and using a running 
> server (I'd really like to avoid shutting down the master)
> 
> Replication has never been run on this machine.
> 
> Where can I go from here to get it running?
> 
> Rob
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mySQL 5 and CPu at 99.99%

[Dan Baughman]

Are you doing a lot of insert statements?

I noticed:
ft_max_word_len: 84
 ft_min_word_len : 2

And if you have sizable full text index insert statements, I could you see
having more than normal cpu usage.




On 2/27/06, Taiyo <[EMAIL PROTECTED]> wrote:
>
> Greetings,
>
> We are running a server and the CPU is at %99.99 at all times, after about
> 2-3 hours of processing queries just hang, sounds like our hardware is
> weak
> but we are running a 4GB RAM Dual Xeons 3.4 at 64bit OS.
>
> I was hoping someone could look at our settings and would help us analyze
> this issue:
>
> Please advise.
>
> Here are the stats:
>
> Some version information:
>
> mySQL version: 5.0.16-standard
> Uname: Linux db.example.com
> 2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:00:54 EDT 2005 x86_64 x86_64 x86_64
> GNU/Linux
> RAM:4GB
> SWAP  1GB
> HD:   2 SCSI 10k RPM on 2 separate
> controllers.
>
> Some information about the load:
> Queries per second avg: 16.346 (about)
> Our biggest table is 3.5 million records and we index 3 of the columns for
> fulltext search
> We do a lot of join queries on 2 tables.
>
> Some mySQL variables:
> [mysqld]
> tmpdir=/db.example.com/tmp
> query_cache_size=1048576
> query_cache_limit = 33554432
> query_cache_size = 33554432
> myisam_sort_buffer_size = 33554432
> sort_buffer_size = 33554432
> max_connections=500
> table_cache = 1000
> max_tmp_tables = 256
>
> Here is all of my mysql -e 'SHOW VARIABLES':
>
> +-+-
> -+
> | Variable_name   | Value
> |
>
> +-+-
> -+
> | auto_increment_increment| 1
> |
> | auto_increment_offset   | 1
> |
> | automatic_sp_privileges | ON
> |
> | back_log| 50
> |
> | basedir | /
> |
> | binlog_cache_size   | 32768
> |
> | bulk_insert_buffer_size | 8388608
> |
> | character_set_client| latin1
> |
> | character_set_connection| latin1
> |
> | character_set_database  | latin1
> |
> | character_set_results   | latin1
> |
> | character_set_server| latin1
> |
> | character_set_system| utf8
> |
> | character_sets_dir  | /usr/share/mysql/charsets/
> |
> | collation_connection| latin1_swedish_ci
> |
> | collation_database  | latin1_swedish_ci
> |
> | collation_server| latin1_swedish_ci
> |
> | completion_type | 0
> |
> | concurrent_insert   | 1
> |
> | connect_timeout | 5
> |
> | datadir | /var/lib/mysql/
> |
> | date_format | %Y-%m-%d
> |
> | datetime_format | %Y-%m-%d %H:%i:%s
> |
> | default_week_format | 0
> |
> | delay_key_write | ON
> |
> | delayed_insert_limit| 100
> |
> | delayed_insert_timeout  | 300
> |
> | delayed_queue_size  | 1000
> |
> | div_precision_increment | 4
> |
> | engine_condition_pushdown   | OFF
> |
> | expire_logs_days| 0
> |
> | flush   | OFF
> |
> | flush_time  | 0
> |
> | ft_boolean_syntax   | + -><()~*:""&|
> |
> | ft_max_word_len | 84
> |
> | ft_min_word_len | 2
> |
> | ft_query_expansion_limit| 20
> |
> | ft_stopword_file| (built-in)
> |
> | group_concat_max_len| 1024
> |
> | have_archive| YES
> |
> | have_bdb| NO
> |
> | have_blackhole_engine   | NO
> |
> | have_compress   | YES
> |
> | have_crypt  | YES
> |
> | have_csv| NO
> |
> | have_example_engine | NO
> |
> | have_federated_engine   | NO
> |
> | have_geometry   | YES
> |
> | have_innodb | YES
> |
> | have_isam   | NO
> |
> | have_ndbcluster | NO
> |
> | have_openssl| NO
> |
> | have_query_cache| YES
> |
> | have_raid   | NO
> |
> | have_rtree_keys | YES
> |
> | have_symlink| YES
> |
> | init_connect|
> |
> | init_file   |
> |
> | init_slave  |
> |
> | innodb_additional_mem_pool_size | 1048576
> |
> | innodb_autoextend_increment | 8
> |
> | innodb_buffer_pool_awe_mem_mb   | 0
> |
> | innodb_buffer_pool_size | 8388608
> |
> | innodb_checksums| ON
> |
> | innodb_commit_concurrency   | 0
> |
> | innodb_concurrency_tickets  | 500
> |
> | innodb_data_file_path 

Elementary replication

[Rob Gormley]

Hi,

Not having a lot of luck with replication.

Have 2 4.1x boxes

Slave is configured, via my.cnf, to only replicate three tables:

replicate-do-table = db.table1
replicate-do-table = db.table2
replicate-do-table = db.table3

SHOW SLAVE STATUS; (sanitised) output is as follows:

mysql> show slave status\G
*** 1. row ***
 Slave_IO_State: 
Master_Host: db0.xxx
Master_User: xxx
Master_Port: 3306
  Connect_Retry: 60
Master_Log_File: mysql-bin.001305
Read_Master_Log_Pos: 10165261
 Relay_Log_File: db2-relay-bin.02
  Relay_Log_Pos: 4
  Relay_Master_Log_File: mysql-bin.001305
   Slave_IO_Running: No
  Slave_SQL_Running: Yes
Replicate_Do_DB: 
Replicate_Ignore_DB: 
 Replicate_Do_Table: db.table1,db.table2,db.table3
 Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
 Last_Errno: 0
 Last_Error: 
   Skip_Counter: 0
Exec_Master_Log_Pos: 10165261
Relay_Log_Space: 4
Until_Condition: None
 Until_Log_File: 
  Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File: 
 Master_SSL_CA_Path: 
Master_SSL_Cert: 
  Master_SSL_Cipher: 
 Master_SSL_Key: 
  Seconds_Behind_Master: NULL
1 row in set (0.00 sec)


Master position was set with "CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.001305', MASTER_LOG_POS = 10165261"
 to deal with restoring a mysqldump, and using a running server (I'd
really like to avoid shutting down the master)

Replication has never been run on this machine.

Where can I go from here to get it running?

Rob

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Multi Master Replication

[Atle Veka]

Your subject is misleading, I would rather call this something like
Chained Replication. Unless your slaves all replicate from 'Master', in
that case your drawing is the culprit. :)

Does the error log on slave3 or slave2 indicate any problems?


Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Mon, 27 Feb 2006, Marvin Wright wrote:

> Hi,
>
> I'm having a few problems at the moment with replication.
> We are in the process of migrating a cache to 2 new database machines.
>
> As the data is a cache its very large is refreshed constantly.
>
> Currently we have 2 machines, 1 master and 1 slave.
>
> I am trying to set up this configuration but I just can not get it to
> work.
>
> Master  -->  Slave1  -->  Slave2  -->  Slave3
>
> Master has id 1 and bin-log enabled
>
> Slave1 has id 2 and bin-log enabled but also a do-bin-log=Cache
>
> Slave2 has id 3 and bin-log enabled
>
> Slave3 has id 4
>
> The process is working between Master, Slave1 and Slave2 but for some
> reason Slave2 is not writing bin-log information.
> So Slave3 is sitting empty and not getting any data because of no data
> in Slave2's bin-log.
>
> The idea is to get this process working correctly, then the
> Slave2->Slave1 link broken and Slave2 will become the new Master.
>
> Any idea why this could be a problem ?
>
> All mysql versions are 4.1.12 and running on linux.
>
> Many Thanks
>
> Marvin Wright
> Flights Developer
> Lastminute.com
> [EMAIL PROTECTED]
>
>
>
>
> **
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> the system manager.
>
> This footnote also confirms that this email message has been swept by
> MIMEsweeper for the presence of computer viruses.
>
> www.mimesweeper.com
> **
>
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MYSQL: Unlimited Forums, Scalablity Issues, advice please?

[jay]

Hi Philip, thanks for the reply.

Single master + many read only slaves would only solve the problem of 
handling many many concurrent read accesses, by distributing the load 
across all slaves.


However, I guess the real problem, is that the writes would still need 
to be performed across ALL databases, and the DB would be HUGE, would it 
not?


Lets say I host 100 forums, with 100k posts each, every write would need 
to be replicated to as many slaves as I have.


Thanks!

Jay



Philip Hallstrom wrote:


Hi there,
I am in the midst of creating a forums hosting site.

1. It has to be highly scalable, so I doubt a single MYSQL db with 
TONS of "subforums that simulate full forums" would do.


2. Also regarding scalablity, I hope to "Add capacity" as and when 
its needed. So i'll have one server running initially, and when it 
gets too crowded, i'll get two servers etc.


3. I will be providing a user with a "dashboard" that allows him to 
view all his subscribed posts across ALL forums. So lets say a user 
is a member of 25 forums, this dashboard view will allow the user to 
view all his posts across all the forums.


Does anyone have advice that could point me in the right direction?
I have solved the scalability issue WITHIN a forum (code can handle 
million + posts easy), but I havent solved the issue of scaling 
MULTIPLE separate forums.



What about having a single write master with many read-only slaves?  
Then modify your code so that posts go to the master and everything 
else happens on the slaves?


Also, does there exist any php package that helps ease the process of 
"deciding which Server/database to connect to"?
For example, someone accesses FORUM A, so the script would 
automatically know to direct all queries to the DB in SERVER 1 etc, 
and if i try to access FORUM J, it would connect to SERVER 2 etc. I 
could easily hard code this, but I was thinking "what if internal IP 
addresses change, or I decide to migrate a busy forum to a server of 
its own etc", so perhaps there is a better available packaged 
solution designed for this task.



Create a table on a "central" server that contains this mapping.  This 
server could also hold the login tables as well...


Just a thought.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: bind-address by name under 5.0.18

[James Long]

> Oh, no. I know about etc/hosts (even Windoze boxes has one). It's just 
> normally not available for reference until you bind your socket library to 
> at least one socket. Again, it's a cart and horse thing. You see, the 
> etc/hosts file counts as a "local" DNS server and the DNS protocol 
> requires a socket (

But an *outgoing* socket for DNS lookups is different from 
the "listening" address the server binds to.

> I am not aware of any IP stack that will allow an etc/hosts lookup until 
> you have at least one socket established (though I could be wrong).

Look, with all due respect to your experience, I can assure
you that none of this is rocket science, nor is it a
chicken-and-egg sort of problem.  It's not impossible to do.
Other databases do it.  IANASE, but I should think a simple
call to gethostbyname(3) should do the trick:

 The structure returned contains either the information 
 obtained from the name server, named(8), broken-out fields 
 from a line in /etc/hosts, or database entries supplied by 
 the yp(8) system.  The order of the lookups is controlled 
 by the `hosts' entry in nsswitch.conf(5).

PostgreSQL's -h option is an example of the functionality
I'm trying to find in MySQL, as I posted in an earlier message
(on which you were not yet cc'ed, and which may not have hit the 
list yet).  It allows the administrator to specify a *list* of
IPs *OR* hostnames that the database server should bind to for
incoming client connections:

"-h hostname

Specifies the IP host name or address on which the postmaster is
to  listen  for TCP/IP connections from client applications. The
value can also be a space-separated list of addresses, or  *  to
specify  listening  on  all available interfaces. An empty value
specifies not listening on any IP addresses, in which case  only
Unix-domain  sockets  can  be used to connect to the postmaster.
Defaults to listening only on localhost.  Specifying this option
is  equivalent  to  setting  the  listen_addresses configuration
parameter."

So I could start Postgres with "-h localhost priv pub sql.example.com"
and bind to those IPs.  Further, if those DNS names resolve to
multiple IPs, then Postgres will bind to as many of them as it
can find on the local machine.

How do I do that with MySQL?

Thanks again for taking the time to answer my questions.

Jim


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Not getting list email?

[Barton L. Phillips]

Thanks. I am pretty sure that I am not getting the list and that it is 
not being caught by my spamassassin. I have looked at my maillog file 
(I'm running Linux) and my spamassassin/mimedefang logs everything even 
if it is ditched. I have gotten a couple of general news things from 
mysql.com but no list mail.


I have been looking at the list on the web site so I can in fact see 
list posts.



sheeri kritzer wrote:

Check your spam folder?  Do you have blacklisting on?

-Sheeri, not replying to the list because you won't see it!

On 2/26/06, Barton L. Phillips <[EMAIL PROTECTED]> wrote:
  

I subscribed to this list about four days ago and am not getting any
mail. I see on the web page that there have been new posts. What is
wrong. What can I do to fix it?

--

Barton L. Phillips
Applied Technology Resources, Inc.
Tel: (818)652-9850
Web: http://www.applitec.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





--

Barton L. Phillips
Applied Technology Resources, Inc.
Tel: (818)652-9850
Web: http://www.applitec.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: email to db

[sheeri kritzer]

Mark,

Databases are not e-mail handlers, so the basic answer is no. 
However, it's possible to have the e-mail handlers update a database
record -- I've seen it done with exim (but don't ask me how!)

-Sheeri

On 2/24/06, Mark <[EMAIL PROTECTED]> wrote:
> Can anyone tell me if it is possible to send an email from outlook to a DB so 
> it updates a record. I have a php tipping script hosted with  a mysql DB but 
> would like users to email their tips instead of loggin on the site.
>
> Mark
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Updating Index Statistics

[SGreen]

"Robert DiFalco" <[EMAIL PROTECTED]> wrote on 02/27/2006 04:53:06 PM:

> Is there a command to regenerating the selectivity statistics of indices
> with MySQL? Or does MySQL not have a cost based optimizer and this would
> make no difference?
> 
> R.

MySQL uses a cost-based optimizer

http://dev.mysql.com/doc/refman/4.1/en/query-speed.html

and the command you are looking for is "ANALYZE TABLE"

http://dev.mysql.com/doc/refman/4.1/en/analyze-table.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: bind-address by name under 5.0.18

[SGreen]

Oh, no. I know about etc/hosts (even Windoze boxes has one). It's just 
normally not available for reference until you bind your socket library to 
at least one socket. Again, it's a cart and horse thing. You see, the 
etc/hosts file counts as a "local" DNS server and the DNS protocol 
requires a socket (

I am not aware of any IP stack that will allow an etc/hosts lookup until 
you have at least one socket established (though I could be wrong).

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



James Long <[EMAIL PROTECTED]> wrote on 02/27/2006 04:26:37 PM:

> You seem to be unaware of /etc/hosts.
> 
> > Yes, there is a very good, not so technical reason to only allow 
binding 
> > by IP Address.  Have you ever heard of "putting the cart before the 
horse" 
> > ?
> > 
> > If binding by hostname were allowed, that would mean that every time 
your 
> > MySQL server started up, it would need to somehow resolve that name 
into 
> > an address. In order to do that, it needs a socket it can use to 
contact a 
> > DNS server in order to change the hostname back into an address. Where 

> > does it get the socket? Without a socket, it cannot contact a DNS 
server 
> > so it cannot resolve it's hostname to an address and it will not be 
able 
> > to bind to a socket.
> 
> I can't speak for your machine, but on my servers, DNS is up before 
> MySQL starts.
> 
> > Binding is the process of giving a socket (an address and a port) to 
an 
> > application so that it has a point of contact on the internet. Without 
a 
> > socket an application has no way to communicate over TCP/IP or UDP/IP 
as 
> > there would be nowhere for the return messages to land. That is the 
simple 
> > reason why you cannot bind to a hostname.
> > 
> > Did I make sense?
> 
> Yes, but none of the information was new.
> 
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> > 
> > James Long <[EMAIL PROTECTED]> wrote on 02/27/2006 03:59:14 
PM:
> > 
> > > > It doesn't allow to bind to a hostname.  According to
> > > > 
> > > > http://dev.mysql.com/doc/refman/5.0/en/server-options.html
> > > > 
> > > > you have to specify an IP address, not a hostname.
> > > > 
> > > > -Sheeri
> > > 
> > > Thanks for your reply.  Yes, I am aware of that, as stated in
> > > my question.
> > > 
> > > Wouldn't it be an improvement if it did, though?  Currently, I
> > > have to use an ugly grep/awk hack to grep the address out of
> > > /etc/hosts.  Are there technical reasons that make it difficult
> > > to write the code such that one could specify an IP or a hostname,
> > > or a list of IPs and/or hostnames?
> > > 
> > > I'm looking for something similar to PostgreSQL's:
> > > 
> > > -h hostname
> > >Specifies the IP host name or address on which the postmaster is
> > >to  listen  for TCP/IP connections from client applications. The
> > >value can also be a space-separated list of addresses, or  *  to
> > >specify  listening  on  all available interfaces. An empty value
> > >specifies not listening on any IP addresses, in which case  only
> > >Unix-domain  sockets  can  be used to connect to the postmaster.
> > >Defaults to listening only on localhost.  Specifying this option
> > >is  equivalent  to  setting  the  listen_addresses configuration
> > >parameter.
> > > 
> > > 
> > > > 
> > > > On 2/22/06, James Long <[EMAIL PROTECTED]> wrote:
> > > > > I would like to be able to bind to a host name, rather than
> > > > > an IP number.  IP numbers come and go, and are beyond the
> > > > > control of anyone who doesn't have their own direct allocation.
> > > > > But since I own my domain, a host name is more permanent.
> > > > >
> > > > > When the time comes to change IP numbers, I want to just edit
> > > > > /etc/hosts to show my new IP:
> > > > >
> > > > > 10.0.0.10   shortname myrealname.example.com
> > > > >
> > > > > and use "--bind-address=shortname" in my start-up script to
> > > > > automatically pick up the new IP number.
> > > > >
> > > > > This eliminates the headache of having to find and fix umpteen
> > > > > literal IP numbers hiding in my startup scripts.  This is much
> > > > > the same reason one says '#define BLKSIZ 512' and then uses the
> > > > > constant 'BLKSIZ' instead of sprinkling literal '512's 
throughout
> > > > > one's code).
> > > > >
> > > > > Why doesn't bind-address allow this?  Or if it does, how can I
> > > > > accomplish this?
> > > > >
> > > > >
> > > > > Thanks!
> > > > >
> > > > >
> > > > > Jim
> > > > >
> > > > > --
> > > > > 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]
> > > 
> > 
> > --=_alternative 0075136A85257122_=
> > Content-Type: 

Updating Index Statistics

[Robert DiFalco]

Is there a command to regenerating the selectivity statistics of indices
with MySQL? Or does MySQL not have a cost based optimizer and this would
make no difference?
 
R.

Re: bind-address by name under 5.0.18

[James Long]

You seem to be unaware of /etc/hosts.

> Yes, there is a very good, not so technical reason to only allow binding 
> by IP Address.  Have you ever heard of "putting the cart before the horse" 
> ?
> 
> If binding by hostname were allowed, that would mean that every time your 
> MySQL server started up, it would need to somehow resolve that name into 
> an address. In order to do that, it needs a socket it can use to contact a 
> DNS server in order to change the hostname back into an address. Where 
> does it get the socket? Without a socket, it cannot contact a DNS server 
> so it cannot resolve it's hostname to an address and it will not be able 
> to bind to a socket.

I can't speak for your machine, but on my servers, DNS is up before 
MySQL starts.

> Binding is the process of giving a socket (an address and a port) to an 
> application so that it has a point of contact on the internet. Without a 
> socket an application has no way to communicate over TCP/IP or UDP/IP as 
> there would be nowhere for the return messages to land. That is the simple 
> reason why you cannot bind to a hostname.
> 
> Did I make sense?

Yes, but none of the information was new.

> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 
> James Long <[EMAIL PROTECTED]> wrote on 02/27/2006 03:59:14 PM:
> 
> > > It doesn't allow to bind to a hostname.  According to
> > > 
> > > http://dev.mysql.com/doc/refman/5.0/en/server-options.html
> > > 
> > > you have to specify an IP address, not a hostname.
> > > 
> > > -Sheeri
> > 
> > Thanks for your reply.  Yes, I am aware of that, as stated in
> > my question.
> > 
> > Wouldn't it be an improvement if it did, though?  Currently, I
> > have to use an ugly grep/awk hack to grep the address out of
> > /etc/hosts.  Are there technical reasons that make it difficult
> > to write the code such that one could specify an IP or a hostname,
> > or a list of IPs and/or hostnames?
> > 
> > I'm looking for something similar to PostgreSQL's:
> > 
> > -h hostname
> >Specifies the IP host name or address on which the postmaster is
> >to  listen  for TCP/IP connections from client applications. The
> >value can also be a space-separated list of addresses, or  *  to
> >specify  listening  on  all available interfaces. An empty value
> >specifies not listening on any IP addresses, in which case  only
> >Unix-domain  sockets  can  be used to connect to the postmaster.
> >Defaults to listening only on localhost.  Specifying this option
> >is  equivalent  to  setting  the  listen_addresses configuration
> >parameter.
> > 
> > 
> > > 
> > > On 2/22/06, James Long <[EMAIL PROTECTED]> wrote:
> > > > I would like to be able to bind to a host name, rather than
> > > > an IP number.  IP numbers come and go, and are beyond the
> > > > control of anyone who doesn't have their own direct allocation.
> > > > But since I own my domain, a host name is more permanent.
> > > >
> > > > When the time comes to change IP numbers, I want to just edit
> > > > /etc/hosts to show my new IP:
> > > >
> > > > 10.0.0.10   shortname myrealname.example.com
> > > >
> > > > and use "--bind-address=shortname" in my start-up script to
> > > > automatically pick up the new IP number.
> > > >
> > > > This eliminates the headache of having to find and fix umpteen
> > > > literal IP numbers hiding in my startup scripts.  This is much
> > > > the same reason one says '#define BLKSIZ 512' and then uses the
> > > > constant 'BLKSIZ' instead of sprinkling literal '512's throughout
> > > > one's code).
> > > >
> > > > Why doesn't bind-address allow this?  Or if it does, how can I
> > > > accomplish this?
> > > >
> > > >
> > > > Thanks!
> > > >
> > > >
> > > > Jim
> > > >
> > > > --
> > > > 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]
> > 
> 
> --=_alternative 0075136A85257122_=
> Content-Type: text/html; charset="US-ASCII"
> 
> 
> Yes, there is a very good, not so technical
> reason to only allow binding by IP Address.  Have you ever heard of
> "putting the cart before the horse" ?
> 
> If binding by hostname were allowed,
> that would mean that every time your MySQL server started up, it would
> need to somehow resolve that name into an address. In order to do that,
> it needs a socket it can use to contact a DNS server in order to change
> the hostname back into an address. Where does it get the socket? Without
> a socket, it cannot contact a DNS server so it cannot resolve it's hostname
> to an address and it will not be able to bind to a socket.
> 
> Binding is the process of giving a socket
> (an address and a port) to an application so that it has a point of contact
> on the internet. Without 

Re: bind-address by name under 5.0.18

[SGreen]

Yes, there is a very good, not so technical reason to only allow binding 
by IP Address.  Have you ever heard of "putting the cart before the horse" 
?

If binding by hostname were allowed, that would mean that every time your 
MySQL server started up, it would need to somehow resolve that name into 
an address. In order to do that, it needs a socket it can use to contact a 
DNS server in order to change the hostname back into an address. Where 
does it get the socket? Without a socket, it cannot contact a DNS server 
so it cannot resolve it's hostname to an address and it will not be able 
to bind to a socket.

Binding is the process of giving a socket (an address and a port) to an 
application so that it has a point of contact on the internet. Without a 
socket an application has no way to communicate over TCP/IP or UDP/IP as 
there would be nowhere for the return messages to land. That is the simple 
reason why you cannot bind to a hostname.

Did I make sense?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

James Long <[EMAIL PROTECTED]> wrote on 02/27/2006 03:59:14 PM:

> > It doesn't allow to bind to a hostname.  According to
> > 
> > http://dev.mysql.com/doc/refman/5.0/en/server-options.html
> > 
> > you have to specify an IP address, not a hostname.
> > 
> > -Sheeri
> 
> Thanks for your reply.  Yes, I am aware of that, as stated in
> my question.
> 
> Wouldn't it be an improvement if it did, though?  Currently, I
> have to use an ugly grep/awk hack to grep the address out of
> /etc/hosts.  Are there technical reasons that make it difficult
> to write the code such that one could specify an IP or a hostname,
> or a list of IPs and/or hostnames?
> 
> I'm looking for something similar to PostgreSQL's:
> 
> -h hostname
>Specifies the IP host name or address on which the postmaster is
>to  listen  for TCP/IP connections from client applications. The
>value can also be a space-separated list of addresses, or  *  to
>specify  listening  on  all available interfaces. An empty value
>specifies not listening on any IP addresses, in which case  only
>Unix-domain  sockets  can  be used to connect to the postmaster.
>Defaults to listening only on localhost.  Specifying this option
>is  equivalent  to  setting  the  listen_addresses configuration
>parameter.
> 
> 
> > 
> > On 2/22/06, James Long <[EMAIL PROTECTED]> wrote:
> > > I would like to be able to bind to a host name, rather than
> > > an IP number.  IP numbers come and go, and are beyond the
> > > control of anyone who doesn't have their own direct allocation.
> > > But since I own my domain, a host name is more permanent.
> > >
> > > When the time comes to change IP numbers, I want to just edit
> > > /etc/hosts to show my new IP:
> > >
> > > 10.0.0.10   shortname myrealname.example.com
> > >
> > > and use "--bind-address=shortname" in my start-up script to
> > > automatically pick up the new IP number.
> > >
> > > This eliminates the headache of having to find and fix umpteen
> > > literal IP numbers hiding in my startup scripts.  This is much
> > > the same reason one says '#define BLKSIZ 512' and then uses the
> > > constant 'BLKSIZ' instead of sprinkling literal '512's throughout
> > > one's code).
> > >
> > > Why doesn't bind-address allow this?  Or if it does, how can I
> > > accomplish this?
> > >
> > >
> > > Thanks!
> > >
> > >
> > > Jim
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> > >
> > 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: LOAD DATA INFILE and BIT columns

[Julie Kelner]

Sheeri,

Wow. That was my first forum email and I thought it would go unnoticed. I 
sure was wrong.


You are exactly right, because apparently with LOAD DATA INFILE, everything 
in the file is treated as a string. I'm using PHP to create the text file, 
so I tried PHP's pack() function to write '2' as binary data. And...it 
worked!


Thanks so much for your input.
~ Julie

- Original Message - 
From: "sheeri kritzer" <[EMAIL PROTECTED]>

To: "Julie Kelner" <[EMAIL PROTECTED]>
Cc: 
Sent: Monday, February 27, 2006 12:50 PM
Subject: Re: LOAD DATA INFILE and BIT columns


Hi Julie,

If you notice after your import, you have 3 warnings.  This intrigued
me, so I created a test case (also running 5.0.18 standard):

create table bit_test (b bit(8));

cat /tmp/bit_test.txt

01010101
2
b'010'
b\'010\'
0x2
02

mysql> load data infile '/tmp/bit_test.txt' into table bit_test;

Query OK, 6 rows affected, 5 warnings (0.05 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 5


Hrm.  I got 5 warnings; you'd only gotten 2.  Weird!


mysql> show warnings;
+-+--+-+
| Level   | Code | Message |
+-+--+-+
| Warning | 1264 | Out of range value adjusted for column 'b' at row 1 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 3 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 4 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 5 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 6 |
+-+--+-+
5 rows in set (0.02 sec)


What this says to me is that the values were too big, for all but row 2.

mysql> select bin(b+0) from bit_test;
+--+
| bin(b+0) |
+--+
|  |
| 110010   |
|  |
|  |
|  |
|  |
+--+
6 rows in set (0.05 sec)

so the  values make sense -- the values were larger than the
largest value, so it truncated it to the largest value.  But why, when
I insert a 2, does it use 11010 instead of 10?

Let's test:


mysql> insert into bit_test VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> select bin(b+0) from bit_test;
+--+
| bin(b+0) |
+--+
|  |
| 110010   |
|  |
|  |
|  |
|  |
| 10   |
+--+
7 rows in set (0.00 sec)


That makes sense!  the last value is 10, which makes sense for a
binary value of 2.  On a hunch, I tried to see what happened if it
treated 2 as a string, not an integer:


mysql> insert into bit_test VALUES ('2');
Query OK, 1 row affected (0.00 sec)

mysql> select bin(b+0) from bit_test;
+--+
| bin(b+0) |
+--+
|  |
| 110010   |
|  |
|  |
|  |
|  |
| 10   |
| 110010   |
+--+
8 rows in set (0.01 sec)


Aha!   the culprit -- it was thinking that the "2" in the file was a
string, not an int.


Hope this helped,

-Sheeri

On 2/24/06, Julie Kelner <[EMAIL PROTECTED]> wrote:
Hi. I'm using MySQL 5.0.18, and I'm trying to use LOAD DATA INFILE into 
tables that have BIT(8) columns. No matter
what format I use, the result is not what I expect (see example below.) 
Anyone know how to properly format the data for loading into a BIT column? 
Thanks!



$ cat /tmp/bit_test.txt
01010101
2
b'010'
b\'010\'
0x2
02


mysql> create table bit_test (b bit(8));
Query OK, 0 rows affected (0.01 sec)

mysql> load data infile '/tmp/bit_test.txt' into table bit_test;
Query OK, 6 rows affected, 3 warnings (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 3

mysql> select bin(b+0) from bit_test;
+

Re: bind-address by name under 5.0.18

[James Long]

> It doesn't allow to bind to a hostname.  According to
> 
> http://dev.mysql.com/doc/refman/5.0/en/server-options.html
> 
> you have to specify an IP address, not a hostname.
> 
> -Sheeri

Thanks for your reply.  Yes, I am aware of that, as stated in
my question.

Wouldn't it be an improvement if it did, though?  Currently, I
have to use an ugly grep/awk hack to grep the address out of
/etc/hosts.  Are there technical reasons that make it difficult
to write the code such that one could specify an IP or a hostname,
or a list of IPs and/or hostnames?

I'm looking for something similar to PostgreSQL's:

-h hostname
   Specifies the IP host name or address on which the postmaster is
   to  listen  for TCP/IP connections from client applications. The
   value can also be a space-separated list of addresses, or  *  to
   specify  listening  on  all available interfaces. An empty value
   specifies not listening on any IP addresses, in which case  only
   Unix-domain  sockets  can  be used to connect to the postmaster.
   Defaults to listening only on localhost.  Specifying this option
   is  equivalent  to  setting  the  listen_addresses configuration
   parameter.


> 
> On 2/22/06, James Long <[EMAIL PROTECTED]> wrote:
> > I would like to be able to bind to a host name, rather than
> > an IP number.  IP numbers come and go, and are beyond the
> > control of anyone who doesn't have their own direct allocation.
> > But since I own my domain, a host name is more permanent.
> >
> > When the time comes to change IP numbers, I want to just edit
> > /etc/hosts to show my new IP:
> >
> > 10.0.0.10   shortname myrealname.example.com
> >
> > and use "--bind-address=shortname" in my start-up script to
> > automatically pick up the new IP number.
> >
> > This eliminates the headache of having to find and fix umpteen
> > literal IP numbers hiding in my startup scripts.  This is much
> > the same reason one says '#define BLKSIZ 512' and then uses the
> > constant 'BLKSIZ' instead of sprinkling literal '512's throughout
> > one's code).
> >
> > Why doesn't bind-address allow this?  Or if it does, how can I
> > accomplish this?
> >
> >
> > Thanks!
> >
> >
> > Jim
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MYSQL: Unlimited Forums, Scalablity Issues, advice please?

[Philip Hallstrom]

Hi there,
I am in the midst of creating a forums hosting site.

1. It has to be highly scalable, so I doubt a single MYSQL db with TONS of 
"subforums that simulate full forums" would do.


2. Also regarding scalablity, I hope to "Add capacity" as and when its 
needed. So i'll have one server running initially, and when it gets too 
crowded, i'll get two servers etc.


3. I will be providing a user with a "dashboard" that allows him to view all 
his subscribed posts across ALL forums. So lets say a user is a member of 25 
forums, this dashboard view will allow the user to view all his posts across 
all the forums.


Does anyone have advice that could point me in the right direction?
I have solved the scalability issue WITHIN a forum (code can handle million + 
posts easy), but I havent solved the issue of scaling MULTIPLE separate 
forums.


What about having a single write master with many read-only slaves?  Then 
modify your code so that posts go to the master and everything else 
happens on the slaves?


Also, does there exist any php package that helps ease the process of 
"deciding which Server/database to connect to"?
For example, someone accesses FORUM A, so the script would automatically know 
to direct all queries to the DB in SERVER 1 etc, and if i try to access FORUM 
J, it would connect to SERVER 2 etc. I could easily hard code this, but I was 
thinking "what if internal IP addresses change, or I decide to migrate a busy 
forum to a server of its own etc", so perhaps there is a better available 
packaged solution designed for this task.


Create a table on a "central" server that contains this mapping.  This 
server could also hold the login tables as well...


Just a thought.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL and Delphi

[johnbarri]

Hi,

Anyone out there using MySQL and Delphi together?

Would love to exchange experiences.

Regards,

John
John Barrington
[EMAIL PROTECTED]
+27 11 6489876

Re: LOAD DATA INFILE and BIT columns

[sheeri kritzer]

Hi Julie,

If you notice after your import, you have 3 warnings.  This intrigued
me, so I created a test case (also running 5.0.18 standard):

create table bit_test (b bit(8));

cat /tmp/bit_test.txt

01010101
2
b'010'
b\'010\'
0x2
02

mysql> load data infile '/tmp/bit_test.txt' into table bit_test;

Query OK, 6 rows affected, 5 warnings (0.05 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 5


Hrm.  I got 5 warnings; you'd only gotten 2.  Weird!


mysql> show warnings;
+-+--+-+
| Level   | Code | Message |
+-+--+-+
| Warning | 1264 | Out of range value adjusted for column 'b' at row 1 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 3 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 4 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 5 |
| Warning | 1264 | Out of range value adjusted for column 'b' at row 6 |
+-+--+-+
5 rows in set (0.02 sec)


What this says to me is that the values were too big, for all but row 2.

mysql> select bin(b+0) from bit_test;
+--+
| bin(b+0) |
+--+
|  |
| 110010   |
|  |
|  |
|  |
|  |
+--+
6 rows in set (0.05 sec)

so the  values make sense -- the values were larger than the
largest value, so it truncated it to the largest value.  But why, when
I insert a 2, does it use 11010 instead of 10?

Let's test:


mysql> insert into bit_test VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> select bin(b+0) from bit_test;
+--+
| bin(b+0) |
+--+
|  |
| 110010   |
|  |
|  |
|  |
|  |
| 10   |
+--+
7 rows in set (0.00 sec)


That makes sense!  the last value is 10, which makes sense for a
binary value of 2.  On a hunch, I tried to see what happened if it
treated 2 as a string, not an integer:


mysql> insert into bit_test VALUES ('2');
Query OK, 1 row affected (0.00 sec)

mysql> select bin(b+0) from bit_test;
+--+
| bin(b+0) |
+--+
|  |
| 110010   |
|  |
|  |
|  |
|  |
| 10   |
| 110010   |
+--+
8 rows in set (0.01 sec)


Aha!   the culprit -- it was thinking that the "2" in the file was a
string, not an int.


Hope this helped,

-Sheeri

On 2/24/06, Julie Kelner <[EMAIL PROTECTED]> wrote:
> Hi. I'm using MySQL 5.0.18, and I'm trying to use LOAD DATA INFILE into 
> tables that have BIT(8) columns. No matter
> what format I use, the result is not what I expect (see example below.) 
> Anyone know how to properly format the data for loading into a BIT column? 
> Thanks!
>
>
> $ cat /tmp/bit_test.txt
> 01010101
> 2
> b'010'
> b\'010\'
> 0x2
> 02
>
>
> mysql> create table bit_test (b bit(8));
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> load data infile '/tmp/bit_test.txt' into table bit_test;
> Query OK, 6 rows affected, 3 warnings (0.00 sec)
> Records: 6  Deleted: 0  Skipped: 0  Warnings: 3
>
> mysql> select bin(b+0) from bit_test;
> +--+
> | bin(b+0) |
> +--+
> |  |
> | 110010   |
> |  |
> |  |
> |  |
> |  |
> +--+
> 6 rows in set (0.00 sec)
>
>
>
> Thanks!
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Encrypted column example

[sheeri kritzer]

Hi Chris,

I remember that article, I think I saw it on PlanetMySQL.com -- but I
can't find it either.  I do remember that it encrypted the column
putting it into the database, and used a VIEW to decode it, and then
limited the access to the view.

Hope that helps!

-Sheeri

On 2/23/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> When 5.0 came out, I saw an article or white paper or something with an
> example of a stored procedure (I think, maybe a trigger) that allowed
> you to transparently encrypt a column so that even if a database backup
> was stolen, that column wouldn't be exposed to the thieves.  I believe
> that the example was for a SSN or credit card number.  Now that I want
> it, I can't find it again.  I've searched MySQL.com without success.
> Does anyone else remember it?  Can someone point me to it?  Thanks.
>
>Chris
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: bind-address by name under 5.0.18

[sheeri kritzer]

It doesn't allow to bind to a hostname.  According to

http://dev.mysql.com/doc/refman/5.0/en/server-options.html

you have to specify an IP address, not a hostname.

-Sheeri

On 2/22/06, James Long <[EMAIL PROTECTED]> wrote:
> I would like to be able to bind to a host name, rather than
> an IP number.  IP numbers come and go, and are beyond the
> control of anyone who doesn't have their own direct allocation.
> But since I own my domain, a host name is more permanent.
>
> When the time comes to change IP numbers, I want to just edit
> /etc/hosts to show my new IP:
>
> 10.0.0.10   shortname myrealname.example.com
>
> and use "--bind-address=shortname" in my start-up script to
> automatically pick up the new IP number.
>
> This eliminates the headache of having to find and fix umpteen
> literal IP numbers hiding in my startup scripts.  This is much
> the same reason one says '#define BLKSIZ 512' and then uses the
> constant 'BLKSIZ' instead of sprinkling literal '512's throughout
> one's code).
>
> Why doesn't bind-address allow this?  Or if it does, how can I
> accomplish this?
>
>
> Thanks!
>
>
> Jim
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Connecting to queries into one

[Peter Brawley]

Courtney,

>SELECT consumers.id FROM consumers
>WHERE date_of_birth = ? AND consumer.id =
>(SELECT consumer_id FROM cases WHERE last_name = ?
>AND full_first_name = ? )


>Is there a way to form the above statement in MYSQL?

Something like this would be faster ...

SELECT co.id
FROM cases AS ca
INNER JOIN consumers AS co USING (consumer_id)
WHERE co.date_of_birth = 
 AND ca.last_name = 
 AND ca.first_name = ;

PB


Courtney Braafhart wrote:

MY GOAL
   
To collect any consumers.id WHERE date of birth, last name and 
first name matches  what was entered by the user.  The trick is that 
date of birth lives in the consumer table and last name and 
first name lives in the cases table (which can be joined to consumers 
by consumers.id and cases.consumer_id).


Can anyone think of way to do this in one mysql statement 
instead of doing a loop?


I am thinking it would work something like this:

SELECT consumers.id FROM consumers WHERE date_of_birth = ? AND 
consumer.id = (SELECT consumer_id FROM cases WHERE last_name = 
? AND full_first_name = ? )


Is there a way to form the above statement in MYSQL?

MY HOPE

That there is a really obvious solution to this question and 
that I am simply suffering from a case of the Mondays!



Thanks in advance!

Courtney Braafhart


--MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]




--No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006





--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Newbie help: extracting pattern based data from fields

[Ravi Malghan]

Hi: My database is mysql 4.1.14-standard. I have a
blob type field in a table1 with mutlple attributes
together in the field as shown below
table1.field1 value for a record is "abc=2 def=4
ghi=10"

I have table2 which has fields abc, def and ghi. I
want to be able to parse the table1.field1 and insert
values 2 for table2.abc, 4 for table2.def and 10 for
table2.ghi.

1. Is stored procedure the only way to do this?
2. 4.x does not support stored procedures. If this is
correct, is there any other way to accomplish this?
3. how do I extract values from a field based on a
pattern? Just looking for some pointers to get started
on this.

Thanks
Ravi

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MYSQL: Unlimited Forums, Scalablity Issues, advice please?

[jay]

Hi there,
I am in the midst of creating a forums hosting site.

1. It has to be highly scalable, so I doubt a single MYSQL db with TONS 
of "subforums that simulate full forums" would do.


2. Also regarding scalablity, I hope to "Add capacity" as and when its 
needed. So i'll have one server running initially, and when it gets too 
crowded, i'll get two servers etc.


3. I will be providing a user with a "dashboard" that allows him to view 
all his subscribed posts across ALL forums. So lets say a user is a 
member of 25 forums, this dashboard view will allow the user to view all 
his posts across all the forums.


Does anyone have advice that could point me in the right direction?
I have solved the scalability issue WITHIN a forum (code can handle 
million + posts easy), but I havent solved the issue of scaling MULTIPLE 
separate forums.


I dont think putting EVERYTHING on one single DB would be a good idea, 
considering that a single forum could grow huge, and have a million posts.
I am thinking that the solution would possibily involve like a fixed 
amount of forums per db/server, and then drop in a new server everytime 
that limit is reached.


Regarding point 3, where a user has a "dashboard" that allows him to 
view all subscribed posts across ALL forums, an obvious solution would 
be to have a user-centric table that also stores copies of all his 
subscribed post IDs (including last viewed time stamp etc), eliminating 
the need to query across all separate DBs.


Am I heading in the right direction?

Also, does there exist any php package that helps ease the process of 
"deciding which Server/database to connect to"?
For example, someone accesses FORUM A, so the script would automatically 
know to direct all queries to the DB in SERVER 1 etc, and if i try to 
access FORUM J, it would connect to SERVER 2 etc. I could easily hard 
code this, but I was thinking "what if internal IP addresses change, or 
I decide to migrate a busy forum to a server of its own etc", so perhaps 
there is a better available packaged solution designed for this task.


Appreciate any advice!

Sprock

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Connecting to queries into one

[John McCaskey]

SELECT consumers.id FROM consumers, cases 
WHERE 
consumers.id=cases.consumers_id 
AND consumers.date_of_birth = ? 
AND cases.last_name = ?
AND cases.first_name = ?


John A. McCaskey

-Original Message-
From: Courtney Braafhart [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 27, 2006 10:31 AM
To: mysql@lists.mysql.com
Subject: Connecting to queries into one

MY GOAL

To collect any consumers.id WHERE date of birth, last
name and  
first name matches  what was entered by the user.  The trick is that  
date of birth lives in  the consumer table and last name and
first  
name lives in the cases table (which can be joined to consumers by  
consumers.id and cases.consumer_id).

Can anyone think of way to do this in one mysql
statement instead  
of doing a loop?

I am thinking it would work something like this:

SELECT consumers.id FROM consumers WHERE date_of_birth =
? AND  
consumer.id = (SELECT consumer_id FROM cases WHERE last_name = ?

AND full_first_name = ? )

Is there a way to form the above statement in MYSQL?

MY HOPE

That there is a really obvious solution to this question
and that I  
am simply suffering from a case of the Mondays!


Thanks in advance!

Courtney Braafhart


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Connecting to queries into one

[Taiyo]

select consumers.id from consumers LEFT JOIN cases ON consumers.id =
cases.id WHERE consumers.date_of_birth = '?' AND cases.last_name = '?' AND
cases.full_first_name = '?'

-Original Message-
From: Courtney Braafhart [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 27, 2006 1:31 PM
To: mysql@lists.mysql.com
Subject: Connecting to queries into one

MY GOAL

To collect any consumers.id WHERE date of birth, last name
and  
first name matches  what was entered by the user.  The trick is that  
date of birth lives in  the consumer table and last name and first  
name lives in the cases table (which can be joined to consumers by  
consumers.id and cases.consumer_id).

Can anyone think of way to do this in one mysql statement
instead  
of doing a loop?

I am thinking it would work something like this:

SELECT consumers.id FROM consumers WHERE date_of_birth = ?
AND  
consumer.id = (SELECT consumer_id FROM cases WHERE last_name = ?

AND full_first_name = ? )

Is there a way to form the above statement in MYSQL?

MY HOPE

That there is a really obvious solution to this question and
that I  
am simply suffering from a case of the Mondays!


Thanks in advance!

Courtney Braafhart


-- 
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]

Connecting to queries into one

[phpninja]

Why do some many people that work in the U.S government post to this mysql
list? Do they really hire people with no experience ? I fear for our
homeland..

-Original Message-

From: Courtney Braafhart
[*mailto:[EMAIL PROTECTED]<[EMAIL PROTECTED]>]


Sent: Monday, February 27, 2006 10:31 AM

To: mysql@lists.mysql.com

Subject: Connecting to queries into one

MY GOAL

To collect any consumers.id WHERE date of birth, last name and first name
matches what was entered by the user. The trick is that

date of birth lives in the consumer table and last name and first

name lives in the cases table (which can be joined to consumers by
consumers.id and cases.consumer_id).

Can anyone think of way to do this in one mysql statement instead of doing a
loop?

I am thinking it would work something like this:

SELECT consumers.id FROM consumers WHERE date_of_birth = ? AND

consumer.id = (SELECT consumer_id FROM cases WHERE last_name = ?

AND full_first_name = ? )

Is there a way to form the above statement in MYSQL?

MY HOPE

That there is a really obvious solution to this question and that I am
simply suffering from a case of the Mondays!



Thanks in advance!

Courtney Braafhart



--

MySQL General Mailing List

For list archives: *http://lists.mysql.com/mysql*

To unsubscribe: *http://lists.mysql.com/[EMAIL PROTECTED]

Connecting to queries into one

[Courtney Braafhart]

MY GOAL

		To collect any consumers.id WHERE date of birth, last name and  
first name matches  what was entered by the user.  The trick is that  
date of birth lives in 		the consumer table and last name and first  
name lives in the cases table (which can be joined to consumers by  
consumers.id and cases.consumer_id).


		Can anyone think of way to do this in one mysql statement instead  
of doing a loop?


I am thinking it would work something like this:

		SELECT consumers.id FROM consumers WHERE date_of_birth = ? AND  
consumer.id = (SELECT consumer_id FROM cases WHERE last_name = ? 			 
AND full_first_name = ? )


Is there a way to form the above statement in MYSQL?

MY HOPE

		That there is a really obvious solution to this question and that I  
am simply suffering from a case of the Mondays!



Thanks in advance!

Courtney Braafhart


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mySQL 5 and CPu at 99.99%

[Taiyo]

Greetings,

We are running a server and the CPU is at %99.99 at all times, after about
2-3 hours of processing queries just hang, sounds like our hardware is weak
but we are running a 4GB RAM Dual Xeons 3.4 at 64bit OS.

I was hoping someone could look at our settings and would help us analyze
this issue:

Please advise.

Here are the stats:

Some version information:

mySQL version: 5.0.16-standard
Uname: Linux db.example.com
2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:00:54 EDT 2005 x86_64 x86_64 x86_64
GNU/Linux
RAM:4GB
SWAP  1GB
HD:   2 SCSI 10k RPM on 2 separate
controllers.

Some information about the load:
Queries per second avg: 16.346 (about)
Our biggest table is 3.5 million records and we index 3 of the columns for
fulltext search
We do a lot of join queries on 2 tables.

Some mySQL variables:
[mysqld]
tmpdir=/db.example.com/tmp
query_cache_size=1048576
query_cache_limit = 33554432
query_cache_size = 33554432
myisam_sort_buffer_size = 33554432
sort_buffer_size = 33554432
max_connections=500
table_cache = 1000
max_tmp_tables = 256
 
Here is all of my mysql -e 'SHOW VARIABLES':
+-+-
-+
| Variable_name   | Value
|
+-+-
-+
| auto_increment_increment| 1
|
| auto_increment_offset   | 1
|
| automatic_sp_privileges | ON
|
| back_log| 50
|
| basedir | /
|
| binlog_cache_size   | 32768
|
| bulk_insert_buffer_size | 8388608
|
| character_set_client| latin1
|
| character_set_connection| latin1
|
| character_set_database  | latin1
|
| character_set_results   | latin1
|
| character_set_server| latin1
|
| character_set_system| utf8
|
| character_sets_dir  | /usr/share/mysql/charsets/
|
| collation_connection| latin1_swedish_ci
|
| collation_database  | latin1_swedish_ci
|
| collation_server| latin1_swedish_ci
|
| completion_type | 0
|
| concurrent_insert   | 1
|
| connect_timeout | 5
|
| datadir | /var/lib/mysql/
|
| date_format | %Y-%m-%d
|
| datetime_format | %Y-%m-%d %H:%i:%s
|
| default_week_format | 0
|
| delay_key_write | ON
|
| delayed_insert_limit| 100
|
| delayed_insert_timeout  | 300
|
| delayed_queue_size  | 1000
|
| div_precision_increment | 4
|
| engine_condition_pushdown   | OFF
|
| expire_logs_days| 0
|
| flush   | OFF
|
| flush_time  | 0
|
| ft_boolean_syntax   | + -><()~*:""&|
|
| ft_max_word_len | 84
|
| ft_min_word_len | 2
|
| ft_query_expansion_limit| 20
|
| ft_stopword_file| (built-in)
|
| group_concat_max_len| 1024
|
| have_archive| YES
|
| have_bdb| NO
|
| have_blackhole_engine   | NO
|
| have_compress   | YES
|
| have_crypt  | YES
|
| have_csv| NO
|
| have_example_engine | NO
|
| have_federated_engine   | NO
|
| have_geometry   | YES
|
| have_innodb | YES
|
| have_isam   | NO
|
| have_ndbcluster | NO
|
| have_openssl| NO
|
| have_query_cache| YES
|
| have_raid   | NO
|
| have_rtree_keys | YES
|
| have_symlink| YES
|
| init_connect|
|
| init_file   |
|
| init_slave  |
|
| innodb_additional_mem_pool_size | 1048576
|
| innodb_autoextend_increment | 8
|
| innodb_buffer_pool_awe_mem_mb   | 0
|
| innodb_buffer_pool_size | 8388608
|
| innodb_checksums| ON
|
| innodb_commit_concurrency   | 0
|
| innodb_concurrency_tickets  | 500
|
| innodb_data_file_path   | ibdata1:10M:autoextend
|
| innodb_data_home_dir|
|
| innodb_doublewrite  | ON
|
| innodb_fast_shutdown| 1
|
| innodb_file_io_threads  | 4
|
| innodb_file_per_table   | OFF
|
| innodb_flush_log_at_trx_commit  | 1
|
| innodb_flush_method |
|
| innodb_force_recovery   | 0
|
| innodb_lock_wait_timeout| 50
|
| innodb_locks_unsafe_for_binlog  | OFF
|
| innodb_log_arch_dir |
|
| innodb_log_archive  | OFF
|
| innodb_log_buffer_size  | 1048576
|
| innodb_log_file_size| 5242880
|
| innodb_log_f

(Solved) Re: Unbelieveable with 4 Gbytes - MySQL 5 only 350 concurrentmysqlconnection... on Linux Redhat 9

[Ady Wicaksono]

Thanks.


Since i use Innodb, i decrease the value of all variables affected 
myisam table


Thx

[EMAIL PROTECTED] wrote:


Lowering your sort_buffer and read_buffer to 512K should help (there are
allocated for each connection, and (12+12)*910 is a lot more than what
your system can handle).
And if you really want to take advantage of your 4GB with MySQL, switch to
a 64 bits system :)

 Jocelyn


 


Yes it's 32 bit System (Double XEON - Intel(R) Xeon(TM) CPU 3.00GHz)

Any solutions for this Joce?


[EMAIL PROTECTED] wrote:

   


Hi,

If it's a 32 bits machine, I don't think you can adress more than 2 GB
for
1 MySQL process.

Jocelyn



 


Make sense,

Kayra,  this is my.cnf for my mysql
cut here ---
[client]
port= 3306
socket  = /var/lib/mysql/mysql.sock


# The MySQL server
[mysqld]
#thread_stack=128KB
#set-variable = table-type=innodb
default-table-type = innodb
port   = 3306
socket = /var/lib/mysql/mysql.sock
max_connections= 910
max_connect_errors = 999
skip-locking
skip-innodb_doublewrite
key_buffer = 384M
max_allowed_packet = 4M
table_cache= 2049
sort_buffer_size   = 12M
read_buffer_size   = 12M
myisam_sort_buffer_size = 64M
thread_cache   = 48
query_cache_size   = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

# Master Replication
# --
#log-bin
#server-id=1

# My Inno DB tables
# -
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir =
innodb_data_file_path =
/data1/ibdata1:2000M;/data1/ibdata2:2000M;/data1/ibdata3:2000M;/data1/ibdata4:2000M;/data2/ibdata5:2000M;/data2/ibdata6:2000M;/data2/ibdata7:2000M;/data2/ibdata8:2000M;/data3/ibdata9:2000M;/data3/ibdata10:2000M;/data3/ibdata11:2000M;/data3/ibdata12:2000M;/data4/ibdata13:2000M;/data4/ibdata14:2000M;/data4/ibdata15:2000M;/data4/ibdata16:2000M;/data5/ibdata17:2000M;/data5/ibdata18:2000M;/data5/ibdata19:2000M;/data5/ibdata20:2000M;

# -- OLD ---
innodb_log_group_home_dir = /data1
innodb_log_arch_dir = /data1
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1300M
innodb_additional_mem_pool_size = 80M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 20
innodb_table_locks=0
transaction-isolation = REPEATABLE-READ
cut here ---

File open limit:
# cat /proc/sys/fs/file-max
512000

Kernel usage:
# uname -a
Linux db 2.4.20-8bigmem #1 SMP Thu Mar 13 17:32:29 EST 2003 i686 i686
i386 GNU/Linux

Redhat Release :
# cat /etc/redhat-release
Red Hat Linux release 9 (Shrike)

However the bad news is when i try c program
(http://forums.mysql.com/read.php?21,71414,71667#msg-71667)

#include 
#include 

int main ( void )
{
size_t siz = 100 * 1024 * 1024 ;
size_t idx = 1 ;
void *ptr ;

for (;;)
{
ptr = malloc ( siz * idx );
if ( ! ptr )
break ;
free ( ptr );
idx ++ ;
}
printf ( "Max malloc %d * 100 MB \n", idx - 1 );
return ( 0 );
}

It return only 20*100Mbyte for MySQL user ~ 2 Gbytes

Please give me clue on this issue

Thx



Kayra Otaner wrote:



   


It can be both Linux kernel setting and MySQL configuration issue.

It can be a kernel issue since:
- Although you have 4G on your machine your kernel may set to let only
2G to a user space process. You may need to upgrade your kernel to a
'hugemem' kernel which gives you either 3/1 or 4/4 (you need 8G mem for
this) memory split
- Number of open files (directly co related to number of open tables in
MySQL) setting in kernel can be lower than what your MySQL instance
needs.

It can be a MySQL configuration issue since :
- You may have incorrect settings in your my.cnf config file. Say if
you
assigned 2G to innodb buffer and other buffers and your kernel lets you
to go upto 2G for user space process. For each connection coming MySQL
dedicates at least 128K (thread size) memory, so for 350 connection,
you
end up using +43M memory which makes MySQL's total mem usage 2G + 43M.
This may be the reason why you're seeing can't create new thread.


Kayra Otaner

On Fri, 2006-02-24 at 20:51 +0700, Ady Wicaksono wrote:




 


I have MySQL 5 installed on My Machine,

But it could handle only max about 350 concurrent mysql connection

Unbelieveable, i have 4 Gbytes, but on the next 351 connection i
always
got Can't create a new thread (errno 12); if you are not out of
available memory, you can consult the manual for
a possible OS-dependent bug in

Is it mysql bug, incorrect Linux setting or what?










   





 


--
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

Multi Master Replication

[Marvin Wright]

Hi,

I'm having a few problems at the moment with replication.
We are in the process of migrating a cache to 2 new database machines.

As the data is a cache its very large is refreshed constantly.

Currently we have 2 machines, 1 master and 1 slave.

I am trying to set up this configuration but I just can not get it to
work.

Master  -->  Slave1  -->  Slave2  -->  Slave3

Master has id 1 and bin-log enabled

Slave1 has id 2 and bin-log enabled but also a do-bin-log=Cache

Slave2 has id 3 and bin-log enabled

Slave3 has id 4

The process is working between Master, Slave1 and Slave2 but for some
reason Slave2 is not writing bin-log information.
So Slave3 is sitting empty and not getting any data because of no data
in Slave2's bin-log.

The idea is to get this process working correctly, then the
Slave2->Slave1 link broken and Slave2 will become the new Master.

Any idea why this could be a problem ?

All mysql versions are 4.1.12 and running on linux.

Many Thanks

Marvin Wright
Flights Developer
Lastminute.com
[EMAIL PROTECTED]




**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**

create/restore database without binary logging

[Rithish Saralaya]

Hello.

I was going to recreate a database of size 35 GB from sql dump file. Wanted
to know if it is possible to do it without mysql writing into the binary
log. If yes, how?

Regards,
Rithish.

Re: MySQL query & gifted book !!!

[Gabriel PREDA]

If you want to find out the content of the CD you should buy the book !
I'm sure it's illegal to make a copy of the CD available online... and also
to have a copy without having the book !

With the respect of the content let me tell you that it contains the book
itself in PDF and some PDFs with exercises that cover the entire book !
THEY'RE GREAT EXERCISES !!!
And I'm sure I'll get the exam ! I'm just waiting for the exam to get out of
BETA !

You can take a peak at MySQL website
www.mysql.com/training/certification/studyguides/sample-chapter50.pdf .

As about how relevant is the book... consider that... I found a question on
MySQL website about a VARCHAR(15) column that was created with UTF8
charset... and the question was "What is the max nr. of bytes this column
will use"
The first reaction is to say 16 (L+1) but the correct answer is 46 (3*L+1)
because UTF uses at most 3 bytes per character...
Maybe is not much... but this made me want the book...

ALMOST OFF TOPIC:
I myself am worried because I did not bought the book it was send to me as a
gift (almost gift... let's say it was a favour... he sent me the book.. I
did domething for him !)...
The fact is that I don't have the bill... does this make me in a less legal
position... will I still get 25% off at the exam ? Should I ask for some
kind of a "deposition" from my friend in USA !???

--
Gabriel PREDA
Senior Web Developer


On 2/27/06, Anago Chima <[EMAIL PROTECTED]> wrote:
>
> Please does anybody knows the content of the CD that
> comes with MySQL 5 Certification Study Guide and how
> relevant it's to passing the exams? Can someone out
> there  provide me with a link to his copy for download?
>

Re: error 1016 : cant open ibd file even though it exists

[Heikki Tuuri]

Rithish,

- Original Message - 
From: ""Rithish Saralaya"" <[EMAIL PROTECTED]>

Newsgroups: mailing.database.myodbc
Sent: Monday, February 27, 2006 7:06 AM
Subject: RE: error 1016 : cant open ibd file even though it exists



Heikki.

I found the ibdata1 and ib_log files located in only '/var/lib/mysql' 
which

is defined as the mysql home folder.

The last time my.cnf was editied was on the 9th of Dec, and the database 
was

created somewhere in the month of January. So the database would have been
created with the same configurations.

Thankfully, I have a dump of the original database. Looks like I have to
restore all of the 35 GB of data again.


then this remains a mystery. InnoDB never deletes ibdata files or 
ib_logfiles by itself.



Regards,
Rithish.



Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables

http://www.innodb.com/order.php



-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
Sent: Saturday, February 25, 2006 4:05 PM
To: mysql@lists.mysql.com
Subject: Re: error 1016 : cant open ibd file even though it exists


Rithish,

ok, then the most probable explanation is that someone had edited my.cnf
earlier, when mysqld was running. When mysqld was restarted, it read the 
new

my.cnf, and got confused.

Please ask your sysadmins to scan the file system of the computer if they
can find another ibdata1 file or ib_logfiles.

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up 
MyISAM

tables
http://www.innodb.com/order.php


- Original Message -
From: ""Rithish Saralaya"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Saturday, February 25, 2006 12:14 PM
Subject: RE: error 1016 : cant open ibd file even though it exists



--=_NextPart_000_001D_01C63A22.BB0C91A0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit

Hello David.

There was supposed to be a power outage in our office that day. So the
server was shut down. Finally when the power was back, the machine was
plugged on. That's all. No file system change. Nothing.

Regards,
Rithish.
 -Original Message-
 From: David Logan [mailto:[EMAIL PROTECTED]
 Sent: Saturday, February 25, 2006 3:32 PM
 To: Rithish Saralaya
 Cc: mysql@lists.mysql.com
 Subject: Re: error 1016 : cant open ibd file even though it exists


 Hi Rithish,

 After reading Heikkis points, I am inclined to agree. Did your sysadmins
change a filesystem during the maintenance? Did they forget to restore a
directory if they changed disks? What was the maintenance that was
performed? Your InnoDB files disappeared at some point because the server
would not have recreated them otherwise. I am sure it was a graceful
shutdown, but something has changed. These things just don't happen by
themselves.

 Regards

 Rithish Saralaya wrote:
The mysql server was shut down for maintenance. However it was a graceful
shutdown and restart. That's all. No files were touched or removed. How
could this have happened, I fail to see.

Regards,
Rithish.


-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
Sent: Friday, February 24, 2006 3:55 PM
To: mysql@lists.mysql.com
Subject: Re: error 1016 : cant open ibd file even though it exists


Rithish,

from the .err log we see that mysqld was shut down for 12 hours on Feb
19th.
What did the sysadmins do during that time?

There are two plausible explanations:

1) they edited datadir in my.cnf to point to a different location (
/var/lib/mysql),

or

2) they removed ibdata1 and ib_logfiles from the the datadir.

That caused InnoDB to recreate these files.

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM
tables
http://www.innodb.com/order.php

- Original Message -
From: ""Rithish Saralaya"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, February 24, 2006 6:55 AM
Subject: RE: error 1016 : cant open ibd file even though it exists


 Hello.

The tables were working perfectly fine a week back. The database was
created
from a sql file generated through the mysqldump utility. So there was
nothing wrong with the database. This irregularity happened this week
onwards.

Our system admins tell us that the server was restarted last weekend. 
When

I
dug up the mysql error logs, this was what I found saw.

==
060219  5:20:25  InnoDB: Starting shutdown...
060219  5:20:30  InnoDB: Shutdown completed; log sequence number 19
1867461149
060219  5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete


Memory status:
Non-mmapped space allocated from system: 16589028
Number of free chun

Re: Why do these transactions show table locks?

[Heikki Tuuri]

Robert,

- Original Message - 
From: ""Robert DiFalco"" <[EMAIL PROTECTED]>

Newsgroups: mailing.database.myodbc
Sent: Sunday, February 26, 2006 8:27 PM
Subject: RE: Why do these transactions show table locks?



It might be important to note that I have a delete trigger on the ELEMS
table, also, this INSERT call is being made from a stored procedure. The
stored procedure only has one line, this INSERT statement. Could this
have anything to do with bug# 16229?

http://bugs.mysql.com/bug.php?id=3D16229=20


yes, I think this is:

http://bugs.mysql.com/bug.php?id=16229

which is fixed in 5.0.19. I just tested that an ordinary INSERT in 5.0.18 
does not use full explicit table locks in InnoDB.


Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables

http://www.innodb.com/order.php


-Original Message-
From: Robert DiFalco [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 26, 2006 9:33 AM
To: mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: Why do these transactions show table locks?

My understanding is that innodb should not be using table locks for
insert, update, or delete. However, the following transactions are
showing table locks. What's up?

R.

---TRANSACTION 0 4573, ACTIVE 1 sec, OS thread id 3112 setting table
lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap
size 320, undo log entries 250 MySQL thread id 4, query id 566875
squid.tripwire.com 10.150.1.30 root System lock INSERT INTO
ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_
LCSEV)
VALUES(-9223372036854523873,-9223372036854775299,-9223372036854775181,-9
223372036854744697,1,-1,-9223372036854027123,-9223372036854027123,1,0,0)
--- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `space/elems` trx id 0 4573 lock mode X waiting
--
---TRANSACTION 0 4569, ACTIVE 1 sec, OS thread id 1708 setting table
lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap
size 320, undo log entries 250 MySQL thread id 6, query id 565737
squid.tripwire.com 10.150.1.30 root System lock INSERT INTO
ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_
LCSEV)
VALUES(-9223372036854557373,-9223372036854775352,-9223372036854775181,-9
223372036854601648,1,-1,-9223372036854027623,-9223372036854027623,1,0,0)
--- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `space/elems` trx id 0 4569 lock mode X waiting
--
---TRANSACTION 0 4565, ACTIVE 2 sec, OS thread id 4008 setting table
lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap
size 320, undo log entries 250 MySQL thread id 5, query id 564870
squid.tripwire.com 10.150.1.30 root System lock INSERT INTO
ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_
LCSEV)
VALUES(-9223372036854578873,-9223372036854775355,-9223372036854775181,-9
223372036854744697,1,-1,-9223372036854033123,-9223372036854033123,1,0,0)
--- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `space/elems` trx id 0 4565 lock mode X waiting
--
---TRANSACTION 0 4561, ACTIVE 2 sec, OS thread id 3148 setting table
lock mysql tables in use 1, locked 0 LOCK WAIT 2 lock struct(s), heap
size 320, undo log entries 250 MySQL thread id 7, query id 563809
squid.tripwire.com 10.150.1.30 root System lock INSERT INTO
ELEMS(oid,E_NID,E_RID,E_NMID,E_INSC,E_ET,E_BLID,E_LCID,E_LCNO,E_LCTYP,E_
LCSEV)
VALUES(-9223372036854557623,-9223372036854775352,-9223372036854775181,-9
223372036854757305,1,-1,-9223372036854033623,-9223372036854033623,1,0,0)
--- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `space/elems` trx id 0 4561 lock mode X waiting
--


--
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]