texts: Good reason to convert LONGTEXT column to smaller width if field is smaller?

texts:
We have a database that uses a LONGTEXT type for a column that does not need that much space. The maximum row size currently has about 6500 characters and that's with a few million inserts so it's very unlikely we will ever need more space.  Is it worthwhile to try to reduce the allocated size of this column?  Will we see performance improvements?

We are using MySQL 5.6 with InnoDB.

Thanks!


texts: Is it possible to prevent data loss with Postgres WAL archiving?

texts:
As I understand from the docs, a WAL segment is archived when the segment file reaches some size or due to archive_timeout.

So if the database crashes, all the data from the incomplete, currently active WAL segment would be lost (assuming we don't have any access to that incomplete segment in the Postgres directory).

Do I get it right and if so, is there a way to avoid this data loss?


texts: mysql 5.6 gtid replication: is log_slave_updates required?

texts:
I've read the docs saying log_slave_updates is required.  It makes sense for a general promotion situation.

But is that really still required for something you're intending to be a read only slave that will never get promoted?


texts: What grants should I give to allow `COPY ... TO STDOUT`?

texts:
Context
I want to set up a logical replication between two postgresql instances.
I created a replication user on the master instance :
CREATE ROLE replication WITH REPLICATION PASSWORD '$REPLICATIONPASSWORD' LOGIN;

Then a publication / subscription :
-- on the master instance :
CREATE PUBLICATION mypub FOR TABLES table1, table2, ...

-- on the slave instance :
CREATE SUBSCRIPTION mysub CONNECTION 'dbname=mydb host=localhost port=1234 user=replication password=...' PUBLICATION mypub;

Issue
In postgres' logs, I see error messages indicating that the replication user can't copy the tables :
-- on the slave side : message repeated for each table
ERROR:  could not start initial contents copy for table "public.table1": ERROR:  permission denied for table table1

-- on the master side : message repeated for each table
ERROR:  permission denied for table table1
STATEMENT:  COPY public.table1 TO STDOUT

Connecting with psql to the master server with the replication user I do see that COPY table1 TO STDOUT is rejected.
Question
What other privileges should I add to the replication user to allow COPY ... TO STDOUT ?


texts: move mySQL 5.6/InnoDB tables to a different physical location

texts:
I was wondering if this is possible with mysql 5.6.

I have a Database with several tables separated by Month (eg TonsOfRows_201411). I don't need access over the whole data so I decided to separate tables instead of partitioning.

The current month is always used for importing/queries (we are talking some 100m rows per month) so I'd like to put the current table on a SSD and then archive it to a slower HDD afterwards (I still need to run queries on it, r/o only)

In postgresql I create a tablespace with different physical location and can change the tablespace later on, so I can move around the table.

I havent found a 'datadir' option in the mysql tablespaces, so not sure if I can solve it via mySQL tablespaces.

I have found a 'datadir' for InnoDB, the problem is alter table doesn't support a change of the datadir (datadir is ignored for alter table), so not sure how to change it.

So I was wondering if its possible to change the datadir of a mysql table after you have created it (with mySQL commands)?

One solution would be symbolic links, but according to the Docu this is not supported for innoDB (nevertheless it is working, but i really prefer a SQL only solution here)

Your help is appreciated, I am sure I am missing something here.


texts: How to enable "only" drop partition DDL for Oracle Golden Gate?

texts:
I'm struggling with how to do this, and hoping somebody could assist with some ideas.
Oracle Golden Gate - general question. Version shouldn't be specific, however, I am currently running on:
Golden Gate: 12.2.0.1.170919
Oracle DB: 12.1.0.2.0
Using Golden Gate, Integrated Extract (and Replicat).
I have setup a test table using Interval partitioning (by month).
I have setup a GG flow to capture the data/rows for this table.
This works perfectly, no issues.
I then try to setup/enable DDL replication for this table.
I add the following to the extract .prm file:
DDL INCLUDE OBJNAME myschema.*

and that works perfectly. It captures ALL ddl ..
ALTER TABLE
TRUNCATE TABLE
CREATE INDEX
... etc.

Now, I want to restrict the DDL so that it captures ONLY :
ALTER TABLE myschema.* DROP PARTITION ...

that's it ... no TRUNCATEs, no DROPs, no other ALTER TABLE
ie ALTER TABLE myschema.mytable MODIFY ( col ....
don't want to capture that - for example.
I've tried some of the following .. all to no avail:
DDL INCLUDE OBJNAME myschema.*   &
    INCLUDE OPTYPE DROP          &
    INCLUDE OBJTYPE 'PARTITION'

this still allows ALL DDL
DDL INCLUDE OBJNAME myschema.*   &
    INCLUDE OPTYPE DROP          &
    INCLUDE OBJTYPE 'PARTITION'  &
    EXCLUDE ALL

This doesn't allow ANY DDL at all ..
DDL INCLUDE OBJNAME myschema.*   &
    INCLUDE OPTYPE DROP          &
    INCLUDE OBJTYPE 'PARTITION'  &
    EXCLUDE OPTYPE TRUNCATE      &
    EXCLUDE OPTYPE CREATE

That manages to prevent TRUNCATEs and CREATEs ..
however, I can't add "ALTER" or "DROP" .. as it starts affecting/preventing the DROP PARTITION clause.
I've opened a ticket with Oracle to get some feedback from them .. however, so far, not much luck there.
I was hoping somebody here might actually know how to do this ?
Thanks!


texts:
I have a forum which is growing way faster than I could imagine, and my site has been a target for DDoS attacks, NTP attacks, and a bunch of hacking attempts. I am starting to worry that there may be a compromise soon with the way these attacks are going about.

The forum database grows an average of 200MBs a day worth of forum topics, posts, and data. I am backing up my system once a week currently to make sure that I won't lose too much data if something is to happen.

Please Note: The backups are transferred to my file storage server after they have been created. I am keeping two backups of the data. One at the beginning of the month and one in the middle of the month, and the final one at the end of the month for the life-time of the site. The others are deleted after three months.

Am I making sure my data is protected enough in case I have to use these backups, or should I keep more copies throughout the lifespan of the site?


texts: Executing an oracle variable string as I do in SQL Server

texts:
In SQL Server I can do something like:

DECLARE @VAR VARCHAR(100);

SELECT @VAR = SELECT * FROM SYS.DATABASES

EXECUTE(@VAR)

How can I execute a string that I've created inside a variable in Oracle?

This is the query:

DECLARE BUSCACOLUNA  VARCHAR(2000);
BEGIN

 BUSCACOLUNA:= 'SELECT  '' SELECT '' || LTRIM(listagg ( '' , '' || ''"'' || T1.COLUMN_NAME || ''"'' || '' AS '' ||''"''|| T2.DESCRICAO ||''"''  ) within group  ( order by t1.column_name ),'' , '') || '' FROM TABELA_ENTRADA ''
  FROM ALL_TAB_COLUMNS T1
    INNER JOIN TABBASE T2
      ON T1.COLUMN_NAME=T2.NO_COL_TABBASE
    WHERE T1.TABLE_NAME=''TABELA_ENTRADA''
    AND T2.CD_CONTEUDO_ARQUIVO= ''X''';

 END;

The result of that dynamic SQL is something like:

  SELECT C1 AS B1, C2 AS B2...FROM TABLE

And I would like to execute this SELECT by executing the VARIABLE.

I tried with a loop, with EXECUTE IMMEDIATE but the query only returns:

  anonymous block completed

So I can understand it's working. If it was an UPDATE or INSERT, it would work, But I would like the return of that.


texts:
Let's say I have a PostgreSQL table with 10 columns and 2 weeks (w1 w2) with 5 countries. Something that would look like this :

What I would like to do is a count per week and per column like this :


texts:
I have a table in which view names can be inserted and the following trigger should ensure that only vew_names can be inserted that really exist in the System (that's all the code the trigger consists of):

CREATE TRIGGER check_if_view_exists ON DATABASE FOR [INSERT], [UPDATE]
AS
BEGIN
   IF EXISTS(SELECT view_name from MySpecial_views EXCEPT SELECT name from sys.views)
       RAISERROR ('You can only insert views in that table that really exist!',10, 1)
      ROLLBACK
   END -- end if
END -- end trigger

However upon creation of the trigger I get the following errors:

  Msg 102, Level 15, State 1, Procedure check_if_view_exists, Line 1
  [Batch Start Line 0] Incorrect syntax near 'INSERT'.

  Msg 102, Level 15, State 1, Procedure check_if_view_exists, Line 8 [Batch Start Line
  0] Incorrect syntax near 'END'.

What is missing?
I have carefully read the grammar of T-SQL Triggers but can't figure out why my trigger is wrong.


texts: Find times when table is least frequently used

texts:
I would like to analyse performance of one of the big tables in the database. There are only two types of operations on it. Inserts and reads. Once data is entered it is never updated.

I need to find when the table is updated least frequently (for example Saturday between 3pm-6pm). I need to also find when the table is read least frequently.

Structure looks like this:

dbo.Table1
    [Column1] [INT] IDENTITY(1,1) NOT NULL,
    [Column2] [VARCHAR](50) NULL,
    [Column3] [DATETIME] NULL CONSTRAINT
    [Column4] [INT] NULL,
    [column5] [VARCHAR](255) NULL,
    [column6] [INT] NULL,
    [column6] [VARCHAR](MAX) NULL,
    [column7] [INT] NULL,

There is clustered index1 on column1 and non clustered index2 on column2 and 3 including column1

We use:
SQL Server 2012


texts:
I create a Job that runs the query below for all databases in a server in order to update empty values with null in all columns for all tables. The problem I'm facing is this query literally killed the sql server, was in a dev enviroment.

I notice the log for the first DataBase grew up from 6gb to 80gb and left the server without space.

Is there a better way to achieve update all empty columns with null in all Tables without crashing the server?

This query generate around 5,000 update statements for each database

I plane to run this kind of cleanup once a week.

SELECT
ROW_NUMBER ( )
    OVER (ORDER BY [object_id] DESC) AS iRow
,name,[object_id]
INTO #temp
FROM sys.Tables
WHERE
name NOT LIKE 'old%'
AND name NOT LIKE '%old'
AND name NOT LIKE '%copy'
AND name NOT LIKE 'copy%'
AND name <> 'sysdiagrams'

--SELECT * FROM #temp
DECLARE @intFlag INT,@endFlag INT

SELECT @intFlag= MIN(iRow) FROM  #temp
SELECT @endFlag= MAX(iRow) FROM  #temp

CREATE TABLE #temp2(name VARCHAR(250),tablename VARCHAR(250))

--SELECT * FROM #temp
WHILE (@intFlag<=@endFlag)
    BEGIN
        INSERT INTO #temp2
        SELECT c.name,(SELECT name FROM #temp WHERE iRow=@intFlag) AS TableName
        FROM syscolumns c
        INNER JOIN systypes t ON c.xtype=t.xtype
        WHERE id =(SELECT [object_id] FROM #temp WHERE iRow=@intFlag)
        AND isnullable = 1 AND c.xtype IN(99,167,175,231,239)
        order by c.name
        SET @intFlag = @intFlag + 1;
    END

DECLARE @SQLquery AS VARCHAR(MAX) = ''

SELECT @SQLquery +='UPDATE ['+tablename+'] SET ['+ name+'] = NULL WHERE LTRIM(RTRIM(['+name+']))='''';'
FROM #temp2

--SELECT @SQLQuery
EXECUTE (@SQLquery)

DROP TABLE #temp
DROP TABLE #temp2


texts: Pros and cons of a particular DB design approach for single vs multiple tables

texts:
I just got a new project (in JAVA) where I am thinking of all the pros and cons of a particular DB design approach. Some pointers:

Our app has many "content" entities like News, Blogs, CMS pages, Wikis, Suggestions etc. I feel that most of these entities are similar (having 75% same fields) but 25% are different. For example, blogs might have a few extra columns which News does not have.
Since every entity seems "similar", I am thinking of keeping them in the same DB table with all the columns covering each entity.
Data load: A customer might have 100K news, 50K blogs, 10K CMS pages, 100K wikis and 50K Suggestions. This is the top-end (maximum) a customer might have given our product. Some customers might have just 2K-5K items of each type in their DB. Lower end data load: 1K items of each type in DB.
There will be more "reads" than "inserts" in this app (customer usage).

My questions:

Will having all this data in a single table hurt performance too much? Note that we will be using Lucene for searching.
Having a single content table with an entity type column which specifies the entity type, we can have a single business layer handling these entities. This will reduce the code base considerably. Any cons of this approach?


texts: Should I add SET NOCOUNT ON to all my triggers?

texts:
It is fairly common knowledge that you should have

SET NOCOUNT ON

by default when creating new stored procedures.

Microsoft has changed the default template to include this in 2012. I thought this should be the same for triggers, yet it is not included in the template.

Is this intentional or just an oversight?


texts: Too many TEXT fields leads to record too big

texts:
I have a table with multiple 9 TEXT columns, each containing JSON data.  I usually UPDATE all the columns at once.  I can't seem to add a 10th TEXT column.

The table is InnoDB.

What can I do?


texts:
I am seeing this line in many mongoDB links

  Use CNAMEs to identify your config servers to the cluster so that you
  can rename and renumber your config servers without downtime

I need some help in how set or add CNAMEs to my mongo config servers.

Can anyone help me with a brief explanation on how to implement that in a shard instance?


texts: Order to restore system and user databases

texts:
We are going to move an sql instance to a new physical server (SQL SERVER 2008 r2). The answer to this question explains how Agent jobs and ssis-packages will be moved by restoring the master and msdb-databases to the new server.

My question is if the order of restoring system vs user databases has anything to say?
Is it OK to restore master and msdb before user databases, even though this will restore agent jobs and ssis-packages that point to user databases not yet restored/created?

Thanks for any help.

br
Gert


texts: Solving "Gaps and Islands" with row_number() and dense_rank()?

texts:
How does one solve the islands part of gaps-and-islands with dense_rank() and row_number(). I've seen this now a few times and I'm wondering if someone could explain it,

Let's use something like this for example data (example uses PostgreSQL),

CREATE TABLE foo
AS
  SELECT x AS id, trunc(random()*3+1) AS x
  FROM generate_series(1,50)
    AS t(x);

Which should produce something like this.

 id | x
----+---
  1 | 3
  2 | 1
  3 | 3
  4 | 3
  5 | 3
  6 | 2
  7 | 3
  8 | 2
  9 | 1
 10 | 3
...

What we want is something like this...  (where z is some value that we can use to GROUP BY)

 id | x | grp
----+------
  1 | 3 | z
  2 | 1 | z
  3 | 3 | z
  4 | 3 | z
  5 | 3 | z
  6 | 2 | z
  7 | 3 | z
  8 | 2 | z
  9 | 1 | z
 10 | 3 | z
...

Such that we can do either

GROUP BY x,grp where (x,grp) is a unique island
GROUP BY grp where grp is a unique island.

That would look like this for (x,grp) in the above example.. (where z is some value that we can use to GROUP BY)

 id | x | grp
----+------
  1 | 3 | 1
  2 | 1 | 1
  3 | 3 | 2
  4 | 3 | 2
  5 | 3 | 2
  6 | 2 | z    -- Because we're grouping by an (x,grp)
  7 | 3 | z    -- Does not have to be `3` (or something unique)
  8 | 2 | z    -- z=1, is fine to use again if we
  9 | 1 | z    -- GROUP BY (x,grp)
 10 | 3 | z

Background

This has come up only a few times in answers in my research.

Using Row_Number to find consecutive row count by @Martin Smith
Getting each status change in a table by @Martin Smith
Grouping or Window by @ErikE

It also seems to explicitly confuse others though it has never been explained on this network,

Question 1 (never explained), solved and accepted with lag()
Question 2 (never explained), solved an accepted with lag()


texts: Recommendation for synchronizing SQL Server DBs

texts:
I have two databases, prod and dev. Each DB is about 500GB.  At the end of each work day, the prod database gets several million new rows and several thousands of updates.  After the data is loaded in prod, I would like to refresh dev database from production. Both servers are SQL Server 2012.

Ideally I need something lightweight, easy to setup and maintain. The synch process needs to have a minimal performance impact on the prod server. Open to 3rd party solutions.

What solution would you recommend: transactional replication, change tracking, change data capture, or something else entirely?

Thanks for your help.

PS. Would using Change Tracking + Sync Framework be an suitable alternative?


texts: Is there a simple, safe and low cost way to implement a (limited) database to be outside (close) IT control?

texts:
This question is a spin off of the discussion here (What are the risks of building a database outside IT control?).

Background: I'm a risk guy that builds little betas, mostly in Excel-VBA. I have a lot of freedom in this 'beta mode'. People ask me things, I build it, they test, it crashes, I fix, it crashes, we change the specs, I rebuild it, they start using in a daily basis, it crashes, I fix it, specs, rebuild, etc..,etc. Lots of betas don't work and we just let it go. For the surviving ones, one sunny day we all realize the whole thing is stable enough, its minimally documented, and they put in a list that one day one real programmer will integrate into some new ideal system to come.

I realize this is problematic for IT (and really for everybody in the end) and that's one of the reason I want to start building simple SQL databases which make the whole thing way less messy then a db hidden in a branch of excel files.

Now, my IT tells me its policy, as in many places, that mortal user like me should not build dbs.

My question is: What's the set up I can offer that is simple, safe and low cost to implement from IT perspective? One idea is just to put it in a separate PC but I do need people in the network to be able to get data for the beta thing to work. Otherwise it can be confined to the hard drive.


texts: JSONB: Finding an element with a null attribute in array of objects

texts:
With the following table in psql 10:

CREATE TABLE logs(id serial, data JSONB);
INSERT INTO logs VALUES
  (1, '[{"name":"test1","date":"2018-01-09"},{"name":"test2","date":"2018-01-10"}]'),
  (2, '[{"name":"test3","date":"2018-01-10"},{"name":"test4","date":null}]');

How do I find row #2 by querying the date=null value in the array?

I already know how to query by name to find row#2:

AND data @> '[{"name": "test4"}]'

But I don't know how you can query for rows with or without a null date attribute.


texts: How can I get MariaDB to use indexes in a RANK() query?

texts:
Consider the following simple RANK() query.

select project_id, group_id, rank()
  over (partition by group_id order by stars desc) as star_rank
from project_metrics;

I've added indexes to the project_metrics table on group_id, stars, and group_id, stars.  And yet, MariaDB (server version 10.3.17) refuses to use any of the indexes.

> explain select project_id, group_id, rank() over (partition by group_id order by stars desc) as star_rank from project_metrics\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: project_metrics
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 55635183
        Extra: Using temporary

Am I missing something?

In response to comment by @Luuk, the JSON output of explain is as follows.
It may indicate that the required sorting is done efficiently after all (it uses two indexed keys for sorting). This would mean that the output of the plain explain is misleading.

EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "window_functions_computation": {
      "sorts": {
        "filesort": {
          "sort_key": "project_metrics.group_id, project_metrics.stars desc"
        }
      },
      "temporary_table": {
        "table": {
          "table_name": "project_metrics",
          "access_type": "ALL",
          "rows": 55635465,
          "filtered": 100
        }
      }
    }
  }
}


