[how to] Which privileges for MySQL extended status?

---

• Which privileges for MySQL extended status?
• Db design for historical option data (~200GB)
• XSD Database Schema when you have Composite Keys
• Game database- many to many relationship
• do MYSQL views occupy physical space?
• Recommended hardware for dedicate sever using MySQL for POS (point of sale) [closed]
• Object name 'Clean Up History' in use when trying to complete the maintenance plan wizard in SQL Server 2008 R2
• Mysql suddenly stopped after is said to be crashed
• Cannot connect to SQL Server 2008 after updating the service pack
• which sql server edition is good for practice
• Use cases for NoSQL databases: Time series
• sql:space at the end of string
• Can I make sure two columns dont have the same value
• Correct way to perform an UPDATE on multiple tables?
• How to implement posts with “seen by” like facebook?
• Show all results for past N "insert batches"
• Lock escalation problem on a trigger
• Functional Dependencies and Normal Forms
• disallow SET command in a postgresql server
• SQL Server service shutdown due to 'bad?' SSL cert
• Conditionally INSERTING columns into Table
• Why is SQL running the same query longer from another connection?
• Can't connect to SQL Server Windows 7
• Remote connection to PostgresSQL in Windows 2008 Server is prompting the error below
• Are view references in a query properly called "derived tables" as such?
• Indexing is not working
• How to increase fast backup and restore of 500GB database using mysqldump?
• How to check growth of database in mysql?
• Is there a tool to check if my database is normalized to the third normal form?

Which privileges for MySQL extended status? Posted: 13 Apr 2013 06:34 PM PDT If I want to execute "SHOW STATUS" within MySQL or use "mysqladmin extended-status" on command line with a non-root user, which privileges would this user then need?

Db design for historical option data (~200GB) Posted: 13 Apr 2013 02:15 PM PDT I need some help determining how to best design my tables. I have about 200GB worth of data I wish to place in a table (historical option price data). The table looks something like: String symbol Date date Date exp; double strike; double ask; double bid; <20 other columns> 95% of the time I will be fetching the current date's data. But the other 5% of the time I want to analyze historical data. Most of the queries would involve some combination of symbol/date/exp. I'm not sure how to best organize the table/s in terms of efficiency. Would it be best to put the data into two tables: historical and current? Or an index on the date? Or maybe an extra boolean column (isCurrent) which indicates which data is current or not? If it matters, I'm using java/hibernate/mysql under the covers. Thanks

XSD Database Schema when you have Composite Keys Posted: 13 Apr 2013 12:20 PM PDT Imagine I have a relational database with many tables, and these link to each other with composite keys that are made up of three or more columns. How can I write the XSD database schema to account for that fact? I can't find any examples of XSD Schemas where the referenced keys are composite. I am using the ID/IDREF way of showing the relations between the tables, but I can't seem to get it to work for when my table unique identifier is of composite type. Plus I also have to tell the Schema that these must also be unique, and I can't find any examples of that either when done on composite keys. I would greatly appreciate any help. Thanks

Game database- many to many relationship Posted: 13 Apr 2013 12:13 PM PDT I am designing a simple relational database about a game that can be played by 2 players each time. I have these tables: Player, Game, PlayerPlaysGame, Payment. I recognize there is a many-to-many relationship between Player and Game and one-to-many between Player and Payment since one player can make many payments whereby one payment can't be made by more than one player. In PlayerPlaysGame table I have only playerID and gameID attributes. Can I also add gameDate and gameScore to that table or is it better to have them in Game table? I'd like to ask if I'm on the right track. Thank you.

do MYSQL views occupy physical space? Posted: 13 Apr 2013 08:10 PM PDT do MYSQL views occupy physical space? Or in other words, is a MYSQL database limited to a certain number of views?

