7
votes
3answers
732 views

Issue with composite type in an UPSERT function

I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it. CREATE OR REPLACE FUNCTION netcen.fun_test(myobj ...
3
votes
2answers
781 views

How to forcibly create/modify MySQL user grants, without restarting the database service

I am familiar with how to start MySQL server with the "--init-file" option, or the "--skip-grant-tables" options. However I have a nagios based mysql monitor, which needs to query a table, and ...
1
vote
1answer
93 views

What is a proper way to handle a “sequence number” like field?

The fields of a trimmed-down version of a database I'm working with looks like this: Model Number, Model Name, Customer, Date, SeqNum The customer can purchase the exact same item on the same date ...
3
votes
2answers
133 views

SQL server 2000 transactions with the same time

I have a BIG problem here. I'm creating a program that can be looping if the connection to the server is broken. But when the program is running for a few months, I found a bug on SQL server 2000. I ...
2
votes
2answers
447 views

INSERT succeeds but all inserted values become NULL

I tired to execute an insert directly from phpMyAdmin as follows: INSERT INTO oracle.PLAYLIST_MUSIC ( TID, ID, STATUS, CREATED_BY, ...
1
vote
1answer
204 views

Ensuring Only Fresh Data Gets Accessed on SQL Server 2008/2012 Replication For Reads

I am designing a scale out solution for a globally dispersed company that has data centers on all continents. Over 10,000 servers report to a SQL Server cluster in Los Angeles, and also query it to ...
1
vote
3answers
10k views

Designing a Products database: One Products table or many separated by vendor?

In order to prevent the unwieldiness of managing product data spread across a number of excel spreadsheets, I have set up a Products database. My data is in spreadsheets because it was (somewhat) ...
1
vote
1answer
60 views

Does implicit casting negatively affect index usage?

I have a query similar to the following: SELECT id FROM MyTable WHERE eventCount > eventCutoff * eventFactor eventCount and eventCutoff are defined as a smallint, and eventFactor as a float. I ...
3
votes
2answers
3k views

SQL Server Agent - Run Query at the end of each month with date variable

I am running SQL Server 2008 R2, and I need to create a new job that will basically run a query at the start of each month (at the 1st of each month at 01:00 AM). Here is the query: INSERT INTO ...
2
votes
1answer
828 views

SQL Server manage indexes

Is it possible to automate the managing of indexes on my database? I'm looking to see if there are any scripts or programs that can automate this process. Would it be possible to use stats generated ...
6
votes
1answer
2k views

SSIS package with BULK data load

I analyzed a C# program over the weekend for a customer that basically does the following: Truncate Table Fetch Data (source on these is either remote database or local database where job runs) ...
1
vote
1answer
667 views

Replication and linked server security

I have three servers: P1, A1, and R1. P1 and A1 are production oltp servers. On each there are two databases that are being replicated down to R1 for olap (mostly SSRS reports). I am working on ...
44
votes
2answers
42k views

Granting a user account permission to create databases in PostgreSQL

How do I give a user account in PostgreSQL the ability to create and drop databases? Is there a way to do this with GRANT?
10
votes
1answer
992 views

Why don't SQL Server queries use more than 7MB/sec of disk I/O

I have an SSD that, using IOmeter test, shows performance over 200MB/s. However when I run any SQL query from local machine the windows resource monitor never shows disk IO above 7MB/sec. This holds ...
1
vote
0answers
408 views

PeopleSoft or Oracle App Designer Tool Alternative [closed]

We are using PeopleSoft app designer tool to manage and create projects, database objects - tables, columns, views etc in Oracle database. This tool has limitation that we cannot create tables/columns ...
4
votes
1answer
504 views

Is there a standard way to replace codes with values from a lookup table for reporting or analytics?

Suppose I have a "main" table that was probably heavily normalized and consists largely of columns that merely contain codes that are lookups into other tables (they are probably foreign keys but feel ...
4
votes
2answers
688 views

Can I manually reset the “internal status flag for a full database backup”?

We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day. This worked last weekend, however this weekend the differential ...
1
vote
0answers
147 views

TMD auto create

I used mysql as part of a project and found it to be a very practical tool. I have a question about mysql in regards to how it creates temporary table types TMD in /var/lib/mysql/. This partition ...
-1
votes
1answer
2k views

In the domain of SQL Server backup, what is a backup set, backup device, backup media, backup media set, backup media family

I'm not a native English speaker and I've read the descriptions in BOL but still can't understand what exactly are they. Can anyone explain in an easy understand way? Thanks.
1
vote
1answer
322 views

MySQL Dump all rountines except one

We want to export a large amount of tables and routines and we've do this often. Each database, table and routine should be dumped with one exception: a procedure with a specific name. So I was ...
1
vote
2answers
7k views

Getting repeated value in a group_concat only if ids are different

I have two tables, first the table Product: id|category_id --+----------- 1 | 12345 2 | 12345 3 | 12465 And then a table activity: id|prod_id|activity_type |description ...
3
votes
1answer
3k views

Setting up a Postgresql Partition with foreign key

I have a Ubuntu 12.04 Server with PostgreSQL 9.1 installed. I am trying to setup Partitioning on a existing Postgres DB(say 'xyz') which has very huge tables and some have foreign keys set on them. ...
1
vote
2answers
95 views

Change the Existing Topology of a MySQL Replication Setup

I want to be able to edit the topology of an existing replication, is that possible? For example if I have a star topology, I want to edit it to make it a ring one. How can I do that? I'm thinking ...
1
vote
2answers
2k views

Factoring out subselect (with clause) ends in ORA-00904

I am trying fo factor a repetitive subquery with with but getting ORA-00904 over and over again. This is my base query which works quite well: select tka.id, tka.name, txt.text as langname, ...
3
votes
1answer
5k views

Why ORA-00932: Inconsistent Types error?

Sorry for the meaningless title, but I couldn't think up a better one. I'm using Oracle 11g r2. The following query raise ORA-00932 error: SELECT BBB.FIELD1 FROM TABLE0 AAA JOIN (SELECT * FROM ...
2
votes
1answer
3k views

Archiving fga_log$: sys.dbms_audit_mgmt.clean_audit_trail doesn't delete anything

I am having a problem to archive and then delete portions of the fga_log$ table. Here's what a script basically does insert into fga_log_archive select ... from sys.fga_log$; begin ...
2
votes
1answer
289 views

How to design functionality to comment about a user joining a Facebook group?

User A joined the group DBA The table responsible for users x groups is "Following" table. user_id group_id 1 1 1 2 2 1 And ...
1
vote
1answer
887 views

What to do with alerted variables phpMyAdmin for MySQL databases?

I've noticed the following alerted variables in phpMyAdmin Variable Value Description Aborted clients 5 The number of connections that were aborted because the client died without closing the ...
0
votes
1answer
119 views

MS SQL and MYSQL [closed]

I was going through some theoretical concepts about SQL based databases. One thing that I found interesting was that MySql and MS SQL are Relational-SQL-Database Management-Systems. Since they both ...
1
vote
4answers
6k views

Cannot remove Mirroring / nor restore or delete database:Error 3456 (SQL Server 2012)

I have a database that was a mirrored partner and went into suspended state. (Mirror Server was shut down). I took the principal database out of mirroring using SSMS. But now I am unable to restore ...
0
votes
0answers
4k views

0x84BB0001 Error when installing SQL Server 2012. Could not create a handshake [closed]

I have hopelessly attempted to install SQL server 2012 onto my 64 bit Windows 8 machine. The database engine always fails to install with error 0x84BB0001. Native reporting also fails, I reckon from ...
1
vote
2answers
3k views

In MySQL Multiple instance, default instance is running, second instance is not

I was made a setup to create multiple instances on the ubuntu machine. When I start mysql instances mysqld1 is running but mysqld2 is not running. root@ubuntu:/var/lib# mysqld_multi report Reporting ...
2
votes
0answers
274 views

tempdb objects creator wanted, bufferpool issue caused by tempdb objects

I have a number of tables in tempdb named like #A0030B77 #A01D908C #A06D1FCB #A0F72FB0 #A1614404 #A1EB53E9 #A205D8FE #A255683D How (if) can I get spid of the process which created them? These ...
2
votes
1answer
8k views

Rename interval partitioning system generated partition names

I have an existing table which I partitioned (interval partitioning on a daily basis) using dbms_redefnition. PARTITION BY RANGE ("TIME_ID") INTERVAL (NUMTODSINTERVAL(1,'DAY')) (PARTITION "p1_1" ...
0
votes
2answers
3k views

Backup plan for MySQL NDB cluster databse not innoDB

I have a Database which will grow more than 250GB all data is in NDB engine(2 datanodes) and no other mysql engine used for data store. What are the best approaches or plans for MySQL Cluster NDB ...
2
votes
3answers
6k views

Single DB shared across multiple servers

I've planned to host many servers (50+) each will have more than 20K users stay connected. There is lot of shared data amongst users (e.g. a user needs to refer data prepared by another user). If I ...
4
votes
2answers
9k views

query_cache_type = 0 vs query_cache_type = 1

Recently, I moved from standard MySQL to Percona, and used the Percona Wizard to generate my.cnf. However, I can see that, by default, the generated settings for my.cnf use query_cache_type = 0. ...
3
votes
1answer
527 views

Is it possible to check all constraints in a MySQL database?

MySQL offers the ability to temporarily disable the foreign key checks, thus allowing inconsistent data to enter the database. Is it possible to check an entire MySQL database, table by table, row by ...
3
votes
1answer
515 views

Setting up simple question & answer database

I am having trouble thinking this (simple) table relation through. I am setting up a quick SQL DB to populate labels and textboxes dynamically on screens. This is just to prove we can do it, so it ...
7
votes
1answer
877 views

Deadlock graph with a lock on an index on a seemingly unrelated table

I have a deadlock graph from a deadlock where one process is doing a SELECT and one is doing an UPDATE. This seems like the classic case where the SELECT gets a NCI lock to perform a join and then a ...
9
votes
2answers
8k views

Multicolumn index and performance

I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries. The scenario: PostgreSQL 8.4, table with about one ...
0
votes
2answers
1k views

Question regarding 2NF partial key dependency

If the functional depencies are AB-->CD BC-->D IS the relation still in 2NF?,I mean since AB is the key and the 2nd BC,out of which B is part of the key,is the relation still in 2NF??
4
votes
2answers
719 views

Insert with 0 Writes?

I've been looking at some SQL Profiler traces recently, and I see something I don't understand. I'm running simple INSERT commands: INSERT INTO Foo (ForeignID, FileExtension, MimeType) VALUES (1, ...
0
votes
2answers
2k views

Indexing a longtext field

I would like to run an index on a longtext field using: CREATE INDEX post_meta ON wp_postmeta (meta_value(8)); There are currently ~1 million records. Questions: Will creating this index affect ...
2
votes
1answer
4k views

sql agent job hangs without any errors or warnings [closed]

Problem: we have a SQL Agent job which runs everyday in the morning at 4:00 am. This job hangs sometimes without providing any errors or warnings. It just hangs. BUT when i stop the job and run again ...
0
votes
1answer
374 views

Having trouble with a T-SQL scalar variable during a create table [closed]

I am still a beginner, but I am having trouble with a T-SQL scalar variable during a create table. Can anyone tell me what is wrong with this script? -- T-SQL MSSQL SET NOCOUNT ON DECLARE @tt = ...
7
votes
2answers
919 views

SQL Server Transaction Logs in the Cloud

It is a common practice to place the transaction logs (*.ldf) on a separate physical disk system than database files (*.mdf, *.ndf). In a virtual machine cloud environment, is it okay to put the ...
1
vote
1answer
1k views

MySqli - select from db based on time difference with CURTIME() or SUBTIME()

I have a db with some customers data. There I store their last login time as datetime. I want to run a cron php script every min which will query mysqli for their last login datetime and if that was ...
0
votes
1answer
265 views

MySQL: select a row if that row is equal for every user

In my database I have 5 fix users, lets say user1, ..., user5. Furthermore I have a table month(name, finished, month, year) where name is VARCHAR (the name of one of the five users) and the ...
1
vote
3answers
295 views

How can I best combine 3 queries into 1?

I need the following queries combined into one query that returns the following from all results from the first query you see below (Right now I load the first query into an array in php then run two ...

15 30 50 per page