texts: Postgres, benefit of named schema va public schema?

texts:
I am new to Postgres and I am trying to understand how schemas fit into everything and what is the benefit of a non-'public' schema. For example, does it provide security, access control benefits or something else?

The setup I am working with is one where postgres owns the database and tables, but grants permissions to another user to select, insert, update, and delete. Would creating the tables in the 'public' schema vs 'myotherschema' make much of a difference?


texts:
We are having an issue that is probably due to some poor design choice or lack of understanding of how to use primary keys.

We have a simple constants table

CREATE TABLE IF NOT EXISTS `constants` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `category` varchar(20) NOT NULL,
  `subcategory` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
)

We reference the unique rows using the id column.

E.g. if(id == 30) do something

Now, our issue arises when we sync between dev and production. On dev, we are constantly trying our new things/testing features. We are adding and deleting constants. So our ID column doesn't map into our production database. So, if we tried the same code again on production:

e.g. if(id == 30) do something

It points to a different record.

My initial thought is that we just make the category/subcategory columns the primary key but I have read that you should almost always use a GUID for a primary key.


texts: I am new to MongoDb, I have 1 TB of Data in JSON format, what should be the processor speed, RAM, disc space required, network bandwidth for maximum of 10 concurrent users for production environment.


texts: Why are queries causing spill to tempdb?

texts:
Background

I am in the process of migrating a 160gb database from MSSQL 2008 (standard) on a Win 2008 server with 48gb RAM to a new server running MSSQL 2012 (64-bit web edition) on Win 2012 with 64gb of RAM. The old server is live and under load; the new server is not in production. The new server has 8 tempdb files (4GB each).

Problem

In testing on the new server I am seeing steps in numerous queries cause alerts mentioning "operator used tempdb to spill data during execution". I have been able to avoid sorts by rewriting some of the queries, but this is not really addressing the issue. The same queries on the old server do not cause spills. I have read that spills happen when MSSQL can't complete an operation in memory and has to spill/page into tempdb. Should I be concerned about spills?

Examples

I have run sp_updatestats on the database, so statistics should be up to date, but you will note there are some discrepancies between the estimated and actual numbers of rows.

Memory concern

I have set a max memory setting for MSSQL of 58 of the 64gb. Currently MSSQL has consumed around 35gb of this memory, but has a working set of only 682mb. The old server (albeit in production, handling load) has 44gb of memory committed to MSSQL of which 43.5gb is in its working set.

I do not know if the spills might be related to a memory setting - anyone have any ideas? MSSQL currently has acres of RAM to spare, so why is it spilling into tempdb for some sorts and hash matches?


texts: How to get the ID of the conflicting row in upsert?

texts:
I have a table tag with 2 columns: id (uuid) and name (text). I now want to insert a new tag into the table, but if the tag already exists, I want to simply get the id of the existing record.

I assumed I could just use ON CONFLICT DO NOTHING in combination with RETURNING "id":

