1
vote
4answers
4k views

How to change the data directory on Postgresql 9.1 on Ubuntu

I am having a problem changing the data directory on postgresql 9.1 on ubuntu 13.04: I first did a: sudo pg_dropcluster --stop 9.1 main and then attempted to create a new one in the desired path: ...
1
vote
2answers
324 views

getting error when i use merge statement

I am using sql 2008 r2. when i use merge statement i am gettig error when i execute following code: merge products as trgt using updatedproducts as src on (trgt.productid = src.productid) ...
3
votes
2answers
1k views

SHOW TABLE STATUS very slow on InnoDB

Recently we've been migrating from MyISAM to InnoDB and I understand that MyISAM uses meta information against each table to track information and such - however, the database is about 1.8gb with 1+ ...
1
vote
0answers
497 views

sql server 2008 execution plan different on two production servers [closed]

We have two servers running SQL server 2008 R2, with identical databases, where we implemented a datawarehousing solution. The cube processing operation was taking a VERY long time in one of the ...
0
votes
0answers
69 views

Linked server performace issue?

I have created a linked server in sql server 2008 R2 Enterprise Edition for mysql i.e. MySQL database is accessible under sql server environment. I have a created a trigger in sql server such that ...
1
vote
1answer
303 views

MySQL users corrupt

I have a strange situation here: From time to time I cannot log in with any of my mysql users. I even cannot make a mysql dump. So I started searching in mysql files and I found that users.MYD and ...
1
vote
1answer
208 views

Syncronize mysql databases between local and hosted servers automatically

We have many website with Development , Staging and Production Server. we have many developers for many projects, we need a solution to synchronize the database with developer database with staging ...
0
votes
2answers
632 views

VARCHAR, InnoDB MySQL, is inline allocated space variable or fixed?

For a log table, I have a custom message column. It will never be updated. It will be searched with LIKE conditions. It will be capped at 1KB. TEXT fields are slower than VARCHAR fields for ...
-2
votes
2answers
1k views

How can I search for a name in two columns in MySQL? [closed]

I want to search a name 'Hamed Haddadi'. 'Hamed' is first name and 'Haddadi' is the last name of the user. When I search individually in the first name and last name columns it shows a record. My ...
1
vote
1answer
97 views

Approach for a 2,000 table migration

What might be the best approach when converting or migrating a database containing over 2,000 tables? Where would you begin to tackle such an issue? Steps to take in the first weeks of designing? ...
2
votes
1answer
211 views

In MySQL, does the order of the columns in a WHERE clause affect query performance,why? [closed]

I have a query that doesn't use any indexes: SELECT 32, guid, 1, 1, 1, 0, 5 FROM test WHERE level >= 20 AND ( ( fun_GetIndexValue(data, 354) ...
1
vote
0answers
1k views

SSIS Merge Join Trouble: 3 tables into 1

So I am having trouble merging three tables into one. All sources are DB, and I am using the MERGE JOIN function. When I join my first two tables, using standard left inner join, it returns the exact ...
2
votes
1answer
36 views

Which MySQL directories to back up using FTP

My server has been hacked and I only have FTP access to recover my data before reinstalling the OS. Which/where are the directories that needs to be backed up? Will MySQL be recovered back to its ...
1
vote
1answer
772 views

Why does pgAdmin3 set OIDS=FALSE and owner to postgres by default?

Why does pgAdmin3 automatically set the table owner to postgres when its created and set OIDS=FALSE? I am new to PostgreSQL and want to know the rational behind this default behavior. -- Table: ...
5
votes
2answers
11k views

SQL Server AFTER INSERT trigger

I want this trigger to be fired after an insert is made with the text married on the marital_status column, this is what I have so far ALTER TRIGGER [dbo].[marriage] ON ...
2
votes
3answers
575 views

SQL server disk write queue dramatically grows sometimes

I have SQL Server 2008 and a single database with full recovery model. Usually, the queue length is less than 1. But sometimes it grows up to several thousands (3000!!) for a few seconds. At this time ...
4
votes
1answer
1k views

What are the disadvantages if indexes are added on all columns?

I know, that it is not a good design to set too many indexes, but just for understanding the theory: What exactly are the disadvantages if you add an index on all columns in a table in MySQL? Are ...
0
votes
1answer
316 views

MySQL: replicating to a different table engine type

According to the replication documentation from MySQL, it is possible to set up replication from InnoDB source tables to MyISAM destination tables. Unfortunately, the documentation has little to say ...
1
vote
1answer
1k views

Databases list not showing up?

This morning when I logged into SQL Server Management Studio (v 10.0.16) I clicked on the databases node and saw all by db's (they are hosted remotely) as I have done for the last 3 years. This ...
0
votes
1answer
98 views

speeding up a query on MySql [closed]

I have a table with more than 10 million rows and 10 fields(columns). There is an index on field_1 and I am running the following query. create table t2 select field_1, sum(ifnull(field_2,0)) ...
1
vote
2answers
4k views

To find a version for your pc check with the software publisher install mongodb

I want to install MongoDb on windows 8 but when i try to run C:\mongodb\bin\mongod.exeiget this error: To find a version for your pc check with the software publisher.
1
vote
2answers
107 views

database design- diagrams

I'm trying to derive an Entity Relation diagram from a class diagram. In the class diagram, I have a class Player with one to many relationship to another class Payment. In Payment there is no ...
2
votes
1answer
58 views