Recommended hardware for dedicate sever using MySQL for POS (point of sale) [closed] Posted: 13 Apr 2013 10:17 AM PDT I'm having some doubts about this point in my project (it's social service). The POS have developed on VB.NET using Visual Studio 2010. The MAXIMUM number of POS that is thinking to attend this server is 8 or 10. The tests will be with 3 POS for started, and gradually escalate till 5 or 6. I don't want a too-much computer, just one that gives the best performance without troubles for this kind of service. Actually I have in mind the next hardware for the server: Intel Core 2 DuoProcessor E4300 (2M Cache, 1.80GHz x 2, 800 MHz FSB) RAM 2 GB DDR3 Windows 7 64 bits 80 GB HDD What do you think? Really I need some orientation. I would like to have the ideal configuration for the server to fit into this project.

Object name 'Clean Up History' in use when trying to complete the maintenance plan wizard in SQL Server 2008 R2 Posted: 13 Apr 2013 02:13 PM PDT I am trying to create a maintenance plan on a instance running SQL Server 2008 R2 SP1 (no CU's installed). When completing the wizard I get the following error: The object name cannot be changed from "{1E1746D3-B671-4799-8F61-7EE6117257C2}" to "Clean Up History" because another object in the collection already uses that name. Use a different name to resolve this error. ({1E1746D3-B671-4799-8F61-7EE6117257C2}) I've checked both [msdb].[dbo].[sysjobsteps] and [msdb].[dbo].[sysjobs] and neither contain the name "Clean Up History". The server is being backed up by Microsoft DPM. Could it be that DPM inserts a cleanup job somewhere that I don't see?

Mysql suddenly stopped after is said to be crashed Posted: 13 Apr 2013 02:17 PM PDT We have a mysql instance running well and suddenly it stop. I have gone through the logs file but found no indication why it stopped? But there have some instance start and stop before this? Below is the snippet of mysqld.log 121229 22:17:45 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 121229 22:17:50 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 121229 22:17:50 InnoDB: Initializing buffer pool, size = 8.0M 121229 22:17:50 InnoDB: Completed initialization of buffer pool 121229 22:17:50 InnoDB: Started; log sequence number 0 206087326 121229 22:17:50 [Note] Event Scheduler: Loaded 0 events 121229 22:17:50 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.66-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 130205 11:09:32 [Note] /usr/libexec/mysqld: Normal shutdown 130205 11:09:32 [Note] Event Scheduler: Purging the queue. 0 events 130205 11:09:34 InnoDB: Starting shutdown... 130205 11:09:36 InnoDB: Shutdown completed; log sequence number 0 529664030 130205 11:09:36 [Note] /usr/libexec/mysqld: Shutdown complete 130205 11:09:36 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 130205 11:09:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 130205 11:09:37 InnoDB: Initializing buffer pool, size = 8.0M 130205 11:09:37 InnoDB: Completed initialization of buffer pool 130205 11:09:37 InnoDB: Started; log sequence number 0 529664030 130205 11:09:37 [Note] Event Scheduler: Loaded 0 events 130205 11:09:37 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.67-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 130310 11:33:12 [Note] /usr/libexec/mysqld: Normal shutdown 130310 11:33:12 [Note] Event Scheduler: Purging the queue. 0 events 130310 11:33:14 InnoDB: Starting shutdown... 130310 11:33:16 InnoDB: Shutdown completed; log sequence number 0 788753738 130310 11:33:16 [Note] /usr/libexec/mysqld: Shutdown complete 130310 11:33:16 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 130310 11:36:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 130310 11:36:03 InnoDB: Initializing buffer pool, size = 8.0M 130310 11:36:03 InnoDB: Completed initialization of buffer pool 130310 11:36:04 InnoDB: Started; log sequence number 0 788753738 130310 11:36:04 [Note] Event Scheduler: Loaded 0 events 130310 11:36:04 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.67-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 130413 20:56:55 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 130413 20:56:56 InnoDB: Initializing buffer pool, size = 8.0M 130413 20:56:56 InnoDB: Completed initialization of buffer pool InnoDB: Log scan progressed past the checkpoint lsn 0 1139894636 130413 20:56:56 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 0 1139895853 130413 20:56:56 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 335782050, file name ./mysql-bin.000003 130413 20:56:57 InnoDB: Started; log sequence number 0 1139895853 130413 20:56:57 [Note] Recovering after a crash using mysql-bin 130413 20:56:59 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 809, event_type: 2 130413 20:56:59 [Note] Starting crash recovery... 130413 20:56:59 [Note] Crash recovery finished. 130413 20:56:59 [Note] Event Scheduler: Loaded 0 events 130413 20:56:59 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.67-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution

Cannot connect to SQL Server 2008 after updating the service pack Posted: 13 Apr 2013 08:45 AM PDT After updating, whenever I start the service of the server in configuration manager, it always stops then. And I found the error in the log 2013-04-13 19:08:19.63 spid7s Error: 15174, Severity: 16, State: 1. 2013-04-13 19:08:19.63 spid7s Login '##MS_PolicyEventProcessingLogin##' owns one or more database(s). Change the owner of the database(s) before dropping the login. 2013-04-13 19:08:19.63 spid7s Error: 912, Severity: 21, State: 2. 2013-04-13 19:08:19.63 spid7s Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 15174, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion. 2013-04-13 19:08:19.63 spid7s Error: 3417, Severity: 21, State: 3. 2013-04-13 19:08:19.63 spid7s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online. 2013-04-13 19:08:19.63 spid7s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required. Can anyone pinpoint why this is happening? As I cannot start the server of SQL server, I cannot connect via management studio and edit anything. How can I edit, for example, the owner of the database?

which sql server edition is good for practice Posted: 13 Apr 2013 06:12 AM PDT Which SQL Server (free/trail) would be good to install, which requires minimum resource and have maximum features. I mean, I can practice mirroring,logshipping and replication. I found this link, but confuse with enterprise and standard edition.

Use cases for NoSQL databases: Time series Posted: 13 Apr 2013 01:42 AM PDT I need to set up a database that will record temporal series data (sensors). A friend of mine has recommended me to use a NoSQL database instead of using Oracle (for instance). There're also some articles that recommend the same approach: see Andrew Oliver's article. Why is not convenient to record a temporal series in Oracle (or any other relational database)? Could you please help me in giving an example?

sql:space at the end of string Posted: 13 Apr 2013 03:52 AM PDT I have a sql code like shown below declare @str nvarchar(max),@i int set @i=0 set @str='abc ' declare @tbl Table(a nvarchar(max)) insert @tbl select @str while (select a from @tbl)<>'' begin set @i=@i+1 set @str = substring(@str,2,len(@str)) update @tbl set a=@str select * from @tbl end Here @str has value 'abc ' (there is an space at the end) when above query is executed it will stop when only a space is present in 'a'. Also output of this query is: bc c for the above query if i give input @str as 'abcd' then output will be bcd cd d So in the first case that is @str='abc ' i want to get output like bc c Now the code is checking for space and because of that i am having problem. But i want it to consider the space at the end also. Same is the problem in sql for len also.Both len('a') and len('a ') will return 1. So if anyone please help on my query so that it will give my desired output.

Can I make sure two columns dont have the same value Posted: 13 Apr 2013 01:04 PM PDT If I have a table that looks like this CREATE TABLE foo ( id INT NOT NULL AUTO_INCREMENT, aa INT NOT NULL, bb INT NOT NULL, PRIMARY KEY (id), UNIQUE KEY (aa, bb), CONSTRAINT aa_ref FOREIGN KEY (aa) REFERENCES bar (id), CONSTRAINT bb_ref FOREIGN KEY (bb) REFERENCES bar (id) ) Is there a way to make sure that aa != bb besides using application level logic or forcing a trigger to fail on BEFORE INSERT?

Correct way to perform an UPDATE on multiple tables? Posted: 13 Apr 2013 07:35 AM PDT This query is working, but I am only just beginning to understand relational tables. I have spent the last few yeas doing all of the processing with PHP after retrieving the data I needed (oftentimes using multiple queries). So, please hold my hand here, and tell me if this is being done the "right" way?: Table Breakdowns SettingsList UserSettings SettingTypes Basically when I add a setting to the API (through the control panel) it gets put into SettingsList, I give the setting a value and a type (so that it actually does something) in the UserSettings table (this will be the only table accessed by the client from the app) . and the setting itself can only be a type specified in the enumeration (of sorts) in the SettingTypes table. Right now, when I edit a setting from MY administrative control panel (the client will have far less control), I am running an update qiery that looks something like this: UPDATE `SettingsList` l, `UserSettings` u SET l.settingName = 'pow', l.settingDescription = 'otherTest', u.setting = 0, u.type = 1 WHERE l.settingID = 3 AND u.settingID = 3 Does this look correct? Again, it does work but as most here would agree, just because it works, doesn't mean I did it right. Well, I may have supplied more information than you need to give me an answer, but how am I doing in my first application with relational tables?

How to implement posts with “seen by” like facebook? Posted: 13 Apr 2013 01:24 PM PDT Inside a Facebook group there is a feature when every time see the group newsfeed it mark all posts as seen by me. And if I see a specific post inside a group thought a url (facebook.com/groups/123/posts/123), it mark as seen this single post. My question is about what is the intuitive way and right to model this and performance. I want to know what I am missing or wrong about about to get it right. What happens on facebook: "When a new member join the group, all the old posts are marked as seen by him". But is it per post or global? Scenarios: Mark as seen all individual posts when click group. Pros: Easy development Easy and fast queries. Could implement a int counter column on each post and a has_many table users_id x post_id Getting and individual post I can tell how many users saw without context Cons: How to deal with a new member join a group with > 100 posts? Batch writes? 2.Mark as seen the group when click group. Pros: One single write when seen a group Any tips and advices are welcome! Thanks!

Show all results for past N "insert batches" Posted: 13 Apr 2013 10:53 AM PDT Every once in a while I have several users check in with some value: +-------+-------------+------+---------------------+ | id | user | RT | time | +-------+-------------+------+---------------------+ | 78984 | Twitter | 0.19 | 2013-04-12 06:04:16 | | 78983 | Instagram | 0.15 | 2013-04-12 06:04:16 | | 78982 | Facebook | 1.44 | 2013-04-12 06:04:16 | | 78981 | Youtube | 0.12 | 2013-04-12 06:04:16 | | 78980 | Dailymotion | 3.72 | 2013-04-12 06:04:16 | | 78979 | Metacafe | 0.29 | 2013-04-12 06:04:16 | | 78978 | Google+ | 0.32 | 2013-04-12 06:04:16 | | 78977 | Twitter | 0.47 | 2013-04-12 06:04:01 | | 78976 | Flickr | 1.69 | 2013-04-12 06:04:01 | | 78975 | Instagram | 0.15 | 2013-04-12 06:04:01 | | 78974 | Facebook | 3.01 | 2013-04-12 06:04:01 | | 78973 | Photobucket | 1.29 | 2013-04-12 06:04:01 | | 78972 | Youtube | 0.22 | 2013-04-12 06:04:01 | | 78971 | Dailymotion | 4.11 | 2013-04-12 06:04:01 | | 78970 | Vimeo | 1.12 | 2013-04-12 06:04:01 | | 78969 | Metacafe | 0.73 | 2013-04-12 06:04:01 | | 78968 | Google+ | 0.31 | 2013-04-12 06:04:01 | | 78967 | Twitter | 0.56 | 2013-04-12 06:04:13 | | 78966 | Youtube | 0.36 | 2013-04-12 06:04:13 | | 78965 | Dailymotion | 4.39 | 2013-04-12 06:04:13 | | 78964 | Metacafe | 0.51 | 2013-04-12 06:04:13 | | 78963 | Google+ | 0.66 | 2013-04-12 06:04:13 | | 78962 | Twitter | 0.21 | 2013-04-12 06:03:16 | | 78961 | Instagram | 0.11 | 2013-04-12 06:03:16 | | 78960 | Facebook | 1.4 | 2013-04-12 06:03:16 | | 78959 | Youtube | 0.15 | 2013-04-12 06:03:16 | | 78958 | Dailymotion | 0.38 | 2013-04-12 06:03:16 | | 78957 | Metacafe | 0.23 | 2013-04-12 06:03:16 | | 78956 | Google+ | 0.31 | 2013-04-12 06:03:16 | +-------+-------------+------+---------------------+ Each 'batch' will have the same time value. For clarity I've added spaces between each 'batch' in the example output. Not every user is represented in each 'batch'. Due to latencies, the time values for each batch are not necessarily sequential, though they should only be outside of true sequentiality by less than a minute (in the example output it can been seen that the second and third batches' positions are transposed in time). How might I get the total RT for each user for the last N 'batches'? For instance the total RT for user 'Twitter' is 0.19 + 0.47 + 0.56 + 0.21 whereas the total RT for user 'Vimeo' is 0 + 1.12 + 0 + 0. If I were to do this in a sloppy way via Python or PHP, I would pull (amountOfUsers * N) rows from the table (ordered by time, not id) and add them up until I got to an N+1 distinct time value. This would require at least two queries (one to get count(*) DISTINCT USERS and one for the real query) and also a bit of 'logic'. However, is there a clever SQL query which could get me the answer in one query? This would be the ideal output: +-------------+-------+ | user | RT | +-------------+-------+ | Twitter | 1.43 | | Flickr | 1.69 | | Instagram | 0.41 | | Facebook | 5.85 | | Photobucket | 1.29 | | Youtube | 0.95 | | Dailymotion | 12.62 | | Vimeo | 1.12 | | Metacafe | 1.78 | | Google+ | 1.60 | +-------------+-------+ I would like to avoid the use of a stored procedure as A) my user doesn't have CALL OR EXECUTE privilege, and B) I would like to learn to use SQL more effectively. The target environment is MySQL 5.x with InnoDB tables running on any arbitrary Linux distro. Thanks.

