[how to] Get all the database in db2 through web

---

• Get all the database in db2 through web
• Table design when one data is dependant of another and the second one has repeated values
• Hierarchical data best practices
• sysjobhistory "sql_severity" codes
• How to use and optimize subquery on 100 million rows
• Configuring MySQL (my.cnf) for MyISAM and InnoDB
• Can't Select a View on Informix
• MySQL - how can I make a select statement get blocked?
• Should we always prefer clustering over a master-slave configuration if the goal is high-availability?
• MySQL Slow query joining on subqueries with max
• SQL Server - Full text search migration 2005 -> 2012(.ndf file?)
• Merge Statement + Replication difference for SQL Server 2012
• If I fail over one database, do the others that share the same mirror endpoint fail over as well?
• What is the safest way to run SSIS on Multiple Named SQL Server Instances?
• How would I debug/test MongoDB stale connections?
• SQL Server Browser uses NT AUTHORITY\SYSTEM account and logon failure error in SQL Log
• DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS'
• Foreign key with multiple references
• mysqlslap chokes on strings that contain ";" delimiter?
• Writing with multiple psql processes to database seems to damage data
• SQL error "#1118 - row size too large" on the first row of the table only
• Hash in SQL query
• Oracle schema import is not importing all the tables present in the schema dump file
• SQL Server in a state of confusion: does the database exist or doesn't it?
• mysql better index?
• Increase in memory use when database server switches
• Queries getting stuck on very simple COUNT queries
• Cannot generate reports from SQL Management Data Warehouse
• User login error when trying to access secured SQL Server database

Get all the database in db2 through web Posted: 09 May 2013 07:35 PM PDT I would like to get all the database available in db2 instance from c# application. The CLP command to be used is "LIST DB DIRECTORY". I would like to know how can i fire this command from c# or is there other select statement i can use to get all the database in one db2 instance? Thanks.

Table design when one data is dependant of another and the second one has repeated values Posted: 09 May 2013 06:14 PM PDT I have a products, and subproducts. Like: Product Identifier - Product Name - Subproduct Identifier - SubProduct Name 1 ProA 1 SubProA1 1 ProA 2 SubProA2 1 ProA 3 SubProA3 2 ProB 1 SubProB1 2 ProB 2 SubProB2 2 ProB 3 SubProB3 3 ProC 1 SubProC1 3 ProC 2 SubProC2 3 ProC 3 SubProC3 As you can see, the subproduct identifier is not unique. My input is gonna be the product and the subproduct, and I need to return the names. I did this: Table Products: ID, Name Table SubProducts: IDproduct, IDsubProduct, Name But I know that is wrong, I wanted to make 3 tables, products, subproducts and a relation between the two, but I don't know how to design it. If I was to save the product and subproduct into another table, I would have to save both values, that table would have to have the productID and subproductID columns, that's why I feel having a relation product subproduct table would be the best. But I'm having a hard time doing it.

Hierarchical data best practices Posted: 09 May 2013 05:58 PM PDT I'm looking for a best practicies\databases to solve our problem: Currently we are using SQL Server 2008 R2 with HierarchyId. It works ok, but we have a lot of performance penalties due to this approach. Example (R = root): -R --R1 ---R1.1 ---R1.2 ---R1.3 --R2 --R3 Current setup: We have around 100 tables and all of those tables reference HierarchyId. Simplified, these are some of the high-level problems: 1. If I need to select records from R1.1 I need to do some joining to retrieve them 2. If record exists at R1 node, All children will have to share those records which adds complexety to select statement. 3. All of the unique constraints are useless. For example we want to enforce that in table animal all names are unique, but those must be unique only in the selected node. So, we have to create complex Check constraints instead.

sysjobhistory "sql_severity" codes Posted: 09 May 2013 08:18 PM PDT Where can I find out what the different "sql_severity" codes mean in the sysjobhistory table? Eg what does 0 mean? I've inherited a report that excludes ones with a 0 as "0 is not a real error"? I googled and looked at MSDN etc etc.. but no real luck.

How to use and optimize subquery on 100 million rows Posted: 09 May 2013 03:37 PM PDT What I'm trying to do is running a job on more than 100 million domains which haven't processed before. I have two tables, "domain" and "domain_setting", on every batch (10.000 domains per batch) I'm getting a list of domains from domain table by checking their status on "domain_setting". At first it was working fine but now approximately 10 million domain processed and it's getting slower. I know that my server is crappy for this kind of database, it has only 8 GB of memory and it's also shared with Apache web server (150 concurrent connections), some other small databases and some lightweight PHP applications. My innodb_buffer_pool_size is only 1 GB. Anyway, I would like to learn if there is a better way or thoughts to write this query more efficiently or any other approach for doing this job and speed up the process. Here is my SQL query: SELECT * FROM domain WHERE domain_ID NOT IN (SELECT domain_ID FROM domain_setting) OR domain_ID IN (SELECT domain_ID FROM domain_setting WHERE is_keyword_checked = 0) LIMIT 0,10000; I must get domains which are not exist in domain_setting table OR has "is_keyword_checked = 0" value in domain_setting table. How can I optimize this query? Thanks in advance :)

Configuring MySQL (my.cnf) for MyISAM and InnoDB Posted: 09 May 2013 06:18 PM PDT I want to convert some (not all) the MyISAM tables to InnoDB. Here is my current my.cnf: pastebin.com/FW8YXtLQ What would be the best configuration? (need to add InnoDB settings, byecause there are no InnoDB settings in my my.cnf)

Can't Select a View on Informix Posted: 09 May 2013 03:20 PM PDT I'm using Informix IDS 11.50 Innovator-C edition running on Slackware Linux 12.0. I've been running a very small Database on it(less than 10000 records on the biggest table). I'm trying to select a view from a web service, and every time there's a high load user load queries are being dropped with messages like: ERROR [HY000] [Informix .NET provider][Informix]Could not write to a temporary file Or ERROR [HY000] [Informix .NET provider][Informix]Could not open or create a temporary file The Web Service is using the .NET Informix Driver which relies on the Informix ODBC API. Searching for those errors on the web or in the online documentation I found that it's something related to DBTEMP environment variable and the DBSPACETEMP configuration parameter. Both are set. DBTEMP pointing to a directory with all permissions(rw), three dbspaces are listed on DBSPACETEMP. One regular dbspace and two flagged as temporary dbspace. Storage is not a problem, the directory and the dbspaces have a lot of free space left. The tables that the view Targets use R-tree indexes, and those are stored on a dedicated dbspace. One more thing I'm using a Geodetic Datablade on the database.

MySQL - how can I make a select statement get blocked? Posted: 09 May 2013 01:17 PM PDT Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables. To prepare the test, I created a table CREATE TABLE `test`.`client` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; and inserted 1,000,000 rows into the table. Now I will begin the test. In MySQL client #1, I deleted all the rows: mysql #1> delete from client; While the delete is still executing, in another MySQL client #2, I try to select a row. mysql #2> select * from client where id=1; +---------+------+ | id | name | +---------+------+ | 1 | joe | +---------+------+ 1 row in set (0.00 sec) The result is displayed instantly, the select was not blocked. Next I will try another test. I insert 1,000,000 rows into the table. In MySQL client #1, I update all the rows: mysql #1> update client set name='Bill'; While the update is still executing, in another MySQL client #2, I try to select a row. mysql #2> select * from client where id=100; +-----+------+ | id | name | +-----+------+ | 100 | joe | +-----+------+ 1 row in set (0.00 sec) mysql #2> select * from client where id=1000; +------+------+ | id | name | +------+------+ | 1000 | joe | +------+------+ 1 row in set (0.00 sec) The result is displayed instantly, the select was not blocked. So now my question, how can I demonstrate a SQL select statement getting blocked by another simple SQL delete or or update statement, using InnoDB tables? Or does MySQL never have any blocking issues? PS I am not trying to simulate two processes deadlocking each other, just one large update or insert blocking a select.