INSERT INTO
    "tag" ("name")
VALUES( 'foo' )
ON CONFLICT DO NOTHING
RETURNING "id";

But this returns an empty result set, if the tag with the name "foo" already exists.

I then changed the query to use a noop DO UPDATE clause:

INSERT INTO
    "tag" ("name")
VALUES( 'foo' )
ON CONFLICT ("name") DO UPDATE SET "name" = 'foo'
RETURNING "id";

This works as intended, but it is somewhat confusing, because I'm just setting the name to the already existing value.

Is this the way to go about this problem or is there a simpler approach I'm missing?


texts: Identity versus Composite values for Primary Keys in Many-To-Many Tables

texts:
I am in the middle of an interesting "Data Architecture" discussion at a place I am contracting & need some input.

As a habit, I always create a Surrogate Key as the Primary Key in my tables - be they Guid (UUID) or Identity values.  IMHO the synthetic-nature of a Surrogate Key has the distinct value of identifying a row without intruding on "real" or "natural" keys-or-values that may otherwise be useful Alternate Keys.

To me...it just seems natural - but maybe I am wrong.

Where I happen to be at the moment...they argue this good for "core" Entity tables, but is completely incorrect for Many-to-Many (relationship) tables.  While I will happily to oblige the customer...I disagree.

Thoughts behind my approach are:

A primary key value must be unique
The primary key should be as compact as possible
Primary key value should be stable
Primary Key identifies a tuple...not the data INSIDE the tuple

MY QUESTION: Which is either correct or better?  And Why?
- Please provide reasoning etc.

NOTE:
Please Ignore the ID column naming in the samples below...that is how they do it here.


texts: Number of tables in join and query performance

texts: Recently I came across a standards in my project which strangely asserts that we should refrain from joining more five tables in SQL query. More than five tables in a query leads to performance degradation. Even though I strongly disagree with such assertion (regardless it's SQL Server or Oracle) I was wondering is there any suggestion as to how many tables we should use in join in SQL Server or Oracle. Does it depends on the database type (SQL Server Vs Oracle Vs MySQL) or number of rows in the tables, indexing, INNER Vs OUTER Join, cardinality of the tables joined, etc.? My understanding is since SQL is not a procedural language we should try to add more tables in JOIN provided that we are using the right columns for joining.


texts: Import database from MSSQL export dump on Ubuntu localhost

texts:
I have an export file from an online database server that I need to use to generate the database locally on my system. I'm running MSSQL 2017 on Ubuntu 16.04.

The export file has Program(application/octet-stream) in file properties dialogue box.

I want to use this file with SQLCMD command-line tool. Is it possible? If yes, how to do it? Please inform me if there's anything else I can specify before closing this question, if it seems vague or anything.


texts: Postgres database migration "zero" downtime

texts:
I have a setup with a JBoss EAP server, postgres 9.1 database.
I will soon update so I have two JBoss servers in a cluster. This is mostly to avoid downtime when doing application deployments.

With the database as a single point of failure, how can I get close to zero downtime when having database migrations.


texts: Postgres: Purpose of archiving in master?

texts:
I am reading tutorials on how to setup streaming replication in Postgres from master to slave.

Some tutorials recommend setting up archiving in the master (and restoring in slave) . In the master, this needs to be set in the conf file:

wal_level = hot_standby
archive_mode = on
archive_command = 'some rsync command to rsync logfile segments from master to slave'

What is the purpose of this archiving? When is it not necessary when setting up streaming replication?


texts: Oracle: Read from CLOB column line by line and insert to the table

texts:
I have CLOB column, and wanna read its data line by line to insert it in another table.

Now let's create the same situation that I have:

Data in a Table MyTable_1

OWNER | TABLE_NAME | SQL
----------------------------
HR    | AAA        | <CLOB>

In <CLOB> there is the following data:

CREATE TABLE AAA
( A NUMBER
);

I want my table to look like this(let's call it MyTable_2) :

OWNER | TABLE_NAME | SQL
----------------------------
HR    | AAA        |CREATE TABLE AAA
HR    | AAA        |( A NUMBER
HR    | AAA        |);


texts:
I was advised to make another question, from here - https://stackoverflow.com/questions/19674045/need-help-to-make-sqlcmd-run-as-per-requirements

My sqlcmd.exe is invoked with a command of the form -

sqlcmd -S .\MYSQLSERVER2008 -U MyUsername -P MyPassword
-i C:\Database\hugescript.sql > log.txt 2> error.txt

Now,

log.txt says - Canceling connection. Please wait...
Error.txt says - Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 :
Operation was canceled..

How do I find out the cause of the problem and fix it ?


texts:
I have literally tried everything, from enabling named pipe to adding exception to ports in the firewall, to everything possible in surface configuration. I can connect to the SQL instance(using TCP and Named Pipes) with SQL Server Management Studio. But sqlcmd throws an error:

Login timeout expired

Help!


texts: Merge two postgress databases with identical schemas

texts:
I have two Postgresql databases with identical schemas, and I'd like to merge them (or merge them both into a third).

The problem is the tables all have id PKs that are auto increment primary keys, and use those id columns as FKs to other tables.

I know the data is logically distinct between my two databases, but they overlap on their PK's, for example:

Database A:

Table Building:

id    | name
1     | Smooth Hall
2     | Orchard Towers

Table Floor:

id    | building_id (FK building.id) | floor_number
1     | 1                            | 1
2     | 2                            | 1
3     | 2                            | 2

Database B:

Table Building:

id    | name
1     | Chancey Theater
2     | Allen Hall

Table Floor:

id    | building_id (FK building.id) | floor_number
1     | 1                            | 1
2     | 2                            | 1
3     | 2                            | 2

Is there a tool or approach you can suggest as to how to merge these databases such that the result would be include all the data, and all the FK relationships?

I don't care about the resulting order of rows, or the value in the id columns in the output, just so long as the data has the same logical structure and relationships, e.g.

Database C:

Table Building:

id    | name
a     | Smooth Hall
b     | Orchard Towers
c     | Chancey Theater
d     | Allen Hall

Table Floor:

id    | building_id (FK building.id) | floor_number
e     | a                            | 1
f     | b                            | 1
g     | b                            | 2
h     | c                            | 1
i     | d                            | 1
j     | d                            | 2

where a-j are any distinct numbers.


texts: SQL Server commands to clear caches before running a performance comparison

texts:
When comparing the execution time of two different queries, it's important to clear the cache to make sure that the execution of the first query does not alter the performance of the second.

In a Google Search, I could find these commands:

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

In fact, my queries are taking a more realistic time to complete after several executions than before. However, I'm not sure this is the recommended technique.

What's the best practice?


texts: Backup Database Task - What's the number at the end of the filename?

texts:
When using a SQL Server 2012 EMaintenance Plan for backup purposes, the Backup Database Task creates a unique filename. I understand the format all the way to the final set of numbers.

For example, a Full Database Backup initiated via the Backup Database Task would create a filename like:

  AdventureWorks_backup_2014_12_17_080005_5862824.bak

What is 5862824 referencing? Is than LSN? Something else?


texts:
I have a table my_link_t with columns t.weight type number and categorical speedcat type integer values from 2 to 8. I want to split data in buckets from min=0, max=0.6, step = 0.001 and build a 3D plot to see weight distribution per each category.

initial data looks like
weight speedcat
0.0234 2
0.8643 6
0.1854 7
(hundred million more entries with weight between 0 and 0.6 and speedcat between 2 and 8)

These queries return correct results and complete in less than a minute each:

--repeat for each variable. Here we loook for speedcat =8
--It takes seconds to run this query
create table   histogram_tbl_8  as (
  select  ttt."Start" as bucket_index, ttt.hist_row as bin8  --here
  FROM ((
  SELECT  Bucket*1 "Start" , Bucket "End", Count(Bucket) hist_row
  FROM (SELECT WIDTH_BUCKET (weight, 0, 0.6, 601) Bucket FROM my_link_t  where speedcat=8)
      GROUP BY Bucket ORDER BY Bucket ) ttt   )   );

The above query is repeated seven times for speedcat in range  2..8

--if a bin is empty populate it with zero, don't skip it.
create table histogram_output as (
select  tr.bucket_index,
    CASE
        WHEN 1 > (select count(*) from histogram_tbl_2   htm where htm.bucket_index = tr.bucket_index)   THEN 0
        ELSE (select htm.bin2 from histogram_tbl_2   htm where htm.bucket_index =  tr.bucket_index and rownum = 1)
     END
     as b2,
     --same for b3-b7
      CASE
        WHEN 1 > (select count(*) from histogram_tbl_8   htm where htm.bucket_index = tr.bucket_index)   THEN 0
        ELSE     (select htm.bin8    from histogram_tbl_8   htm where htm.bucket_index =  tr.bucket_index and rownum = 1)
     END
     as b8
     FROM  (SELECT LEVEL as bucket_index, 0 as b2, /* 0 as b3, 0 as b4, 0 as b5, 0 as b6, 0 as b7, */ 0 as b8  FROM DUAL CONNECT BY LEVEL < 600)  tr
     )

Finally

      select sum(b2), sum(b3),sum(b4),sum(b5),sum(b6),sum(b7),sum(b8) from histogram_output
       select bucket_index,
              round(b2 * 1000000 / 12921) as  b2, --normalize so that total is 1000000 ppm
              -- repeat for b3-b7
              round(b8 * 1000000 / 6262) as  b8 --normalize so that total is 1000000 ppm
         from histogram_output

I get a table like

bin_end speedcat_2 speedcat_3 speedcat_4 .. speedcat_8
0.001
0.002
..
0.599
0.600

Showing ppm of objects within this category and this bin
Now, when I combine queries in

-- DONT USE THE EXAMPLE BELOW - it is ineefficient  (runs 2+ hours instead of seconds for the method above)
       SELECT  Bucket_2*1 "Start" , Bucket_2 "End",
       Count(Bucket_2) as b2,
       --same for b3 .. b7
       Count(Bucket_8) as b8

      FROM
      (
          SELECT WIDTH_BUCKET (t2.weight, 0, 0.6, 601) Bucket_2,
                 --same for t3,.. t7
                 WIDTH_BUCKET (t8.weight, 0, 0.6, 601) Bucket_8

           FROM (select weight from my_link_t where  speedcat = 2) t2,
           -- ..speedcat = 3) t3, .. speedcat = 4) t4, etc
            (select weight from my_link_t where  speedcat = 8 ) t8

      )
      GROUP BY Bucket_2 ORDER BY Bucket_2
      ------

Query runs several hours (some 500 times longer runtime than individual queries) until I kill it. Books recommend to do all data slicing in SQL. This example suggests that loading data to Java and slicing it there might be better in case of complex queries.

What can cause the difference?


texts:
So I have a continuation of this question. New requirements have thrown in a third table.

So here's the breakdown of how the tables relate:

TabA(TabA_id, desc)
TabB(TabB_id, TabA_id)
TabC(TabC_id, TabA_id)

I need a count of each time a TabA desc occurs in both TabB and TabC, and I only need the records from TabC that have a TabA_id.

This is the query that is getting me close (sort of):