Do I need to reenter old data after adding an index to a table?

I want to add index to my tables. Some of my tables already have couple thousand rows. Do I need to reenter my stored data after adding index to columns (to make them aware of index/so the indexing ...
7
votes
5answers
1k views

Database design: Dividing multiple identical tables, good or bad?

I am very new at SQL and databases in general. I only use them for the occasional homework so I haven't even tried to master them. I have seats at a theater, the seats are divided into 4 main areas ...
1
vote
1answer
133 views

Can a production Oracle DB server also have a dataguard active standby for a different production database?

If you have a production oracle database (PRIM1) protected using Oracle Dataguard to a standby (STBY1) physical server in a different data centre, the standby server needs to be a similar ...
1
vote
1answer
86 views

Oracle error handling strategy [duplicate]

I am developing a project using oracle. I am writing functions and stored procedures to handle CRUD statements. My question is in addition to oracle check constraints do I have to check error ...
0
votes
1answer
198 views

Which privileges for MySQL extended status?

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?
3
votes
1answer
751 views

Db design for historical option data (~200GB)

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 ...
1
vote
1answer
80 views

Game database- many to many relationship

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 ...
1
vote
3answers
659 views

do MYSQL views occupy physical space?

do MYSQL views occupy physical space? Or in other words, is a MYSQL database limited to a certain number of views?
0
votes
1answer
180 views

Object name 'Clean Up History' in use when trying to complete the maintenance plan wizard in SQL Server 2008 R2

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 ...
0
votes
1answer
208 views

Mysql suddenly stopped after is said to be crashed

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 ...
2
votes
0answers
432 views

Cannot connect to SQL Server 2008 after updating the service pack

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, ...
0
votes
2answers
193 views

which sql server edition is good for practice [closed]

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 ...
0
votes
0answers
218 views

Use cases for NoSQL databases: Time series

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 ...
0
votes
4answers
1k views

Space at the end of string

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 ...
2
votes
3answers
1k views

Can I make sure two columns dont have the same value

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 ...
0
votes
1answer
543 views

admin user can not login to other databases after upgrading to mongodb 2.4

I had a mongodb 2.2 database with auth enabled. but after upgrading to 2.4 my admin user can not login to any database other than admin database. I removed it from system.users then created another ...
3
votes
2answers
1k views

How can I improve this nested MySQL query to avoid redundant WHERE statement?

I am trying to improve on a nested MySQL query, using the following table structure (omitting a few dozen not relevant to my question): tbl_users: ID | email (all subscribers to our email list) ...
0
votes
1answer
27 views

mysql error creating table

I was creating a DB with mySQL, and this error appeared: Can't create table 'projectefinal.apuntats' (errno: 150) I've got this: CREATE DATABASE IF NOT EXISTS projectefinal CREATE TABLE IF ...
1
vote
3answers
124 views

Correct way to perform an UPDATE on multiple tables?

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 ...
1
vote
1answer
304 views

MySQL: OPTIMIZE after TRUNCATE?

Using MySQL (either InnoDB or MyISAM tables--I have both), if I TRUNCATE a table and then LOAD DATA INFILE, should I issue an OPTIMIZE TABLE call after the LOAD? It's unclear to me if this is a ...
1
vote
1answer
469 views

Service Broker - Communication not happening between servers despite ssbdiagnose saying all is well

I am setting up Service Broker communication between 2 servers, A and B we will call them. I used this article as an example: ...
6
votes
1answer
859 views

How do I optimize large table so queries that target only recent data perform optimaly?

So I have this table that is ever growing. Most queries are targeting just recent data, say one month old. I suppose this is common problem but I have no idea how it can be solved. I am open to ...
1
vote
1answer
85 views

extra steps after changing storage engine and adding index

Someone told me to look into his website for quick optimization; I'm a programmer and i don't have much experience optimizing databases. I have a php/MySQL site uses the MyISAM storage engine. It ...
1
vote
1answer
237 views

Does WITH SCHEMABINDING on a multi-statement TVF improve cardinality estimates?

Based on http://blogs.msdn.com/b/psssql/archive/2010/10/28/query-performance-and-multi-statement-table-valued-functions.aspx and other articles, SQL Server assumes that a multi-line table valued ...
3
votes
3answers
459 views

Which system databases should I checkdb and defrag indices?

I have SQL Server Agent (2005) jobs that periodically perform CHECKDB and a defrag (ALTER INDEX REORGANIZE/REBUILD) of any index that is highly fragmented. These are typical maintenance best ...
1
vote
2answers
2k views

Recursive query in mysql

I am using GetAncestry function found at this post, The problem is when I try to use it with select query Mysql hang, I am not sure why it is happens my Mysql version is "5.5.16" any help appriciable ...
1
vote
1answer
49 views

Best way to perform backups with filegroups and then restore those backups

Scenario: Database consists of these file groups: PRIMARY xxTEST2 xxTEST3 xxTEST4 Database consists of the following files in those file groups: xxTEST_RESTORE - Rows Data - PRIMARY - ...
14
votes
11answers
1k views

Why are constraints applied in the database rather than the code?

Why are constraint applied in Database? Will it not be more flexible to be in code? I'm reading a beginners book on implementing databases, so this is a beginner's question! Let's say I design a ...

15 30 50 per page