5
votes
2answers
1k views

Is there a way to hint to query optimizer to MySQL which constraints should be done first?

This is my current query: SELECT BusinessID as ID, 111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from ( SELECT * FROM ...
4
votes
3answers
582 views

sp_send_dbmail fails when mailing queries, but no error returned, query just hangs

I have an issue SQL Server 2005. sp_send_dbmail fails when mailing out with the result of a query (inline or attatchment). No error message is given / reported, the query just keeps executing but ...
1
vote
0answers
37 views

How does MySQL 5.1 use indexes?

I've read the docs for MySQL v5.1.x on how about the indexes are used, but I'm not sure I understood it properly. Suppose having the following MySQL table: CREATE TABLE IF NOT EXISTS ...
1
vote
1answer
415 views

How to match on a longer word using MySQL Full Text Search?

I have 2 tables : Company and category (category table has tags field, I wrote keywords for each category, I used comma(,) as delimiter) When I search something, it will first search in the company ...
2
votes
0answers
62 views

How to Remove Articles Based on User

I am using merge replication on SQL 2012. I have a very large database with a large number of tables. The subscribers are using netbooks. I have a filtering scheme that controls which tables end up ...
21
votes
9answers
54k views

PostgreSQL is running locally but I cannot connect. Why?

Recently updated my machine from Mac OS X Lion (10.7.4) to Mountain Lion (10.8) and I think it borked my PostgreSQL installation. It was installed originally via Homebrew. I'm not a DBA, but hoping ...
7
votes
3answers
2k views

SQL Server 2012 Standard Edition - multiple instances and memory utilization

If we have multiple instances of a SQL Server 2012 Standard Edition (which has a 64 GB memory limit) on one server which has 192 GBs of RAM, do both instances only have access to the first 64 GBs of ...
1
vote
2answers
1k views

How can I optimize this mySQL table that will need to hold 5+ million rows?

The table has about 12 rows. Indexes: idint(11), Unique, auto-increment, primary ATTACHMENTIDvarchar(14), Unique MLSNUMBERbigint(20) POSITIONint(10) FILETYPEvarchar(32) I'm doing an extended ...
2
votes
1answer
689 views

Cube Designer Aggregation Wizard Optimization Zero Percent Every Time

I am pretty new to SSAS. I have a cube (SSAS 2008 R2) that once had aggregations that has been created using the Aggregation Design wizard. When changing schema a few dimensions, i encountered an ...
3
votes
2answers
781 views

Where should I fix the deadlock?

We have an application database on Server A that is replicated to Server B using push transactional replication. Reporting is done on the replicated copy on Server B. We are having issues where the ...
2
votes
3answers
249 views

What is running right now?

I know I can use "show processlist" to see what is running when I'm in the office. How can I see what is running when I'm not there. Something is running on my server at 3am every morning that is ...
2
votes
2answers
276 views

Proper use of NULL, and utilizing CHECK constraints for business logic vs stored procedures

This question regards the proper use of NULL and utilizing CHECK constraints for business logic vs stored procedures. I have the following tables setup. I normalized the tables to avoid using ...
-1
votes
1answer
40 views

Ordering of rows with different kinds of objects

I have a table of real estate objects. The objects are shown in three lists (sell, rent long, rent short), each object in exactly one list (defined by an ENUM field). I want to add field seq INT to ...
1
vote
1answer
106 views

MySQL GRANT control

How can I write a script to automate MySQL database grants in a very easy way? I have a config file that contains the grant changes I want to make. I want the script to read the config file, and ...
0
votes
1answer
66 views

How to append some letters or numbers to already existing data

I would like to append some data to already existing data, something like 720967537 to 255720967537. All the data in my column is in that format. Is there any way I can do that to all of the records ...
21
votes
5answers
19k views

What is the most efficient way to get the minimum of multiple columns on SQL Server 2005?

I'm in a situation where I want to get the minimum value from of 6 columns. I've found three ways so far to accomplish this, but I have concerns with the performance of these methods and would like ...
12
votes
3answers
2k views

Implications of using a Unique NonClustered Index with Covering Columns instead of a Primary Key

We have a large table [MyTable] which currently has both a Primary Key, and a Unique Non Clustered Index on the same column ([KeyColumn]). The U NC index also has additional covering columns. Having ...
1
vote
1answer
1k views

SQL Server Log: The database 'master4IDR' is marked RESTORING

Why SQL Server log showing following message. I am dead sure, there is no such db on our production server naming "master4IDR" {The database 'master4IDR' is marked RESTORING and is in a state that ...
2
votes
0answers
110 views

Are there any best practices before moving SQL Server to another node (controlled failover)?

Let's say there are multiple jobs running on the SQL Server - we have index maintenance jobs, backups, replication, etc. We would like to perform a switchover using Failover Cluster Manager. Are there ...
1
vote
1answer
4k views

Restoring an SQL Server 2012 MDF to SQL Server 2008 [duplicate]

Possible Duplicate: Restore SQL Server 2012 backup to a SQL Server 2008 database? I have created a database on SQL server 2012 with compatibility mode for SQL server 2008 (100). When I do a ...
1
vote
1answer
259 views

How to create a new DB with same data from another DB in oracle 10g

I trying to copy entire data from old database to newly created database in oracle 10g. please any one tell me the steps to do the process.
3
votes
2answers
655 views

Pipe psql timing to file

I am currently using \timing on to do some simple performance testing in Postgres. I would like to run many queries and pipe the timing results to a file. However, all of the options I have tried ...
2
votes
1answer
125 views

why/how does the number of matched columns influences the way of excecuting a query

Imagine the following situation: Table A uses MyISAM and contains 4 fields (text) with a combined FULLTEXT-index. FULLTEXT fulltext1 | fulltext2 | fulltext3 | fulltext4 Table B uses InnoDB and ...
4
votes
1answer
463 views

Ideal environment and cost for running a medium size MongoDb server

I know some people will say this question does not belong here but i didnt find a better place on the internet to ask this. Please migrate to an appropriate place if you think it should be. I am using ...
1
vote
1answer
641 views

Where is sp_MSrepl_getpublisherinfo in SQL Server?

In learning about replication in SQL Server and having problems creating a subscription, I looked at the definition for sp_addsubscription. It calls sys.sp_MSrepl_getpublisherinfo, which I cannot find ...
4
votes
1answer
3k views

Function/procedure to use dblink to fetch remote data and insert to multiple local tables

Am trying to fetch data from remote db and insert into three local tables. Currently I have a query which is successfully getting the data into one table. But now I was thinking of creating a ...
2
votes
3answers
2k views

Transactional Replication snapshot locking Publisher db from beginning

I have recently upgraded my remote distributor to 2008r2. I am getting complete system locks when attempting to generate a new snapshot in the day when we have added an article to the publication (we ...
6
votes
2answers
3k views

Scaling Percona datacenters: setup and replication

Since our startup has taken off nicely we are now meeting with some of those issues that you always assumed would NEVER effect you. We have already scaled alot of our application stack: we offloaded ...
0
votes
2answers
1k views

Why won't Oracle alter the size of a column that is used for sub partitioning?

I am trying to resize a varchar2 column, when I get an ORA-14265 error: ORA-14265: data type or length of a table subpartitioning column may not be changed With Oracle providing the unhelpful: ...
5
votes
2answers
195 views

DELETE LOW_PRIORITY row visibility

I have a large MyISAM table on MySQL 5.5(Windows XP x64) on which I will have to run DELETE LOW_PRIORITY queries. Does DELETE LOW_PRIORITY make the rows invisible to SELECT statements immediately and ...
1
vote
2answers
102 views

Basic PARTITION question in MySQL

Say I have an application using MySQL and at some point decide that implementing Partitions on certain tables might improve performance. Would I need to change the applications select statements ...
0
votes
1answer
2k views

Oracle big datafile restore fails using Netbackup (ORA-19511, ORA-27190)

I am restoring datafiles about 1000GB in size. I am using Netbackup as a backup server. After 2 hours reading Netbackup fails with the following error: 7/25/2012 3:27:50 PM - Error bpbrm(pid=1176) ...
6
votes
3answers
3k views

Is MariaDB a suitable drop in replacement for MySQL on a standard LAMP stack?

I've been wondering lately what (if any) are the improvements available in MariaDB over 'conventional' MySQL? I understand that where platform interoperability and/or backwards compatibility may be ...
1
vote
1answer
716 views

Mysql Join to get multiple row value from

I am new to my Mysql and need help regarding join I have a table called employee and this table contain some foreign key value like phone, address, sex. Table phone and address have 4 rows in it, I ...
2
votes
2answers
822 views

Mysql database replication on different vlan/subnet/another site

Saw the posts for "MySQL high availability, failover and replication with Latency" Is it possible to work it in a DR site when its not in the same vlan?
4
votes
2answers
2k views

Dynamic access to record column in plpgsql function

How can I address a column from a record in a plpgsql function dynamically? In the following snippet I have access to a variable entity.colname that contains the column that should be checked in the ...
2
votes
1answer
75 views

Need a solution on dataset as given below

Data Set: col1,col2,col3,col4.col5 (A,b,c,d,e) (A,z,p,q,r) (F,s,t,y,v) (F,w,x,y,z) (F,p,x,r,t) (G,m,n,o,p) (G,m,i,q,s) Actually I need result set that has all the columns and only single row for ...
1
vote
1answer
209 views

Follow up to certificate sharing question

Follow up to the post Sharing certificates encryped by password between DBs and instances I created a certificate with a password CREATE CERTIFICATE testcert ENCRYPTION BY PASSWORD = 'test123' ...
1
vote
1answer
16k views

Error Code: 1205 Lock wait timeout exceeded in MySQL

I have set InnoDB lock wait time-out to 120 seconds. When I run the query below for first time, it works fine. However, the second time, the same query with a different value gives me: Error ...
1
vote
1answer
4k views

Drop multiple databases in one script

I am trying to figure out how to drop multiple databases on PostgreSQL in one sql script. I preferably need it to work both when executed in pgAdmin console, and when used in psql command line tool. ...
3
votes
6answers
8k views

Taking backup of all the databases in SQL Server

I have a Microsoft SQL Server 2005 DB server. In the DB server, I have around 250 user databases. I have to take a back up of all these databases. Since, manually taking backup consumes lot of time, I ...
1
vote
2answers
507 views

How to DELETE duplicate entries on a field

I have a huge table that has 1,990,546 users with numerous data. Sometimes I get email lists and dump them into this table also. I want to delete users with duplicate email addresses. However, I ...
2
votes
1answer
683 views

Database Design - Creating Multiple databases to avoid the headache of limit on table size

I need to design database to store information contained in millions of log files generated by devices on trial on network. My first approach was storing all the information in tables contained in a ...
2
votes
2answers
1k views

Bulk grant execute permissions for scalar functions & stored procedures

We have a situation where we have restored a large number (50+) of MSSQL2008 databases from backup and most if not all of them require that a user is granted EXECUTE permissions on scalar functions ...
1
vote
2answers
4k views

Why does dbms_scheduler max_run_duration not raise event JOB_OVER_MAX_DUR?

Starting from this question and this example I've tried to create an event based dbms_scheduler job, which waits for event JOB_OVER_MAX_DUR raised from jobs, which exceeded their max_run_duration. ...
1
vote
1answer
170 views

Help me convert SQL statement to MySQL

I have an application called Moodle version 1.9 and was migrated from Windows platform and Microsoft SQL Server to Linux and MySQL. Now, when I click save changes the SQL query statement from Custom ...
6
votes
1answer
4k views

Why is DELETE so much slower than SELECT, then DELETE by id?

I have a fairly busy InnoDB table (200,000 rows, I guess something like tens of queries per second). Due to a bug I got 14 rows with (the same) invalid email addresses in them and wanted to delete ...
0
votes
1answer
2k views

mysql closing tables taking double the time of process

I have a delete query that is taking a long time. When I check in processlist that status is showing as closing table. It is taking a long time to close tables. ex: I run a query and the total time ...
1
vote
0answers
464 views

Why not use SQL_Latin1_General_CI_AS for a global system?

What would be some really good reasons to not use the SQL_Latin1_General_CI_AS collation on a system which handles English, German, Japanese, and Chinese data? I'm having a really hard time finding ...
1
vote
3answers
5k views

When is Seconds_Behind_Master too big?

My mysql_slave got stuck on an error and built up to being 200,000+ Seconds_Behind_Master. Should I rebuild it and start from scratch, or let it catch up by itself?

15 30 50 per page