Lock escalation problem on a trigger Posted: 13 Apr 2013 08:16 AM PDT I've inherited a SQL Server 2005 database that is getting 2-3 deadlocks a day. I've tracked it down to a scheduled job that runs during the day and inserts into a table with a trigger. The trigger consists of 10 updates to another table for slightly different criteria. The deadlock occurs in the trigger. When a person makes an application and the job is running that's when the deadlock occurs. The application inserts into the same table as the scheduled job. Looking at the trace it seems to happen when process 1 obtains a key lock, process 2 obtains a page lock, then process 1 escalates the key lock to a page lock and process 2 tries to obtain a key lock. I've added missing indexes which seems to have helped but its still happening. I'm not a DBA so any advice on an approach to solving this would be appreciated. I've added a link to the deadlock xml - this is from a test I did to duplicate the problem. deadlock xml

Functional Dependencies and Normal Forms Posted: 13 Apr 2013 09:16 AM PDT I'm trying to grasp the concepts of functional dependencies and normal forms, specifically first (1NF), second (2NF), and third (3NF) normal forms. The link here was extremely helpful in deciding the distinction between the three forms, but I have a much more abstract problem. The accepted answer to this post gives wonderful examples of tables and which normal form they are in. However, my question poses the following: For (W X Y Z), determine the form of: WX->Y, WX->Z, Y->Z. (Note: 'WX' should be underlined as the combined primary key, but I couldn't find that in the formatting, so it's bold) I don't get it; how can I even begin to do this? The textbook I'm referencing only gives concrete examples, and I don't understand the theory well enough to apply it to the abstract. Any insight?