select  COALESCE(count(b.TabC_id) + count(c.TabC_id), 0) as "Number of Records",
a.desc as "Type",
to_char(round(ratio_to_report(count(b.TabC_id) + count(c.TabC_id)) over()*100)) || '%' as "Percentage of Total"
from TabA a
left join TabB on a.TabA_id = TabB.TabA_id,
TabB b
left join TabC on b.TabC id = TabC.TabC_id,
TabC c
right join TabA on c.TabA_id = TabA.TabA_id
where c.TabA_id is not null
group by b.TabC_id, c.TabC_id, a.desc
order by a.desc asc;

Hope this makes some sense. Looking forward to comments for clarification.


texts:
How can I use a SELECT clause based on comparison between values of different rows? For instance, let Table 1 be:

ID   fieldA    fieldB    fieldC
1      A         X         2
2      A         Y         3
3      B         X         3
4      B         X         2
5      C         X         2

for each diffent value of fieldA, I want to get only the rows where combination of fieldC for fieldB=Y is greater then fieldC for fieldB=X. Note:There is a maximum of two rows for each value of fieldA.

ID   fieldA    fieldB    fieldC
1      A         X         2
2      A         Y         3
3      B         X         3
5      C         X         2

I was trying to use OVER clause, but I couldn't make it to work so far...

Thank you for your help!


texts: Dual management node for mysql clustering

texts:
I currently have mysql cluster setup and running.
Here is my current setup:
1 - management node
2 - data nodes
2 - mysql nodes

I wish to incorporate a failover for the management node. The management node seems to be the bottleneck in my current setup. If the management node fails, my entire cluster is down. How can I incorporate a dual management node settings? Is it possible to have a 2nd management node? Must I also increase my data node and mysql node to accommodate the 2nd management node. Please assist.

This is what I have in my config.ini now: The 2 data nodes is together with my management node. I have separate out the mysql nodes.

[ndb_mgmd]
hostname=192.168.0.1
datadir=c:\Users\user1\my_cluster\ndb_data
NodeId=1
[ndbd default]
noofreplicas=2
datadir=c:\Users\user1\my_cluster\ndb_data
[ndbd]
hostname=192.168.0.1
NodeId=3
[ndbd]
hostname=192.168.0.1
NodeId=4
[mysqld]
hostname=192.168.0.2
NodeId=5
[mysqld]
hostname=192.168.0.3
nodeId=6


texts:
Can I change tempdb size to a specific value and can I shrink it during our daily working hours on our production database ?. Please tell me if it has bad effect on a production database.

My database server has 4 cores and I'm using SQL Server 2008 R2 SP3. I asked this question because I noted that its size is growing suddenly. I'm afraid that it may make the hard drive full.

I frequently run below query to see if there is page contention or not. I always see an empty result, which indicates that there is no page contention, therefore I did not add additional data file to tempdb.

