select contiguous addresses that start on a bit boundary
I need help creating a select that returns 4 records that have contiguous addresses that start on a bit boundary. If 4 do not exist, I need a return of zero records. I would like to do this in one statement and I do not have ownership of this mysql server, so fancy views, temporary tables, indexing, etc are outside my permission level. I am also not the only consumer of this database, so altering it for my needs could hurt the other consumers. Below I specify the issue and where I am. Thank you for your attention. # # Create problem set # - This has non-contiguous addresses # - This has one status not 0 # - This has contiguous addresses that start before the bit boundary # CREATE TABLE addresses ( address BIGINT(20), status INT ); INSERT INTO addresses VALUES (1001,0), (1003,0), (1004,0), (1005,1), (1006,0), (1007,0), (1008,0), (1009,0), (1010,0), (1011,0), (1013,0), (1014,0), (1015,0), (1016,0), (1017,0); # # This shows the bit boundary, where the start is (address & 3) = 0 # select address, (address & 3) as boundary from addresses where address >0 and status=0 order by address limit 10 ; +--+--+ | address | boundary | +--+--+ | 1001 |1 | | 1003 |3 | | 1004 |0 | | 1006 |2 | | 1007 |3 | | 1008 |0 | | 1009 |1 | | 1010 |2 | | 1011 |3 | | 1013 |1 | +--+--+ 10 rows in set (0.00 sec) # # This shows contiguous add, but they do not stat on the bit boundary # select c1.address, (address & 3) as boundary from addresses c1 where 4 = ( SELECT count(*) FROM addresses c2 WHERE c2.status = 0 and c2.address BETWEEN c1.address AND (c1.address + 3) ) limit 10; +--+--+ | address | boundary | +--+--+ | 1006 |2 | | 1007 |3 | | 1008 |0 | | 1013 |1 | | 1014 |2 | +--+--+ 5 rows in set (0.00 sec) I can't seem to add my ((address & 3) = 0) condition to the correct location to get the desired result. I don't understand how I can use c1.address in the BETWEEN, and yet I can't seem to make ((address & 3) = 0) work anywhere. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: select contiguous addresses that start on a bit boundary
I should have said consecutive addresses, rather than contiguous. I care about a set of consecutive addresses, and there is no guarantee of record order. On 12/17/2015 07:35 AM, John Stile wrote: > I need help creating a select that returns 4 records that have > contiguous addresses that start on a bit boundary. > > If 4 do not exist, I need a return of zero records. > > I would like to do this in one statement and I do not have ownership of > this mysql server, so fancy views, temporary tables, indexing, etc are > outside my permission level. > > I am also not the only consumer of this database, so altering it for my > needs could hurt the other consumers. > > Below I specify the issue and where I am. > > Thank you for your attention. > > # > # Create problem set > # - This has non-contiguous addresses > # - This has one status not 0 > # - This has contiguous addresses that start before the bit boundary > # > CREATE TABLE addresses ( address BIGINT(20), status INT ); > INSERT INTO addresses > VALUES (1001,0), >(1003,0), >(1004,0), >(1005,1), >(1006,0), >(1007,0), >(1008,0), >(1009,0), >(1010,0), >(1011,0), >(1013,0), >(1014,0), >(1015,0), >(1016,0), >(1017,0); > # > # This shows the bit boundary, where the start is (address & 3) = 0 > # > select address, (address & 3) as boundary from addresses where address >> 0 and status=0 order by address limit 10 ; > +--+--+ > | address | boundary | > +--+--+ > | 1001 |1 | > | 1003 |3 | > | 1004 |0 | > | 1006 |2 | > | 1007 |3 | > | 1008 |0 | > | 1009 |1 | > | 1010 |2 | > | 1011 |3 | > | 1013 |1 | > +--+--+ > 10 rows in set (0.00 sec) > # > # This shows contiguous add, but they do not stat on the bit boundary > # > select c1.address, (address & 3) as boundary from addresses c1 where 4 > = ( SELECT count(*) FROM addresses c2 WHERE c2.status = 0 and c2.address > BETWEEN c1.address AND (c1.address + 3) ) limit 10; > > +--+--+ > | address | boundary | > +--+--+ > | 1006 |2 | > | 1007 |3 | > | 1008 |0 | > | 1013 |1 | > | 1014 |2 | > +--+--+ > 5 rows in set (0.00 sec) > > > > I can't seem to add my ((address & 3) = 0) condition to the correct location > to get the desired > result. I don't understand how I can use c1.address in the BETWEEN, and > yet I can't seem to make ((address & 3) = 0) work anywhere. > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: select contiguous addresses that start on a bit boundary
I have a solution. SELECT start_bit_boundary FROM ( SELECT min(address) as start_bit_boundary, status, count(*) as CT FROM MAC_addresses WHERE status = 0 GROUP BY address >> 2 ) AS _INNER WHERE _INNER.CT = 4 ORDER BY start_bit_boundary LIMIT 0,1; It returns the first of 4 consecutive addresses. This works with the following data set. CREATE TABLE addresses ( address BIGINT(20), status INT ); INSERT INTO addresses VALUES (1001,0), (1003,0), (1004,0), (1005,1), (1006,0), (1007,0), (1009,0), (1010,0), (1011,0), (1013,0), (1008,0), (1014,0), (1015,0), (1016,0), (1017,0); If I want to print all the addresses I could do this: select * from addresses where status = 0 AND address BETWEEN ( SELECT @b := start_bit_boundary FROM ( SELECT min(address) as start_bit_boundary,status,count(*) as CT FROM MAC_addresses WHERE status = 0 GROUP BY address >> 2 ) AS _INNER WHERE _INNER.CT = 4 ORDER BY start_bit_boundary LIMIT 0,1 ) AND (@b+3) limit 0,4; On 12/17/2015 08:14 AM, John Stile wrote: > I should have said consecutive addresses, rather than contiguous. > I care about a set of consecutive addresses, and there is no guarantee > of record order. > > On 12/17/2015 07:35 AM, John Stile wrote: >> I need help creating a select that returns 4 records that have >> contiguous addresses that start on a bit boundary. >> >> If 4 do not exist, I need a return of zero records. >> >> I would like to do this in one statement and I do not have ownership of >> this mysql server, so fancy views, temporary tables, indexing, etc are >> outside my permission level. >> >> I am also not the only consumer of this database, so altering it for my >> needs could hurt the other consumers. >> >> Below I specify the issue and where I am. >> >> Thank you for your attention. >> >> # >> # Create problem set >> # - This has non-contiguous addresses >> # - This has one status not 0 >> # - This has contiguous addresses that start before the bit boundary >> # >> CREATE TABLE addresses ( address BIGINT(20), status INT ); >> INSERT INTO addresses >> VALUES (1001,0), >>(1003,0), >>(1004,0), >>(1005,1), >>(1006,0), >>(1007,0), >>(1008,0), >>(1009,0), >>(1010,0), >>(1011,0), >>(1013,0), >>(1014,0), >>(1015,0), >>(1016,0), >>(1017,0); >> # >> # This shows the bit boundary, where the start is (address & 3) = 0 >> # >> select address, (address & 3) as boundary from addresses where address >>> 0 and status=0 order by address limit 10 ; >> +--+--+ >> | address | boundary | >> +--+--+ >> | 1001 |1 | >> | 1003 |3 | >> | 1004 |0 | >> | 1006 |2 | >> | 1007 |3 | >> | 1008 |0 | >> | 1009 |1 | >> | 1010 |2 | >> | 1011 |3 | >> | 1013 |1 | >> +--+--+ >> 10 rows in set (0.00 sec) >> # >> # This shows contiguous add, but they do not stat on the bit boundary >> # >> select c1.address, (address & 3) as boundary from addresses c1 where 4 >> = ( SELECT count(*) FROM addresses c2 WHERE c2.status = 0 and c2.address >> BETWEEN c1.address AND (c1.address + 3) ) limit 10; >> >> +--+--+ >> | address | boundary | >> +--+--+ >> | 1006 |2 | >> | 1007 |3 | >> | 1008 |0 | >> | 1013 |1 | >> | 1014 |2 | >> +--+--+ >> 5 rows in set (0.00 sec) >> >> >> >> I can't seem to add my ((address & 3) = 0) condition to the correct location >> to get the desired >> result. I don't understand how I can use c1.address in the BETWEEN, and >> yet I can't seem to make ((address & 3) = 0) work anywhere. >> >> >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Altering column syntax help/and repare
I think I really misunderstood the directions for alter table, an I'm not sure how to fix this. I was trying to allow certain columns to be null in an existing table. I used the following syntax: ALTER TABLE users ALTER COLUMN title SET DEFAULT NULL; Now 'describe users;' shows extra columns with no titles. A. What did that syntax do? B. How do I undo it? C. What is the proper syntax? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld_safe starts, but mysqld(API) node not connected
/ [API] Id: 20 ExecuteOnComputer: 1 [API] Id: 21 ExecuteOnComputer: 2 - - /etc/my.cnf - [client] port= 3306 socket = /var/run/mysqld/mysqld.sock [mysqld] port= 3306 socket = /var/run/mysqld/mysqld.sock skip-locking key_buffer = 32M max_allowed_packet = 2M table_cache = 64 sort_buffer_size = 1M net_buffer_length = 16K read_buffer_size = 512K read_rnd_buffer_size = 1M myisam_sort_buffer_size = 16M datadir=/usr/local/mysql/data ndbcluster log-bin server-id = 2 tmpdir = /tmp/ innodb_data_home_dir = /usr/local/mysql/data innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/log innodb_log_arch_dir = /usr/local/mysql/log innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 35M sort_buffer_size = 25M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 35M sort_buffer_size = 25M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout - Start On postal1: --- shell cd /usr/local/mysql shell rm -rf data/ib* data/postal2-bin.* ndb/ndb_2* ndb/ndb1/ndb_2* ndb/mgmt/ndb_* shell export MYSQL_HOME=/usr/local/mysql shell export NDB_HOME=$MYSQL_HOME/ndb shell export PATH=$PATH:$MYSQL_HOME/bin shell export LD_LIBRARY_PATH=$MYSQL_HOME/lib shell export MANPATH=$MANPATH:$MYSQL_HOME/man shell pushd /usr/local/mysql/ndb/mgmt/ ndb_mgmd -c config.ini -l Ndb.cfg popd shell pushd /usr/local/mysql/ndb/ndb1/ ndbd --nodaemon --nostart --initial shell pushd /usr/local/mysql/ shell ./bin/mysqld_safe On postal2: --- shell cd /usr/local/mysql shell rm -rf data/ib* data/postal2-bin.* ndb/ndb_2* ndb/ndb1/ndb_2* ndb/mgmt/ndb_* shell export MYSQL_HOME=/usr/local/mysql shell export NDB_HOME=$MYSQL_HOME/ndb shell export PATH=$PATH:$MYSQL_HOME/bin shell export LD_LIBRARY_PATH=$MYSQL_HOME/lib shell export MANPATH=$MANPATH:$MYSQL_HOME/man shell pushd /usr/local/mysql/ndb/ndb1/ ndbd --nodaemon --nostart --initial shell pushd /usr/local/mysql/ shell ./bin/mysqld_safe Stop On postal1: --- shell cd /usr/local/mysql shell export MYSQL_HOME=/usr/local/mysql shell export NDB_HOME=$MYSQL_HOME/ndb shell export PATH=$PATH:$MYSQL_HOME/bin shell export LD_LIBRARY_PATH=$MYSQL_HOME/lib shell export MANPATH=$MANPATH:$MYSQL_HOME/man shell ndb_mgm NDB show Cluster Configuration - [ndbd(NDB)] 2 node(s) id=1@192.168.0.40 (Version: 3.5.2, Nodegroup: 0, Master) id=2@192.168.0.41 (Version: 3.5.2, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=63 @192.168.0.40 (Version: 3.5.2) [mysqld(API)] 2 node(s) id=20 @192.168.0.40 (Version: 3.5.2) id=21 @192.168.0.41 (Version: 3.5.2) NDB shutdown 2 NDB Cluster storage node(s) have shutdown. NDB Cluster management server shutdown. shell mysqladmin -u root -p shutdown On postal2: --- shell cd /usr/local/mysql shell export MYSQL_HOME=/usr/local/mysql shell export NDB_HOME=$MYSQL_HOME/ndb shell export PATH=$PATH:$MYSQL_HOME/bin shell export LD_LIBRARY_PATH=$MYSQL_HOME/lib shell mysqladmin -u root -p shutdown The next time I start mysqld_safe, it does not appear as a connected API. -- ._. | \0/John Stile | | UniX Administration | | / \ 510-305-3800 | | [EMAIL PROTECTED] | .-. signature.asc Description: This is a digitally signed message part
Help with ALTER TABLE error
I have Mysql Cluster (version 4.1.16-gama) running, and now I need to convert database tables from engine MyISAM to NDBCLUSTER, but ALTER TABLE fails on some tables. Does anyone know what the error means or how to get around it? mysql use database1; mysql alter table attr engine=NDB; ERROR 1005: Can't create table './database1/#sql-4627_3a.frm' (errno: 4009) -- ._. | \0/John Stile | | UniX Administration | | / \ 510-305-3800 | | [EMAIL PROTECTED] | .-. signature.asc Description: This is a digitally signed message part
Re: Help with ALTER TABLE error
On Tue, 2004-10-19 at 21:42, John Stile wrote: I have Mysql Cluster (version 4.1.16-gama) running, and now I need to Sorry, I meant version mysql-max-4.1.6-gamma-pc-linux-i686. signature.asc Description: This is a digitally signed message part