disallow SET command in a postgresql server Posted: 13 Apr 2013 12:02 PM PDT Im planning to publish my postgresql server to a few untrusted clients. I dont want them to modify any runtime setting, like work_mem or something risky to my server. In general I assume the pg_catalog schema is public but I don't want to allow updating pg_settings at all. Is it possible?

SQL Server service shutdown due to 'bad?' SSL cert Posted: 13 Apr 2013 06:01 AM PDT This morning it transpired that SQL Server 2012 service running on Win2012 had shut itself down. When I logged in as the server admin and tried to access SSMS, after entering my credentials I got the 'transport' error. On checking Windows Services, both SQL Server and Agent were stopped. I tried to start them, but got a timeout error. The SQL event log contains this entry: The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid. The Windows event logs contain these entries: Log Name: Application Source: MSSQLSERVER Date: 14/03/2013 09:01:40 Description: Unable to load user-specified certificate [Cert Hash(sha1) "A1CF6EE1C352B7A185950EB400013269759C24BD"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online. Description: TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property. Description: TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property. Description: Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log. Description: SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems. The following procedure fixed the problem: Start > SQL Server Configuration Manager SQL Server Network Configuration > Protocols for MSSQLSERVER (Properties) Uncheck General > Force Encryption Start > Services > Start MSSQLSERVER service I looked at the certificate in Certificate Manager, and Windows reports no problems. The certificate itself is valid until March 2014. Can anyone offer any advice as to why this might suddenly have occurred? I really don't need mornings to start off like this ever again...!

Conditionally INSERTING columns into Table Posted: 13 Apr 2013 03:01 AM PDT using SQL Azure and like to insert a data to a table using dynamic Query. No of columns in select statment is dynamic. It will be based on some conditions. Ex: Salary, Code. How can I create a table for conditional based columns and do insert in coditions? I don't want to use SELECT INTO #tempTable as it is not supporting in SQL Azure Here is the code: DECLARE @sql NVARCHAR(MAX) ,@sqlSelect NVARCHAR(MAX) = '' ,@sqlFrom NVARCHAR(MAX) ='' CREATE TABLE #myTempTable (Id INT, DeptId INT, DeptName VARCHAR(100)) SET @sqlSelect ='INSERT INTO #myTempTable SELECT EMP.Id, EMP.DeptId, EMP.DeptName' SET @sqlFrom =' FROM dbo.EMPLOYEE AS EMP' IF (someCondition) BEGIN SET @sqlSelect = @sqlSelect +', EMP.Salary, EMP.Code' END SET @sql = @sqlSelect +@sqlFrom EXEC sp_executesql @sql

Why is SQL running the same query longer from another connection? Posted: 13 Apr 2013 11:01 AM PDT Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely? Dont jump to any conclusion just yet, let me explain what I mean... Here is the setup: A Windows 2008R2 (correction: 2003) application server executes a stored procedure that performs some action, (what its doing is really important at this point in time). This stored procedure is executed on the SQL server over a TCP/IP connection to the database server. The DB server is physicaly located right next to the application server, and they are connected to eachother via 1GB NICs to a 1GB Switch. The DB server is running SQL 2005 SP2 Enterprise Edition, and has 16GB of memory and several vLUNS striped across 48 15k drives in an HP-EVA FC connected SAN. From all indicators thus far, there are no I/O, Mem, or CPU constreints or limits being hit. Trace Falg 1118 is on and TempDB is split across 8 file on their own vLUN. Data, and TLogs also have their own vLUNS too. So, here is what I am seeing: Using SQLCMD on the database server, with SQLProfiler running from the same DB server, I can execute the stored procedure and I see that the execution starts immediatly, and compleats with a durration of about 2,100ms with an IO of about 1200. Using SQLCMD on the application server, with SQLProfiler running from the DB server, I can execute the same exact stored procedure, with the SAME exact parameters, and I see that the execution starts immediatly, and compleats with a durration of about 110,000ms with an IO of about 1200. The query results in 1 row, with 4 columns [INT, INT, VARCHAR(50), VARCHAR(100)] ASIDE:(I know the query is a train wreck, this is a regulated system and I cannot change it on a live prodution server, so please dont make any sugestions about doing so. The next version has been rewritten to be better.) From everything we can see, there is no reason that we should be seeing differances like this, but what is heppening is the .NET application that calls this query from the application server is timing out waiting for the responce. We have checked locking and blocking, WAIT states, Query plans, IO contention, CPU contention, MEM contention, NETWORK saturation/utilization, performed indexes rebuilds on all indexes, updates all stats, and a hand full of other items, but haven't come up with anything that points to why this is happening. Please ask more questions if you have any, make recomendations as you come up with them, and depending on the impact (remember this is a production environment) we will try them and respond back. -Thanks! -Chris

Can't connect to SQL Server Windows 7 Posted: 13 Apr 2013 04:01 AM PDT TITLE: Connect to Server Cannot connect to localhost. ADDITIONAL INFORMATION: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476 I keep getting this error when trying to connect to sql server. I also ran a repair and I got this error. Could this be the problem and do you have any idea how I can repair it. I have looked all over Google and tried quite a few solutions with no luck. I am Using SQL Server 2008 R2 Developer Edition

Remote connection to PostgresSQL in Windows 2008 Server is prompting the error below Posted: 13 Apr 2013 12:29 PM PDT The error: "Unable to read data from the transport connection. an existing connection was forcibly closed by the remote host." The conditions: 1) I have the following connection string: server = "CIRMS01" password = "password" user id = "postgres" database = "egov_euc" port = "5432" 2) pg_hba.conf has: host all all 150.46.1.9/32 md5 3) postgres.conf has: listen_addresses = * 4) client = Windows XP 32bit server = Windows 2008 Server 64bit I don't know what seems to be the problem why it is prompting the error message above and why it cannot connect. I googled it but there seems I cannot find a clear cut solution.