(SELECT session_id AS [Session],
wait_duration_ms AS [WaitTime(ms)],
resource_description AS [Type]
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH_%'
AND (resource_description LIKE '2:%:1'
OR resource_description LIKE '2:%:2'
OR resource_description LIKE '2:%:3')

My tempdb is located on the local C drive. I have only one data file for it.


texts: Alternatives for a LEFT OUTER JOIN query

texts:
Problem

I have a SQL statement with an LEFT OUTER JOIN which works fine on our Microsoft SQL Server. My problem is that i have to be compatible with H2 Database and this one got a bug with OUTER JOINS.

SQL Query

SELECT *
FROM tSysNls
WHERE nlsGuid IN
    ( SELECT nlsGuid = CASE
    WHEN de_AT.nlsGuid IS NOT NULL THEN de_AT.nlsGuid
    WHEN de.nlsGuid IS NOT NULL THEN de.nlsGuid ELSE en.nlsGuid
    END
    FROM tSysNLS en
    LEFT OUTER JOIN tSysNLS de ON en.nlsAttribute=de.nlsAttribute
        AND en.nlsClazz=de.nlsClazz
        AND de.nlsLocale= 'de'
    LEFT OUTER JOIN tSysNLS de_AT ON de.nlsAttribute=de_AT.nlsAttribute
        AND de.nlsClazz=de_AT.nlsClazz
        AND de_AT.nlsLocale= 'de_AT'
    WHERE en.nlsLocale= 'en'
        AND en.nlsClazz= 'Contact'
        AND en.nlsAttribute= 'firstName')

Table

CREATE TABLE tsysNLS
(
   nlsGuid           nvarchar(207)   NOT NULL,
   nlsLocale         nvarchar(5)     NOT NULL,
   nlsClazz          nvarchar(100)   NOT NULL,
   nlsAttribute      nvarchar(100)   NOT NULL,
   nlsDisplayName    nvarchar(255)   NOT NULL,
   nlsOldname        nvarchar(50),
   nlsDescription    nvarchar(255),
   nlsShapefilename  nvarchar(10)
);

Example data

INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.en', N'en', N'Contact', N'firstName', N'Name - First Name', N'conFirstName', N'Name - First Name', NULL);
INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.de', N'de', N'Contact', N'firstName', N'Vorname', N'conFirstName', NULL, N'ConNamVor');
INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.de_AT', N'de_AT', N'Contact', N'firstName', N'Vorname (AT)', N'conFirstName', NULL, N'ConNamVor');


texts: Get Job step run for all jobs and job steps using TSQL

texts: I was wondering if there is an easy way to check the "Run as" value for each job step using TSQL code instead of opening every job on the server and each individual step? I cannot figure out where to get this data from and I have scoured the MSDB in search of this but cant find anything in the sysjobs and related tables? SQL has to store this value somewhere. The question is where?


texts:
I have an application which works with two separate firebird database, but each database has the same user, but in case of Firebird the connection could be driven to each by connection string. My question is that if I would like to do the same in oracle, how can I do it? I created two tablespace(one for matadata one for data). User 'User1' has access to both of them, but how can I handle that once I want to read/write in Mata TS once in the DATA TS? I need to create a new user for the second one, or there is a way to manage it?
I would be surprised if there was no solution because then why would it allow a user to have several tablespace if I can not control where to write?
Thank you in advance for the answers.


texts:
I have a system that gathers RSS from many sites including UTF-8 based sites (non-english sites). I have tried to avoid repeated rows but I have repeats in my table.

RSS_items (id(auto_increment) PRIMARY KEY, url ,title, body)

Is there any way to delete the repeat? The problem is that sometimes this repeat happens and sometimes not. The first thing I think I must find those who are repeated, then delete the extra rows. But how ?


texts: Incorrect "Sensitive data columns should be classified" report

texts:
Every week I'm getting a failed "Vulnerability Assessment" email report from my Microsoft Azure SQL Database that is incorrect.
It complains about columns that have is_computed column in sys.columns set to 1.
It is not possible to add a classification for those columns.

Is it possible to stop the warning?


texts: SELECT ... INTO variable results in NULL or idk

texts:
you can find all the relevant code on pastebin.

The problem: the variable to_spend stays default. If I leave the default 0, it is 0, if change it to NULL, it is NULL. So the statement if(to_spend > points) is evaluated FALSE then SQLSTATE 45000 is signalled.

If I evaluate the query separately in PHPMyAdmin

SELECT `current_points` INTO @var FROM `smartfuel_users` WHERE `id` = user_id;
SELECT @var;

the expected value is returned. I have been searching the whole day to find similar issues. Everywhere the datatype incompatibility has been the solution. After I checked the datatypes, it seems, this one is not the case.

Thanks for any clues, in advance.
The complete code:

CREATE TABLE IF NOT EXISTS `smartfuel_users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `surname` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `email` varchar(50) CHARACTER SET ascii NOT NULL,
    `password` varchar(255) COLLATE utf8_czech_ci NOT NULL,
    `chip_card_id` varchar(20) CHARACTER SET ascii DEFAULT NULL,
    `total_points` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `current_points` mediumint(8) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE KEY `email` (`email`,`chip_card_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `user_spends_points`$$
CREATE DEFINER=`a30267_palivo`@`%` PROCEDURE `user_spends_points`(IN `user_id` INT UNSIGNED, IN `points` MEDIUMINT UNSIGNED)
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE to_spend MEDIUMINT UNSIGNED DEFAULT 0;
    DECLARE id INT UNSIGNED;
    DECLARE loc_points MEDIUMINT UNSIGNED;
    DECLARE loc_spent MEDIUMINT UNSIGNED;
    DECLARE cur CURSOR FOR SELECT `id`, `points`, `spent` FROM `smartfuel_activities` WHERE `user_id` = user_id AND `expired` = 0 ORDER BY `created_at` ASC;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        CALL `log`("handler", "SQLEXCEPTION");
        RESIGNAL;
    END;
    DECLARE EXIT HANDLER FOR SQLSTATE '45000'
    BEGIN
        ROLLBACK;
        CALL `log`("handler", "SQLSTATE");
        CALL `log`("to_spend", CAST(to_spend as CHAR(5)));
        RESIGNAL;
    END;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    BEGIN
        SET done:= TRUE;
        CALL `log`("handler", "NOT FOUND");
        CALL `log`("to_spend", CAST(to_spend as CHAR(5)));
    END;

    START TRANSACTION;
    SELECT `current_points` INTO to_spend FROM `smartfuel_users` WHERE `id` = user_id;

    IF to_spend > points THEN
        UPDATE `smartfuel_users` SET `current_points` = `current_points` - points WHERE `id` = user_id;

        OPEN cur;

        userPointsLoop: LOOP
            FETCH cur INTO id, loc_points, loc_spent;
            IF done THEN
                LEAVE userPointsLoop;
            END IF;

            IF points > 0 THEN
                IF (loc_points - loc_spent) > points THEN
                    UPDATE `smartfuel_activities` SET `spent` = points WHERE `id` = id;
                    SET points = 0;
                    LEAVE userPointsLoop;
                ELSE
                    UPDATE `smartfuel_activities` SET `spent` = loc_points WHERE `id` = id;
                    SET points = points - loc_points;
                END IF;
            ELSE
                LEAVE userPointsLoop;
            END IF;
        END LOOP userPointsLoop;

        CLOSE cur;
    ELSE
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Not enough points to complete expenditure', MYSQL_ERRNO = 1000;
    END IF;

    COMMIT;
END$$

DELIMITER ;


texts: Get lines of pg_hba.conf with trust method from user with minimal privileges

texts:
I try on PostgreSQL 9.3 to get the lines of pg_hba.conf where the authentication method is trust from user with minimal privileges. I wrote a function, but it doesn't work:

create function public.get_pghba_with_trust()
  returns TABLE (
    TYPE            text
  , DATABASE        text
  , PGUSER            text
  , ADDRESS         text
  , METHOD          text)
as
$$
  SELECT *
  FROM regexp_split_to_table(pg_read_file('pg_hba.conf'),'\n') t(a)
  WHERE a ~ '^[^#].*trust\s*$';
$$
language sql
security definer;

This returns an error:

ERROR:  return type mismatch in function declared to return record
DETAIL:  Final statement returns too few columns.
CONTEXT:  SQL function "get_pghba_with_trust"

Grant execute on function

grant execute on function get_pghba_with_trust() to redcheck;

Try other function

CREATE OR REPLACE FUNCTION get_pghba_with_trust() RETURNS text AS $$
DECLARE
  trust text;
BEGIN
  SELECT * INTO trust from regexp_split_to_table(pg_read_file('pg_hba.conf'),'\n') t(a) WHERE a ~ '^[^#].*trust\s*$';
  IF NOT FOUND THEN
    RETURN 'not used';
  ELSE
    RETURN trust;
  END IF;
END;
$$ LANGUAGE plpgsql security definer;

Grant execute on function

grant execute on function get_pghba_with_trust() to redcheck;

Try run function get_pghba_with_trust

SELECT * FROM get_pghba_with_trust();

Error:

ERROR: must be superuser TO READ files
CONTEXT: SQL STATEMENT "SELECT * from regexp_split_to_table(pg_read_file('pg_hba.conf'),'\n') t(a) WHERE a ~ '^[^#].*trust\s*$'" PL/pgSQL FUNCTION get_pghba_with_trust() line 5 AT SQL STATEMENT

How get lines of pg_hba.conf on PostgreSQL 9.3 with trust method from user with minimal privileges?


texts: SQL Server many active create procedure processes

texts:
While troubleshooting performance issues on a SQL 2008 instance, I've noticed there are several SPIDs which appear to be executingcreate procedurestatements for already existing procedures.

Is this part of SQL's normal internal functioning, or do I have some odd application logic going on here? I can find these procedures listed in SSMS, and it says they were created years ago.

Thanks.


texts: Why does sys.fn_hadr_backup_is_preferred_replica take so long? Is there a better way?

texts:
I am using sys.fn_hadr_backup_is_preferred_replica to query the databases on each of my Availability Group replicas to figure out if the database should be backed up on that replica - then, if the function returns true, checking msdb.dbo.backupset to see if the database has been backed up recently.

The problem I'm experiencing is that the function takes a long time to run (about 3 seconds per database) - can anyone recommend an alternative that is faster?

In response to the comment below, I am currently on SQL 2012 SP1 (11.0.3000.0) - however, the queries I am creating will need to target multiple different installations of SQL Server 2012 on multiple customer sites, so, even if the performance of this system function has been improved in subsequent updates, I may not be able to force people to update, unfortunately.


texts:
So, it seems that it is not possible to set the timeout for each single step in a SQL Server job (article from StackExchange).

MSDN points out that you can of course set the Agent timeout:

Sql Server Agent properties.
Set Job execution shutdown.

Questions

How does Agent time-out work?
Is there any other way (preferably by configuration) to setup the single step timeout?
Is there any other way (preferably by configuration) to setup the whole job timeout?


texts:
Is it possible to get an execution plan of currently running query by sql_id?

I couldn't succeed with:

select
   DBMS_SQL_MONITOR.REPORT_SQL_MONITOR
        (sql_id=>'b3x6apqyskn7x') report
from dual;


texts: Is there a cool way of performing CU updates for SQL Server on hundreds of machines?

texts:
My company has grown from 50 SQL Servers to over 200 overnight in a merger acquisition. HELP!

My questions are:

How can I perform CU updates on this many servers and still have a life and keep my sanity? SCCM doesn't seem to be able to perform CU updates.
Has anyone else been able to perform CU updates on 100s of servers?
What are some other alternatives?


texts: Starting PostgreSQL server after a HDD crash results in FAILED STATE

texts:
I am using Fedora 15 with PostgreSQL 9.1.4. Fedora crashed recently after which:
An attempt to start the PostgreSQL server :
service postgresql-9.1 start

gives
Starting postgresql-9.1 (via systemctl):  Job failed. See system logs and 'systemctl status' for details.
                                                       [FAILED]

Although, the server starts normally when I start the server for the first time after system reboot.
But, an attempt to use psql gives this error :
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

.s.PGSQL.5432 file is not present anywhere on the system.
A locate .s.PGSQL.5432 outputs nothing.

The system log has this :
Aug 14 17:31:58 localhost systemd[1]: postgresql-9.1.service: control process exited, code=exited status=1
Aug 14 17:31:58 localhost systemd[1]: Unit postgresql-9.1.service entered failed state.

A
systemctl status postgresql-9.1.service

gives
postgresql-9.1.service - SYSV: PostgreSQL database server.
          Loaded: loaded (/etc/rc.d/init.d/postgresql-9.1)
      Active: failed since Tue, 14 Aug 2012 17:31:58 +0530; 58s ago
     Process: 2811 ExecStop=/etc/rc.d/init.d/postgresql-9.1 stop (code=exited, status=1/FAILURE)
     Process: 12423 ExecStart=/etc/rc.d/init.d/postgresql-9.1 start (code=exited, status=1/FAILURE)
    Main PID: 2551 (code=exited, status=1/FAILURE)
      CGroup: name=systemd:/system/postgresql-9.1.service

I had not changed the default setting of fsync so I am guessing, it was set to on. I am on a HDD. The HDD crashed.
HDD crash
The HDD crash resulted in running a manual fsck on a prompt and not gui based. With it repairing gazillion inodes etc.. After which I restarted the system with a Ctrl+Alt+Delete.
PostgreSQL's log has this:
LOG:  database system was interrupted; last known up at 2012-08-14 17:31:57 IST
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 0/41A4E58
LOG:  redo is not required
FATAL:  could not access status of transaction 1
DETAIL:  Could not open file "pg_multixact/offsets/0000": No such file or directory.
LOG:  startup process (PID 13016) exited with exit code 1
LOG:  aborting startup due to startup process failure

Update
Trying to start the server after taking a file system level copy of the /var/lib/pgsql directory, and running ./pg_resetxlog -f /var/lib/pgsql/9.1/data/ with the result xlog -f /var/lib/pgsql/9.1/data/ still yields in :
LOG:  database system was interrupted; last known up at 2012-08-14 18:46:36 IST
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 0/6000078
LOG:  redo is not required
FATAL:  could not access status of transaction 1
DETAIL:  Could not open file "pg_multixact/offsets/0000": No such file or directory.
LOG:  startup process (PID 13766) exited with exit code 1
LOG:  aborting startup due to startup process failure


texts: ERROR: invalid XML content, when Select unnest(xpath(... on a text column

texts:
I have a text column named big_xml in my table which has the following value:

<?xml version=1.0 encoding=UTF-8?>
<dpid:DpidDatabase xmlns:dpid=http://ddex.net/xml/dpid/11 xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance xsi:schemaLocation=http://ddex.net/xml/dpid/11 http://ddex.net/xml/dpid/11/dpid.xsd>
 <DpidOwner SequenceNumber=1>
  <DPID>PADPIDA2006111001O</DPID>
  <CompanyName>234AG</CompanyName>
  <Address>Riedtlistrasse 23, Zürich, 8006, CH</Address>
 </DpidOwner>
 <DpidOwner SequenceNumber=2>
  <DPID>PADPIDA2007011501Q</DPID>
  <CompanyName>OpenIMP</CompanyName>
  <Address>8-10 Rhoda Street, London, E2 7EF, UK</Address>
 </DpidOwner>
 <DpidOwner SequenceNumber=3>
  <DPID>PADPIDA2007040501K</DPID>
  <CompanyName>The Harry Fox Agency</CompanyName>
  <Address>711 Third Avenue, 8th Floor, New York, 10017, USA</Address>
 </DpidOwner>
</dpid:DpidDatabase>

Execute query:

SELECT
  unnest((xpath('/dpid:DpidDatabase/DpidOwner',  table1.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS SequenceNumber
  ,unnest((xpath('/dpid:DpidDatabase/DpidOwner/DPID',  table1.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS DPID
  ,unnest((xpath('/dpid:DpidDatabase/DpidOwner/CompanyName',  table1.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS CompanyName
  ,unnest((xpath('/dpid:DpidDatabase/DpidOwner/Address',  table1.big_xml::xml,ARRAY[ARRAY['dpid', 'http://ddex.net/xml/dpid/11']]))) AS Address
FROM table1_20190828

Error displayed:

ERROR: invalid XML content
 Detail: line 1: XML declaration allowed only at the start of the document
<?xml version=1.0 encoding=UTF-8?>
        ^
1 statement failed.


texts: is there a way to connect to mysql using oracle sqlplus instant client

texts:
i am stuck while trying to connect to mysql using sqlplus client and i have already asked this question at askubuntu.com as

https://askubuntu.com/questions/674871/not-able-to-connect-to-mysql-database-from-oracle-sqlplus-instant-client-in-ubun

please any one help me.. trying to solve it from last 5 hours :(


texts: Is it safe to rely on transactions in Firebird 2.5 Embedded DB in case of power outage?

texts:
How it's safe to say that the following list of action will never be reflected in DB in case of power outage somewhere in the middle of #2 line, before transaction is committed?
#1 begin transaction
#2 delete record in table A that cascade deletes another record in table B
#3 update another record in table C
#4 commit transaction


texts:
I have understood the importance of transaction logs but still unclear on a few things:

I've taken back-up of a T-log using this statement:

BACKUP LOG myDB
TO DISK = 'I:\Log_Backup\myDB.TRN'
GO

Then i used the following statment to monitor the space used:

DBCC SQLPERF(LOGSPACE);
GO

Log spaced used which was previously large went down to 0.859 %

Now on the issue of backing up i have two questions:

Our TSM guy is backing up the folders: I:/Database and I:/Logs thrice a day. I:/Database is the folder with all the MDF-files and I:/Logs is the folder with all the LDF-files.
Shoud he instead be backing up I:/Log_Backup/ and I:/Logs?
Or should he be backing up only I:/Logs where all the actual logs reside.
In case of a disaster when i need to retore a DB, should i use the last back-up from I:/Log_Backup/ or from I:/Logs


texts:
I wrote this query that suppose to count the working days in a month but I get the following error:

[42883] ERROR: operator does not exist: double precision + interval Hinweis: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 305

The date is a timestamp and this is the function below serves as the denominator to normalize sales:
(count(((date_part('month',date))+ Interval '1 month'- Interval '1 day')-(date_part('month',date)))
           filter (where date_part('dow', date) not in (6,0)))

In the end I will have:
select date_part('month', date) as month,
sum(sales)/denominator as Monthly_noralized_sales
from sales;


texts:
I am looking at a SQL Server database for an application I have inherited. I have not looked into SQL Server for approximately 10 years, so please bear with me.

The database table I am looking at has a bigint NOT NULL column called id, yet, when I check for constraints, I don't see any, and the same holds true for all database tables.

Am I right in assuming that there is no primary key & no indexing (clustered or nonclustered) on these tables?

I ran the following queries and the results appear to confirm my suspicion:

//**returns 0**
select count(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

//**returns no rows**
select * from sys.indexes
where object_id = (select object_id from sys.objects where name = 'NAME-OF-TABLE');

//**returns all tables in database**
SELECT name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0;


texts: PostgreSQL: Find records with single quotes

texts:
I have identified records in one of our tables with TWO single quotes.
So the record field appears as: ''
What SQL could I utilize to find all the record occurrences with ''
I have tried '%' and also '%''%' with no success.


texts: MySQL out of Memory, but Only Through MSSQL Linked Server

texts:
I am getting an error "MySQL client ran out of memory" when trying to do some simple MySQL selects or inserts from Microsoft SQL through a linked server. It was working fine for a week or so, and for some reason now throws the error every time.

If I use Toad to connect to MySQL, everything seems fine running inserts or big selects. But trying to insert just 1 row through the MSSQL Linked server gives an error !?!?

I can "SELECT ID FROM MyTable LIMIT 10", but if I add "ORDER BY ID" to it, I get the error, even though there's an index on ID.

Is there some kind of maintenence I need to do with MySQL ?

I'm pretty much a MySQL "noob" as relates to trouble shooting. It's on a Linux box at our hosting site, with 4 gig ram.


texts: How do I find duplicate values in multiple columns over different rows?

texts:
I have a database with a table of games (some columns omitted):

gameid
hometeam
awayteam
`date`

Sometimes games are added with the same home and away teams, but on a different date that is just 1 day difference (game date moved), and the old game isn't deleted. I'd like to be able to search over a variable number of days as well. Thus, for football, having the same opponent in a time span of +/- 5 days would be wrong, but possible in basketball or baseball or many other sports.

It's easy to search for multiple values of the same home/away/date, but the date difference makes this harder.

Also, the home and away team may be swapped, with the date also being the same or slightly different.

Example data:

gameid hometeam awayteam date
5      777      999      2014-10-23
6      999      777      2014-10-23
7      777      999      2014-10-24
8      777      999      2014-10-25

All of these are duplicates. Determining which doesn't matter, just that it should let me know that there are 4 games scheduled for this which it should (probably) be 1.

This is what I use to find duplicated games for the same home/away/date:

SELECT COUNT(*) as num,hometeam as teamid,`date` FROM `game` WHERE sportid=1 AND 'deleted_at' IS NULL AND `date` BETWEEN '2014-07-01' AND '2015-06-30' GROUP BY `date`,hometeam HAVING `num`>1
            UNION
SELECT COUNT(*) as num,awayteam as teamid,`date` FROM `game` WHERE sportid=1 AND 'deleted_at' IS NULL  AND `date` BETWEEN '2014-07-01' AND '2015-06-30' GROUP BY `date`,awayteam HAVING `num`>1 ORDER BY `num` DESC;


texts: My cluster disappeared, but data is still there

texts:
I was tasked with recovering data from an old, unmaintaned server, where its main purpose application was no longer working. Basically just want to get a postgres db dump before burning the server to the ground.

I ssh into the instance, but cannot access the database.

psql: FATAL:  database "foobar" does not exist

That's weird. I do pg_lscluster

Version Cluster   Port Status Owner    Data directory                     Log file
9.1     main      5433 online postgres /var/lib/postgresql/9.1/main       /var/log/postgresql/postgresql-9.1-main.log

Okay, something is running. I check the data dir ls /var/lib/postgresql/

8.4  9.1

Huh, okay then. looking at the contents of the two dirs, it is pretty clear that all of the data is in the 8.4 folder, and the 9.1, I can see only holds default data, when I look using psql for the running cluster.

So somehow the cluster that ran 8.4 was removed. No idea why or how (I'm not the only one with access to the server, but I cannot find anything supicious in bash history).

So how would I go about restoring access to the data, so I can pull out a dump?


texts: How to monitor InnoDB Disk I/O Required for MySQL?

texts:
I am doing an estimate on how much i/o[disk read write] on Mysql 5.6 [innodb storage engine], for getting idea how to calculate total number of disk read write i have searched a lot on SO on DA and googled also  and encounter a post give on this link Which is quite helpful.

It says I have to use SHOW STATUS

mysql>SHOW STATUS;

And monitor Key_read and Key_write for physical index read write , Created_tmp_disk_tables and Created_tmp_files  for creating temp file  for creating temp table and file on disk.

As i am using innodb , buffer pool size also affected on total number of disk read operation [which is totally depend on availability of hot pages ] so here i am confused about

  1>>.What are the Innodb "Server Status Variables” i should consider while
    counting disk  read write?

  2>>.Are there any other "Server Status Variables” i should consider while
    counting disk  read write?

  3>>. Are there other factor i am missing here?

  4>>. Dose hard disk type and speed will affect on total i/o disk read write?

thanks in advance!!!


texts:
I have established a master A and master B replication between two servers. Both are getting replicated, but it is limited to only one database.

My questions:

How to create a new database in Master A has to replicated in Master B or doing the same in vice versa?
Need to Replicate more than one database


texts: When doing an UPSERT, how do I return the record even if the update doesn't actually make a change?

texts:
This is a problem for me because I am processing upserts off a queue that can not be made to be FIFO. So we have the application sending us a timestamp with the record to upsert. We only want to update if the timestamp is newer than the record in the DB, but we always want to return the record.
Currently the query looks something like:
INSERT INTO people (firstName, lastName, email, timestamp)
ON CONFLICT ON CONSTRAINT "unique_email"
DO UPDATE SET
firstName = EXCLUDED.firstName,
lastName = EXCLUDED.lastName,
timestamp = EXCLUDED.timestamp
WHERE people.timestamp < EXCLUDED.timestamp
RETURNING *

Which does exactly what I need it to do, EXCEPT, when I get a stale record from the queue. The DB record is more current than the queue record, and I want that current DB record returned.
I am trying to do this without making separate checks from the application to reduce latency between the DB and app code.
So I want something like:
INSERT INTO people (firstName, lastName, email, timestamp)
ON CONFLICT ON CONSTRAINT "unique_email"
DO UPDATE
IF people.timestamp > EXCLUDED.timestamp
  SET email = EXCLUDED.email #essentially forcing an update that doesn't really update anything
ELSE
  SET
  firstName = EXCLUDED.firstName,
  lastName = EXCLUDED.lastName,
  timestamp = EXCLUDED.timestamp
  WHERE people.timestamp < EXCLUDED.timestamp
END
  RETURNING *

How would I do something like this?
My current working solution seems pretty inefficient, but I want to include it in case it helps.
INSERT INTO people (firstName, lastName, email, timestamp)
ON CONFLICT ON CONSTRAINT "unique_email"
DO UPDATE SET
firstName = CASE
  WHEN people.timestamp < EXCLUDED.timestamp
  THEN EXCLUDED.firstName
END,
lastName = CASE
  WHEN people.timestamp < EXCLUDED.timestamp
  THEN EXCLUDED.lastName
  ELSE people.lastName
END,
timestamp = CASE
  WHEN people.timestamp < EXCLUDED.timestamp
  THEN EXCLUDED.timestamp
  ELSE people.timestamp
END
RETURNING *


texts: Trying to check if a string contains a number exclusively

texts:
I have been trying to write a function to check if a string contains a number without that number being part of a bigger number (in other words if the number being searched for is '6' and the string is '7+16+2' it should return false, because the '6' in this string is part of the number '16')

I wrote the function below (It is lengthy but I was going to test it first before refactoring it)

Upon testing I found a bug whereby it only runs the first instance of a found number through the logic.  So running this function with '6' against '16+7+9+6' would return false because it determines that the firrst '6' is part of a bigger number and stops processing.

I figured that to get around this I have to implement a loop to shorten the 'haystack' string (so that, using the example '16+7+9+6' the function continues checking '+7+9+6' after eliminating the first '6') but before spending the time to make an already convoluted function even more convoluted I wanted to check if there is a simpler way to acheive the same goal?

drop function dbo.runners_contain_runner
go
create function dbo.runners_contain_runner(@runner varchar(max), @runners varchar(max))
returns int
as
begin

    /*
    eliminate the plus sign from @runners so that the
    'isnumeric' function doesn't return false positives (it returns 1 for '+')
    */
    set @runners = replace(@runners,'+','_' )

    declare @ret int;
    set @ret = 0;

    -- if the runner is the only runner return 1
    if @runners = @runner
        set @ret = 1
    else
    begin
        declare @charindex int;
        set @charindex = charindex(@runner,@runners)
        if @charindex > 0
        begin

            -- if it is at the beginning then check the char after it
            if @charindex = 1
            begin
                if isnumeric(substring(@runners,@charindex + len(@runner),1)) = 0
                    set @ret = @charindex
            end

            -- if it is at the end then check the char before it
            else if @charindex = len(@runners) - (len(@runner) - 1)
            begin
                if isnumeric(substring(@runners,@charindex - 1,1)) = 0
                    set @ret = @charindex
            end

            -- if it is in the middle check the chars either side of it
            else
            begin
                if isnumeric(substring(@runners,@charindex - 1,1)) +
                isnumeric(substring(@runners,@charindex + len(@runner),1)) = 0
                    set @ret = @charindex
            end
        end
    end
    return @ret
end


texts: Is there a way to disable / disallow someone from using View Creator using Diagram Tool in SQL Server?

texts: We have a lot of DB and BI analysts who regularly create views. Some folks create these views using SSMS' view creator which allows someone to drag and drop tables. This creates a lot of problems when we need to review or modify their code as the diagram tool in SSMS messes up the indentations and formatting, making it impossible to review the code. Is there an option or permission that I can revoke/change that disallows everyone from using it?


texts: What is the easiest way to install the latest postgis_tiger_geocoder?

texts:
Ubuntu distributes PostGIS with a core extension called PostGIS TIGER Geocoder, packaged as postgis_tiger_geocoder. Currently, as of PostGIS 2.2.2, it pulls data from TIGER2015. I would like to upgrade this to pull from TIGER2016. I want to use the newest version of the geocoder. How do I go about this?

I want to upgrade just postgis_tiger_geocoder?


texts: High CPU usage, low memory usage on Mariadb 10.5.8 -- SQL tuner complaining about memory, performance seems fine

texts:
I'm hoping to get some friendly advice how to proceed with optimizing this server.
VM:
8vCPU - 2.6GHz
32GB mem
Disk speed
~3Gbps (380MBps)

Application is Librenms
my.cnf.d/server.cnf  -- Built from template somewhere on github
mysqltuner.pl output
The server currently idles at around 500% cpu with Mariadb being the culprit
sar -q output
free -m output


texts:
I am having trouble connecting to my Microsoft SQL Server database through a jTDS connection:

Connection conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433
    /MnA;instance=SQLEXPRESS", "sa","");

SQL Server is enabled, TCP/IP is enabled, Mixed server authentication is enabled.

Any ideas?

java.sql.SQLException: Login failed for user 'sa'.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
    at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:603)
    at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:345)
    at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
    at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at DB.dbConnect(testConnection.java:17)
    at testConnection.main(testConnection.java:7)


texts: (VS Comm./VB6) How to Select from 2 different data sources/formats in one query?

texts:
I have two working TableAdapterQueries in my project, one selects from a MySQL database on a virtual server I own, the other is an Oracle PL/SQL database on a separate server.

The data (and datatypes) in the MySQL database come from the Oracle Database. The point of the script will be synchronisation of a sort.

What I fundamentally want to do is something like this, in a query

Dataset3 = "Select ID_Column From OracleDB.Tablename
             where OracleDB.TableName.FieldName NOT IN (
                     Select ID_Column From MySQL.Tablename)"

To be clear, I have 1 Dataset/Datatable connecting to Oracle fine, one connecting to MySQL fine, I just need to write back to the MySQL one with values found, not found, and changed.

i.e.  'Select from Oracle where not in MySQL' would give a list of all items that need to be added to MySQL table.

'Select from MySQL where not in Oracle' is a list of items that need to be removed, and

'Update MySQL[field] where in Oracle

Is this all doable in VS Community now that I have working Datatable connections?

I'm doing this in VB where possible as I'm most comfortable with debugging and writing it. I'd accept a .NET or C# answer if it were clear how to modify it.

Even if the 'answer' to this question is just a googling topic suggestion. I have found stuff on LINQ to two data bases, but it's all compiled in solutions that are quite hard to read and understand, let alone adapt to my application

Thanks


texts: MSSQL: High number of logical reads when inserting into fragmented heap

texts:
I'm inserting a batch of rows - using INSERT INTO tblUploadBanking SELECT FROM ... - into a table, which is a heap and have 3 narrow NC indexes.

When I execute the query from SSMS with SET STATISTICS IO ON, I can see output like this:

Table 'tblUploadBanking'. Scan count 0, logical reads 709738, physical reads 493, read-ahead reads 429, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 3, logical reads 136234, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblTrans_Cash_HO'. Scan count 1, logical reads 96944, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblPaymentType'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblWorkstation_Group'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

When I sum the number of logical reads, I'm getting 942922 reads. But, when I look into the output from profiler, I can see for this query 1475067 logical reads, which is 532k more than the sum from statistics output. BTW, from Extended Events I'm getting same number like from profiler.

What are these hidden reads? The only thing I have found out so far is that there is a connection with heap and indexes fragmentation. When I rebuild the heap, the execution plan remains exactly the same - including cost distribution, rows estimations etc. The number of reads in statistics is about the same as well, but in the profiler the number of logical reads has dropped to 1007183, which is almost matching with the output from statistics. Also the CPU time has reduced by 25%.

With other queries I had even bigger discrepancies - for few sessions I saw in trace 6-10M reads, but according to statistics there were just about 900k of logical reads. These queries were also inserts into fragmented heap and rebuilding the heap "solved it" as well like in this case.

From my knowledge about the heaps, there shouldn't be any reason for slower inserts when the heap is fragmented. It could be because of fragmented NC indexes, but I would expect that logical reads needed for index insert are included in statistics for the given table.

PS: Please do not suggest me to turn table from heap to clustered table, it's a plan for the future which is not possible now.


texts: What is the MySQL equivalent of Postgres' C collation?

texts:
I'm working on reimplementing a Postgres schema in MySQL, and one of the things I can't seem to find a clear equivalent for is Postgres' "C" collation.

id TEXT COLLATE "C" NOT NULL

What would be the equivalent collation for MySQL?


texts:
I'm making a table which has "steps", The thing is

  Some steps may have multiple steps involved. So while looping over the steps I should be able to show this.

What I'm thinking of

+----+----------+-------------+-------+----------------+
| id | guide_id | description | order | parent_step_id |
+----+----------+-------------+-------+----------------+
|  1 |        1 | Step 1      |     1 | null           |
|  2 |        1 | Step 2      |     2 | null           |
|  3 |        1 | Step 2.1    |     3 | 2              |
|  4 |        1 | Step 2.2    |     4 | 2              |
+----+----------+-------------+-------+----------------+

This seems like the only logical way of doing this? But i'm hoping i'm wrong.

Should I create another table?

Looking forward to your responses.
Thank you!


texts: How can I configure RMAN to delete crontrol files automatically?

texts:
I am using Oracle Release 12.2.0.1.0 Standard Edition on ODA (Oracle Database Appliance, running linux). My instance "xyz" is running in archivelog mode. RMAN performs backup and unnecessary datafiles are deleted automatically from the backup set.

[oracle@oda ~]$ rman TARGET /
Recovery Manager: Release 12.2.0.1.0 - Production on Fr Aug 31 16:50:05 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Mit Zieldatenbank verbunden: XYZ (DBID=4079461799)
RMAN> show all;
Kontrolldatei der Zieldatenbank wird anstelle des Recovery-Katalogs verwendet
RMAN-Konfigurationsparameter für Datenbank mit db_unique_name XYZ sind:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mnt/backup/rman/xyz/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/mnt/backup/rman/xyz/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/app/oracle/fast_recovery_area/XYZ/controlfile/snapcf_xyz.f';

When I check the directory everything looks fine - except for the control files which keep piling up

[oracle@oda1 xyz]$ pwd
/mnt/backup/rman/xyz
[oracle@oda1 xyz]$ l
total 4144832
drwxr-xr-x 2 oracle oinstall       4096 Aug 31 11:00 .
drwxr-xr-x 7 oracle oinstall       4096 Jul  4 14:38 ..
-rw-r----- 1 oracle asmadmin 1072447488 Aug 29 01:02 6ntbnscr_1_1
-rw-r----- 1 oracle asmadmin     943104 Aug 29 01:02 6otbnshm_1_1
-rw-r----- 1 oracle asmadmin   18115072 Aug 29 11:00 6qtbovhi_1_1
-rw-r----- 1 oracle asmadmin   62260224 Aug 30 01:00 6stbqgoj_1_1
-rw-r----- 1 oracle asmadmin 1072734208 Aug 30 01:05 6ttbqgq1_1_1
-rw-r----- 1 oracle asmadmin    1159680 Aug 30 01:05 6utbqh2k_1_1
-rw-r----- 1 oracle asmadmin   10599424 Aug 30 11:00 70tbrjtj_1_1
-rw-r----- 1 oracle asmadmin   42828288 Aug 31 01:00 72tbt54i_1_1
-rw-r----- 1 oracle asmadmin 1073242112 Aug 31 01:02 73tbt54q_1_1
-rw-r----- 1 oracle asmadmin    2316288 Aug 31 01:02 74tbt59l_1_1
-rw-r----- 1 oracle asmadmin   17406976 Aug 31 11:00 76tbu89j_1_1
-rw-r----- 1 oracle asmadmin   10698752 Jun 20 11:11 c-4079461799-20180620-00
-rw-r----- 1 oracle asmadmin   10698752 Jun 20 11:25 c-4079461799-20180620-01
-rw-r----- 1 oracle asmadmin   10698752 Jun 20 13:03 c-4079461799-20180620-02
-rw-r----- 1 oracle asmadmin   10698752 Jun 20 13:48 c-4079461799-20180620-03
-rw-r----- 1 oracle asmadmin   10698752 Jun 20 14:03 c-4079461799-20180620-04
-rw-r----- 1 oracle asmadmin   10698752 Jul 16 10:22 c-4079461799-20180716-00
-rw-r----- 1 oracle asmadmin   10698752 Jul 26 17:32 c-4079461799-20180726-00
-rw-r----- 1 oracle asmadmin   10698752 Jul 27 01:02 c-4079461799-20180727-00
-rw-r----- 1 oracle asmadmin   10698752 Jul 27 11:00 c-4079461799-20180727-01
..

The files beginning with "c" are the control files.

Question: What can I do to delete these files also automatically?

Thank you for your insight!


texts: How to add constraint on a varchar column to have at least one non numeric character

texts:
I am trying to implement some constraints on tables so that there is no tampering of the data from what is expected.

One of the constraints is on a varchar column. It should at least contain one non-numeric character.

How can I implement this constraint in SQL Server, and if I implement it, What will be the effect on the performance if any?


texts:
I'm experiencing some extremely strange behavior on one of our OLTP Servers. Just to provide a bit of background, we have an 'OLTP' which has many large and wide tables. We have 560 tables within it which have more than 100 columns within them, and unfortunately as it is an outsourced application there is little I can do with the database design.

So, the graphs below show page reads and writes per second. We have a large spike with reads at around 4 Am which is a load to Qlikview which we are aware of. However, page reads on the whole tend to be lowish, I think they are still higher than the 90 page per second threshold, but less of an issue in comparison to writes. Writes on the other hand, as the graph displays is much higher with regular instances where values are higher than 500 pages per second.

Since 15/10/2018, for the vast majority of data capture intervals, the below has applied

where Lazywrites per second is greater than 1.
Page life expectancy also is normally very high. We do have instances where the value drops during the morning which we know about (4AM), however with the odd exception, we are almost always above the suggested threshold 7500s (Memory/4)*30.
Buffer Cache Hit Ratio also is very healthy at or around 99%.
Finally, we have had very few if any memory related waits, Resource_Semaphore is at 0 seconds and CMEM_THREAD has accumulated 60seconds since 27/10/2018.
Values for Memory grants pending is 0 and Memory grants Outstanding is 1 with data collected since 27/10/2018.

These factors, in my opinion rule out memory pressure being an issue but I can't seem to explain the strange spikes with regards to writes per second. In my opinion, it's related to our database design. We do have extremely wide tables which can be inserted/updated/deleted to frequently. We also have many wide indexes with inefficient data types.

From the data and graphs I've provided, are there any signs of what they high writes is attributed to or are there any metrics I can use to explain the spikes?

Version:

  Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64)
  Jul  3 2017 02:25:44   Copyright (c) Microsoft Corporation  Standard
  Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Data Source

  The graphs above are taken from solarwinds, the data I assume is collected from sys.dm_os_performance_counters
  All other counter data which I have references is derived from sys.dm_os_performance_counters, captured at periodic intervals with the wait stats taken from sys.dm_os_wait_stats.

Thanks


texts:
Im installing pgAgent using Stack Builder.

In one step ask me for service account. Should I use PC, Domain or DB user? Or create a new one?

I try using my DOMAIN\my.user.name  also using the postgres user and return same error.

I dont have the PC_Name\Administrator password

  User creation failed. The password specified is incorrect or length of the user name is longer than supported


texts: How does database connection actually works?

texts:
I am wondering, on a practical level : what is a database connection?
What does it represent for PHP/Java/Ruby/etc to have an opened connection for instance? What about the MySQL deamon? Does it materialize in some way?

From PHP side, I can guess it is an object like any other. I can imagine a similar concept from MySQL's point of view. But is there an "in between" handled by one or both sides that actually materializes the connection in some way?

My apologies if the question sounds very vague. I am so used to speak of PDO "database connections", I realize now that I am not sure at all what that is. I understand what it does, but how it does it just seems very foggy.

Many thanks for every clarification of this concept.
Pointing me towards a good reading is considered a valid explanation :D


texts:
I have some complex queries that are usually fast (< 1 sec), but occasionally slow (> 10 sec, even minutes).

How do I track this down or identify what condition is causing it?

One theory was caching - perhaps the query is fast when the blocks cached in memory.  I tried flushing both the shared pool and the buffer cache ("alter system flush buffer_cache") but the query is still fast after doing that.

Is there a way to nail down precisely what was going on during a specific execution that was bad, and which part of query execution took the longest?


texts: Error 3154 while restoring a backup using WITH REPLACE

texts:
I have SQL 2012 with SP1 installed on my computer. I made a backup of a database test.bak.

I have a database with the name test2 which is the same database, but the data changed.

I want to restore test.bak over test2 database.

I am always getting the error:

  Error 3154: The backup set holds a backup of a database other than the existing database.

I tried:

I right-cliked on test2 -> Restore database -> From device

I chose test.bak and checked With Replace but I get the error.
Then I tried to right-click on test2 -> Restore file and filegroups

I chose test.bak and checked With Replace but I get the error.

I can delete my old database and then restore my backup with the right name, but when I was using SQL 2008, I had no problem restoring over an existing database.

It seems that since I use SQL2012, I get this error a lot!


texts: What is the best way to recover from a mysql replication fail?

texts:
Today, the replication between our master mysql db server and the two replication servers dropped.
I have a procedure here which was written a long time ago and i'm not sure it's the fastest method to recover for this issue.
I'd like to share with you the procedure and I'd appreciate if you could give your thoughts about it and maybe even tell me how it can be done quicker.

At the master:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
And copy the values of the result of the last command somewhere.

Wihtout closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:

mysqldump mysq
Now you can release the lock, even if the dump hasn't end. To do it perform the following command in the mysql client:

UNLOCK TABLES;
Now copy the dump file to the slave using scp or your preferred tool.

At the slave:

Open a connection to mysql and type:

STOP SLAVE;
Load master's data dump with this console command:

mysql -uroot -p < mysqldump.sql
Sync slave and master logs:

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
Where the values of the above fields are the ones you copied before.

Finally type

START SLAVE;
And to check that everything is working again, if you type

SHOW SLAVE STATUS;
you should see:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
That's it!

At the moment i'm in the stage of copying the db from the master to the other two replication servers and it takes more than 6 hours to that point, isn't it too slow?
The servers are connected through a 1gb switch.


texts:
I have enabled default auditing in one of my database. Oracle 11g. Audit_trail is set to DB, other than that for a particular schema 'test' I have set:

audit all on test by access

With the above setting, I know I can get to know what action has been performed (alter,create,update,etc) but will I be able to fetch the SQL statments (all DML operations) with the exact SQL statement fired from that schema from the audit records? Let me know how?

If the above is not possible, what is required to fetch the exact SQL statement that is run by the schema.


texts: Why do I need to reset user mapping always after restoring my DB

texts: I have created a user using SQL Server 2014 management studio, I have mapped user to a couple of tables as db_owner. But after restoring my databases the mapping again cleared for the user and I need to set the User Mapping. How to fix it?


texts:
In what ways is consistency weakened when using pgpool-II load-balancing on Postgresql servers running streaming replication?

Obviously with asynchronous replication you can insert a row on the master server and then attempt (and fail) to select it from a slave. But let's assume synchronous replication is used (synchronous_commit and synchronous_standby_names options).

Are there still consistency issues to be considered?

I assume serializable transactions are always sent to the master server?


texts: Cassandra - Why does Read/Write Latency fluctuate?

texts:
Question

Why does write (read as well) latency fluctuate in Cassandra?

Background

Running Cassandra Stress Tool on a node (s01) against another node (m01). Run several times but always saw the latency (write as well as read) always flucuating. The metric is from Cassandra JMX oneMinuteRate of the keyspace.

$syds01 > cassandra-stress write -node sydm01

The replication factor was changed to 1.

cqlsh > ALTER KEYSPACE keyspace1 WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };

Write

Result (example)

Results:
Op rate                   :   73,184 op/s  [WRITE: 73,184 op/s]
Partition rate            :   73,184 pk/s  [WRITE: 73,184 pk/s]
Row rate                  :   73,184 row/s [WRITE: 73,184 row/s]
Latency mean              :   12.4 ms [WRITE: 12.4 ms]
Latency median            :    6.2 ms [WRITE: 6.2 ms]
Latency 95th percentile   :   40.6 ms [WRITE: 40.6 ms]
Latency 99th percentile   :   73.8 ms [WRITE: 73.8 ms]
Latency 99.9th percentile :  127.3 ms [WRITE: 127.3 ms]
Latency max               :  423.4 ms [WRITE: 423.4 ms]
Total partitions          :  6,108,134 [WRITE: 6,108,134]
Total errors              :          0 [WRITE: 0]
Total GC count            : 206
Total GC memory           : 64.555 GiB
Total GC time             :    8.2 seconds
Avg GC time               :   40.0 ms
StdDev GC time            :   17.9 ms
Total operation time      : 00:01:23

Improvement over 609 threadCount: 37%

Read

Environment

Cassandra 3.7 on VMWare 4CPU 12G memory (m01 and s01)

[cqlsh 5.0.1 | Cassandra 3.7.0 | CQL spec 3.4.2 | Native protocol v4]

Linux

Ubuntu 14.04.3 LTS 64 bit

VMWare


texts: How can dm_db_index_usage_stats show 0 for user_seeks and user scans but a large number for user_lookups?

texts:
I'm probably not understanding something but here is the question.

I have done a query on dm_db_index_usage_stats. I've noticed that a couple of my indexes have large numbers (6.5 million and 20 thousand) in the user_lookups column but these also have a zero in both the user_seeks and user_scans columns.

My understanding (from books online) is that the user_lookups column counts bookmark lookups. As I understand it that is when the database can't find all the columns it needs in an index and has to visit the main table or clustered index for the remaining columns. However why would SQL server have done this if it has never done an seek or scan on the index?


texts: Does InnoDB Log Files take RAM space or Disk Space

texts:
When I set InnoDB Log File size to 512M, does it take the space (512M * 2) from RAM or the SSD Disk space?

When I explored, I can see that the log files are located inside /var/lib/mysql folder. So from this I am assuming it is infact stored in disk space and not RAM. I always thought when I allocate log files space it was coming off the RAM and now I have a feeling I was wrong with that assumption. Can someone confirm this please...

Also I am considering to increase the log size to maybe 1GB (for a 3GB Buffer Pool Size) and I am not sure if that would have an effect on my ram usage where I would need to increase my Ram too when I increase the demand for log size. If log files doesnt consume Ram space then I dont see it affecting my ram even if the logfile size are increased. And hence my question to clarify this...


texts: Postgresql - Is this possible to setup a Zero downtime PostgreSQL Cluster?

texts:
I have already asked here about PostgreSQL HA setup, and I got many possible answers. But the bad thing is, even though if I use any third party software like PGpool, Repmgr, pacemaker are asking for timeout(If master is unavailable for n seconds). So there will be a minimal downtime.

Is there any best option to overcome this and setup a zero downtime cluster?


texts:
I have granted priviledges to a user for testing in PostGreSql:

GRANT USAGE ON SCHEMA "MySchema" TO "Tester";
GRANT ALL ON ALL TABLES IN SCHEMA "MySchema" TO "Tester";
GRANT ALL ON ALL SEQUENCES IN SCHEMA "MySchema" TO "Tester";

Yet, when I run the following statement;

TRUNCATE TABLE "MySchema"."USER_ACCOUNT" RESTART IDENTITY;

I get the following error message:

[error: must be owner of relation USER_ACCOUNT_ID_seq]

What else do I need to grant to my Tester user, and how? Thanks!


texts: How to install downgrade of SQL Standard 2016 to Standard 2014

texts: This is probably a super basic question, but if I purchase an OEM SQL Server 2016 Standard edition, how would I install it as 2014 to match the other servers? Can I get a 2014 installer and use the 2016 license key during the installation?


texts:
I have a request to send emails the first of every month for each department that hasn't submitted their hours.  I have a query that generates a list of email addresses, the email subject, and the email body.  However, in the Data Flow SSIS Toolbox there is not a "Send Email Task" option like there is in the Control Flow.

How can I send emails based off of my query results?


texts: Determining primary key columns in PGAdmin, if the constraint name is generated by system

texts:
I am trying to learn a new schema of a PostgreSQL database, but it's unclear from looking at pgAdmin which columns per the tables constitute the primary keys. It appears that when the tables were created, they were created with the default PRIMARY KEY (primary_key_column_name)
This results in the default naming convention when checking under the constraints in pgAdmin. How can I check to see what the actual names of the columns are for the primary key for a table?


texts: How do I enable READ-only (passive) mode in a particular RDBMS?

texts:
As most of you have noticed all StackExchange sites have been under maintenance/recovery a few days ago so they switched on READ-only mode. From their blog I know they used to use MS SQL Server 2008, and recently have moved on to SQL 2012.

The question is how do you switch READ-only mode in a specific RDBMS, thus keep the service up during maintenance?

Since I use CUBRID RDBMS most of the time, I know they provide an ACCESS_MODE switch in CUBRID Broker middleware (cubrid_broker.conf) which allows DBAs to dynamically turn a READ/WRITE-enabled service to READ-only (+ ...) and vice versa. This is especially useful in an HA environment.

In MySQL as I know, even if you turn read_only=1 in my.cnf/my.ini, users with SUPER and replication privileges still can write to a read only server.

I wonder how this is done in other relational databases. How does SO do this?


texts: SQL Server - difference between instance name and instance id

texts:
I'm a bit confused with SQL Server instances. The Instance Name and Instance ID are different properties, yet Instance ID tends to be defaulted to a capitalized version of the Instance Name. The installation allows you to set a different value for the ID than the Name, although typically they're the same.

Obviously, ID refers to internal reference and Name refers to user-friendly reference. But what are the fundamental differences between the two? When should one be used rather than the other?

I need to understand this while deploying / installing SQL Server 2008 R2 Express on our clients' server machines from a custom installation process.


texts: Best practice for storing record metadata

texts:
What is the best practice for storing metadata of individual records in a database?

I need to store common meta data such as creation time and time of last update for many tables in my database. I found a few different solutions:

Store the meta data directly in the tables.

Pros:

Meta data is directly linked to records
No joins are required to retrieve meta data

Cons:

A lot of duplicate columns are required (unless inheritance is used)
Meta data and business data are not separated

Create a general meta data table with and use soft foreign keys to link data to the correct tables and records.

Pros:

No duplication of columns
Meta data is separated from business data

Cons:

No direct links between meta data and data (FK's can't be used)
Joins require an additional condition

Create individual meta data tables for each table requiring meta data.

Pros:

Meta data is directly linked to records
Meta data is separated from business data

Cons:

A lot of extra tables are required
A lot of duplicate columns are required (unless inheritance is used)

Are there more options, pros or cons than the ones I mentioned here? And what is the best practice for storing this meta data?


texts:
Suppose I have a table containing job roles:

CREATE TABLE roles
(
  "role" character varying(80) NOT NULL,
  CONSTRAINT "role" PRIMARY KEY (role)
);

Suppose I further have a table, users, and each row (a specific user) can have an arbitrary number of job roles:

CREATE TABLE users
(
  username character varying(12) NOT NULL,
  roles character varying(80)[] NOT NULL,
  CONSTRAINT username PRIMARY KEY (username)
);

I should probably make sure that each member of users.roles[] exists in roles.role.  It seems to me that what I want is a foreign key constraint on each member of users.roles[] such that if references roles.role.

This doesn't seem possible with postgres.  Am I looking at this the wrong way?  What is the suggested "right" way to handle this?