Should we always prefer clustering over a master-slave configuration if the goal is high-availability? Posted: 09 May 2013 12:29 PM PDT Given: I have two virtual machines on a SAN that I can use to host RavenDB. Question: If my goal is high-availability, then should I always prefer clustering over a master-slave configuration? From my vantage point, master-slave seems like more of a maintenance headache. Clustering seems much easier. [But perhaps I'm missing something.]

MySQL Slow query joining on subqueries with max Posted: 09 May 2013 12:36 PM PDT I'm creating a report based on user input (i.e. a search tool) that grabs data from eight tables and will output them to the screen. Here are the create table statements. I'm very aware of how badly some of these tables were made, but I don't have the option of changing them any time soon. CREATE TABLE `exp_z_srs_tickets` ( `id` bigint(25) unsigned NOT NULL AUTO_INCREMENT COMMENT 'e.g. 1', `srs_id` bigint(20) NOT NULL COMMENT 'e.g. 250; i.e. Ticket ''title''', `status` int(5) NOT NULL COMMENT 'e.g. draft, approved, rejected, saved', `position` int(5) NOT NULL, `wfrl1` int(5) NOT NULL COMMENT 'e.g. consult, workflow, report', `wfrl2` int(5) NOT NULL COMMENT 'e.g. revision', `wfrl3` int(5) NOT NULL, `home_dept_cd` varchar(50) CHARACTER SET latin1 NOT NULL COMMENT 'e.g. 071234', `target_eid` varchar(9) CHARACTER SET latin1 DEFAULT NULL, `updated_by_eid` varchar(9) CHARACTER SET latin1 NOT NULL DEFAULT '0', `date` datetime NOT NULL COMMENT 'e.g. 2013-02-22 14:39', `date_unix` varchar(10) CHARACTER SET latin1 NOT NULL, `transx` varchar(50) CHARACTER SET latin1 NOT NULL, PRIMARY KEY (`id`), KEY `srs_id` (`srs_id`), KEY `status` (`status`), KEY `position` (`position`), KEY `wfrl3` (`wfrl3`), KEY `wfrl2` (`wfrl2`) ) ENGINE=InnoDB AUTO_INCREMENT=269059 DEFAULT CHARSET=utf8 CREATE TABLE `exp_z_srs_ticket_meta` ( `id` bigint(30) unsigned NOT NULL AUTO_INCREMENT, `srs_id` bigint(25) unsigned NOT NULL, `version_id` bigint(25) unsigned NOT NULL, `meta_key` varchar(85) CHARACTER SET latin1 NOT NULL, `meta_value` longtext CHARACTER SET latin1 NOT NULL, `updated_unix` varchar(10) CHARACTER SET latin1 NOT NULL, `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `meta_key` (`meta_key`), KEY `srs_id` (`srs_id`) ) ENGINE=InnoDB AUTO_INCREMENT=739901 DEFAULT CHARSET=utf8 CREATE TABLE `exp_z_dept_assign` ( `LER` varchar(9) DEFAULT NULL, `LER_2` varchar(9) DEFAULT NULL, `SC` varchar(9) DEFAULT NULL, `CSR` varchar(9) DEFAULT NULL, `CSR_2` varchar(9) DEFAULT NULL, `BFTS` varchar(9) DEFAULT NULL, `DMS` varchar(9) DEFAULT NULL, `FSAP` varchar(9) DEFAULT NULL, `SC_2` varchar(9) DEFAULT NULL, `ISS` varchar(9) DEFAULT NULL, `MSO` varchar(9) DEFAULT NULL, `MSO_2` varchar(9) DEFAULT NULL, `DEPT_HR` varchar(9) DEFAULT NULL, `DEPT_HR_2` varchar(9) DEFAULT NULL, `DEPT_HR_3` varchar(9) DEFAULT NULL, `DEPT_GME_F` varchar(9) DEFAULT NULL, `DEPT_GME_R` varchar(9) DEFAULT NULL, `DEPT_FIN` varchar(50) DEFAULT NULL, `DEPT_PAYROLL` varchar(9) DEFAULT NULL, `AA_UNIT` varchar(4) DEFAULT NULL, `LEVEL_2_DEPT_CD` varchar(8) DEFAULT NULL, `LEVEL_2_DEPT_TITLE` varchar(60) DEFAULT NULL, `LEVEL_3_DEPT_CD` varchar(8) DEFAULT NULL, `LEVEL_3_DEPT_TITLE` varchar(60) DEFAULT NULL, `LEVEL_4_DEPT_CD` varchar(8) DEFAULT NULL, `LEVEL_4_DEPT_TITLE` varchar(60) DEFAULT NULL, `LEVEL_5_DEPT_CD` varchar(8) DEFAULT NULL, `LEVEL_5_DEPT_TITLE` varchar(60) DEFAULT NULL, `LEVEL_6_DEPT_CD` varchar(8) DEFAULT NULL, `LEVEL_6_DEPT_TITLE` varchar(60) DEFAULT NULL, `LEVEL_7_DEPT_CD` varchar(8) DEFAULT NULL, `LEVEL_7_DEPT_TITLE` varchar(60) DEFAULT NULL, `DEPT_CD` varchar(8) DEFAULT NULL, `DEPT_TITLE` varchar(60) DEFAULT NULL, `CountOfEMPLOYEE_ID` int(11) DEFAULT NULL, `DEPT_ORG_UNIT` varchar(4) DEFAULT NULL, `DEPT_ORG_UNIT_TITLE` varchar(60) DEFAULT NULL, `LAYOFF_UNIT` varchar(100) DEFAULT NULL, `CLUSTER` varchar(1) DEFAULT NULL, `DEPT_CD_LEVEL` varchar(20) DEFAULT NULL, `date_modified` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY `LEVEL_2_DEPT_CD` (`LEVEL_2_DEPT_CD`,`DEPT_CD`,`DEPT_TITLE`) USING BTREE, UNIQUE KEY `LER` (`LER`,`DEPT_CD`) USING BTREE, UNIQUE KEY `SC` (`SC`,`DEPT_CD`) USING BTREE, UNIQUE KEY `CSR` (`CSR`,`DEPT_CD`) USING BTREE, UNIQUE KEY `DMS` (`DMS`,`DEPT_CD`) USING BTREE, UNIQUE KEY `SC_2` (`SC_2`,`DEPT_CD`) USING BTREE, UNIQUE KEY `DEPT_CD` (`DEPT_CD`) USING BTREE, UNIQUE KEY `MSO` (`MSO`,`DEPT_CD`) USING BTREE, UNIQUE KEY `DEPT_HR` (`DEPT_HR`,`DEPT_CD`) USING BTREE, UNIQUE KEY `MSO_2` (`MSO_2`,`DEPT_CD`) USING BTREE, UNIQUE KEY `DEPT_HR_2` (`DEPT_HR_2`,`DEPT_CD`) USING BTREE, UNIQUE KEY `DEPT_HR_3` (`DEPT_HR_3`,`DEPT_CD`) USING BTREE, UNIQUE KEY `DEPT_GME_F` (`DEPT_GME_F`,`DEPT_CD`) USING BTREE, UNIQUE KEY `LER_2` (`LER_2`,`DEPT_CD`) USING BTREE, KEY `ISS` (`ISS`) USING BTREE, KEY `LEVEL_3_DEPT_CD` (`LEVEL_3_DEPT_CD`) USING BTREE, KEY `DEPT_CD_key` (`DEPT_CD`) USING BTREE, KEY `DEPT_TITLE` (`DEPT_TITLE`) USING BTREE, KEY `CLUSTER` (`CLUSTER`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `exp_z_srs_ticket_role_meta` ( `id` bigint(25) NOT NULL AUTO_INCREMENT, `srs_id` bigint(25) unsigned NOT NULL, `version_id` bigint(5) unsigned NOT NULL DEFAULT '1', `role` varchar(255) CHARACTER SET latin1 NOT NULL, `eid` varchar(10) CHARACTER SET latin1 NOT NULL, `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `srs_id` (`srs_id`), KEY `role` (`role`), KEY `eid` (`eid`) ) ENGINE=InnoDB AUTO_INCREMENT=650763 DEFAULT CHARSET=utf8 CREATE TABLE `exp_z_srs_tix_status` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 CREATE TABLE `exp_z_srs_tix_position` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `group` int(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 CREATE TABLE `exp_z_srs_users` ( `id` bigint(10) NOT NULL AUTO_INCREMENT, `eid` varchar(9) CHARACTER SET latin1 NOT NULL, `name` varchar(100) CHARACTER SET latin1 DEFAULT NULL, `email` varchar(100) CHARACTER SET latin1 DEFAULT NULL, `home_dept_code` varchar(6) CHARACTER SET latin1 DEFAULT NULL, `status` varchar(1) CHARACTER SET latin1 DEFAULT NULL, `last_login` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `supervisor_eid` varchar(9) CHARACTER SET latin1 DEFAULT NULL, `last_activity` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `home_dept_code` (`home_dept_code`), KEY `supervisor_eid` (`supervisor_eid`), KEY `eid` (`eid`) ) ENGINE=InnoDB AUTO_INCREMENT=56309 DEFAULT CHARSET=utf8 CREATE TABLE `exp_z_srs_transaction_types` ( `id` bigint(10) NOT NULL AUTO_INCREMENT, `key` varchar(25) DEFAULT NULL, `value` longtext, `date_modified` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `child_of` bigint(20) DEFAULT NULL, `menu_index` int(10) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=89 DEFAULT CHARSET=latin1 Here is the query. Note, it contains a MASSIVE in statement. This is generated based on user input and will be changed in the future. It slows things down a bit, but not as much as some of the joins are. select t.srs_id, t.status, t.position, t.wfrl2, t.wfrl3, t.home_dept_cd, t.updated_by_eid, t.date, t.date_unix, ts.name as status_name, tp.name as position_name, da.dept_title as dept_name, da.cluster as service_center, tm1.meta_value as form_type, tm2.meta_value as target_employee_name, tm3.meta_value as target_eid, trm1.eid as initiator_eid, trm2.eid as approver_eid, u1.name as target_name, u2.name as initiator_name, u3.name as approver_name, tt.value as transaction_type from exp_z_srs_tickets t join ( select max( id ) as id from exp_z_srs_tickets group by srs_id order by srs_id desc ) tj on tj.id = t.id join exp_z_srs_tix_status ts on ts.id = t.status join exp_z_srs_tix_position tp on tp.id = t.position join exp_z_dept_assign da on da.dept_cd = t.home_dept_cd left join ( select srs_id, meta_key, meta_value, max( version_id ) as version_id from exp_z_srs_ticket_meta where meta_key = 'rqst_category' group by srs_id ) tm1 on tm1.srs_id = t.srs_id left join ( select srs_id, meta_key, meta_value, max( version_id ) as version_id from exp_z_srs_ticket_meta where meta_key = 'target_employee_name' group by srs_id ) tm2 on tm2.srs_id = t.srs_id left join ( select srs_id, meta_key, meta_value, max( version_id ) as version_id from exp_z_srs_ticket_meta where meta_key = 'target_eid' group by srs_id ) tm3 on tm3.srs_id = t.srs_id join exp_z_srs_ticket_role_meta trm1 on trm1.srs_id = t.srs_id and trm1.role = 'Initiator' join exp_z_srs_ticket_role_meta trm2 on trm2.srs_id = t.srs_id and trm2.role = 'Approver' left join exp_z_srs_users u1 on u1.eid = tm3.meta_value left join exp_z_srs_users u2 on u2.eid = trm1.eid left join exp_z_srs_users u3 on u3.eid = trm2.eid left join exp_z_srs_transaction_types tt on tt.id = t.wfrl3 where 1 and t.home_dept_cd in (007940,008431,018661,023548,031673,101785,156093,407893,456777,650637,776725,779919,177613,257448,261415,268150,270593,272662,274198,276436,276908,278700,285676,327293,331698,336586,359542,359748,364493,373615,379469,382482,397154,400961,412351,425016,427443,447190,447546,448322,450461,450611,451004,452619,455260,455643,457115,468942,470421,525220,577058,603127,610930,642086,659497,706472,721133,727823,861464,899228,954186,228756,238629,241651,247584,252595,259295,528414,557392,877509,027973,549963,551441,552501,529271,529538,532837,545413,546635,548768,118811,109242,114646,117661,123921,127268,148101,155101,298323,331064,380180,460922,575997,899674,948336,981165,038989,070755,188790,231626,263468,288067,312717,355626,538706,538902,604723,612466,770847,773274,782264,850485,855006,892225,059215,178113,211176,442140,040823,127507,565221,752563,809664,106780,627673,097157,104354,156761,316697,941039,086739,133741,300846,587957,646395,816763,321716,390106,478263,510432,527497,336363,424963,479583,526835,541961,264216,276230,444380,538742,550665,742833,797427,053015,169267,276766,289182,301872,387405,618479,666005,696367,719949,938212,980460,035232,070461,161176,192106,211425,279255,289985,645298,649007,650183,652421,801145,890021,940334,130272,266884,279344,477638,562242,708201,836901,961463,071567,105291,142660,217492,312012,456991,513206,540365,550898,818217,979659,279399,088292,256859,320548,374696,709816,712848,726539,754213,767183,815434,838945,350863,355190,356652,467408,778698,023021,065850,083590,115949,120540,206912,273224,317801,343630,392211,420912,447207,456072,490089,588091,729064,738330,798051,847677,886599,898014,940682,953837,957110,036669,284221,301382,378754,428237,493175,623944,937730,947328,969553,996202,094285,358990,068009,157467,179274,643842,714490,741512,912882,930014,155959,281402,329754,357900,461486,466454,525836,582774,625577,638803,817824,848916,862659,888739,951474,987953,077543,185052,233376,408598,678190,701636,766095,920490,410905,567513,059616,747749,437780,048460,104764,573007,653830,812311,814328,917404,967252,026581,027624,041350,065075,119197,153176,176641,345843,457419,461850,481543,506107,530054,552798,600674,606240,617737,629270,701583,747801,799979,808166,830266,951134,955568,956594,991001,033403,053113,107342,112817,120158,137202,162102,173467,180930,204415,206967,221860,231289,280485,338931,394497,414288,417203,428004,468755,476158,478655,497616,543460,580106,652500,663972,679787,687288,769555,791441,812696,813472,839864,863532,878401,910358,948531,972157,974422,070611,102310,141876,222645,331359,424213,552020,563571,785430,989078,543521,791085,916361,985642,459417,650664,874940,031511,130405,243105,452398,479869,526345,854169,988729,162184,094882,268061,455279,457801,991804,161693,804990,883556,096540,352157,849559,306369,345674,915237,037016,053845,087792,227748,660305,782200,783094,859431,865219,945188,410353,952525,397181,876779,898256,969740,981263,086267,094784,189147,239290,252112,252443,273581,325053,370583,597884,598133,709503,712170,813720,298289,552896,875805,022996,134205,142492,201695,696296,971390,199001,207305,326365,396299,721419,952231,060454,331466,753651,876840,849309,141046,202596,275133,792654,431143,069892,161309,235613,238479,493451,962042,076820,097326,102203,102686,243025,305226,315313,357134,370930,491523,653812,710957,775263,868314,990182,113576,242650,359506,371788,124234,127632,161390,498811,529342,590006,771221,823050,989489,202408,252924,392033,415116,895534,038514,056977,135053,235506,465473,745750,767628,046015,278746,369960,670884,682238,846142,100465,182509,332214,404413,535549,636039,929507,140519,603993,711322,778377,195078,310942,347164,373768,380769,975519,019615,046569,049138,066001,072851,076599,078775,079426,084991,156075,165635,173234,328238,587476,713160,756239,111523,204406,485647,524418,023673,075420,114254,353968,584497,800743,189138,329843,461618,482098,578306,857530,246139,332651,483480,499561,830088,691219,650218,700209,540481,345665,684753,240769,513224,172967,215671,217170,221147,236131,303745,351158,453887,493059,532720,552486,598268,650888,657186,694644,699827,707738,715444,849657,886410,925048,015272,096112,132555,147601,300427,371001,433338,439528,523721,527932,629341,640104,656178,660163,682041,686056,733362,784949,880381,323359,500283,003098,024627,032770,074412,115921,181403,216509,219123,266955,276043,287843,348779,352576,413305,454458,522795,538323,551799,609470,616113,664882,701057,734922,748800,851992,866726,892626,897532,935974,959662,964969,467319,656908,255832,072600,449125,591032,641452,689133,731863,054318,528469,844288,415615,503191,933244,395389,222663,621134,198761,327836,635049,769911,466944,377684,974173,384729,009644,257279,121479,057510,329905,607212,642013,195167,008789,043973,051589,063111,064655,108467,123618,160104,169141,195835,228097,256582,258401,295503,329996,418676,419648,420440,445637,453253,463215,468504,489135,502405,559265,562331,563394,573294,581267,774950,833904,860027,903482,913248,303674,650129,671179,721614,822140,867271,969633,273545,545262,550139,746330,777154,916272,941609,188736,688376,886802,322788,555759,181029,720722,748882,029356,081752,130691,415544,460539,531400,693672,819859,845125,850332,902928,968803,158689,242856,844028,126884,184856,909253,924343,093954,252933,384499,437487,499259,575853,726370,797329,998745,009136,078702,113120,118884,290143,541300,976386,989327,031110,103989,492755,646723,115994,118615,374222,176516,348573,349894,396048,543317,666853,764774,809959,859681,948407,016912,082868,177221,475417,771613,235846,043651,187032,314190,725344,787161,873031,887758,921845,996328,177258,303255,444923,988024,988239,633504,000590,034466,146247,148218,148343,204736,457507,642674,715202,782442,787713,010794,164538,207323,210140,354280,523874,114913,477095,515160,142553,741022,125616,317142,497625,631891,410978,453903,584157,784011,812366,896695,932986,102338,276579,510977,569496,569584,725530,771953,831087,843804,926412,932012,982011,171030,310112,433276,684138,687171,758567,174670,654571,866708,897694,346762,124653,161452,478021,877144,180477,216787,428291,723319,810643,055120,290768,397984,439145,772818,519941,261576,478566,649034,662410,737983,071255,321547,615463,778322,780195,946980,462788,785056,820696,566809,638082,222501,346619,436424,327202,842681,440598,996550,370681,134624,242552,287184,374936,704223,715845,808763,915558,304441,316072,114496,365526,777573,097040,151427,337433,575274,312762,584914,903240,997559,049995,054906,300999,406331,260880,407937,435933,588304,631230,698908,491293,749006,056174,069838,113095,172921,230020,257144,283810,342819,487930,489064,509514,515437,526256,541257,549179,558836,562974,564570,632649,726352,729457,744788,749818,794894,852973,908290,909477,940076,974501,979668,991993,117812,428488,572892,763560,072334,428843,741399,997087,343140,814505,062782,954113,055825,375052,178943,384319,757309,400088,743324,222306,455787,947382,013185,016734,221085,244603,330500,362315,476666,535647,657257,863131,374623,625719,187201,964273,247496,395030,107011,729322,997229,069954,140868,237087,946524,002678,471796,504350,404477,764408,990529,321618,436656,638858,798355,111122,472205,995301,074083,530802,693841,061079,396798,735431,985740,203407,215234,219882,712704,751939,882548,998326,082582,360978,687867,035599,394077,537066,668628,672855,930238,966020,135865,137505,294096,481259,716390,799899,179988,183456,240812,359267,455055,479011,696857,594164,985303,173939,300490,320520,357367,443728,541701,559657,704562,742129,813588,884662,991163,127311,592763,108546,120489,138755,275936,372750,462699,671268,900868,195354,835485,279022,665596,816013,913765,199804,242847,256396,404510,901661,156235,229489,778572,006825,859789,952124,160131,412734,274278,364126,527344,253264,938481,055424,060837,079587,088853,185864,216411,236998,613330,821285,092483,159722,307091,546975,466007,428353,557178,425695,420146,448714,415198,414787,410193,404743,581908,435620,534648,455910,469941,449526,470920,515204,449107,478940,491710,516258,518773,396164,377014,372536,245871,255663,245513,245238,235123,232420,230431,001508,003070,258606,267286,363298,358874,341320,337344,308134,292819,281803,275838,273028,271119,222814,998040,884715,874174,957101,868537,860394,857585,847267,846295,826306,815522,811198,908539,913836,995356,985802,973414,968769,962444,957833,954630,939239,924860,919643,915246,807363,792869,785047,660724,637172,632890,632827,629733,620634,605973,600781,599445,594869,590701,668833,674210,777644,768477,768191,764079,745304,743949,734076,730043,724283,700691,676502,584898,220068,003953,162308,157788,012783,013675,016173,056450,064931,070648,078784,175535,130192,496172,211880,208260,099351,009724,818342,200231,195773,183376,083475,105870,124911,129676,930489,133830,148281,149002,143142,476853,152195,098254,151025,157261,154317,096933,087596,154111,194603,428978,643236,437469,243338,763524,698294,182778,404100,584308,602351,694430,963620,984581,047327,602397,248556,208885,294595,336728,206342,854016,497643,860223,890414,554787,648623,946052,367702,058911,052221,590168,207056,311576,680784,204852,421387,628609,430313,434685,124154,935661,301541,411833,657024,176384,226419,219141,908879,877297,076090,033234,190153,923004,095881) group by t.srs_id order by t.srs_id desc Here's the explain for the query. +----+-------------+-----------------------+--------+--------------------------------+-------------+---------+---------------------------------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------------+--------+--------------------------------+-------------+---------+---------------------------------+--------+----------------------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 47863 | Using temporary; Using filesort | | 1 | PRIMARY | t | eq_ref | PRIMARY,srs_id,status,position | PRIMARY | 8 | tj.id | 1 | Using where | | 1 | PRIMARY | da | ref | DEPT_CD,DEPT_CD_key | DEPT_CD_key | 11 | zucsfhr_DEVEE150.t.home_dept_cd | 1 | Using where | | 1 | PRIMARY | ts | eq_ref | PRIMARY | PRIMARY | 4 | zucsfhr_DEVEE150.t.status | 1 | | | 1 | PRIMARY | trm1 | ref | srs_id,role | srs_id | 8 | zucsfhr_DEVEE150.t.srs_id | 2 | Using where | | 1 | PRIMARY | tp | eq_ref | PRIMARY | PRIMARY | 4 | zucsfhr_DEVEE150.t.position | 1 | | | 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 47343 | | | 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 46682 | | | 1 | PRIMARY | <derived5> | ALL | NULL | NULL | NULL | NULL | 36743 | | | 1 | PRIMARY | u1 | ref | eid | eid | 11 | tm3.meta_value | 1 | | | 1 | PRIMARY | u2 | ref | eid | eid | 11 | zucsfhr_DEVEE150.trm1.eid | 1 | | | 1 | PRIMARY | trm2 | ref | srs_id,role | srs_id | 8 | zucsfhr_DEVEE150.t.srs_id | 2 | Using where | | 1 | PRIMARY | u3 | ref | eid | eid | 11 | zucsfhr_DEVEE150.trm2.eid | 1 | | | 1 | PRIMARY | tt | eq_ref | PRIMARY | PRIMARY | 8 | zucsfhr_DEVEE150.t.wfrl3 | 1 | | | 5 | DERIVED | exp_z_srs_ticket_meta | ref | meta_key | meta_key | 87 | | 75554 | Using where; Using temporary; Using filesort | | 4 | DERIVED | exp_z_srs_ticket_meta | ref | meta_key | meta_key | 87 | | 98574 | Using where; Using temporary; Using filesort | | 3 | DERIVED | exp_z_srs_ticket_meta | ref | meta_key | meta_key | 87 | | 102798 | Using where; Using temporary; Using filesort | | 2 | DERIVED | exp_z_srs_tickets | index | NULL | srs_id | 8 | NULL | 260955 | Using index | +----+-------------+-----------------------+--------+--------------------------------+-------------+---------+---------------------------------+--------+----------------------------------------------+ I'm very certain that the three tm joins ( tm1, tm2, tm3 ) are the culprit. If I remove the subquery and do a simple join on those tables, the query is much faster. As it is now, I don't know how long the query takes to run. I gave up after about five minutes of waiting. How would you recommend rewriting this?

SQL Server - Full text search migration 2005 -> 2012(.ndf file?) Posted: 09 May 2013 12:15 PM PDT So I'm trying to figure out the details for migrating from sql server 2005 to sql server 2012. I tried to do restore and backup of a DB from 2005 server to 2012 server. This worked well and for the text search catalog created a .ndf file. I tried to detach and attach a DB from 2005 to 2012 server, this also seemed to work fine including the text search but I'm wondering where's the catalog files on the file system because I don't see a .ndf file or a dedicated folder for text search catalog (the way we used to have in versions prior to 2008)? (PS. I atttached only .mdf and .ldf files, not sure how to attach a specific text search catalog file because it's multiple files) I might have missed something but any suggestions will be helpful, thanks!

Merge Statement + Replication difference for SQL Server 2012 Posted: 09 May 2013 11:36 AM PDT We have a SSIS package that will pull from a source database and run a merge statement to put the data into a "Master" database. (Eventually the "Master" database will be the source of truth, but we are a while away from that.) After the Master Database gets the data, it replicates it down to several other databases. With Sql Server 2008 R2 this all worked great. But with SQL Server 2012 we are noticing that there are a bunch of delete statements for these tables being replicated. The Merge Statement in the SSIS Package does not delete anything. when NOT MATCHED BY SOURCE then update set IsActive = 0 So I am left to wonder why this is happening. Could SQL Server 2012 be translating my MATCHED Update to a delete and insert? (that seems insane, but I can't seem to figure out why this is happening). NOTE: We turned off replication of delete statements and the problem stopped, but I want to know what is going on. Why would I be seeing so many deletes when the source seems unchanged? Is there a way I can see the transactions posted against a table? NOTE: I tried doing a SQL Trace, but it did not show any deletes. Only the merge I described above.

If I fail over one database, do the others that share the same mirror endpoint fail over as well? Posted: 09 May 2013 01:29 PM PDT We have two databases setup for mirroring on a single SQL Server instance: a test database and a production database. Both get mirrored to another server using the exact same endpoints. If I go into the Database Properties for the test database and click the "Failover" button, will it fail over the production database as well since both databases share a mirror endpoint and their Server Network Addresses properties are the same? I am concerned because when I set up mirroring for the 2nd database, I did not have to configure anything new. It just used all the existing information. If I use the "Failover" button from the Database Properties, will it result in failing over all databases that use that endpoint, or just the specific database that I am viewing the properties for?

What is the safest way to run SSIS on Multiple Named SQL Server Instances? Posted: 09 May 2013 09:54 AM PDT I have an Active/Active SQL Server 2008 R2 cluster. There are two named instances on each node and no default instance. I need to deploy SSIS packages to the different instances. To do this, I needed to modify the MsDtsSrvr.ini.xml file to add the instance names so they would be seen by BIDS and SSMS. I put all 4 instance names in that XML file (on both nodes) and restarted SSIS on both nodes. I can now see the folder for each instance when connecting to any instance in SSMS through an Integration Services connection. However, clicking the 2 instance names that are on the other node generates a login failure since they are not active on the current node. Maybe this is just an inconvenience, but am I going to run into any issues with this setup? Or is there a way to do it that makes more sense?

How would I debug/test MongoDB stale connections? Posted: 09 May 2013 09:12 AM PDT I am running into a problem with stale connections in mongo and am not sure how to debug or resolve it. I performed some heavy load testing earlier in the day (3pm) and then later that night our staging site was failing (around midnight) because of connection timeout issues. Which leads me to believe that I am getting stale connections. I am not sure whether the issue is in my database or in my mongoid pool code? Any suggestions how I could debug this, or can someone shed some light on mongo and pooling. ps. I am using Ruby/Rails with an Object Relation Mapper, Monoid2, and mongo-ruby driver 1.6. Backtrace vendor/ruby-1.9.3/lib/ruby/1.9.1/thread.rb:71:in `sleep' vendor/ruby-1.9.3/lib/ruby/1.9.1/thread.rb:71:in `wait' vendor/bundle/ruby/1.9.1/gems/mongo-1.6.2/lib/mongo/util/pool.rb:298:in `block (2 levels) in checkout' <internal:prelude>:10:in `synchronize' vendor/bundle/ruby/1.9.1/gems/mongo-1.6.2/lib/mongo/util/pool.rb:252:in `block in checkout' vendor/bundle/ruby/1.9.1/gems/mongo-1.6.2/lib/mongo/util/pool.rb:245:in `loop' vendor/bundle/ruby/1.9.1/gems/mongo-1.6.2/lib/mongo/util/pool.rb:245:in `checkout' vendor/bundle/ruby/1.9.1/gems/mongo-1.6.2/lib/mongo/connection.rb:500:in `checkout_writer' vendor/bundle/ruby/1.9.1/gems/mongo-1.6.2/lib/mongo/networking.rb:34:in `send_message' vendor/bundle/ruby/1.9.1/gems/mongo-1.6.2/lib/mongo/collection.rb:981:in `block in insert_documents' vendor/bundle/ruby/1.9.1/gems/mongo-1.6.2/lib/mongo/util/logging.rb:36:in `instrument' vendor/bundle/ruby/1.9.1/gems/mongo-1.6.2/lib/mongo/collection.rb:977:in `insert_documents' vendor/bundle/ruby/1.9.1/gems/mongo-1.6.2/lib/mongo/collection.rb:353:in `insert' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/collections/master.rb:25:in `block in insert' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/collections/retry.rb:29:in `retry_on_connection_failure' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/collections/master.rb:24:in `insert' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/collection.rb:98:in `insert' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/persistence/operations/insert.rb:27:in `block in persist' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/persistence/insertion.rb:26:in `block (3 levels) in prepare' vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:414:in `_run__692211473077833086__create__1885631693259307085__callbacks' vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:405:in `__run_callback' vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:385:in `_run_create_callbacks' vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:81:in `run_callbacks' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/callbacks.rb:43:in `run_callbacks' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/persistence/insertion.rb:25:in `block (2 levels) in prepare' vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:403:in `_run__692211473077833086__save__1885631693259307085__callbacks' vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:405:in `__run_callback' vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:385:in `_run_save_callbacks' vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:81:in `run_callbacks' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/callbacks.rb:43:in `run_callbacks' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/persistence/insertion.rb:24:in `block in prepare' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/persistence/insertion.rb:22:in `tap' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/persistence/insertion.rb:22:in `prepare' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/persistence/operations/insert.rb:26:in `persist' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/persistence.rb:49:in `insert' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/persistence.rb:154:in `upsert' vendor/bundle/ruby/1.9.1/gems/mongoid-2.4.12/lib/mongoid/persistence.rb:75:in `save!' app/models/visit.rb:25:in `save_visit!' app/operations/visiting_operation.rb:20:in `visit!' app/controllers/api/v1/application_controller.rb:93:in `visit_id' vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:418:in `_run__2237228336839231794__process_action__3138631562321588978__callbacks' vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:405:in `__run_callback' vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:385:in `_run_process_action_callbacks' vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.13/lib/active_support/callbacks.rb:81:in `run_callbacks' vendor/bundle/ruby/1.9.1/gems/actionpack-3.2.13/lib/abstract_controller/callbacks.rb:17:in `process_action' vendor/bundle/ruby/1.9.1/gems/actionpack-3.2.13/lib/action_controller/metal/rescue.rb:29:in `process_action' vendor/bundle/ruby/1.9.1/gems/actionpack-3.2.13/lib/action_controller/metal/instrumentation.rb:30:in `block in process_action' vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.13/lib/active_support/notifications.rb:123:in `block in instrument' vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.13/lib/active_support/notifications/instrumenter.rb:20:in `instrument' vendor/bundle/ruby/1.9.1/gems/activesupport-3.2.13/lib/active_support/notifications.rb:123:in `instrument' vendor/bundle/ruby/1.9.1/gems/actionpack-3.2.13/lib/action_controller/metal/instrumentation.rb:29:in `process_action' vendor/bundle/ruby/1.9.1/gems/actionpack-3.2.13/lib/action_controller/metal/params_wrapper.rb:207:in `process_action' vendor/bundle/ruby/1.9.1/gems/newrelic_rpm-3.5.5.38/lib/new_relic/agent/instrumentation/rails3/action_controller.rb:34:in `block in process_action' vendor/bundle/ruby/1.9.1/gems/newrelic_rpm-3.5.5.38/lib/new_relic/agent/instrumentation/controller_instrumentation.rb:268:in `block in perform_action_with_newrelic_trace' vendor/bundle/ruby/1.9.1/gems/newrelic_rpm-3.5.5.38/lib/new_relic/agent/method_tracer.rb:240:in `trace_execution_scoped' vendor/bundle/ruby/1.9.1/gems/newrelic_rpm-3.5.5.38/lib/new_relic/agent/instrumentation/controller_instrumentation.rb:263:in `perform_action_with_newrelic_trace' vendor/bundle/ruby/1.9.1/gems/newrelic_rpm-3.5.5.38/lib/new_relic/agent/instrumentation/rails3/action_controller.rb:33:in `process_action' vendor/bundle/ruby/1.9.1/gems/actionpack-3.2.13/lib/abstract_controller/base.rb:121:in `process' vendor/bundle/ruby/1.9.1/gems/actionpack-3.2.13/lib/abstract_controller/rendering.rb:45:in `process' vendor/bundle/ruby/1.9.1/gems/actionpack-3.2.13/lib/action_controller/metal.rb:203:in `dispatch' vendor/bundle/ruby/1.9.1/gems/actionpack-3.2.13/lib/action_controller/metal/rack_delegation.rb:14:in `dispatch' vendor/bundle/ruby/1.9.1/gems/actionpack-3.2.13/lib/action_controller/metal.rb:246:in `block in action' vendor/bundle/ruby/1.9.1/gems/actionpack-3.2.13/lib/action_dispatch/routing/route_set.rb:73:in `call' vendor/bundle/ruby/1.9.1/gems/actionpack-3.2.13/lib/action_dispatch/routing/route_set.rb:73:in `dispatch' vendor/bundle/ruby/1.9.1/gems/actionpack-3.2.13/lib/action_dispatch/routing/route_set.rb:36:in `call' vendor/bundle/ruby/1.9.1/gems/journey-1.0.4/lib/journey/router.rb:56:in `each' vendor/bundle/ruby/1.9.1/gems/journey-1.0.4/lib/journey/router.rb:56:in `call'

SQL Server Browser uses NT AUTHORITY\SYSTEM account and logon failure error in SQL Log Posted: 09 May 2013 09:22 AM PDT Using SQL Server 2012 on Windows 2008 R2 I have been getting errors in SQL Logs for failed login for NT AUTHORITY\SYSTEM. All of SQL Server's services are running under their own Domain accounts, except for SQL Server Browser service. Once I had shut down this service, the errors immediately stopped and so I believe this was the cause. I believe there are three(3) solutions to this: Giving database permissions to NT AUTHORITY\LOCAL SERVICE account Running this service under a different account Using a non-dynamic port and disabling this service However, here are my questions : Why is this service attempting to logon to databases? I thought, its purpose was simply to allow users to logon to SQL server using an alias instead of IP address What is the best solution to this error? Thank you!

DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS' Posted: 09 May 2013 09:24 AM PDT I have inherited a few scripts that are pumped through a c#.NET custom app to update a SQL2005 SP3 database. One of these scripts does a deletion on a user table called msw_timer_task, based on a filtered join to sys.conversation_endpoints. Here is the SQL: SET ANSI_NULLS , ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF /* SELECT name, is_ansi_nulls_on, is_ansi_padding_on, is_ansi_warnings_on, is_arithabort_on, is_concat_null_yields_null_on, is_numeric_roundabort_on, is_quoted_identifier_on FROM sys.databases where name = db_name() */ print 'clean up unattended time tasks' delete from msw_timer_task where timer_task_id in ( select m.timer_task_id from msw_timer_task m left join sys.conversation_endpoints c on c.conversation_handle = m.conversation_handle where c.conversation_handle is null and m.state = 'WAITING') This script errors with the following error message: ERROR: Severity 16, state 1 error number 1934 line 8 of proc on server SomeSillySrv: DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. I have done a lot of Googling on the issue, and have played around a lot with the set statement in an attempt to get it to work. In testing, in SSMS, the script did not produce an error, at least not before shipping it. I am not sure if I am missing something, but it seems that the settings are not properly set when run through the app. Am I missing something in the script?

Foreign key with multiple references Posted: 09 May 2013 03:17 PM PDT I have the following three tables in mysql database named "THE_COLLEGE" mysql> desc students; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | STU_Id | char(5) | NO | PRI | | | | STU_Name | varchar(20) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> desc staff; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | STF_Id | char(5) | NO | PRI | | | | STF_Name | varchar(20) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> desc users; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | Id | char(5) | NO | | | | | user_Name | varchar(20) | YES | | NULL | | | Password | varchar(20) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.02 sec) If a new row want to be inserted into the table "users", that row must be found in students table or staff table. Which means a staff or a student can be a user. Is it possible? I tried with following foreign keys. ALTER TABLE users ADD CONSTRAINT fky_stu FOREIGN KEY (ID) REFERENCES Students (STU_Id); ALTER TABLE users ADD CONSTRAINT fky_stf FOREIGN KEY (ID) REFERENCES Staff (STF_Id); But the user table is allow to insert a row, if the row found in both tables (Student and Staff). But I need to allow if the row found in any of the table (Student or Staff).

mysqlslap chokes on strings that contain ";" delimiter? Posted: 09 May 2013 08:46 PM PDT I'm having a problem passing preexisting files full of SQL statements into mysqlslap. For example, I have a file named create.sql that contains my table structure (as dumped by mysqldump), with normal ; delimiting. I also have a file called slap.sql (actually a slightly munged general-log-file, but this is a tiny example that reproduces the error) that contains INSERT INTO comments VALUES ("I like winks ;) and frowns :("); And I run: mysqlslap --delimiter=";" --create create.sql --query thing.sql I get the error: mysqlslap: Cannot run query INSERT INTO comments VALUES ("I like winks ERROR : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"I like winks' at line 1 Which is consistent with MySQL terminating the statement at the ; that's in the middle of a string. What can I do to make mysqlslap take --create data from mysqldump and not hork on semicolons embedded in strings in the --query data? Is it possible that mysqlslap does not follow normal parsing rules when you pass a file in to the --query parameter?

Writing with multiple psql processes to database seems to damage data Posted: 09 May 2013 06:42 PM PDT I have a couple terabytes of CSV data that I am trying to import into a PostgreSQL 8.4 database (on a RedHat 6.2 server), whose data directory is initialized on a multipath hardware RAID. There are four folders of CSV data that need to be imported, and the import script acts according to what it finds in those directories, so right now it's simplest for me to run the import script separately for each server. I have run these scripts serially on a Debian server (without multipath) before, waiting for each script to finish, and that worked. However, when I had to re-import later on this RedHat system, I decided to fire up four screen sessions and blast away. Unfortunately, something I'm doing here is destroying the filesystem (asterisks are omitted names): [trevor@***** ~]$ ls -lah /var/datastore/ ls: cannot access /var/datastore/usersnapshot: Input/output error ls: cannot access /var/datastore/****: Input/output error ls: cannot access /var/datastore/localdb: Input/output error ls: cannot access /var/datastore/*****_DATA: Input/output error ls: cannot access /var/datastore/*****: Input/output error total 48K drwxr-xr-x 10 root root 4.0K May 3 23:16 . drwxr-xr-x. 31 root root 4.0K Apr 22 20:45 .. d?????????? ? ? ? ? ? ***** d?????????? ? ? ? ? ? *****_DATA d?????????? ? ? ? ? ? **** drwxrwx--- 2 root root 4.0K Feb 26 11:47 ******* drwxrwx--- 5 trevor coders 4.0K Apr 24 14:07 codez drwxrwx--- 3 root root 4.0K Mar 14 11:28 ***** d?????????? ? ? ? ? ? localdb drwx------ 2 root root 16K Feb 26 11:17 lost+found drwxrwx--- 2 root root 4.0K Feb 26 11:47 ******** drwxr-xr-x 2 1000 1000 4.0K May 4 00:00 trexdata drwxr-xr-x 2 1000 1000 4.0K May 4 00:00 trexdata_snapshot d?????????? ? ? ? ? ? usersnapshot There should be a postgres data directory here with ownership postgres.postgres named pgsqldb, but it's now gone. Worse, when I drop into a psql prompt to look at the database, the tables are listed, but only data from the first import script has been imported properly. If I stop the postmaster, unmount, and run fsck, I don't get that directory back either. What's going on here? I was assured the multipath drivers and mounts for the RAID volume in question is working, so I don't think it's the hardware at this point. For reference, each script adds about 105,000 points every couple seconds to a table in the database. Here's the import script code: #!/bin/bash # run as: /pathtoshfile/cell_import.sh $(pwd)/data_files in IMPORT_DATA dir for csv_file in $@ do myfilename=`basename $csv_file` # echo $myfilename i=${myfilename:0:4} j=${myfilename:5:4} grid=${myfilename:17:1} echo "loading grid $grid, i=$i, j=$j from file $csv_file" psql db <<SQLCOMMANDS CREATE TEMPORARY TABLE timport (LIKE data10min); COPY timport (point_date,gmt_time,surface_skin_temp_k,surface_pressure_mb,accum_precip_kg_per_m2,agl_2m_humid_g_per_kg,down_shortwave_rad_flux_w_per_m2,down_longwave_rad_flux_w_per_m2,agl_10m_temp_k,agl_10m_windspd_m_per_s,agl_10m_winddir_deg,agl_50m_temp_k,agl_50m_windspd_m_per_s,agl_50m_winddir_deg,agl_temp_k,agl_80m_windspd_m_per_s,agl_80m_winddir_deg,agl_100m_temp_k,agl_100m_windspd_m_per_s,agl_100m_winddir_deg,agl_200m_temp_k,agl_200m_windspd_m_per_s,agl_200m_winddir_deg) FROM '$csv_file' WITH CSV; UPDATE timport SET grid_id = '$grid', grid_i=$i, grid_j=$j; INSERT INTO data10min SELECT * FROM timport; SQLCOMMANDS done Sample script output: COPY 105408 UPDATE 105408 INSERT 0 105408 loading grid E, i=0135, j=0130 from file /media/backup1/****_DATA/E/0135_0130.****.E.txt CREATE TABLE COPY 105408 UPDATE 105408 INSERT 0 105408 loading grid E, i=0135, j=0131 from file /media/backup1/****_DATA/E/0135_0131.****.E.txt CREATE TABLE COPY 105408 UPDATE 105408 INSERT 0 105408

SQL error "#1118 - row size too large" on the first row of the table only Posted: 09 May 2013 09:30 AM PDT I ran into a problem using a mySQL database. I have some columns as type text but when I try to enter data into the first row I get the error code "#1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs" for anything with more than 15 characters. This only occurs for the first row, all the other rows in the column and table work just fine. Help! Field Type Collation Null Default Extra pid int(11) No None AUTO_INCREMENT 3 text utf8_general_ci Yes NULL 6 text utf8_general_ci Yes NULL 7 text utf8_general_ci Yes NULL 8 text utf8_general_ci Yes NULL 9 text utf8_general_ci Yes NULL 10 text utf8_general_ci Yes NULL 21 text utf8_general_ci Yes NULL 22 text utf8_general_ci Yes NULL 23 text utf8_general_ci Yes NULL 24 text utf8_general_ci Yes NULL 25 text utf8_general_ci Yes NULL 26 text utf8_general_ci Yes NULL 27 text utf8_general_ci Yes NULL 28 text utf8_general_ci Yes NULL 29 text utf8_general_ci Yes NULL 30 text utf8_general_ci Yes NULL 31 text utf8_general_ci Yes NULL 32 text utf8_general_ci Yes NULL 33 text utf8_general_ci Yes NULL 34 text utf8_general_ci Yes NULL 35 text utf8_general_ci Yes NULL 36 text utf8_general_ci Yes NULL 37 text utf8_general_ci Yes NULL 38 text utf8_general_ci Yes NULL 39 text utf8_general_ci Yes NULL 40 text utf8_general_ci Yes NULL 41 text utf8_general_ci Yes NULL 42 text utf8_general_ci Yes NULL 44 text utf8_general_ci Yes NULL 45 text utf8_general_ci Yes NULL 46 text utf8_general_ci Yes NULL 47 text utf8_general_ci Yes NULL 48 text utf8_general_ci Yes NULL 49 text utf8_general_ci Yes NULL 50 text utf8_general_ci Yes NULL 51 text utf8_general_ci Yes NULL 52 text utf8_general_ci Yes NULL 53 text utf8_general_ci Yes NULL 54 text utf8_general_ci Yes NULL 55 text utf8_general_ci Yes NULL 56 text utf8_general_ci Yes NULL 57 text utf8_general_ci Yes NULL 58 text utf8_general_ci Yes NULL 59 text utf8_general_ci Yes NULL Indexes: Keyname Type Unique Packed Field Cardinality Collation Null PRIMARY BTREE Yes No pid 61 A Server Version: 5.5.9

Hash in SQL query Posted: 09 May 2013 06:06 PM PDT I saw few sql queries with ### appended before and after column name in where clause like `select a from emp where a.###id### = 1` What is significance of these hashes?

Oracle schema import is not importing all the tables present in the schema dump file Posted: 09 May 2013 09:06 AM PDT I have exported an existing oracle schema from another machine and then imported it in my local machine. Import was successful, but some tables which are present in the export dump file are not imported. Here are the export and import commands i have used. Export Command: --------------- exp sreeni/sreeni@agentrics1:1524/ezmodc full=n file=SreeniTrunkDump.dmp log=SreeniTrunkDump_Export.log Import Command: --------------- imp badri/badri@localhost:1521/xe file=SreeniTrunkDump.dmp log=BadriSchemaImport_Import.log full=y The Oracle we are using is 10g EE. What could be going wrong ? Can you please suggest a solution to this issue.

SQL Server in a state of confusion: does the database exist or doesn't it? Posted: 09 May 2013 12:37 PM PDT Got a really weird, annoying problem.. Somehow the instance of SQL Server 2008 R2 running on our server has gotten somewhat corrupted. First, we noticed that the database we created yesterday was missing. So, we looked around and found that it was still there, but detached. So, we tried to attach the mdf but got a message which was something like The file is currently in use. I thought that was odd, so restarted SQL Server. Same thing... okay, time for drastic measures... so, I stopped the service, zipped up the mdf, started the service, unzipped it and then tried to restore it. The above message was gone, but then I got: Cannot attach a database with the same name as an existing database Ouch. Of course it's not showing in the database explorer, so no idea what's going on... last resort: DROP DATABASE [DatabaseName] Of course that didn't work.. that tells me the database does not exist. So, I'm stuck... at one point SQL Server thinks the database does exist and at another point it thinks the db does not exist.. obviously it's in a state of confusion. Has anyone seen this before? Got any ideas on how to fix it?

mysql better index? Posted: 09 May 2013 02:06 PM PDT Hi i am not sure where should i add primary index in my table salas order. Table have invoice date where is lets say 50 values added every day and i need to search them this column is date datatype or add index to invoice number where lets say is 40 values added every day with same invoice number but this column is varchar datatype and values looks like this a100,a100,a100,a101,a102,a102 etc what will be better for search of invoices. Selec* from salesorders where date ... or where invoicenumer ...

Increase in memory use when database server switches Posted: 09 May 2013 12:06 PM PDT I have an ASP.NET 4.0 application running on Window Server 2008 and a Oracle 11g database server running on Linux. There are two database servers with the same virtual IP. Recently somehow one database server went down and another database server starting working. Every thing went perfectly on the database server site with a small problem with the Oracle listener. But on my application server, memory started increasing and went up to above 15 GB, almost 99% use. Normally it uses 2 Gb of memory. I am using ADO.Net to connect to Oracle. My application has properly closed database connections. I was not able to figure out what is the problem and what should I do in my application. Can any one suggest me what should I do?

Queries getting stuck on very simple COUNT queries Posted: 09 May 2013 05:06 PM PDT process list: | 16004 | metrics | xxx:64616 | metrics | Query | 29064 | Sending data | SELECT COUNT(*) FROM Plugin where LastUpdated >= '1356856200' | 0 | 0 | 1021 | | 16019 | metrics | xxx:23506 | metrics | Query | 29043 | Sending data | SELECT COUNT(*) FROM Plugin where LastUpdated >= '1356856200' | 0 | 0 | 1021 | | 16102 | metrics | xxx:9117 | metrics | Query | 28881 | Sending data | SELECT COUNT(*) FROM Plugin where LastUpdated >= '1356856200' | 0 | 0 | 1021 | | 16149 | metrics | xxx:14772 | metrics | Query | 28768 | Sending data | SELECT COUNT(*) FROM Plugin where LastUpdated >= '1356856200' | 0 | 0 | 1021 | | 16384 | metrics | xxx:51572 | metrics | Query | 27916 | Sending data | SELECT COUNT(*) FROM Plugin where LastUpdated >= '1356858000' | 0 | 0 | 1021 | ... | 17890 | metrics | xxx:28643 | metrics | Query | 709 | Sending data | SELECT COUNT(*) FROM Plugin where LastUpdated >= '1356885000' | 0 | 0 | 21 | | 17924 | metrics | xxx:48797 | metrics | Query | 681 | Sending data | SELECT COUNT(*) FROM Plugin where LastUpdated >= '1356885000' | 0 | 0 | 21 | | 17929 | metrics | xxx:55624 | metrics | Query | 671 | Sending data | SELECT COUNT(*) FROM Plugin where LastUpdated >= '1356885000' | 0 | 0 | 21 | | 17938 | metrics | xxx:2632 | metrics | Query | 654 | Sending data | SELECT COUNT(*) FROM Plugin where LastUpdated >= '1356885000' | 0 | 0 | 21 | The table only has around 2500 rows, only ~1000 need to be scanned. Table is InnoDB. This started happening last night. I was not able to run REPAIR TABLE but ANALYZE TABLE ran ok. I can't run any selects manually. When it happened last night I had to kill the mysql process and let it do crash recovery but it's doing it again. UPDATEs on the table run without error (it is frequently updated), it just seems to be anything that selects from it that use COUNT(). I am able to SELECT * FROM Plugin and have every row returned, but even if I do SELECT COUNT(ID) FROM Plugin it still freezes on sending data Explain: mysql> explain SELECT COUNT(*) FROM Plugin where LastUpdated >= '1356856200'; +----+-------------+--------+-------+---------------+-------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+-------------+---------+------+------+--------------------------+ | 1 | SIMPLE | Plugin | range | LastUpdated | LastUpdated | 5 | NULL | 1382 | Using where; Using index | MySQL is using Percona Server 5.5.27 Any ideas on what is causing this? Thanks a lot. EDIT: innodb% variables and INNODB STATUS Rolando: you say to increase innodb_io_capacity could you please explain? I am using 2x 7200 rpm hard drives in a ZFS mirror. Why should I increase it to 2000 ? The MySQL website advises 100 for single 7200 rpm drives (I have it at the default 200) Excerpt from INNODB STATUS. These lovely selects have been stuck for half a day now. These are the oldest queries. ---TRANSACTION 27986E9D1, ACTIVE 48622 sec fetching rows mysql tables in use 1, locked 0 MySQL thread id 570263, OS thread handle 0xad7380000, query id 1217058950 lb1.xxxx xxx.ip.xxx metrics Sending data SELECT COUNT(*) FROM Plugin where LastUpdated >= '1357635600' Trx read view will not see trx with id >= 27986E9D3, sees < 278C78F08 ---TRANSACTION 2797C7249, ACTIVE 49200 sec fetching rows mysql tables in use 1, locked 0 MySQL thread id 570063, OS thread handle 0xad7380800, query id 1216141959 lb1.xxxx xxx.ip.xxx metrics Sending data SELECT COUNT(*) FROM Plugin where LastUpdated >= '1357635600' Trx read view will not see trx with id >= 2797C724A, sees < 278C78F08 Full status: ===================================== 130109 22:44:19 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 50 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 737366 1_second, 728615 sleeps, 73673 10_second, 921 background, 911 flush srv_master_thread log flush and writes: 766639 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 75724985, signal count 103543991 --Thread 46560449536 has waited at row0sel.c line 3941 for 0.0000 seconds the semaphore: S-lock on RW-latch at 0x83368d9b8 '&block->lock' a writer (thread id 46560448512) has reserved it in mode wait exclusive number of readers 1, waiters flag 1, lock_word: ffffffffffffffff Last time read locked in file btr0sea.c line 942 Last time write locked in file /usr/local/src/Percona-Server-5.5.27-rel28.1/storage/innobase/row/row0upd.c line 2194 --Thread 46560428032 has waited at row0sel.c line 2924 for 0.0000 seconds the semaphore: S-lock on RW-latch at 0x8337b76f8 '&block->lock' number of readers 1, waiters flag 0, lock_word: fffff Last time read locked in file btr0sea.c line 942 Last time write locked in file /usr/local/src/Percona-Server-5.5.27-rel28.1/storage/innobase/row/row0upd.c line 2194 --Thread 46560448512 has waited at row0upd.c line 2194 for 0.0000 seconds the semaphore: X-lock (wait_ex) on RW-latch at 0x83368d9b8 '&block->lock' a writer (thread id 46560448512) has reserved it in mode wait exclusive number of readers 1, waiters flag 1, lock_word: ffffffffffffffff Last time read locked in file btr0sea.c line 942 Last time write locked in file /usr/local/src/Percona-Server-5.5.27-rel28.1/storage/innobase/row/row0upd.c line 2194 --Thread 46560446464 has waited at row0sel.c line 2924 for 0.0000 seconds the semaphore: S-lock on RW-latch at 0x83368d9b8 '&block->lock' a writer (thread id 46560448512) has reserved it in mode wait exclusive number of readers 1, waiters flag 1, lock_word: ffffffffffffffff Last time read locked in file btr0sea.c line 942 Last time write locked in file /usr/local/src/Percona-Server-5.5.27-rel28.1/storage/innobase/row/row0upd.c line 2194 Mutex spin waits 860007450, rounds 4788572135, OS waits 44460586 RW-shared spins 56868852, rounds 806806376, OS waits 12775136 RW-excl spins 115255109, rounds 878353038, OS waits 11254430 Spin rounds per wait: 5.57 mutex, 14.19 RW-shared, 7.62 RW-excl -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (read thread) I/O thread 4 state: waiting for i/o request (read thread) I/O thread 5 state: waiting for i/o request (read thread) I/O thread 6 state: waiting for i/o request (read thread) I/O thread 7 state: waiting for i/o request (read thread) I/O thread 8 state: waiting for i/o request (read thread) I/O thread 9 state: waiting for i/o request (read thread) I/O thread 10 state: waiting for i/o request (write thread) I/O thread 11 state: waiting for i/o request (write thread) I/O thread 12 state: waiting for i/o request (write thread) I/O thread 13 state: waiting for i/o request (write thread) I/O thread 14 state: waiting for i/o request (write thread) I/O thread 15 state: waiting for i/o request (write thread) I/O thread 16 state: waiting for i/o request (write thread) I/O thread 17 state: waiting for i/o request (write thread) Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 6880197 OS file reads, 409935916 OS file writes, 1108361 OS fsyncs 1 pending preads, 1 pending pwrites 15.60 reads/s, 16930 avg bytes/read, 190.92 writes/s, 2.34 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 824, free list len 518, seg size 1343, 364193 merges merged operations: insert 589402, delete mark 221097, delete 9349 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 21249841, node heap has 32061 buffer(s) 142440.39 hash searches/s, 8066.70 non-hash searches/s --- LOG --- Log sequence number 3642503658567 Log flushed up to 3642503574648 Last checkpoint at 3636441447521 Max checkpoint age 6957135914 Checkpoint age target 6739725417 Modified age 6062211046 Checkpoint age 6062211046 0 pending log writes, 0 pending chkp writes 398344529 log i/o's done, 170.86 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 11031019520; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 695290368 (169998728 + 525291640) Page hash 10625704 (buffer pool 0 only) Dictionary cache 42705560 (42501104 + 204456) File system 99832 (82672 + 17160) Lock system 26573920 (26563016 + 10904) Recovery system 0 (0 + 0) Dictionary memory allocated 204456 Buffer pool size 655359 Buffer pool size, bytes 10737401856 Free buffers 96 Database pages 623202 Old database pages 230029 Modified db pages 307887 Pending reads 1 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 20888946, not young 0 183.06 youngs/s, 0.00 non-youngs/s Pages read 6929404, created 2039787, written 15209639 16.10 reads/s, 11.04 creates/s, 23.24 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 12.42/s, Random read ahead 0.00/s LRU len: 623202, unzip_LRU len: 0 I/O sum[750]:cur[6], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 20 read views open inside InnoDB ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 2797C724A Read view up limit trx id 278C78F08 Read view low limit trx id 2797C724A Read view individually stored trx ids: Read view trx id 2797C7249 Read view trx id 2797C6206 Read view trx id 2797A2F63 Read view trx id 27976266E Read view trx id 279761B1E Read view trx id 278C78F08 ----------------- Main thread id 34397547520, state: sleeping Number of rows inserted 42523215, updated 894466983, deleted 12775, read 9294801842 12.36 inserts/s, 907.78 updates/s, 0.00 deletes/s, 17273.29 reads/s ------------ TRANSACTIONS ------------ Trx id counter 27B056747 Purge done for trx's n:o < 278FA84BC undo n:o < 5D History list length 15024237 LIST OF TRANSACTIONS FOR EACH SESSION: [...] ---TRANSACTION 279D23E45, ACTIVE 42765 sec fetching rows mysql tables in use 1, locked 0 MySQL thread id 572264, OS thread handle 0xb0420f400, query id 1225918036 lb1.xxxx xxx.ip.xxx metrics Sending data SELECT COUNT(*) FROM Plugin where LastUpdated >= '1357641000' Trx read view will not see trx with id >= 279D23E48, sees < 2797C7249 ---TRANSACTION 279B9A787, ACTIVE 45181 sec fetching rows mysql tables in use 1, locked 0 MySQL thread id 571429, OS thread handle 0xad737c400, query id 1222536287 lb1.xxxx xxx.ip.xxx metrics Sending data SELECT COUNT(*) FROM Plugin where LastUpdated >= '1357639200' Trx read view will not see trx with id >= 279B9A78D, sees < 2797C7249 ---TRANSACTION 2799448B4, ACTIVE 47853 sec fetching rows mysql tables in use 1, locked 0xxx.ip.xxx MySQL thread id 570543, OS thread handle 0xadb3ea000, query id 1218229116 lb1.xxxx xxx.ip.xxx metrics Sending data SELECT COUNT(*) FROM Plugin where LastUpdated >= '1357635600' Trx read view will not see trx with id >= 2799448B5, sees < 278C78F08 ---TRANSACTION 27986E9D1, ACTIVE 48622 sec fetching rows mysql tables in use 1, locked 0 MySQL thread id 570263, OS thread handle 0xad7380000, query id 1217058950 lb1.xxxx xxx.ip.xxx metrics Sending data SELECT COUNT(*) FROM Plugin where LastUpdated >= '1357635600' Trx read view will not see trx with id >= 27986E9D3, sees < 278C78F08 ---TRANSACTION 2797C7249, ACTIVE 49200 sec fetching rows mysql tables in use 1, locked 0 MySQL thread id 570063, OS thread handle 0xad7380800, query id 1216141959 lb1.xxxx xxx.ip.xxx metrics Sending data SELECT COUNT(*) FROM Plugin where LastUpdated >= '1357635600' Trx read view will not see trx with id >= 2797C724A, sees < 278C78F08 ---------------------------- END OF INNODB MONITOR OUTPUT ============================ innodb% variables | innodb_adaptive_flushing | OFF | | innodb_adaptive_flushing_method | estimate | | innodb_adaptive_hash_index | ON | | innodb_adaptive_hash_index_partitions | 1 | | innodb_additional_mem_pool_size | 10485760 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_blocking_buffer_pool_restore | OFF | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_restore_at_startup | 0 | | innodb_buffer_pool_shm_checksum | ON | | innodb_buffer_pool_shm_key | 0 | | innodb_buffer_pool_size | 10737418240 | | innodb_change_buffering | all | | innodb_checkpoint_age_target | 0 | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_corrupt_table_action | assert | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_dict_size_limit | 0 | | innodb_doublewrite | OFF | | innodb_doublewrite_file | | | innodb_fake_changes | OFF | | innodb_fast_checksum | OFF | | innodb_fast_shutdown | 1 | | innodb_file_format | Barracuda | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_file_per_table | ON | | innodb_flush_log_at_trx_commit | 2 | | innodb_flush_method | O_DIRECT | | innodb_flush_neighbor_pages | none | | innodb_force_load_corrupted | OFF | | innodb_force_recovery | 0 | | innodb_ibuf_accel_rate | 100 | | innodb_ibuf_active_contract | 1 | | innodb_ibuf_max_size | 5368692736 | | innodb_import_table_from_xtrabackup | 0 | | innodb_io_capacity | 200 | | innodb_kill_idle_transaction | 0 | | innodb_large_prefix | OFF | | innodb_lazy_drop_table | 0 | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_block_size | 512 | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 4294967296 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 75 | | innodb_max_purge_lag | 0 | | innodb_merge_sort_block_size | 1048576 | | innodb_mirrored_log_groups | 1 | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 0 | | innodb_open_files | 300 | | innodb_page_size | 16384 | | innodb_purge_batch_size | 20 | | innodb_purge_threads | 1 | | innodb_random_read_ahead | OFF | | innodb_read_ahead | none | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 8 | | innodb_recovery_stats | OFF | | innodb_recovery_update_relay_log | OFF | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | | innodb_show_locks_held | 10 | | innodb_show_verbose_locks | 0 | | innodb_spin_wait_delay | 6 | | innodb_stats_auto_update | 1 | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | ON | | innodb_stats_sample_pages | 8 | | innodb_stats_update_need_lock | 1 | | innodb_strict_mode | OFF | | innodb_support_xa | OFF | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_concurrency_timer_based | OFF | | innodb_thread_sleep_delay | 10000 | | innodb_use_global_flush_log_at_trx_commit | ON | | innodb_use_native_aio | OFF | | innodb_use_sys_malloc | ON | | innodb_use_sys_stats_table | OFF | | innodb_version | 1.1.8-27.0 | | innodb_write_io_threads | 8 |xxxx

Cannot generate reports from SQL Management Data Warehouse Posted: 09 May 2013 01:29 PM PDT I'm running SQL Server 2008 R2 and have installed the MDW on one server and have a Data Collector collecting and uploading the server activity, query results, and Disk activity data to the MDW. When I select any of the reports from the MDW with Data Collection > Reports > Management Data Warehouse I receive the error: Exception has been thrown by the target of an invocation - > Object reference not set to an instance of an object. This occurs for all 3 reports and after I've waiting some time and data has been uploaded from the data collector. I do not have SSRS running, but read that isn't necessary. Any suggestions?

User login error when trying to access secured SQL Server database Posted: 09 May 2013 12:26 PM PDT We have a username that was recently renamed from one username to another (think getting married). The Active Directory admin renamed the user because "it has always worked in the past". One vendor package we use uses the built-in MS SQL Server security. Each module has three groups: xxView = View Only xxUser = Add, Update rows (cannot delete) xxAdmin = Delete rows So we can add a person to one of these groups an they get the appropriate access. I don't have the actual error message in front of me anymore, but it said that they are not authorized to table CriticalVendorTable. It worked before the rename. The admin removed the person from each group and re-added them. Still no go. I even restarted the server and it still doesn't work. My best guess is that there is UUID (or unique id) somewhere that is causing problems. The vendor's response is to delete the user and then re-add them. I have only had time to do some brief searching, but I found this page; AD User SID Mis-mapping. Would this be worth trying? Would it be better to just delete the user and recreate them?

You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610