Are view references in a query properly called "derived tables" as such? Posted: 13 Apr 2013 08:01 PM PDT While answering a question on stackoverflow, I presented a definition of derived tables: A derived table is a complete query, inside of parentheses, that is used as if it were a real table. But a commenter objected: Though there are other kinds of derived tables besides those "inside of parentheses". ... [such as] Views and Table-Valued Functions ... . and further backed this up with: From the ISO/IEC 2003 Spec, section 4.3 of the Framework volume, page 13 of the August 2003 draft Spec: "An operation that references zero or more base tables and returns a table is called a query. The result of a query is called a derived table." Note that both Views and Table-Valued Functions return "the result of a query", which is a derived table. Microsoft (and to a lesser extent, Oracle) are notorious for mistakenly equating "derived table" and "sub-query" in their documentation, but Derived Tables do also include pre-defined queries like Views. So what's the real scoop, here? Am I to relegate what I thought of as derived tables to simply "FROM clause aliased inline subqueries" or are views not properly derived tables? Please note: I searched for quite a while online and could not find anything definitive. I don't have a copy of the said spec. Also, I think it's worth addressing something else. Let's say that views are properly called "derived tables". Does this make the reference to the view also a "derived table" or merely a reference? For an example with a CTE that should drive the point home: WITH SalesTotals AS ( SELECT O.CustomerID, SalesTotal = Sum(OrderTotal) FROM dbo.CustomerOrder O GROUP BY O.CustomerID ) SELECT C.Name, S.SalesTotal FROM dbo.Customer C INNER JOIN SalesTotals S ON C.CustomerID = S.CustomerID; The SalesTotals CTE introduced with WITH is a derived table. But is INNER JOIN SalesTotals also a derived table, or just a reference to a derived table? Does this query have two derived tables or one? If one, then by extension I think that a view may be a derived table, but referencing it may not have to be a derived table.

Indexing is not working Posted: 13 Apr 2013 07:01 PM PDT Following is the table and index information for which I have the queries at the end. Create Table: CREATE TABLE `food` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `page_no` int(11) DEFAULT NULL, `nid` int(11) DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL, `source` varchar(4) CHARACTER SET utf8 NOT NULL, `brand` varchar(500) CHARACTER SET utf8 DEFAULT NULL, `cuisine` varchar(100) CHARACTER SET utf8 DEFAULT NULL, `subcuisine` varchar(100) CHARACTER SET utf8 DEFAULT NULL, `type` varchar(255) CHARACTER SET utf8 DEFAULT NULL, `per_nr` float DEFAULT NULL, `category` varchar(100) CHARACTER SET utf8 DEFAULT NULL, `source_category` varchar(255) CHARACTER SET utf8 DEFAULT NULL, `source_parent_category` varchar(255) CHARACTER SET utf8 DEFAULT NULL, KEY `id_idx` (`id`), KEY `page_no_idx` (`page_no`), KEY `source_idx` (`source`), KEY `nid_idx` (`nid`), KEY `per_nr_idx` (`per_nr`), KEY `per_nr_source_category_nid_idx` (`per_nr`,`source_category`,`nid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 EXPLAIN 1 mysql > EXPLAIN SELECT food.nid, name, brand, cuisine, source_category, subcuisine, course, per_nr FROM food where category is not null and per_nr is not null and nid is not null; +----+-------------+-------+------+---------------------------------------------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------------------------------------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | food | ALL | nid_idx,per_nr_idx,per_nr_source_category_nid_idx | NULL | NULL | NULL | 532752 | Using where | +----+-------------+-------+------+---------------------------------------------------+------+---------+------+--------+-------------+ EXPLAIN 2 mysql > EXPLAIN SELECT name, nid, brand, cuisine, type, subcuisine, course, per_nr, source, source_category FROM food where per_nr>47 and source_category='Cereal Grains and Pasta' and food.nid!=205014 and food.nid is not null; +----+-------------+-------+------+---------------------------------------------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------------------------------------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | food | ALL | nid_idx,per_nr_idx,per_nr_source_category_nid_idx | NULL | NULL | NULL | 532752 | Using where | +----+-------------+-------+------+---------------------------------------------------+------+---------+------+--------+-------------+ 1 row in set (0.09 sec) Queries: Explain 1: As we can see the query contains column name which are indexed as well, but still explain show that the mysql was not able to identify key to make it faster. Explain 2: I have created combined index per_nr_source_category_nid_idx in the same order in which the fields are available in where clause but still the mysql couldn't identify the index. Although I have created indexes but still why system is not able to use it. This is my question in both the above queries. Detailed Index details mysql> SHOW INDEX FROM food; +-------+------------+--------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+--------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | food | 1 | id_idx | 1 | id | A | 822286 | NULL | NULL | | BTREE | | | | food | 1 | page_no_idx | 1 | page_no | A | 822286 | NULL | NULL | YES | BTREE | | | | food | 1 | source_idx | 1 | source | A | 27 | NULL | NULL | | BTREE | | | | food | 1 | nid_idx | 1 | nid | A | 822286 | NULL | NULL | YES | BTREE | | | | food | 1 | per_nr_idx | 1 | per_nr | A | 27 | NULL | NULL | YES | BTREE | | | | food | 1 | per_nr_source_category_nid_idx | 1 | per_nr | A | 27 | NULL | NULL | YES | BTREE | | | | food | 1 | per_nr_source_category_nid_idx | 2 | source_category | A | 12092 | NULL | NULL | YES | BTREE | | | | food | 1 | per_nr_source_category_nid_idx | 3 | nid | A | 822286 | NULL | NULL | YES | BTREE | | | +-------+------------+--------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 8 rows in set (4.35 sec)

How to increase fast backup and restore of 500GB database using mysqldump? Posted: 13 Apr 2013 04:01 PM PDT There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore using mysql dump taking quit a long time or days. max_allowed_packet=1G foreign_key_checks=0 auto_commit = off

How to check growth of database in mysql? Posted: 13 Apr 2013 03:01 PM PDT I want to know is there any method to check the growth of database on file For example Database A contains all tables in INNODB storage engine another database B contains all tables in MyISAM storage engine.

Is there a tool to check if my database is normalized to the third normal form? Posted: 13 Apr 2013 03:23 PM PDT I learned about normalization recently, and understand how important it is when implementing a new schema. How can I check if my database is 2NF or 3NF compliant ? Manual review is a sure option, but I'm looking for an automated tool here. I'm not looking for a point-and-click tool, more something that would highlight possible optimizations to make a table 3NF compliant. I guess it might use statistics based on good sample data and/or semantic analysis of columns names.

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